DROP SCHEMA hoteldb CASCADE; CREATE SCHEMA hoteldb; SET SEARCH_PATH TO hoteldb;

CREATE TABLE room ( room_num INTEGER, max_occupancy SMALLINT, cost DECIMAL(6,2), primary key (room_num) );

INSERT INTO room VALUES(100,4,139.99); INSERT INTO room VALUES(101,2,89.99); INSERT INTO room VALUES(102,2,89.99); INSERT INTO room VALUES(103,2,89.99); INSERT INTO room VALUES(104,2,89.99); INSERT INTO room VALUES(105,3,99.99); INSERT INTO room VALUES(106,20,139.99); INSERT INTO room VALUES(200,4,139.99); INSERT INTO room VALUES(201,2,89.99); INSERT INTO room VALUES(202,2,89.99); INSERT INTO room VALUES(203,2,89.99); INSERT INTO room VALUES(204,4,62.99); INSERT INTO room VALUES(205,20,139.99);

CREATE TABLE customer ( customer_id SERIAL, lastname VARCHAR(30), firstname VARCHAR(30), primary key (customer_id) ); INSERT INTO customer VALUES(default,'Smith','Fred'); INSERT INTO customer VALUES(default,'Jones','Mary'); INSERT INTO customer VALUES(default,'Andy','Williams'); INSERT INTO customer VALUES(default,'Amy','Michaels'); INSERT INTO customer VALUES(default,'Smith','Mike'); INSERT INTO customer VALUES(default,'Doe','james'); INSERT INTO customer VALUES(default,'kamarade','john'); INSERT INTO customer VALUES(default,'fred','James');

CREATE TABLE payment_method ( payment_method_id SERIAL, card_type VARCHAR(30), card_number VARCHAR(30), expiration_date DATE, card_code VARCHAR(10), primary key (payment_method_id) ); INSERT INTO payment_method VALUES(default,'Mastercard','1234567890','2018-12-31','123'); INSERT INTO payment_method VALUES(default,'Mastercard','2345678901','2019-03-31','234'); INSERT INTO payment_method VALUES(default,'Visa','3456789012','2017-06-30','345'); INSERT INTO payment_method VALUES(default,'American Express','4567890123','2020-01-31','456'); INSERT INTO payment_method VALUES(default,'Mastercard','5678901234','2020-02-29','143'); INSERT INTO payment_method VALUES(default,'Mastercard','1234567890','2018-12-31','345'); INSERT INTO payment_method VALUES(default,'Mastercard','1234567890','2020-03-31','711'); INSERT INTO payment_method VALUES(default,'Visa','987654321','2022-07-13','911'); INSERT INTO payment_method VALUES(0,'Cash');

CREATE TABLE reservation ( reservation_id SERIAL, rdate DATE, customer_id INTEGER, payment_method_id SERIAL, primary key (reservation_id), FOREIGN KEY (customer_id) REFERENCES customer(customer_id) on DELETE set DEFAULT ON UPDATE cascade, FOREIGN KEY (payment_method_id) REFERENCES payment_method(payment_method_id) ON DELETE SET DEFAULT ON UPDATE cascade ); INSERT INTO reservation VALUES(default,'2017-10-10',default,default); INSERT INTO reservation VALUES(default,'2017-10-10',default,default); INSERT INTO reservation VALUES(default,'2017-10-11',default,default); INSERT INTO reservation VALUES(default,'2017-10-12',default,default); INSERT INTO reservation VALUES(default,'2017-10-12',default,default); INSERT INTO reservation VALUES(default,'2017-10-12',default,default);

CREATE TABLE room_reservation ( reservation_id INTEGER, room_num INTEGER, primary key (reservation_id) );

INSERT INTO room_reservation VALUES(1,100); INSERT INTO room_reservation VALUES(2,101); INSERT INTO room_reservation VALUES(3,102); INSERT INTO room_reservation VALUES(4,105);

INSERT INTO room_reservation VALUES(5,200); INSERT INTO room_reservation VALUES(6,201); INSERT INTO room_reservation VALUES(7,202); INSERT INTO room_reservation VALUES(8,5);

DELETE FROM customer WHERE customer_id=3;

UPDATE customer SET customer_id=3 WHERE customer_id=2

DELETE FROM payment_method WHERE payment_method_id=0; --PRIMARY KEY (artist_id,title), --FOREIGN KEY (artist_id) REFERENCES artist(id) ON DELETE SET DEFAULT --CASCADE -- on DELETE set DEFAULT ON UPDATE cascade

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

I got stuck on instruction #4