http://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:
room_reservationtable should include both fields. Once the primary keys have been added. Make appropriate changes to the insert commands to support those changes.
customer_idas 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.
payment_methodtable create an entry that has a
payment_method_idof 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.
payment_method_idas 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.
expiration_datefields in the
payment_methodtable 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.
max_occupancyto be in the range 0 to 6. Fix inserts as needed.
card_codeto be a 3 digit number (and change its type to
CHAR(3). Fix inserts as needed.
rdatefield in the
reservationtable 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.