データベーススペシャリスト　平成31年 午後2　解説解答

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


公式サイトの問題冊子はこちら。
https://www.ipa.go.jp/shiken/mondai-kaiotu/gmcbt8000000ddiw-att/2019h31h_db_pm2_qs.pdf
# 問1-設問1-(1)
*表4中の太枠内に"◯"印を記入し、表を完成させよ*
- 図1より
```mermaid
graph TB
 支店-->窓口端末
 端末種別-->窓口端末
 窓口端末-->ログ収集
 行員-->行員所属
 支店-->行員所属
 行員-->ログ基本
 支店-->ログ基本
 窓口端末-->ログ基本
 ログ収集---ログ基本
 取引種別-->取引
 取引-->ログ基本
 ログ基本-->ログ明細
 画面-->ログ基本
```
## 処理5
- 「利用者が指定した前月以前の年月に一致」「店番」「明細件数」「店番」「合計明細件数」はログ基本を参照
- 「端末種別コード」は、**窓口端末**を参照
- 「店名」は、**支店**を参照
- 「端末種別名」は、ログ収集、窓口端末を通して**端末種別**を参照
## 処理6
- 「画面番号」「年月」「明細件数」「合計明細件数」はログ基本を参照
- 「店名」は、**支店**を参照
- 「取引種別名」は、**取引**を通して**取引種別**を参照
- 「タイトル」は、**画面**を参照
::: note
|　|支店 |端末種別 |窓口端末 |取引種別 |取引 |画面 |行員 |行員所属|
|---|---|---|---|---|---|---|---|---|
|処理5| ◯ | ◯ | ◯ |  |  |  |  |  |
|処理6| ◯ |  |  | ◯ | ◯ | ◯ |  |  |
:::
# 問1-設問1-(2)
*表5中の空欄を埋め、表を完成させよ*
- 「図1中のマスタ情報を格納するテーブルについて、処理1～6で結合に用いられる主キー以外の列に索引を定義することにし、その対象テーブル名・列名を表5にまとめた」
- 結合に用いられるキー列

|処理名|結合先|結合元|結合元列名|主キー|
|---|---|---|---|---|
|処理1|支店|ログ基本|店番|×|
|処理1|取引|ログ基本|取引番号|×|
|処理1|行員|ログ基本|行員番号|×|
|処理1|行員所属|行員|行員番号|◯|
|処理3|取引|ログ基本|取引番号|×|
|処理3|ログ収集|ログ基本|店番、機番、TS|×|
|処理3|ログ基本|ログ関連|ログID|◯|
|処理4|取引種別|**取引**|**取引種別コード**|**×**|
|処理4|取引|ログ基本|取引番号|◯|
|処理4|ログ基本|ログ明細|ログID|◯|
|処理5|支店|ログ基本|店番|×|
|処理5|端末種別|**窓口端末**|**端末種別コード**|**×**|
|処理5|窓口端末|ログ基本|店番、機番|×|
|処理6|支店|ログ基本|店番、機番|×|
|処理6|取引種別|**取引**|**取引種別コード**|**×**|
|処理6|画面|ログ基本|画面番号|×|

