6.3 LABORATORY - SQL FOR DATABASE INTEGRATION

---

Purpose:

To learn the basic command language, SQL, that is used to interact with relational databases.

Overview:

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.

Pre-Lab:

Read the SQL and database material.

In-Lab:

First Time Installation:

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.

2. Log in as root with 'su - root' and edit the file '/var/lib/pgsql/.bashrc' to include the following lines. (Note: the .bashrc file may not exist, but it will be created by the editor.)

PGLIB=/usr/lib/pgsql

PGDATA=/usr/lib/pgsql/data

export PGLIB PGDATA

3. Change the ownership of the postgres directory to the user postgres with the command 'chown postgres /usr/lib/pgsql'.

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'.

5. Set up the databases with the command 'initdb'. This will set up all of the needed files and directories.

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.

8. Log out from the postgres account with 'exit'.

Before Use:

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.

Creating and Using a New Database

1. Create a new database with `createdb test'.

2. Connect to the database with `psql test'. This is a simple program that allows interaction with the database using typed commands. Type `\h' to see a list of commands.

3. Create a new database table using the SQL command below. This program requires that you end each line with a `\g'.

CREATE TABLE grades (name CHAR(10), grade CHAR(3), year INT)\g

6. Display the table (it is empty) with the command below. Note: upper and lower case values are used to make the SQL commands stand out.

SELECT * FROM grade

7.Add data with the commands below. After adding each datapoint, print out the table values. (Note: using the up and down cursor keys will allow you to recall previously entered commands.)

INSERT INTO grades (name, grade, year) VALUES (`egr 101', `D', 1997)

INSERT INTO grades (name, year, grade) VALUES (`egr 101', 1998, `B+')

INSERT INTO grades (name, grade, year) VALUES (`egr 103', `A', 1999)

INSERT INTO grades (name, grade, year) VALUES (`egr 209', `B+', 1999)

INSERT INTO grades (name, year) VALUES (`egr 226', 1999)

INSERT INTO grades (year) VALUES (2000)

8. When done use '\q' to disconnect from the database.

9. Follow the tutorials in the `/usr/share/doc/postgres/html' directory.

10. Develop a database (of your own design) that will keep customer information, and inventory levels.

Submit (individually):

1. A completed customer information database.