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

キーワード・論点整理

解答の論理的説明

DELETE FROM 従業員
 WHERE 退職フラグ = (a)
   AND (
         (DAYELEMENT(退職年月日) < (b))
      OR (MONTHELEMENT(退職年月日) < MONTHELEMENT((c)))
       )

1. 退職フラグ = (a)
   問題文には

   「退職すると, “従業員” テーブルの退職フラグを, … '0'(在籍)から '1' (退職) に更新する。」
   とあるので、削除対象は「退職」済みの行、すなわち
   a = '1' です。

2. 当月の20日までに退職
   毎月25日に「当月の20日までに退職した従業員の行を削除する」とあるので、
   • 同じ月に退職した場合は「日」が1～20 → DAYELEMENT(退職年月日) < 21
   • 前の月以前に退職した場合は「月」が当月より小さい → MONTHELEMENT(退職年月日) < MONTHELEMENT(現在日付)
   したがって、日付の境界は21日を使い、
   b = 21 となります。
3. 現在日付の指定
   「当月」を動的に取得するには標準関数 CURRENT_DATE（または DBMS 環境で SYSDATE）を用います。
   模範解答では
   c = CURRENT_DATE
   としており、「MONTHELEMENT(CURRENT_DATE)」で当月を抽出します。

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

1. b に「20」を入れてしまう
   条件は「当月の20日までに退職した」とあるものの、不等号が < (b) なので、20日以下を表すには b = 21 が正解です。
2. c に今日の日付リテラルを直接指定してしまう
   TO_DATE('2021-12-25','YYYY-MM-DD') 等を入れると毎月 SQL を修正する必要があり運用に即しません。
3. SYSDATE と CURRENT_DATE の混同
   試験上はどちらでも動的に現在日時を取得できますが、模範解答は CURRENT_DATE です。
4. 「月」と「日」の論理を取り違える
   前の月の場合は日付同士の比較ではダメで、「月部分」の比較が必要です。

試験対策ポイント

• 不等号と境界値：< (b) なのか <= (b) なのかで増減オフセットが変わるのでよく確認する。
• 日付関数の用途：DAYELEMENT／MONTHELEMENT のように、日付を要素別に抽出する関数は境界条件の判定で有用。
• 動的日付取得：SQL で「当月」や「今日」を参照するには CURRENT_DATE（ISO 準拠）や SYSDATE（Oracle など）を使い分ける。
• 要件→条件式への落とし込み：設問文に出てくる「当月の20日まで」といった自然言語は、必ず論理式でどのように表現するか落とし込む練習をしておく。

キーワード整理

解答の理由

1. 「部署コード順に … 一覧表が欲しい」とある
   問題文より「福利厚生担当者から … 部署コード順に 2007年1月1日以降に生まれた扶養対象者をもつ従業員数と扶養対象者数の一覧表が欲しい」とあるため、並べ替えの句として ORDER BY を使い、並べ替え対象の列として 従業員.部署コード を指定します。
2. 「従業員数」は従業員コードのユニーク件数
   同一社員に扶養対象者が複数いても、従業員数は社員ごとに 1 件としたいため、COUNT の内部で DISTINCT を使い、重複する従業員コードを排除して集計します。
3. GROUP BY の必要性
   SELECT 句に非集計列「従業員.部署コード」を含める場合、GROUP BY に同じ列を指定しなければなりません。これにより「部署コードごとに集計」した結果が得られます。

注意点や誤りやすいポイント

• COUNT の引数に DISTINCT を付け忘れると、扶養対象者ごとにカウントされてしまい「従業員数」が実際より大きくなる。
• SELECT 句に書く非集計列は必ず GROUP BY に明示する必要がある。
• 並べ替え句を忘れると「部署コード順」に並ばず、要件を満たさないレポートとなる。
• ORDER BY の後に集計関数を指定してしまうと「扶養対象者数順」など別要件になる点に注意。

試験対策ポイント

1. SQL で集計を行う際は、
   • GROUP BY によるグルーピング
   • 各列が集計対象か否かの整理
   • 重複を排除したい場合は COUNT(DISTINCT 列) を使う
