データベーススペシャリスト　令和3年 午後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-(1)](#問2-設問1-1)
  - [設問1-(2)](#問2-設問1-2)
  - [設問1-(3)](#問2-設問1-3)
  - [設問1-(4)](#問2-設問1-4)
  - [設問1-(5)](#問2-設問1-5)
  - [設問2-(1)](#問2-設問2-1)
  - [設問2-(2)](#問2-設問2-2)
  - [設問2-(3)](#問2-設問2-3)
  - [設問2-(4)](#問2-設問2-4)
  - [設問3-(1)](#問2-設問3-1)
  - [設問3-(2)](#問2-設問3-2)
- 問3
  - [設問1-(1)](#問3-設問1-1)
  - [設問1-(2)](#問3-設問1-2)
  - [設問1-(3)](#問3-設問1-3)
  - [設問1-(4)](#問3-設問1-4)
  - [設問2-(1)](#問3-設問2-1)
  - [設問2-(2)](#問3-設問2-2)
  - [設問2-(3)](#問3-設問2-3)


公式サイトの問題冊子はこちら。
https://www.ipa.go.jp/shiken/mondai-kaiotu/gmcbt8000000apad-att/2021r03a_db_pm1_qs.pdf
# 問1-設問1-(1)
*図2中のa～kに入れる適切な属性名を答えよ*
- 「ポイント付与率は、通常は全加盟企業共通で決められている基準ポイント付与率を適用するが、後述の**クーポンの利用によって変わる**ことがある」
- 「設定できるクーポンには、適用対象となる**店舗を限定**したクーポン、～」
- 支払に店舗を紐付けるため、店舗の主キーを外部キーに持たせる
::: note info
- a:加盟企業コード(FK)
- b:店舗コード(FK)
:::
- 「支払方法ごとの付与ポイントは、各支払方法での**支払金額**に、ポイント付与率を乗じて計算する」
::: note info
- c:支払金額
:::
- 「商品の購入金額(**購入数**×商品単価)にポイント付与率を乗じて計算する」
::: note info
- d:購入数
:::
- 「ポイント設定は、ポイント設定コードで識別し、**ポイント付与率**、適用期間をもつ」
- 「ポイントを付与する**支払方法にポイント設定を対応付ける**」
::: note info
- e:ポイント設定コード(FK)
- f:ポイント付与率
:::
- 「クーポンには、**配布上限数**と配布期間を設定している」
::: note info
- g:配布上限数
:::
- 「クーポン配布リストに登録されている会員が、全加盟企業のいずれかの店舗を利用した場合に、クーポンを発行する」
- クーポン配布は、クーポン設定と会員の多対多結合を表現したもの
::: note info
- h:<INS>クーポンコード</INS>
- i:<INS>会員コード</INS>
:::
- 「会員は、クーポンコードが異なる**複数のクーポンを1回の支払で**利用できる。クーポンの効果を測るために、**クーポン**が**どの支払で**利用されたか分かるように記録する」
::: note info
- j:<INS>クーポンコード</INS>
- k:<INS>レシートコード</INS>
:::
# 問1-設問1-(2)
*必要なリレーションシップを全て記入し、図を完成させよ*
- 「ポイント付与率は、通常は全加盟企業共通で決められている基準ポイント付与率を適用するが、後述の**クーポンの利用によって変わる**ことがある」
- 「設定できるクーポンには、適用対象となる**店舗を限定**したクーポン、～」
- 支払と店舗が多対1
::: note info
```mermaid
graph TB
  店舗-->支払
```
:::
- クーポン配布は、クーポン設定と会員の多対多結合
::: note info
```mermaid
graph TB
  クーポン設定-->クーポン配布
  会員-->クーポン配布
```
:::
- クーポン料金は、クーポン設定と支払の多対多結合
::: note info
```mermaid
graph TB
  クーポン設定-->クーポン利用
  支払-->クーポン利用
```
:::
- クーポン設定対象店舗は、クーポン設定と店舗の多対多結合
::: note info
```mermaid
graph TB
  クーポン設定-->クーポン設定対象店舗
  店舗-->クーポン設定対象店舗
```
:::
- クーポン設定対象商品は、クーポン設定と加盟企業商品の多対多結合
::: note info
```mermaid
graph TB
  クーポン設定-->クーポン設定対象商品
  加盟企業商品-->クーポン設定対象商品
```
:::
- 「ポイントを付与する**支払方法にポイント設定を対応付ける**」
::: note info
```mermaid
graph TB
  ポイント設定-->支払方法
```
:::
# 問1-設問2-(1)
*関係“加盟企業商品”の候補キーを全て答えよ*
- 「加盟企業商品は、**加盟企業コード**と**加盟企業商品コード**で識別する」
- 「横断分析用商品情報には、横断分析用商品コードと横断分析用商品名を設定し、**横断分析用商品コード**で識別する」
- その他に、再利用されない一意の属性は無い
::: note info
- {加盟企業コード、加盟企業商品コード}
- {加盟企業コード、横断分析用商品コード}
:::
*部分関数従属性、推移的関数従属性の有無を示せ。ありの場合は、関数従属性の具体例を示せ*
- その他に、再利用されない一意の属性は無い
::: note info
| 関数従属性 | 表記法 |
|:-:|:-:|
|部分関数従属性|加盟企業商品コード→加盟企業商品名、加盟企業コード→加盟企業名|
|推移的関数従属性|{加盟企業コード、加盟企業商品コード}→横断分析用商品コード→横断分析用商品名、→加盟企業商品コード→加盟企業商品名|
:::
# 問1-設問2-(2)
*関係“加盟企業商品”の候補キーのうち、主キーとして採用できないものはどれか答えよ。また、その理由を45字以内で具体的に述べよ*
- 「横断分析用商品コードの設定には、加盟企業商品の登録から数日を要する場合がある」
::: note info
- {加盟企業コード、横断分析用商品コード}
- 理由:参照するタイミングによって、横断分析用商品コードが設定されていない場合があるから
:::
# 問1-設問2-(3)
*関係“加盟企業商品”は第1正規形、第2正規形、第3正規形のうち、どこまで正規化されているか答えよ。第3正規形でない場合は、第3正規形に分解し、関係スキーマを示せ*
- 加盟企業コード→加盟企業名は**部分関数従属**なので、第1正規形
- {加盟企業コード、横断分析用商品コード}→横断分析用商品コード→横断分析用商品名は**推移的関数従属**
::: note info
- 第1正規形
- 加盟企業(<INS>加盟企業コード</INS>、加盟企業名、契約開始日、契約終了日)
- 加盟企業商品(<INS>加盟企業商品コード</INS>、JANコード、加盟企業商品名、横断分析用商品コード)
- 横断分析用商品(<INS>横断分析用商品コード</INS>、横断分析用商品名)
:::
# 問1-設問3-(1)
*日次バッチの集計処理では、付与ポイントの記録がポイント残高に加算されない場合がある。それはどのような場合か。本文中の用語を用いて30字以内で述べよ*
- 「会員が**ポイントカードを忘れた場合**、会員が申告すると店員は支払時のレシートに押印する。会員がこのレシートを**1か月以内に**この店舗に行き、ポイントカードを提示すると、**その支払で付与するポイントを記録**する」
::: note info
- ポイントカードを忘れて、後日、ポイントを記録する場合
:::
# 問1-設問3-(2)
*その属性の役割を30字以内で述べよ*
- 後日付与されたポイントも日次バッチで抽出できるようにすればいい
::: note info
- ポイント付与を記録した日時で抽出できるようにする
:::
# 問2-設問1-(1)
*本文中のa～hに入れる適切な数値を答えよ*
- 「オーソリ履歴テーブルのアクセス経路は**表探索だった**ので」オーソリ履歴の全ぺーシを読み込む
::: note info
- a:24億
:::
```math
\frac{24億×20×200B}{100MB/秒}=96000
```
::: note info
- b:96000
:::
```math
1000万枚×80回
```
::: note info
- c:8億
:::
```math
8億×1行
```
::: note info
- d:8億
:::
- テーブルのバッファピット率が0%なので、行ごとにページ読み込みが発生
::: note info
- e:8億
:::
```math
8億×1ミリ秒
```
::: note info
- f:800000
:::
```math
8億×0.1ミリ秒
```
::: note info
- g:8000
:::
```math
96000+8000+800000
```
::: note info
- h:904000
:::
# 問2-設問1-(2)
*アクセス経路に着目し、それぞれ理由を25字以内で述べよ*
- 「**表探索では**索引を使わずに先頭ページから**順に全行を探索**する」
- 「オーソリ履歴テーブルのアクセス経路は**表探索**だったので、aページを非同期に読み込む」
- 「外側の表の結果行ごとに加盟店テーブルの主キー索引を索引検索し、加盟店テーブルを1行、**ランダムに**合計d回読み込む」
::: note info
- 加盟店:ランダム読み込みの処理時間を短縮できるから
- オーソリ履歴:1行ずつ順に読み込むため、効果を見込めないから
:::
# 問2-設問1-(3)
*案2を適用した場合、オーソリ処理の処理時間が長くなると考えられる。その理由を25字以内で述べよ*
- オーソリ履歴の行を変える度に索引も更新する必要がある
::: note info
- 行追加の際に利用日の索引も更新する必要があるから
:::
# 問2-設問2-(1)
*その理由を25字以内で具体的に述べよ*
- 「オーソリ処理は最大100多重で処理される」
- 「テーブルを検索するSQL文のWHERE句の述語に区分キー列を指定すると、**区分キー列で特定した区分**だけを探索する」
- 同じタイミングで同じカード番号のオーソリ処理が発生しにくく、常に多重処理される
- 同じタイミングで同じ利用日のオーソリ処理が発生しやすく、その場合は多重処理できない
::: note info
- 多重処理の際、行挿入先の区分が重なりにくいから
:::
# 問2-設問2-(2)
*表2中のイ、ロに入れる適切な数値を答えよ*
- 「請求書作成に必要な**1か月分**の利用明細の記録をオーソリ履歴テーブルから抽出しファイルに出力する」
- 「オーソリ履歴テーブルには**直近5年分**を保持する」
- ジョブあたり
```math
\frac{抽出期間:1か月}{区分単位:1区分/1か月}=1区分
```
```math
\frac{24億ページ}{5年×12か月}=4000万ページ/月
```
::: note info
- イ: 1
- ロ: 4000万
:::
# 問2-設問2-(3)
*その理由を30字以内で具体的に述べよ*
- ハッシュ区分だと**昇順に区分されない**
::: note info
- ハッシュ区分だと昇順に区分されず、探索先を絞れないから
:::
# 問2-設問2-(4)
*その理由を20字以内で具体的に述べよ*
- 区分キーで区分された領域は**物理的にも**区分される
::: note info
- 1区分を切り離すだけで完了するから
:::
# 問2-設問3-(1)
*その理由をRDBMSの仕様に基づいて30字以内で述べよ*
- 「更新処理はバッチ処理であり、カーソルを使用して**1000行を更新するごとに**コミットする」
- 「トランザクションのコミットは**ログ出力処理完了まで待たされる**」
- ログが1000行になるまで、トランザクションもコミットされない
::: note info
- コミットは、ログ出力処理完了まで待たされるから
:::
# 問2-設問3-(2)
*本文中の用語を用いて25字以内で述べよ*
- 1000行ごとなら1回のUPDATEで済むが、1行ごとだとUPDATE文を**1000回実行**することになる
::: note info
- ログ出力処理の待ち時間がトータルで増加する
:::
# 問3-設問1-(1)
*表3中のイ、ロに入れる適切な数値を、ハ～ホに入れる適切な字句を答えよ*
- 「ここで、沿線、エアコン、オートロックの列値の分布は互いに独立し、各列の列値は一様分布に従うと仮定すること」
```math
\frac{1600000行}{沿線400×エアコン2×オートロック2}
```
::: note info
- イ:1000
:::
- 「設備にエアコン又はオートロックのいずれかがある物件」は、両方無い物件以外のパターン全てだから
```math
\frac{1600000行}{沿線400}×(1-\frac{両方なし1}{エアコン2×オートロック2}
```
::: note info
- ロ:3000
:::
- ハは分子なので、「設備にエアコンとオートロックの両方がある物件」数が入る
- ニは分母なので、「全物件数」が入る
- ホは、「沿線ごとに集計」
::: note info
- ハ:COUNT(*)
- ニ:TOTAL
- ホ:沿線
:::
# 問3-設問1-(2)
*Kさんが採用した案Bの長所を一つ、本文中の用語を用いて、25字以内で具体的に述べよ*
- 物件設備テーブルによって、新物件と設備の多対多に対応される
- 設備の追加を行う場合、従来は物件テーブルに新たな設備列を追加する必要があったが、案Bだと**テーブル構造を変えることなく**物件設備テーブルへの行追加で対応できる
::: note info
- テーブル構造を変えることなく、設備の追加に対応できる
:::
# 問3-設問1-(3)
*表4中のa、c及びdに入れる適切な字句を、b、eに入れる一つの適切な述語を答えよ*
- SQL4より、**エアコンの設備コードはA1**、**オートロックの設備コードはA2**
::: note info
- a:物件コード、'A1'、1
- b:エアコン='Y'
- c:UNION ALL
- d:物件コード、'A2'、1
- e:オートロック='Y'
:::
# 問3-設問1-(4)
*表5中のあ、いに入れる適切な数値を答えよ*
- 「設備コードは、**全設備を一意に識別**するコードで、そのうち20個は、物件テーブルの各設備列に対応させた」
- 「物件テーブルから**設備列20個**を除いた全行を、新物件テーブルに複写した」
::: note info
- あ:1600000
- い:20
:::
# 問3-設問2-(1)
*表6中のf、jに入れる適切な字句を答えよ*
- 「SQL1:沿線が○△線であり、かつ、設備にエアコンとオートロックの両方がある物件」なので、○△線、エアコンあり、オートロックありが**積結合**。INNER JOINで結合する。
- 「SQL2:沿線が○△線であり、かつ、設備にエアコンまたはオートロックのいずれかがある物件」なので、○△線、(エアコンA1かオートロックA2の少なくともどちらかがある)が**積結合**。()内は、**和結合なので、WHERE ～OR ～句**で条件を指定する
::: note info
- f:INNER JOIN
- g:INNER JOIN
- h:S1.設備コード='A1'
- i:S2.設備コード='A2'
- j:(S2.設備コード='A1' OR S2.設備コード='A2')
:::
# 問3-設問2-(2)
*このSQL7で行が重複するのはどのような場合か。本文中の用語を用いて、30字以内で具体的に述べよ*
- 物件設備テーブルは、**1設備に対して1行**なので、物件に2設備あれば2件
::: note info
- エアコンとオートロックの両方が設置されている物件
:::
# 問3-設問2-(3)
*図3中のk～oに入れる適切な字句を答えよ*
- エアコンの設備コードはA1
- 物件テーブルは、設備が**有っても無くても1行**なので、新物件テーブルとは左結合
::: note info
- k:BS1.設備コード='A1'
- l:'Y'
- m:'N'
- n:LEFT OUTER JOIN
- o:BS1.設備コード='A1'
:::