応用情報H30秋「入室管理システム」を完全攻略：ER図・複合キー・COUNT判定・自己結合を総合解説

応用情報技術者試験の午後問題、その中でもデータベース分野は、多くの受験者が苦戦する一方で、実務に直結する重要な知識が問われる領域です。特に平成30年秋季の問2「入室管理システム」は、単なるSQLの知識だけでは解けない、データベースの”なぜ”を深く理解しているかが試される名作問題と言えるでしょう。ER図の線の意味から、複合主キーの存在理由、COUNT関数を使った巧妙な条件判定、そして自己結合による階層構造の展開まで、内容は多岐にわたります。本記事では、この「入室管理システム」を題材に、各設問の背後にあるデータベースの原理原則を一つひとつ丁寧に解き明かしていきます。「なんとなく正解できた」から「論理的に説明できる」レベルへ引き上げることを目標に、豊富な図解と具体例を交えながら、データベース設計とSQL操作の核心を完全攻略していきましょう。

設問①徹底解説：ER図の線が示す「参照整合性」と外部キーの役割

応用情報技術者試験のデータベース問題では、最初にER図（実体関連図）の読解が求められることが頻繁にあります。設問1は、まさにその読解力を試す問題でした。エンティティ間を結ぶ「線」が一体何を意味するのか、その本質を理解することが攻略の鍵です。結論から言うと、ER図の線はエンティティ間の「参照関係」、つまり外部キーによるテーブルの繋がりを表しています。

線を引くのは「外部キー」が存在するときだけ

ER図に描かれる実線や破線は、単なる関連性を示唆するものではありません。これは、一方のテーブル（子テーブル）が、もう一方のテーブル（親テーブル）の主キーを「外部キー」として自身の列に保持し、それによってデータの整合性を担保している状態（参照整合性制約）を明示するものです。

例えば、今回の問題における「社員」テーブルと「入室許可」テーブルの関係を見てみましょう。

【社員テーブル】
社員ID (主キー), ...

【入室許可テーブル】
社員ID (外部キー), 室ID (外部キー), ...

「入室許可」テーブルは、どの社員に許可を与えるかを示すために「社員ID」を持っています。この「社員ID」は、「社員」テーブルに実際に存在する社員IDでなければなりません。この制約があるからこそ、両テーブルを線で結ぶことができるのです。

身近な例で言えば、これはスマートフォンの電話帳とメッセージアプリの関係に似ています。メッセージアプリでやり取りする相手は、必ず電話帳に登録されている（あるいは一時的に番号が記録されている）人物ですよね。存在しない相手にメッセージを送れないように、データベースでも親テーブルに存在しないデータを参照する子テーブルの行は作成できない、というルールがこの「線」によって保証されています。

「入室許可」と「入退室ログ」が直接結ばない理由

では、なぜ問題のER図では「入室許可」と「入退室ログ」が直接線で結ばれていないのでしょうか。一見すると、「許可された人がログを残す」のだから関係がありそうに思えます。

ここが重要なポイントで、データベース設計では「データとして直接参照する必要があるか」という観点でのみ線を引きます。両者の関係を整理してみましょう。

「入退室ログ」テーブルは、ログが記録された時点で必要な情報（カードID、室ID、日時）がすべて揃っています。後から「この入室は許可されていたか？」をチェックする処理は発生するかもしれませんが、それはアプリケーション側のロジックです。「入退室ログ」テーブルの構造として、「入室許可」テーブルの情報を外部キーとして保持する必要はないのです。

もし無理に両者を結ぶと、許可情報が変更された際にログとの整合性が取れなくなるなど、かえってデータ構造が複雑化してしまいます。必要な情報が他のテーブル（今回はカードや室）経由で間接的に取得できるなら、直接の線は引かないのが正規化の原則です。

ER図は「処理」ではなく「データの構造」を表す

この問題は、私たちにER図の根本的な役割を再認識させてくれます。ER図は「データの流れ」や「システムの処理（Aの後にBを実行する）」を描くフローチャートではありません。あくまでデータの静的な「構造」と「関係性」を示す、いわばデータベース世界の設計図です。

例えば、家の建築設計図は、リビングとキッチンの位置関係（構造）を示しますが、「朝起きたらまずリビングに行き、次にキッチンでお茶を淹れる」といった人の動き（処理）は描きません。ER図も同じです。「入室許可」テーブルと「入退-室ログ」テーブルが直接結ばないのは、家の設計図で言えば、リビングと寝室の間に直接のドアがないようなもの。廊下（他のテーブル）を介して行き来できるなら、構造として直接繋ぐ必要はない、という考え方なのです。

