Now create the Room, Booking, and Guest tables using the integrity enhancement features of SQL with the following constraints:
(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.
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)));
You might also like to view...
The most widely used operating systems on larger servers is one of the many versions of ____.?
A. ?UNIX B. ?Windows XP C. ?MAC OS D. ?Windows NT
What is the logical address?
What will be an ideal response?
A __________ is an organized collection of data.
a) row b) database c) data reader d) primary key
After a proposed change is accepted or rejected, which of the following if removed from the cell?
A) The changes B) The triangle C) The tracking notation D) The cell's value