令和6年度秋期 データベーススペシャリスト 午後Ⅰ 問3

依存関係と前提条件を整理する

この問題では、定義や処理の順序、データの格納状態など、答えを左右する前提が記述の中に含まれ得ます。結論を急がず、何が何に依存しているか、どの条件が明示されているかを先に書き出してから判断すると、見落としを防ぎやすくなります。

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

問われている結論に影響する前提条件と依存関係を洗い出す観点に絞ります。どの要素が先に存在している必要があるか、データがどのような並びで保持されているとされているかなど、記述中の条件を一つずつ根拠として確認してください。

視点

制約や格納方式に関する問題では、一般論の知識だけで判断すると、問題ごとに設定された条件との食い違いに気づきにくくなります。まず、登場する要素どうしの参照や依存の向きを矢印などで整理します。次に、数量や配置に関する前提がどこに書かれているかを確認します。そのうえで、自分の判断がどの前提に基づいているのかを言葉にできるかを点検すると、根拠の弱い推測を避けられます。

確認ポイント

• 登場する要素どうしの参照・依存の向きを図や矢印で書き出したか
• 件数・サイズ・並び方など、判断に使う前提が記述のどこにあるかを確認したか
• 自分の結論がどの前提に基づくかを一文で説明できるか

解説

プロジェクト稼働管理システムを題材に、三つの力を問う問題です。一つ目は外部キー制約がテーブル定義順序・行登録順序・行削除に与える影響の理解、二つ目は外結合とウィンドウ関数を使ったSQLの設計、三つ目は索引のクラスタ性を踏まえた読込みページ数の見積りです。公式解答例では、組織長従業員コードの外部キーを後からALTER TABLEで追加すること、COALESCE(SUM(計画時間)、0)で順序付けすること、読込みページ数を22ページ・2、000ページと見積もることなどが示されています。

問題の全体像

E社がPJシステムをRDBMSで更改する事例です。組織・従業員・役職・ランク・時間単価・稼働計画・稼働実績などのテーブルが図1で与えられています。出題趣旨では、参照制約や索引のクラスタ性は初歩の概念である一方、他に及ぼす影響を正しく理解して実装することが求められるとされています。そのうえで、制約の設定が実装に与える影響を理解する能力と、索引や表の特性を性能見積りにつなげる能力が問われています。設問1は外部キーとテーブル定義・行登録・行削除の関係、設問2はSQL1の空欄補充、設問3はSQL2を入れ子ループ結合で処理したときの読込み行数・ページ数の見積りです。採点講評では全体の正答率は平均的とされ、RESTRICTの挙動、ウィンドウ関数の書き方、クラスタ性の性質の理解が課題として挙げられています。

設問1(1)(a)

図2の“組織”テーブルには外部キーが二つあります。組織長従業員コードは“従業員”を参照し、上位組織コードは“組織”自身を参照します。自己参照は定義中のテーブル自体を参照するので、他の表が未定義でも問題になりません。一方、“従業員”は未定義なので、この外部キー指定があると実行に失敗します。したがって、いったん削除する外部キーは組織長従業員コードです。公式解答例も組織長従業員コードとしています。

設問1(1)(b)

図1の参照関係を整理します。“ランク”は他を参照しません。“時間単価”はランクコードと組織コードを参照し、“役職”はランクコードを参照します。“従業員”は組織コードと役職コードを参照します。“組織”は①で定義済みなので、最初に実行できるのは参照先を持たない“ランク”です。公式解答例ではbはランクです。

設問1(1)(c)

“ランク”の次は、“組織”と“ランク”だけを参照する“時間単価”を定義できます。3番目が“役職”と決められているので、2番目は時間単価になります。公式解答例ではcは時間単価です。

設問1(1)(d)

“従業員”は“組織”と“役職”を参照するので、“役職”より後に定義する必要があります。したがって最後はdの従業員です。公式解答例も従業員としています。

設問1(1)(e)

すでに作成済みの“組織”テーブルに外部キー制約を後から加えるには、テーブル定義を変更する文を使います。“従業員”の定義後に組織長従業員コードの外部キーを追加するので、公式解答例ではALTER TABLEとしています。

設問1(2)(組織長従業員コード)

本文の行登録では、“組織”に先に行を挿入し、その後に“従業員”などへ挿入しています。“組織”の挿入時点では“従業員”に行がないため、組織長従業員コードに値を入れると参照先がなく制約違反になります。この列はNULLを許すので、挿入時はNULLにしておき、従業員の登録後に本文にあるUPDATE文で値を設定すればよいことになります。公式解答例は、挿入時にNULLを設定しておくことです。答案では、NULLを設定する点と挿入時である点を25字以内に収めます。

設問1(2)(上位組織コード)

上位組織コードは“組織”自身を参照するため、上位組織の行が先に存在しなければ下位組織を挿入できません。表1から、最上位組織の上位組織コードはNULLです。したがって最上位組織から始めて、上位から下位へ順に登録する必要があります。公式解答例は、最上位組織から上位順に組織を登録することです。

設問1(3)(SET NULL)

