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, ROOM and RESERVATION. The tables have been filled with demo data by the INSERT statements.

CITY (City Table)

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

CREATE TABLE city

(zip CHAR(5) PRIMARY KEY,

name CHAR(30) 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`	`Seattle`	`WA`
`20005`	`Seattle`	`WA`
`20019`	`Seattle`	`WA`

CUSTOMER (Customer Table)

Every 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.

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)

`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)

Every 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.

CREATE TABLE hotel

(hno FIXED(4) PRIMARY KEY,

name CHAR(50) NOT NULL,

zip CHAR(5),

address CHAR(40) NOT NULL)

`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)

Every 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.

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`

RESERVATION (Reservation Table)

Every 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.

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`

Note Note

If you load the complete set of demo data with the Installation Manager or the Database Studio, the tables are created with much more extensive definitions and a larger number of data records and additional objects (such as views, indexes, and so on). You can also use this demo data to test the SQL statements. However, the result set specified for an example in this SQL tutorial may differ from the result set you receive for the complete set of demo data.

End of the note.

More Information

DEMO Data for the SQL Tutorial

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