Reservation
The
reservation table contains a list of rooms with the following information:RNO |
CNO |
HNO |
ROOMTYPE |
ARRIVAL |
DEPARTURE |
100 |
3000 |
80 |
single |
11/13/1998 |
11/15/1998 |
110 |
3000 |
100 |
double |
12/24/1998 |
01/06/1999 |
120 |
3200 |
50 |
suite |
11/14/1998 |
11/18/1998 |
130 |
3900 |
110 |
single |
02/01/1999 |
02/03/1999 |
140 |
4300 |
80 |
double |
04/12/1998 |
04/30/1998 |
150 |
3600 |
70 |
double |
03/14/1999 |
03/24/1999 |
160 |
4100 |
70 |
single |
04/12/1998 |
04/05/1998 |
170 |
4400 |
150 |
suite |
09/01/1998 |
09/03/1998 |
180 |
3100 |
120 |
double |
12/23/1998 |
01/08/1999 |
190 |
4300 |
140 |
double |
11/14/1998 |
11/17/1998 |
A logical link between the
customer, hotel, and room tables is established via the reservation table.SQL statement for creating the table structure
CREATE TABLE reservation
(rno FIXED(4) KEY CONSTRAINT rno BETWEEN 1 AND 9999,
cno FIXED(4) CONSTRAINT cno BETWEEN 1 AND 9999,
hno FIXED(4) CONSTRAINT hno BETWEEN 1 AND 9999,
roomtype CHAR(6) CONSTRAINT roomtype IN ('single', 'double', 'SUITE'),
arrival DATE NOT NULL,
departure DATE CONSTRAINT departure > arrival)