設問1は、この「構造と処理の分離」というデータベース設計の基本原則を理解しているかを問う、非常に優れた問題だったと言えるでしょう。

設問②で学ぶ複合主キー：なぜ「社員ID＋室ID」で一意性を担保するのか

設問②は、データベース設計の核心とも言える「主キー」の概念、特に複数の列を組み合わせる「複合主キー」の必要性を問う問題です。なぜ「入室許可」テーブルでは、単一のIDではなく「社員ID」と「室ID」の2つをセットで主キーにする必要があるのでしょうか。その答えは、「社員」と「室」の間に存在する“多対多”の関係性にあります。

「入室許可」は“多対多”を解決する中間テーブル

まず、「社員」と「室」の関係性を整理しましょう。

• 一人の社員は、複数の室への入室を許可されることがある。（例：Aさんはサーバ室と資料室に入れる）
• 一つの室は、複数の社員に入室が許可されている。（例：サーバ室にはAさん、Bさん、Cさんが入れる）

このように、互いに「多」の関係にある状態を「多対多（many-to-many）」と呼びます。リレーショナルデータベースでは、この多対多の関係を直接表現することはできません。そこで登場するのが、両者の間に挟まる「中間テーブル（連関エンティティ）」です。

今回の「入室許可」テーブルは、まさにこの中間テーブルの役割を担っています。

【社員テーブル】 ----> (1対多) ----> 【入室許可テーブル】
【室テーブル】   ----> (1対多) ----> 【入室許可テーブル】

上図のように、「社員」と「入室許可」は1対多、「室」と「入室許可」も1対多です。この中間テーブルを設けることで、複雑な多対多の関係を、データベースが扱えるシンプルな「1対多」の組み合わせに分解しているのです。

単一キーでは業務要件を表現できない

では、なぜ「入室許可」テーブルの主キーは一つではダメなのでしょうか。具体的に考えてみましょう。

• もし「社員ID」だけを主キーにしたら？
  主キーは重複が許されないため、「社員ID 'E001'」の行は1行しか作れません。つまり、社員E001さんは、どれか1つの室の許可しか登録できず、複数の室への入室許可を与えられなくなります。
• もし「室ID」だけを主キーにしたら？
  同様に、「室ID 'S101'」の行が1行しか作れません。つまり、S101室には、誰か1人の社員しか入室許可を与えられなくなります。

どちらのケースも、実際の業務要件（「一人の社員が複数拠点に入れる」「一つの拠点に複数が所属する」など）を満たせません。

「社員ID＋室ID」の組み合わせで初めて一意になる

そこで複合主キーの出番です。複合主キーとは、複数の列を一つのセットとして扱い、その「組み合わせ」の重複を許さないという制約です。

「入室許可」テーブルでは、「どの社員が、どの室への入室を許可されているか」という情報そのものが1つのレコードの意味を持ちます。

この表では、

• E001 という社員IDは複数回登場できます。
• S101 という室IDも複数回登場できます。
• しかし、(E001, S101) という組み合わせは、このテーブル内で一度しか登場できません。

これにより、「一人の社員が複数の室の権限を持つ」「一つの室に複数の社員が権限を持つ」という多対多の関係を、データの整合性を保ちながら完璧に表現できるのです。SQLでテーブルを定義する際は、以下のように記述します。

CREATE TABLE 入室許可 (
    社員ID VARCHAR(10),
    室ID VARCHAR(10),
    許可日時 DATE,
    ...,
    PRIMARY KEY (社員ID, 室ID) -- ここで複合主キーを定義
);

複合キーは「正規化」の自然な結果

データベース設計には「正規化」という、データの重複をなくし、整合性を高めるための重要なプロセスがあります。詳細は割愛しますが、この正規化を進める過程で多対多の関係が見つかると、それを解消するために中間テーブルが作られます。そして、その中間テーブルの主キーは、元となった2つのテーブルの主キーを引き継いだ複合主キーとなるのが最も自然な形なのです。

これは実世界の業務でも頻繁に現れます。例えば、「顧客」と「商品」の関係を管理するECサイトの「購入履歴」テーブルは、どの顧客がどの商品を買ったかの記録であり、通常は「顧客ID」と「商品ID」の複合主キー（場合によっては購入日時も含む）を持ちます。設問②は、このデータベース設計の定石を理解しているかを問う問題だったのです。

