データベーススペシャリスト試験 2024年 午後1 問03

模範解答のキーワードと論点整理

解答の理由解説

1. (a) 組織長従業員コード
   問文より，最初の手順は

   「図 2 中から、(a)を外部キーとする指定を削除した CREATE TABLE 文を実行する。」
   とあります。図2のCREATE TABLE文には，以下の２つの外部キー制約があります。

   FOREIGN KEY (組織長従業員コード) REFERENCES 従業員（従業員コード） ON DELETE RESTRICT,
   FOREIGN KEY (上位組織コード)    REFERENCES 組織（組織コード）      ON DELETE RESTRICT
   

   未定義の他表を参照しているのは「従業員」を参照する前者のみなので，削除対象は「組織長従業員コード」です。
2. (b)，(c)，(d) の順序
   問文では，

   ② “従業員”, “役職”, “時間単価”, “ランク” の各テーブルを定義する CREATE TABLE 文を“(b)”, “(c)”, “役職”, “d”の順番で実行する。
   とあります。各テーブルの依存関係は以下の通りです。

   • ランク ← 参照なし
   • 時間単価 ← ランク，組織
   • 役職 ← ランク
   • 従業員 ← 役職，組織
   したがって依存先のテーブルから順に作成すると，
   1. ランク
   2. 時間単価
   3. 役職
   4. 従業員
   となり，(b)=「ランク」，(c)=「時間単価」，(d)=「従業員」となります。
3. (e) ALTER TABLE
   最後の手順は，

   ③ “組織” テーブルに対する(e)文を用いて, (a)を外部キーとする指定の定義を追加する。
   既存のテーブル定義に外部キー制約をあとから追加するには，ALTER TABLE … ADD CONSTRAINT … を用いるため，(e)は ALTER TABLE です。

受験者が誤りやすいポイント

• 自己参照制約
  図2の外部キーには「上位組織コード」→「組織」自身を参照するものもありますが，自己参照はCREATE TABLE文内で許容されるため，最初に削除すべきは「従業員」参照側の制約です。
• テーブル作成順序
  各テーブルが参照する外部キー先を把握せずに，画一的に「従業員」→「役職」→…と並べてしまうと，依存関係違反で生成に失敗します。
  → 各テーブルがどの参照制約を持つかを整理して，依存度の低いものから順に作成することが重要です。
• 制約の再追加方法
  ALTER TABLE と CREATE TABLE の使い分けを間違えやすい点です。既存テーブルに後から外部キーを設定する場合は必ず ALTER TABLE を用います。

試験対策として覚えておくべきポイント

1. 外部キー制約の依存関係
   テーブル間の参照関係を整理し，「参照される側」を先に作成，「参照する側」は後で作成する。
2. 自己参照（自己結合）
   自己参照制約は同一CREATE TABLE文内で定義可能。したがって最初に削除すべきは他表への参照制約であることを押さえる。
3. ALTER TABLE の使い方
   • テーブル定義の追加・変更（制約追加／削除）は ALTER TABLE
   • 新規テーブル作成は CREATE TABLE
4. 外部キー制約時のオプション
   ON DELETE RESTRICT などの動作指定を忘れずに記述できるようにしておく。

キーワード・論点の整理

• 組織長従業員コード
  ・「FOREIGN KEY (組織長従業員コード) REFERENCES 従業員（従業員コード） ON DELETE RESTRICT」
  ・外部キー制約のため、挿入時に参照先の従業員コードが存在しないとエラー
  ・NULL 許容（参照先が未確定のままでも挿入可能）
• 上位組織コード
  ・「FOREIGN KEY (上位組織コード) REFERENCES 組織（組織コード） ON DELETE RESTRICT」
  ・自己参照の外部キー
  ・親となる組織コードが事前に存在している必要がある

解答の理由づけ

1. 【組織長従業員コード】
   図2 に示された定義より、

   FOREIGN KEY (組織長従業員コード) REFERENCES 従業員（従業員コード） ON DELETE RESTRICT
   とあり、挿入時には参照先となる「従業員」テーブルの当該従業員コードが存在しなければ外部キー違反となります。
   　しかし、組織を新設するタイミングでは組織長の従業員が未確定の場合もあるため、
   挿入時に NULL を設定しておくことで、外部キー制約を満たしつつレコードを登録できます。

2. 【上位組織コード】
   表1 によれば

   「組織は階層構造であり,最上位の組織以外はいずれか一つの上位組織に属する。」
   とあり、図2 に
   FOREIGN KEY (上位組織コード) REFERENCES 組織（組織コード） ON DELETE RESTRICT
   の自己参照外部キーがあります。
   自己参照外部キーは、挿入時に 参照先（親組織）が既にテーブル内に存在 していなければ整合性違反となるため、
   最上位組織から順に階層を下って登録する必要があります。

