データベーススペシャリスト　令和2年午後2　解説解答

データベーススペシャリスト

Last updated at 2024-10-05Posted at 2024-09-01

問1
問2

公式サイトの問題冊子はこちら。
https://www.ipa.go.jp/shiken/mondai-kaiotu/gmcbt8000000d05l-att/2020r02o_db_pm2_qs.pdf

問1-設問1-(1)

索引を構成する列名を定義順に答えよ

住居(住居番号、ユーザー番号(FK)、地域コード(FK)、住所、世帯区分、電力会社番号(FK)、プラン番号(FK)、緯度、経度)
処理2: 「比較対象住居(同じ地域、同じ世帯区分、同じ地域かつ同じ世帯区分のいずれか)」
同じ地域に複数の世帯区分は存在するが、同じ世帯区分に複数の地域が設定されることはないので、両者のカーディナリティは下記の関係

表1で「地域コードは4桁の半角英数字」、「世帯区分は3桁の半角英数字」と記載されているが、必ずしもカーディナリティの数に比例しない(ので、この記載はトラップ)

世帯区分、地域コード

問1-設問1-(2)

①

予測について、本文中のa～cに入れる適切な字句を、本文中の用語を用いて答えよ

「索引のキー値の順番と、キー値が指す行の物理的な並び順が一致している場合(以下、クラスタ率という)が高いほど、隣接するキー値が指す行が同じページに格納されている割合が高い」
「見積ページ数は、“見積行数÷ページ当たり平均行数”の小数部を切り上げる」
クラスタ率が最も高いときは、読み込んだページが探索行で埋め尽くされているので探索行数÷ページ当たり平均行数になる

a: 探索行数
b: ページ当たり平均行数

クラスタ率が最も低い場合は、探索行ごとにページが異なる

c: 探索行数

表9中のd～fに入れる適切な数値を答えよ

表5より、使用電力は1日500件/住居
「最大*3年分**のデータを保存する。1年を360日として行数を見積もる」

500件/日×3年×360日/年

d: 540,000

表5より、ページ当たり平均行数は

54,000,000,000行÷600,000,000ページ=90

最小読み込みページ数は、探索行数÷ページ当たり平均行数なので

540,000行÷90行/ページ=6,000

e: 6,000

最大読み込みページ数は、探索行数に等しいので

f: 540,000

②

対策について、追加するテーブルのテーブル構造を答えよ

「当日の電力量は“買電”、“発電”、“使用電力”の各テーブルから求め、それ以外の電力量は追加したテーブルから求める」
買電(住居番号、年月日、時、分、買電量)
発電(住居番号、回路番号、年月日、時、分、発電量)
使用電力(住居番号、回路番号、年月日、時、分、使用電力量)
「買電、発電、使用電力を区別する列を用いる場合は、列名を“電力区分”とし」とあるので、買電、発電、使用電力を電力区分に置き換える

住居番号、年月日、電力区分、電力量

③

②のテーブルの最小読み込みページ数、最大読み込みページ数を答えよ

データ型とサイズは以下の通りなので、行長は15バイト

列名	データ型	サイズ
住居番号	INTEGER	4
年月日	DATE	4
電力区分	CHAR(1)	1
電力量	DECIMAL(10,7)	6
計		15

「どのテーブルもページ長を2000バイト、空き領域率を10%とする」なので、ページ当たり平均行数は

2000バイト×(1-0.1)÷15バイト=120行

消費電力の探索行数は2ヶ月分なので

2ヶ月×30日=60行

買電と発電の探索行数は1ヶ月分なのでそれぞれ

1ヶ月×30日=30行

トータルの探索行数は

消費電力60+買電30+発電30=120行

最小読み込みページ数は、探索行数÷平均行長
最大読み込みページ数は、探索行数

最小読み込みページ数: 1
最大読み込みページ数: 120

問1-設問2-(1)

図3中のg～kに入れる適切な字句を答えよ

W1

