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.