After you execute the SQL statements for data definition in HOTELDB_SIMPLE, the HOTEL schema contains the tables city, customer, hotel, room, and reservation. The tables were filled with data using the INSERT statements.
Each row in this table contains the following information: zip, name of the city, abbreviation of the name of the state where the city is located.
CREATE TABLE city
(zip CHAR(5) PRIMARY
KEY,
name CHAR(20) NOT NULL,
state CHAR(2) NOT
NULL)
ZIP |
NAME |
STATE |
12203 |
Albany |
NY |
60601 |
Chicago |
IL |
60615 |
Chicago |
IL |
45211 |
Cincinnati |
OH |
33575 |
Clearwater |
FL |
75243 |
Dallas |
TX |
32018 |
Daytona Beach |
FL |
33441 |
Deerfield Beach |
FL |
48226 |
Detroit |
MI |
90029 |
Hollywood |
CA |
92714 |
Irvine |
CA |
90804 |
Long Beach |
CA |
11788 |
Long Island |
NY |
90018 |
Los Angeles |
CA |
70112 |
New Orleans |
LA |
10580 |
New York |
NY |
10019 |
New York |
NY |
92262 |
Palm Springs |
CA |
97213 |
Portland |
OR |
60018 |
Rosemont |
IL |
95054 |
Santa Clara |
CA |
20903 |
Silver Spring |
MD |
20037 |
Washington |
DC |
20005 |
Washington |
DC |
20019 |
Washington |
DC |
Each row in this table contains the following customer information: a number that uniquely identifies the customer, the title used for correspondence with the customer, the customer’s first and last name, and the customer’s address consisting of the zip code, street, and house number.
CREATE TABLE
customer
(cno FIXED(4) PRIMARY
KEY,
title CHAR(7),
firstname CHAR(10),
name CHAR(10) NOT
NULL,
zip CHAR(5),
address CHAR(25) NOT NULL)
CNO |
TITLE |
FIRSTNAME |
NAME |
ZIP |
ADDRESS |
3000 |
Mrs |
Jenny |
Porter |
10580 |
1340 N.Ash Street, #3 |
3100 |
Mr |
Peter |
Brown |
48226 |
1001 34th Str., APT.3 |
3200 |
Company |
? |
Datasoft |
90018 |
486 Maple Str. |
3300 |
Mrs |
Rose |
Brian |
75243 |
500 Yellowstone Drive, #2 |
3400 |
Mrs |
Mary |
Griffith |
20005 |
3401 Elder Lane |
3500 |
Mr |
Martin |
Randolph |
60615 |
340 MAIN STREET, #7 |
3600 |
Mrs |
Sally |
Smith |
75243 |
250 Curtis Street |
3700 |
Mr |
Mike |
Jackson |
45211 |
133 BROADWAY APT. 1 |
3800 |
Mrs |
Rita |
Doe |
97213 |
2000 Humboldt Str., #6 |
3900 |
Mr |
George |
Howe |
75243 |
111 B Parkway, #23 |
4000 |
Mr |
Frank |
Miller |
95054 |
27 5th Str., 76 |
4100 |
Mrs |
Susan |
Baker |
90018 |
200 MAIN STREET, #94 |
4200 |
Mr |
Joseph |
Peters |
92714 |
700 S. Ash Str., APT.12 |
4300 |
Company |
? |
TOOLware |
20019 |
410 Mariposa Str., #10 |
4400 |
Mr |
Antony |
Jenkins |
20903 |
55 A Parkway, #15 |
Each row in this table contains the following hotel information: a number that uniquely identifies the hotel, the hotel name and address (zip code, street, and house number).
CREATE TABLE
hotel
(hno FIXED(4) PRIMARY KEY,
name CHAR(15) NOT NULL,
zip CHAR(5),
address CHAR(25) NOT NULL)
HNO |
NAME |
ZIP |
ADDRESS |
10 |
Congress |
20005 |
155 Beechwood Str. |
20 |
Long Island |
11788 |
1499 Grove Street |
30 |
Regency |
20037 |
477 17th Avenue |
40 |
Eight Avenue |
10019 |
112 8th Avenue |
50 |
Lake Michigan |
60601 |
354 OAK Terrace |
60 |
Airport |
60018 |
650 C Parkway |
70 |
Empire State |
12203 |
65 Yellostone Dr. |
80 |
Midtown |
10019 |
12 Barnard Str. |
90 |
Sunshine |
33575 |
200 Yellostone Dr. |
100 |
Beach |
32018 |
1980 34th Str. |
110 |
Atlantic |
33441 |
111 78th Street |
120 |
Long Beach |
90804 |
35 Broadway |
130 |
Star |
90029 |
13 Beechwood Place |
140 |
River Boat |
70112 |
788 MAIN STREET |
150 |
Indian Horse |
92262 |
16 MAIN STREET |
Each row in this table contains the following room information: a number that uniquely identifies the hotel where the room is located, the room type (single, double, suite), the number of vacant rooms, and the price per room.
CREATE TABLE
room
(hno FIXED(4),
type CHAR(6) PRIMARY KEY
(hno, type),
free FIXED(3,0),
price FIXED(6,2))
HNO |
TYPE |
FREE |
PRICE |
10 |
double |
45 |
200.00 |
10 |
single |
20 |
135.00 |
20 |
double |
13 |
100.00 |
20 |
single |
10 |
70.00 |
30 |
double |
15 |
80.00 |
30 |
single |
12 |
45.00 |
40 |
double |
35 |
140.00 |
40 |
single |
20 |
85.00 |
50 |
double |
230 |
180.00 |
50 |
single |
50 |
105.00 |
50 |
suite |
12 |
500.00 |
60 |
double |
39 |
200.00 |
60 |
single |
10 |
120.00 |
60 |
suite |
20 |
500.00 |
70 |
double |
11 |
180.00 |
70 |
single |
4 |
115.00 |
80 |
double |
19 |
150.00 |
80 |
single |
15 |
90.00 |
80 |
suite |
5 |
400.00 |
90 |
double |
145 |
150.00 |
90 |
single |
45 |
90.00 |
90 |
suite |
60 |
300.00 |
100 |
double |
24 |
100.00 |
100 |
single |
11 |
60.00 |
110 |
double |
10 |
130.00 |
110 |
single |
2 |
70.00 |
120 |
double |
78 |
140.00 |
120 |
single |
34 |
80.00 |
120 |
suite |
55 |
350.00 |
130 |
double |
300 |
270.00 |
130 |
single |
89 |
160.00 |
130 |
suite |
100 |
700.00 |
140 |
double |
9 |
200.00 |
140 |
single |
10 |
125.00 |
140 |
suite |
78 |
600.00 |
150 |
double |
115 |
190.00 |
150 |
single |
44 |
100.00 |
150 |
suite |
6 |
450.00 |
Each row in this table contains the following reservation information: a number that uniquely identifies the reservation, a number that uniquely identifies the customer, a number that uniquely identifies the hotel, the room type (single, double, suite), the date of arrival, and the date of departure.
CREATE TABLE
reservation
(rno FIXED(4) PRIMARY KEY,
cno FIXED(4),
hno FIXED(4),
type CHAR(6),
arrival DATE NOT NULL,
departure DATE NOT NULL)
RNO |
CNO |
HNO |
TYPE |
ARRIVAL |
DEPARTURE |
100 |
3000 |
80 |
Single |
2004-11-13 |
2004-11-15 |
110 |
3000 |
100 |
Double |
2004-12-24 |
2005-01-06 |
120 |
3200 |
50 |
Suite |
2004-11-14 |
2004-11-18 |
130 |
3900 |
110 |
Single |
2005-02-01 |
2005-02-03 |
140 |
4300 |
80 |
Double |
2005-03-14 |
2005-03-24 |
150 |
3600 |
70 |
Double |
2004-04-12 |
2004-04-30 |
160 |
4100 |
70 |
Single |
2004-04-12 |
2004-04-15 |
170 |
4400 |
150 |
Suite |
2004-09-01 |
2004-09-03 |
180 |
3100 |
120 |
Double |
2004-12-23 |
2005-01-08 |
190 |
4300 |
140 |
Double |
2004-11-14 |
2004-11-17 |