Do the following exercises:

1. Exercise 5.14

   • Implement this schema using the Oracle SQL DDL. Please add an item name in the Item table.
   • Include appropriate constraints and, in particular, specify what should be done on deletes (e.g., cascade, ...). Justify your implementation in the code comments.
   • Include 3-5 sample records in each table. You’ll need to have sufficient records to drive the queries specified below, so it’s probably wise to look through the queries so that you will have non-empty and non-NULL results for each of them.

2. Exercise 5.20.a & c

   • Consider only this modified version of the text exercise - What recommendations would you have for CIT if they were considering replacing surrogate student ID numbers with a more natural key? Either suggest a new form of key or try to convince them that surrogate keys are acceptable.

3. Write the SQL commands to retrieve the following from the customer-order database you built above.

   1. all the order dates and amounts for orders made by a customer with a particular name (one that exists in your database), ordered chronologically by date
   2. all the customer ID numbers for customers who have at least one order in the database
   3. the customer IDs and names of the people who have ordered an item with a particular name (one that exists in your database)

4. Complete lab exercises 2.3 & 2.4.

Store your solutions to the questions specified above in homework02.sql, either as SQL commands or as textual comments.

Checking in

We will grade your work according to the following criteria:

• 25% — Exercise 2.1
• 25% — Exercise 2.2
• 25% — Exercise 2.3
• 25% — Exercise 2.4