基本情報技術者試験 - 平成27年秋期(科目B) - 問3
問題
電子部品の出荷データを管理する関係データベースの運用に関する次の記述を読んで,設問1~4に答えよ。
C社は,電子部品を製造販売する会社である。
ある期間に出荷した特定の電子部品について,製造装置の設定ミスによる不具合が発生しているおそれがあるので,顧客への連絡と出荷済みの電子部品の無償交換(回収及び再出荷。以下,リコールという)を実施することにした。
出荷情報は,図1に示す表で管理されている。下線付きの項目は主キーを表す。
顧客表
部品表
出荷表
図1 表の構成とデータの格納例
設問1 リコールの対象となる電子部品の出荷先の顧客番号,顧客名,出荷番号,出荷日,出荷数を,顧客番号の昇順に表示する。リコールの対象となる電子部品の部品番号は“007551”で,出荷日は2015年1月10日から2015年1月20日までである。次のSQL文の[ a ]に入れる正しい答えを,解答群の中から選べ。
SELECT 顧客表.顧客番号, 顧客表.顧客名,
出荷表.出荷番号, 出荷表.出荷日, 出荷表.出荷数
FROM 顧客表, 出荷表
WHERE 出荷表.顧客番号 = 顧客表.顧客番号 AND
出荷表.部品番号 = '007551' AND
[ a ]
ORDER BY 顧客表.顧客番号
aに関する解答群
設問2 C社では,電子部品を単品で出荷するだけでなく,複数の電子部品を同梱したパッケージも出荷している。このパッケージにも一意の部品番号が割り振られている。パッケージの同梱部品の情報は,図2に示すパッケージ表で管理されている。
リコールの対象となる電子部品がパッケージにも含まれていることが判明したので,該当するパッケージの出荷情報も含めて表示するよう設問1のSQL文を変更する。次のSQL文の[ b ]に入れる正しい答えを,解答群の中から選べ。ここで,[ a ]には設問1の正しい答えが入っているものとする。また,パッケージの同梱部品にパッケージが含まれることはない。
部品表
パッケージ表
図2 パッケージ表の構成とデータの格納例
SELECT 顧客表.顧客番号, 顧客表.顧客名,
出荷表.出荷番号, 出荷表.出荷日, 出荷表.出荷数
FROM 顧客表, 出荷表
WHERE 出荷表.顧客番号 = 顧客表.顧客番号 AND
[ b ] AND
[ a ]
ORDER BY 顧客表.顧客番号
bに関する解答群
設問3 今回のリコールの対象となる電子部品の出荷金額の合計を表示する。次のSQL文の[ c ]に入れる正しい答えを,解答群の中から選べ。ここで,[ a ]と[ b ]には設問1及び設問2の正しい答えが入っているものとする。
SELECT [ c ] AS 合計出荷金額
FROM 出荷表
WHERE [ b ] AND
[ a ]
cに関する解答群
設問4 回収の対象となった出荷の情報は残したまま,再出荷に関する情報を管理することができるように,表の構成を変更する。次の記述中の[ ]に入れる適切な答えを,解答群の中から選べ。
図1及び図2の表に回収及び再出荷の情報を追加する場合,[ d ]に回収日と再出荷番号の項目を追加し,初期値にはNULLを設定しておき,回収対象の場合には回収した日と再出荷時の出荷番号を設定すればよい。ただし,この方法では既存データへの影響が大きく,また,リコールの頻度が低い場合は効率が悪い。
そこで,既存データに影響を与えない方法として,新たに回収表を作成して,一意に割り振った回収番号,回収対象となった出荷の出荷番号,回収日,再出荷時の出荷番号を格納する方法を考えた。この方法では,例えば,ある月の出荷金額の合計を求めるとき,回収対象となった出荷の出荷金額を除いて求めたい場合は,[ e ]から集計できる。
dに関する解答群
eに関する解答群
C社は,電子部品を製造販売する会社である。
ある期間に出荷した特定の電子部品について,製造装置の設定ミスによる不具合が発生しているおそれがあるので,顧客への連絡と出荷済みの電子部品の無償交換(回収及び再出荷。以下,リコールという)を実施することにした。
出荷情報は,図1に示す表で管理されている。下線付きの項目は主キーを表す。
顧客表
| 顧客番号 | 顧客名 | 住所 | 代表電話 |
| 0181 | 情報電機株式会社 | 東京都文京区本駒込○○-△ | 99-9999-9999 |
| 部品番号 | 部品名 | 単価 |
| 007551 | スイッチ | 80 |
| 出荷番号 | 顧客番号 | 部品番号 | 出荷数 | 出荷金額 | 出荷日 |
| 150412 | 0181 | 007551 | 400 | 32000 | 20150115 |
| 150413 | 0059 | 000890 | 100 | 48000 | 20150115 |
設問1 リコールの対象となる電子部品の出荷先の顧客番号,顧客名,出荷番号,出荷日,出荷数を,顧客番号の昇順に表示する。リコールの対象となる電子部品の部品番号は“007551”で,出荷日は2015年1月10日から2015年1月20日までである。次のSQL文の[ a ]に入れる正しい答えを,解答群の中から選べ。
SELECT 顧客表.顧客番号, 顧客表.顧客名,
出荷表.出荷番号, 出荷表.出荷日, 出荷表.出荷数
FROM 顧客表, 出荷表
WHERE 出荷表.顧客番号 = 顧客表.顧客番号 AND
出荷表.部品番号 = '007551' AND
[ a ]
ORDER BY 顧客表.顧客番号
aに関する解答群
| ア | 出荷表.出荷日 = '20150110' OR 出荷表.出荷日 = '20150120' |
|---|---|
| イ | 出荷表.出荷日 = ANY ('20150110', '20150120') |
| ウ | 出荷表.出荷日 BETWEEN '20150110' AND '20150120' |
| エ | 出荷表.出荷日 IN ('20150110', '20150120') |
設問2 C社では,電子部品を単品で出荷するだけでなく,複数の電子部品を同梱したパッケージも出荷している。このパッケージにも一意の部品番号が割り振られている。パッケージの同梱部品の情報は,図2に示すパッケージ表で管理されている。
リコールの対象となる電子部品がパッケージにも含まれていることが判明したので,該当するパッケージの出荷情報も含めて表示するよう設問1のSQL文を変更する。次のSQL文の[ b ]に入れる正しい答えを,解答群の中から選べ。ここで,[ a ]には設問1の正しい答えが入っているものとする。また,パッケージの同梱部品にパッケージが含まれることはない。
部品表
| 部品番号 | 部品名 | 単価 |
| 009220 | スイッチモジュール | 400 |
| 部品番号 | 同梱部品 | 同梱点数 |
| 009220 | 000058 | 1 |
| 009220 | 007551 | 3 |
SELECT 顧客表.顧客番号, 顧客表.顧客名,
出荷表.出荷番号, 出荷表.出荷日, 出荷表.出荷数
FROM 顧客表, 出荷表
WHERE 出荷表.顧客番号 = 顧客表.顧客番号 AND
[ b ] AND
[ a ]
ORDER BY 顧客表.顧客番号
bに関する解答群
| ア | (出荷表.部品番号 = '007551' AND 出荷表.部品番号 = ANY (SELECT パッケージ表.同梱部品 FROM パッケージ表)) |
|---|---|
| イ | (出荷表.部品番号 = '007551' AND 出荷表.部品番号 = ANY (SELECT パッケージ表.部品番号 FROM パッケージ表)) |
| ウ | (出荷表.部品番号 = '007551' OR 出荷表.部品番号 = ANY (SELECT パッケージ表.同梱部品 FROM パッケージ表 WHERE パッケージ表.部品番号 = '007551')) |
| エ | (出荷表.部品番号 = '007551' OR 出荷表.部品番号 = ANY (SELECT パッケージ表.部品番号 FROM パッケージ表 WHERE パッケージ表.同梱部品 = '007551')) |
設問3 今回のリコールの対象となる電子部品の出荷金額の合計を表示する。次のSQL文の[ c ]に入れる正しい答えを,解答群の中から選べ。ここで,[ a ]と[ b ]には設問1及び設問2の正しい答えが入っているものとする。
SELECT [ c ] AS 合計出荷金額
FROM 出荷表
WHERE [ b ] AND
[ a ]
cに関する解答群
| ア | AVG(出荷表.出荷金額) |
|---|---|
| イ | COUNT(出荷表.出荷金額) |
| ウ | MAX(出荷表.出荷金額) |
| エ | SUM(出荷表.出荷金額) |
設問4 回収の対象となった出荷の情報は残したまま,再出荷に関する情報を管理することができるように,表の構成を変更する。次の記述中の[ ]に入れる適切な答えを,解答群の中から選べ。
図1及び図2の表に回収及び再出荷の情報を追加する場合,[ d ]に回収日と再出荷番号の項目を追加し,初期値にはNULLを設定しておき,回収対象の場合には回収した日と再出荷時の出荷番号を設定すればよい。ただし,この方法では既存データへの影響が大きく,また,リコールの頻度が低い場合は効率が悪い。
そこで,既存データに影響を与えない方法として,新たに回収表を作成して,一意に割り振った回収番号,回収対象となった出荷の出荷番号,回収日,再出荷時の出荷番号を格納する方法を考えた。この方法では,例えば,ある月の出荷金額の合計を求めるとき,回収対象となった出荷の出荷金額を除いて求めたい場合は,[ e ]から集計できる。
dに関する解答群
| ア | 顧客表 |
|---|---|
| イ | 出荷表 |
| ウ | パッケージ表 |
| エ | 部品表 |
eに関する解答群
| ア | 出荷表と回収表 |
|---|---|
| イ | パッケージ表と回収表 |
| ウ | 部品表と回収表 |
| エ | 部品表と出荷表とパッケージ表 |
| オ | 部品表とパッケージ表と回収表 |
正解と解説
正解設問1 (a)ウ 設問2 (b)エ 設問3 (c)エ 設問4 (d)イ (e)ア
分野テクノロジ系 › 技術要素 › データベース › データ操作
解説
■ 問題の概要
電子部品のリコール(無償交換)のために,出荷データからSQLで必要な情報を取り出す問題です。SQLはデータベースに「こういう条件のデータを出して」と頼むための言語です。■ 設問1
出荷日が2015年1月10日から1月20日まで,という「範囲」の条件を書きます。範囲指定には「BETWEEN A AND B」(A以上B以下)を使うので,「ウ」が正解です。「ア」と「エ」は1月10日と1月20日の2日分だけを取り出してしまいます。「イ」の「= ANY」も同じ意味になり,間の日付が含まれません。
■ 設問2
リコール対象の部品“007551”は,単品で出荷されるほか,パッケージ(セット商品)の中にも含まれています。そのため,出荷表の部品番号が次のどちらかであれば対象です。- 部品番号が“007551”そのもの。
- 同梱部品に“007551”を含むパッケージの部品番号(例では“009220”)。
「どちらか」なのでORでつなぎます。後者は,パッケージ表から「同梱部品='007551'」の行を探し,その「部品番号」を取り出す副問合せで求めます。これに合うのは「エ」です。「ウ」は取り出す列と条件が逆になっています。■ 設問3
出荷金額の「合計」を求めるので,合計を計算する関数SUMを使います。AVGは平均,COUNTは件数,MAXは最大値です。■ 設問4
回収日と再出荷番号は,1件1件の「出荷」ごとに決まる情報なので,項目を追加するなら出荷表(「イ」)です。回収表を別に作る方法の場合,回収表には回収対象となった出荷番号が入っています。ある月の出荷金額の合計から回収分を除くには,出荷表の金額を集計し,そのうち回収表に出荷番号があるものを除けばよいので,出荷表と回収表(「ア」)があれば集計できます。