応用情報技術者 2020年 秋期 午後 問06

解答の導き方

1. [a] の決定
   図中の説明に「予約の確定の処理では、予約のレコードを挿入した後、各宿泊日について、予約明細に必要な部屋数分のレコードを挿入する」という記述があります。これは一つの予約（予約）が複数の予約明細（予約明細のレコード）を持つことを意味します。E-R図の凡例に「矢印 —→ ：1対多」とあるため、予約から予約明細への関連は1対多です。よって図2中の空欄 [a] には1対多を示す矢印を入れます。
   最終的に [a] = → です。
2. [b] の決定
   図2に「施設 —→ 部屋（1対多）」とあり、部屋エンティティの属性欄に空欄 [b] が存在します。リレーショナルデータベースで1対多の関連を実装する基本ルールは「多側の表に一側の主キーを外部キーとして持たせる」ことです。したがって、部屋表（多側）には施設側の主キーである施設IDを属性として持たせる必要があります。問題文にも属性一覧に「施設ID」が存在することを示す文脈があるため、[b] に入る属性名は 施設IDです。
   最終的に [b] = 施設IDです。

• [a]：→
• [b]：施設ID

誤りやすいポイント

• 予約と予約明細の関係を逆に考える
  「予約明細が多数の予約を持つ」と解釈しやすいが、本文の「各宿泊日について、予約明細に必要な部屋数分のレコードを挿入する」を読むと一つの予約に対して複数の予約明細が入るため、方向は予約→予約明細（1対多）である。
• 部屋IDが全体で一意だから施設IDは不要と誤解する
  部屋IDが全施設で一意であっても、E-R上の「施設 —→ 部屋（1対多）」を関係データベースで実装する際には多側の表（部屋）に一側の主キー（施設ID）を外部キーとして持たせるのが標準であり、参照整合性やクエリの明確化のために施設IDを属性として置くのが適切である。
• 表記ルール・凡例の読み落とし
  図2の凡例で「矢印 —→ ：1対多」と示されている点を見落とし、別の記号や用語で埋めてしまうことがある。
• 属性名の混同
  部屋種別IDや部屋番号など別の属性名を[b]に入れてしまう。空欄[b]は施設との関連を表す位置にあるため、施設IDが適切である。

FAQ

解答の論理構成

1. 部屋の空きを判定する要件
   • 本文に「部屋を予約する際には、…チェックイン日付、チェックアウト日付、部屋数について、空き状況の照会を行う。」とあります。
   • また「図3のSQL文の検索結果として、レコードが返された場合に予約可能であると判定」とあり、返るレコードは “空いている部屋種別” を示す必要があります。
2. c の導出 ―― 既存予約と重複しない部屋を抽出
   • 図3のSQLでは部屋表を外側に置き、「予約明細」表をサブクエリで参照しています。
   • 重複がある部屋は除外するために、サブクエリに該当レコードが“存在しない”ことを条件にします。
   • SQLで存在しないことを表す代表的な述語は NOT EXISTS です。
   • よって c には NOT EXISTS が入ります。
3. d の導出 ―― 希望部屋数を満たすグループのみ返却
   • 外側の SELECT では「施設ID, 部屋種別ID, COUNT(*)」を取得し、その直後に「GROUP BY 施設ID, 部屋種別ID」が置かれています。
   • グループ化の結果に対して行数（＝空いている部屋数）が “:部屋数” 以上かどうかを判定する必要があります。
   • 集計後の行数に条件を付けるには HAVING 句を使います。
   • したがって d に入るのは HAVING COUNT(*) です。
4. 以上より
   • c：NOT EXISTS
   • d：HAVING COUNT(*)

誤りやすいポイント

• サブクエリを NOT IN で書こうとしてNULLを含むケースを見落とす。NOT EXISTS を使うとNULL問題を回避できます。
• HAVING を WHERE に置き換えてしまう。WHERE はグループ化前に評価されるため、COUNT(*) などの集計結果を直接使えません。
• HAVING COUNT() >= :部屋数 の COUNT() を別名で受け取り、その別名を HAVING で参照しようとしてエラーになるケース。集計関数そのものを HAVING 句に書く方が安全です。

FAQ

解答の論理構成

• 【問題文】に「同じ宿泊日の同じ部屋について、予約明細のレコードが重複して挿入されてしまう不具合が報告された」とあります。
  └ 重複の判定条件は “同じ宿泊日” かつ “同じ部屋” であることが明示されています。
