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

解答の論理構成

1. (a) を決定
   • 図2の定義に「FOREIGN KEY (組織長従業員コード) REFERENCES 従業員 (従業員コード)」と明示されている。
   • 未定義の「従業員」表を参照するため CREATE TABLE が失敗したという流れより、外部キー列は「組織長従業員コード」と判断できる。
2. (b)〜(d) を決定
   • 手順②に「“従業員”、“役職”、“時間単価”、“ランク” の各テーブルを…“(b)”、“(c)”、“役職”、“d”の順番で実行」とある。
   • 依存関係
     ・「役職」は「ランク」を参照。
     ・「時間単価」は「ランク」と「組織」を参照。
     ・「従業員」は「組織」と「役職」を参照。
   • 参照されない最初のテーブルは「ランク」→(b)。
   • 次に「ランク」が存在すれば作成可能で、まだ「役職」を参照しない「時間単価」→(c)。
   • 「役職」は既に「ランク」があるので続けて作成、指定どおり名前をそのまま記載。
   • 最後に「従業員」→(d)。
3. (e) を決定
   • 手順③では「“組織” テーブルに対する(e)文を用いて…外部キー…を追加」とある。
   • 既存表に制約を付け足す標準 SQL 文は ALTER TABLE であり、他の候補 (UPDATE, INSERT など) では制約定義は行えない。

誤りやすいポイント

• 図中の列名と同じ語をそのまま解答するルールを見落として文字種や全角・半角を変えてしまう。
• 「時間単価」と「従業員」の順序を逆にして依存関係を崩す。
• 外部キー追加に CREATE INDEX などを選んでしまう。

FAQ

解答の論理構成

1. 外部キー制約の確認
   • 図2で「FOREIGN KEY (組織長従業員コード) REFERENCES 従業員 (従業員コード)」と定義。
   • 同図で「FOREIGN KEY (上位組織コード) REFERENCES 組織 (組織コード)」と自己参照。
2. 挿入時の制約回避策
   • 組織長従業員コード
     • 先に組織を登録する時点では組織長となる従業員が「従業員」テーブルにまだ存在しないケースが多い。
     • したがって制約に抵触しないよう NULL を設定しておき、後で UPDATE する。
   • 上位組織コード
     • 自己参照のため同一テーブル内に親行が必要。
     • 表1に「最上位組織の上位組織コードにはNULLを設定する。」とあるので、 ①最上位をNULLで登録 → ②下位を親→子の順で登録、で整合性を保つ。

誤りやすいポイント

• 両方の外部キーをまとめて「NULLにすればよい」と思い込む
  → 上位組織コードは最上位以外NULLにできない。
• 自己参照外部キーの存在を忘れ、下位組織から挿入してエラーになる。
• 組織長従業員コードに仮のコードを入れて後で削除しようとし、参照整合性違反を起こす。

FAQ

解答の導き方

1. 子側の列が主キー相当であることを確認する
   本文には「時間単価は、ランク別組織別年月日別に決めている」とあり、図1に「時間単価（ランクコード、組織コード、適用開始年月日、時間単価）」と列が並んでいます。業務上ランク・組織・年月日で時間単価を一意に管理することが明示されているため、ランクコード・組織コード・適用開始年月日が主キーに相当する設計と判断できます（主キーの列はNULLを許容しません）。
2. 親表の組織コードの定義を確認する
   図2 の CREATE TABLE 文に「組織コード CHAR(8) NOT NULL PRIMARY KEY」とあります。親表側の組織コードは主キー列であることが明確です。
3. ON DELETE SET NULL の影響と失敗の理由
   ON DELETE SET NULL は「親行削除時に子表の外部キー列を NULL に書き換える」動作です。しかし、上で判定したとおり時間単価の組織コードは主キーの一部であり NULL を許容しない列です。したがって、子側の組織コードを NULL にできないため、外部キー制約の定義時（子表の定義で NULL 非許容が矛盾する）あるいは親行削除時に制約違反で失敗します。
4. ON DELETE RESTRICT の影響と失敗の理由
   ON DELETE RESTRICT は「子表に参照行が存在する場合、親行の削除を禁止する」動作です。問題文で F さんは「次いで…時間単価…の各テーブルに対しても INSERT 文を用いて行を挿入した」とあるため、時間単価に当該組織コードを参照する行が存在する可能性があります。よって親表から組織行を削除しようとすると参照が残っていて RESTRICT により削除が拒否され、失敗します。

• SET NULLの場合：時間単価テーブルの組織コードは主キーの一部でありNULLに変更できないから
• RESTRICTの場合：時間単価テーブルに同じ組織コードの行が存在する場合があるから

誤りやすいポイント

• ON DELETE SET NULL は「親表の値を変える」と誤解する（正しくは子表の外部キー列を NULL にする動作です）。
• 図1の業務記述「ランク別組織別年月日別…」を見落とし、組織コードが主キーの一部であることを判断できない。
• 主キー列は自動的に NOT NULLである点を忘れて SET NULLが許されると思い込む。
• RESTRICTがいつエラーを出すか（定義時か削除時か）を混同する。RESTRICTは削除時の参照有無で拒否される。

