Show TOC

Objects in the HOTEL SchemaLocate this document in the navigation structure

Use

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.

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)
            

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.

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)
            

The cno column is the primary key column. Specifying the cno_cons constraint ensures that the customer number is 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.

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)
            

The hno column is the primary key column. Specifying the hno_cons constraint ensures that the hotel number is 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.

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)
            

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.

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)
            

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.

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)
            

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.

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
            

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.

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
            

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.

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
            

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

CREATE INDEX city_state ON city (state)
            

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

Multi Column Index

CREATE INDEX full_name_index ON customer (name, firstname)
            

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

More Information