ER図の読解を深める：カーディナリティ（多重度）とテーブルの依存関係

ER図をさらに深く読み解くためには、「カーディナリティ」と「依存関係」という2つの概念を理解することが不可欠です。カーディナリティ（多重度）とは、エンティティ間の関係が「1対1」なのか、「1対多」なのか、「多対多」なのかを示す数の対応関係を指します。そして、この関係性はテーブル間の「依存関係」によって成り立っています。

「1対多」は“多”側が“1”側を参照して成り立つ

データベースにおけるテーブル間の関係で最も一般的なのが「1対多」です。この関係は、どのようにして成立するのでしょうか。それは、「多 (N)」側のテーブルが、「1」側のテーブルの主キーを外部キーとして持つという構造的なルールに基づいています。

今回の問題における「社員」と「カード」の関係が典型例です。

• 1人の社員は、複数のカードを所持する可能性がある（紛失・再発行など）。
• 1枚のカードは、特定の1人の社員にしか紐づかない。

この業務ルールをデータベースで実現するために、以下のような設計が行われます。

【社員テーブル】 (1側)
  社員ID (主キー)
  氏名
  ...

【カードテーブル】 (多側)
  カードID (主キー)
  社員ID (外部キー)  <-- ここで社員テーブルを参照
  発行日
  ...

「カード」テーブルが外部キーとして「社員ID」を持つことで、「このカードは、どの社員のものか」という情報が明確になります。これにより、「社員」を親、「カード」を子とする親子のような関係、すなわち依存関係が生まれるのです。

依存関係がなければカーディナリティは存在しない

前述の「依存関係」とは、より厳密に言うと「子テーブルのレコードは、親テーブルに対応するレコードが存在しない限り、存在できない」という制約（参照整合性制約）を意味します。

例えば、まだ「社員」テーブルに登録されていない「社員ID: E999」を持つカード情報を、「カード」テーブルにいきなり登録しようとすると、データベースは「参照先が存在しません」というエラーを返します。これが依存関係です。カードは、社員という存在に依存しているのです。

この関係は、ECサイトにおける「注文」と「注文明細」の関係に似ています。

• 注文：「2025年10月7日の田中様の購入」という取引全体。
• 注文明細：「りんご2個」「みかん3個」といった取引の各品目。

「どの注文にも属さない、謎の注文明細」が存在し得ないように、データベースの世界でも「どの社員にも紐づかない、謎のカード」は存在できません。ER図に描かれる線（リレーションシップ）は、単なる関連性ではなく、このようなデータの存在をかけた強い依存関係を表しているのです。

複合キーを持つテーブルは“両親”に依存する

では、設問②で登場した「入室許可」テーブルはどうでしょうか。このテーブルは「社員ID」と「室ID」の複合主キーを持っていました。これは、2つの外部キーを併せ持つことを意味します。

【社員テーブル】 ----> 参照される ----> 【入室許可テーブル】
【室テーブル】   ----> 参照される ----> 【入室許可テーブル】

この構造が示すのは、「入室許可」テーブルが2つの親テーブル（社員、室）に同時に依存しているということです。

• 「入室許可」レコードの社員IDは、「社員」テーブルに実在するIDでなければならない。
• 「入室許可」レコードの室IDは、「室」テーブルに実在するIDでなければならない。

つまり、「存在しない社員」や「存在しない室」に対する入室許可情報は、絶対に登録できない仕組みになっています。中間テーブルは、このように複数の親テーブルの“身元”を確認することで、データ全体の整合性を保つハブの役割を果たしているのです。この「多方面への依存」を理解することが、複雑なデータベース構造を正確に把握する鍵となります。

設問③SQLトリック解説：INNER JOINとCOUNT(*)で「存在確認」する論理

設問③は、本試験の核心とも言える複雑なSQL読解問題です。提示されたSELECT文が、どのようにして「入室許可のある社員・室の組み合わせに対し、入室実績があれば『1』を、なければその行自体を返さない」という処理を実現しているのか。その鍵はINNER JOIN、GROUP BY、COUNT(*)、そしてHAVING句の連携にあります。このSQLの動作を一つずつ分解していきましょう。

目的：「社員×室」の組み合わせごとに入室実績があるかを確認する

