Tables in the Database
Tables in the example database are described in this section.
The table descriptions are set up as follows:
- The first column lists the table name and the column names.
- The second column shows which columns which make up the primary key (*).
- The third column shows the columns that are foreign keys (f). Refer to the CREATE statements later in this section for a full definition of foreign keys in the database.
- The fourth column shows the column data type. CHAR(n) is a character string of length n bytes. INT(p) specifies an integer of up to p digits long. DEC(p,s) specifies numbers of up to p digits long, of which s follow the decimal point. DATE is a date in the Gregorian calendar in the form YYYY-MM-DD. TIME(s) is a time on an unspecified day, in the form HH:MM:SS, with s digits following the decimal point in the seconds value.
- The fifth column explains the column contents.
Table Descriptions
Hotel Table
Column Name
|
Primary Key = *
|
Foreign Key
|
DataType
|
What is it?
|
| HOTELCODE |
* |
|
CHAR(4) |
Hotel identity code |
| NAME |
|
|
CHAR(15) |
Hotel name |
| CITY |
|
|
CHAR(15) |
Location |
ROOMSTATUS Table
Column Name
|
Primary Key = *
|
Foreign Key
|
DataType
|
What is it?
|
| STATUS |
* |
|
CHAR(10) |
Room status |
ROOMTYPES Table
Column Name
|
Primary Key = *
|
Foreign Key
|
DataType
|
What is it?
|
| ROOMTYPE |
* |
|
CHAR(6) |
Room type |
| DESCRIPTION |
|
|
VARCHAR(40) |
Room description |
ROOMS Table
Column Name
|
Primary Key = *
|
Foreign Key
|
DataType
|
What is it?
|
| ROOMNO |
* |
|
CHAR(7) |
Room number |
| HOTELCODE |
|
f |
CHAR(4) |
Hotel identity code |
| ROOMTYPE |
|
f |
CHAR(6) |
Room type |
| STATUS |
|
f |
CHAR(10) |
Room status |
ROOM_PRICES Table
Column Name
|
Primary Key = *
|
Foreign Key
|
DataType
|
What is it?
|
| HOTELCODE |
* |
f |
CHAR(4) |
Hotel identity code |
| ROOMTYPE |
* |
f |
CHAR(6) |
Room type |
| FROM_DATE |
* |
|
DATE |
Date when price becomes valid |
| TO_DATE |
|
|
DATE |
Date until which price is valid |
| PRICE |
|
|
INT(4) |
Cost of room per day |
Charges Table
Column Name
|
Primary Key = *
|
Foreign Key
|
DataType
|
What is it?
|
| CHARGE_CODE |
* |
|
CHAR(3) |
Charge code |
| DESCRIPTION |
|
|
CHAR(25) |
Cost description |
| CHARGE_PRICE |
|
|
INT(4) |
Price charged for room |
Book_Guest Table
Column Name
|
Primary Key = *
|
Foreign Key
|
DataType
|
What is it?
|
| RESERVATION |
* |
|
INT(5) |
Guest reference number |
| BOOKING_DATE |
|
|
DATE |
Date of booking |
| HOTELCODE |
|
f |
CHAR(4) |
Hotel identity code |
| ROOMTYPE |
|
f |
CHAR(6) |
Room type |
| COMPANY |
|
|
VARCHAR(100) |
Name of company reserving room |
| TELEPHONE |
|
|
CHAR(15) |
Telephone number of above |
| RESERVED_FNAME |
|
|
CHAR(25) |
First name of expected guest |
| RESERVED_LNAME |
|
|
CHAR(25) |
Last name of expected guest |
| ARRIVE |
|
|
DATE |
Expected check-in date |
| DEPART |
|
|
DATE |
Expected check-out date |
| GUEST_FNAME |
|
|
CHAR(25) |
Guest first name |
| GUEST_LNAME |
|
|
CHAR(25) |
Guest last name |
| ADDRESS |
|
|
VARCHAR(50) |
Guest address |
| CHECKIN |
|
|
DATE |
Actual check-in date |
| CHECKOUT |
|
|
DATE |
Actual check-out date |
| ROOMNO |
|
f |
CHAR(7) |
Room number |
| PAYMENT |
|
|
CHAR(10) |
Payment type |
Bill Table
Column Name
|
Primary Key = *
|
Foreign Key
|
DataType
|
What is it?
|
| RESERVATION |
|
f |
INT(5) |
Guest reference number |
| ON_DATE |
|
|
TIMESTAMP(0) |
Billing date and time |
| CHARGE_CODE |
|
f |
CHAR(3) |
Charge code |
| COST |
|
|
INT(4) |
Cost of stay |
WAKE_UP Table
Column Name
|
Primary Key = *
|
Foreign Key
|
DataType
|
What is it?
|
| ROOMNO |
* |
f |
CHAR(7) |
Room number |
| WAKE_DATE |
* |
|
DATE |
Wake up date |
| WAKE_TIME |
|
|
TIME |
Wake up time |
EXCHANGE_RATE Table
Column Name
|
Primary Key = *
|
Foreign Key
|
DataType
|
What is it?
|
| CURRENCY |
* |
|
CHAR(3) |
Currency |
| RATE |
|
|
DEC(6,3) |
Exchange rate |