FAQ

解答の論理構成

1. PARTITION BY の検討
   SQL1の目的文に「組織ごとに計画時間の少ない順に順位付けし、順位に沿って従業員数が等分となるように1〜3の番号を付与」とある。等分処理は NTILE(3)。よって PARTITION 単位＝「組織コード」。
2. ORDER BY の検討
   目的文に「計画時間の少ない順に順位付け」とある。また稼働計画が無い従業員は 0 として扱う旨も明記されている。「COALESCE(SUM(計画時間),0)」は SELECT 句と同一で、NULL→0 の変換を含む唯一の候補。
3. FROM 句のテーブル選定
   “役職コード = 'SE' の従業員”という条件は「従業員」テーブルにしか存在しないため、A別名を付ける基表は「従業員」。
4. LEFT OUTER JOIN 先
   目的文に「稼働計画に登録されていない従業員を含めて」とある。欠落行を補完するため従業員側を残す LEFT OUTER JOIN が必要。残りのテーブル候補「稼働計画」を B として結合する。
5. ウインドウ関数の二重使用
   同一の計画時間合計で NTILE と RANK を取るので ORDER BY 句は両方とも同一式「COALESCE(SUM(計画時間),0)」を再記述する。集計列の別名はウインドウ関数の中では参照できないため式を直接書く必要がある。

誤りやすいポイント

• 集計列別名をウインドウ関数内で直接使ってエラーになる。
• LEFT OUTER JOIN の向きを逆にして未登録従業員が欠落する。
• ORDER BY に単純な「SUM(計画時間)」を書き、NULL が 0 として扱われず意図しない順位になる。
• NTILE の PARTITION BY を指定し忘れ、全組織をまとめて3階級にしてしまう。

FAQ

解答の導き方

1. 表の数値から事実を読み取ります。表2の“従業員”テーブルの従業員コードの列値個数は「8,800」、表3の“稼働実績”テーブルの従業員コードの列値個数は「8,000」と示されています。これは稼働実績側に登場する異なる従業員の数が従業員テーブルの登録数より少ないことを意味します。
2. 用語の意味を整理します。問題文中の「列値個数」はその列に現れる異なる値の個数（＝DISTINCTな値の個数）を示します。一方で稼働実績テーブルの行数は「1人の従業員が複数日の実績や複数PJで複数行を持つ」ため多くなり得ます。
3. 業務の記述を確認します。業務の概要に「従業員は複数のPJに参加することがあり、 PJに参加していない従業員も一部いる」とあります。これは、すべての従業員が稼働実績に記録されるわけではないことを示します。
4. 以上より結論を導きます。稼働実績テーブルの「従業員コードの列値個数」が従業員テーブルより小さいのは、稼働実績に記録されない（＝PJに参加していない）従業員が存在するからです。
   解答：PJに参加していない従業員も一部いるから

誤りやすいポイント

• 「列値個数」を「行数」と混同する：行数は重複を含むが列値個数は異なる値の個数である点を見落とす。
• 稼働実績の行数が多いから必ず多くの従業員が参加していると誤解する：実際は同一従業員の複数行である場合が多い。
• 「重複は存在しない」と考える誤り：稼働実績では同一従業員コードが複数行で現れる（複数日・複数PJ）点を忘れる。
• 集計対象（列値個数の差）を別の要因（削除やデータ不整合）と早合点する。

FAQ

解答の論理構成

1. 外表（“従業員”）の対象行数を把握
   【問題文】には
   「まず外表から指定した組織コードに対して、…平均22行を読み込む。」
   と明記されている。したがって対象行は22行。
2. 索引のクラスタ性を確認
   【問題文】
   「外表の副次索引は低クラスタな索引」
   低クラスタ索引は【RDBMSの主な仕様】で説明されるとおり、
   • 「キーが指す行の物理的な並び順が一致している割合が低く、行へのアクセスがランダムになる。」
     つまり1行取得するたびに別ページに飛ぶ可能性が高い。
3. ページ数の上限を決定
   行が22行、各行が別ページに散らばっている最悪ケースでは
   読み込むページ数 ＝ 行数 ＝ 22
   したがって (f) = 22となる。

誤りやすいポイント

• 低クラスタでも「20行／ページだから ⌈22÷20⌉＝2ページ」と勘違いする。高クラスタなら近い値になるが、低クラスタは行ごとにページ散在と考える。
• 行数22を計算せず、400組織という列値個数だけを見てしまう。
• 「平均22行だから平均22/20ページ」と“平均”をそのままページ数に適用してしまう。設問は“最大”ページ数を求めている。

FAQ

解答の論理構成