誤りやすいポイント

• 組織長従業員コード を「空文字」「不明コード」などで埋めようとして、外部キー違反を起こす
• 上位組織コード を子組織から登録し、まだ登録されていない親組織コードを指定してしまう
• 外部キー制約の「ON DELETE RESTRICT」は削除時の挙動を規定するもので、挿入時の参照有無とは区別して理解する必要がある

試験対策ポイント

• 外部キー制約の基本ルール：
  ① 挿入・更新時には参照先の主キーが既に存在するか、または NULL であること
  ② 削除時の動作（RESTRICT, CASCADE など）を正しく把握
• 自己参照外部キー を持つテーブルでは、親→子の順でレコードを登録
• 「最上位組織の上位組織コードには NULL を設定する」という業務ルールも合わせて確認すること

1. 模範解答の核心となるキーワード・論点整理

2. 解答に至る論理的説明

1. 「時間単価」テーブルの定義では，外部キーである「組織コード」のDELETEオプションにCASCADEを指定している。
   問題文より

   “時間単価” テーブルの定義では,外部キーである “組織コード” のDELETE オプションを CASCADE に指定した。

2. SET NULLを指定した場合
   • 外部キー制約で親の行削除時に子の「組織コード」をNULLに更新しようとする
   • 一方，該当列は「時間単価」テーブルの主キーの一部（NOT NULL）であるため，NULLを設定できず，制約違反になる
   • したがって
     “時間単価” テーブルの組織コードは主キーの一部であり NULL に変更できないから
3. RESTRICTを指定した場合
   • RESTRICTは，親（組織）行を削除する際に子（時間単価）テーブルに対応する参照行が存在すると削除自体を禁止する
   • 組織コードごとに時間単価を設定しているため，多くの場合子側に該当行が残っており，削除時にエラーとなる
   • したがって
     “時間単価” テーブルに同じ組織コードの行が存在する場合があるから

3. 受験者が誤りやすいポイント・ひっかけ

• 「SET NULLだから常にNULL許容が前提」と考えがちだが，外部キー列そのものがNOT NULLの主キーであればSET NULLはそもそも不可能です。
• RESTRICTとNO ACTIONの違いを混同し，「定義時にエラーになる」と誤認する場合があります。RESTRICTは行削除時の動作であり，定義時ではなく削除実行時に制約違反が発生します。
• 「CASCADE→子も削除」「RESTRICT→親は削除できない」が基本動作ですが，設問のポイントは「いつ」「なぜ」エラーとなるかを正確に把握することです。

4. 試験対策として覚えておくべきポイント

• 外部キー制約のDELETEオプションには主に以下がある
  • CASCADE：親削除時に子も削除
  • SET NULL：親削除時に子の該当列をNULLに更新（NULL許容が前提）
  • RESTRICT（またはNO ACTION）：子行がある場合は親削除を禁止
• SET NULLを使う場合，必ず外部キー列がNULLを許容するかどうか（NOT NULL制約）を確認する
• RESTRICTは「データ操作時の制約違反」であり，参照行が残っていると削除できない
• 外部キー制約の動作と対象列の制約（主キー・NOT NULLなど）の両面を意識して設計・検証を行うこと

キーワード・論点整理

• ウィンドウ関数（NTILE、RANK）
  • PARTITION BY：グループ単位（ここでは組織ごと）に番号振り
  • ORDER BY：順位付けに使用する値（ここでは計画時間の合計）
• NULL を含む行も扱うための外部結合
  • 従業員全員を対象にし、稼働計画がない従業員には計画時間を 0 として扱う
• 別名（エイリアス）と集計関数
  • 同一 SELECT リスト内では、集計関数の結果に付けた別名（計画時間合計）はウィンドウ関数の ORDER BY 内で再利用できない
  • そのため COALESCE(SUM(計画時間),0) を直接記述

解答とその理由

FROM    ウ：従業員 A
        LEFT OUTER JOIN エ：稼働計画 B
          ON A.従業員コード = B.従業員コード
         AND 計画年 = '2024'
         AND 計画月 = '11'
…
NTILE(3) OVER (
    PARTITION BY ア：組織コード
    ORDER BY     イ：COALESCE(SUM(計画時間),0)
) AS 時間階級,
RANK() OVER (
    ORDER BY イ：COALESCE(SUM(計画時間),0)
) AS 時間ランク

各ブランクの選択理由

