Objects in the HOTEL Schema

After you have loaded the demo data into your demo database, the tables CITY, CUSTOMER, HOTEL, ROOM, RESERVATION, EMPLOYEE and other database objects are located in the HOTEL schema. The database administrator MONA is the owner of the HOTEL schema and its objects. The tables are filled with demo data.

CITY Table

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

Syntax Syntax

CREATE TABLE city
(zip   CHAR(5)  PRIMARY KEY CONSTRAINT zip_cons CHECK
                SUBSTR(zip,1,1) BETWEEN '0' AND '9' AND
                SUBSTR(zip,2,1) BETWEEN '0' AND '9' AND
                SUBSTR(zip,3,1) BETWEEN '0' AND '9' AND
                SUBSTR(zip,4,1) BETWEEN '0' AND '9' AND
                SUBSTR(zip,5,1) BETWEEN '0' AND '9',
 name  CHAR(30) NOT NULL,
 state CHAR(2)  NOT NULL)

End of the code.

The zip column is the primary key column. Specifying the zip_cons constraint ensures that only characters between 0 and 9 can be used as zip codes. The city name name and state abbreviation state must not contain any NULL values.

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.

Syntax Syntax

CREATE TABLE customer
(cno       FIXED(4) PRIMARY KEY CONSTRAINT cno_cons CHECK cno > 0,
 title     CHAR(7)  CONSTRAINT title_cons CHECK title IN ('Mr','Mrs','Company'),
 firstname CHAR(20),
 name      CHAR(40) NOT NULL,
 zip       CHAR(5)  CONSTRAINT zip_cons CHECK
           SUBSTR(zip,1,1) BETWEEN '0' AND '9' AND
           SUBSTR(zip,2,1) BETWEEN '0' AND '9' AND
           SUBSTR(zip,3,1) BETWEEN '0' AND '9' AND
           SUBSTR(zip,4,1) BETWEEN '0' AND '9' AND
           SUBSTR(zip,5,1) BETWEEN '0' AND '9',
 address   CHAR(40) NOT NULL,
FOREIGN KEY customer_zip_in_city (zip) REFERENCES city ON DELETE RESTRICT)

End of the code.

The cno column is the primary key column. Specifying the cno_cons constraint ensures that the customer number is be greater than zero. The title_cons constraint ensures that Mr or Mrs or Company is selected as title. NULL values cannot be entered in the name and address columns. Specifying the zip_cons constraint ensures that only characters between 0 and 9 can be used as zip codes. The zip column is the foreign key column. The keywords ON DELETE RESTRICT have the effect that the system warns you when you delete rows from the CITY table if a row exists in the CUSTOMER table that matches the zip code in the row that you want to delete.

HOTEL Table

Every table row contains the following information: a number that uniquely identifies the hotel, the hotel name and address (zip code, street, and house number), and information about the hotel.

Syntax Syntax

CREATE TABLE hotel
(hno     FIXED(4) PRIMARY KEY CONSTRAINT hno_cons CHECK hno > 0,
 name    CHAR(50) NOT NULL,
 zip     CHAR(5) CONSTRAINT zip_cons CHECK
         SUBSTR(zip,1,1) BETWEEN '0' AND '9' AND
         SUBSTR(zip,2,1) BETWEEN '0' AND '9' AND
         SUBSTR(zip,3,1) BETWEEN '0' AND '9' AND
         SUBSTR(zip,4,1) BETWEEN '0' AND '9' AND
         SUBSTR(zip,5,1) BETWEEN '0' AND '9',
 address CHAR(40) NOT NULL,
 info    CLOB,
FOREIGN KEY hotel_zip_in_city (zip) REFERENCES city ON DELETE RESTRICT)

End of the code.

The hno column is the primary key column. Specifying the hno_cons constraint ensures that the hotel number is be greater than zero. NULL values cannot be entered in the name and address columns. Specifying the zip_cons constraint ensures that only characters between 0 and 9 can be used as zip codes. The zip column is the foreign key column. The keywords ON DELETE RESTRICT have the effect that the system warns you when you delete rows from the CITY table if a row exists in the HOTEL table that matches the zip code in the row that you want to delete. In this case, the row is not deleted.

ROOM Table

Every table row contains the following 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.

Syntax Syntax

CREATE TABLE room
(hno   FIXED(4)   CONSTRAINT hno_cons CHECK hno > 0,
 type  CHAR(6)    CONSTRAINT type_cons CHECK type IN ('single','double','suite'), PRIMARY KEY (hno,type),
 free  FIXED(3,0) CONSTRAINT free_cons CHECK free >= 0,
 price FIXED(6,2) CONSTRAINT price_cons CHECK price BETWEEN 0.00 AND 5000.00,
FOREIGN KEY room_hno_in_hotel (hno) REFERENCES hotel ON DELETE CASCADE)

End of the code.

The columns hno and type form the primary key. Specifying the hno_cons constraint ensures that the hotel number is greater than zero. The type_cons constraint ensures that single, double or suite is selected as room type. Specifying the free_cons constraint ensures that the number of free rooms is greater than zero. Specifying the price_cons constraint ensures that the room price lies between 0 and 5000 is. The hno column is the foreign key column. The keywords ON DELETE CASCADE have the effect that when you delete rows from the HOTEL table, the system deletes the rows from the ROOM table that contain a hotel number identical to that in the row that you want to delete.

RESERVATION Table

