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

解答の導き方

1. aの導出
   問題文に「退職フラグを、あらかじめ設定している '0'(在籍)から '1' (退職) に更新する。」とあります。削除対象は「退職した従業員の行」ですから、退職を表す値である '1' を条件に使う必要があります。したがって (a) は '1' です。
2. b, cの導出（目的の整理）
   問題文に「毎月25日に、… 当月の20日までに退職した従業員の行を削除する。」とあります。ここで「当月の20日までに」は当月1日から当月20日までを含むことを意味します。
3. DAYELEMENTに関する判断（bの決定）
   注記1に「DAYELEMENT関数は、指定された DATE 型の年月日の日の部分を数値として抽出するユーザ定義関数とする。」とあります。表1の述語はDAYELEMENT(退職年月日) < (b) という形ですから、「当月の20日まで」を含めるにはDAYELEMENTが1〜20のとき真になる式にする必要があります。比較が < (b) なので (b) に21を入れてDAYELEMENT(...) < 21とすれば、DAYELEMENT = 20の行も含まれます（例：退職日が当月20日のとき20 < 21は真）。よって (b) = 21です。
4. MONTHELEMENTに関する判断（cの決定）
   注記2に「MONTHELEMENT関数は、指定された DATE 型の年月日の年月の部分を数値として抽出するユーザ定義関数とする。」とあります。これは年と月を一体で比較できる値（例えば200701のようなYYYYMM相当）を返すものと解釈して年月単位の大小比較が可能であることを意味します。SQLの比較はMONTHELEMENT(退職年月日) < MONTHELEMENT((c)) になっているため、比較対象には実行時点の年月を与えるのが自然です。SQL実行時の現在日付を表す組込み表現はCURRENT_DATEなので、(c) = CURRENT_DATEが適切です。
5. 条件の意味（確認用）
   上記を置き換えると、削除条件は次のような意味になります（表示用一行）:
   DELETE FROM 従業員 WHERE 退職フラグ = '1' AND ((DAYELEMENT(退職年月日) < 21) OR (MONTHELEMENT(退職年月日) < MONTHELEMENT(CURRENT_DATE)))

誤りやすいポイント

• (b) に20を入れてしまう誤り
  与えられた式がDAYELEMENT(...) < (b) の形なので、当月20日を含めるには (b) = 21とする必要があります。単に「20」と考えると当日（20日）が除外されます。
• MONTHELEMENTを「月の部分（1〜12）」だけと誤解する誤り
  注記2は「年月の部分」を抽出すると書いてあるため、年を含めた年月（例：YYYYMM相当）で比較する想定です。年を含まない単独の月値だと前年・翌年との比較が成立しません。
• CURRENT_DATEを文字列として 'CURRENT_DATE' としてしまう誤り
  CURRENT_DATEは組込みの日付式です。クォートで囲むと文字列扱いになり比較が正しく動作しなくなります。
• 退職フラグの表記を整数1として扱う誤り
  問題文はフラグ値を '0'／'1' と示しているため、SQL中では文字リテラル '1' を使う想定です。定義に従ってください。

FAQ

解答の導き方

1. dが何であるか（グループ化・表示する列）

• 要件が「部署コード順に … 一覧表」と明示しているため、(d) は部署コードを表す列です。
• 図1 に従って 部署コード は従業員テーブルの列なので、あいまいさを避けるために完全修飾で 従業員.部署コード を使います。GROUP BY と SELECT の非集約列は一致している必要があるため、(d) = 従業員.部署コード が妥当です。

1. eが何であるか（従業員数の集計方法）

• 従業員数は「扶養対象者をもつ従業員の人数」です。従業員家族テーブルは扶養対象者ごとに行があるので、単に COUNT(従業員家族.従業員コード) とすると扶養対象者行の数（扶養対象者数）になってしまい、同一従業員が複数の扶養対象者を持つ場合に過大カウントされます。
• 重複する従業員コードを一意に数えるには DISTINCT を使います。問題のプレースホルダの形が COUNT((e) 従業員家族.従業員コード) なので、(e) = DISTINCT が正しいです。つまり COUNT(DISTINCT 従業員家族.従業員コード) により従業員の数が得られます。

