page 108

5. DATABASES

Databases are used to store various types of information in a manufacturing enterprise. For example, consider an inventory tracking system. A simple database might contain a simple list of purchased parts. Each part is a line in a database table, as shown in figure X.1. If a new inventory item is added, a new row is added to the table. If parts are added or removed to the inventory, the quantity value for one of the rows is changed. The total inventory cost can be calculated by multiplying the quantity and part costs together, and summing these for all rows. The tables are often designed to suit the way a particular business runs.

Figure X.1 - A Simple Inventory Table

A more complex database will be made up of many tables that relate items together. For example a more complex database might have separate tables for customer data, supplier data, purchased inventory, work in process, finished inventory, etc. The purchased inventory table might refer to a supplier number that identifies a supplier in the supplier table. The formal name for a database that uses related tables of information is ’relational’.

page 109

In modern applications a database (server) will run on one computer, but be shared by many other computers (clients) that access it through networks. Client programs might be highly variable. For example a worker on the shop floor may only be able to view order information. A shop floor supervisor might be able to change order status, personnel tables. A salesperson might be able to enter new orders, and check on order status. It is also possible to access the database directly and make special inquiries using a special command language called Structured Query Language (SQL).

In summary, database allow information to be;

-stored and managed in a central location

-shared with many other computers

-structured and accessed quickly

-searched for patterns and matches

5.1 SQL AND RELATIONAL DATABASES

Structured Query Language (SQL) was developed to provide a common interface language for accessing relational databases. The key concept behind relational databases is that all information is stored in tables. The example in Figure X.2 illustrates a customer order tracking system that uses three tables. Consider the first table called ’Orders’, it contains four rows, each with an order number. The first three rows are for the same order, and order number. In this case all three entries are also for the same customer, but it involves three different parts. The entries in the ’customer_id’ and ’part_id’ columns can be used to lookup more information from the two other tables.

page 111

CREATE TABLE Orders ( order_number INTEGER, customer_id INTEGER, part_id INTEGER, quantity INTEGER

)

CREATE TABLE Customers ( customer_id INTEGER, name CHAR(50)

)

CREATE TABLE Parts ( part_id INTEGER, description CHAR(20), location CHAR(25)

)

The queries can also be combined onto a single line.

CREATE TABLE CUSTOMERS (customer_id INTEGER, name CHAR(50))

Figure X.3 - The SQL Commands to Create the Tables in Figure X.2

Figure X.4 shows SQL commands to enter data into the tables. In all cases these statements

begin with ’INSERT INTO’, followed by the table name. After that the data fields to fill are

named, followed by the actual values to insert. The column names are provided so that the values

can be supplied in a different order, or omitted altogether. In this example all of the values are pro-

vided for all ’INSERT INTO’ statement, but this is not necessary.

page 112

INSERT INTO Orders (order_number, customer_id, part_id, quantity)

VALUES (00103, 002, 0001, 1)

INSERT INTO Orders (order_number, customer_id, part_id, quantity)

VALUES (00103, 002, 0000, 1)

INSERT INTO Orders (order_number, customer_id, part_id, quantity)

VALUES (00103, 002, 0002, 1)

INSERT INTO Orders (order_number, customer_id, part_id, quantity)

VALUES (00134, 001, 0002, 50)

INSERT INTO Customers (customer_id, name) VALUES (000, ’ACME Dastardly Gadgets’) INSERT INTO Customers (customer_id, name) VALUES (001, ’Widgets Inc.’)

INSERT INTO Customers (customer_id, name) VALUES (002, ’I.M. Reech and Co.’)

INSERT INTO Parts (part_id, description, location) VALUES (0000, ’cylinder’, ’bin 5-4’) INSERT INTO Parts (part_id, description, location) VALUES (0001, ’valve’, ’bin 2-3’) INSERT INTO Parts (part_id, description, location) VALUES (0002, ’hose’, ’bin 8-2’)

Figure X.4 - Entering Data Into The Tables

Once data has been entered into the database it can be recalled using a simple ’SELECT’ statement. In the first example the ’*’ indicates to select all data values ’FROM’ the ’Customers’ table. The second example shows only listing the ’name’ values from the ’Customers’ table. Finally the third example shows the listing of ’order_numbers’ from the ’Orders’ table where the ’quantity’ of parts is greater than 10.

page 113

SELECT * FROM Customers

Customerscustomer_idname

000 ACME Dastardly Gadgets

001 Widgets Inc.

002 I.M. Reech and Co.

SELECT name FROM Customers

Customersname

ACME Dastardly Gadgets

Widgets Inc.

I.M. Reech and Co.

SELECT order_number FROM Orders WHERE quantity > 10

Orders order_number 00134

Figure X.5 - Simple Database Query Examples

It is possible to make database queries where the results are merged from many different tables. The example in Figure X.6 shows a query that is to list values for ’order_number’, ’name’, ’description’ and ’location’. These are to be merged from three tables ’Orders’, or ’O’, Customers, or ’C’, and ’Parts’, or ’P’. Finally, the conditions for a match follow the ’WHERE’ statement. The conditions are the ’customer_id’ field in the ’Customer’ and ’Order’ tables must match., and the ’part_id’ field must match in the ’Order’ and ’Part’ tables.