F21DF1 Database and Information Systems

Assessed Coursework

A Relational Database for Sir Edward Kelly & Co

This is the first of two pieces of coursework for this module. In it you will construct, query and modify your own database to record part of the data in the Sir Edward Kelly case study. You will do so using the relational model, SQL and MySQL DBMS skills developed previously.

You should hand in a clearly-numbered report containing the SQL statements, query results, and discussion detailed in parts 2 to 6 below. A plain text report is perfectly acceptable. The report is due on Monday of week 8.

1. Log in to your MySQL database and use the SQL statements from http://www.macs.hw.ac.uk/~trinder/DbInfSystems/ to create and populate the PurchaseContract, ContractItem and ShippingSheet tables for the Sir Edward Kelly database.

   The easiest way to prepare large SQL statements is to write them in a text editor (e.g. emacs), and then paste them into the MySQL window. You can paste a single command, e.g. DROP TABLE PurchaseContract; or a sequence of commands. In a similar way, the results of a query can be cut from the MySQL window and pasted into your editor.

2. Create a ShippingItem table using MySQL. In your report give the SQL commands to create the table, using suitable attribute types and include primary key, foreign key and other constraints.

   (4 marks)

3. Insert into the ShippingItem table the example data from the shipping sheet in the case study found in section 1.2 of the lecture notes. Your report should list the SQL commands to insert the data, and a listing of the table contents.

   (2 marks)

4. Write SQL queries to answer the following questions about the data. For each query give the SQL, together with the table produced by the query.
   1. At what dock and berth is the shipment on shipping sheet 1193?
   2. What Bills of Lading are due in May 2000?
   3. What is the total number of packs, the total number of pieces and the total volume in each shipment?

   4. What different types of wood have been shipped on contract R28?
   5. 1. Which Bills of Lading on contract R28 have not yet been shipped?
      2. For the Bills of Lading that have been shipped on contract R28, list the contracted volume together with the shipped volume.

   (14 marks)

5. Starting with the ContractItem table containing the data in the case study handout, give the SQL statements to perform the following operations, together with the contents of the table after each operation.
   1. List the contents of the ContractItem table.
   2. Delete Bill of Lading 533 from the ContractItem table.
   3. Update Bills of Lading 531 and 532 to be due in June 2000.
   4. Delete all ContractItems for contract R28.

   (7 marks)

6. Give an SQL statement to delete contract R28 from the PurchaseContract table.
   1. Describe what happens when this statement is executed.
   2. Using correct technical terms discuss whether this outcome is desirable. You may illustrate your answer by listing parts of the database.

   (3 marks)

   Total: 30 marks