住居と日射量の距離を計算するため、双方共にNOT NULLであることが必須なので、INNER JOINする

g: INNER JOIN

W2

「ランク=距離を昇順に並べた順位」

h: 距離

W3

「住居番号、年月日、時、平均標準発電量=[住居番号、年月日、時ごとの標準発電量の平均]」

i: AVG(標準発電量) AS 平均標準発電量
j: GROUP BY 住居番号、年月日、時

W4

「住居番号、年月日、時、時間発電量=[住居番号、年月日、時ごとの発電量の合計]」

k: SUM(標準発電量) AS 時間発電量

問1-設問2-(2)

表11中のl～o、図4中のpに入れる適切な字句を答えよ

W1

地域コードは、住居とINNER JOINして取得する

l: 住居番号で住居とINNER JOIN

W3

「W3 AS (SELECT 地域コード、階級番号、AVG(合計使用電力量) AS 平均使用電力量 FROM W2 GROUP BY 地域コード、階級番号)」

m: 平均使用電力量=[地域コード、階級番号ごとの合計使用電力量の平均]

「W3 AS (SELECT 地域コード、階級番号、AVG(合計使用電力量) AS 平均使用電力量 FROM W2 GROUP BY 地域コード、階級番号)」

m: 平均使用電力量=[地域コード、階級番号ごとの合計使用電力量の平均]

地域名は、地域とINNER JOINして取得する

n: W3の全行を選択
o: 地域コードで地域とINNER JOIN

「地域ごとに、各住居の1ヶ月間の使用電力量の合計を大きい方から順序付け」
PARTITION BY a ORDER BY b DESCで、aの中でbの大きい順に並べる

p: PARTITION BY 地域コード ORDER BY 合計使用電力量 DESC

問1-設問3-(1)

①

この行数で本番環境の性能が推測できる理由を50字以内で述べよ

「分散は、一つ又は複数の列を分散キーとし、その値に基づいてRDBMS内部で生成するハッシュ値によって、各ノードにデータを配置する方法」
住居テーブルでは、住居番号を分散キーに各ノードにデータが配置される

本番環境

「データベースはシェアードナッシングのクラスタ構成とし、20ノードを配置する」

テスト環境

「DBサーバー2台による2ノードのクラスタ構成のテスト環境を使用する」なので、テスト環境のノード数は本番環境の10分の1
行数もノード数も10分の1なので、ノード当たりの行数は本番環境と同等

行数もノード数も本番環境の10分の1なので、ノード当たり行数が本番環境と同等になるから

②

表13の下線部について、地域コードに値を設定する上で留意すべき事項を具体的にを50字以内で述べよ
地域コードには、適切な列値を設定する

表2の処理4で実施される地域ごとの順序付け処理の量を本番環境同等にする
地域当たりの住居数(表5より)

住居100,000件÷地域1,000件=住居100件/地域

一地域あたりの住居数が本番環境と同等の100件程度になるように設定する

問1-設問3-(2)

差異1、差異2について、最も可能性が高いと考えられる際の発生原因を、それぞれ具体的に50字以内で述べよ

差異1

「指定された住居番号、年月日、ログ種別について、全機種の機器ログを対象に、住居番号、年月日、ログ番号、メーカーコード、機器種別、ロあグテキストを抽出する」
「機器ログ、買電、発電、使用電力の各テーブルは、配置方法を分散にし、住居番号を分散キーに指定する」
「ページ長が2000バイトのバッファには、480,000ページ分のサイズを指定する」
「どのテーブルもページ長を2000バイト、空き領域率を10%とする」
機器ログの見積ページ数は981,818,182ページ>>バッファサイズ
テスト環境の住居数は本番環境の10分の1なのでバッファヒット率は10倍になる

同じ住居番号と年月日の機器ログがある程度並んで登録され、クラスタ率が高い状態であるため

差異2

