The following tables form part of a database held in a relational DBMS:

Hotel (hotelNo, hotelName, city)

Room (roomNo, hotelNo, roomType, price)

Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo)

Guest (guestNo, guestName, guestAddress, city, returningGuest)

where Hotel contains hotel details, Room contains room details for each hotel, Booking contains details of bookings, and Guest contains guest details.

1. Create the following domains:

a. (5 points) Create Domain for hotelNo (It must have 3 characters).

b. (5 points) Create Domain for roomNo (It must have 3 digits and in the range 101 -999).

c. (5 points) Create Domain for roomType (It must be one of the following: Q, DQ, K, DT, SSQ, SDQ, SK).

d. (5 points) Create Domain for guestNo (It must have 4 characters).

e. (5 points) Create Domain for returningGuest (It must be Y or N).

2. Create the following tables using the integrity enhancement features of SQL with the following constraints. Use the domains that you created in question 1.

a. (5 points) Create the Hotel table using the integrity enhancement features of SQL. (Hint: Set primary key, foreign key if any, hotelName cannot be null.)

b. (10 points) Create the Room table using the integrity enhancement features of SQL. (Hint: Set primary key, foreign key if any, default roomType = Q, default price = $95.)

c. (10 points) Create the Booking table using the integrity enhancement features of SQL. (Hint: Set primary key, foreign key if any, guestNo cannot be null, pick another candidate key and set it to be unique.)

d. (10 points) Create the Guest table using the integrity enhancement features of SQL. (Hint: Set primary key, foreign key if any, guestName, city cannot be null.)

DROP SCHEMA IF EXISTS HotelSchema;

CREATE SCHEMA HotelSchema;

USE HotelSchema;

CREATE TABLE Hotel(hotelNo VARCHAR(3), hotelName VARCHAR(20), city VARCHAR(20)); CREATE TABLE Guest (guestNo VARCHAR(4), guestName VARCHAR(30), guestAddress VARCHAR(50), city VARCHAR(20)); CREATE TABLE Room (roomNo INT(3), hotelNo VARCHAR(3), roomtype VARCHAR(25), price FLOAT); CREATE TABLE Booking (hotelNo VARCHAR(3), guestNo VARCHAR(4), dateFrom date, dateTo date, roomNo INT(3));

INSERT INTO Hotel VALUES('H01', 'Hilton', 'Oklahoma City'); INSERT INTO Hotel VALUES('H02', 'Hilton Garden Inn', 'Oklahoma City'); INSERT INTO Hotel VALUES('H03', 'Mariott', 'Oklahoma City'); INSERT INTO Hotel VALUES('H04', 'Comfort Suites', 'Oklahoma City'); INSERT INTO Hotel VALUES('H05', 'Sheraton', 'New York City'); INSERT INTO Hotel VALUES('H06', 'Hilton', 'San Jose'); INSERT INTO Hotel VALUES('H07', 'Holiday Inn', 'Seattle'); INSERT INTO Hotel VALUES('H08', 'Embassy Suites', 'Dallas'); INSERT INTO Hotel VALUES('H09', 'Mariott', 'Dallas'); INSERT INTO Hotel VALUES('H10', 'Hyatt', 'Oklahoma City');

/*SELECT * FROM Hotel;*/

INSERT INTO Guest VALUES('G001', 'Jane Doe', '1506 Chambers St', 'Oklahoma City'); INSERT INTO Guest VALUES('G002', 'Harry Patel', '23 Drury Ln', 'Oklahoma City'); INSERT INTO Guest VALUES('G003', 'Daniel Ross', '450 James St', 'Oklahoma City'); INSERT INTO Guest VALUES('G004', 'Helen Bush', '450 James St', 'St. Louis'); INSERT INTO Guest VALUES('G005', 'Susan Chase', '1301 4th St', 'New York City'); INSERT INTO Guest VALUES('G006', 'Samantha Drew', '1499 Fioli Loop', 'San Jose'); INSERT INTO Guest VALUES('G007', 'Benn Franklin', '99 23rd St', 'Seattle'); INSERT INTO Guest VALUES('G008', 'Robert Grove', '24 Baycharter Blvd', 'Dallas'); INSERT INTO Guest VALUES('G009', 'Mary Harker', '340 Jenkins Cr', 'Dallas'); INSERT INTO Guest VALUES('G010', 'Yana Krum', '280 Central Pkwy', 'Oklahoma City');

/*SELECT * FROM Guest;*/

INSERT INTO Room VALUES(701, 'H04', 'King', 250); INSERT INTO Room VALUES(202, 'H03', 'King', 200); INSERT INTO Room VALUES(403, 'H04', 'Double Queen', 175); INSERT INTO Room VALUES(205, 'H10', 'Double Queen', 159); INSERT INTO Room VALUES(107, 'H02', 'Double Twin', 99); INSERT INTO Room VALUES(107, 'H06', 'Single Queen', 169); INSERT INTO Room VALUES(108, 'H04', 'Double Queen', 79); INSERT INTO Room VALUES(209, 'H03', 'Suite King', 199); INSERT INTO Room VALUES(710, 'H01', 'Suite Single Queen', 159); INSERT INTO Room VALUES(710, 'H04', 'Suite Single Queen', 89); INSERT INTO Room VALUES(402, 'H04', 'King', 129); INSERT INTO Room VALUES(605, 'H04', 'Suite Double Queen', 89);

/*SELECT * FROM Room;*/

INSERT INTO Booking VALUES('H04', 'G006', 20160404, 20160406, 701); INSERT INTO Booking VALUES('H03', 'G001', 20160516, 20160526, 202); INSERT INTO Booking VALUES('H04', 'G003', 20160113, 20160115, 403); INSERT INTO Booking VALUES('H10', 'G005', 20160820, 20160828, 205); INSERT INTO Booking VALUES('H02', 'G005', 20160401, 20160406, 107); INSERT INTO Booking VALUES('H06', 'G003', 20160823, 20160825, 107); INSERT INTO Booking VALUES('H04', 'G003', 20160404, 20160406, 108); INSERT INTO Booking VALUES('H03', 'G002', 20160516, 20160526, 209); INSERT INTO Booking VALUES('H01', 'G010', 20160810, 20160815, 710); INSERT INTO Booking VALUES('H04', 'G010', 20160404, 20160504, 710);

/*SELECT * FROM Booking;*/