🏨 【120点解説】応用情報 データベース完全攻略！宿泊予約システムのSQL・E-R図・制約設計を網羅
応用情報技術者試験（午後問6）対応・実務スキル向上ガイド
🏨 宿泊施設の予約システムとDB設計
本記事で解説するのは、旅館や民宿を管理する「宿泊予約システム」のデータベース設計に関する問題です。 システムの要件定義から、E-R図の作成、さらにはデータ整合性を守るためのSQL実装まで、エンジニアとして必須の知識が網羅されています。
📌 主な検討テーマ
- E-R図の構成： 施設・部屋・予約明細の多重度と外部キーの関係
- 空き室検索ロジック：
NOT EXISTSを用いた相関副問合せの活用 - 不具合への対策： 同時実行による重複予約を防ぐ
UNIQUE制約の実装
応用情報技術者試験（午後）において、データベース分野は得点源になりやすい科目です。 特に「レコードが重複して挿入されてしまう不具合」をSQLと制約でどう解決するかという、実務に即したシナリオへの対応力が試されます。
1. 📊 データベース設計とE-R図の読み取り
設計の第一歩は、データの「関係性（多重度）」を正確に把握することです。
1-1. エンティティ間の関連（1..*）の導出
設問1(a)の解説： 1つの「予約」に対して、宿泊日ごとの「予約明細」が複数紐づきます。業務要件に「部屋の種別ごとに予約する」とあるため、1回の予約アクションには必ず1つ以上の明細が存在します。よって関連は 1対多（1..*） となります。
1-2. 主キーと外部キーの判別
設問1(b)の解説： 部屋テーブルにおける「施設ID」の扱いです。問題文に「部屋IDは全施設を通して一意」という重要な制約があるため、施設IDは主キーの一部である必要はありません。ただし、どの施設に属するかを示す必要があるため、外部キーとして保持します。
2. 🔍 高度なSQL：空き状況照会のテクニック
予約システムにおいて最も負荷がかかり、かつ正確性が求められるのが「空き部屋検索」です。
「予約明細にレコードが存在しない部屋」を探すために
NOT EXISTS を使用します。 WHERE NOT EXISTS (
SELECT * FROM 予約明細 WHERE 予約明細.部屋ID = 部屋.部屋ID
AND 宿泊日 >= :チェックイン AND 宿泊日 < :チェックアウト
)
GROUP BY 施設ID, 部屋種別ID
HAVING COUNT(*) >= :部屋数
設問2のポイント： HAVING 句は、グループ化した後の統計量（この場合は空き部屋の合計数）に対して条件をかけるために必須の構文です。
3. ⚠️ 不具合対応：排他制御とデータの整合性
システムテスト中に発覚した「二重予約」の不具合。これは典型的な並行処理の問題です。
3-1. UNIQUE制約による物理ガード
設問3(1)(2)の解説： アプリケーション側のチェックだけでは、同時実行時に限界があります。データベース側で 「宿泊日」と「部屋ID」の複合UNIQUE制約 を設定することで、物理的に同じ部屋・同じ日の重複を許さない堅牢なシステムにします。
3-2. 重複データのクレンジング（削除SQL）
設問3(3)の解説： 既に発生した重複から「最新（IDが大きい方）」を消すロジックです。副問合せで MIN(t2.予約ID) を取得し、それより大きいIDを WHERE t1.予約ID > ... で抽出します。
🚀 具体的な使用状況の解説（実務での応用）
この設計とSQLは、以下のようなシーンでそのまま応用可能です。
- 在庫管理システム： 特定の日時に「在庫（部屋）」が「出荷（予約）」されていないかを確認するロジック。
- シェアオフィス・会議室予約： 重複予約を防ぐためのデータベース制約の設計。
- イベント申し込み： 定員数（部屋数）に達しているかどうかをリアルタイムで判定。
📝 まとめ：要点チェック
| 項目 | 重要ポイント |
|---|---|
| E-R図 | 多重度（1..*）と、主キー・外部キーの定義ミスに注意。 |
| SQL | NOT EXISTS（不在確認）と HAVING（集計後判定）を使いこなす。 |
| 整合性 | 同時実行不具合は UNIQUE制約 などのDB制約で解決するのが鉄則。 |
これであなたもデータベース・スペシャリストへの一歩をクリア！✨
☕ 周辺知識の整理と出題傾向
今回の問題は、単なるSQLの穴埋めではなく「同時実行制御（排他制御）」という、システム開発における最重要トピックが隠れたテーマでした。
🔍 試験の背景と出題のトレンド
近年の応用情報技術者試験では、本問のように「業務アプリの不具合をDBの制約で解決させる」シナリオが頻出しています。 特に、以下のキーワードはセットで覚えておきましょう。
- ACID特性： データの整合性を保つための4要素（原子性・一貫性・独立性・永続性）。
- デッドロック： UNIQUE制約や更新ロックを多用する際に発生するトレードオフの問題。
- インデックス： 検索速度向上のために必要ですが、制約を増やすと更新速度は低下します。
🚀 具体的な実務への応用
現実の予約システム（航空券やコンサートチケット等）では、今回のようなUNIQUE制約によるガードに加えて、 「楽観的ロック」や「悲観的ロック」といった排他制御技術が組み合わされます。 「どのタイミングでデータを確定させるか」という設計思想は、今回の問題のCOMMITのタイミングを考えることと直結しています。
―― データベースを制する者は、バックエンド開発を制す。 ――
執筆：応用情報対策プロジェクトチーム
試験に出る！「記述解答」定型フレーズ集
応用情報の午後試験では、理由や対策を40文字程度で説明させる問題が頻出します。採点者に「わかっている」と思わせるマジックワードをマスターしましょう。
🎴 データベース重要用語：暗記カード
マウスを乗せる（PC）かタップ（スマホ）で答えが表示されます
※応用情報技術者試験 午後問題対策セクション
⚠️ 合否を分ける「ひっかけ」一問一答
問題文をクリックすると正解が表示されます
問題文に「重複して挿入されてしまう」とあれば、反射的に「UNIQUE制約」や「排他制御（ロック）」を疑いましょう。この「パターンの認識」が午後のスピードアップに繋がります。
📖 登場用語・キーワード完全解説リスト
【データベース設計・構造】
- ● E-R図 (Entity-Relationship Diagram)
- 実体（施設、利用者など）と、その間の関連を可視化する図。データの構造を論理的に整理します。
- ● エンティティ (Entity)
- データの管理対象となる実体。本問では「施設」「部屋」「予約」「予約明細」などが該当します。
- ● 多重度 (カーディナリティ)
- 「1対多（1..*）」などのリレーションシップの量的な関係。設問1(a)の重要ポイントです。
- ● 主キー (Primary Key)
- テーブル内の行を一意に識別する列。重複不可かつNULL不可（非空値制約）が鉄則です。
- ● 外部キー (Foreign Key)
- 他のテーブルの主キーを参照する列。設問1(b)の「施設ID」のように、テーブル間の結合に用いられます。
- ● 属性 (Attribute)
- エンティティが持つ情報の項目。氏名、住所、電話番号などが属性にあたります。
【SQL・問合せ制御】
- ● 相関副問合せ (Correlated Subquery)
- 主問合せ（外側）の1行ごとに、その値を副問合せ（内側）に渡して実行する高度なSQL。設問2の空き室判定で使用されています。
- ● NOT EXISTS
- 副問合せの結果が「空（レコードなし）」の時に真となる述語。予約が入っていない部屋を探すために使われます。
- ● GROUP BY / HAVING句
- 特定の列でグループ化し、その集計結果に対して絞り込み条件（COUNTなど）を指定する機能です。
- ● 埋込み変数 (Bind Variable)
- プログラムからSQLへ渡される動的な値（:チェックイン日付など）。安全かつ効率的なクエリ実行に寄与します。
- ● 自己結合 (Self Join)
- 同一のテーブルを仮想的に2つ（t1, t2）として扱い比較すること。設問3(3)の重複削除SQLで活用されています。
【整合性・排他制御】
- ● UNIQUE制約 (一意識別制約)
- 列内の値の重複を禁止するデータベースの機能。重複予約（設問3）を解決する物理的な最終防衛線です。
- ● 複合キー (Composite Key)
- 複数の属性を組み合わせて、一意性や制約を持たせること。「部屋ID×宿泊日」の組み合わせを一意にする際に使われます。
- ● トランザクション (Transaction)
- 一連の処理をひとまとまりとして扱う単位。予約確定からコミットまでの処理がこれに該当します。
- ● コミット (COMMIT)
- 処理が全て正常終了した際に、変更をデータベースに永続的に反映させる操作です。
- ● 競合状態 (Race Condition)
- 複数のユーザーが同時に操作することで、意図しないデータ重複（二重予約）が発生する現象。実務上の重大なバグです。
- ● 参照整合性制約
- 外部キーが参照する主キーの値が必ず存在することを保証するルール。データの矛盾を未然に防ぎます。
「主キーと外部キーの違い」「WHEREとHAVINGの実行タイミング」などは、午前試験でも非常によく狙われます。
このリストの用語が頭に入っていれば、午後問題の文脈理解がぐっと楽になります！
この記事へのコメント