# 応用情報技術者試験 2020年 秋期 午後 問6 ## 宿泊施設の予約を行うシステム U社は、旅館や民宿などの宿泊施設の宿泊予約を行うWebシステム（以下、予約システムという）を開発している。予約システムの主な要件を図1に示す。 ・利用者が予約システムを最初に利用する際には、氏名、住所、電話番号を入力し、利用者登録を行う。 ・利用者は空き部屋照会のための条件入力の画面上で、希望する施設に対し、チェックインとチェックアウトの日付、予約したい部屋の種別及び部屋数を指定して空き状況を照会する。 ・予約は部屋の種別ごとに行う。種別の違う部屋を予約したい場合は、部屋の種別ごとに分けて予約を行う。 ・空き状況の照会を行った時点で、希望した種別の部屋に、希望した部屋数の空きがなかった場合は、部屋が空いていない旨を画面に表示する。 ・空き状況の照会を行った時点で希望した部屋数の空きがあった場合は、予約手続の画面に遷移する。利用者は、宿泊人数を入力し、部屋の予約を確定する。 ・部屋の予約を確定するまでの間に他の利用者が予約を入れてしまい、必要な部屋数を確保できなくなってしまった場合には、その旨を画面に表示して予約の処理を中断する。

【データベースの設計】 予約システムを開発するに当たり、データベースの設計を行った。データベースのE-R図を図2に示す。 ```mermaid %% 図2 データベースのE-R図（一部） erDiagram 施設 ||--o{ 部屋 : has 部屋 ||--o{ 部屋種別マスタ : belongs_to 利用者 ||--o{ 予約 : makes 予約 ||--o{ 予約明細 : contains 部屋 ||--o{ 予約明細 : is_booked 施設 { string 施設ID PK string 施設名 } 部屋 { string 部屋ID PK string 施設ID FK string 部屋種別ID FK string 部屋番号 } 部屋種別マスタ { string 部屋種別ID PK string 名称 int 宿泊可能人数 } 利用者 { string 利用者ID PK string 氏名 string 住所 string 電話番号 } 予約 { string 予約ID PK string 利用者ID FK int 人数 date チェックイン日付 date チェックアウト日付 } 予約明細 { string 予約明細ID PK string 予約ID FK string 部屋ID FK date 宿泊日 string 宿泊料 } ``` このデータベースでは、E-R図のエンティティ名を表名にし、属性名を列名にして、適切なデータ型で定義義した関係データベースによって、データを管理する。部屋IDは、全施設を通して一意な値である。また、予約ID、予約明細IDは、レコードを挿入した順に値が大きくなる。 【部屋の予約の流れ】 部屋の予約は、部屋の空き状況の確認と、予約確定の二つの処理から成る。部屋を予約する際には、希望した施設、部屋の種別、チェックイン日付、チェックアウト日付、部屋数について、空き状況の照会を行う。照会の結果、部屋に空きがあった場合は、予約手続の画面を表示する。部屋に空きがなかった場合は、部屋が空いていない旨を画面に表示し、空き部屋照会のための条件入力の画面に戻って条件を変更するよう促す。 部屋の空き状況の確認を行うためのSQL文を図3に示す。予約する部屋の施設ID、部屋種別ID、チェックイン日付、チェックアウト日付及び部屋数は、埋込み変数":施設ID"、":部屋種別ID"、":チェックイン日付"、":チェックアウト日付"及び":部屋数"に設定されている。

SELECT 施設ID, 部屋種別ID, COUNT(*) FROM 部屋
WHERE c (
    SELECT * FROM 予約明細 WHERE 予約明細.部屋ID = 部屋.部屋ID
    AND 予約明細.宿泊日 >= :チェックイン日付 AND 予約明細.宿泊日 < :チェックアウト日付
)
AND 施設ID = :施設ID AND 部屋種別ID = :部屋種別ID
GROUP BY 施設ID, 部屋種別ID
d >= :部屋数

【部屋の空き状況の確認の処理】 予約システムは、図3のSQL文の検索結果として、レコードが返された場合に予約可能であると判定し、予約手続の画面を表示する。レコードが返されなかった場合は、部屋が空いていない旨を画面に表示する。空き状況確認の処理の流れを図4に示す。 ```mermaid %% 図4 空き状況確認の処理の流れ flowchart TD START([開始]) SQL_QUERY[図3のSQL文で予約可能かを確認] DECISION{予約可能} NO_VACANCY[部屋が空いていない旨を画面に表示] PROCEDURE[予約手続の画面を表示] END([終了]) START --> SQL_QUERY SQL_QUERY --> DECISION DECISION -->|No| NO_VACANCY DECISION -->|Yes| PROCEDURE NO_VACANCY --> END PROCEDURE --> END ``` 【予約確定の処理】 予約手続の画面が表示された後、利用者は予約の確定の操作を行うことで部屋の予約を確定させる。予約の確定の処理では、予約のレコードを挿入した後、各宿泊日について、予約明細に必要な部屋数分のレコードを挿入する。 予約手続の画面が表示されてから、利用者が予約の確定の操作を行うまでの間に、他の利用者が先に予約を確定してしまうこともある。そこで、予約確定の処理では、レコードの挿入の前に図3のSQL文を再度実行し、まだ予約可能な状態であるかを確認してから挿入を行う。予約確定の処理の流れを図5に示す。 ```mermaid %% 図5 予約確定の処理の流れ flowchart TD START([開始]) SQL_QUERY[図3のSQL文で予約可能かを確認] DECISION{予約可能} NO_VACANCY[部屋が空いていない旨を画面に表示] GET_ROOM_ID[予約可能な部屋の部屋IDを取得する] INSERT_RECORD[レコードを挿入する] COMMIT[コミットする] END([終了]) START --> SQL_QUERY SQL_QUERY --> DECISION DECISION -->|No| NO_VACANCY DECISION -->|Yes| GET_ROOM_ID GET_ROOM_ID --> INSERT_RECORD INSERT_RECORD --> COMMIT COMMIT --> END NO_VACANCY --> END ``` 【不具合の報告と対応】 予約システムのテスト中に、同じ宿泊日の同じ部屋について、予約明細のレコードが重複して挿入されてしまう不具合が報告された。報告された事象について確認すると、別々の利用者が同じ時刻に予約確定の操作を行った際に発生していた。 そこで、今後同じ宿泊日の同じ部屋の予約が重複して入らないようにするために、予約明細テーブルのe列とf列の複合キーに対して制約を追加することにした。このような制約のことを、gという。 gを追加するためには、既に重複して挿入されてしまったレコードを削除する必要がある。削除に当たっては、同じ宿泊日の同じ部屋の予約が重複した予約明細のレコードについて、最初に挿入された予約のレコードと、それに紐づく予約明細のレコードを残し、それ以外の予約明細、予約のレコードを削除することにした。 予約明細について、削除するレコードを抽出するSQL文を図6に示す。図6で得られた該当の予約明細のレコードを削除するとともに、それらに紐づく予約のレコードを削除してから、テストの作業を再開することにした。 予約明細テーブルへの制約の追加後、当該の不具合について再度テストを行ったところ、追加した制約によって、重複が発生しなくなったことが確認できた。

SELECT t1.予約ID, t1.予約明細ID, t1.部屋ID, t1.宿泊日 FROM 予約明細 t1
WHERE t1.予約ID > ( SELECT h FROM 予約明細 t2
                    WHERE i AND j )