2. 要件に沿った「並べ替え」は ORDER BY で指定
3. SQL の句の順序
   SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY の流れを必ず意識する
4. レポート要件（例：何をキーに何を集計し、どう並べ替えるか）を正確に反映するSQL設計を行う練習を重ねること

キーワード整理

• 参照制約（外部キー制約）：あるテーブルの列が，別のテーブルの主キー／候補キーを参照する仕組み
• NO ACTION：参照先の行を更新・削除しても，参照元には何もしない（違反があればエラー）
• CASCADE：参照先の行を更新・削除すると，参照元にも連鎖的に同じ操作を適用
• 即時モード：各 SQL 実行直後に制約を検査
• 猶予モード：トランザクション終了時に制約を検査
• NULL 値：外部キー列においては，原則として制約検査の対象外

解答の論理的説明

(a) 部署コードが NULL の行を“従業員”に挿入

• 外部キー列に NULL を格納した場合，RDBMS では「参照先が未定義」とみなして制約検査の対象外となります（NULL は参照制約チェックをスキップ）。
• したがって，正常終了します。

(b) 管理者従業員を“従業員”テーブルから削除

• “部署”テーブルの 管理者従業員コード 列が，従業員（従業員コード） を参照しています。
• DELETE 時の挙動モードは NO ACTION，検査契機モードは 即時モード なので，削除直後に参照元（“部署”テーブル）に参照先の従業員コードを持つ行がないかチェックし，存在すればエラーとなります。
• 管理者退職により該当従業員を削除すると，“部署”テーブル側にまだその 従業員コード を持つ行が残っているため，エラーとなります。

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

1. NULL の扱い
   外部キー列に NULL が入ると「参照なし」とみなされるため制約エラーにならない点。
2. CASCADE／NO ACTION の混同
   「従業員→部署」の参照（部署コード）は DELETE CASCADE 一方，「部署→従業員」の参照（管理者従業員コード）は DELETE NO ACTION である点を押さえる必要があります。
3. 即時モード／猶予モードのタイミング
   即時モードなら SQL 実行直後に，猶予モードならトランザクション終了時に制約検査が行われる違い。

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

• 外部キー列に NULL を格納してもエラーにはならない（参照なし扱い）。
• NO ACTION は「違反があればエラー」。CASCADE は「自動連鎖処理」。
• 即時モードは「各 SQL 後で即チェック」、猶予モードは「トランザクション終了時にチェック」。
• 参照制約の向き（どちらのテーブルが外部キーか）とモードを正確に把握する。
• 仕組みを理解すれば，アプリケーションでのチェック漏れを DB 側に任せて安全性を高められる。

1. キーワードと論点整理

• 参照制約の CASCADE 削除とトリガの競合
• トリガの実行タイミング（前／後）
• 削除された行のデータ保存（別テーブルへのアーカイブ）
• 元トリガ（“従業員”テーブルの DELETE 後に家族行を再度削除していた処理）の問題

2. 解答が導かれる理由の論理的説明

・削除した “従業員” テーブルの行を別テーブルに挿入する。
・“従業員家族” テーブルのその従業員の家族の行について, 別テーブルに挿入し, その後削除する。

“従業員家族” テーブルの従業員コードは 参照先：従業員（従業員コード），DELETE の挙動モードが CASCADE，検査契機モードが 即時モード

“従業員家族” テーブルのその従業員の家族の行を…別テーブルに挿入し, その後削除する

1. “従業員”テーブルの削除後トリガから，家族行の削除処理部分を除き，従業員行のみを別テーブルに保存
2. “従業員家族”テーブルに対して，新たに「削除の後」トリガを定義し，削除された家族行を別テーブルに保存

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

• 「削除の前 (BEFORE DELETE)」と「削除の後 (AFTER DELETE)」の違い
  ・CASCADE 削除は対象行を先に消してしまうため，BEFORE でしか OLD を参照できないと誤解すると失敗します。
  ・実際には即時モードかつ CASCADE で自動削除されるため，家族行はトリガ発火前に消えています。
