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.
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’.
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).
7.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.
The tables in Figure X.2 can be created using the SQL commands in Figure X.3. One command is needed for each table. Each command starts with ’CREATE TABLE’ followed by the name of the table. After this the columns of the table are defined. In the case of the ’Orders’ table there are four columns. Each column is given a unique name, and a data type is defined. Once these commands have been used the tables will exist, but be empty with no data in them.
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 provided for all ’INSERT INTO’ statement, but this is not necessary.
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.
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.
The SQL queries are easily used when interacting with a command interface. Although, it is more common for these commands to be used from within computer programs that call the database to make automatic queries.
7.2 Database Issues
7.3 Laboratory - SQL for Database Integration
Databases store information in tables. The users can use or manipulate this data to suit other purposes. The fundamental language for interacting with the databases is SQL. This lab will offer a simple introduction, and then go on to interface with the database using C programs in the following lab.
1. Check to see if the database is installed. One way to do this is to look for the database server using ’which postmaster’. If it is not installed it can be installed from the Redhat distribution CD, or by downloading it from www.postgresql.org.
4. Log in as the user ’postgres’ - An account called ’postgres’ is normally defined in most modern Linux distributions, but the account password is disabled. To log in the first time you must be logged in as root, then log in as postgres with ’su - postgres’. You can change the password so you can log in directly as postgres in the future. To do this, log in as root and then use ’passwd postgres’. Verify that you are logged in as postgres before continuing with ’whoami’.
6. At this point the database should be ready for use. In an earlier chapter you turned on database support, so you should not need to do so again. But you can always force the database to start with the ’pg_ctl’ function. You can also check to see if the ’postmaster’ program is present in the process list (i.e. ’ps -auxw’).
7. Use ‘createuser <YOUR_LOGIN_NAME_GOES_HERE>’ to add yourself as a valid database user. Answer ‘y’es when asked if you are allowed to create databases. And answer ‘y’es when asked if you can create new users. These choices will allow you full control over the database. Note: ‘destroyuser <YOUR_NAME>’ can be used if you need to remove a user.
1. Start the database when logged in as postgres with ’postmaster &’. This should start the database server. You can check to see if it is accepting connections with the command ’netstat -a | grep postmaster’. If the postmaster program is listed the database is ready for use.
2. The database can also be set up to run automatically each time the computer is rebooted by adding it to the ’system V’ initialization list. But, this step is not necessary unless setting up a permanent database.
7.4 Laboratory - Using C for Database Calls
The program listing in Figure X.8 can be used to access the Postgres database. It uses a database access library called ’libpq’. This library of functions allows SQL database queries. The results of the query can then be easily retrieved.
In this example the program begins with an attempt to connect to the database using the function ’PQconnectdb’, and the status of the connection is then checked using ’PQstatus’. An SQL query is passed to the database using the ’PQexec’ command, and the results are returned into the ’PGresult’ structured called ’res’ in this example. The results can be checked using ’PQresultStatus’, and retrieved using the ’PQgetvalue’ function. The ’PQntuples’ function returns the number of matching results.