Objects in the Schema HOTEL (SAP Library

Objects in the Schema HOTEL

After you load the demo data into the Demo Database the HOTEL schema contains the CITY, CUSTOMER, HOTEL, ROOM, RESERVATION, EMPLOYEE tables and other database objects. User MONA is the owner of the HOTEL schema and its objects. The tables are filled with demo data.

Tables

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.

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.

The zip_cons constraint ensures that the only numbers between 0 and 9 can be entered for the zip code.

The city name (name) and state abbreviation (state) columns cannot contain NULL values.

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.

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 a customer number greater than zero is selected.

Specifying the title_cons constraint ensures that Mr, Mrs, or Company is selected as the title.

You cannot enter a NULL value in the name and address columns.

Specifying the zip_cons constraint ensures that only characters between 0 and 9 can be used to specify the zip codes.

The zip column is the foreign key column. The keywords ON DELETE RESTRICT have the effect that the system outputs a warning 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 (Hotel Table)

Each row in this table contains the following hotel 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    LONG,
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 a hotel number greater than zero is selected.

You cannot enter a NULL value in the name and address columns.

Specifying the zip_cons constraint ensures that only characters between 0 and 9 can be used to specify the zip codes.

The zip column is the foreign key column. The keywords ON DELETE RESTRICT have the effect that the system outputs a warning 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 (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.

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 hno and type columns form the primary key.

Specifying the hno_cons constraint ensures that a hotel number greater than zero is selected.

Specifying the type_cons constraint ensures that single, double, or suite is selected as the room type.

Specifying the free_cons constraint ensures that a number of free rooms greater than zero is selected.

Specifying the price_cons constraint ensures that a room price with a value of between 0 and 5000 is selected.

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

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 a reservation number greater than zero is selected.

Specifying the cno_cons constraint ensures that a customer number greater than zero is selected.

Specifying the hno_cons constraint ensures that a hotel number greater than zero is selected.

Specifying the type_cons constraint ensures that single, double, or suite is selected as the room type.

You cannot enter a NULL value in the arrival and departure columns.

Specifying the staying constraint ensures that a date after the arrival date is selected as the departure 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 customer number identical to that in the row that you want to delete.

The hno and type columns form the foreign 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 (Employee Table)

Each row in this table contains the following employee 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'),
firstname   CHAR(20),
name        CHAR(20) NOT NULL,
manager_eno FIXED(4),
FOREIGN KEY employee_hno_in_hotel (hno) REFERENCES hotel ON DELETE CASCADE)

The hno and eno columns form the primary key.

The title_cons constraint ensures that Mr or Mrs 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.

View Tables

CUSTOMER_ADDR (Customer Addresses)

Each row in the view table contains the following customer 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 key words WITH CHECK OPTION have the effect that the insert statement or update statement issued on the view table does not create any rows that could not be selected subsequently via the view table; that is, the search condition of the view table must be true for any resulting rows.

HOTEL_ADDR (Hotel Addresses)

Each row in the view table contains the following hotel 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.

CUSTOM_HOTEL (Hotel Reservations)

Each row of the view table contains the following information: the customer’s last name, place of residence and the name and city of the hotel.

CREATE VIEW custom_hotel (customname, customcity, hotelname, hotelcity)
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.

Indexes

Single-column index

CREATE INDEX city_state ON city (state)

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

Multi-column index

CREATE INDEX full_name_index ON customer (surname, first name)

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