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