DBデータベーススペシャリスト試験午後Ⅰ

平成30年度春期データベーススペシャリスト午後Ⅰ 問2

事例を読んで設問の答えを考えてから、解説のポイントとIPA公式の解答例・採点講評で確認しましょう。

問題

問2 データベースでの制約の実装に関する次の記述を読んで，設問1～3に答えよ。総合商社のY社は，人事情報管理にRDBMSを用いている。〔RDBMSの主な仕様〕人事情報管理データベースに用いているRDBMSの主な仕様は，次のとおりである。 1. 参照制約参照制約では，挙動モードと検査契機モードを指定できる。 (1) 挙動モード挙動モードには，次の二つがある。 ① NO ACTION：参照先のテーブルの行を削除又は更新したとき，参照元のテーブルの行に対して何もしない。 ② CASCADE：参照先のテーブルの行を削除又は更新したとき，参照元のテーブルの行にも削除又は更新を連鎖させる。 (2) 検査契機モード検査契機モードには，次の二つがある。 ① 即時モード：SQL実行終了ごとに，対象となる全ての行の実行結果に対して，制約を検査する。 ② 猶予モード：トランザクション終了時に，トランザクション内の全てのSQLを実行した結果に対して，制約を検査する。 2. トリガテーブルに対する変更操作（挿入・更新・削除）を契機に，あらかじめ定義された処理を，操作対象の行ごとに実行する。実行タイミング（挿入・更新・削除の前又は後），列値による実行条件を定義することができる。ただし，実行タイミングを挿入・更新・削除の前として定義したトリガの処理の中で，テーブルに対する変更操作を行うことはできない。〔人事情報管理データベースのテーブル〕人事情報管理データベースの主なテーブルのテーブル構造は，図1のとおりである。索引は，主キーだけに定義されている。図1 主なテーブルのテーブル構造〔人事情報管理の業務概要〕人事情報管理担当者は，業務上のイベント発生時にテーブルの更新を行うとともに，社内の各部署からの情報提供要求に対応するために，SQLで検索した結果をレポートにして提供している。業務上の主なイベントとテーブルの更新内容，及びレポート提供の一例は次のとおりである。 1. 従業員の退職満65歳での定年退職及び従業員の自己都合による退職が随時発生する。定年退職の場合，退職年月日は，満65歳の誕生日の前日である。従業員を“従業員”テーブルに登録するときに，退職年月日に定年退職の年月日を設定する。自己都合による退職の場合，退職年月日は申告された日に更新する。退職すると，“従業員”テーブルの退職フラグを，あらかじめ設定している‘0’（在籍）から‘1’（退職）に更新する。この処理は，退職年月日の当日に行う。給与計算は毎月20日締めで行う。また，毎月25日に，“従業員”テーブル及び“従業員家族”テーブルから，当月の20日までに退職した従業員の行を削除する。削除した行を別テーブルに保存するために，実行タイミングを“従業員”テーブルの削除の後としたトリガを定義している。トリガに定義している処理は，次のとおりである。・削除した“従業員”テーブルの行を別テーブルに挿入する。・“従業員家族”テーブルのその従業員の家族の行について，別テーブルに挿入し，その後削除する。毎月25日に行を削除するための，三つの述語から成るSQLの構文は表1のとおりである。表1 退職した従業員の行を削除するSQLの構文（未完成） 2. 従業員家族構成の変更従業員ごとの事由によって，家族の増減，扶養対象者数の変更などが随時発生する。このとき，“従業員家族”テーブルへの行挿入，主キー以外の列値の更新（扶養フラグは，扶養対象者でない場合は‘0’，扶養対象者の場合は‘1’に更新），行削除を行う。 3. 定期的な組織変更及び人事異動毎年4月1日及び10月1日に組織変更及び人事異動を実施している。組織変更では，部署の新設，廃止が発生する。このとき，“部署”テーブルに対する行挿入，行削除を行う。人事異動で，従業員が所属する部署が変更になった場合，“従業員”テーブルの部署コードの更新を行う。 4. レポート提供の一例福利厚生担当者から人事情報管理担当者に対して，従業員家族向けのレクリエーション企画のために，部署コード順に2007年1月1日以降に生まれた扶養対象者をもつ従業員数と扶養対象者数の一覧表が欲しいとの依頼があった。その例を図2に示す。人事情報管理担当者は，表2のSQL文を用いて，図2の一覧表を作成した。図2 福利厚生担当者から求められた一覧表の例表2 一覧表を作成するSQLの構文（未完成）〔人事情報管理データベースで発生している問題点〕定型的な業務上のイベントはアプリケーションプログラムで実装している。しかし，組織変更及び人事異動のイベントで，部署コードなどの更新する列の内容が変動するものは，次のような運用を行っている。・人事情報管理担当者が，イベント別に用意してある更新SQL文のバッチジョブを利用して，業務上のイベントに対応したテーブルの更新を実施する。・人事情報管理担当者はイベントに応じてバッチジョブへの入力データを作成している。現状の問題点として，入力データの作成ミスによって，実際に存在しないコードでテーブルを更新することがあり，給与計算システムでトラブルが起きている。この問題点を解決するために，RDBMSの参照制約機能を利用することにした。参照制約機能の利用案を表3に示す。表3 参照制約機能の利用案〔参照制約機能の利用の検討〕参照制約機能を利用する以前には，定期的な組織変更及び人事異動に対応する処理は，図3に示すように“部署”テーブルの行を更新した後，“従業員”テーブルの行を更新していた。参照制約機能を利用するに当たって，図3中の①～⑥の更新手順の変更，及び処理時間の検討を行った。図3 参照制約機能を利用する以前の更新手順設問1 〔人事情報管理の業務概要〕について，(1)，(2)に答えよ。 (1) 表1中の［a］～［c］に入れる適切な字句を答えよ。 (2) 表2中の［d］～［f］に入れる適切な字句を答えよ。設問2 表3について，(1)，(2)に答えよ。 (1) 次の(a)，(b)の処理を実行した場合，正常終了と，制約検査でエラーのどちらになるか。答案用紙の正常終了・エラーのいずれかを○で囲んで示せ。エラーとなる場合は，その理由を，40字以内で具体的に述べよ。 (a) 新規従業員登録のために，所属未定（部署コードがNULL）の行を“従業員”テーブルに挿入する。 (b) ある部署の管理者退職に伴い，“従業員”テーブルから当該従業員を削除する。 (2) “従業員”テーブル及び“従業員家族”テーブルから退職した従業員の行を削除して別テーブルに保存するトリガについて，参照制約を利用することによって不具合が発生する。その対策として，“従業員”テーブルのトリガ定義を変更した上で，新たなトリガを定義する。新たに定義するトリガについて，対象となるテーブルのテーブル名，実行タイミング，処理内容をそれぞれ答えよ。設問3 〔参照制約機能の利用の検討〕に示した，参照制約機能を利用した後について，(1)～(3)に答えよ。 (1) 図3中の①～⑥のデータの削除，挿入，更新の順序を変更せずに運用した場合，不具合が発生することがある。不具合が発生する契機を図3中の丸数字で答えよ。また，発生する不具合の内容を，40字以内で述べよ。 (2) (1)の不具合の回避のために，図3中の①，③，⑤の順序を変更する。どのように変更すればよいか，①，③，⑤の変更後の順序を答えよ。 (3) 表3に示すとおり，“従業員”テーブルの部署コードに参照制約が猶予モードで設定されている。この状況で，“部署”テーブルの部署コードを更新したときの振る舞いに関して，(a)，(b)に答えよ。 (a) RDBMSは猶予モードの制約の検査のために，トランザクション終了時にどのような検査を行っているか，検査内容を55字以内で具体的に述べよ。 (b) (a)の検査を行う際，想定よりも処理時間が長くなるおそれがある。その理由を，50字以内で具体的に述べよ。