処理2は、住居、日射量、発電、使用電力を参照
処理3は、住居、発電を参照
「ページ長が2000バイトのバッファには、480,000ページ分のサイズを指定する」
1日あたりの使用電力のページ数=600,000,000÷(3年×360日)>555,555>バッファサイズ
処理3に不要な使用電力のデータだけで、バッファサイズを上回る

処理2によってバッファがほぼ更新され、処理3に必要な行がほとんど残っていないため

問2-設問1-(1)

欠落しているリレーションシップを補って、図3を完成させよ

「部品と素材を併せて部材と呼ぶ」

部品と素材は部材のサブタイプ

「品目にはA社が設計する専用品と、それ以外の汎用品がある」

専用品と汎用品は品目のサブタイプ

「製品の全ては専用品に該当する」

製品は専用品のサブタイプ

「専用品に該当する部品を専用部品、汎用品に該当する部品を汎用部品と呼ぶ」

専用部品は専用品のサブタイプ
汎用部品は汎用品のサブタイプ

「素材の全ては汎用品に該当する」

素材は汎用品のサブタイプ

「品目のうち、製品と専用部品には、それを生産する上で必要となる下位の品目があり、どの品目を幾つ用いるかの情報を構成と呼ぶ」
「製品の構成には、幾つかの専用部品、汎用部品、素材があり得る」
「専用部品の構成にも、幾つかの専用部品、汎用部品、素材があり得る」
製品構成、専用部品構成のスーパータイプは構成
専用部品、汎用部品、素材のスーパータイプは部材

「調達先のうち、専用部品を発注する先を協力会社(以下、BPという)と呼び、BPフラグを設定している」

BPは調達先のサブタイプ

「調達先のうち、汎用品を購入する先を仕入先と呼び、仕入先フラグを設定している」

仕入先は調達先のサブタイプ

「部品生産部とBPを総称して生産先と呼び、生産先コードを付与している」

部品生産部とBPは生産先のサブタイプ

「倉庫とBPを総称して地点と呼び、地点コードを付与している」

倉庫とBPは地点のサブタイプ

「汎用品は、その汎用品を調達する仕入先を一つに決めている」

「専用部品は、その専用部品を生産する生産先を一つに決めている」

図6より、汎用品発注、専用部品生産指示、専用部品発注、支給指示は、調達手配のサブタイプに設定

汎用品発注、専用部品生産指示、専用部品発注、支給指示は調達手配のサブタイプ

「在庫は、地点、品目ごとに把握している」
「倉庫の在庫を倉庫在庫、BPの在庫をBP在庫と呼ぶ」
「部材にはその部材の在庫を持つときのために」
「調達先のうち、専用部品を発注する先を協力会社(以下BPという)と呼び、BPフラグを設定している」
倉庫在庫に記録する品目は部材、BP在庫に記録する品目は専用部品

問2-設問1-(2)

図5中のア～コに、適切な一つ又は複数の属性名を補って、関係スキーマを完成させよ

「製品生産部は、受注に基づいて、製品の生産に必要な部材の出庫指示を行い、製品を生産する。また、生産ライン数を設定している」

ア: 生産ライン数

「部品生産部とBPを総称して生産先と呼び、生産先コードを付与している」

イ: 生産先コード(FK)
ウ: 生産先コード(FK)

「専用品には設計番号を設定し、汎用品には汎用品仕様として、メーカー名、カタログ名、カタログ発行年月、カタログ品番を連結した文字列を設定する」
「汎用品は、その汎用品を調達する仕入先を一つに決めている」

エ: 設計番号
オ: 汎用品仕様、仕入調達先コード(FK)

「専用部品には、その専用部品を輸送するときの個体重量を設定している」
「専用部品は、その専用部品を生産する生産先を一つに決めている」

カ: 個体重量、生産先コード(FK)

「製品の構成には、幾つかの専用部品、汎用部品、素材があり得る」
「専用部品の構成にも、幾つかの専用部品、汎用部品、素材があり得る」
専用部品、汎用部品、素材のスーパータイプは部材

