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

データベーススペシャリスト

Posted at 2024-10-10

問1
問2
問3

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

問1-設問1-(1)

図2中のa～kに入れる適切な属性名を答えよ

「組織には、コピー機を販売する営業所と、コピー機の設置と保守を行うサービスセンタがあり、組織区分で分類する」

a: 組織区分

「地域ごとに、担当SCを決めている」

b: SC組織コード(FK)

「営業担当者はいずれかの営業所に所属する」

c: 営業所組織コード(FK)

「顧客ごとに、担当営業所を決めている」

d: 営業所組織コード(FK)

「設置営業所は、顧客コードと設置事業所コードで識別し、名称、住所、その住所を基にした地域コードを登録する」

e: 顧客コード
f: 地域コード(FK)

「見積りは、見積番号で識別し、案件名、顧客コード、担当営業所の組織コード、営業担当者の社員番号、見積年月日、納期年月日、見積有効期限年月日、商品コード、商品コードごとの数量、見積単価を登録する」

g: 顧客コード(FK)
h: 営業所組織コード(FK)
i: 社員番号(FK)

「受注は、受注番号で識別し、該当する見積番号、受注年月日を登録する」

j: 見積番号(FK)

「受注明細は、設置の単位であり、本体製品1台単位、又はセット製品1セット単位に作成し、設置事業所、設置場所詳細、設置補足を登録する」
「設置事業所は、顧客コードと設置事業所コードで識別」

k: 商品コード(FK)

問1-設問1-(2)

必要なリレーションシップを全て記入し、図を完成させよ

「設置事業所は、顧客コードと設置事業所コードで識別し、、、地域コードを登録する」
顧客と設置事業所は1対多
地域と設置事業所は1対多

「顧客ごとに、担当営業所を決めている」
営業所と顧客は1対多

「地域ごとに、担当SCを決めている」
SCと地域は1対多

「営業担当者は、いずれか一つの営業所に所属する」
営業所と営業担当者は1対多

「見積りは、、、顧客コード、担当営業所の組織コード、営業担当者の社員番号、、、商品コード、、、を登録する」
顧客と見積は1対多
営業所と見積は1対多
営業担当者と見積は1対多
商品と見積は1対多

「受注は、受注番号で識別し、該当する見積番号、受注年月日を登録する」
「成約に至ったときに、見積りと同じ単位で受注登録を行う」
見積と受注は1対1

問1-設問2-(1)

商品名の例に倣って、太枠内に◯印を付けて、表1を完成させよ

商品区分

「商品には、製品と設置サービスがあり、商品区分で分類する」

製品区分

「単体製品には、本体製品とオプション製品がある」
「本体製品かオプション製品かは、製品区分で分類する」

オプション区分

「オプション製品には、、、などがあり、オプション区分で分類する」

単体セット製品区分

「製品には、単体製品とセット製品がある」
「単体製品かセット製品かは、単体セット製品区分で分類する」

シリーズコード

「本体製品には、製品のシリーズを表すシリーズコードを登録する」

製品サイズ

「単体製品には、製品サイズを登録する」

標準設置時間

「設置サービスには、標準設置時間を登録する」

設置サービス商品コード

「製品ごとに、どの設置サービスを適用するかを決めている」

セット製品本体製品商品コード

「セット製品は、一つの本体製品と一つ以上のオプション製品を組み合わせたもので、どのオプション製品で構成されるかについて、セット製品構成に登録する」

エンティティタイプ	商品名	商品区分	製品区分	オプション区分	単体セット区分	シリーズコード	製品サイズ	標準設置時間	設置サービス商品コード
商品	◯	◯
製品					◯				◯
設置サービス								◯
本体製品						◯
オプション製品				◯
セット製品
単体製品			◯				◯

問1-設問2-(2)

図3中の太枠内にエンティティタイプ名を入れ、欠落しているリレーションシップを補って、図を完成させよ

「商品には、製品と設置サービスがあり、商品区分で分類する」

「製品には、単体製品とセット製品がある」

「単体製品には、本体製品とオプション製品がある」

「設置サービスには複数種類があり、製品ごとに、どの設置サービスを適用するかを決めている」
設置サービスと製品は1対多

「セット製品は一つの本体製品と一つ以上のオプション製品を組み合わせたもので、どのオプション製品で構成されるかについて、セット製品構成に登録する」
本体とセット製品は1対多
セット製品とオプション製品は多対多なので、連関エンティティとしてセット製品構成を利用する