図1 主なテーブルのテーブル構造。部署（部署コード，部署名，管理者従業員コード），職位（職位コード，職位名），従業員（従業員コード，氏名，生年月日，入社年月日，退職年月日，性別，部署コード，職位コード，退職フラグ），従業員家族（従業員コード，連番，続柄，家族氏名，生年月日，扶養フラグ）の関係スキーマ。実線下線は主キー，破線下線は外部キー。年月日の列はDATE型との注記。

表1 退職した従業員の行を削除するSQLの構文（未完成）。SQL1：DELETE FROM 従業員 WHERE 退職フラグ = [a] AND ((DAYELEMENT(退職年月日) < [b]) OR (MONTHELEMENT(退職年月日) < MONTHELEMENT([c])))。注記でDAYELEMENT関数とMONTHELEMENT関数をユーザ定義関数として説明。

図2 福利厚生担当者から求められた一覧表の例。部署別扶養対象者数（2007年1月1日以降生まれ）一覧の例。列は部署コード，従業員数，扶養対象者数。行は78001が4と7，78005が2と4，79003が5と11で，以下省略。

表2 一覧表を作成するSQLの構文（未完成）。SQL2：SELECT [d], COUNT([e] 従業員家族.従業員コード) AS 従業員数, COUNT(*) AS 扶養対象者数 FROM 従業員, 従業員家族 WHERE 従業員コードで結合し，扶養フラグ='1'かつ生年月日>=TO_DATE('2007-01-01')。GROUP BY [d] [f] [d]。TO_DATE関数の注記付き。