まず、このSQLが達成したい目的を明確にしておきましょう。目的は非常にシンプルです。
「『入室許可』テーブルに登録されているすべての権限（社員IDと室IDのペア）について、それに対応する『入退室ログ』が過去に1件でもあるかどうかを判定したい」
というものです。最終的に欲しいのは、「実績のあった権限リスト」ということになります。

SQL構文の分解（問題の再現）

問題で問われているSQLの骨子を再現すると、以下のようになります。

SELECT
    T1.社員ID,
    T1.室ID,
    COUNT(*) AS 実績     -- ③ グループごとの件数を数え、「実績」とする
FROM
    入室許可 AS T1
INNER JOIN
    入退室ログ AS T2 ON ... -- ① 許可とログを結合し、「実績のある」ペアのみに絞る
GROUP BY
    T1.社員ID, T1.室ID      -- ② 社員と室の組み合わせでグループ化する
HAVING
    COUNT(*) >= 1           -- ④ グループ化した結果、件数が1件以上のものだけを残す

このSQLは、番号を振った①〜④の順で論理的に処理されていきます。各ステップの役割を見ていきましょう。

INNER JOIN：実績のある組み合わせだけを抽出する

最初のステップはINNER JOIN（内部結合）です。INNER JOINは、指定された条件（ON句）に一致する行が両方のテーブルに存在するデータだけを結果として返します。

• 入室許可テーブルには、「社員E001は室S101に入れる」という権限情報があります。
• 入退室ログテーブルには、過去の入退室記録が蓄積されています。

INNER JOINでこの2つを結合すると、「入室許可テーブルに権限があり、かつ、入退室ログテーブルに対応する入室記録が実際に存在する」組み合わせだけが中間結果として残ります。逆に入室許可はあっても一度も入室したことのない（ログがない）組み合わせは、この段階で跡形もなく消え去ります。

COUNT(*) と GROUP BY：「組み合わせごと」に件数を集計する

次にGROUP BY T1.社員ID, T1.室IDが実行されます。これは、INNER JOINを通過した結果セットを、「社員IDと室IDのペア」でグループ分けする命令です。例えば、(E001, S101)のペアがログに3回記録されていれば、その3行が1つのグループにまとめられます。

そして、SELECT句のCOUNT(*)が、このグループごとに行数をカウントします。

• (E001, S101) のログが3回あれば、COUNT(*)は 3 となります。
• (E002, S205) のログが10回あれば、COUNT(*)は 10 となります。

ここでのポイントは、INNER JOINを経由しているため、COUNT(*)の結果が 0 になることは絶対にない、ということです。COUNT(*)が計算される対象は、すでに入室実績があるデータだけだからです。

HAVING句：集計結果を最終フィルタリングする

最後にHAVING COUNT(*) >= 1句です。HAVINGは、GROUP BYで集計された後の結果に対して適用されるフィルターです。WHERE句が集計前の個々の行を絞り込むのに対し、HAVING句は「合計が1000以上のグループのみ」や「平均点が80点以上のクラスのみ」といった、集計結果に対する条件を指定します。

今回のHAVING COUNT(*) >= 1は、「グループごとの件数が1件以上のもの」という条件ですが、前述の通りINNER JOINによって実績のない組み合わせは消えているため、すべてのグループがこの条件を満たします。これは、SQLの処理として念のため記述されている、あるいは他のJOIN（LEFT JOINなど）を使った場合でも同じ結果を得られるように汎用性を持たせた記述と解釈できます。

設問④ 自己結合(Self Join)完全理解：組織表T1, T2, T3の階層展開トリック

最後の設問④は、多くの受験者が苦手とする「自己結合（セルフジョイン）」に関する問題です。1つのテーブルをT1, T2, T3といった異なる別名を付けて何度も結合するのはなぜか。これは、テーブル内に存在する「階層構造」の情報を、1行のレコードとしてフラットに展開するための、SQLにおける非常に強力なテクニックなのです。

「組織」テーブルは自分自身を参照する「自己参照構造」

まず、「組織」テーブルの構造的特徴を理解する必要があります。このテーブルには、各組織を識別する組織ID（主キー）と、その組織が所属する一つ上の組織を示す上位組織ID`（外部キー）が含まれています。

FROM
    組織 AS T1
INNER JOIN
    組織 AS T2 ON T1.上位組織ID = T2.組織ID
INNER JOIN
    組織 AS T3 ON T2.上位組織ID = T3.組織ID