Computer Science 455

Fourth Exercise Set

Due: Monday, March 8.

The file [csmt.matthews.cs455]customer.dat contains customer records (cno, cname, city).

The file [csmt.matthews.cs455]orders.dat contains information on customer orders (cno, ordno, date).

The file [csmt.matthews.cs455]lineitems.dat contains information on individual customer orders: (ordno, lineno, catno, qty).

Finally, the file [csmt.matthews.cs455]catalog.dat contains information on the catalog: (catno, cname, cost).

1. Write a SQL command file which will create four tables for these files, with appropriate primary keys and foreign keys specified in the table definition. Call this command file SQL02.SQL
2. For each table, write a .CTL file which can be used by the database loader. Be careful to name them by file names, for example, customer.ctl (otherwise my grading program will not read them successfully).
3. Write a second SQL command file (SQL03.SQL) which will print out entries in each table, insert a new customer (and verify that the customer is in the file), and delete a customer with orders, verifying that the customer and all orders have been deleted. The command file should then delete all the tables created in SQL02.SQL.
4. Finally, write a command file called SCRIPT01.COM which will do all this in sequence: run SQL02.SQL, run the database loader utility to load all four tables, and run SQL03.SQL.

Be sure that all command files have appropriate documentation, and that all are correctly named. If you have any questions, please let me know. Many thanks!