表3 参照制約機能の利用案。従業員.部署コード→部署（UPDATE NO ACTION，DELETE CASCADE，猶予モード），従業員.職位コード→職位（NO ACTION/CASCADE，即時），従業員家族.従業員コード→従業員（NO ACTION/CASCADE，即時），部署.管理者従業員コード→従業員（NO ACTION/NO ACTION，即時）。

図3 参照制約機能を利用する以前の更新手順。更新手順は，①“部署”テーブルから不要な行を削除，②コミット，③“部署”テーブルに新規の行を挿入，④コミット，⑤“従業員”テーブルの部署コードを更新，⑥コミットの順である。

解き方のヒントを見る

整合性確保は処理の順序と状態で考える

データ整合性に関わる問題では、ある操作が行われる前後で各表のデータがどのような状態にあるかを、問題に示された条件に沿って一段階ずつ追うことが基本です。思い込みの一般論ではなく、与えられた設定だけを根拠に考えましょう。

この問題で問われていること

確認すべき観点は一つ、処理の各時点におけるデータの状態です。どの操作がどの順で実行され、その時点で関連するデータが存在するか・どの値を持つかを整理し、制約や自動処理がどの状態を前提に働くかを照らし合わせてください。

視点

整合性を保つ仕組みは、データが変化する瞬間に何らかの判定や処理を行います。そのため、仕組みの名前や一般的な印象から結論を急ぐと誤りやすくなります。まず、関係する表とデータの関係を書き出します。次に、一連の更新を時系列に並べ、各段階でデータがどう変わるかを追います。問題に示された定義や条件を根拠にし、書かれていない前提は補わないことが大切です。

確認ポイント

一連の更新を時系列に並べ、各段階の直前と直後でデータがどう変わるかを書き出したか
各段階で、関連するデータが存在するか、どの値を持つかを問題の記述から確認したか
制約や自動処理が働く時点のデータ状態を、問題に示された定義や条件だけを根拠に照合したか

正解と解説を見る

解説

人事情報管理DBを題材に、参照制約を導入したときの影響を問う問題です。SQLの穴埋め（退職者の削除条件、部署別の集計）に加え、次の三つを読み解きます。①NULLの外部キーとNO ACTIONによるエラー、②CASCADEの連鎖削除が既存トリガを壊す仕組みとその対策、③部署の削除によって所属従業員まで連鎖削除される不具合と、それを避ける手順の組み替えです。最後に、猶予モードの検査内容と、索引がないことによる性能リスクを説明します。

問題の全体像

