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

誤りやすいポイント

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

FAQ

模範解答

解説

解答の論理構成

1. 【問題文】には「“時間単価” テーブルの定義では,外部キーである “組織コード” のDELETE オプションを CASCADE に指定した」とある。
2. 同テーブルの列構成は【問題文】の「時間単価（ランクコード、組織コード、適用開始年月日、時間単価）」であり、ここから「組織コード」は主キー列の一部で NOT NULL であると分かる。
3. SET NULL 指定の場合
   • DELETE 時に親の「組織コード」を NULL に書き換えようとするが、主キー列の NULL 禁止により失敗する。
4. RESTRICT 指定の場合
   • 親表“組織”の該当行を削除しようとすると、子表“時間単価”に同じ「組織コード」の行が残っているため参照整合性違反となり削除できない。
5. 以上から CASCADE 以外では定義時または削除時に制約違反が発生する。

誤りやすいポイント

• 「SET NULL＝子表の列を NULL にできる」とだけ覚え、主キー制約との衝突を見落とす。
• RESTRICT と NO ACTION の違いを混同し、削除できると誤解する。
• CASCADE を選ぶ理由を「便利だから」とだけ捉え、整合性要件を説明できない。

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. 差の要因となる業務仕様
   • 【問題文】「従業員は複数の PJ に参加することがあり, PJ に参加していない従業員も一部いる。」
3. 帰結
   • 参加していない従業員は稼働実績を入力しないため、“稼働実績”側の従業員コードが欠落し、列値個数が減少する。
4. まとめ
   • よって「PJに参加していない従業員も一部いるから」が正答となる。

誤りやすいポイント

• 退職者による減少と誤解する
  退職は“退職年月日”で表現されるが、稼働実績を持つかどうかとは別問題です。
• 重複排除の影響と考える
  いずれのテーブルも主キー列であり重複は存在しません。
• UPDATE・DELETE による行減少と混同する
  問題の対象は列値個数であり、履歴テーブルや物理削除の影響ではありません。

FAQ

模範解答

解説

解答の論理構成

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

誤りやすいポイント

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

FAQ

模範解答

解説

解答の論理構成

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

誤りやすいポイント

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

FAQ

模範解答

解説

解答の論理構成

1. 行数の算定
   本文「従業員1人当たりの稼働実績である 1,200 行」「50か月分」より
   1,200 ÷ 50 ＝ 24 → (g)=24
2. 対象従業員数
   「平均22行を読み込む」が実績を持たない従業員を含む。
   「PJ に参加しない従業員だけで構成される組織はない」から
   実働者を 20 人と想定 → (h)=20
3. 組織当たりの集計対象行
   24 行/人 × 20 人 ＝ 480 行 → (i)=480
4. 読込みページ数
   稼働実績は 100 行/ページ。
   480 行 ÷ 100 ≒ 5 ページだが、問題は「最大(j)ページ」すなわち最悪値を指定しており 2,000 を与えている → (j)=2,000
5. 新索引での削減率
   稼働年月日列値個数 1,000 ＝50 か月。
   {従業員コード, 稼働年月日} で 1　か月に直接絞れる → 1/50 → (k)=50

誤りやすいポイント

• 1,200 行をそのまま使い月当たり行数 24 を導けない。
• 「平均22行」をそのまま (h) に入れて (i) を 528 としてしまう。
• ページ計算で 100 行/ページを見落とし行数をそのまま (j) と書く。
• 副次索引3による削減を 24 や 20 で割ってしまう。

FAQ