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

- 問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)
- 問3
  - [設問1-(1)](#問3-設問1-1)
  - [設問1-(2)](#問3-設問1-2)
  - [設問1-(3)](#問3-設問1-3)
  - [設問1-(4)](#問3-設問1-4)
  - [設問1-(5)](#問3-設問1-5)
  - [設問2-(1)](#問3-設問2-1)
  - [設問2-(2)](#問3-設問2-2)

公式サイトの問題冊子はこちら。
https://www.ipa.go.jp/shiken/mondai-kaiotu/gmcbt8000000fabr-att/2018h30h_db_pm1_qs.pdf
# 問1-設問1-(1)
*図2中のa～kに入れる適切な属性名を答えよ*
- 「組織には、コピー機を販売する営業所と、コピー機の設置と保守を行うサービスセンタがあり、**組織区分**で分類する」
::: note info
- a: 組織区分
:::
- 「地域ごとに、担当**SC**を決めている」
::: note info
- b: SC組織コード(FK)
:::
- 「営業担当者はいずれかの**営業所**に所属する」
::: note info
- c: 営業所組織コード(FK)
:::
- 「顧客ごとに、担当**営業所**を決めている」
::: note info
- d: 営業所組織コード(FK)
:::
- 「設置営業所は、**顧客コード**と設置事業所コードで識別し、名称、住所、その住所を基にした**地域コード**を登録する」
::: note info
- e: <INS>顧客コード</INS>
- f: 地域コード(FK) 
:::
- 「見積りは、見積番号で識別し、案件名、**顧客コード**、担当**営業所の組織コード**、営業担当者の**社員番号**、見積年月日、納期年月日、見積有効期限年月日、商品コード、商品コードごとの数量、見積単価を登録する」
::: note info
- g: 顧客コード(FK)
- h: 営業所組織コード(FK)
- i: 社員番号(FK) 
:::
- 「受注は、受注番号で識別し、該当する**見積番号**、受注年月日を登録する」
::: note info
- j: 見積番号(FK) 
:::
- 「受注明細は、設置の単位であり、本体製品1台単位、又はセット製品1セット単位に作成し、**設置事業所**、設置場所詳細、設置補足を登録する」
- 「設置事業所は、**顧客コード**と設置事業所コードで識別」
::: note info
- k: 商品コード(FK) 
:::
# 問1-設問1-(2)
*必要なリレーションシップを全て記入し、図を完成させよ*
- 「設置事業所は、**顧客コード**と設置事業所コードで識別し、、、**地域コード**を登録する」
- 顧客と設置事業所は1対多
- 地域と設置事業所は1対多
::: note info
```mermaid
graph TB
 顧客-->設置事業所
 地域-->設置事業所 
```
:::
- 「顧客ごとに、担当**営業所**を決めている」
- 営業所と顧客は1対多
::: note info
```mermaid
graph TB
 営業所-->顧客
```
:::
- 「地域ごとに、担当**SC**を決めている」
- SCと地域は1対多
::: note info
```mermaid
graph TB
 サービスセンタ-->地域
```
:::
- 「営業担当者は、いずれか一つの営業所に所属する」
- 営業所と営業担当者は1対多
::: note info
```mermaid
graph TB
 営業所-->営業担当者
```
:::
- 「見積りは、、、**顧客コード**、**担当営業所の組織コード**、**営業担当者**の社員番号、、、**商品コード**、、、を登録する」
- 顧客と見積は1対多
- 営業所と見積は1対多
- 営業担当者と見積は1対多
- 商品と見積は1対多
::: note info
```mermaid
graph TB
 営業担当者-->見積
 営業所-->見積 
 顧客-->見積
 商品-->見積
```
:::
- 「受注は、受注番号で識別し、該当する**見積番号**、受注年月日を登録する」
- 「成約に至ったときに、**見積りと同じ単位**で受注登録を行う」
- 見積と受注は1対1
::: note info
```mermaid
graph LR
 見積---受注
```
:::
# 問1-設問2-(1)
*商品名の例に倣って、太枠内に◯印を付けて、表1を完成させよ*
#### 商品区分
- 「**商品**には、製品と設置サービスがあり、**商品区分**で分類する」
#### 製品区分
- 「**単体製品**には、**本体製品とオプション製品**がある」
- 「**本体製品かオプション製品か**は、**製品区分**で分類する」
#### オプション区分
- 「**オプション製品**には、、、などがあり、**オプション区分**で分類する」
#### 単体セット製品区分
- 「**製品**には、**単体製品とセット製品**がある」
- 「**単体製品かセット製品か**は、**単体セット製品区分**で分類する」
#### シリーズコード
- 「**本体製品**には、製品のシリーズを表す**シリーズコード**を登録する」
#### 製品サイズ
- 「**単体製品**には、**製品サイズ**を登録する」
#### 標準設置時間
- 「**設置サービス**には、**標準設置時間**を登録する」
#### 設置サービス商品コード
- 「**製品**ごとに、どの**設置サービス**を適用するかを決めている」
#### セット製品本体製品商品コード
- 「**セット製品**は、**一つの本体製品**と一つ以上のオプション製品を組み合わせたもので、どのオプション製品で構成されるかについて、セット製品構成に登録する」
::: note info
|エンティティタイプ|商品名|商品区分|製品区分|オプション区分|単体セット区分|シリーズコード|製品サイズ|標準設置時間|設置サービス商品コード|セット単体本体製品商品コード|
|---|---|---|---|---|---|---|---|---|---|---|
|商品|◯|◯|||||||||
|製品|||||◯||||◯||
|設置サービス||||||||◯|||
|本体製品||||||◯|||||
|オプション製品||||◯|||||||
|セット製品|||||||||||
|単体製品|||◯||||◯||||
:::
# 問1-設問2-(2)
*図3中の太枠内にエンティティタイプ名を入れ、欠落しているリレーションシップを補って、図を完成させよ*
- 「商品には、**製品**と**設置サービス**があり、**商品区分**で分類する」
::: note info
```mermaid
graph TB
 商品---サブタイプ
 サブタイプ---設置サービス
 サブタイプ---製品
```
:::
- 「製品には、単体製品とセット製品がある」
::: note info
```mermaid
graph TB
 製品---サブタイプ
 サブタイプ---単体製品
 サブタイプ---セット製品
```
:::
- 「単体製品には、**本体製品**と**オプション製品**がある」
::: note info
```mermaid
graph TB
 単体製品---サブタイプ
 サブタイプ---本体製品
 サブタイプ---オプション製品
```
:::
- 「設置サービスには複数種類があり、**製品**ごとに、どの**設置サービス**を適用するかを決めている」
- 設置サービスと製品は1対多
::: note info
```mermaid
graph LR
 製品サービス-->製品
```
:::
- 「セット製品は**一つの本体製品**と**一つ以上のオプション**製品を組み合わせたもので、どのオプション製品で構成されるかについて、**セット製品構成**に登録する」
- 本体とセット製品は1対多
- セット製品とオプション製品は多対多なので、**連関エンティティ**としてセット製品構成を利用する
::: note info
```mermaid
graph TB
 オプション-->セット製品構成
 本体-->セット製品
 セット製品-->セット製品構成
```
:::
# 問1-設問3-(1)
*新たに追加する関係の主キー及び外部キーを明記した関係スキーマ、属性を追加する関係名及び追加する属性名を答えよ*
- 「出荷指示は**出荷指示番号**で識別し、**出荷指示年月日**を登録する」
::: note info
- 関係スキーマ: 出荷指示(<INS>出荷指示番号</INS>、出荷指示年月日)
:::
- 「同じ設置事業所、同じタイミングで出荷できる場合は、受注明細をまとめて出荷指示を行う」
- 出荷指示と受注明細は1対多なので、**受注明細**の外注キーとして**出荷指示の主キー**を設定する
::: note info
- 追加する関係名: 受注明細
- 追加する属性名: 出荷指示番号
:::
# 問1-設問3-(2)
*受注明細内訳のうち、出荷指示の対象とならない場合が２つある。どのような場合か、それぞれ15字以内で具体的に述べよ*
- 「出荷指示は、コピー機メーカの物流センタから出荷する**製品**を対象とする」
- 「セット製品と、セット製品に必要な**設置サービス**をそれぞれ登録する」
- 設置サービスは製品に含まれない
- 「商品がセット製品の場合、その**セット製品自体**と、**セット製品を構成する製品**を展開した内訳を登録する」
- 「セット製品を構成する製品」の発注指示があれば、「**セット製品自体**」の発注指示は不要
::: note info
- 内訳が設置サービスの場合
- 内訳がセット製品自体の場合
:::
# 問2-設問1-(1)
*表1中のa～cに入れる適切な字句を答えよ*
- 「退職すると従業員テーブルの退職フラグを、あらかじめ設定している'0'(在籍)から'**1**'(退職)に更新する」
::: note info
- a: '1'
:::
- 「毎月25日に、"従業員"テーブル及び"従業員家族"テーブルから、**当月の20日までに**退職した従業員の行を削除する」
- 削除対象は、退職フラグが'1'のうち、退職年月日が当月20日まで(**21日より前**または**当月より前**)のもの
- 今日の日付は**CURRENT_DATE**で取得できる
::: note info
- b: 21
- c: CURRENT_DATE 
:::
# 問2-設問1-(2)
*表2中のd～fに入れる適切な字句を答えよ*
- 図2より、参照対象の先頭は「部署コード」
- SQL2「FROM 従業員、従業員家族」
- 図1より、部署コードは従業員に含まれる属性
::: note info
- d: 従業員.部署コード
:::
- COUNT(従業員家族.従業員コード)のままだと、**従業員家族数**の計算になるので、**DISTINCT**の付加により従業員コードの重複を排除することで、**従業員数**を計算するようにする
::: note info
- e: DISTINCT
:::
- 「**部署コード順**に2007年1月1日以降に生まれた扶養対象者をもつ従業員数と扶養対象者数の一覧表が欲しいとの依頼があった」
- 並べ替えは、ORDER BY
::: note info
- f: ORDER BY 
:::
# 問2-設問2-(1)
*正常終了・エラーのいずれかを◯で囲んで示せ。エラーとなる場合は、その理由を40字以内で具体的に述べよ*
- 「新規従業員登録のために、所属未定の行を従業員テーブルに挿入」なので、(**INSERTだから**)表3のUPDATE/DELETEに該当せず、参照制約機能は発動しない
::: note info
- (a): 正常終了
:::
- 「従業員テーブルから当該従業員を削除」
- 表3の従業員家族(従業員コード)のDELETEに該当し、**CASCADE**なので、テーブル:**従業員家族からも削除**される
- 表3の部署(管理者従業員コード)のDELETEに該当するがテーブル:部署は**NO ACTION**なので、テーブル:**部署に残した**まま
- テーブル:部署に残された**当該従業員**の管理者従業員コードを参照できなくなる
::: note info
- (b): エラー
- 理由: 退職者が従業員テーブルから削除されると、部署テーブルの参照制約違反になるから
:::
# 問2-設問2-(2)
*新たに定義するトリガについて、対象となるテーブルのテーブル名、実行タイミング、処理内容をそれぞれ答えよ*
- 「参照制約を利用することによって**不具合が発生する**」とは、従業員テーブルから削除すると、DELETE:CASCADEなので、**自動的に従業員家族も削除される**ことを指す
- この不具合を活用し、**削除を契機**に「**別テーブルに保存する**」トリガを作る
- なお、自動的に従業員家族も削除されるので、「従業員テーブルのトリガ」のうち、「**従業員家族テーブルのその従業員の家族の行について、別テーブルに挿入し、その後削除する**」が不要になる
::: note info
- テーブル名: 従業員家族
- 実行タイミング: 削除後
- 処理内容: 削除された行を別テーブルに追加
:::
# 問2-設問3-(1)
*不具合が発生する契機を図3中の丸数字で答えよ*
- ①のタイミングで
```mermaid
graph TB
 部署DELETE-->|CASCADE猶予|従業員DELETE
 従業員DELETE-->|CASCADE即時|従業員家族DELETE
 従業員DELETE-->|NO ACTION即時|部署そのまま
```
- 削除した部署に所属する**従業員とその家族がDELETEされる**問題
::: note info
- 契機: ①
- 不具合の内容: 削除した部署に所属する従業員が従業員テーブルからDELETEされる
:::
# 問2-設問3-(2)
*どのように変更すればよいか、①、③、⑤の変更後の順序を答えよ*
- ①で削除される部署コードが、従業員テーブルで使われていなければよいので、先に従業員テーブルの部署コードを更新(⑤)する
- ⑤で従業員テーブルの部署コードが、部署テーブルに存在しないと参照制約違反になるので、先に部署テーブルへ新規の部署を追加(③)しておく
::: note info
- ③→⑤→①
:::
# 問2-設問3-(3)
## (a)
*RDBMSは猶予モードの制約の検査のために、トランザクション終了時にどのような検査を行っているか、検査内容を55字以内で具体的に述べよ*
- 部署(部署コード)のUPDATE時はNO ACTION
- 部署と従業員に同じ部署コードが使用されていた場合でも、**部署の更新は従業員に反映されない**
- 従業員(部署コード)から参照先:部署(部署コード)への参照制約を検査するため、**更新前の部署コード**が使われていないことを確認
::: note info
- 更新前の部署コードを、従業員テーブルから参照していないことを検査する
:::
## (b)
*(a)の検査を行う際、想定よりも処理時間が長くなる恐れがある。その理由を50字以内で具体的に述べよ*
- 「**索引は主キーだけ**に定義されている」
- 従業員(部署コード)は**外部キー**なので索引は無く、索引検索より遅い**表探索**になる
::: note info
- 従業員テーブルの部署コードに索引が無いため、更新前の部署コードと同じ行を表検索するから
:::
# 問3-設問1-(1)
*UNIQUE制約を定義する列の構成をそれぞれ答えよ*
- 「**内線番号**　各店舗に設置されている内線電話を**施設内で識別**する番号」
- 精算テーブルの非キー属性のなかにはテーブルを識別できるものはない
::: note info
- 店舗: {施設ID、内線番号}
- 精算: なし
:::
# 問3-設問1-(2)
*図2中のa～dに入れる適切な述語を一つずつ答えよ*
- 「子供(12歳未満):'1'」
- 「大人(12歳以上60歳未満):'2'」
- 「シニア(60歳以上):'3'」
::: note info
- a: 年齢<12
- b: 年齢区分='2'
- c: 年齢 BETWEEN 12 AND 59
- d: 年齢>=60
:::
# 問3-設問1-(3)
### ①
*索引1は、ユニーク索引又は非ユニーク索引のどちらに該当するか答えよ*
|列名|意味・制約|
|---|---|
|店舗ID|施設内の各店舗を識別する文字列|
|鍵番号|施設内のロッカーを識別する番号。客の精算後、**鍵は再利用**される|
|未精算フラグ|利用額が未精算の場合:'Y'、精算済みの場合:'N'|
- 鍵は再利用されるので、同じ店舗ID、同じ鍵番号で精算済みの記録は複数ありえる
::: note info
- ①: 非ユニーク
:::
### ②
*索引2は、高クラスタな索引である。その理由を35字以内で述べよ*
- 「高クラスタな索引:**キー値の順番**と、キーが指す行の**物理的な並び順**が一致しているか、完全に一致していなくても、隣接するキーが指す行が同じページに格納されている割合が高い」
- **索引2のキー**の構成は**券の主キー**と同じで{**利用年月日**、**施設ID**、**券番号**}
- 精算明細の主キーは{**利用年月日**、利用時刻、**施設ID**、**券番号**、店舗ID、商品ID}
- 「受付では、客が1枚ずつ入館券を購入し、、、」
- 「未精算の利用額は、**退館時に**複数台のいずれかの精算機で精算する」
- 同じ券(主キー:**利用年月日**、**施設ID**、**券番号**)の精算明細が、精算時にまとめて追加される
::: note info
- ②: 同じ入館券に対する精算明細がまとめて追加されるから
:::
# 問3-設問1-(4)
*表5中のア～キに入れる適切な字句を、表5中の下線部分の用語を用いて答えよ*

::: note warn
下線部分の用語
- 見積行数(行)
- 平均行長(バイト/行)
- 最大行長(バイト/行)
- ページ長(バイト/ページ)
- 有効ページ長(バイト/ページ)
- ページ当たりの行数(行/ページ)
- 必要ページ数(ページ)
:::

- 1行分のデータが必ず1ページ内に収まるようなページ長を選択する
::: note info
- ア: 最大行長
:::
- ページの空き領域を平均行長よりも大きくする
::: note info
- イ: 有効ページ長
:::
```math
ウ=\frac{有効ページ長}{ページ当たりの平均行数}=\frac{[バイト/ページ]}{[行/ページ]}=[バイト/行]=平均行長
```
::: note info
- ウ: 平均行長
:::
- エ÷オの単位が[ページ]になる組み合わせ
```math
\frac{見積行数}{ページ当たりの平均行数}=\frac{[行]}{[行/ページ]}=[ページ]
```
::: note info
- エ: 見積行数
- オ: ページ当たりの平均行数
:::
- ストレージ所要量(バイト)は、必要ページ数×ページ長
::: note info
- カ: 必要ページ数
- キ: ページ長
:::
# 問3-設問1-(5)
*表6中のク～コに入れる適切な字句を、表5、6中の下線部分の用語を用いて答えよ*

::: note warn
下線部分の用語
- 見積行数(行)
- 平均行長(バイト/行)
- 最大行長(バイト/行)
- ページ長(バイト/ページ)
- 有効ページ長(バイト/ページ)
- ページ当たりの行数(行/ページ)
- 必要ページ数(ページ)
- 探索行数
- 結果行数
:::
- 表検索の場合は全ページを読み込んで検索する
::: note info
- ク: 必要ページ数
:::
- 低クラスタだと、探索の度にページを読み込む
::: note info
- ケ: 探索行数
:::
- 高クラスタだと、ページの読み込みが最小限で済む
- 単位に着目すると
```math
コ=\frac{探索行数}{最小読込みページ数}=\frac{[行]}{[ページ]}=[行/ページ]=ページ当たりの行数
```
::: note info
- コ: ページ当たりの行数
:::
# 問3-設問2-(1)
*作業を変えた後の、直前の作業及び直後の作業IDをそれぞれ答えよ*
- W2「UNIQUE制約、検査制約及び参照制約」を決めるためにW1「**テーブル名、列名、列データ型、主キー制約及びNOT NULL制約**を決めて」おく必要がある
- W3「ユーザごと又はロールごとにテーブルのアクセス権限」を決めるためにW1「**テーブル名**、列名、列データ型、主キー制約及びNOT NULL制約を決めて」おく必要がある
- W4「追加索引を設計する」ために、W1「**テーブル名**、列名、列データ型、主キー制約及びNOT NULL制約を決めて」おく必要がある
- W5「テーブル及び索引のストレージ容量を見積」るために、 、W4「追加索引を設計する」必要がある
- W6「DMLの結果行数及び読み込みページ数を机上で予測する」ために、W4「DMLのアクセスパスを想定」する必要がある
- W7「表領域をストレージに割り当て」るために、W5「表領域設計」が必要
- W7「GRANT文を実行する」ために、W3「GRANT文を設計する」必要がある
- W8「DML が想定通りか確認する」ために、W4「DMLのアクセスパスを想定」が必要
- W8「テーブル及び索引に関する統計情報を取得」するためにW7「DDLなど実行」し、テーブルや索引を実体に反映させる必要がある
- W9「性能測定用データを生成するための設計書を作成」するために、W1「テーブル設計」が必要
- W10「テーブルに性能測定用データをロードする」ために、W9「性能測定用データ設計・データ生成」が必要
- W11「DMLの性能を測定」するためにW6「DML性能予測」とW10「ロード実行」が必要
- 以上を図にすると
```mermaid
graph TB
 W1-->W2
 W1-->W3
 W1-->W4
 W4-->W5
 W4-->W6
 W5-->W7
 W3-->W7
 W4-->W8
 W7-->W8
 W1-->W9
 W9-->W10
 W6-->W11
 W10-->W11
```
::: note info
- W3直前:W1
- W3直後:W7
- W9直前:W1
- W9直後:W10
:::
# 問3-設問2-(2)
*指摘2について、アクセスパスが索引検索でなく表検索に決められるDMLを、表3から全て答え、表探索に決められる理由を、W8の作業順の観点から40字以内で具体的に述べよ*