Y社の人事情報管理DB（部署・職位・従業員・従業員家族）では、手作業で作った入力データのミスにより、存在しないコードでテーブルが更新されるトラブルが起きていました。これを防ぐため、表3のとおり参照制約を導入します。各外部キーのUPDATEはNO ACTIONです。DELETEは、従業員の部署コード・職位コードと従業員家族の従業員コードがCASCADE、部署の管理者従業員コードがNO ACTIONです。検査契機は、従業員の部署コードだけが猶予モードで、それ以外は即時モードです。出題趣旨では、参照制約の影響で予期せぬ結果が起きないよう、処理の内容や順序を注意深く設計する力と、SQLの基礎知識を問うとしています。採点講評では、全体の正答率は低く、設問3は参照制約の知識の有無で出来が二分されたと述べています。

設問1(1)a

SQL1は、退職済みの従業員を削除するSQLです。問題文には「退職すると退職フラグを‘0’（在籍）から‘1’（退職）に更新する」とあります。したがって、退職フラグと比較する値は‘1’です。公式解答例も '1' です。退職フラグは文字の値として定義されているので、引用符付きで書く点に注意します。

設問1(1)b

削除は毎月25日に行い、対象は「当月の20日までに退職した従業員」です。この条件は二つに分けて書かれています。一つはDAYELEMENTで日の部分を比べる条件、もう一つはMONTHELEMENTで年月を比べる条件です。退職フラグが‘1’なので、退職年月日は処理日（25日）以前であることが保証されています。当月分は「日が20以下」、つまり「日 < 21」で表せます。そのため公式解答例は 21 です。前月の21日以降に退職した人は前月の処理で消されずに残っていますが、今月は後半の年月条件（前月以前）に当てはまるので削除されます。

設問1(1)c

後半の条件は「退職年月日の年月が当月より前」を表します。比較相手には、SQL実行時点の日付の年月が必要です。公式解答例では、SQL標準の日付関数 CURRENT_DATE を入れています。採点講評では、設問1のうち(1)cだけ正答率が低く、SQLの基礎知識なので是非知っておいてほしいと述べています。

設問1(2)d

図2の一覧表は、部署コードごとに集計し、部署コード順に並べたものです。SELECT句・GROUP BY句・ORDER BY句の三か所に同じ字句が入るので、集計と並べ替えのキーである部署コードを入れます。部署コードを持つのは“従業員”テーブルなので、公式解答例では表名で修飾した「従業員.部署コード」としています。FROM句で2表を結合しているため、どの表の列かを明示する書き方です。

設問1(2)e

従業員と従業員家族を結合すると、扶養対象者1人につき1行ができます。扶養対象者数は COUNT(*) で数えられます。しかし従業員数をそのまま数えると、扶養対象者を複数もつ従業員が重複して数えられます。図2でも、78001は従業員数4に対して扶養対象者数7です。そこで COUNT(DISTINCT 従業員家族.従業員コード) で重複を除いて数えます。公式解答例は DISTINCT です。

設問1(2)f

依頼は「部署コード順に」一覧表を作ることです。GROUP BY だけでは結果の並び順は保証されません。そのため最終行で並べ替えを指定します。公式解答例は ORDER BY で、後ろに d（従業員.部署コード）が続く形です。

設問2(1)(a)

従業員の部署コードは部署（部署コード）を参照します。参照制約（外部キー）は、値がNULLの行を検査しないのが基本の仕様です。所属未定でNULLの行を挿入しても、存在しないコードを参照していることにはなりません。そのため公式解答例は「正常終了」で、理由は（なし）です。採点講評では、設問2(1)は正答率が高く、題意が理解されていたとしています。

設問2(1)(b)

表3では、“部署”テーブルの管理者従業員コードが従業員（従業員コード）を参照しており、DELETEの挙動は NO ACTION、検査は即時モードです。管理者である従業員の行を削除すると、部署の行は何も変更されず、存在しない従業員を参照したまま残ります。この状態はSQL実行の終了時に検査され、エラーになります。公式解答例は「エラー」で、理由は「“部署”テーブルの管理者従業員コードの参照制約に違反するから」です。40字以内の答案では「どのテーブルの、どの列の参照制約に違反するか」を具体的に書くことが要点です。なお、従業員家族（CASCADE）の行は連鎖削除されるので、エラーの原因にはなりません。