問1-設問3-(1)

新たに追加する関係の主キー及び外部キーを明記した関係スキーマ、属性を追加する関係名及び追加する属性名を答えよ

「出荷指示は出荷指示番号で識別し、出荷指示年月日を登録する」

関係スキーマ: 出荷指示(出荷指示番号、出荷指示年月日)

「同じ設置事業所、同じタイミングで出荷できる場合は、受注明細をまとめて出荷指示を行う」
出荷指示と受注明細は1対多なので、受注明細の外注キーとして出荷指示の主キーを設定する

追加する関係名: 受注明細
追加する属性名: 出荷指示番号

問1-設問3-(2)

受注明細内訳のうち、出荷指示の対象とならない場合が２つある。どのような場合か、それぞれ15字以内で具体的に述べよ

「出荷指示は、コピー機メーカの物流センタから出荷する製品を対象とする」
「セット製品と、セット製品に必要な設置サービスをそれぞれ登録する」
設置サービスは製品に含まれない
「商品がセット製品の場合、そのセット製品自体と、セット製品を構成する製品を展開した内訳を登録する」
「セット製品を構成する製品」の発注指示があれば、「セット製品自体」の発注指示は不要

内訳が設置サービスの場合
内訳がセット製品自体の場合

問2-設問1-(1)

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

「退職すると従業員テーブルの退職フラグを、あらかじめ設定している'0'(在籍)から'1'(退職)に更新する」

a: '1'

「毎月25日に、"従業員"テーブル及び"従業員家族"テーブルから、当月の20日までに退職した従業員の行を削除する」
削除対象は、退職フラグが'1'のうち、退職年月日が当月20日まで(21日より前または当月より前)のもの
今日の日付はCURRENT_DATEで取得できる

b: 21
c: CURRENT_DATE

問2-設問1-(2)

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

図2より、参照対象の先頭は「部署コード」
SQL2「FROM 従業員、従業員家族」
図1より、部署コードは従業員に含まれる属性

d: 従業員.部署コード

COUNT(従業員家族.従業員コード)のままだと、従業員家族数の計算になるので、DISTINCTの付加により従業員コードの重複を排除することで、従業員数を計算するようにする

e: DISTINCT

「部署コード順に2007年1月1日以降に生まれた扶養対象者をもつ従業員数と扶養対象者数の一覧表が欲しいとの依頼があった」
並べ替えは、ORDER BY

f: ORDER BY

問2-設問2-(1)

正常終了・エラーのいずれかを◯で囲んで示せ。エラーとなる場合は、その理由を40字以内で具体的に述べよ

「新規従業員登録のために、所属未定の行を従業員テーブルに挿入」なので、(INSERTだから)表3のUPDATE/DELETEに該当せず、参照制約機能は発動しない

(a): 正常終了

「従業員テーブルから当該従業員を削除」
表3の従業員家族(従業員コード)のDELETEに該当し、CASCADEなので、テーブル:従業員家族からも削除される
表3の部署(管理者従業員コード)のDELETEに該当するがテーブル:部署はNO ACTIONなので、テーブル:部署に残したまま
テーブル:部署に残された当該従業員の管理者従業員コードを参照できなくなる

(b): エラー
理由: 退職者が従業員テーブルから削除されると、部署テーブルの参照制約違反になるから

問2-設問2-(2)

新たに定義するトリガについて、対象となるテーブルのテーブル名、実行タイミング、処理内容をそれぞれ答えよ

「参照制約を利用することによって不具合が発生する」とは、従業員テーブルから削除すると、DELETE:CASCADEなので、自動的に従業員家族も削除されることを指す
この不具合を活用し、削除を契機に「別テーブルに保存する」トリガを作る
なお、自動的に従業員家族も削除されるので、「従業員テーブルのトリガ」のうち、「従業員家族テーブルのその従業員の家族の行について、別テーブルに挿入し、その後削除する」が不要になる

テーブル名: 従業員家族
実行タイミング: 削除後
処理内容: 削除された行を別テーブルに追加

問2-設問3-(1)

不具合が発生する契機を図3中の丸数字で答えよ

①のタイミングで

削除した部署に所属する従業員とその家族がDELETEされる問題

契機: ①
不具合の内容: 削除した部署に所属する従業員が従業員テーブルからDELETEされる

問2-設問3-(2)

どのように変更すればよいか、①、③、⑤の変更後の順序を答えよ

