Laboratory Exercise 5

Database Modification (Command-line)

CMP 420: Database Systems
Lehman College, City University of New York
13 March 2003

Unix Commands

Each lab begins with several Unix commands that will be useful for completing today's lab and future labs and assignments. Try the following commands:

```
ls -la
```
This command gives a listing of all the files in the current directory preceded by the permissions on the files. For example:
```
stjohn% ls -la
total 32
drwxr-xr-x   3 stjohn  staff    102 Feb  6 09:29 ./
drwxr-xr-x  34 stjohn  staff   1156 Mar 10 20:18 ../
-rw-r--r--   1 stjohn  staff  12558 Mar  3 18:38 index.html
```
Note that the first column indicates whether or not its a directory (thus the d), the next three letters indicate the permissions for the owner of the files, the next indicate the permissions for other users in the owner's group, and the last three indicate the permissions for all others. So, the permissions on index.html say that it's not a directory, the owner (stjohn) has read and write permission, while the group and others have only read permission. The rest of the line indicates the number of links to the file, the owner, the group, the size of the file, the last day and time modified, followed by the file's name.
```
chmod
```
The chmod command changes the permissions on files. There are many options to this command. The easiest way to specify a change in permissions is by listing who ('u' for user, 'g' for group, and 'o' for others), whether the permission is to be added ('+') or taken away ('-'), and which permission you are changing ('r' for read, 'w' for write, and 'x' for execute). For example, to remove write permission for group and others to the file index.html, you would type:
```
chmod go-w index.html
```
To make a file executable, for example, the perl file used below mk_examples.pl:
```
chmod u+x mk_examples.pl
```

Creating Databases

For today's lab, we're going to create a database to hold information about students and courses. This lab will have three tables: Students, Courses, and Enrollment. The Students table will keep track of students' ID number, name, address, and phone number. The Courses table will keep track of the courses' registration code, department, and course number. The Enrollment table will keep track of which students are enrolled in which courses. Since the
IDNumber is the key for Students and registrationCode is the key for Courses, the Enrollment table will only need to store those two attributes to tell which students are enrolled in which courses.

First, create a database at the command line for today's lab (see Lab 2 for details).

Next, create the three tables in psql using the CREATE TABLE command. Details of this command can be found in Chapter 6 of the textbook or in the PostgreSQL manual (see the list of commands). The three tables should be:

Students(IDNumber,name,address,phone)
Courses(registrationCode,department,number)
Enrollment(IDNumber,registrationCode)

Check using the "\d" command to make sure all tables were created correctly.

Making Data for your Database

One way to enter the information into your database is to type in every line by hand. Another way is to read in the database stored as a file. In Lab 2, we used a sample database from PostgreSQL Notes by Richard Huxton. For this lab, you need to modify his program to create data for today's database. First, download his perl program, and try running it (see directions on his webpage). It will create a data file much like that used for Lab 2.

Several quick hints for reading and modifying perl:

Variables don't have to be declared. A variable that starts its name with a "$" is assumed to be a scalar or single value (like $i or $name). A variable that starts its name with a "@" is assumed to be a list or an array.
All loops, decisions, and arithmetic that you learned in java will (likely) work in perl.
Methods or functions are at the end of the program and begin with the work sub.

Next, modify the perl program to produce:

100 students, based on 10 different first names, 10 different middle initials, and 10 different last names (see the $num_companies scalar variable and the lists used to hold first, middle, and last parts of names: @co_name1, @co_name2, @co_name3). For the IDNumber, use the index $i of the loop (so, the first student created will have IDNumber = 1, and the next will have IDNumber = 2, etc)
30 courses, based on 8 different department names and 10 different course numbers. Again, you can use the loop variable $i to create a unique registrationCode.
Modify the perl code that creates the orders table to randomly choose IDNumbers (between 1 and 100) and registrationCodes (between 1 and 30) and inserts 150 such pairs into the Enrollment table.
You can remove the perl code that creates the diary table

Save the output of your perl program to a file. You might have to modify it several times before it matches your database schema. Now, load it into psql and try some simple queries to make sure that it works.

Database Modification

Using the notes from class and the list of commands for PostgreSQL, find the commands that:

delete a column from a table,
add a column to a table, and
change a column on a table.

Final Comments

Before leaving the lab, you need to log out and shut down your computer. First, you should quit all applications. Then, go to the K menu in the bottom left hand corner and select Log Out. Choose the Shut Down option, and shut off the monitor when you're done.

Remember to pick up any files you sent to the printer and to take any personal belongings. It is difficult to get back into the lab later (since it's locked when not in use), so, it's worth making sure you haven't forgotten anything.