応用情報技術者過去問題 令和2年秋期 午後問6
⇄問題文と設問を画面2分割で開く⇱問題PDF問6 データベース
宿泊施設の予約を行うシステムに関する次の記述を読んで，設問1～3に答えよ。
U社は，旅館や民宿などの宿泊施設の宿泊予約を行うWebシステム(以下，予約システムという)を開発している。予約システムの主な要件を図1に示す。
〔データベースの設計〕
予約システムを開発するに当たり，データベースの設計を行った。データベースのE-R図を図2に示す。
このデータベースでは，E-R図のエンティティ名を表名にし，属性名を列名にして，適切なデータ型で表定義した関係データベースによって，データを管理する。部屋IDは，全施設を通して一意な値である。また，予約ID，予約明細IDは，レコードを挿入した順に値が大きくなる。
〔部屋の予約の流れ〕
部屋の予約は，部屋の空き状況の確認と，予約確定の二つの処理から成る。部屋を予約する際には，希望した施設，部屋の種別，チェックイン日付，チェックアウト日付，部屋数について，空き状況の照会を行う。照会の結果，部屋に空きがあった場合は，予約手続の画面を表示する。部屋に空きがなかった場合は，部屋が空いていない旨を画面に表示し，空き部屋照会のための条件入力の画面に戻って条件を変更するよう促す。
部屋の空き状況の確認を行うためのSQL文を図3に示す。予約する部屋の施設ID，部屋種別ID，チェックイン日付，チェックアウト日付及び部屋数は，埋込み変数":施設ID"，":部屋種別ID"，":チェックイン日付"，":チェックアウト日付"及び":部屋数"に設定されている。
〔部屋の空き状況の確認の処理〕
予約システムは，図3のSQL文の検索結果として，レコードが返された場合に予約可能であると判定し，予約手続の画面を表示する。レコードが返されなかった場合は，部屋が空いていない旨を画面に表示する。空き状況確認の処理の流れを図4に示す。
〔予約確定の処理〕
予約手続の画面が表示された後，利用者は予約の確定の操作を行うことで部屋の予約を確定させる。予約の確定の処理では，予約のレコードを挿入した後，各宿泊日について，予約明細に必要な部屋数分のレコードを挿入する。
予約手続の画面が表示されてから，利用者が予約の確定の操作を行うまでの間に，他の利用者が先に予約を確定してしまうこともある。そこで，予約確定の処理では，レコードの挿入の前に図3のSQL文を再度実行し，まだ予約可能な状態であるかを確認してから挿入を行う。予約確定の処理の流れを図5に示す。
〔不具合の報告と対応〕
予約システムのテスト中に，同じ宿泊日の同じ部屋について，予約明細のレコードが重複して挿入されてしまう不具合が報告された。報告された事象について確認すると，別々の利用者が同じ時刻に予約確定の操作を行った際に発生していた。
そこで，今後同じ宿泊日の同じ部屋の予約が重複して入らないようにするために，予約明細テーブルのe列とf列の複合キーに対して制約を追加することにした。このような制約のことを，gという。
gを追加するためには，既に重複して挿入されてしまったレコードを削除する必要がある。削除に当たっては，同じ宿泊日の同じ部屋の予約が重複した予約明細のレコードについて，最初に挿入された予約のレコードと，それに紐づく予約明細のレコードを残し，それ以外の予約明細，予約のレコードを削除することにした。
予約明細について，削除するレコードを抽出するSQL文を図6に示す。図6で得られた該当の予約明細のレコードを削除するとともに，それらに紐づく予約のレコードを削除してから，テストの作業を再開することにした。
予約明細テーブルへの制約の追加後，当該の不具合について再度テストを行ったところ，追加した制約によって，重複が発生しなくなったことが確認できた。
U社は，旅館や民宿などの宿泊施設の宿泊予約を行うWebシステム(以下，予約システムという)を開発している。予約システムの主な要件を図1に示す。
予約システムを開発するに当たり，データベースの設計を行った。データベースのE-R図を図2に示す。
〔部屋の予約の流れ〕
部屋の予約は，部屋の空き状況の確認と，予約確定の二つの処理から成る。部屋を予約する際には，希望した施設，部屋の種別，チェックイン日付，チェックアウト日付，部屋数について，空き状況の照会を行う。照会の結果，部屋に空きがあった場合は，予約手続の画面を表示する。部屋に空きがなかった場合は，部屋が空いていない旨を画面に表示し，空き部屋照会のための条件入力の画面に戻って条件を変更するよう促す。
部屋の空き状況の確認を行うためのSQL文を図3に示す。予約する部屋の施設ID，部屋種別ID，チェックイン日付，チェックアウト日付及び部屋数は，埋込み変数":施設ID"，":部屋種別ID"，":チェックイン日付"，":チェックアウト日付"及び":部屋数"に設定されている。
予約システムは，図3のSQL文の検索結果として，レコードが返された場合に予約可能であると判定し，予約手続の画面を表示する。レコードが返されなかった場合は，部屋が空いていない旨を画面に表示する。空き状況確認の処理の流れを図4に示す。
予約手続の画面が表示された後，利用者は予約の確定の操作を行うことで部屋の予約を確定させる。予約の確定の処理では，予約のレコードを挿入した後，各宿泊日について，予約明細に必要な部屋数分のレコードを挿入する。
予約手続の画面が表示されてから，利用者が予約の確定の操作を行うまでの間に，他の利用者が先に予約を確定してしまうこともある。そこで，予約確定の処理では，レコードの挿入の前に図3のSQL文を再度実行し，まだ予約可能な状態であるかを確認してから挿入を行う。予約確定の処理の流れを図5に示す。
予約システムのテスト中に，同じ宿泊日の同じ部屋について，予約明細のレコードが重複して挿入されてしまう不具合が報告された。報告された事象について確認すると，別々の利用者が同じ時刻に予約確定の操作を行った際に発生していた。
そこで，今後同じ宿泊日の同じ部屋の予約が重複して入らないようにするために，予約明細テーブルのe列とf列の複合キーに対して制約を追加することにした。このような制約のことを，gという。
gを追加するためには，既に重複して挿入されてしまったレコードを削除する必要がある。削除に当たっては，同じ宿泊日の同じ部屋の予約が重複した予約明細のレコードについて，最初に挿入された予約のレコードと，それに紐づく予約明細のレコードを残し，それ以外の予約明細，予約のレコードを削除することにした。
予約明細について，削除するレコードを抽出するSQL文を図6に示す。図6で得られた該当の予約明細のレコードを削除するとともに，それらに紐づく予約のレコードを削除してから，テストの作業を再開することにした。
予約明細テーブルへの制約の追加後，当該の不具合について再度テストを行ったところ，追加した制約によって，重複が発生しなくなったことが確認できた。
設問1
図2中のa，bに入れる適切なエンティティ間の関連及び属性名を答え，E-R図を完成させよ。
なお，エンティティ間の関連及び属性名の表記は，図2の凡例及び注記に倣うこと。
(※正誤判定の都合上、主キー属性は{属性名}、外部キー属性は(属性名)と入力してください)
なお，エンティティ間の関連及び属性名の表記は，図2の凡例及び注記に倣うこと。
(※正誤判定の都合上、主キー属性は{属性名}、外部キー属性は(属性名)と入力してください)
解答入力欄
- a：
- b：
解答例・解答の要点
- a：→
- b：施設ID
解説
〔aについて〕
予約エンティティと予約明細エンティティには共通の属性「予約ID」があります。この属性「予約ID」は、予約エンティティ側では主キーであり、予約を一意に特定します。また、予約明細エンティティ側では外部キーです。
E-R図の予約明細エンティティを見ると、属性として「部屋ID」と「宿泊日」があるので、予約明細のレコードは部屋ごと宿泊日ごとに1件ずつ生成されることがわかります。宿泊予約は、チェックインとチェックアウトの日付および部屋数を指定して行うので、宿泊日が数日にわたる予約や複数の部屋に宿泊する予約などでは、1件の予約に複数件の予約明細が紐づくことになります。予約明細エンティティでは、同一の予約IDをもつ複数のレコードが存在しえるので、予約エンティティと予約明細エンティティのカーディナリティは"1対多"であり、aには「→」が当てはまります。
関係データベースの理論上、主キー属性と外部キー属性の関連があるとき、主キー側エンティティが"1"、外部キー側エンティティが"多"になるので、E-R図を見て機械的に判断してしまってもOKです。
∴a＝→
〔bについて〕
部屋エンティティと施設エンティティの間には"多対1"の関係があります。関係データベースでは、共通の主キーと外部キーを持たせることでエンティティ同士を関連付けますが、部屋エンティティと施設エンティティは関連があるにもかかわらず共通の属性がありません。したがって、施設エンティティの主キー属性である「施設ID」を部屋エンティティに追加する必要があります。追加する属性「施設ID」は、施設エンティティの「施設ID」を参照する外部キーとなります。
次に、部屋エンティティにおいて「施設ID」が主キーの一部となるかどうかですが、〔データベースの設計〕には「部屋IDは，全施設を通して一意な値である」とあるため、部屋エンティティのレコードは「部屋ID」のみで一意に特定できることがわかります（施設IDとの複合主キーではない）。
したがって、bには外部キーを示す破線を付けた「施設ID」が当てはまります。
∴b＝施設ID
予約エンティティと予約明細エンティティには共通の属性「予約ID」があります。この属性「予約ID」は、予約エンティティ側では主キーであり、予約を一意に特定します。また、予約明細エンティティ側では外部キーです。
E-R図の予約明細エンティティを見ると、属性として「部屋ID」と「宿泊日」があるので、予約明細のレコードは部屋ごと宿泊日ごとに1件ずつ生成されることがわかります。宿泊予約は、チェックインとチェックアウトの日付および部屋数を指定して行うので、宿泊日が数日にわたる予約や複数の部屋に宿泊する予約などでは、1件の予約に複数件の予約明細が紐づくことになります。予約明細エンティティでは、同一の予約IDをもつ複数のレコードが存在しえるので、予約エンティティと予約明細エンティティのカーディナリティは"1対多"であり、aには「→」が当てはまります。
関係データベースの理論上、主キー属性と外部キー属性の関連があるとき、主キー側エンティティが"1"、外部キー側エンティティが"多"になるので、E-R図を見て機械的に判断してしまってもOKです。
∴a＝→
〔bについて〕
部屋エンティティと施設エンティティの間には"多対1"の関係があります。関係データベースでは、共通の主キーと外部キーを持たせることでエンティティ同士を関連付けますが、部屋エンティティと施設エンティティは関連があるにもかかわらず共通の属性がありません。したがって、施設エンティティの主キー属性である「施設ID」を部屋エンティティに追加する必要があります。追加する属性「施設ID」は、施設エンティティの「施設ID」を参照する外部キーとなります。
次に、部屋エンティティにおいて「施設ID」が主キーの一部となるかどうかですが、〔データベースの設計〕には「部屋IDは，全施設を通して一意な値である」とあるため、部屋エンティティのレコードは「部屋ID」のみで一意に特定できることがわかります（施設IDとの複合主キーではない）。
したがって、bには外部キーを示す破線を付けた「施設ID」が当てはまります。
∴b＝施設ID
設問2
図3中のc，dに入れる適切な字句を答えよ。
解答入力欄
- c：
- d：
解答例・解答の要点
- c：NOT EXISTS
- d：HAVING COUNT(*)
解説
〔cについて〕
WHERE句の副問合せを見てみると、予約明細テーブルから、主問合せの部屋IDに一致し、宿泊日がチェックイン日付以上チェックアウト日付未満になっているレコードを抽出しています。この絞込みは、指定の部屋についてチェックイン日からチェックアウト日前日まで、すなわち宿泊希望期間に予約が入っているか（予約明細テーブルにレコードが存在するか）をチェックするものです。今回は予約されていない部屋の個数を数えたいので、予約済のレコードが見つかった場合に偽を返す「NOT EXISTS」を入れるのが適切となります。
この設問のように表のある列の値が、別の表のある列に存在するかどうかはEXISTS句を用いた相関副問合せにより判定することができます。副問合せ部分のWHERE句で「主問合せの表の列名 = 副問合せの表の列名」を指定することで相関副問合せを実現します。図3中で「予約明細.部屋ID = 部屋.部屋ID」と指定しているのがそうです。相関副問合せでは主問合せの各レコードごとに副問合せが実行されるので、予約明細テーブルの部屋IDごとに副問合せが実行され、部屋テーブルのうち予約がされていない部屋のレコードだけがWHERE句によって抽出されるという流れになります。
∴c＝NOT EXISTS
〔dについて〕
GROUP BY句により、WHERE句で抽出されたレコード（予約がされていない部屋）が指定の施設ごと部屋種別ごとグルーピングされています。各グループのレコード数が予約したい部屋数以上であれば予約可能と判定できるので、グループごとのレコード数と比較する「COUNT(*) >= :部屋数」という条件を指定します。ここで注意しなければならないのは、GROUP BY句でグルーピング後の結果セットに対してグループの絞込みを行うには、WHERE句ではなくHAVING句を使用しなければならないということです。したがって、dには「HAVING COUNT(*)」が当てはまります。
∴d＝HAVING COUNT(*)
※(*)の中は、施設IDでも部屋種別でも正しく動作しますが、どれを指定しても同じ動作のときは *（全属性）を解答するのがベターです。
WHERE句の副問合せを見てみると、予約明細テーブルから、主問合せの部屋IDに一致し、宿泊日がチェックイン日付以上チェックアウト日付未満になっているレコードを抽出しています。この絞込みは、指定の部屋についてチェックイン日からチェックアウト日前日まで、すなわち宿泊希望期間に予約が入っているか（予約明細テーブルにレコードが存在するか）をチェックするものです。今回は予約されていない部屋の個数を数えたいので、予約済のレコードが見つかった場合に偽を返す「NOT EXISTS」を入れるのが適切となります。
この設問のように表のある列の値が、別の表のある列に存在するかどうかはEXISTS句を用いた相関副問合せにより判定することができます。副問合せ部分のWHERE句で「主問合せの表の列名 = 副問合せの表の列名」を指定することで相関副問合せを実現します。図3中で「予約明細.部屋ID = 部屋.部屋ID」と指定しているのがそうです。相関副問合せでは主問合せの各レコードごとに副問合せが実行されるので、予約明細テーブルの部屋IDごとに副問合せが実行され、部屋テーブルのうち予約がされていない部屋のレコードだけがWHERE句によって抽出されるという流れになります。
∴c＝NOT EXISTS
〔dについて〕
GROUP BY句により、WHERE句で抽出されたレコード（予約がされていない部屋）が指定の施設ごと部屋種別ごとグルーピングされています。各グループのレコード数が予約したい部屋数以上であれば予約可能と判定できるので、グループごとのレコード数と比較する「COUNT(*) >= :部屋数」という条件を指定します。ここで注意しなければならないのは、GROUP BY句でグルーピング後の結果セットに対してグループの絞込みを行うには、WHERE句ではなくHAVING句を使用しなければならないということです。したがって、dには「HAVING COUNT(*)」が当てはまります。
∴d＝HAVING COUNT(*)
※(*)の中は、施設IDでも部屋種別でも正しく動作しますが、どれを指定しても同じ動作のときは *（全属性）を解答するのがベターです。
設問3
〔不具合の報告と対応〕について，(1)～(3)に答えよ。
- 本文中のe，fに入れる適切な列名を答えよ。
- 本文中のgに入れる適切な字句を答えよ。
- 図6中のh～jに入れる適切な字句を答えよ。
解答入力欄
- e：
- f：
- g：
- h：
- i：
- j：
解答例・解答の要点
- e：宿泊日
- f：部屋ID
- g：UNIQUE制約
- h：MIN(t2.予約ID)
- i：t1.部屋ID = t2.部屋ID
- j：t1.宿泊日 = t2.宿泊日
解説
- 同じ宿泊日・同じ部屋の重複した予約を受け付けないようにするためには、予約明細テーブル内に同じ宿泊日・同じ部屋の値の組をもつレコードが存在しえないようにする必要があります。このように、列の値（の組）がテーブル内のすべての行で一意になるようにしたいときに付与する制約を「UNIQUE制約」と言います。
今回は"宿泊日"列と"部屋ID"列の値の組が重複しないようするのが目的なので、"宿泊日"列と"部屋ID"列の組に対するUNIQUE制約を予約明細テーブルに付与するのが適切です。
∴e、f＝宿泊日、部屋ID（順不同） - 〔gについて〕
(1)の解説どおり、gにはUNIQUE制約が入ります。一意制約や一意性制約でも問題ありません。
∴g＝UNIQUE制約 - 〔i、jについて〕
先に副問合せのWHERE句の条件に付いて考えます。
図6のSQL文には、予約明細テーブルから同一の部屋ID・宿泊日の組をもつレコードを絞り込む指定がないので、WHERE句でそれを指定します。
予約明細テーブルのレコードを1行ずつ確認し、その"部屋ID"列と"宿泊日"列の値の組が重複したレコードが他にあるかどうかは、相関副問合せを用いて判定することができます。主問合せ側のテーブルは t1、副問合せ側のテーブルは t2 というエイリアス（別名）が付いているので、「t1.部屋ID = t2.部屋ID」「t1.宿泊日 = t2.宿泊日」という2つの条件で、副問合せ側のレコードを絞り込むことになります。この操作によって、主問合せ側の現在のレコードと同じ部屋ID・宿泊日の組を持つレコードが副問合せ側で抽出されます。
∴i、j＝t1.部屋ID = t2.部屋ID、t1.宿泊日 = t2.宿泊日（順不同）
〔hについて〕
まず、主問合せ側WHERE句の比較演算子が">"になっていることに注目すると、副問合せからは単一値（スカラ値）、すなわち1行1列の結果を返すようになっている必要があることがわかります。そうでなければ単一値である予約IDと比較したとき実行時エラーとなってしまいます。SELECT句には列名や式および集計関数などを指定できますが、副問合せの結果セットが複数件になることを考えると、空欄には列名ではなく集計関数が入ると判断できます。
予約明細テーブルの中で削除したいレコードは、"部屋ID"列と"宿泊日"列の値の組が重複するレコード（以下、重複レコード群）のうち、2件目以降に登録されたものです。〔データベースの設計〕には「予約ID，予約明細IDは，レコードを挿入した順に値が大きくなる」とあるので、2件目以降に登録されたレコードは、最初に挿入されたレコードより予約IDの値が大きいことがわかります。言い換えると、最初に挿入されたレコードは、重複レコード群の中で最も小さい予約IDの値を持つということです。そして、削除対象となるレコードは重複レコード群のうち予約IDが最小ではないレコードということになります。
したがって、WHERE句で絞り込んだ結果セットから予約IDの最小値を主問合せ側に返すことにより、主問合せ側で現在のレコードが削除対象であるか否かを判断することが可能です。現在のレコードの予約IDの値（t1.予約ID）が、副問合せから返された予約IDの値より大きければ削除対象ということになります。t2.予約ID のうち最小の値を返したいので、hには集計関数のMIN()を使用した「MIN(t2.予約ID)」が当てはまります。
∴h＝MIN(t2.予約ID)
