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

解答の論理構成

1. 図2におけるa
   • エンティティ間の関連は記号で表します。
   • 予約と予約明細は「1つの予約に対し、複数行の予約明細が存在し得る」という要件から「1 対 多」です。
   • 図2の凡例で「1対多」は “—→” を用いると示されています。
   • よって a には “→” を入れ、「予約 —→ 予約明細」となります。
2. 図2におけるb
   • 部屋は必ずいずれかの施設に属します。
   • 施設を識別する主キーは “施設ID” です。
   • 部屋から施設を参照するための外部キーが必要であり、図2の注記で「外部キーは破線下線で表記する」とあるため、b には “施設ID” が入ります。
3. 以上より模範解答と一致します。

誤りやすいポイント

• 「1対多」の向きを逆に書く
  予約明細から予約へ “←—” としてしまうミスが頻発します。
• 外部キー表記の線種
  実線下線は主キー、破線下線は外部キーです。“施設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