1. fが何であるか（並べ替え）

• 要件に「部署コード順に」とあるため、結果を部署コードでソートする必要があります。SQL の並べ替え句は ORDER BY なので、(f) = ORDER BY です。
• 実装上は ORDER BY に SELECT の列名やエイリアス、列番号を使える RDBMS もありますが、問題文の意図に沿ってかつ可搬性を考えると ORDER BY 従業員.部署コード のように明示するのが安全です。

誤りやすいポイント

• 従業員数と扶養対象者数を取り違える：従業員数は従業員ごとの人数（重複を除く）であり、扶養対象者数は従業員家族テーブルの行数（条件を満たす扶養対象者の個数）です。DISTINCT の有無で結果が大きく変わります。
• COUNT() と COUNT(列) の違い：COUNT() は行数（NULL を含む）を数え、COUNT(列) はその列が NULL でない行だけを数えます。今回の扶養対象者数には COUNT(*) が適しています（WHERE によって該当行のみを抽出しているため）。
• GROUP BY と SELECT の整合性：GROUP BY に指定する式は、SELECT の非集約列と一致させる必要があります。あいまいさを避けるために完全修飾（例：従業員.部署コード）で書くと安全です。
• ORDER BY の書き方：多くの RDBMS は ORDER BY にエイリアスや列番号を許容しますが、問題や移植性を考えると元の列式（従業員.部署コード）を明示するのが確実です。
• 結合の種類：問題の要件は「扶養対象者をもつ従業員」を集計するため内部結合（暗黙の結合 WHERE 句での結合条件）で問題ありませんが、扶養対象者がいない従業員も列挙したい場合は LEFT OUTER JOIN と COALESCE 等の工夫が必要です。

FAQ

解答の論理構成

1. “参照制約” の仕様
   【問題文】では挙動モードに「① NO ACTION」「② CASCADE」が定義されています。NO ACTIONは参照整合性を保持できない操作を禁止します。
2. (a) 所属未定の従業員挿入
   • 【表3】で “従業員”.“部署コード” の挙動は UPDATE／NO ACTION、DELETE／CASCADE、検査契機は「猶予モード」です。
   • 挿入時に “部署コード” がNULLの場合、参照制約は適用されません（参照値が存在しないため）。
   • よって制約違反は発生せず「正常終了」と判断できます。
3. (b) 管理者従業員の削除
   • “部署”.“管理者従業員コード” は【表3】で DELETE／NO ACTION と定義されています。
   • 当該従業員が “部署” テーブルから参照されている状態で “従業員” テーブルから削除すると、参照先が失われるためNO ACTIONによりエラーが返されます。
   • したがって「エラー」になり、その理由は「“部署”テーブルの管理者従業員コードの参照制約に違反するから」です。

誤りやすいポイント

• NULLでも必ず制約検査が行われると誤解する。
• CASCADEとNO ACTIONを参照元・参照先で混同する。
• 猶予モード と 即時モード の違いを結果判定に結び付けられない。
• “従業員家族” 側の DELETE／CASCADE に気を取られて (b) が許可されると誤判断する。

FAQ

解答の導き方

1. 現状のトリガ処理を確認する
   問題文には「実行タイミングを“従業員”テーブルの削除の後としたトリガを定義している。」とあり、その処理として「“従業員家族”テーブルのその従業員の家族の行について、別テーブルに挿入し、その後削除する。」とあります。つまり現在は従業員側の AFTER DELETE トリガ内で、従業員行の退避と同時にその従業員に紐づく従業員家族の行を取得してアーカイブし、続けて削除していることが分かります。
2. 表3の参照制約の設定を確認する
   表3では従業員家族の従業員コードに対する参照制約の DELETE が CASCADE に設定されています。したがって従業員行を削除すると、RDBMS が従業員家族の該当行を自動的に削除します。
