宿泊施設の予約を行うシステムに関する次の記述を読んで,設問1〜3に答えよ。
U社は,旅館や民宿などの宿泊施設の宿泊予約を行うWebシステム(以下,予約システムという)を開発している。予約システムの主な要件を図1に示す。
・利用者が予約システムを最初に利用する際には,氏名,住所,電話番号を入力し,利用者登録を行う。
・利用者は空き部屋照会のための条件入力の画面上で,希望する施設に対し,チェックインとチェックアウトの日付,予約したい部屋の種別及び部屋数を指定して空き状況を照会する。
・予約は部屋の種別ごとに行う。種別の違う部屋を予約したい場合は,部屋の種別ごとに分けて予約を行う。
・空き状況の照会を行った時点で,希望した種別の部屋に,希望した部屋数の空きがなかった場合は,部屋が空いていない旨を画面に表示する。
・空き状況の照会を行った時点で希望した部屋数の空きがあった場合は,予約手続の画面に遷移する。利用者は,宿泊人数を入力し,部屋の予約を確定する。
・部屋の予約を確定するまでの間に他の利用者が予約を入れてしまい,必要な部屋数を確保できなくなってしまった場合には,その旨を画面に表示して予約の処理を中断する。
図1 予約システムの主な要件
予約システムを開発するに当たり,データベースの設計を行った。データベースのE-R図を図2に示す。
図2 データベースのE-R図(一部)
このデータベースでは,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文
予約システムは,図3のSQL文の検索結果として,レコードが返された場合に予約可能であると判定し,予約手続の画面を表示する。レコードが返されなかった場合は,部屋が空いていない旨を画面に表示する。空き状況確認の処理の流れを図4に示す。
図4 空き状況確認の処理の流れ
〔予約確定の処理〕
予約手続の画面が表示された後,利用者は予約の確定の操作を行うことで部屋の予約を確定させる。予約の確定の処理では,予約のレコードを挿入した後,各宿泊日について,予約明細に必要な部屋数分のレコードを挿入する。
予約手続の画面が表示されてから,利用者が予約の確定の操作を行うまでの間に,他の利用者が先に予約を確定してしまうこともある。そこで,予約確定の処理では,レコードの挿入の前に図3のSQL文を再度実行し,まだ予約可能な状態であるかを確認してから挿入を行う。予約確定の処理の流れを図5に示す。
図5 予約確定の処理の流れ
予約システムのテスト中に,同じ宿泊日の同じ部屋について,予約明細のレコードが重複して挿入されてしまう不具合が報告された。報告された事象について確認すると,別々の利用者が同じ時刻に予約確定の操作を行った際に発生していた。
そこで,今後同じ宿泊日の同じ部屋の予約が重複して入らないようにするために,予約明細テーブルの 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 )
図6 削除するレコードを抽出するSQL文
出典:令和2年度秋期 問6
設問1 図2中の a , b に入れる適切なエンティティ間の関連及び属性名を答え,E-R図を完成させよ。なお,エンティティ間の関連及び属性名の表記は,図2の凡例及び注記に倣うこと。
解答・解説
解答例
a:→
b:施設ID
解説
- 予約テーブルの主キーである予約IDが、予約明細テーブルに外部キーとして定義されているため、“予約 → 予約明細”という関係になります。
- 施設テーブルと部屋テーブル、部屋種別マスタテーブルと部屋テーブルがそれぞれ1対多の関係になっているため、部屋テーブルには外部キーとして、施設テーブルと部屋種別マスタテーブルの主キーがあるはずです。
部屋種別マスタテーブルの主キーの部屋種別IDはすでに記載されているため、答えは施設テーブルの主キーである施設IDが入ります。
(外部キーであるため破線下線が必要です)
設問2 図3中の c , d に入れる適切な字句を答えよ。
解答・解説
解答例
c:NOT EXISTS
d:HAVING COUNT(*)
解説
- XXX
- XXX
完成したSQL文は次の通りです。
SELECT 施設ID, 部屋種別ID, COUNT(*) FROM 部屋
WHERE NOT EXISTS (
SELECT * FROM 予約明細 WHERE 予約明細.部屋ID = 部屋.部屋ID
AND 予約明細.宿泊日 >= :チェックイン日付 AND 予約明細.宿泊日 < :チェックアウト日付
)
AND 施設ID = :施設ID AND 部屋種別ID = :部屋種別ID
GROUP BY 施設ID, 部屋種別ID
HAVING COUNT(*) >= :部屋数
設問3 〔不具合の報告と対応〕について,(1)〜(3)に答えよ。
(1)本文中の e , f に入れる適切な列名を答えよ。
解答・解説
解答例
e:宿泊日
f:部屋ID
解説
- XXX
- XXX
(2)本文中の g に入れる適切な字句を答えよ。
解答・解説
解答例
UNIQUE制約
解説
XXX
(3)図6中の h 〜 j に入れる適切な字句を答えよ。
解答・解説
解答例
h:MIN(t2.予約ID)
i:t1.部屋ID = t2.部屋ID
j:t1.宿泊日 = t2.宿泊日
解説
- XXX
- XXX
- XXX
完成したSQL文は次の通りです。
SELECT t1.予約ID, t1.予約明細ID, t1.部屋ID, t1.宿泊日 FROM 予約明細 t1
WHERE t1.予約ID > ( SELECT MIN(t2.予約ID) FROM 予約明細 t2
WHERE t1.部屋ID = t2.部屋ID AND t1.宿泊日 = t2.宿泊日 )
IPA公開情報
出題趣旨
データベースを利用するシステムを開発,運用する際には,データベース上に不整合なデータが入らないよ うにする必要がある。万が一,プログラムの不具合が原因で不整合なデータが入ってしまった場合には,速や かな暫定対応と,その後の恒久対応が求められる。
本問では,宿泊施設の予約システムを題材に,データベースに関する基本的な理解と,データ保全に関する 応用力を問う。
採点講評
問 6 では,宿泊施設の予約システムを題材に,データベースに関する基本的な理解と,データ保全に関する 応用力について出題した。全体として正答率は平均的であった。
設問 2 の d は,正答率がやや低かった。HAVING 句は,GROUP BY 句でグルーピングした項目に対する条件 付けに利用されるので,よく理解しておいてほしい。
設問 3(3)の h は,正答率が低かった。列名の指定は正しいが,最小値を指定する記述がない解答が散見され た。どのような抽出条件を記述すれば,目的のレコードを正しく抽出できるのかについて,注意深く考えて正 答を導いてほしい。