Every table row contains the following 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.

Syntax Syntax

CREATE TABLE reservation
(rno       FIXED(4) PRIMARY KEY CONSTRAINT rno_cons CHECK rno > 0,
 cno       FIXED(4) CONSTRAINT cno_cons CHECK cno > 0,
 hno       FIXED(4) CONSTRAINT hno_cons CHECK hno > 0,
 type      CHAR(6)  CONSTRAINT type_cons CHECK type IN ('single','double','suite'),
 arrival   DATE     NOT NULL,
 departure DATE     NOT NULL, CONSTRAINT staying CHECK departure > arrival,
FOREIGN KEY reservation_cno_in_customer (cno) REFERENCES customer ON DELETE CASCADE
FOREIGN KEY reservation_info_in_room (hno,type) REFERENCES room ON DELETE CASCADE)

End of the code.

The rno column is the primary key column. Specifying the rno_cons constraint ensures that the reservation number is greater than zero. Specifying the cno_cons constraint ensures that the customer number is greater than zero. Specifying the hno_cons constraint ensures that the hotel number is greater than zero. The type_cons constraint ensures that single, double or suite is selected as room type. NULL values cannot be entered in the arrival and departure columns. Specifying the staying constraint ensures that the departure date is after the arrival date. The cno column is the foreign key column. The keywords ON DELETE CASCADE have the effect that when you delete rows from the CUSTOMER table, the system deletes the rows from the RESERVATION table that contain a hotel number identical to that in the row that you want to delete. The columns hno and type form the primary key. The keywords ON DELETE CASCADE have the effect that when you delete rows from the ROOM table, the system deletes the rows from the RESERVATION table that contain a hotel number and room type identical to those in the row that you want to delete.

EMPLOYEE Table

Every table row contains the following information: a number that uniquely identifies the hotel, a number that uniquely identifies the employee, the title used in correspondence, the first and last name of the employee, and a number that assigns the employee to a superior.

Syntax Syntax

CREATE TABLE employee
(hno         FIXED(4),
 eno         FIXED(4), PRIMARY KEY (hno,eno),
 title       CHAR(7)  CONSTRAINT title_cons CHECK title IN ('Mr','Mrs'),
 name        CHAR(20) NOT NULL,
 manager_eno FIXED(4),
FOREIGN KEY employee_hno_in_hotel (hno) REFERENCES hotel ON DELETE CASCADE)

End of the code.

The columns hno and eno form the primary key. The title_cons constraint ensures that Mr or Mrs or is selected as the title. NULL values cannot be entered in the name column. The hno column is the foreign key column. The keywords ON DELETE CASCADE have the effect that when you delete rows from the HOTEL table, the system deletes the rows from the EMPLOYEE table that contain a hotel number identical to that in the row that you want to delete.

CUSTOMER_ADDR View Table

Every row in the view table contains the following information: a number for uniquely identifying the customer, the title to be used in correspondence, the customer’s last name, and the customer address, consisting of the zip code, city, state abbreviation, street and house number.

Syntax Syntax

CREATE VIEW customer_addr (cno, title, name, zip, city, state, address)
AS SELECT customer.cno, customer.title, customer.name, customer.zip,
          city.name, city.state, customer.address
          FROM customer, city
          WHERE customer.zip = city.zip WITH CHECK OPTION

End of the code.

The required information is taken from the customer and city tables. The keywords WITH CHECK OPTION have the effect that if an INSERT or UPDATE statement is issued for the view table, rows can only be created that can subsequently be selected via the view table. In other words, the search condition for the view table must be fulfilled for the resulting rows.

HOTEL_ADDR View Table

Every row in the view table contains the following information: a number for uniquely identifying the hotel, the name of the hotel and the address of the hotel, consisting of the zip code, city, state abbreviation, street and house number.

Syntax Syntax

CREATE VIEW hotel_addr (hno, name, zip, city, state, address)
AS SELECT hotel.hno, hotel.name, hotel.zip,
          city.name, city.state, hotel.address
          FROM hotel, city
          WHERE hotel.zip = city.zip WITH CHECK OPTION

End of the code.

The required information is taken from the hotel and city tables. The keywords WITH CHECK OPTION have the effect that if an INSERT or UPDATE statement is issued for the view table, rows can only be created that can subsequently be selected via the view table. In other words, the search condition for the view table must be fulfilled for the resulting rows.

CUSTOM_HOTEL View Table (Hotel Reservations)

Every row in the view table contains the following information: customer surname, customer address, name and city of the hotel.

Syntax Syntax

CREATE VIEW custom_hotel (customname, customcity, hotelname, hotelsity)
AS SELECT customer_addr.name, customer_addr.city,
          hotel_addr.name, hotel_addr.city
          FROM customer_addr, hotel_addr, reservation
          WHERE customer_addr.cno = reservation.cno
          AND hotel_addr.hno = reservation.hno

End of the code.

The required information is taken from the customer_addr and hotel_addr view tables of the reservation table. The customers who have reservations in the hotels are displayed.

Single Column Index

Syntax Syntax

```
CREATE INDEX city_state ON city (state)
```

End of the code.

The secondary index city_state is defined for the state column in the city table.

Multi Column Index

Syntax Syntax

CREATE INDEX full_name_index ON customer (name, firstname)

End of the code.

The secondary index full_name_index is defined for the name and firstname columns in the customer table.

More Information

Demo Database