①で削除される部署コードが、従業員テーブルで使われていなければよいので、先に従業員テーブルの部署コードを更新(⑤)する
⑤で従業員テーブルの部署コードが、部署テーブルに存在しないと参照制約違反になるので、先に部署テーブルへ新規の部署を追加(③)しておく

③→⑤→①

問2-設問3-(3)

(a)

RDBMSは猶予モードの制約の検査のために、トランザクション終了時にどのような検査を行っているか、検査内容を55字以内で具体的に述べよ

部署(部署コード)のUPDATE時はNO ACTION
部署と従業員に同じ部署コードが使用されていた場合でも、部署の更新は従業員に反映されない
従業員(部署コード)から参照先:部署(部署コード)への参照制約を検査するため、更新前の部署コードが使われていないことを確認

更新前の部署コードを、従業員テーブルから参照していないことを検査する

(b)

(a)の検査を行う際、想定よりも処理時間が長くなる恐れがある。その理由を50字以内で具体的に述べよ

「索引は主キーだけに定義されている」
従業員(部署コード)は外部キーなので索引は無く、索引検索より遅い表探索になる

従業員テーブルの部署コードに索引が無いため、更新前の部署コードと同じ行を表検索するから

問3-設問1-(1)

UNIQUE制約を定義する列の構成をそれぞれ答えよ

「内線番号　各店舗に設置されている内線電話を施設内で識別する番号」
精算テーブルの非キー属性のなかにはテーブルを識別できるものはない

店舗: {施設ID、内線番号}
精算: なし

問3-設問1-(2)

図2中のa～dに入れる適切な述語を一つずつ答えよ

「子供(12歳未満):'1'」
「大人(12歳以上60歳未満):'2'」
「シニア(60歳以上):'3'」

a: 年齢<12
b: 年齢区分='2'
c: 年齢 BETWEEN 12 AND 59
d: 年齢>=60

問3-設問1-(3)

①

索引1は、ユニーク索引又は非ユニーク索引のどちらに該当するか答えよ

列名	意味・制約
店舗ID	施設内の各店舗を識別する文字列
鍵番号	施設内のロッカーを識別する番号。客の精算後、鍵は再利用される
未精算フラグ	利用額が未精算の場合:'Y'、精算済みの場合:'N'

鍵は再利用されるので、同じ店舗ID、同じ鍵番号で精算済みの記録は複数ありえる

①: 非ユニーク

②

索引2は、高クラスタな索引である。その理由を35字以内で述べよ

「高クラスタな索引:キー値の順番と、キーが指す行の物理的な並び順が一致しているか、完全に一致していなくても、隣接するキーが指す行が同じページに格納されている割合が高い」
索引2のキーの構成は券の主キーと同じで{利用年月日、施設ID、券番号}
精算明細の主キーは{利用年月日、利用時刻、施設ID、券番号、店舗ID、商品ID}
「受付では、客が1枚ずつ入館券を購入し、、、」
「未精算の利用額は、退館時に複数台のいずれかの精算機で精算する」
同じ券(主キー:利用年月日、施設ID、券番号)の精算明細が、精算時にまとめて追加される

②: 同じ入館券に対する精算明細がまとめて追加されるから

問3-設問1-(4)

表5中のア～キに入れる適切な字句を、表5中の下線部分の用語を用いて答えよ

下線部分の用語

見積行数(行)
平均行長(バイト/行)
最大行長(バイト/行)
ページ長(バイト/ページ)
有効ページ長(バイト/ページ)
ページ当たりの行数(行/ページ)
必要ページ数(ページ)

1行分のデータが必ず1ページ内に収まるようなページ長を選択する

ア: 最大行長

ページの空き領域を平均行長よりも大きくする

イ: 有効ページ長

ウ=\frac{有効ページ長}{ページ当たりの平均行数}=\frac{[バイト/ページ]}{[行/ページ]}=[バイト/行]=平均行長

ウ: 平均行長

エ÷オの単位が[ページ]になる組み合わせ

\frac{見積行数}{ページ当たりの平均行数}=\frac{[行]}{[行/ページ]}=[ページ]

エ: 見積行数
オ: ページ当たりの平均行数

ストレージ所要量(バイト)は、必要ページ数×ページ長

カ: 必要ページ数
キ: ページ長

問3-設問1-(5)

表6中のク～コに入れる適切な字句を、表5、6中の下線部分の用語を用いて答えよ