1. 列値個数1,000は日付の種類数を示すだけ
2. 行の粒度は表1の説明より「ＰＪごと稼働年月日ごと従業員ごと作業実績」
3. 問題文には「従業員は同じ日に複数PJの稼働時間を入力できる。」と明記
4. よって同じ日付が複数行に現れ、従業員1人当たりの行数が1,000より多くなる

誤りやすいポイント

• 列値個数＝最大行数と短絡し、複合キーで重複行が生じることを忘れる
• 「複数月分のデータだから増える」と月数だけに注目し、同日複数ＰＪの条件を見落とす
• 行数計算で「ＰＪコード」の存在を無視し、日付×従業員だけで考えてしまう

FAQ

解答の導き方

1. g（内表の集計対象：従業員1人当たりの該当行数）
   • 問題文の統計情報から「稼働実績」の総行数が「9,600,000行」であり、列値個数として「従業員コード」が「8,000」であることが分かります。したがって従業員1人当たりの平均行数は $9,600,000÷8,000=1,200$ 行です（この1,200行は全月分の累計）。
   • また「稼働年月日列の列値個数は1,000（50か月分）」とあるので、50か月で均等分布すると1か月分は $1,200÷50=24$ 行になります。
   • よってg = 24。
2. h（組織当たり稼働実績を計上している従業員数）
   • 「従業員」テーブルの行数が「8,800行」で、組織コードの列値個数が「400」なので、従業員の組織当たり平均は $8,800÷400=22$ 人です。
   • ただし稼働実績に登場する従業員は8,000人（表3の従業員コード列値個数）です。問題文の前提で「PJに参加しない従業員だけで構成される組織はない」としてよいので、組織当たりの「稼働実績を計上している従業員数」は平均を8,000/8,800の比で補正できます： $22\times \frac{8,000}{8,800}=22\times \frac{10}{11}=20$ 人。
   • よってh = 20。
3. i（組織当たりの集計対象行数）
   • 1人当たりの該当行数gと組織当たりの人数hを掛けます： $24\times 20=480$ 行。
   • よってi = 480。
4. j（内表の副次索引1が高クラスタであるときの、組織当たりの最大読込みページ数）
   • ここは「最大」で評価する点に注意します。問題文に「従業員当たり1か月分の行が高々2ページに格納されることが分かった」とあり、また稼働年月日は「1,000（50か月分）」とされています。副次索引1は従業員コードだけをキーにした索引であり、日付で絞り込まずに索引経由で行を読み込むと、各従業員について50か月分（全期間）を読み込む必要があります（本文は「従業員1人当たりの稼働実績である1,200行を読み込み、... BETWEEN 述語を評価する」と明示しています）。
   • 「高々2ページ／月」という上限を最悪ケース（最大）で積み上げると、従業員1人あたりの全期間の最大ページ数は $2\text{ページ／月}\times 50\text{月}=100\text{ページ}$ となります。
   • 組織当たりh = 20人なので、組織当たりの最大読込みページ数は $100\times 20=2,000$ ページとなります。
   • （補足：平均的・連続的に格納されている前提なら $1,200÷100=12$ ページ／人、組織で $12\times 20=240$ ページと算出できますが、設問は「最大(j)ページ」を問うため「高々2ページ／月」を最悪に積み上げた $2,000$ を採ります。）
   • よってj = 2,000。
5. k（副次索引3の導入による行数・ページ数の削減倍率）
   • 副次索引3を {従業員コード, 稼働年月日} の複合キーで作ると、索引上で「1か月分」に直接絞り込めます。稼働年月日の列値個数が50か月分であるため、従来読み込んでいた全期間分が50分の1に縮小されます。
   • 数値で見ると、従来は従業員1人当たり $1,200$ 行を読み、導入後はその $1/50$ の $24$ 行だけを読みます。従って削減倍率は $50$ です。
   • よってk = 50。

• g：24
• h：20
• i：480
• j：2,000
• k：50

誤りやすいポイント

• hを単に「従業員テーブルの平均（22）」とだけして終わらせる誤り。稼働実績に登場する従業員数（8,000）と従業員テーブルの総数（8,800）が異なるため、稼働実績を計上している実働者数で補正する必要があります。問題文の前提「PJに参加しない従業員だけで構成される組織はない」を使って補正する点を忘れないでください。
• jの扱いでの混同。平均ページ数（$1,200÷100=12$ ページ／人 → 組織で240ページ）を使うか、問題文の「最大」「高々2ページ／月」という表現を最大化して2,000を得るかで結果が大きく変わります。問題文は「最大(j)ページ」と表現しているため、与えられた「高々2ページ／月」を最悪の形で積み上げて評価するのが設問意図に合致します。
• 稼働年月日の「1,000（50か月分）」の読み替えミス。1,000（日）＝50か月（20日/月）という前提があるので、月数は50として計算します。
• 副次索引のキーを間違えるとgとkの解釈が変わります。副次索引1は従業員コードに関する索引である点を確認してください。

FAQ