• トリガの定義対象テーブル
  ・“従業員”トリガを修正しただけでは家族行のアーカイブが不可能になる点
• 参照制約の検査契機モード（即時／猶予）とトリガ発火のタイミングは別次元の概念である点

4. 新トリガ定義のまとめ

5. 試験対策としての覚えどころ

• 参照制約の CASCADE DELETE 時は，RDBMS 自動処理が先行し，トリガはその後に発火する
• トリガの BEFORE/AFTER は，主に OLD/NEW の扱いや他の自動処理との呼び出し順序に影響
• 参照制約とトリガを組み合わせる場合，「誰が（どのテーブル）」「いつ（前／後）」「何を（OLD/NEW）」を整理して設計
• モデルケースとして，「親テーブル削除→CASCADEで子を自動削除→子テーブルのAFTER DELETEでアーカイブ」を覚えておくこと。

1. キーワード・論点整理

• 参照制約の「挙動モード」と「検査契機モード」
  • 挙動モードには NO ACTION と CASCADE があり，CASCADE は「参照先の行を削除・更新したときに参照元にも連鎖させる」。
  • 検査契機モードには 即時モード と 猶予モード があり，猶予モードは「トランザクション終了時にまとめて検査する」。
• 図3 の更新手順①～⑥
  ① “部署” テーブルから不要な行を削除
  ② コミット
  ③ “部署” テーブルに新規行を挿入
  ④ コミット
  ⑤ “従業員” テーブルの部署コードを更新
  ⑥ コミット
• 問題となる参照制約

  テーブル	列名	参照先	実行契機	挙動モード	検査契機モード
  従業員	部署コード	部署（部署コード）	DELETE	CASCADE	猶予モード

2. 解答の導出

1. 問題文の参照制約定義より，

   「従業員.部署コード → 部署（部署コード）」に対して，DELETE CASCADE，検査契機モードは 猶予モード。
   の記述があるため，親となる“部署”テーブルの行を削除すると，子側の“従業員”テーブルの行にも 即時にCASCADE が適用され，従業員の行が 削除される。

2. 図3 の手順①は，

   ① “部署” テーブルから不要な行を削除する。
   であり，ここで部署の行が削除されると同時に，参照制約の CASCADE によりその部署に所属している従業員の行も削除される。

3. 以上より，
   • 契機：①
   • 不具合内容：削除された部署に所属している従業員が，“従業員”テーブルから削除される

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

• 親テーブル／子テーブルの関係を取り違える
  「部署」→「従業員」が親子関係であることを押さえず，「従業員を基準に部署を…」と誤解すると，CASCADE の働きを逆にイメージしやすいです。
• NO ACTION と CASCADE の違い
  CASCADE は「子を連鎖的に削除／更新」し，NO ACTION は「何もしない（エラーになる場合もある）」と即座に動作が異なります。
• 検査契機モードのタイミング
  即時モード／猶予モードで制約検査のタイミングが異なることを，誤って挙動モードと混同しないように注意が必要です。

4. 覚えておくべきポイント

• 参照制約 挙動モード
  • NO ACTION：参照先を削除・更新しても子はそのまま（制約検査エラーになる場合がある）
  • CASCADE：参照先を削除・更新すると子も連鎖して削除・更新される
• 参照制約 検査契機モード
  • 即時モード：各 SQL 実行後すぐに制約を検査
  • 猶予モード：トランザクション終了時にまとめて検査
• 親子テーブルの関係を正しく把握し，CASCADE の動きを図示して整理しておくことが，本番での確実な解答につながります。

1. キーワード・論点整理

• 参照制約（リファレンシャル・インテグリティ）
  • 「従業員．部署コード」が「部署（部署コード）」を参照
  • 挙動モード：UPDATE → NO ACTION、DELETE → CASCADE
  • 検査契機モード：猶予モード（トランザクション終了時に制約検査）
• 図3 の手順
  ① 「部署」テーブルから不要な行を削除
  ② コミット
  ③ 「部署」テーブルに対して新規の行を挿入
  ④ コミット
  ⑤ 「従業員」テーブルの部署コードを更新
  ⑥ コミット

