HW07: Applying Integrity Constraints due Wed 01 Mar 13:00

\begin{purpose} You will learn to apply multiple integrity contraints on an existing database. \end{purpose}

Description

In this assignment we will continue with the hotel reservations theme. However, we will focus on only a subset of problem and you will use an pre-existing database rather than your own design. In particular, use the tables found in https://josephus.hsutx.edu/classes/db/source/hoteldb.sql. Start by running the provided script and verifying that it works correctly. The provided script will create and populate tables. However, the tables have no specified integrity constraints. You will systematically provide the constraints listed below and then fix data to follow the specified constraints.

Modify your copy of hoteldb.sql as follows:

  1. (2 pts) Add primary keys to every table. The primary key for the room_reservation table should include both fields. Once the primary keys have been added. Make appropriate changes to the insert commands to support those changes.
  2. (4 pts) In the reservation table identify customer_id as a foreign key. If a customer record is deleted or updated it should also delete/update the reservation accordingly. Fix inserts as needed. Verify that deletes/updates are behaving properly by adding a queries at the end of the document to delete customer whose id is 3 and to update customer whose id is 2 to have an id of 3.
  3. (2 pts) In the payment_method table create an entry that has a payment_method_id of zero that has a card type call “cash” with a blank card number, code, an expiration date. Then make this payment method the default method for a reservation.
  4. (3 pts) In the reservation table identify payment_method_id as a foreign key. If a payment method is deleted it should be set to its default value. Updates should cascade. Fix inserts as needed. Verify that deletes are behaving properly by adding a query at the end of the document to delete payment method 1.
  5. (1 pts) Restrict customer name (first and last) to disallow null values. Fix inserts as needed.
  6. (2 pts) Require the card_number and expiration_date fields in the payment_method table to be unique. By that I mean, the table should not allow those two values to be the same for separate rows. It would be okay if the same card number appears in a separate row as long as the expiration date is different. Fix inserts as needed.
  7. (3 pts) Require max_occupancy to be in the range 0 to 6. Fix inserts as needed.
  8. (3 pts) Require card_code to be a 3 digit number (and change its type to CHAR(3). Fix inserts as needed.
  9. (3 pts) Add a default value for the rdate field in the reservation table to be set to the current date. Add a query at the bottom of the document that inserts a new reservation but does not specify a value for the date to verify it is working.
  10. (2 pts) Add foreign key constraints to both of the fields in the room_reservation table.
  11. (1 pts) Require values for the fields max_occupancy and cost in the room table.
  12. (2 pts) In a SQL comment section describe one possible more sophisticated constraint on data that could be useful if this database were to actually be used by a hotel for managing reservations. As one example, you might want to flag if a the same credit card was being used by two different people. Come up with one more example.

Submission and Grading

Once you have completed the document upload your hoteldb.sql file into canvas. Point values for each step are identified above.

Database Systems

Course Resources

Grades and Homework

Quick Links