— Question (6 marks)
— Copyright (c) 2020 Diane Horton
— Below is a schema about bookings at guesthouses:
DROP SCHEMA IF EXISTS BedAndBreakfast CASCADE;
CREATE SCHEMA BedAndBreakfast;
SET SEARCH_PATH TO BedAndBreakfast;
— A guest who can make bookings.
CREATE TABLE Guest(
— The guest’s ID:
guestID INT PRIMARY KEY,
— The guest’s name:
name TEXT,
— The guest’s email address:
email TEXT
);
— A guesthouse that can be booked.
CREATE TABLE Guesthouse(
— The ID for this guesthouse:
guesthouseID INT PRIMARY KEY,
— The guesthouse’s location:
location TEXT,
— The number of rooms in this guesthouse:
numRooms INT
);
— This guest made a booking for this guesthouse.
CREATE TABLE Booking(
— The ID for this booking:
booking INT PRIMARY KEY,
— The guesthouse that was booked:
guesthouse INT REFERENCES Guesthouse(guesthouseID),
— The guest that booked it:
guest INT REFERENCES Guest(guestID),
— When they will arrive:
checkIn TIMESTAMP NOT NULL,
— When they will leave:
checkOut TIMESTAMP NOT NULL
);
— A charge for room service.
CREATE TABLE RoomService(
— The booking associated with this charge:
booking INT REFERENCES Booking(booking),
— When the room service charge was made:
moment TIMESTAMP,
— The amount of the room service charge:
amount FLOAT,
PRIMARY KEY (booking, moment)
);
— This person is an additional guest for this booking.
CREATE TABLE AdditionalGuest(
— The booking that has this additional guest:
booking INT REFERENCES Booking(booking),
— Who this additional guest is:
person INT REFERENCES Guest(guestID),
PRIMARY KEY (booking, person)
);
— 1.
— Extend the schema to support the following:
— 1. A guesthouse may offer free breakfast, lunch, and/or dinner.
— 2. The credit card number of the guest who made a booking must be
— recorded. Note that a guest may use different credit cards
— for different bookings.
— 3. The guest who made a booking can leave a rating for that booking
— of between 0 and 5 stars inclusive.
—
— You may modify existing tables or add new ones. You will be graded
— on being able to represent the information described, and also for
— good design.
— 2.
— Modify the above DDL to enforce each of the rules below. If a constraint
— is already enforced, do nothing for that constraint.
— 1. For each booking, the check out time should be later than the check in
— time.
— 2. If a guesthouse is deleted, all of its bookings must be deleted as well.
— 3. The default number of rooms in a guesthouse is 5.