問1
問2
問3

公式サイトの問題冊子はこちら。
https://www.ipa.go.jp/shiken/mondai-kaiotu/ps6vr70000010d6y-att/2023r05a_db_pm1_qs.pdf

問1-設問1-(1)

関係“社員所属”の候補キーを全て挙げよ

「社員所属(社員コード、社員氏名、社員所属組織コード、社員所属組織名、社員所属上位組織コード、社員所属上位組織名、社員所属上位組織名、社員役職コード、社員役職名、報告先社員コード、報告先社員氏名)」
「組織は、組織コードで識別し、組織名をもつ。組織名は重複しない」
「社員は、社員コードで識別し、氏名をもつ。同姓同名の社員は存在し得る」
「社員は、いずれかの組織に所属し、複数の組織に所属し得る」
社員所属は、社員と所属の多対多関係をつなぐための中間テーブルのため、社員と所属組織それぞれのキーを組合せた複合キーをもつ
所属組織名の候補キーには社員所属組織コードと社員所属組織名がある
同姓同名が存在しうるので、氏名は社員の候補キーにならない

{社員コード、社員所属組織コード}、{社員コード、社員所属組織名}

問1-設問1-(2)

関係“社員所属”は、次のどの正規形に該当するか

一つのレコードの中に同じ項目が繰り返し存在しないので第1正規形以上
候補キーの一部である社員コードに社員氏名が部分関数従属している
候補キーの一部である社員所属組織コードに社員所属組織名が部分関数従属している
候補キーの一部である社員所属組織コードに社員所属上位組織コードが部分関数従属している
候補キーの一部に関数従属する属性が含まれるので第2正規形ではない

第1正規形

その根拠を、具体的な属性名を挙げて60字以内で答えよ

一つのレコードに同項目の繰返しはないが、候補キーの一部である社員コードに社員氏名が関数従属しているから

第3正規形に分解した関係スキーマを示せ

社員には、所属組織ごとに、業務内容の報告先となる社員が高々1名決まっている
非キー属性の報告先社員コードに報告先社員氏名が推移関係従属している

