Explanation of the Tables in the HOTEL Demo Schema 

After you execute the SQL statements for the HOTEL demo schema, the HOTEL demo schema contains the tables CITY, CUSTOMER, HOTEL, and RESERVATION. The tables have been filled with demo data by the INSERT statements.

CITY (City Table)

Each table row contains the following city information: zip code, city name, and the abbreviation for the name of the state in which the city is located.

Definition

CREATE TABLE city
(zip       CHAR(5)  PRIMARY KEY,
 name      CHAR(30) NOT NULL,
 state     CHAR(2)  NOT NULL)

Data

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	Seattle	WA
20005	Seattle	WA
20019	Seattle	WA

CUSTOMER (Customer Table)

Each table row contains the following customer information: a number for uniquely identifying the customer, the title to be used in correspondence, the customer’s first and last names, and the customer address, consisting of the zip code, street, and house number.

Definition

CREATE TABLE customer
(cno        FIXED(4) PRIMARY KEY,
 title      CHAR(7),
 firstname  CHAR(20),
 name       CHAR(40) NOT NULL,
 zip        CHAR(5),
 address    CHAR(40) NOT NULL)

Data

CNO	TITLE	FIRSTNAME	NAME	ZIP	ADDRESS
3000	Mrs	Jenny	Porter	10580	1340 N. Ash Street, #3
3100	Mr	Peter	Brown	48226	1001 34th St., APT.3
3200	Company	?	Datasoft	90018	486 Maple St.
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 St., #6
3900	Mr	George	Howe	75243	111 B Parkway, #23
4000	Mr	Frank	Miller	95054	27 5th St., 76
4100	Mrs	Susan	Baker	90018	200 MAIN STREET, #94
4200	Mr	Joseph	Peters	92714	700 S. Ash St., APT.12
4300	Company	?	TOOLware	20019	410 Mariposa St., #10
4400	Mr	Antony	Jenkins	20903	55 A Parkway, #15

HOTEL (Hotel Table)

Each table row contains the following hotel information: a number for uniquely identifying the hotel, the hotel name and hotel address, consisting of the zip code, street and house number.

Definition

CREATE TABLE hotel
(hno     FIXED(4) PRIMARY KEY,
 name    CHAR(50) NOT NULL,
 zip     CHAR(5),
 address CHAR(40) NOT NULL)

Data

HNO	NAME	ZIP	ADDRESS
10	Congress	20005	155 Beechwood St.
20	Long Island	11788	1499 Grove Street
30	Regency	20037	477 17th Avenue
40	Eighth Avenue	10019	112 8th Avenue
50	Lake Michigan	60601	354 OAK Terrace
60	Airport	60018	650 C Parkway
70	Empire State	12203	65 Yellowstone Dr.
80	Midtown	10019	12 Barnard St.
90	Sunshine	33575	200 Yellowstone Dr.
100	Beach	32018	1980 34th St.
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

ROOM (Room Table)

Each table row contains the following room information: a number for uniquely identifying the hotel in which the room is located, information about the room type (single room, double room, or suite), the number of rooms free and the price per room.

Definition

CREATE TABLE room
(hno       FIXED(4),
 type      CHAR(6)  PRIMARY KEY (hno, type),
 free      FIXED(3,0),
 price     FIXED(6,2))

Data

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

RESERVATION (Reservation Table)

Each table row contains the following reservation information: a number for uniquely identifying the reservation, a number for uniquely identifying the customer, a number for uniquely identifying the hotel, information about the room type (single room, double room, suite), and date of arrival and departure.

Definition

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)

Data

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

If you load all the demo data with the Installation Manager or the Database Manager, the tables are created with much more extensive definitions and contents and additional objects (e.g. views, indexes, etc.) are generated.

You can use this demo data as well. However, the result set specified for an example statement in this SQL tutorial may differ from the result set you receive for the extended demo data.

See also:

Concepts for the Database System, Demo Database, Objects in the HOTEL Schema