- ログ基本テーブルは参照元なので索引不要
::: note
|テーブル名|索引を定義する列名|
|---|---|
|窓口端末|端末種別コード|
|取引|取引種別コード|
:::
# 問1-設問1-(3)
*図2中のa～cに入れる適切な字句を答えよ*
- 「利用者の権限レベルによって参照可能なログが異なり、'1'の場合は、当該利用者がその時点で**所属する支店**かつ**当該利用者のログ**だけを、'2'の場合は、当該利用者がその時点で**所属する支店**のログだけを、'3',の場合は、**全ログ**を対象にする」
- IS NULLをORの最初に指定することで、NULLの場合に発生するエラーを回避できる
::: note
- a: IN('1', '2')
- b: ='1'
- c: IS NULL 
:::
*図3～5中のdに入れる適切な字句を答えよ*
- 「平均経過時間を平均経過時間の**降順**に出力する」
- 降順ソートはORDER BY 列名　DESC
::: note
- d: ORDER BY 平均経過時間 DESC  
:::
# 問1-設問1-(4)
*図5中のアの列に事前に設定すべき値の内容を、20字以内で具体的に述べよ*
- 処理2「全ログを対象に、**店番、機番ごとに、TS順に**連続する二つのログの画面番号の組を前画面番号、後画面番号としてログ間の経過時間の平均値を求め、」
::: note
- 店番、機番、TSを正順に並べた時の並び順
:::
# 問1-設問2-(1)
*表10中のe～hに入れる適切な字句を答えよ*
- 「問合せ[A] ログID='**101**'及びその**下位**の全てのログIDを抽出する」
- 「問合せ[B] ログID='**105**'及びその**上位**の全てのログIDを抽出する」
- 案1のWHERE句では、結合元の親ログID=結合先のログID
- 案3のWHERE句では、上位ノードの左端番号が小さく右端番号が大きい。下位ノードはその逆。
::: note
- e: ログID='105'
- f: B.親ログID
- g: A:左端番号>=B.左端番号
- h: A.右端番号<=B.右端番号
:::
# 問1-設問2-(2)
*本文中のi～kに入れる適切な数字を答えよ*
- 案2は上位ノードを記録するので、**'199'より下位のノード全て**が更新対象になる
- '199'より下位のノードは、図6より '103' '104' '105' '106'の4つ
- 案3はノード**'199'より大きい番号をもつノード**全てが更新対象になる
- '199'より大きい番号をもつノードは、図6より '102'以外の8つ
::: note
- i: 4
- j: 8
- k: 3
:::
*l～nに入れる適切な字句を、本文中の用語を用いて答えよ*
- 「新たに発生したログを追加するだけで、**更新は行わない**」
::: note
- l: 更新は行わない
:::
- 「WITH句にRECURSIVEを指定した**再帰的な問合せ**の構文をサポートする」
::: note
- m: 再帰的な問合せ
:::
- SELECT句で「選択」
- 「WHERE A.ログID='105'」等で、「射影」
- 「FROM ～ WHERE A.左端番号<=B.左端番号」等で結合
- 「処理1～6で**結合**に用いられる主キー以外の列に索引を定義することにし、、、」
::: note
- n: 結合
:::
# 問1-設問2-(3)
*案2の性能が案3よりも劣る理由を、30字以内で具体的に述べよ*
- 案3の「WHERE A.ログID='10x'」は、主キーの完全一致なので問合せ[A][B]ともに**索引検索**
- 「LIKE述語の検索パターンが‘ABC%’のように**前方一致**の場合は**索引検索**を選択」
- 問合せ[A]の案2「WHERE 経路 LIKE '105'」も**索引検索**
- 「**WHERE句の述語が関数を含む場合、表検索**を選択する」
- 問合せ[B]の案2「POSITION(B.経路 IN A.経路)=1」は**表検索**
- 索引検索の問合せと比較すると表検索の問合せは性能が劣る
::: note
- WHERE句の述語が関数を含んでおり、表検索になる為
:::
# 問1-設問3-(1)
*①追加の性能評価において、案Cが低い理由*
- 同じTSの行は同じ区分なので　新たなログが**同時に複数追加**されても、同じ区分に集中するため、**並列で処理できない**
::: note
- 複数行の追加が同時に発生した時に並列処理にならないから
:::
*②参照の性能評価において、どの案も同じ理由*
- ログ収集には**ログ基本から{店番、機番、TS}の組で検索**される
- 表11より、案A,B,C共に{店番、機番、TS}の組で**ローカル索引**が用意されている
::: note
- ログ基本から検索する際、どの案もローカル索引を活用できる為
:::
# 問1-設問3-(2)
*表13中のo～rに入れる適切な数値を答えよ*
- 案Dは「ハッシュ(ログID)　ハッシュ値ごとに60区分に分割」
- 処理5では、ログIDの対象は制限されていないので、探索区分数は区分数と同じ60
- 処理5では、ログIDがハッシュ化され、**ランダムに並ぶ**ので、探索ページ数はテーブルのページ数と同じ60M
::: note
- o: 60
- p: 60M
:::
- 「WHERE句の述語に、**ローカル索引の先頭列**を指定すると、ローカル索引によって区分内を探索することができる」
- 処理6「年月が前々年4月から前年3月までの**12か月分のログ**を対象に」とあるので、WHERE句の述語に「**年月**」を指定する
- 案Fのローカル索引の先頭列は「**年月**」
- 「蓄積した60か月分のログ」のうち、12か月分のログだけで探索することができるので、探索区分数/探索ページ数ともにローカル検索を持たない案D/案Eに対して**5分の1**に抑えられる
::: note
- q: 12
- r: 12M
:::
*表Eについて、処理4,5の探索ページ数の試算数が最小となるローカル索引を構成する列名を全て答えよ*
- 「WHERE句の述語に、**ローカル索引の先頭列**を指定すると、ローカル索引によって区分内を探索することができる」
- 処理5「利用者が指定した前月以前の**年月**に一致する1か月分のログを対象に、**店番**、端末種別コード明細件数を集計して」
- ローカル索引の先頭列に年月を指定すると、区分内を年月で索引探索できる
- 端末種別コードはログ基本の属性ではない
::: note
- {年月、店番}
:::
# 問1-設問3-(3)
*①処理4の性能が低下した理由を、50字以内で具体的に述べよ*
- 処理2はログ基本テーブルだけを参照すればよいが、処理5は支店、端末種別、窓口端末への**結合**もある
- 「テーブル間の結合では、**他ノードに処理を依頼**するので、自ノード内で処理する場合と比べて、**ノード間通信のオーバーヘッド**が発生する」
::: note
- 結合先テーブルを参照する際、他ノードへの処理依頼となり、ノード間通信のオーバーヘッドが発生するため
:::
*②取引種別テーブル及び取引テーブルについて行うべき対応を20字以内で具体的に述べよ*
- 「各ノードへのデータの配置方法には、次に示す**分散と複製**があり、テーブルごとにいずれかを指定する」
- 「複製による配置方法は、全ノードにテーブルの複製を保持する」
- 分散配置すると結合先が**他ノード**になるので、ノード間通信が発生するが、複製だと**同ノード**に結合先を配置できる
::: note
- データの配置方法を分散から複製に変更する
:::
*③どのように見直せばよいか、20字以内で具体的に述べよ*
- 処理4「利用者が指定した一つの**店番**について、**年月**が前年4月から前年9月までの6か月分のログを対象に」
- 「各テーブルのデータの配置方法に分散を指定し、**主キー列を分散キー**として設定することにした」
- ログ明細の分散キーは、**ログID**と**明細番号**
- ログIDは同じ「店番、機番、TS」であれば同じ値をとるので、分散キーに設定しても同じ{店番、年月}の組は**同ノードに集中**
- 明細番号は同じ{店番、年月}の中で異なる値をとるので、分散キーに設定すると**ノード間通信が発生**しやすくなる
::: note
- 分散キーから明細番号を削除する
:::
# 問2-設問1-(1)
*図1に欠落しているリレーションシップを補って、図を完成させよ*
- 「製造部門は、製パンの3工程に対応した**次の3部門の総称**である。・攪拌部門、、、・成型部門、、、・焼成部門」
::: note
- 攪拌部門、成型部門、焼成部門は製造部門のサブタイプ
::: 
- 「成型材料には、成型部門が成型する**内製成型材料**と、食材業者から調達する**外注成型材料**がある」
::: note
- 内製成型材料と外注成型材料は成型材料のサブタイプ
::: 
- 「品目のうちの貯蔵品目には、**原材料、生地材料及び外注成型材料**が含まれる」
- 「品目のうちの調達品目には、**原材料、外注成型材料**が含まれる」
- 「品目のうちの内製品目には、**生地材料、内製成型材料及び製品**が含まれる」
- 
|サブタイプ|原材料|生地材料|外注成型材料|内製成型材料|製品|
|---|---|---|---|---|---|
|貯蔵品目|◯|◯|◯|×|×|
|調達品目|◯|×|◯|×|×|
|内製品目|×|◯|×|◯|◯|
- **調達品目と内製品目**は重複しないが、**貯蔵品目**はそれらと重複する場合がある
::: note
- 原材料、生地材料及び外注成型材料は、貯蔵品目のサブタイプ
- 原材料、外注成型材料は、調達品目のサブタイプ
- 貯蔵品目は品目のサブタイプ
- 調達品目と内製品目は品目のサブタイプ
:::
- 「要求元部門には、要求先の焼成部門を設定する」
::: note
```mermaid
graph LR
 焼成部門-->要求元部門
```
:::
- 「内製成型材料には、対応する代替外注成型材料を一つ決めて設定する。外注成型材料が代替できる内製成型材料は、一つだけである」
- 「一つの内製成型材料からは、一つの製品だけ製造する」
::: note
```mermaid
graph LR
 内製成型材料---外注成型材料
 内製成型材料---製品
```
:::
- 「生地材料には、そのレシピとして、1回の製造に使用する、幾つかの原材料とその使用量を設定する」
- 生地材料と原材料は多対多
::: note
```mermaid
graph TB
 生地材料--->生地材料レシピ
 原材料-->生地材料レシピ
```
:::
- 「**内製成型材料**には、そのレシピとして、1回の材料製造に使用する、幾つかの品目(生地材料又は原材料)とその使用量を設定する」
- 「生地材料又は原材料」は**貯蔵品目**のサブタイプ
- 貯蔵品目と内製成型材料は多対多
::: note
```mermaid
graph TB
 貯蔵品目-->成型材料レシピ
 内製成型材料-->成型材料レシピ
```
::: 
# 問2-設問1-(2)
*図2に欠落しているリレーションシップを補って、図を完成させよ*
- 「発行した補充要求書を、調達品目の要求と生地材料の要求に分類する」
::: note
- 調達品目補充要求と生地材料補充要求は、補充要求のサブタイプ
::: 
- 「供給明細に対して、どの焼成実績から幾つ引き当てたかを記録する」
- 供給明細と焼成実績は多対多
::: note
```mermaid
graph TB
 供給明細-->焼成実績供給引当
 焼成実績-->焼成実績供給引当
```
:::
- 「後述する**生地材料補充要求**について、その生地材料の製造に必要な原材料の**払出しを依頼**する」
- 生地材料補充要求と払出依頼は1対1
::: note
```mermaid
graph LR
 払出依頼---生地材料補充要求
```
:::
- 「**生地材料補充要求に対するMixの実績**で、製造で識別する」
- 生地材料補充要求とMix実績は1対1
::: note
```mermaid
graph LR
 生地材料補充要求---Mix実績
```
:::
- 「注文に対する納品は、ものによって**複数回に分かれる**ことがあるが、**明細の単位は維持**される」
- 注文と納品の明細同士が1対1になる
::: note
```mermaid
graph LR
 調達品目補充要求---納品明細
```
:::
# 問2-設問1-(3)
*図3中のa～f、図4中のg～kに入れる適切な属性名を、一つ又は複数答えよ*
- 「要求元部門には、**要求先の焼成部門**を設定する」
::: note
- a: 要求先焼成部門コード
::: 
- 「調達又は内製のいずれかを表す**調整内製区分**」
- 「貯蔵対象かどうかを表す**貯蔵区分**」
- 「補充要求をかけたら**補充要求済みフラグ**をセットし、入庫したら補充要求済みフラグをリセットする」は品目単位
::: note
- b: 調整内製区分、貯蔵区分、補充要求済みフラグ
:::
- 「内製成型材料には、対応する**代替外注成型材料**を一つ決めて設定する」
::: note
- c: 代替外注成型材料品目コード
:::
- 「製品には、1回の製造単位としての焼成ロットサイズ、及び焼成に用いる**内製成型材料**を設定する」
::: note
- d: 内製成型材料品目コード
:::
- 「生地材料には、そのレシピとして、1回の製造に使用する、**幾つかの原材料**とその**使用量**を設定する」
- 生地材料レシピは、原材料と生地材料を**多対多**で紐付ける為の連関エンティティ
::: note
- e: <INS>使用品目コード</INS>、<INS>生地材料品目コード</INS>、使用量
:::
- 「内製成型材料には、そのレシピとして、1回の製造に使用する、**幾つかの品目(生地材料又は原材料)**とその**使用量**を設定する」
- 
::: note
- f: <INS>使用品目コード</INS>、<INS>内製成型材料品目コード</INS>、使用量
:::
- 「供給明細に対して、どの焼成実績から**幾つ引き当てたか**を記録する」
- 供給明細は、**供給番号**と**供給明細番号**で識別する
- 焼成実績は、**製造番号**で識別する
::: note
- g: <INS>供給番号</INS>、<INS>供給明細番号</INS>、<INS>製造番号</INS>、引当数
:::
- 「**成型材料製造依頼**に対して製造可否が可となった分について、成型に必要な生地材料及び原材料の払出しを依頼する」
- 「**成型材料製造依頼**に対して製造可否が否となった分について、焼成に必要な外注成型材料の払出しを依頼する」
- 「後述する**生地材料補充要求**について、その生地材料の製造に必要な原材料の払出しを依頼する」 
- 成型材料製造依頼は、**成型材料製造依頼番号**で識別する
- 生地材料補充要求は、**補充要求番号**で識別する
::: note
- h: 成型材料製造依頼番号(FK)、補充要求番号(FK)
:::
- 補充要求のサブタイプなので、主キーに補充要求番号も
- 「生地材料補充要求は、**在庫確認の都度**、**Mix部門に送る**」
- 「貯蔵庫からの払い出しは、**同じロケーション内の部門**に限って行う」
- 「生地材料は**毎時間帯**終了後に、在庫管理を行う」
- 毎時間帯終了後の**在庫確認の都度**、補充要求の対象を判別するために**時間帯**の記録が必要
- **新館と本館のどちらのMix部門**に要求するのかを示す**部門コード**が必要
::: note
- i: <INS>補充要求番号</INS>、在庫確認時間帯、Mix部門コード(FK)
:::
- 補充要求のサブタイプなので、主キーに補充要求番号
- 「調達品目補充要求は、注文に対する注文明細に位置付ける」
::: note
- j: <INS>補充要求番号</INS>、注文番号(FK)
:::
- 「注文に対する納品は、ものによって複数回に分かれることがあるが、**明細の単位は維持**される」
- 注文と納品の明細同士が1対1になる
- 「調達品目補充要求は、**注文に対する注文明細**に位置付ける」
- 調達品目補充要求の主キーは補充要求番号
::: note
- k: 補充要求番号(FK)
:::