社員(社員コード、社員氏名)
組織(組織コード、組織名、上位組織コード(FK))
役職(役職コード、役職名)
社員所属(社員コード、社員所属組織コード、社員役職コード(FK)、報告先社員コード(FK)

問1-設問2-(1)

図1中の欠落しているリレーションシップを補って図を完成させよ

「品目を調達する際は、・・・複数の仕入先に見積依頼を行う」
同じ仕入先に対して違う日に複数の見積依頼を行う可能性があるので、仕入先と見積依頼は1対多

「見積依頼には、・・・どの試作案件に対する見積依頼かが分かるようにしておく」とあるので、試作案件と見積依頼は1対多

「見積回答が複数に分かれることはない」
「入手した見積回答には、見積依頼番号、・・・が記載されている」

「見積回答の明細には、見積依頼明細番号、・・・が記載されている」
「見積回答の明細には、見積依頼とは別の複数の品目が提案として返ってくることがある」

「見積回答の明細には、一つの品目に対して複数の調達条件が返ってくることがある」
「見積回答の明細には、・・・メーカー型式番号、・・・が記載されている」
メーカー型式番号は、品目の主キー

「選定した調達条件に対応する見積回答明細を発注明細に記録し、発注ロット数、指定納入年月日を決める」

問1-設問2-(2)

図2中のa～eに入れる一つ又は複数の適切な属性名を補って関係スキーマを完成させよ落しているリレーションシップを補って図を完成させよ

a

「モデルは、試作案件番号とモデル名で識別する」

試作案件番号

b

「通常、品目の調達はA社が行うが、得意先から無償で至急されることがある。この数量は得意先支給数量としてもつ」
「合計所要数量から得意先支給数量を減じた必要調達数量をもつ」

得意先支給数量、必要調達数量

c

「見積依頼には、見積依頼番号を付与し、見積依頼年月日を記録する。また、どの試作案件に対する見積依頼かが分かるようにしておく」
仕入先と見積依頼は1対多なので、見積依頼には仕入先の主キーを外部キーにもつ

試作案件番号(FK)、取引先コード(FK)

d

「見積回答の明細には、見積依頼明細番号、メーカー型式番号、調達条件、仕入先が付与した見積回答明細番号が記載されている」
「見積回答明細番号は、仕入先間で重複しうる」ので重複しない主キーを設けるため見積回答の主キーである見積依頼番号を追加する必要がある
「見積回答の明細には、見積依頼とは別の複数の品目が提案として返ってくることがある。その場合、その品目の提案理由が記載されている」
「見積回答の明細には、一つの品目に対して複数の調達条件が返ってくることがある。例えば、ロットサイズが1,000個の品目に対して、見積依頼の必要調達数量が300個の場合、仕入先から、ロットサイズ1,000個で単価0.5円、ロットサイズ1個で単価2円、という2通りの見積回答の明細が返ってくる」ので、調達条件としてロットサイズの追加が必要

見積依頼番号、メーカー型式番号(FK)、提案理由、ロットサイズ

e

「選定した調達条件に対応する見積回答明細を発注明細に記録し、発注ロット数、指定納入年月日を決める」
対応する見積回答明細を記録するために、その主キー{見積依頼番号、見積回答明細番号}を外部キーに追加する

見積依頼番号(FK)、見積回答明細番号(FK)、発注ロット数

問1-設問3-(1)

(a)

該当するエンティティタイプ名を挙げ、どのように追加又は変更すべきかを、30字以内で答えよ

「品目分類を大分類、中分類、小分類のような階層的な構造にしたい。当面は3層でよいが、将来的には階層を増やす可能性がある」
自エンティティタイプを参照する、再帰リレーションシップを作ることで、無限の階層を実現できる

品目分類に再帰リレーションシップを追加する

(b)

属性を追加する関係名及び追加する属性名を答えよ

自エンティティタイプの主キーへの外部キー「上位品目分類コード」をもつことで再帰リレーションを実現する

関係名 : 品目分類
属性名 : 上位品目分類コード

問1-設問3-(2)

(a)

該当するエンティティタイプ名を挙げ、どのように追加又は変更すべきかを、45字以内で答えよ

図1において、発注明細と入荷明細が1対1だと、分納要望に対応できない

発注明細と入荷明細のリレーションシップを1対多に変更する

(b)

属性を追加する関係名及び追加する属性名をそれぞれ答えよ

「未だ納入されていない数量である発注残ロット数も記録するようにしたい」を発注明細に追加する
入荷数が発注ロット数と一致しなくなる場合があるので、分納ごとの入荷ロット数を別途記録する必要がある

発注明細 : 発注残ロット数
入荷明細 : 入荷ロット数

問2-設問1-(1)

図1中の欠落しているリレーションシップを補って図を完成させよ

「客室はホテルごとに客室番号で識別する」

「客室ごとに客室タイプを設定する」

「1回の予約で、客は宿泊するホテル、客室タイプ、泊数、客室数、宿泊人数、チェックイン予定年月日を指定する」
ホテル、客室タイプは、複数の予約にまたがることがある

「予約有の場合には該当する予約を検索し、客室を決め、宿泊を記録する」
「予約無の場合には・・・客室を決め、宿泊を記録する」
予約有の場合のみ予約と紐付ける
一つの客室が別日の宿泊にまたがることがある

「会員の宿泊に対して、次回以降の宿泊料金に充当できる宿泊割引券を発行し、郵送する。1回の宿泊で割引券を1枚発行し、・・・」
割引券発行は宿泊と同時ではなく、後で実施することも考えられるため、図2の割引券発行対象宿泊への発行記録が必要

図2より、エンティティタイプ割引券の外部キーに、会員の主キーである会員番号が設定されている
「券面には割引券を識別する割引券番号と発行先の会員番号を記載する」
会員には宿泊回数と同数の割引券が発行される

「・・・、宿泊割引券番号を記録する。1回の予約で1枚を会員本人の予約だけに利用できる」

「ホテルでのチェックイン時の前払い、チェックアウト時の精算で利用する場合、宿泊割引券番号を記録する。1回の宿泊で1枚を会員本人の宿泊だけに利用できる」

「チェックアウト時の精算で利用する場合、館内施設割引券番号を記録する。1回の宿泊で1枚を会員本人の宿泊だけに利用できる」

問2-設問1-(2)

図2中のア～エに入れる一つ又は複数の適切な属性名を補って関係スキーマを完成させよ

ア

「客室ごとに客室タイプを設定する」
図2より客室タイプは客室タイプコードを主キーとするエンティティ

客室タイプコード(FK)

イ

「自社サイト予約では、・・・、どの旅行会社での予約かが記録される」

旅行会社コード(FK)

「1回の予約で、客は宿泊するホテル、客室タイプ、・・・を指定する」

ホテルコード(FK)、客室タイプコード(FK)

「予約時の前払いで利用する場合、宿泊割引券番号を記録する」

宿泊割引券番号(FK)

ウ

「予約有の場合には、・・・客室を決め、宿泊を記録する」
「予約無の場合には・・・客室を決め、宿泊を記録する」
図2より、客室は{ホテルコード、客室番号}が主キー

ホテルコード(FK)、客室番号(FK)

「ホテルでのチェックイン時の前払い、チェックアウト時の精算で利用する場合、宿泊割引券番号を記録する」
「チェックアウト時の精算で利用する場合、館内施設割引券番号を記録する」
「チェックインからチェックアウトまでを宿泊」

宿泊割引券番号(FK)、館内施設割引券番号(FK)

エ

「予約有の場合には該当する予約を検索し、客室を決め、宿泊を記録する」

予約番号(FK)

問2-設問2-(1)

表2中のa～dに入れる適切な字句を答えよ

「会員の宿泊に対して、次回以降の宿泊料金に充当できる宿泊割引券を発行」
「旅行会社予約による宿泊は発行対象外となる。発行対象の宿泊かどうかを割引券発行区分で分類する」
「自社サイト予約と旅行会社予約があり、予約区分で分類する」

a:宿泊
b:会員番号
c:割引券発行済フラグ
d:発行対象

問2-設問2-(2)

表3中のe～jに入れる適切な字句を答えよ

「予約時の前払いで利用する場合、宿泊割引券番号を記録する」
「割引券には宿泊割引券と館内施設割引券があり、割引券区分で分類する」
「割引券の状態には未利用、利用済、有効期限切れによる失効があり、割引券ステータスで分類する」
「1回の予約で1枚を会員本人の予約だけに利用できる」

e:割引券区分
f:宿泊割引券
g:割引券ステータス
h:未利用
i:会員番号
j:会員番号

問2-設問3-(1)

図3中の欠落しているリレーションシップを補って図を完成させよ

「毎月末に過去1年間の累計泊数に応じて会員の会員ランクを決める」
複数の会員に同じ会員ランクを設定する場合が考えられる

「ポイントの付与、支払充当、商品交換及び失効が発生する都度、・・・をポイント増減として記録する」

ポイントの付与、支払充当、商品交換、ポイント失効、はポイント増減のサブタイプ

図4より、ポイント増減(会員番号、ポイント増減連番、・・・)

「交換時に商品と個数を記録する」

問2-設問3-(2)

図4中のオ～サに入れる一つ又は複数の適切な属性名を補って関係スキーマを完成させよ

オ

「会員ランクには・・・、それぞれの必要累計泊数及びポイント付与率を決める」

必要累計泊数、ポイント付与率

カ

「商品ごとに交換に必要なポイント数を決める」
表1の商品名は、商品の属性にあたる

必要ポイント、商品名

キ

「・・・が発生する都度、ポイントの増減区分、増減数及び増減時刻をポイント増減として記録する」

ポイント増減区分、増減数、増減時刻

ク

「ポイントを付与した際に、有効期限年月日及び付与したポイント数を未利用ポイント数の初期値として記録する」

有効期限年月日、未利用ポイント数

ケ

「失効前メール添付日時と失効後メール送付日時を記録する」

失効後メール送付日時

コ

「支払充当では、支払充当区分(予約時、チェックイン時、チェックアウト時のいずれか)、ポイントを利用した予約の予約番号又は宿泊の宿泊番号を記録する」

支払充当区分

サ

「交換時に商品と個数を記録する」

商品コード(FK)、個数

問2-設問3-(3)

「ポイントが利用される都度、その時点で有効期限の近い未利用ポイント数から利用されたポイント数を減じて、消し込んでいく」
(a)消込みの対象とするインスタンスを選択する条件

未利用ポイント数が1以上

(b)(a)で選択したインスタンスに対して消込みを行う順序付けの条件
::: note info

有効期限期限年月日の近い順

問3-設問1-(1)

表3中のa,bに入れる適切な字句を答えよ

a

日平均温度は、分平均気温の平均から求められる

圃場ID、農事日付、AVG(分平均温度)

b

「圃場ごと農事日付ごと」

圃場ID、農事日付

問3-設問1-(2)

その理由を30字以内で答えよ

「圃場の日出時刻と日没時刻は・・・日ごとに変わる」
「日出時刻から翌日の日出時刻の1分前までとする日付を、農事日時という」

農事日時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

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を順に並べた時の先頭から現在行まで

f : 日平均温度
g : 圃場ID
h : 農事日付

問3-設問2-(1)

その理由を35字以内で答えよ

「ハッシュ区分:区分キーの値に基づき、RDBMSが生成するハッシュ値によって行を一定数の区分に分配する。区分値を変更する場合、全行を再分配する」
副次索引は、圃場ID、農事日付の順
表6から、区分化の目的は表5のSQL3の性能を上げることだと読み取れる

異なる圃場ID同士が混在し、区分化のメリットが得られないから

問3-設問2-(2)

表6中のアに入れる適切な数値を答えよ

「年度を区分キーとするレンジ区分によって区分化する」
「表2に示した副次索引から100日間の観測データ144,000行を読み込むことを仮定」
PARTITION BY 圃場IDで区分化後は、同ページに他の圃場IDのレコードが混ざらなくなる
圃場ID、農事日付の副次索引順に読み込むので、ページの切り替えは最小限になる
読込み行数÷ページ長

9000

問3-設問2-(3)

その理由を30字以内で答えよ

区分化前は{観測日付、観測時分、圃場ID}順なので、次に同じ圃場IDが得られるのは1440行後
ページあたり行数は4行なので、同ページに同じ圃場IDは存在不可能

同じページに同じ圃場IDの行は複数存在しないから

問3-設問2-(4)

その理由を35字以内で答えよ

「日出時刻から翌日の日出時刻の1分前までとする日付を、農事日付という」
「圃場IDごとに農事日付の1月1日から12月31日の値の範囲を年度として、その年度を区分キーとするレンジ区分によって区分化する」

農事日時は翌日の日出前までだから

問3-設問2-(5)

表7中のイ～カに入れる手順を、それぞれ次の①～⑤の中から一つ選べ

区分化前後に問わず、圃場カレンダーの不要行削除①と、断片化解消のための再構成②が必要
外部キー制約CASCADEが設定されているので、圃場カレンダーから不要行を削除すると自動的に観測からも不要行が削除される
観測の行削除後は断片化解消のための再構成④が必要
区分化前は①と④が抜けている

イ: ①
ウ: ④

区分化後の場合①②③④が足りないが、表7の３つの空欄には収まらない
区分化後は、レンジ区分の古い観測テーブルだけを物理的に切り離すことができ、この場合は観測の削除と再構成は不要なので、①②⑤のみで完了する
観測に不要行が残っている状態で圃場カレンダーからの削除を行うと、観測の自動削除が無意味に発動してしまうので、切り離し後に圃場カレンダーからの削除を行うのがいい

エ: ⑤
オ: ①
カ: ②