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


公式サイトの問題冊子はこちら。
https://www.ipa.go.jp/shiken/mondai-kaiotu/ps6vr70000010d6y-att/2023r05a_db_pm1_qs.pdf
# 問1-設問1-(1)
*関係“社員所属”の候補キーを全て挙げよ*
- 「社員所属(**社員コード**、**社員氏名**、**社員所属組織コード**、**社員所属組織名**、社員所属上位組織コード、社員所属上位組織名、社員所属上位組織名、社員役職コード、社員役職名、報告先社員コード、報告先社員氏名)」
- 「組織は、組織コードで識別し、組織名をもつ。**組織名は重複しない**」
- 「社員は、社員コードで識別し、氏名をもつ。**同姓同名の社員は存在し得る**」
- 「社員は、いずれかの組織に所属し、複数の組織に所属し得る」
- 社員所属は、社員と所属の多対多関係をつなぐための中間テーブルのため、社員と所属組織それぞれのキーを組合せた複合キーをもつ
- 所属組織名の候補キーには社員所属組織コードと社員所属組織名がある
- 同姓同名が存在しうるので、氏名は社員の候補キーにならない
::: note info
- {社員コード、社員所属組織コード}、{社員コード、社員所属組織名}
:::
# 問1-設問1-(2)
*関係“社員所属”は、次のどの正規形に該当するか*
- 一つのレコードの中に同じ項目が繰り返し存在しないので第1正規形以上
- 候補キーの一部である社員コードに社員氏名が部分関数従属している
- 候補キーの一部である社員所属組織コードに社員所属組織名が部分関数従属している
- 候補キーの一部である社員所属組織コードに社員所属上位組織コードが部分関数従属している
- 候補キーの一部に関数従属する属性が含まれるので第2正規形ではない
::: note info
- 第1正規形
:::
*その根拠を、具体的な属性名を挙げて60字以内で答えよ*

