平成26年度 春期 データベーススペシャリスト試験 午後Ⅰ 問3 設問1 (4)

平成26年度　春期　データベーススペシャリスト試験　午後Ⅰ
http://www.jitec.ipa.go.jp/1_04hanni_sukiru/mondai_kaitou_2014h26_1/2014h26h_db_pm1_qs.pdf

問3 テーブルの設計及びSQLの設計に関する次の記述を読んで，設問1～3に答えよ。

　設問1 〔テーブルの設計〕について，(1)～(4)に答えよ。

　(4) 図5中の　　b　 ～　e　 に入れる適切な述語を，①～⑥から一つずつ選んで答えよ。


〔テーブルの設計〕

　Fさんが設計した関係”商品”及び”在庫”の関係スキーマを，図1に示す。

　商品（商品番号，商品名，商品説明，写真，販売単価，表示順）

　　単品商品（商品番号，社内原価）

　　セット商品（商品番号，化粧箱番号，詰合せ日数）

　在庫（商品番号，引当可能数）

　　単品商品在庫（商品番号，不足セット商品用引当済数）
　　セット商品在庫（商品番号，不足セット商品数）

図1 関係”商品”及び”在庫”の関係スキーマ

　Fさんは，関係”商品”のテーブルの設計に当たり，次の二つの案を考えた。

　　案1 サブタイプをスーパタイプに統合し，一つの”商品”テーブルとする。

　　案2 サブタイプ別に　”単品商品”テーブル及び”セット商品”テーブルとする。

　案1の”商品”テーブルの構造を図2に、案2の”単品商品”テーブル及び”セット商品”
テーブルの構造を図3に示す。

　商品（商品番号，商品名，商品説明，写真，販売単価，表示順，単品区分，

　　　　社内原価，化粧箱番号，詰合せ日数）

図2 案1の”商品”テーブルの構造

　単品商品（商品番号，商品名，商品説明，写真，販売単価，表示順，社内原価）

　セット商品（商品番号，商品名，商品説明，写真，販売単価，表示順，

　　　　　　　化粧箱番号，詰合せ日数）

図3 案2の”単品商品”テーブル及び”セット商品”テーブルの構造

　Fさんは，関係”在庫”については，一つの”在庫”テーブルを設計した。”在庫”テーブルと，
その他の主なテーブルの構造を，図4に示す。図4のテーブルは，全て案1，2に共通する。
また，主な列の意味を表1に示す。

　セット商品構成（セット商品番号，単品商品番号，構成数）

　在庫（商品番号，引当可能数，不足セット商品数，不足セット商品用引当済数）

　注文（注文番号，注文日，お届け予定日，住所，顧客名，電話番号，支払情報）

　注文明細（注文番号，注文明細番号，商品番号，販売単価，注文数）

図4 両案に共通の主なテーブルの構造

表1 主な列の意味

列名	意味
商品区分	単品商品とセット商品を識別する区分値。区分値は，単品商品では 'Y'，セット商品では'N'が設定される。
社内原価	単品商品の社内原価。セット商品の販売単価を決める際に必ず使用 される。
化粧箱番号	セット商品に使用される化粧箱を一意に識別する番号。セット商品には 必ず一つの化粧箱が使われ，同じ化粧箱番号が複数のセット商品で 使用される。
詰合せ日数	単品商品をセット商品として化粧箱に詰め合わせるのに要する日数。 未定の場合，NULLが設定される。
引当可能数	注文を受け付けたときに引き当て可能な数
不足セット商品 数	当該行がセット商品の場合，注文を受け付けたときに惹き当てられ なかったセット商品の数
不足セット商品 用引当済み数	当該行が単品商品の場合、注文を受け付けたときに引き当てられ なかったセット商品の詰合せのために引き当てた単品商品の数

　Fさんが案1の”商品”テーブルに定義した制約を表2に，案2の”単品商品”テーブル
及び”セット商品”テーブルに定義した制約を表3に示す。

　受注管理システムに採用される予定のRDBMSのUNIQUE制約は，ユニーク索引を
用いて実現される。ユニーク索引は，一つのテーブル内でキー列の一意性を保証する
ものであり，ユニーク索引を複数のテーブルにまたがって作成することはできない。


表2 案1”商品”テーブルに定義した制約（未完成）

テーブル名	制約の種類	制約を定義した列名
商品	PRIMARY KEY	商品番号
	UNIQUE	a
	NOT NULL	商品名，商品説明，写真，販売単価，表示順，単品区分

表3 案2の”単品商品”テーブル及び”セット商品”テーブルに定義した制約（未完成）

テーブル名	制約の種類	制約を定義した列名
単品商品	PRIMARY KEY	商品番号
	UNIQUE	a
	NOT NULL	商品名，商品説明，写真，販売単価，表示順，社内原価
セット商品	PRIMARY KEY	商品番号
	UNIQUE	a
	NOT NULL	商品名，商品説明，写真，販売単価，表示順，化粧箱番号

表2に示した案1でのNOT NULL制約は不十分なので，Fさんは，図5に示すように案1の
”商品”テーブルに検査制約を追加した。検査制約は，次の①～⑥のいずれかの述語を
組み合わせて指定する。

　①　社内原価　IS NOT NULL
　②　社内原価　IS NULL
　③　化粧箱番号　IS NOT NULL
　④　化粧箱番号　IS NULL
　⑤　詰合せ日数　IS NOT NULL
　⑥　詰合せ日数　IS NULL

　CHECK（   ( 単品区分 = 'Y' AND  ①　AND 　　b　  AND 　　C　 ）

　       OR ( 単品区分 = 'N' AND  　d　  AND 　　e　           ）)

図5 案1の”商品”テーブルに追加した検査制約（未完成）

やはり、データイメージがわからないと、想定できない。

図1 関係”商品”の関係スキーマと表1 主な列の意味を確認しつつ、記入してみる。

商品テーブル

商品番号	商品名	…	単品区分	社内原価	化粧箱番号	詰合せ日数
TS001	たまご茸	…	Y	100	null	null
TS002	へび苺	…	Y	120	null	null
SS001	山菜セット（松）	…	N	null	1	5/null
SS002	山菜セット（竹）	…	N	null	1	3

詰合せ日数では、「未定の場合，NULLが設定される」ということがあるので，注意


【回答】

b　：　④　（単品商品では、化粧箱番号は不使用）
c　：　⑥　（単品商品では、詰合せ日数は不使用）

d　：　②　（セット商品では、社内原価は不使用)
e　：　③　（セット商品では、化粧箱番号は必須)