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

- 問1
  - [設問1-(1)](#問1-設問1-1)
  - [設問1-(2)](#問1-設問1-2)
  - [設問2-(1)](#問1-設問2-1)
  - [設問2-(2)](#問1-設問2-2)
  - [設問3-(1)](#問1-設問3-1)
  - [設問3-(2)](#問1-設問3-2)
- 問2
  - [設問1-(1)](#問2-設問1-1)
  - [設問1-(2)](#問2-設問1-2)
  - [設問2-(1)](#問2-設問2-1)
  - [設問2-(2)](#問2-設問2-2)
  - [設問3-(1)](#問2-設問3-1)
  - [設問3-(2)](#問2-設問3-2)
  - [設問3-(3)](#問2-設問3-3)

公式サイトの問題冊子はこちら。
https://www.ipa.go.jp/shiken/mondai-kaiotu/gmcbt8000000d05l-att/2020r02o_db_pm2_qs.pdf
# 問1-設問1-(1)
*索引を構成する列名を定義順に答えよ*
- 住居(<INS>住居番号</INS>、ユーザー番号(FK)、**地域コード**(FK)、住所、**世帯区分**、電力会社番号(FK)、プラン番号(FK)、緯度、経度)
- 処理2: 「比較対象住居(**同じ地域**、**同じ世帯区分**、**同じ地域かつ同じ世帯区分**のいずれか)」
- **同じ地域に複数の世帯区分**は存在するが、同じ世帯区分に複数の地域が設定されることはないので、両者のカーディナリティは下記の関係
```mermaid
graph LR
 地域コード-->世帯区分
```
- 表1で「地域コードは**4桁の半角英数字**」、「世帯区分は**3桁の半角英数字**」と記載されているが、必ずしもカーディナリティの数に比例しない(ので、この記載は**トラップ**)
::: note info
- 世帯区分、地域コード
:::
# 問1-設問1-(2)
## ①
*予測について、本文中のa～cに入れる適切な字句を、本文中の用語を用いて答えよ*
- 「**索引のキー値の順番と、キー値が指す行の物理的な並び順が一致している**場合(以下、**クラスタ率**という)が高いほど、隣接するキー値が指す行が同じページに格納されている割合が高い」
- 「見積ページ数は、“**見積行数÷ページ当たり平均行数**”の小数部を切り上げる」
- クラスタ率が最も高いときは、読み込んだページが探索行で埋め尽くされているので**探索行数÷ページ当たり平均行数に**なる
::: note info
- a: 探索行数
- b: ページ当たり平均行数
:::
- クラスタ率が最も低い場合は、探索行ごとにページが異なる
::: note info
- c: 探索行数
:::
*表9中のd～fに入れる適切な数値を答えよ*
- 表5より、使用電力は**1日500件/住居**
- 「最大*3年分**のデータを保存する。**1年を360日**として行数を見積もる」
```math
500件/日×3年×360日/年
```
::: note info
- d: 540,000
:::
- 表5より、ページ当たり平均行数は
```math
54,000,000,000行÷600,000,000ページ=90
```
- 最小読み込みページ数は、探索行数÷ページ当たり平均行数なので
```math
540,000行÷90行/ページ=6,000
```
::: note info
- e: 6,000
:::
- 最大読み込みページ数は、探索行数に等しいので
::: note info
- f: 540,000
:::
## ②
*対策について、追加するテーブルのテーブル構造を答えよ*
- 「当日の電力量は“買電”、“発電”、“使用電力”の各テーブルから求め、それ以外の電力量は追加したテーブルから求める」
- 買電(<INS>**住居番号**</INS>、<INS>**年月日**</INS>、<INS>時</INS>、<INS>分</INS>、**買電量**)
- 発電(<INS>**住居番号**</INS>、<INS>回路番号</INS>、<INS>**年月日**</INS>、<INS>時</INS>、<INS>分</INS>、**発電量**)
- 使用電力(<INS>**住居番号**</INS>、<INS>回路番号</INS>、<INS>**年月日**</INS>、<INS>時</INS>、<INS>分</INS>、**使用電力量**) 
- 「買電、発電、使用電力を区別する列を用いる場合は、列名を“**電力区分**”とし」とあるので、買電、発電、使用電力を**電力区分**に置き換える
::: note info
- <INS>住居番号</INS>、<INS>年月日</INS>、<INS>電力区分</INS>、電力量
:::
## ③
*②のテーブルの最小読み込みページ数、最大読み込みページ数を答えよ*
- データ型とサイズは以下の通りなので、行長は15バイト

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

- 「どのテーブルもページ長を2000バイト、空き領域率を10%とする」なので、ページ当たり平均行数は
```math
2000バイト×(1-0.1)÷15バイト=120行 
```
- 消費電力の探索行数は2ヶ月分なので
```math
2ヶ月×30日=60行
```
- 買電と発電の探索行数は1ヶ月分なのでそれぞれ
```math
1ヶ月×30日=30行
```
- トータルの探索行数は
```math
消費電力60+買電30+発電30=120行
```
- 最小読み込みページ数は、探索行数÷平均行長
- 最大読み込みページ数は、探索行数
::: note info
- 最小読み込みページ数: 1
- 最大読み込みページ数: 120
:::
# 問1-設問2-(1)
*図3中のg～kに入れる適切な字句を答えよ*
## W1 
- 住居と日射量の距離を計算するため、双方共にNOT NULLであることが必須なので、INNER JOINする
::: note info
- g: INNER JOIN
:::
## W2 
- 「ランク=**距離を昇順に並べた順位**」
::: note info
- h: 距離
:::
## W3 
- 「住居番号、年月日、時、平均標準発電量=[住居番号、年月日、**時ごとの標準発電量の平均**]」
::: note info
- i: AVG(標準発電量) AS 平均標準発電量
- j: GROUP BY 住居番号、年月日、時
:::
## W4 
- 「住居番号、年月日、時、時間発電量=[住居番号、年月日、**時ごとの発電量の合計**]」
::: note info
- k: SUM(標準発電量) AS 時間発電量
:::
# 問1-設問2-(2)
*表11中のl～o、図4中のpに入れる適切な字句を答えよ*
## W1 
- 地域コードは、住居とINNER JOINして取得する
::: note info
- l: 住居番号で住居とINNER JOIN 
:::
## W3
- 「W3 AS (SELECT 地域コード、階級番号、**AVG(合計使用電力量) AS 平均使用電力量** FROM W2 **GROUP BY 地域コード、階級番号**)」
::: note info
- m: 平均使用電力量=[地域コード、階級番号ごとの合計使用電力量の平均] 
:::
- 「W3 AS (SELECT 地域コード、階級番号、**AVG(合計使用電力量) AS 平均使用電力量** FROM W2 **GROUP BY 地域コード、階級番号**)」
::: note info
- m: 平均使用電力量=[地域コード、階級番号ごとの合計使用電力量の平均] 
:::
- 地域名は、地域とINNER JOINして取得する
::: note info
- n: W3の全行を選択
- o: 地域コードで地域とINNER JOIN 
:::
- 「地域ごとに、各住居の1ヶ月間の使用電力量の合計を大きい方から順序付け」
- PARTITION BY a ORDER BY b DESCで、aの中でbの大きい順に並べる
::: note info
- p: PARTITION BY 地域コード ORDER BY 合計使用電力量 DESC
:::
# 問1-設問3-(1)
## ①
*この行数で本番環境の性能が推測できる理由を50字以内で述べよ*
- 「分散は、一つ又は複数の列を分散キーとし、その値に基づいてRDBMS内部で生成するハッシュ値によって、各ノードにデータを配置する方法」
- 住居テーブルでは、住居番号を分散キーに各ノードにデータが配置される
### 本番環境
- 「データベースはシェアードナッシングのクラスタ構成とし、20ノードを配置する」 
### テスト環境
- 「DBサーバー2台による2ノードのクラスタ構成のテスト環境を使用する」なので、テスト環境のノード数は本番環境の10分の1
- 行数もノード数も10分の1なので、**ノード当たりの行数は本番環境と同等**
::: note info
- 行数もノード数も本番環境の10分の1なので、ノード当たり行数が本番環境と同等になるから
:::
## ②
*表13の下線部について、地域コードに値を設定する上で留意すべき事項を具体的にを50字以内で述べよ*
<INS>地域コードには、適切な列値を設定する</INS>
- 表2の処理4で実施される地域ごとの順序付け処理の量を本番環境同等にする
- 地域当たりの住居数(表5より)
```math
住居100,000件÷地域1,000件=住居100件/地域
```
::: note info
- 一地域あたりの住居数が本番環境と同等の100件程度になるように設定する
:::
# 問1-設問3-(2)
*差異1、差異2について、最も可能性が高いと考えられる際の発生原因を、それぞれ具体的に50字以内で述べよ*
## 差異1
- 「指定された**住居番号、年月日、ログ種別**について、全機種の機器ログを対象に、住居番号、年月日、ログ番号、メーカーコード、機器種別、ロあグテキストを抽出する」
- 「**機器ログ**、買電、発電、使用電力の各テーブルは、配置方法を分散にし、**住居番号を分散キー**に指定する」
- 「ページ長が2000バイトのバッファには、**480,000ページ分のサイズ**を指定する」
- 「どのテーブルも**ページ長を2000バイト**、空き領域率を10%とする」
- 機器ログの見積ページ数は981,818,182ページ>>バッファサイズ
- テスト環境の住居数は本番環境の10分の1なのでバッファヒット率は10倍になる
::: note info
- 同じ住居番号と年月日の機器ログがある程度並んで登録され、クラスタ率が高い状態であるため
:::
## 差異2
- 処理2は、住居、日射量、発電、**使用電力**を参照
- 処理3は、住居、発電を参照
- 「ページ長が2000バイトのバッファには、**480,000ページ分のサイズ**を指定する」
- 1日あたりの使用電力のページ数=600,000,000÷(3年×360日)>555,555>バッファサイズ
- 処理3に不要な使用電力のデータだけで、**バッファサイズを上回る**
::: note info
- 処理2によってバッファがほぼ更新され、処理3に必要な行がほとんど残っていないため
:::
# 問2-設問1-(1)
*欠落しているリレーションシップを補って、図3を完成させよ*
- 「**部品**と**素材**を併せて**部材**と呼ぶ」
::: note info
- 部品と素材は部材のサブタイプ
:::
- 「**品目**にはA社が設計する**専用品**と、それ以外の**汎用品**がある」
::: note info
- 専用品と汎用品は品目のサブタイプ
:::
- 「**製品**の全ては**専用品**に該当する」
::: note info
- 製品は専用品のサブタイプ
:::
- 「**専用品**に該当する部品を**専用部品**、**汎用品**に該当する部品を**汎用部品**と呼ぶ」
::: note info
- 専用部品は専用品のサブタイプ
- 汎用部品は汎用品のサブタイプ
:::
- 「**素材**の全ては**汎用品**に該当する」
::: note info
- 素材は汎用品のサブタイプ
:::
- 「品目のうち、**製品と専用部品**には、それを生産する上で必要となる下位の品目があり、どの品目を幾つ用いるかの情報を**構成**と呼ぶ」
- 「**製品の構成**には、幾つかの専用部品、汎用部品、素材があり得る」
- 「**専用部品の構成**にも、幾つかの専用部品、汎用部品、素材があり得る」
- 製品構成、専用部品構成のスーパータイプは**構成**
- 専用部品、汎用部品、素材のスーパータイプは**部材**
::: note info
```mermaid
graph TB
 部材-->構成
```
:::
- 「**調達先**のうち、専用部品を発注する先を協力会社(以下、**BP**という)と呼び、BPフラグを設定している」
::: note info
- BPは調達先のサブタイプ
:::
- 「**調達先**のうち、汎用品を購入する先を**仕入先**と呼び、仕入先フラグを設定している」
::: note info
- 仕入先は調達先のサブタイプ
:::
- 「**部品生産部**と**BP**を総称して**生産先**と呼び、生産先コードを付与している」
::: note info
- 部品生産部とBPは生産先のサブタイプ
:::
- 「**倉庫**と**BP**を総称して**地点**と呼び、地点コードを付与している」
::: note info
- 倉庫とBPは地点のサブタイプ
:::
- 「**汎用品**は、その汎用品を調達する**仕入先**を一つに決めている」
::: note info
```mermaid
graph LR
 仕入先-->汎用品
```
:::
- 「**専用部品**は、その専用部品を生産する**生産先**を一つに決めている」
::: note info
```mermaid
graph LR
 生産先-->生産部品
```
:::
- 図6より、**汎用品発注、専用部品生産指示、専用部品発注、支給指示**は、**調達手配のサブタイプ**に設定
::: note info
- 汎用品発注、専用部品生産指示、専用部品発注、支給指示は調達手配のサブタイプ
:::
- 「在庫は、地点、**品目**ごとに把握している」
- 「倉庫の在庫を倉庫在庫、BPの在庫をBP在庫と呼ぶ」
- 「**部材**にはその部材の**在庫を持つ**ときのために」
- 「調達先のうち、**専用部品**を発注する先を協力会社(以下**BP**という)と呼び、BPフラグを設定している」
- 倉庫在庫に記録する品目は部材、BP在庫に記録する品目は専用部品
::: note info
```mermaid
graph LR
 部材-->倉庫在庫
 専用部品-->BP在庫
```
:::
# 問2-設問1-(2)
*図5中のア～コに、適切な一つ又は複数の属性名を補って、関係スキーマを完成させよ*
- 「製品生産部は、受注に基づいて、製品の生産に必要な部材の出庫指示を行い、製品を生産する。また、**生産ライン数**を設定している」
::: note info
- ア: 生産ライン数
:::
- 「部品生産部とBPを総称して生産先と呼び、**生産先コード**を付与している」
::: note info
- イ: 生産先コード(FK)
- ウ: 生産先コード(FK)
::: 
- 「専用品には**設計番号**を設定し、汎用品には**汎用品仕様**として、メーカー名、カタログ名、カタログ発行年月、カタログ品番を**連結した文字列**を設定する」
- 「汎用品は、その汎用品を調達する**仕入先**を一つに決めている」
::: note info
- エ: 設計番号
- オ: 汎用品仕様、仕入調達先コード(FK)
::: 
- 「専用部品には、その専用部品を輸送するときの**個体重量**を設定している」
- 「専用部品は、その専用部品を生産する**生産先**を一つに決めている」
::: note info
- カ: 個体重量、生産先コード(FK)
::: 
- 「製品の構成には、幾つかの**専用部品、汎用部品、素材**があり得る」
- 「専用部品の構成にも、幾つかの**専用部品、汎用部品、素材**があり得る」
- 専用部品、汎用部品、素材のスーパータイプは**部材**
::: note info
- キ: 部材品目コード(FK)
- ク: 部材品目コード(FK)
::: 
- 「**部材**には、その部材の在庫を持つときのために、次を設定している」は、**倉庫在庫**を対象にしたもの
- 「調達先のうち、**専用部品**を発注する先を協力会社(以下**BP**という)と呼び、BPフラグを設定している」
- 倉庫在庫に記録する品目は部材、BP在庫に記録する品目は専用部品
::: note info
- ケ: 部材品目コード(FK)
- コ: 専用部品品目コード(FK)
::: 
# 問2-設問2-(1)
*欠落しているリレーションシップを補って、図を完成させよ*
- 図6より、**調達手配**のサブタイプに**汎用品発注、専用部品生産指示、専用部品発注、支給指示**がある
::: note info
- 汎用品発注、専用部品生産指示、専用部品発注、支給指示は調達手配のサブタイプ
:::
- 「**荷卸実績**のうち支給部品のBPへの荷卸実績は、**支給部品理論入庫実績**でもある」
::: note info
- 支給部品理論入庫実績は荷卸実績のサブタイプ
:::
- 「**親部品の発注**に基づいて、BPは構成から求められる子部品を**使用数分**使うので、その分の**支給部品理論出庫実績**を記録する」
- BPでの**親部品の発注**は、**専用部品発注**を指す
::: note info
```mermaid
graph LR
 専用部品発注-->支給部品理論出庫実績
```
:::
- 「**輸送の必要な調達**では、該当する調達手配に対応させて、**輸送指示**を行う」
- 図2より輸送の必要な調達は、専用部品発注と**支給指示**
- 図2より、支給指示に支給部品出庫指示も紐付く
::: note info
```mermaid
graph LR
 支給指示---輸送指示
 支給指示---支給部品出庫指示
```
:::
- 図6より**出庫指示**と出庫実績は、主キーが同じ
- 支給部品理論出庫実績は、専用部品発注に基づいて記録するため、**出庫指示を伴わない**
- つまり、出庫実績のサブタイプのうち**倉庫出庫実績のみ**、出庫指示と1対1のリレーションシップをもつ
::: note info
```mermaid
graph LR
 出庫指示---倉庫出庫実績
```
:::
- 図2より、専用部品の生産実績に倉庫入庫実績が紐付く
::: note info
```mermaid
graph LR
 専用部品の生産実績---倉庫入庫実績
```
:::
# 問2-設問2-(2)
*図6中のサ～タに、適切な一つ又は複数の属性名を補って、関係スキーマを完成させよ*
- 「輸送の必要な調達では、**該当する調達手配**に対応させて、輸送指示を行う」
- 調達手配の主キーは**調達番号**
::: note info
- サ: 調達番号(FK)
:::
- 「荷卸実績のうち支給部品のBPへの**荷卸実績**は、**支給部品理論入庫実績**でもある」
- 荷卸実績の主キーは**輸送番号**
::: note info
- シ: 輸送番号(FK)
:::
- 「製品生産部は、**受注**に基づいて、製品の生産に必要な**部材**の**出庫指示**を行い、製品を生産する」
- 受注の主キーは**受注番号**、部材の主キーは**品目コード**
::: note info
- ス: 受注番号(FK)、部材品目コード(FK)
:::
- 「製品生産部は、**専用部品の生産指示**に基づいて、A社が内製する専用部品を生産する。また、専用部品の生産用に、**部材**の出庫指示を行う」
- 専用部品の生産指示の主キーは**調達番号**、部材の主キーは**品目コード**
::: note info
- セ: 専用部品生産指示調達番号(FK)、部材品目コード(FK)
:::
- 図2より、支給指示に支給部品出庫指示が紐付く
- 支給指示の主キーは調達番号
::: note info
- ソ: 支給指示調達番号(FK)
:::
- 「**親部品の発注**に基づいて、BPは構成から求められる子部品を**使用数分**使うので、その分の**支給部品理論出庫実績**を記録する」
- **親部品の発注**の主キーは、**調達番号**
- **部品**の主キーは、**品目コード**
::: note info
- タ: 専用部品発注調達番号(FK)、部品品目コード(FK)、使用数
:::
# 問2-設問3-(1)
*図8中のa～kに、適切な字句を入れて、関係スキーマ処理フローを完成させよ*
- 「営業時間終了時に、**翌営業日分の輸送指示**について**ルート別巡回順**に輸送重量の和を求める」は、**輸送日**が翌営業日の輸送指示、を対象としていると読み取れる
- c,dは荷積重量、e,fは荷卸重量 
::: note info
- a: 輸送日
- b: 輸送日 
- c: 輸送重量
- d: ルート番号、積地巡回順
- e: 輸送重量
- f: ルート番号、卸地巡回順 
- g: ルート番号
- h: ルート番号
- i: 積地巡回順
- j: 卸地巡回順 
:::
- 差引重量は荷積重量と荷卸重量の差
::: note info
- k: 荷積重量-荷卸重量 
:::
# 問2-設問3-(2)
*欠落しているリレーションシップを補って、図を完成させよ*
- 「求めた輸送重量の和が、車両の最大積載重量を越えていた場合、巡回順と輸送番号の順で、累計輸送重量が最大積載重量を超過した以降の輸送指示に対して、**別の車両**を割り当て、車両を**確定**させる」
- 輸送指示は車両ごとに組み換えられる
::: note info
```mermaid
graph LR
 輸送指示---確定輸送指示---輸送実績
```
:::
# 問2-設問3-(3)
*(2)で追加したエンティティタイプ“確定輸送指示”について、関係スキーマを答えよ*
- ルートにあらかじめ定められた車両とは**別の車両を割り当てた**ことを確定輸送指示には記録する
::: note info
- 確定輸送指示(<INS>輸送番号</INS>、車両番号(FK))
:::