1. ウ＝従業員
   • 「従業員を引き継いで、…稼働計画に登録されていない従業員を含めて要員を計算する」
     → 稼働計画の有無に関わらずすべての従業員をベースにするには、従業員テーブルを外表にした LEFT OUTER JOIN が必須。
2. エ＝稼働計画
   • JOIN 句で参照するのは 計画時間 や 計画年・計画月 を持つテーブル → 稼働計画 テーブル。
3. ア＝組織コード
   • 「組織ごとに計画時間の少ない順に順位付けし、…番号を付与」
     → PARTITION BY には組織ごとのグルーピングに使う列を指定。
4. イ＝COALESCE(SUM(計画時間),0)
   • GROUP BY で従業員ごとに SUM(計画時間) を算出後、NULL（計画なし時）を 0 に置き換え
   • ウィンドウ関数の ORDER BY には集計式をそのまま書く必要がある（列別名は使えない RDBMS が多いため）

受験者が誤りやすいポイント

• ● ウィンドウ関数内で列別名を再利用
  「ORDER BY 計画時間合計」と書きたくなるが、SQL 標準や多くの RDBMS では同一レベルの SELECT リストで定義した別名を参照できないことがある。
• ● PARTITION BY の単位
  「従業員コードで分ける」とすると１人あたり１行になり NTILE の意味が失われる。
• ● テーブルの選択
  「稼働実績」を使うと日別のデータになり、月間合計の集計とは異なる。

試験対策として覚えておくべきポイント

1. ウィンドウ関数の基本
   • PARTITION BY と ORDER BY の役割
   • 同一 SELECT 内での列別名再利用の可否
2. 外部結合と NULL の扱い
   • LEFT OUTER JOIN で「ない行」を含め、COALESCE で NULL を置換するパターン
3. SQL 設計の意図把握
   • 「全従業員を対象に」「組織単位で比較」「期間限定（月別）の集計」など、業務要件に合わせたテーブル選択・結合方法を確実に選ぶ
4. SQL の実行計画を意識した記述
   • 列別名よりも式をそのまま書くほうが明示的で、可搬性が高いこともある

キーワード・論点の整理

• 列値個数：統計情報で報告される、その列に登録された「異なる値」の数
• 従業員テーブル：全社の従業員を管理
• 稼働実績テーブル：実際にプロジェクトで稼働した従業員の時間を管理
• PJに参加していない従業員：稼働実績テーブルに一度も行が作成されない従業員

解答となる理由の説明

「PJに参加していない従業員も一部いる。」

• “従業員”テーブルの「従業員コード」列値個数：8,800（全従業員数）
• “稼働実績”テーブルの「従業員コード」列値個数：8,000

受験者が誤りやすいポイント

• 「退職年月日」がNULLの行を勘違いしてしまう
  → 統計情報の列値個数はNULLを除いた一意値数なので、「退職」そのものは直接関係しない
• 「行数」と「列値個数」を混同する
  → 行数（レコード数）ではなく、あくまで列に登録された異なる値の数を問われている

覚えておくべき試験対策ポイント

• 列値個数は「その列に一意に登録されている値の数」を示す
• 統計情報は「実際に存在するデータ」に基づくため、入力されていないデータ（稼働実績がない従業員など）は反映されない
• 業務要件（ここでは「PJに参加していない従業員もいる」）と統計情報の関係を正しく理解することが重要です。

キーワード・論点整理

• 外表（従業員テーブル）に対する索引探索
• 副次索引１（組織コード列）を使用
• 「低クラスタな索引」によるランダムアクセス
• 行数÷列値個数 ＝ 平均取得行数 → ページ数に換算

解答の論拠

「まず外表から指定した組織コードに対して, 外表の副次索引を用いて平均22行を読み込む。
外表の副次索引は低クラスタな索引なので, 最大で(f)ページを読み込む。」

• 組織コードの列値個数は 400、行数は 8,800 なので、
  平均して 8,800 ÷ 400 = 22 行 が該当
• 低クラスタな索引では、該当行がほぼランダムに各ページへ分散
• 最悪ケースでは 1 行ごとに別ページ → 22 ページ の読み込み

受験者が誤りやすいポイント

• 「ページ当たり行数20行」を用いて 22行 ÷ 20行 ≒ 2 ページ と誤る
  → これは高クラスタ性がある場合の連続読み込み想定であり、
  低クラスタ索引では各行が別ページに分散すると考える
• テーブル全体のページ数や他テーブルの統計値と混同しない
• 「平均22行」と「最大22ページ」を区別して理解することが重要

試験対策：覚えておくべき知識

