スキーマ HOTEL のオブジェクト 

デモデータをデモデータベースにロードした後の HOTEL スキーマには、CITY、CUSTOMER、HOTEL、ROOM、RESERVATION、EMPLOYEE の各テーブルおよびその他のデータベースオブジェクトが含まれています。HOTEL スキーマおよびそのオブジェクトの所有者は MONA です。テーブルには、デモデータが格納されています。

テーブル

CITY (市区町村テーブル)

このテーブルの各行には、郵便番号、市区町村名、および市区町村が属する都道府県/州の短縮形という市区町村情報が含まれています。

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)

zip 列は、一次キー列です。

zip_cons 制約によって、郵便番号には 0 〜 9 の数字のみが入力されることが保証されます。

市区町村名 (name) 列および都道府県/州の短縮形 (state) 列には、Null 値を指定することはできません。

CUSTOMER (得意先テーブル)

このテーブルの各行には、得意先を一意に識別する番号、連絡時に使用する敬称、得意先の姓名、および郵便番号、地名、番地から構成される得意先の住所情報という得意先情報が含まれています。

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)

cno 列は、一次キー列です。

cno_cons 制約を指定することで、得意先コードに 0 より大きい値が選択されることが保証されます。

title_cons 制約を指定することで、敬称として Mr、Mrs、または Company が選択されることが保証されます。

name および address 列には、Null 値を入力することはできません。

zip_cons 制約を指定することで、郵便番号には 0 〜 9 の数字のみが使用されることが保証されます。

zip 列は、外部キー列です。キーワード ON DELETE RESTRICT を指定すると、CITY テーブルから行を削除する場合に、削除する行の郵便番号に一致する行が CUSTOMER テーブルに存在すると、システムによって警告が出力されます。

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)

hno 列は、一次キー列です。

hno_cons 制約を指定することで、ホテルコードに 0 より大きい値が選択されることが保証されます。

name および address 列には、Null 値を入力することはできません。

zip_cons 制約を指定することで、郵便番号には 0 〜 9 の数字のみが使用されることが保証されます。

zip 列は、外部キー列です。キーワード ON DELETE RESTRICT を指定すると、CITY テーブルから行を削除する場合に、削除する行の郵便番号に一致する行が HOTEL テーブルに存在すると、システムによって警告が出力されます。この場合、その行は削除されません。

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)

hno 列および type 列は、一次キーです。

hno_cons 制約を指定することで、ホテルコードに 0 より大きい値が選択されることが保証されます。

type_cons 制約を指定することで、部屋のタイプとして single、double、または suite が選択されることが保証されます。

free_cons 制約を指定することで、空き部屋数に 0 より大きい値が選択されることが保証されます。

price_cons 制約を指定することで、部屋の料金に 0 〜 5000 の範囲の値が選択されることが保証されます。

hno 列は、外部キー列です。キーワード ON DELETE CASCADE を指定すると、HOTEL テーブルから行を削除する場合に、削除する行のホテルコードに一致する行が、システムによって ROOM テーブルから削除されます。

RESERVATION (予約テーブル)

このテーブルの各行には、予約を一意に識別する番号、得意先を一意に識別する番号、ホテルを一意に識別する番号、部屋のタイプについての情報 (シングルルーム、ダブルルーム、スイート)、および到着日と出発日という予約情報が含まれています。

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)

rno 列は、一次キー列です。

rno_cons 制約を指定することで、予約コードに 0 より大きい値が選択されることが保証されます。

cno_cons 制約を指定することで、得意先コードに 0 より大きい値が選択されることが保証されます。

hno_cons 制約を指定することで、ホテルコードに 0 より大きい値が選択されることが保証されます。

type_cons 制約を指定することで、部屋のタイプとして single、double、または suite が選択されることが保証されます。

arrival 列および departure 列には、Null 値を入力することはできません。

staying 制約を指定することで、出発日として、到着日よりも後の日付が選択されることが保証されます。

cno 列は、外部キー列です。キーワード ON DELETE CASCADE を指定すると、CUSTOMER テーブルから行を削除する場合に、削除する行の得意先コードに一致する行が、システムによって RESERVATION テーブルから削除されます。

hno 列および type 列は、外部キーです。キーワード ON DELETE CASCADE を指定すると、ROOM テーブルから行を削除する場合に、削除する行のホテルコードおよび部屋のタイプに一致する行が、システムによって RESERVATION テーブルから削除されます。

EMPLOYEE (従業員テーブル)

このテーブルの各行には、ホテルを一意に識別する番号、従業員を一意に識別する番号、連絡時に使用する敬称、従業員の姓名、および従業員を上長に割り当てる番号という従業員情報が含まれています。

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)

hno 列および eno 列は、一次キーです。

title_cons 制約を指定することで、敬称として Mr または Mrs が選択されることが保証されます。

name 列には、Null 値を入力することはできません。

hno 列は、外部キー列です。キーワード ON DELETE CASCADE を指定すると、HOTEL テーブルから行を削除する場合に、削除する行のホテルコードに一致する行が、システムによって EMPLOYEE テーブルから削除されます。

ビューテーブル

CUSTOMER_ADDR (得意先住所情報)

このビューテーブルの各行には、得意先を一意に識別する番号、連絡時に使用する敬称、得意先の姓、および郵便番号、市区町村、都道府県/州の短縮形、地名、番地から構成される得意先の住所情報という得意先情報が含まれています。

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

必要な情報は、customer テーブルおよび city テーブルから取得されます。

キーワード WITH CHECK OPTION を指定すると、ビューテーブルに対して insert 文または update 文を発行した場合に、後でビューテーブルを介して選択することができないデータを登録することができないようにすることができます。つまり、ビューテーブルの検索条件を満たすデータのみが登録されます。

HOTEL_ADDR (ホテル住所情報)

このビューテーブルの各行には、ホテルを一意に識別する番号、ホテルの名称、および郵便番号、市区町村、都道府県/州の短縮形、地名、番地から構成されるホテルの住所情報というホテル情報が含まれています。

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

必要な情報は、hotel テーブルおよび city テーブルから取得されます。

キーワード WITH CHECK OPTION を指定すると、ビューテーブルに対して insert 文または update 文を発行した場合に、後でビューテーブルを介して選択することができないデータを登録することができないようにすることができます。つまり、ビューテーブルの検索条件を満たすデータのみが登録されます。

CUSTOM_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

必要な情報は、reservation テーブルを基準にして、customer_addr ビューテーブルおよび hotel_addr ビューテーブルから取得されます。

ホテルに予約をしている得意先が表示されます。

索引

単独列索引

CREATE INDEX city_state ON city (state)

二次索引 city_state が、city テーブルの state 列に定義されます。

複数列索引

CREATE INDEX full_name_index ON customer (surname, first name)

二次索引 full_name_index が、customer テーブルの name 列および firstname 列に定義されます。