• 同じ箇所で「予約明細テーブルのe列とf列の複合キーに対して制約を追加することにした」と記述されています。
  └ つまり、重複防止用のユニーク制約（複合キー）を掛ける対象列がeとfです。
• 予約明細テーブルに存在し、かつ重複判定で使われる列は “宿泊日” と “部屋ID” の二つしかありません。
  └ “予約ID” や “予約明細ID” は予約ごと・明細ごとの識別子であり、同一部屋・同一宿泊日の重複を検知できません。
• 以上より、e と f には
  ・宿泊日
  ・部屋ID
  を設定すれば、同一部屋・同一宿泊日に同じレコードが二度入ることをユニーク制約で防止できます。

誤りやすいポイント

• 予約IDや予約明細IDをキーに含めてしまう
  → これらはそもそも重複してはいけない主キー属性であり、今回の「部屋×宿泊日」の重複を防げません。
• チェックイン日付／チェックアウト日付を選んでしまう
  → 予約明細は宿泊日ごとに1レコードを持つ設計なので、期間情報ではなく “宿泊日” が必要です。
• キーの順序を気にし過ぎる
  → SQLレベルのユニーク制約では複合キー内の列順は一意性判定に影響せず、部屋ID→宿泊日でも宿泊日→部屋IDでも同じ効果です。

FAQ

解答の論理構成

1. まず問題文中に
   “予約明細テーブルのe列とf列の複合キーに対して制約を追加することにした。このような制約のことを、gという。”
   と明記されています。
2. 目的は “同じ宿泊日の同じ部屋について、予約明細のレコードが重複して挿入されてしまう不具合” を防ぐことです。
3. すなわち “e列とf列” （＝部屋IDと宿泊日） の組み合わせがテーブル内で重複しないようにする必要があります。
4. RDBで列または列集合の重複を禁止する制約は “UNIQUE制約” で、主キー以外の列にも設定できます。
5. よって “g” に入る適切な語句は “UNIQUE制約” となります。

誤りやすいポイント

• 主キーと混同する
  UNIQUE制約は重複禁止ですがNULLを許容でき、主キーとは異なるという点を忘れがちです。
• CHECK制約との取り違え
  CHECK は値の範囲や論理式を検証するだけで、行間の重複は検出できません。
• インデックスと制約の違い
  UNIQUEインデックスと結果的に同じ動作になるDBもありますが、問題では “制約” と明言されているため UNIQUE制約と答える必要があります。

FAQ

解答の論理構成

1. 目的の整理
   不具合修正では、 「同じ宿泊日の同じ部屋の予約明細のレコードについて、最初に挿入された予約のレコードと、それに紐づく予約明細のレコードを残し、それ以外の予約明細、予約のレコードを削除する」
   ことが求められています。
2. “最初に挿入された” かどうかの判定軸
   問題文には「予約ID、予約明細IDは、レコードを挿入した順に値が大きくなる。」とあります。
   したがって、同一の部屋・宿泊日で最小の 予約IDが “最初の予約” になります。
3. 取得したいレコード
   ・外側のt1は “削除候補” を示します。
   ・内側の副問い合わせt2は “同じ部屋・同じ宿泊日の中で最小（＝先頭）の予約ID” を返します。
   ・t1.予約ID > (SELECT …) という比較で、先頭レコードより後に挿入された重複分だけを抽出します。
4. 字句の対応
   (1) h は、同じグループ内で最小の 予約IDを取得する集約関数 ― MIN(t2.予約ID)
   (2) i と j は “同じ部屋” と “同じ宿泊日” を示す等価条件
   ・t1.部屋ID = t2.部屋ID
   ・t1.宿泊日 = t2.宿泊日
   順不同なのでi・jいずれに置いても成立します。
5. 結論
   h：MIN(t2.予約ID)
   i：t1.部屋ID = t2.部屋ID
   j：t1.宿泊日 = t2.宿泊日

誤りやすいポイント

• “最初に挿入” を 予約明細IDで比較してしまう
  → 要件では “予約IDで判断する” と明示されています。
• 集約関数を MIN(*) や MIN(予約明細ID) にしてしまう
  → 副問い合わせは先頭の予約を探すので、対象列は必ず 予約IDです。
• 等価条件を >= や > に書き換えてしまう
  → グループ化のキーは完全一致でなければ同一レコードとみなせません。

FAQ