Now create the Room, Booking, and Guest tables using the integrity enhancement features of SQL with the following constraints:

Question

(a) Type must be one of Single, Double, or Family.
(b) Price must be between £10 and £100.
(c) roomNo must be between 1 and 100.
(d) dateFrom and dateTo must be greater than today’s date.
(e) The same room cannot be double booked.
(f) The same guest cannot have overlapping bookings.

Answer 1

CREATE DOMAIN RoomType AS CHAR(1)
CHECK(VALUE IN (‘S’, ‘F’, ‘D’));
CREATE DOMAIN HotelNumbers AS HotelNumber
CHECK(VALUE IN (SELECT hotelNo FROM Hotel));
CREATE DOMAIN RoomPrice AS DECIMAL(5, 2)
CHECK(VALUE BETWEEN 10 AND 100);
CREATE DOMAIN RoomNumber AS VARCHAR(4)
CHECK(VALUE BETWEEN ‘1’ AND ‘100’);
CREATE TABLE Room(
roomNo RoomNumber NOT NULL,
hotelNo HotelNumbers NOT NULL,
type RoomType NOT NULL DEFAULT ‘S’
price RoomPrice NOT NULL,
PRIMARY KEY (roomNo, hotelNo),
FOREIGN KEY (hotelNo) REFERENCES Hotel
ON DELETE CASCADE ON UPDATE CASCADE);

CREATE DOMAIN GuestNumber AS CHAR(4);
CREATE TABLE Guest(
guestNo GuestNumber NOT NULL,
guestName VARCHAR(20) NOT NULL,
guestAddress VARCHAR(50) NOT NULL);
CREATE DOMAIN GuestNumbers AS GuestNumber
CHECK(VALUE IN (SELECT guestNo FROM Guest));
CREATE DOMAIN BookingDate AS DATETIME
CHECK(VALUE > CURRENT_DATE);
CREATE TABLE Booking(
hotelNo HotelNumbers NOT NULL,
guestNo GuestNumbers NOT NULL,
dateFrom BookingDate NOT NULL,
dateTo BookingDate NULL,
roomNo RoomNumber NOT NULL,
PRIMARY KEY (hotelNo, guestNo, dateFrom),
FOREIGN KEY (hotelNo) REFERENCES Hotel
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (guestNo) REFERENCES Guest
ON DELETE NO ACTION ON UPDATE CASCADE,
FOREIGN KEY (hotelNo, roomNo) REFERENCES Room
ON DELETE NO ACTION ON UPDATE CASCADE,
CONSTRAINT RoomBooked
CHECK (NOT EXISTS ( SELECT *
FROM Booking b
WHERE b.dateTo > Booking.dateFrom AND
b.dateFrom < Booking.dateTo AND
b.roomNo = Booking.roomNo AND
b.hotelNo = Booking.hotelNo)),

CONSTRAINT GuestBooked
CHECK (NOT EXISTS ( SELECT *
FROM Booking b
WHERE b.dateTo > Booking.dateFrom AND
b.dateFrom < Booking.dateTo AND
b.guestNo = Booking.guestNo)));