2. 解答（変更後の①,③,⑤の順序）

③ → ⑤ → ①

3. なぜこの順序になるのか

1. 参照制約の DELETE モードが CASCADE であるため，
   「部署」テーブルの行を先に削除すると，当該部署コードを参照する「従業員」の行まで連鎖削除されてしまいます。
2. そこで，
   a. まず ③ 新規部署行を挿入 → 子テーブルの更新先部署コードを先に用意
   b. 続いて ⑤ 従業員テーブルの部署コードを更新 → すべての従業員が新しい部署コードを参照
   c. 最後に ① 古い部署行を削除 → 古い部署コードを参照する行がないため CASCADE による誤削除が起きない

• 新部署コードへの更新時点では親テーブル（部署）に行が存在するため NO ACTION による制約違反も起きず，
• 古い部署行の削除時には子テーブルに該当行が残っておらず，
• 安全に不要行を削除できます。

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

• 「DELETE→CASCADE」の仕組みを忘れてしまい，古い部署行を先に削除してしまう
• UPDATE（NO ACTION）の検査契機モードが猶予モードであるため，INSERT→UPDATE→DELETE の順序でまとめてコミットする構成を見落とす
• 図3の番号順（①②③…）と実際の並べ替え後の手順を混同しやすい

5. 試験対策としてのまとめ

• 参照制約の挙動モード（NO ACTION／CASCADE） と 検査契機モード（即時／猶予） の違いを正確に理解する
• 親テーブルの DELETE→CASCADE が子テーブル行を削除する点に注意し，削除順序を設計する
• トランザクション内での制約検査タイミング（即時 or 猶予）を踏まえ，INSERT・UPDATE・DELETE の順序を適切に組む
• 問題文にある「どの操作で何の制約モードか」を整理し，手順変更の理由を論理的に説明できるよう練習しておくこと

キーワード・論点整理

• 検査契機モード（猶予モード）
  「検査契機モードには…② 猶予モード: トランザクション終了時に, トランザクション内の全ての SQLを実行した結果に対して, 制約を検査する。」
• 挙動モード（NO ACTION）
  「参照先のテーブルの行を削除又は更新したとき, 参照元のテーブルの行に対して何もしない。」
• 参照制約の対象
  表3 より、従業員 テーブルの 部署コード が 部署（部署コード） を参照し、検査契機モードは 猶予モード である。

(a) の解説

理由・引用

• 検査契機モードが「猶予モード」のため、トランザクション終了時にまとめて制約チェックを行う。

  「検査契機モードには…② 猶予モード: トランザクション終了時に…制約を検査する。」

• UPDATE によって 部署 テーブルの主キー（部署コード）が変更された場合、参照元 の 従業員 テーブルに残る古い値が不整合にならないかを確認する必要があるため、
  トランザクション終了時に「更新によって無くなった部署コード」が従業員テーブルに残っていないかをチェックします。

(b) の解説

理由・引用

• 問題文冒頭に「索引は,主キーだけに定義されている。」とある。
• 外部キーである 従業員.部署コード には索引が設定されていないため、
  トランザクション終了時に全行走査（フルスキャン）で該当値を探す必要がある。

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

• 即時モードと猶予モードの違い
  即時モードでは各 SQL の終了時に検査、猶予モードではトランザクション終了時に一括検査します。
• NO ACTION と CASCADE の違い
  NO ACTION：違反時にエラーを返す（連鎖しない）、
  CASCADE：参照先の変更を参照元にも伝播させる。
• インデックスの有無
  外部キー列にインデックスがないと、制約チェック時の検索コストが大幅に増加します。

試験対策ポイント

• 参照制約の 挙動モード（NO ACTION／CASCADE）と 検査契機モード（即時／猶予）を明確に区別して覚える。
• 猶予モード の検査タイミングは「トランザクション終了時」であることを定着させる。
• 外部キー列には 必ずインデックスを張る 運用が望ましいことを理解し、パフォーマンスへの影響を押さえる。
• 制約検査の実行タイミングと検索方式（インデックス有無による全行走査）に関する具体的理由を自分の言葉で説明できるようにしておく。