• 高クラスタ索引：キー順と物理行順がおおむね一致し、連続ページ読み込みが可能
• 低クラスタ索引：物理行順とキー順が一致しないため、ランダムアクセスに近くページヒット率が低い
• 平均取得行数＝テーブル行数 ÷ 列の列値個数
• 低クラスタ索引では「1行 → 1ページ」を想定して最大ページ数を見積もる
• ページ当たり行数の知識は「高クラスタ／表探索」の場合に効果的に使われる

小問①の解答キーワード・論点整理

• キーワード：
  「同じ日に複数PJの稼働時間を入力できる」
• 論点：
  • 「稼働年月日」列の列値個数は1,000（50か月分の異なる日付）
  • しかし，従業員１人当たりは同一日付でもPJごとに複数行を登録可能
  • よって，１人あたりの行数が列値個数を上回る

解答の理由

「従業員は同じ日に複数PJの稼働時間を入力できる。」

参照データ（抜粋）

誤りやすいポイント

• 「列値個数＝行数上限」と誤解する
  → 列値個数はあくまで異なる値の数。行数は同一値の重複登録を許す。
• PJコードや従業員コードを見て「１行／日」と思い込む
  → 実際は，同一従業員・同一日付でも異なるPJがあれば行が増える。

試験対策（覚えておくべきポイント）

• 列値個数は「重複しない値の種類数」であり，行数の上限ではないこと
• 入力要件（同一キー以外の重複を許すか）を設問文から正確に読み取ること
• RDBMSの統計情報では「行数」「列値個数」「ページ当たり行数」など異なる統計があるため，混同しないように整理すること

キーワード・論点整理

• 行数・ページ数の算出
  • 総行数や列の列値個数から「従業員１人当たりの総行数」「組織当たりの従業員数」を求める
  • 日付・従業員コードによる一部データの絞り込みとその行数
• インデックスのクラスタ性
  • 高クラスタ索引 ⇒ キーの順序と物理格納順が近く，範囲検索後のページ読み込みが少ない
  • 低クラスタ索引 ⇒ 物理的にランダムアクセスになる
• 性能改善のポイント
  • 既存の副次索引（稼働年月日）で全行スキャンに近いアクセスが発生 → 大量のページ読み込み
  • {従業員コード, 稼働年月日} の複合索引を追加することで月次範囲かつ従業員絞り込みが同時に可能 ⇒ 読み込み行数・ページ数を大幅削減

解答の論理的説明

(g) 24

• 全体 9,600,000 行／8,000 人 → 従業員１人あたり 1,200 行
• そのうち，20 日分のデータ比率：1,200 × (20 ÷ 1,000) = 24 行

(h) 20

• “稼働実績” テーブルの従業員コードの列値個数は 8,000
• 組織コードの列値個数は 400（均等分布仮定）
• 8,000 ÷ 400 = 20 人／組織

(i) 480

• g 行 × h 人 = 24 行 × 20 人 = 480 行

(j) 2,000

• 副次索引 1 は稼働年月日をキーに範囲検索 ⇒ まず 1,000 日あたり 9,600,000 ÷ 1,000 = 9,600 行／日
• 1 日分の読み込みページ数 ≒ 9,600 ÷ 100 行/ページ = 96 ページ
• 20 日分で 96 × 20 = 1,920 ページ → 約 2,000 ページ

(k) 50

• 現状，従業員１人あたり 1,200 行を読み込む
• 複合索引で 1 か月分だけ 24 行読み込む ⇒ 1,200 ÷ 24 = 50

受験者が誤りやすいポイント

• h の算出
  従業員テーブルの行数 8,800 ではなく，「稼働実績」テーブルに登場する従業員数 8,000 を使うこと
• g の算出
  「1,200 行」の意味を「全月次合計」ではなく「従業員あたりの全期間合計」と捉える
• j の算出
  組織あたり計 480 行でなく，「日付索引で読み込む全社分行数」で計算すること
• クラスタ性の理解
  範囲検索 → 高クラスタ索引なら物理ページが連続し，ローディスク数が抑えられる

試験対策まとめ

• ページ数の基本計算
  　行数 ÷ ページ当たり行数 → 物理ページ数
• 分布仮定の活用
  　均等分布 ⇒ 列値個数や組織数で割って平均を求める
• インデックスのクラスタ性
  　- 範囲検索は 高クラスタ索引を使うほど読み込みページ数が減少
• 複合索引の効果
  　複数条件（従業員＋日付など）の絞り込みを同時に行える索引でアクセス行数×ページ数を大幅削減できる
• SQL 性能評価パターン
  　外表／内表の選択，入れ子ループ結合での索引利用状況，範囲検索と行フィルタリング順序を押さえること