キ: 部材品目コード(FK)
ク: 部材品目コード(FK)

「部材には、その部材の在庫を持つときのために、次を設定している」は、倉庫在庫を対象にしたもの
「調達先のうち、専用部品を発注する先を協力会社(以下BPという)と呼び、BPフラグを設定している」
倉庫在庫に記録する品目は部材、BP在庫に記録する品目は専用部品

ケ: 部材品目コード(FK)
コ: 専用部品品目コード(FK)

問2-設問2-(1)

欠落しているリレーションシップを補って、図を完成させよ

図6より、調達手配のサブタイプに汎用品発注、専用部品生産指示、専用部品発注、支給指示がある

汎用品発注、専用部品生産指示、専用部品発注、支給指示は調達手配のサブタイプ

「荷卸実績のうち支給部品のBPへの荷卸実績は、支給部品理論入庫実績でもある」

支給部品理論入庫実績は荷卸実績のサブタイプ

「親部品の発注に基づいて、BPは構成から求められる子部品を使用数分使うので、その分の支給部品理論出庫実績を記録する」
BPでの親部品の発注は、専用部品発注を指す

「輸送の必要な調達では、該当する調達手配に対応させて、輸送指示を行う」
図2より輸送の必要な調達は、専用部品発注と支給指示
図2より、支給指示に支給部品出庫指示も紐付く

図6より出庫指示と出庫実績は、主キーが同じ
支給部品理論出庫実績は、専用部品発注に基づいて記録するため、出庫指示を伴わない
つまり、出庫実績のサブタイプのうち倉庫出庫実績のみ、出庫指示と1対1のリレーションシップをもつ

図2より、専用部品の生産実績に倉庫入庫実績が紐付く

問2-設問2-(2)

図6中のサ～タに、適切な一つ又は複数の属性名を補って、関係スキーマを完成させよ

「輸送の必要な調達では、該当する調達手配に対応させて、輸送指示を行う」
調達手配の主キーは調達番号

サ: 調達番号(FK)

「荷卸実績のうち支給部品のBPへの荷卸実績は、支給部品理論入庫実績でもある」
荷卸実績の主キーは輸送番号

シ: 輸送番号(FK)

「製品生産部は、受注に基づいて、製品の生産に必要な部材の出庫指示を行い、製品を生産する」
受注の主キーは受注番号、部材の主キーは品目コード

ス: 受注番号(FK)、部材品目コード(FK)

「製品生産部は、専用部品の生産指示に基づいて、A社が内製する専用部品を生産する。また、専用部品の生産用に、部材の出庫指示を行う」
専用部品の生産指示の主キーは調達番号、部材の主キーは品目コード

セ: 専用部品生産指示調達番号(FK)、部材品目コード(FK)

図2より、支給指示に支給部品出庫指示が紐付く
支給指示の主キーは調達番号

ソ: 支給指示調達番号(FK)

「親部品の発注に基づいて、BPは構成から求められる子部品を使用数分使うので、その分の支給部品理論出庫実績を記録する」
親部品の発注の主キーは、調達番号
部品の主キーは、品目コード

タ: 専用部品発注調達番号(FK)、部品品目コード(FK)、使用数

問2-設問3-(1)

図8中のa～kに、適切な字句を入れて、関係スキーマ処理フローを完成させよ

「営業時間終了時に、翌営業日分の輸送指示についてルート別巡回順に輸送重量の和を求める」は、輸送日が翌営業日の輸送指示、を対象としていると読み取れる
c,dは荷積重量、e,fは荷卸重量

a: 輸送日
b: 輸送日
c: 輸送重量
d: ルート番号、積地巡回順
e: 輸送重量
f: ルート番号、卸地巡回順
g: ルート番号
h: ルート番号
i: 積地巡回順
j: 卸地巡回順

差引重量は荷積重量と荷卸重量の差

k: 荷積重量-荷卸重量

問2-設問3-(2)