設問2(2)

既存のトリガは、“従業員”テーブルの削除の後に動きます。削除された従業員の行を別テーブルへ保存したうえで、その従業員の家族の行を別テーブルへ挿入し、削除する処理です。ところが参照制約導入後は、従業員家族.従業員コードがDELETE CASCADE（即時）になります。従業員の行を削除した時点で家族の行が連鎖削除されるため、トリガが家族の行を保存しようとしても、行はもう残っていません。そこで、従業員側のトリガは従業員の行の保存だけに変えます。そのうえで、公式解答例のとおり新たなトリガを定義します。対象テーブルは“従業員家族”、実行タイミングは「削除の後」、処理内容は「削除した行を別テーブルに挿入する」です。こうすればCASCADEで削除される家族の行も、行ごとに保存できます。削除の前に動くトリガでは、仕様上テーブルへの変更操作（別テーブルへの挿入）ができないので、タイミングは「後」になります。採点講評では、「挿入・更新・削除」だけを書いて前か後かを答えていない解答や、実行条件を答えた解答が多かったと指摘しています。

設問3(1)

図3の順序は、①部署の削除→②コミット→③部署の挿入→④コミット→⑤従業員の部署コード更新→⑥コミットです。①の時点では、廃止される部署に所属している従業員が、まだ異動前の部署コードのまま残っています。従業員.部署コードのDELETEはCASCADEなので、部署の行を削除するとその従業員の行まで連鎖削除されてしまいます。公式解答例では、契機を①、不具合を「削除された部署に所属している従業員が、“従業員”テーブルから削除される」としています。40字以内で「どの従業員が、どのテーブルから消えるか」を書きます。

設問3(2)

不具合を避けるには、部署を削除する前に、所属していた従業員を異動先の部署へ付け替えておく必要があります。また、異動先となる新設部署は、従業員の部署コードを更新する前に存在していなければなりません。したがって順序は、部署の新規挿入（③）→従業員の部署コード更新（⑤）→不要な部署の削除（①）になります。公式解答例は［③］→②→［⑤］→④→［①］→⑥です。この順序なら、①の時点で削除対象の部署に所属する従業員がいないので、CASCADEによる連鎖削除は起きません。

設問3(3)(a)

部署コードの更新に対する挙動は NO ACTION で、参照元の値は書き換えられません。猶予モードでは、トランザクション終了時にまとめて検査します。確認すべきなのは、更新によって部署テーブルから無くなった旧部署コードを参照している従業員が残っていないかどうかです。公式解答例では「更新によって無くなった部署コードが、“従業員”テーブルの“部署コード”に存在しないことを確認する」としています。55字以内の答案では、①検査する値（更新で無くなった部署コード）、②検査先（“従業員”テーブルの部署コード）、③確認内容（存在しないこと）の三つを押さえます。

設問3(3)(b)

問題文には「索引は、主キーだけに定義されている」とあります。“従業員”テーブルの部署コードは外部キーで、主キーではないので索引がありません。(a)の検査では、無くなった部署コードを参照する従業員がいないかを調べるため、“従業員”テーブルを全件走査することになります。そのため処理時間が想定より長くなるおそれがあります。公式解答例は「“従業員”テーブルの“部署コード”に索引がなく、全行を参照しなければならないから」です。50字以内で「索引がないこと」と「全行を参照すること」の因果を書くのが要点です。

学習のポイント

表3の参照制約（挙動モード・検査契機モード）を一行ずつ業務操作に当てはめ、連鎖削除・エラー・検査コストを予測できるようにすることが重点です。あわせて、COUNT(DISTINCT)・GROUP BY・ORDER BY・CURRENT_DATEといったSQLの基礎を確実に押さえます。

出典：独立行政法人情報処理推進機構（IPA）平成30年度春期データベーススペシャリスト試験午後Ⅰ 問2（公式資料：問題冊子（PDF）／解答例／採点講評）

平成30年度春期 データベーススペシャリスト 午後Ⅰ 問2

問題