::: note info
- 一つのレコードに同項目の繰返しはないが、候補キーの一部である社員コードに社員氏名が関数従属しているから
:::
*第3正規形に分解した関係スキーマを示せ*
- 社員には、**所属組織ごとに**、業務内容の報告先となる社員が高々1名決まっている
- 非キー属性の報告先社員コードに報告先社員氏名が推移関係従属している
::: note info
- 社員(<INS>社員コード</INS>、社員氏名)
- 組織(<INS>組織コード</INS>、組織名、上位組織コード(FK))
- 役職(<INS>役職コード</INS>、役職名)
- 社員所属(<INS>社員コード</INS>、<INS>社員所属組織コード</INS>、社員役職コード(FK)、報告先社員コード(FK)
:::
# 問1-設問2-(1)
*図1中の欠落しているリレーションシップを補って図を完成させよ*
- 「品目を調達する際は、・・・複数の仕入先に見積依頼を行う」
- 同じ仕入先に対して違う日に複数の見積依頼を行う可能性があるので、仕入先と見積依頼は1対多
::: note info
```mermaid
graph TB
  仕入先-->見積依頼
```
:::
- 「見積依頼には、・・・どの試作案件に対する見積依頼かが分かるようにしておく」とあるので、試作案件と見積依頼は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
  見積回答明細-->発注明細
```
:::
# 問1-設問2-(2)
*図2中のa～eに入れる一つ又は複数の適切な属性名を補って関係スキーマを完成させよ落しているリレーションシップを補って図を完成させよ*
## a
- 「モデルは、**試作案件番号**とモデル名で識別する」
::: note info
- <INS>試作案件番号</INS>
:::
## b
- 「通常、品目の調達はA社が行うが、得意先から無償で至急されることがある。この数量は**得意先支給数量**としてもつ」
- 「合計所要数量から得意先支給数量を減じた**必要調達数量**をもつ」
::: note info
- 得意先支給数量、必要調達数量
:::
## c
- 「見積依頼には、見積依頼番号を付与し、見積依頼年月日を記録する。また、どの**試作案件**に対する見積依頼かが分かるようにしておく」
- 仕入先と見積依頼は1対多なので、見積依頼には**仕入先の主キー**を外部キーにもつ
::: note info
- 試作案件番号(FK)、取引先コード(FK)
:::
## d
- 「見積回答の明細には、見積依頼明細番号、**メーカー型式番号**、**調達条件**、仕入先が付与した見積回答明細番号が記載されている」
- 「見積回答明細番号は、仕入先間で重複しうる」ので重複しない主キーを設けるため見積回答の主キーである**見積依頼番号**を追加する必要がある
- 「見積回答の明細には、見積依頼とは別の複数の品目が提案として返ってくることがある。その場合、その品目の**提案理由**が記載されている」
- 「見積回答の明細には、一つの品目に対して複数の調達条件が返ってくることがある。例えば、ロットサイズが1,000個の品目に対して、見積依頼の必要調達数量が300個の場合、仕入先から、ロットサイズ1,000個で単価0.5円、ロットサイズ1個で単価2円、という2通りの見積回答の明細が返ってくる」ので、調達条件として**ロットサイズ**の追加が必要
::: note info
- <INS>見積依頼番号</INS>、メーカー型式番号(FK)、提案理由、ロットサイズ
:::
## e
- 「選定した調達条件に**対応する見積回答明細を発注明細に記録**し、**発注ロット数**、指定納入年月日を決める」
- 対応する見積回答明細を記録するために、その主キー{**見積依頼番号**、**見積回答明細番号**}を外部キーに追加する
::: note info
- 見積依頼番号(FK)、見積回答明細番号(FK)、発注ロット数
:::
# 問1-設問3-(1)
## (a)
*該当するエンティティタイプ名を挙げ、どのように追加又は変更すべきかを、30字以内で答えよ*
- 「品目分類を大分類、中分類、小分類のような階層的な構造にしたい。当面は3層でよいが、**将来的には階層を増やす**可能性がある」
- 自エンティティタイプを参照する、**再帰リレーションシップ**を作ることで、無限の階層を実現できる
::: note info
- 品目分類に再帰リレーションシップを追加する
:::
## (b)
*属性を追加する関係名及び追加する属性名を答えよ*
- **自エンティティタイプの主キー**への外部キー「上位品目分類コード」をもつことで再帰リレーションを実現する
::: note info
- 関係名 : 品目分類
- 属性名 : 上位品目分類コード
:::
# 問1-設問3-(2)
## (a)
*該当するエンティティタイプ名を挙げ、どのように追加又は変更すべきかを、45字以内で答えよ*
- 図1において、発注明細と入荷明細が1対1だと、**分納要望**に対応できない
::: note info
- 発注明細と入荷明細のリレーションシップを1対多に変更する
:::
## (b)
*属性を追加する関係名及び追加する属性名をそれぞれ答えよ*
- 「未だ納入されていない数量である**発注残ロット数**も記録するようにしたい」を発注明細に追加する
- 入荷数が発注ロット数と一致しなくなる場合があるので、**分納ごとの入荷ロット数**を別途記録する必要がある
::: note info
- 発注明細 : 発注残ロット数
- 入荷明細 : 入荷ロット数
:::
# 問2-設問1-(1)
*図1中の欠落しているリレーションシップを補って図を完成させよ*
- 「**客室はホテルごとに**客室番号で識別する」
::: note info
```mermaid
graph TB
 ホテル-->客室
```
:::
- 「**客室ごとに客室タイプ**を設定する」
::: note info
```mermaid
graph TB
 客室タイプ-->客室
```
:::
- 「**1回の予約で**、客は宿泊する**ホテル**、**客室タイプ**、泊数、客室数、宿泊人数、チェックイン予定年月日を指定する」
- ホテル、客室タイプは、複数の予約にまたがることがある
::: note info
```mermaid
graph TB
 ホテル-->予約
 客室タイプ-->予約
```
:::
- 「**予約有の場合**には該当する**予約を検索**し、**客室**を決め、**宿泊**を記録する」
- 「予約無の場合には・・・**客室**を決め、**宿泊**を記録する」
- 予約有の場合のみ予約と紐付ける
- 一つの客室が別日の宿泊にまたがることがある
::: note info
```mermaid
graph TB
 予約有宿泊---予約
 客室-->宿泊 
```
:::
- 「会員の宿泊に対して、次回以降の宿泊料金に充当できる宿泊割引券を発行し、**郵送する**。**1回の宿泊で割引券を1枚発行**し、・・・」
- 割引券発行は宿泊と同時ではなく、後で実施することも考えられるため、図2の**割引券発行対象宿泊への発行記録**が必要
::: note info
```mermaid
graph TB
  宿泊割引券---割引券発行対象宿泊
```
:::
- 図2より、エンティティタイプ**割引券の外部キーに、会員の主キーである会員番号**が設定されている
- 「券面には割引券を識別する**割引券番号**と発行先の**会員番号**を記載する」
- 会員には**宿泊回数と同数の**割引券が発行される
::: note info
```mermaid
graph TB
  会員-->割引券
```
:::
- 「・・・、**宿泊割引券番号**を記録する。**1回の予約で1枚を**会員本人の予約だけに利用できる」
::: note info
```mermaid
graph TB
  宿泊割引券-->予約
```
:::
- 「ホテルでのチェックイン時の前払い、チェックアウト時の精算で利用する場合、**宿泊割引券番号を記録**する。**1回の宿泊で1枚を会員本人の宿泊だけに**利用できる」
::: note info
```mermaid
graph TB
  宿泊---宿泊割引券
```
:::
- 「チェックアウト時の精算で利用する場合、**館内施設割引券番号を記録**する。**1回の宿泊で1枚を会員本人の宿泊だけに**利用できる」
::: note info
```mermaid
graph TB
  宿泊---館内施設割引券
```
:::
# 問2-設問1-(2)
*図2中のア～エに入れる一つ又は複数の適切な属性名を補って関係スキーマを完成させよ*
## ア
- 「客室ごとに**客室タイプ**を設定する」
- 図2より客室タイプは客室タイプコードを主キーとするエンティティ
::: note info
- 客室タイプコード(FK)
:::
## イ
- 「自社サイト予約では、・・・、どの**旅行会社**での予約かが記録される」
::: note info
- 旅行会社コード(FK)
:::
- 「1回の予約で、客は**宿泊するホテル**、**客室タイプ**、・・・を指定する」
::: note info
- ホテルコード(FK)、客室タイプコード(FK)
:::
- 「**予約時の前払いで利用**する場合、**宿泊割引券番号**を記録する」
::: note info
- 宿泊割引券番号(FK)
:::
## ウ
- 「予約有の場合には、・・・**客室**を決め、**宿泊**を記録する」
- 「予約無の場合には・・・**客室**を決め、**宿泊**を記録する」
- 図2より、客室は{ホテルコード、客室番号}が主キー
::: note info
- ホテルコード(FK)、客室番号(FK)
:::
- 「ホテルでのチェックイン時の前払い、チェックアウト時の精算で利用する場合、**宿泊割引券番号**を記録する」
- 「チェックアウト時の精算で利用する場合、**館内施設割引券番号**を記録する」
- 「チェックインからチェックアウトまでを宿泊」
::: note info
- 宿泊割引券番号(FK)、館内施設割引券番号(FK)
:::
## エ
- 「**予約有の場合**には該当する**予約**を検索し、客室を決め、**宿泊を記録**する」
::: note info
- 予約番号(FK)
:::
# 問2-設問2-(1)
*表2中のa～dに入れる適切な字句を答えよ*
- 「**会員の宿泊に対して**、次回以降の宿泊料金に充当できる宿泊割引券を発行」
- 「**旅行会社予約**による宿泊は発行対象外となる。発行対象の宿泊かどうかを割引券発行区分で分類する」
- 「**自社サイト予約**と**旅行会社予約**があり、**予約区分**で分類する」
::: note info
- a:宿泊
- b:会員番号
- c:割引券発行済フラグ
- d:発行対象
:::
# 問2-設問2-(2)
*表3中のe～jに入れる適切な字句を答えよ*
- 「**予約時の前払いで利用**する場合、宿泊割引券番号を記録する」
- 「割引券には**宿泊割引券**と館内施設割引券があり、**割引券区分**で分類する」
- 「割引券の状態には未利用、**利用済**、有効期限切れによる失効があり、**割引券ステータス**で分類する」
- 「1回の予約で1枚を**会員本人の予約だけに**利用できる」
::: note info
- e:割引券区分
- f:宿泊割引券
- g:割引券ステータス
- h:未利用
- i:会員番号
- j:会員番号
:::
# 問2-設問3-(1)
*図3中の欠落しているリレーションシップを補って図を完成させよ*
- 「毎月末に過去1年間の累計泊数に応じて会員の会員ランクを決める」
- **複数の会員に同じ会員ランク**を設定する場合が考えられる
::: note info
```mermaid
graph TB
  会員ランク-->会員
```
:::
- 「**ポイントの付与、支払充当、商品交換及び失効**が発生する都度、・・・をポイント増減として記録する」
::: note info
- ポイントの付与、支払充当、商品交換、ポイント失効、はポイント増減のサブタイプ
:::
- 図4より、ポイント増減(**<INS>会員番号</INS>**、<INS>ポイント増減連番</INS>、・・・)
::: note info
```mermaid
graph TB
  会員-->ポイント増減
```
:::
- 「交換時に商品と個数を記録する」
::: note info
```mermaid
graph TB
  商品-->商品交換
```
:::
# 問2-設問3-(2)
*図4中のオ～サに入れる一つ又は複数の適切な属性名を補って関係スキーマを完成させよ*
## オ
- 「会員ランクには・・・、それぞれの**必要累計泊数**及び**ポイント付与率**を決める」
::: note info
- 必要累計泊数、ポイント付与率
:::
## カ
- 「商品ごとに交換に**必要なポイント数**を決める」
- 表1の商品名は、商品の属性にあたる
::: note info
- 必要ポイント、商品名
:::
## キ
- 「・・・が発生する都度、**ポイントの増減区分、増減数及び増減時刻**をポイント増減として記録する」
::: note info
- ポイント増減区分、増減数、増減時刻
:::
## ク
- 「ポイントを付与した際に、**有効期限年月日**及び付与したポイント数を**未利用ポイント数**の初期値として記録する」
::: note info
- 有効期限年月日、未利用ポイント数
:::
## ケ
- 「失効前メール添付日時と**失効後メール送付日時**を記録する」
::: note info
- 失効後メール送付日時
:::
## コ
- 「支払充当では、**支払充当区分**(予約時、チェックイン時、チェックアウト時のいずれか)、ポイントを利用した予約の予約番号又は宿泊の宿泊番号を記録する」
::: note info
- 支払充当区分
:::
## サ
- 「交換時に商品と個数を記録する」
::: note info
- 商品コード(FK)、個数
:::
# 問2-設問3-(3)
- 「ポイントが利用される都度、その時点で**有効期限の近い未利用ポイント数から**利用されたポイント数を減じて、消し込んでいく」
*(a)消込みの対象とするインスタンスを選択する条件*
::: note info
- 未利用ポイント数が1以上
:::
*(b)(a)で選択したインスタンスに対して消込みを行う順序付けの条件*
::: note info
- 有効期限期限年月日の近い順
:::
# 問3-設問1-(1)
*表3中のa,bに入れる適切な字句を答えよ*
## a
- 日平均温度は、**分平均気温の平均**から求められる
::: note info
- 圃場ID、農事日付、AVG(分平均温度)
:::
## b
- 「圃場ごと農事日付ごと」
::: note info
- 圃場ID、農事日付
:::
# 問3-設問1-(2)
*その理由を30字以内で答えよ*
- 「圃場の日出時刻と日没時刻は・・・**日ごとに変わる**」
- 「**日出時刻から翌日の日出時刻の1分前まで**とする日付を、農事日時という」
::: note info
- 農事日時1日は1440分とは限らないから
:::
# 問3-設問1-(3)
*図3中のc～eに入れる適切な数値を答えよ*
- 「ORDER BY **農事日付** ROWS BETWEEN **2 PRECEDING** AND CURRENT ROW」で、2つ前から現在までの農事日付を集計対象に指定している
- XはAVG(日平均温度)なので、**2日前～現在の日平均温度の平均**
- c=(10.0+12.0+20.0)/3
- d=(20.0+10.0+15.0)/3
- e=(15.0+14.0+19.0)/3
::: note info
- c : 14.0
- d : 15.0
- e : 16.0
:::
# 問3-設問1-(4)
*表5中のf～hに入れる適切な数値を答えよ*
- 「指定した農事日付の期間について、圃場ごと農事日付ごとの**積算温度**を調べる」
- 積算温度は、日平均温度の合計
- 「PARTITION BY g ORDER BY h ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW」は、gで区切った上でhを順に並べた時の先頭から現在行まで
::: note info
- f : 日平均温度
- g : 圃場ID
- h : 農事日付
:::
# 問3-設問2-(1)
*その理由を35字以内で答えよ*
- 「ハッシュ区分:区分キーの値に基づき、RDBMSが生成するハッシュ値によって行を一定数の区分に分配する。区分値を変更する場合、全行を再分配する」
- 副次索引は、**圃場ID**、農事日付の順
- 表6から、**区分化の目的は表5のSQL3の性能を上げること**だと読み取れる
::: note info
- 異なる圃場ID同士が混在し、区分化のメリットが得られないから
:::
# 問3-設問2-(2)
*表6中のアに入れる適切な数値を答えよ*
- 「年度を区分キーとするレンジ区分によって区分化する」
- 「表2に示した副次索引から100日間の観測データ144,000行を読み込むことを仮定」
- PARTITION BY 圃場IDで区分化後は、同ページに**他の圃場IDのレコードが混ざらなくなる**
- 圃場ID、農事日付の副次索引順に読み込むので、ページの切り替えは最小限になる
- **読込み行数÷ページ長**
::: note info
- 9000
:::
# 問3-設問2-(3)
*その理由を30字以内で答えよ*
- 区分化前は{観測日付、観測時分、圃場ID}順なので、**次に同じ圃場IDが得られるのは1440行後**
- ページあたり行数は4行なので、同ページに同じ圃場IDは存在不可能
::: note info
- 同じページに同じ圃場IDの行は複数存在しないから
:::
# 問3-設問2-(4)
*その理由を35字以内で答えよ*
- 「日出時刻から翌日の**日出時刻の1分前まで**とする日付を、農事日付という」
- 「圃場IDごとに**農事日付の1月1日から12月31日の値の範囲**を年度として、その年度を区分キーとするレンジ区分によって区分化する」
::: note info
- 農事日時は翌日の日出前までだから
:::
# 問3-設問2-(5)
*表7中のイ～カに入れる手順を、それぞれ次の①～⑤の中から一つ選べ*
- 区分化前後に問わず、**圃場カレンダーの不要行削除**①と、**断片化解消のための再構成**②が必要
- 外部キー制約CASCADEが設定されているので、圃場カレンダーから不要行を削除すると自動的に観測からも不要行が削除される
- 観測の行削除後は**断片化解消のための再構成**④が必要
- 区分化前は①と④が抜けている
::: note info
- イ: ①
- ウ: ④
:::
- 区分化後の場合①②③④が足りないが、**表7の３つの空欄には収まらない**
- 区分化後は、レンジ区分の古い観測テーブルだけを物理的に切り離すことができ、この場合は**観測の削除と再構成は不要**なので、①②⑤のみで完了する
- 観測に不要行が残っている状態で圃場カレンダーからの削除を行うと、観測の自動削除が無意味に発動してしまうので、**切り離し後に圃場カレンダーからの削除を行う**のがいい
::: note info
- エ: ⑤
- オ: ①
- カ: ②
:::