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

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

公式サイトの問題冊子はこちら。
https://www.ipa.go.jp/shiken/mondai-kaiotu/gmcbt8000000d05l-att/2020r02o_db_pm1_qs.pdf
# 問1-設問1-(1)
*図1中のア、イに入れる適切なエンティティタイプ名を答えよ。また、必要なリレーションシップを全て記入し、概念データモデルを完成させよ*
- 「店舗は、必要な自社製品とその発注数量を設定して**発注**する」
- 「一つの発注の中で同一の自社商品を複数回登録することができる」
- 「発注は、発注番号で識別し、**発注明細**は発注番号と発注明細番号で識別する」
- 発注と自社製品が多対多なので、それらを発注明細と1対多で接続する
- 「生産明細は、生産番号と商品コードで識別」
- 「生産の対象とした発注明細に対して、**生産番号**を記録する」
- 「配送明細は、配送番号と商品コードで識別」 
- 「配送の対象とした発注明細に対して、**配送番号**を記録する」
::: note info
- ア: 発注
- イ: 発注明細
```mermaid
graph TB
 店舗-->発注
 発注-->発注明細
 自社製品-->発注明細
 自社製品-->生産明細
 生産明細-->発注明細
 自社製品-->配送明細 
 配送明細-->発注明細
```
:::
# 問1-設問1-(2)
*図2中のa～hに一つ又は複数の適切な属性名を入れ、図を完成させよ*
## a
- 「店舗は、一つの配送ルートに属し、その**ルート番号**をもつ。また、配送ルート上何番目に配送されるかを表す**配送順序**をもつ」
::: note info
- a: ルート番号(FK)、配送順序
:::
## b
- 「自社製品ごとに生産ロットサイズを決めている」
::: note info
- b: 生産ロットサイズ
:::
## c
- 「配送ルートは、ルート番号で識別し、ルート名称と一つの配送元の**拠点コード**をもつ」
::: note info
- c: 生産工場拠点コード(FK)
:::
## d
- dはア:発注
- 「発注は、**発注番号**で識別」
- 「A社本部は、店舗からの発注について、**店舗の拠点コード**、**発注登録日時**を確認し、**配送予定日時**を記録する」
::: note info
- d: <INS>発注番号</INS>、店舗拠点コード(FK)、発注登録日時、配送予定日時
:::
## e
- eはイ:発注明細
- 「店舗は、必要な自社製品とその**発注数量**を設定して発注する」 
- 「発注明細は**発注番号と発注明細番号**で識別する」
- 発注明細は、発注を自社製品ごとにわけたものなので、商品コードを外部キーとしてもつ
- 「A社本部は、店舗からの発注について、**店舗の拠点コード**、**発注登録日時**を確認し、**配送予定日時**を記録する」
- 「生産の対象とした発注明細に対して、**生産番号**を記録する」 
- 「配送の対象とした発注明細に対して、**配送番号**を記録する」
::: note info
- e: <INS>発注番号</INS>、<INS>発注明細番号</INS>、発注数量、商品コード(FK)、生産番号(FK)、配送番号(FK)、
:::
## f
- 「生産は、生産番号で識別し、生産工場の**拠点コード**、生産完了予定日時を記録する」
::: note info
- f: 生産工場拠点コード(FK)
:::
## g
- 「生産明細は、生産番号と**商品コード**で識別し、生産数量を記録する」
::: note info
- g: <INS>商品コード</INS>
:::
## h
- 「配送は、配送番号で識別し、配送完了予定日時と配送先の**拠点コード**を記録する」
::: note info
- h: 店舗拠点コード(FK)
:::
# 問1-設問2-(1)
*図3中のウ～カに入れる適切なエンティティタイプ名を答えよ。また、必要なリレーションシップを全て記入し、概念データモデルを完成させよ*
- 「個々の委託商品を生産する委託工場は、一つに決まっている」
- 委託工場と委託商品は1対多で接続
- 「自社工場と委託工場を併せて工場と呼ぶ」
::: note info
- ウ: 委託工場
- エ: 自社工場
- 自社工場と委託工場は工場のサブタイプ
:::
- 「**既存の配送ルート**を使って自社商品と委託商品を併せて店舗へ配送する」
- 既存の配送ルート
```mermaid
graph LR
 生産工場-->配送ルート
 配送ルート-->店舗
```
- 「委託工場の追加に伴って、生産工場を**自社工場**と呼ぶ」
- 「これまで**自社工場**内で仕分けと配送を行っていた役割に、工場の拠点コードとは別に**物流センタ**としての拠点コードを付与する」
- つまり、既存の配送ルートにおいて、生産工場は**物流センタ**に差し替わる
- 「各自社工場と自社工場内の物流センタ、各委託工場と各物流センタの組を**納入ルート**と呼ぶ」
- 工場、物流センタと納入ルートは1対多
- 「**配送ルート**と**納入ルート**を併せて**ルート**と呼ぶ」
::: note info
- オ: 納入ルート
- カ: 配送ルート
- 配送ルートと納入ルートはルートのサブタイプ
```mermaid
graph LR
 物流センタ-->配送ルート
 配送ルート-->店舗
```
:::
- 「**納入**は、納入番号で識別し、**納入するルート番号**と納入予定日時を記録する」
::: note info
```mermaid
graph LR
 納入ルート-->納入
```
:::
- 「自社商品と委託商品を併せて**自社仕様商品**と呼ぶ」
- 「**納入明細**は、納入番号と**商品コード**で識別」
- 「納入の対象となる発注明細に対して、納入番号を記録する」
::: note info
```mermaid
graph LR
 自社仕様商品-->納入明細
 納入明細-->発注明細
 自社仕様商品-->生産明細
 自社仕様商品-->配送明細
 自社仕様商品-->発注明細
```
:::
- 「自社商品と委託商品を併せて**自社仕様商品**と呼ぶ」
-　「委託工場は、生産の指示に基づいて、生産を行い、**自社工場と同様**の記録を行う」
- 発注、生産、配送については、自社商品を自社仕様商品に、自社工場を工場に差し替える以外、委託商品追加前のリレーションシップをそのまま引き継ぐ
::: note info
```mermaid
graph TB
 工場-->生産
 生産-->生産明細
 自社仕様商品-->生産明細
 生産明細-->発注明細
 自社仕様商品-->配送明細
 配送明細-->発注明細
 自社仕様商品-->発注明細
 店舗-->発注
 発注-->発注明細
```
:::
# 問1-設問2-(2)
*図4中のi～kに一つ又は複数の適切な属性名を入れ、図を完成させよ*
- 委託工場は拠点コードを主キーとする工場のサブタイプ
- 「委託工場は委託開始年月日をもつ」
::: note info
- i: <INS>拠点コード</INS>、委託開始年月日
:::
- 納入ルートはルート番号を主キーとするルートのサブタイプ
- 「これまで自社工場内で仕分けと配送を行っていた役割に、**工場の拠点コード**とは別に**物流センターとしての拠点コード**を付与する」
::: note info
- j: 工場拠点コード(FK)、物流拠点コード(FK)
:::
- 配送ルートはルート番号を主キーとするルートのサブタイプ
- ルート(<INS>ルート番号</INS>、ルート名称)
- 追加前の配送ルート(<INS>ルート番号</INS>、ルート名称、拠点コード(FK))
- 「配送ルートの配送元を自社工場から物流センタに変更し、物流センタに対する配送の指示及び店舗への配送は、現状業務と同様に行う」
::: note info
- k: 物流拠点コード(FK)
:::
# 問1-設問2-(3)
*カーディナリティの値(数値)と、どのような場合に発生するかを25字以内で具体的に答えよ*
- 「A社には**3か所**の生産工場がある」
- 「これまで自社工場内で仕分けと配送を行っていた役割に、工場の拠点コードとは別に**物流センタ**としての拠点コードを付与する」
- 「各自社工場と自社工場内の物流センタ、各委託工場と各物流センタの組を納入ルートと呼ぶ」
- 各自社工場は**各自社工場内の**物流センタだけを利用
- 各委託工場は**自社工場3か所にある**物流センタを利用
::: note info
- 1個: 自社工場から物流センタへの納入ルート
- 3個: 委託工場から物流センタへの納入ルート
:::
# 問2-設問1
*図4中のa～gに入れる適切な字句を答えよ*
- 「空席情報は、○:空席あり、×:売切れ」
- CASE WHEN THEN ELSE構文を使って、空席数がNULLの場合に×を、そうでない場合に○を出力する
::: note info
- a: CASE WHEN 
- b: IS NULL 
:::
- A1は全ての{公演番号、公演日、開演時間、席種}が網羅されるが、A2は一部の{公演番号、公演日、開演時間、席種}が存在しない場合もあるため、LEFT JOINで結合する
::: note info
- c: LEFT JOIN 
:::
- 「空席管理システムから、公演日が2020年4月かつ都道府県が東京のチケット情報一覧の出力の例」
- BETWEEN 開始日 AND 最終日
::: note infod
- d: BETWEEN 
- e: AND 
:::
- 「チケット情報は、公演名・公演日・開演時刻の昇順、料金の降順に出力される」
::: note infod
- f: ORDER BY 
- g: DESC 
:::
# 問2-設問2-(1)
*表2中のあ～おに入れる適切な字句を答えよ*
- 「ロックは**行単位**で掛ける。共有ロックを掛けている間は、他のトランザクションからの対象行の参照は可能でり、**変更は共有ロックの解放待ち**となる」
- 「全ての購入希望チケットの購入フラグをオンにする」は対象行の変更なので、同じ{公演番号、公演日、開演時刻、座席番号}の行(**座席**)に対して専有ロックがかかる
- ②のあとに、他のトランザクションによって③の対象行に専有ロックが掛けられ、トランザクションが正常終了するとロックは解除されるが、**空席フラグがオフ**になり空席管理システムの処理において、「空席フラグがオンか確認する」でエラーになる
::: note info
- あ: 同じ座席
- い: 購入不可である
:::
- 「②全ての購入希望チケットの**購入番号がNULL**か確認する」によって空席確認する
- 「⑦全ての購入希望チケットの購入情報を登録し、**購入番号を設定**する」によって購入が確定する
::: note info
- う: 購入番号
- え: NULL
:::
- 掛けられたロックはコミットによって解除される
::: note info
- お: コミット
:::
# 問2-設問2-(2)
*①か②で答えよ。また、その理由を30字以内で述べよ*
- トランザクションxが行う「仮予約フラグがオフか確認する」と「仮予約フラグをオンにする」の間のタイミングで、同じ座席に対してトランザクションyの「仮予約フラグがオフか確認する」が行われようとするケースを考える
- **①READ COMMITED**の場合は、トランザクションxが仮予約フラグをオンにして決済処理に進んだ後にトランザクションyも仮予約フラグをオンにして決済処理に進むために、同じ座席に対して**決済処理が重複**してしまう可能性がある
- **②REPEATABLE READ**の場合、トランザクションx、yともに「仮予約フラグをオンにする」を実施するために必要な専有ロックを取得できずに**デッドロックする**。この場合、トランザクションxかyの**どちらかの処理をキャンセル**して共有ロックを解放すれば他方のみ「仮予約フラグをオンにする」を実施後、決済処理に移行できる。双方をキャンセルして、やり直す
::: note info
- ②: 同時に発生した他方の処理に同座席の仮予約をさせないため
:::
# 問2-設問3-(1)
*図6中のア～ウに入れる適切な字句を、本文中の字句を用いて答えよ*
- 「**空席管理システム**を介して、**空席情報を表示**するサービスも提供」
- 「**座種在庫**には、座種ごとの空席数をもつ」
- 「**座席状況**には、公演開催明細ごとの全ての座席の状況をもつ」
- 他に、**空席情報**を含むテーブルは無い
::: note info
ア: 座種在庫
イ: 座席状況
:::
- 「レプリケーション機能: **1か所のデータを複数か所に複製**する機能、複数か所のデータを1か所に集約する機能、及び両者を組み合わせて双方向に反映する機能がある」
- 「**サービスの提供先ごとに**レプリカデータを用意する」
::: note info
ウ: 1か所のデータを複数か所に複製
:::
# 問2-設問3-(2)
*表3中のエに入れる文章を、1.に倣って30字以内で述べよ*
- 「1.購入された座席が空席として表示される」
- 「座席の購入が確定したら空席数を減らし、購入された座席が**キャンセルされたら空席数を戻す**」
::: note info
キャンセルされた座席が空席として表示されない
:::
# 問2-設問3-(3)
*イベント型レプリケーション機能の対象とするテーブルとその例を答えて、表4を完成させよ*
- **座種在庫**(<INS>公演番号</INS>、<INS>公演日</INS>、<INS>開演時刻</INS>、<INS>座種</INS>、**空席数**)
- **座席状況**(<INS>公演番号</INS>、<INS>公演日</INS>、<INS>開演時刻</INS>、<INS>座席番号</INS>、**空席フラグ**、仮予約フラグ)
::: note info
|レプリケーション元テーブル |レプリケーション対象列  |
|---|---|
|座種在庫 |空席数  |
|座席状況 |空席フラグ |
:::
# 問3-設問1-(1)
*表2、3中のa、bに入れる適切な字句を答えよ*
- 地域(**<INS>地域コード</INS>**、店舗名、、、)
- 会員地域コードは、地域テーブルの地域コードと紐付く
::: note info
- a: 地域(地域コード)
:::
- 社員(**<INS>社員ID</INS>**、社員名、店舗コード、、、)
- 担当社員IDは、社員テーブルの社員IDと紐付く
::: note info
- b: 社員(社員ID)
:::
# 問3-設問1-(2)
*会員区分を追加することなく既存の会員番号をそのまま移行すれば、どのような場合にどのような不都合が起きると考えられるか、それぞれ25字以内で述べよ*
- 「個人会員番号及び法人会員番号の付与は、**互いに無関係**でそれぞれ独自に行っている」
- 個人会員番号と法人会員番号は**重複**してしまう場合がある
::: note info
- 場合: 個人会員番号と法人会員番号が同じ場合
- 不都合: 会員番号の主キー違反でエラーが発生
:::
# 問3-設問1-(3)
*表4に示したNOT NULL欄中のc～g、及び制約欄中のh～mに入れる適切な字句を答えよ*
- 個人会員(<INS>個人会員コード</INS>、氏名、会員地域コード(FK)、性別、年代(FK)、、、)
- 法人会員(<INS>法人会員コード</INS>、社名、会員地域コード(FK)、担当社員ID(FK)、、、)
- 会員区分は主キーなので、NOT NULL
- 「会員には個人会員と法人会員があり、**会員地域コード**が設定される。法人会員には、担当する社員が**登録後に**1名決められる」
- 性別、年代は法人会員には含まれない
::: note info
- c: Y
- d: Y
- e: N
- f: N
- g: N
:::
- 年代に対する参照制約は表2のものを引き継ぐ
::: note info
- h: RESTRICT
:::
- 担当社員IDに対する参照制約は表3のものを引き継ぐ
::: note info
- i: SET NULL 
:::
- 氏名、社名は、会員名に移行する
- 性別に対する検査制約は表2のものを引き継ぐ
- 個人会員には、担当社員IDは含まれない
- 個人会員「年代には、次のいずれかが設定される」
- 法人会員には、性別、年代は含まれない
::: note info
- j: 性別 IN ('M','F')
- k: 担当社員ID IS NULL 
- l: 年代 IS NOT NULL
- m: 年代 IS NULL 
:::
# 問3-設問2-(1)
*図3中のあ～くに入れる適切な字句を、回答群の中から選び、記号で答えよ*
- 「2020年3月の店舗コード別商品コード別売上高を調べる」SQLなので、(店舗コード、商品コード、販売額)を含む図2の売上テーブルを参照すれば事足りるはず
- SELECT句を見ると、Uは(商品コード、販売額、クーポン額)を含むので、売上テーブルが該当
- Mは店舗コードを含むので、店舗テーブルが該当
- **店舗 LEFT OUTER JOIN 売上**として、売上ゼロの店舗コードが除外されないようにしようとしていることが読み取れる
- **年は2020**、**月は3**
- 「**店舗コードはM1、M2及びM3**に、**商品コードはP1及びP2**に限定する」
::: note info
- あ: ②
- い: ①
- う: ⑥
- え: ④
:::
- ON句に指定した条件はFROMのテーブルには適用されないので、FROMのテーブルに指定したい条件はWHERE句に
::: note info
- お: ⑧
- か: ⑨
- き: ⑩
- く: ⑫
:::
# 問3-設問2-(2)
*図3中の選択リスト中の販売額又はクーポン額がNULLになるのはどのような場合か、本文中の用語を用いて、それぞれ25字以内で述べよ*
- 売上テーブルに記録されていない店舗が存在するとき
- 「販売額は商品の店頭価格。クーポン額は、会員が広告チラシ又はスマートフォンでクーポンを提示したときに適用した金額で、**適用しなかった場合、NULL**が設定される。」
- 売上のない店舗はJOINする相手が存在しないのでNULLが設定される
::: note info
- 販売額: 売上記録が無い店舗の場合
- クーポン額: クーポンを適用しなかった場合
:::
# 問3-設問3-(1)
*表6中のイ～ハに入れる適切な字句を答えよ*
## イ
- 「2020年3月と2019年3月」で2パターン
- 「店舗地域コード別」で10パターン
- 「大分類コード別」で10パターン
``` math
2×10×10=200
```
::: note info
- イ: 200
:::
## ロ
- 「2019年の月別」で12パターン
- 「店舗コード別」で300パターン
- 「大分類コード別」で10パターン
``` math
12×300×10=36000
```
::: note info
- ロ: 36000
:::
## ハ
- 「店舗コード別」で300パターン
- 「性別」で2パターン
- 「年代別」で7パターン
``` math
300×2×7=4200
```
::: note info
- ハ: 4200
:::
# 問3-設問3-(2)
*Aに入れる複数の列名を答えよ*
- 表6の各分析名に対して、サマリーテーブルに必要な属性
B2: 年、月、会員区分、会員地域コード、売上額
B3: 年、月、店舗地域コード、**大分類コード**、売上額
B4: 年、月、店舗コード、**大分類コード**、売上額
B5: 年、月、店舗コード、**性別、年代**、売上額
B6: 年、月、**小分類コード**、売上額
- 「S(年、月、店舗コード、会員区分、会員地域コード、A、売上額)」
- サマリーテーブルに必要な属性のうち、Sに足りないのは、**店舗地域コード、大分類コード、性別、年代、小分類コード**
- **店舗地域コード**は、店舗コードによって決定する
- **大分類コード**は、小分類コードによって決定する
::: note info
- 小分類コード、性別、年代
:::