“時間単価”の主キーは{ランクコード、 組織コード、 適用開始年月日}なので、組織コードは主キーの一部です。主キー列にはNULLを設定できません。組織削除時に組織コードをNULLに変えるSET NULLは主キーの制約と矛盾するため、定義時または削除時に失敗するおそれがあります。公式解答例では、“時間単価”テーブルの組織コードは主キーの一部でありNULLに変更できないからとしています。答案では、主キーの一部であることとNULLにできないことの2点を入れます。

設問1(3)(RESTRICT)

時間単価はランク別・組織別・年月日別に決めているので、組織が廃止されてPJ管理に不要と判断されても、その組織コードを持つ時間単価の行が残っていることがあります。RESTRICTでは参照している行が存在すると参照先の削除が拒否されるため、“組織”の行を削除するときに失敗します。公式解答例では、“時間単価”テーブルに同じ組織コードの行が存在する場合があるからとしています。採点講評では正答率がやや低かったとされ、RESTRICTが参照先の削除を防ぐという挙動と、関与するのが“時間単価”と“組織”である点を読み取ってほしいと述べられています。

設問2(ア)

SQL1の目的には、組織ごとに計画時間の少ない順に順序付けして1～3の番号を付けるとあります。NTILE(3)をどのグループの中で適用するかはPARTITION BYで指定するので、区切りは組織コードです。公式解答例ではアは組織コードです。

設問2(イ)

順序付けの基準は計画時間の合計です。ただし、稼働計画にない従業員は外結合によってSUM(計画時間)がNULLになります。目的には計画時間をゼロで表示するとあるので、ゼロとして並べる必要があります。SELECT句と同じ式を使い、公式解答例ではCOALESCE(SUM(計画時間)、0)としています。この式はNTILEとRANKの両方のORDER BYで使います。採点講評では正答率がやや低く、ウィンドウ関数の書き方と集計条件の読み取りが求められると指摘されています。

設問2(ウ)

稼働計画に登録されていない従業員も含めるため、行を必ず残す左側の表(A)は従業員の一覧である必要があります。WHERE句の役職コードも“従業員”の列です。公式解答例ではウは従業員です。

設問2(エ)

右側の表(B)は、従業員コード・計画年・計画月で結合し、計画時間を持つ表です。これは稼働計画です。計画年・計画月の条件をWHERE句ではなくON句に書いているので、計画のない従業員も外結合で残ります。公式解答例ではエは稼働計画です。

設問3(1)

表2では従業員コードの列値個数が8、800、表3では8、000です。業務の概要2(1)に、PJに参加していない従業員も一部いるとあります。そうした従業員は稼働実績を入力しないので、“稼働実績”には現れません。公式解答例は、PJに参加していない従業員も一部いるからです。30字以内という条件なので、本文の表現をそのまま使うのが確実です。

設問3(2)

外表の“従業員”8、800行を組織コードの列値個数400で割ると、1組織当たり平均22行になります。副次索引1は低クラスタなので行へのアクセスはランダムになり、最悪の場合は1行ごとに別のページを読みます。そのため最大で22ページです。公式解答例ではfは22です。採点講評では、低クラスタな索引ではアクセスがランダムになるという性質の理解が求められています。

設問3(3)

稼働実績の9、600、000行を従業員コードの列値個数8、000で割ると、1人当たり1、200行です。稼働年月日は1、000種類しかないので、同じ日付に複数の行を持つ場合があることになります。業務の概要2(4)に、従業員は同じ日に複数PJの稼働時間を入力できるとあります。公式解答例は、従業員は同じ日に複数PJの稼働時間を入力できるからです。

設問3(4)(g)

1人当たりの1、200行は50か月分です。SQL2の対象は1か月分なので、1、200÷50=24行になります。1か月20日と見れば、1日当たり1.2行です。公式解答例ではgは24です。

設問3(4)(h)

稼働実績を計上している従業員は8、000人で、組織は400です。前提により、PJに参加しない従業員だけで構成される組織はなく、列値も均等に分布しています。したがって8、000÷400=20人です。公式解答例ではhは20です。外表から読む22人のうち2人は、実績行を持たないことになります。

設問3(4)(i)

組織当たりの集計対象の行は、20人×24行=480行です。公式解答例ではiは480です。

設問3(4)(j)

副次索引1はキーが従業員コードだけなので、1人当たり1、200行、つまり50か月分をすべて読んでからBETWEENで絞り込みます。本文の調査結果では、従業員当たり1か月分の行は高々2ページに収まっており、副次索引1は高クラスタです。そのため1人当たり最大2ページ×50か月=100ページ、組織当たりでは100ページ×20人=2、000ページになります。公式解答例ではjは2、000です。1、200行÷100行/ページのように単純に割るのではなく、本文に示された1か月当たり最大2ページという条件を使う点が重要です。

設問3(4)(k)

副次索引3のキーは{従業員コード、 稼働年月日}なので、索引の段階で対象の1か月分に絞り込めます。副次索引1では50か月分を読んでいたので、表から読む行数とページ数はいずれも50分の1になります(1人当たり1、200行→24行、100ページ→2ページ)。公式解答例ではkは50です。

学習のポイント

参照関係からテーブルの依存順序を組み立てる練習と、ON DELETEの各オプションの挙動を主キー制約と合わせて理解することが大切です。さらに、統計情報から行数とページ数を段階的に計算する手順を身に付けましょう。採点講評では、設問1(3)のRESTRICT、設問2のイ、設問3の(2)と(4)の正答率が低かったと指摘されています。