# 問2-設問2-(1)
*①幾つかの行が不要になる。その行番号を全て答えよ*
```mermaid
graph TB
 食材業者-->|原材料|本館貯蔵庫
 本館貯蔵庫-->|原材料|本館Mix部門
 本館Mix部門-->|生地材料|本館成型部門
 本館成型部門-->|成型材料|本館焼成部門
 食材業者-->|外注成型材料|本館貯蔵庫
 本館貯蔵庫-->|外注成型材料|本館焼成部門
 本館焼成部門-->|製品|MD
 食材業者-->|原材料|新館貯蔵庫 
 新館貯蔵庫-->|原材料|新館Mix部門
 新館Mix部門-->|生地材料|新館成型部門
 新館成型部門-->|成型材料|新館焼成部門
 食材業者-->|外注成型材料|新館貯蔵庫
 新館貯蔵庫-->|外注成型材料|新館焼成部門
 新館焼成部門-->|製品|CD_CF_BQ
```
- 「**新館Mix部門**と**新館成型部門**を廃止し、それぞれ本館Mix部門、本館成型部門に集約する」
- **新館Mix部門か新館成型部門がF**(始点)になることがなくなるので、行12,14は不要
- 新館Mix部門と新館成型部門を廃止すると、**食材業者～新館成型部門への、原材料と生地材料の物流**もなくなるので、行3,9,10も不要
::: note
- 3,9,10,12,14
:::
*②行番号15,17に必要となる指示情報を答えよ*
- 行番号15,17はの実績情報は**焼成実績票**「焼成ロットごとの**焼成実績を記録**する伝票で、製造番号で識別する」
- 「**焼成指示**では、対象の年月日、時間帯について、使用する焼成部門ごとの窯の段を割り当てて焼成する製造を決め、1枚の天板の焼成ごとに焼成番号を発番する」
::: note
- 焼成指示
::: 
*③表1に倣って表2を完成させよ*
- 「内製限定にした焼成指示を先に、**成型部門**に対して**成型材料製造依頼**をかける」
- 「**新館**成型部門を廃止」し、「**本館**成型部門に集約」されたので、**新館**焼成部門は**本館**成型部門に成型材料製造依頼をかける
::: note
|行番号|食材業者|本館貯蔵庫|新館貯蔵庫|本館Mix部門|本館成型部門|本館焼成部門|新館Mix部門|新館成型部門|新館焼成部門|MD|CD|BQ|CF|原材料|生地材料|成型材料|製品|指示情報|実績情報|
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|21|||||F||||T|||||||||成型材料製造依頼書|成型実績票|
:::
# 問2-設問2-(2)
*欠落しているリレーションシップを補って、図を完成させよ*
- 「要求元部門からの**要求を、同じ製品について**内製限定の分を先に**引き当てる**。引き当てた要求が内製限定の場合、引き当てられた**焼成指示**を**内製限定**にする」
- 要求には、内製限定の焼成指示とそうでない焼成指示が**混在する**場合がある
- 焼成指示要求引当は、**要求明細と焼成指示をつなぐ**連関エンティティになる
::: note
```mermaid
graph TB
 要求明細-->焼成指示要求引当
 焼成指示-->焼成指示要求引当
```
:::
- 「内製限定にした焼成指示を先に、成型部門に対して成型材料製造依頼をかける」
- 焼成指示と成型材料製造依頼は1対1
::: note
```mermaid
graph LR
 焼成指示---成型材料製造依頼
```
:::
- 焼成指示と焼成実績は1対1
::: note
```mermaid
graph LR
 焼成指示---焼成実績
```
:::
# 問2-設問2-(3)
*図6中のl～nに入れる適切な属性名を、一つ又は複数答えよ*
- 焼成指示要求引当は、**要求明細と焼成指示をつなぐ**連関エンティティになる
- 要求の主キーは**要求番号**
- 焼成指示の主キーは{**対象年月日、対象時間帯、焼成部門コード、焼成番号**}
::: note
l: <INS>要求番号</INS>、<INS>要求明細番号</INS>、<INS>対象年月日</INS>、<INS>対象時間帯</INS>、<INS>焼成部門コード</INS>、<INS>焼成番号</INS>
:::
- どの焼成指示に対する成形材料製造依頼なのかを記録する
- 焼成指示の主キーは{**対象年月日、対象時間帯、焼成部門コード、焼成番号**}
::: note
m: 対象年月日(FK)、対象時間帯(FK)、焼成部門コード(FK)、焼成番号(FK)
:::
- どの焼成指示に対する焼成実績なのかを記録する
- 焼成指示の主キーは{**対象年月日、対象時間帯、焼成部門コード、焼成番号**}
- 「外部キーの役割を持たせるためにサブタイプに継承した属性は、**前後を"["と"]"で挟んで明示**すること」とあるので、スーパータイプ製造実績に含まれる属性を[]で囲む
::: note
n: [対象年月日(FK)]、[対象時間帯(FK)]、焼成番号(FK)
:::