3. なぜ親の AFTER DELETE で家族行をアーカイブしてはいけないかを論理的に導く
   トリガの説明に「トリガはテーブルに対する変更操作を契機に、…操作対象の行ごとに実行する」とあるため、従業員家族テーブルに対する削除（明示的削除でも CASCADE による削除でも）は従業員家族側の DELETE トリガの発火対象になります。逆に、親（従業員）の AFTER DELETE トリガ内で子（従業員家族）行を参照してアーカイブしようとすると、CASCADE によって子行が既に削除されている場合があり、子行を取得できずアーカイブできないか、削除操作でエラーになる可能性があります。
   また問題文は「実行タイミングを挿入・更新・削除の前として定義したトリガの処理の中で、テーブルに対する変更操作を行うことはできない」と明記しているため、BEFOREトリガ内で別テーブルへ挿入する方式は利用できません。
4. したがって取るべき対策（論理的帰結）
   • 子行の削除が発生するタイミング（CASCADEによる削除を含む）で確実にアーカイブ処理が実行されるように、従業員家族テーブル側に削除後トリガを定義します。
   • BEFOREトリガは別テーブル挿入が禁止されているため、実行タイミングは削除の後（AFTER）にします。
   • 親トリガ（従業員側）の中の「従業員家族を別テーブルに挿入して削除する」処理は削除しておく必要があります（重複アーカイブや参照エラーを避けるため）。

• テーブル名：従業員家族
• 実行タイミング：削除の後
• 処理内容：削除した行を別テーブルに挿入する。

誤りやすいポイント

• 親（従業員）側の AFTER DELETE に家族行のアーカイブ処理を残したままにする。CASCADE によって子行が既に削除されている可能性があるため失敗または二重処理になる。
• BEFORE DELETE を選ぶと「OLD の値が取れない」と誤解する（実際は OLD は利用可能）が、問題文の制約により BEFORE では別テーブル挿入などの変更操作ができない点を見落とす。
• 「CASCADEだと子側のトリガが起動しない」と誤認する。子テーブルへの削除操作が発生すれば子側のトリガは行ごとに実行される点を理解しておく。
• 親・子双方でアーカイブ処理を残しておき、アーカイブ重複や整合性不整合を招くこと。親側の家族関連処理は削除するか無効化する必要がある。
• 大量削除時の性能問題（行ごとのトリガ実行によりオーバーヘッドが出る）を考慮しないこと。

FAQ

解答の論理構成

1. 前提確認
   【表3】に
   従業員／部署コード … DELETE CASCADE
   とある。削除元（親）は“部署”、削除先（子）は“従業員”である。
2. 現行手順の再掲
   図3手順①：『“部署”テーブルから不要な行を削除する。』
   手順⑤：『“従業員”テーブルの部署コードを更新する。』
3. 参照制約導入後の挙動
   ①で部署行を削除すると、DELETE‐CASCADE により参照している従業員行が自動削除される。
   ⑤で部署コードを更新しようとしても、既に該当従業員行はないので処理以前にデータが失われる。
4. 結論
   よって不具合の契機は①、内容は
   「削除された部署に所属している従業員が、“従業員”テーブルから削除される。」

誤りやすいポイント

• 「NO ACTIONはエラーになる」と早合点し、CASCADEの連鎖削除を見落とす。
• ⑤の UPDATE が先に走ると考え、①の影響を評価しない。
• 検査契機モードの「猶予モード」を即時チェックと混同し、トランザクション終了まで削除が遅れると誤解する。

FAQ

解答の導き方

1. 問題文から参照制約とトリガに関する重要な事実を抽出する

• 表3を見ると、従業員の「部署コード」に対する参照制約の「DELETE」の挙動モードが「CASCADE」、検査契機モードが「猶予モード」となっています。つまり「DELETE」によって参照先の部署行を削除すると、参照元の従業員行は参照制約の定義に従って自動的に処理される可能性があること、参照整合性の検査はトランザクション終了時に行われることがわかります。
• また、問題文はトリガについて「削除した ‘従業員’ テーブルの行を別テーブルに挿入する」「‘従業員家族’ テーブルのその従業員の家族の行について、別テーブルに挿入し、その後削除する」と記載しています。削除によって従業員行が除去されると、その削除に伴うトリガ処理（アーカイブや家族行の削除）が実行される点も重要です。

1. 元の順序（①→②→③→④→⑤→⑥）だと何が起きるかを考える

• ①で部署を削除してしまうと、表3の指定により「DELETE＝CASCADE」が効いている場合、当該部署に所属する従業員行が自動的に削除されます。従業員の削除に対してはトリガが定義されており、そのトリガで従業員行や従業員家族の行が別テーブルへ移されてから削除されます。すなわち、従業員の所属替えを行うはずが、従業員データが意図せず削除・アーカイブされてしまう危険があります。
• さらに、表3の「検査契機モード」が「猶予モード」であるため、参照整合性の検査はコミット時に行われます。途中でコミットしてしまうと、その時点で整合性違反（あるいは意図したCASCADEの副作用）が確定してしまう可能性があります。

1. 安全な順序の論理（なぜ ③ → ⑤ → ① が良いか）

• まず ③（部署の新規挿入）を行えば、従業員を移す先の部署コードが確実に存在します。
• 次に ⑤（従業員の部署コード更新）を行えば、従業員は旧部署から新部署へ移動し、旧部署を参照する行がなくなります。表3の参照検査が「猶予モード」なので、挿入と更新を同一トランザクション内で行い最後にコミットすれば、最終状態で参照整合性が保たれます。
• 最後に ①（旧部署の削除）を行えば、従業員が既に新部署に移っているため、DELETE の「CASCADE」によって従業員が削除される事態は避けられます。また、従業員削除に伴うトリガの誤動作（不要なアーカイブや家族データの削除）も防げます。
  以上から、同じ処理群（部署の切替）は「新部署を作る → 従業員を移す → 旧部署を消す」という順序、すなわち ③ → ⑤ → ① が安全であり正解です。

• 実際のバッチではコミット位置が運用に依存しますが、参照制約の「猶予モード」を利用するなら、可能であれば ③ と ⑤ と ① を同一トランザクションで実行してからコミットするのが最も整合性を保ちやすいです。モデル解答の全体表現は [ ③ ] → ② → [ ⑤ ] → ④ → [ ① ] → ⑥ のようにコミットが挟まれる形を示していますが、問題の問われ方（①、③、⑤の順序）に対する本質は ③ → ⑤ → ① です。

誤りやすいポイント

• 「CASCADEは親を削除しても参照元（子）が残るから即座に違反になる」と誤解する点。実際は「CASCADE」は子行を自動削除して参照不整合を残さない動作をするが、その削除がトリガや他の制約（即時モードの外部キー）と干渉して問題になることがある点を忘れやすいです。
• 「猶予モード」と「即時モード」を混同する点。猶予モードはトランザクション終了時に検査するため、トランザクション内で一時的に不整合を許すが、即時モードは各SQL実行後に検査されるため順序を厳密に守る必要があります。
• 外部キーの向き（参照先／参照元）を取り違える点。どちらが親テーブルかを誤認すると、削除や更新の影響を逆に考えてしまいます。
• トリガの影響を見落とす点。削除によるトリガが別テーブルへのアーカイブや家族行の削除を行うなら、CASCADEによる子削除は実務上大きな副作用を生みます。
• コミットの位置を軽視する点。特に「猶予モード」ではコミット時に整合性が確定するため、途中でコミットすると意図しない確定（エラーや不要な削除）につながります。

FAQ

解答の論理構成

1. 参照制約の仕様確認
   【問題文】「① 即時モード」「② 猶予モード: トランザクション終了時に…制約を検査する。」
   よって猶予モードでは処理完了後にまとめて検査。
2. 検査内容の特定
   ・表3で “従業員”.“部署コード” が “部署”.“部署コード” を参照。
   ・更新により “部署” テーブルから削除・変更されたコードが “従業員” に残っていれば参照整合性違反。
   ⇒ 検査は「更新でなくなった部署コードが、参照元に存在しないこと」の確認となる。
3. 処理時間が延びる理由
   ・図1の注記で「索引は、主キーだけに定義されている。」
   ・“部署コード” は主キーでないため索引が無い。
   ・検査時には “従業員” の全行を検索（フルスキャン）。行数が多いほど時間増大。

誤りやすいポイント

• 猶予モードを「最後のSQL実行直後」と誤解し、途中で検査が行われると考えてしまう。
• CASCADE動作と猶予モード検査を混同し、検査が不要になると思い込む。
• 索引が主キーにのみ定義されている事実を見落とし、処理時間の影響源を誤答。

FAQ