平成29年秋期試験午後問題　問3

午前試験免除制度対応！基本情報技術者試験のeラーニング【独習ゼミ】

問3　データベース

会員制通信販売事業者における会員販売データ管理に関する次の記述を読んで，設問1～4に答えよ。

　清涼飲料水の会員制通信販売事業を運営するD社では，販売促進と商品管理の効率化を目的に会員情報や販売情報を管理するシステム(以下，販売管理システムという)を，事業開始当初から導入している。注文の受付は電話応対で行い，電話の受付時間は8時から20時までである。
　販売管理システムで利用するデータベースの表構成とデータ格納例を，図1に示す。下線付きの項目は主キーである。
pm03_1.png
広告

設問1

販売促進のために，コーヒーの新商品案内のはがきを送ることになった。その際，購入しそうな会員に効率よく案内するために，2016年の1月1日から12月31日までの1年間において，分類がコーヒーである商品を5回以上購入し，かつ，その購入額の合計が10,000円以上である会員の氏名，郵便番号，住所を抽出することにした。ここで，1回の購入は販売明細表の1行に該当するものとする。次のSQL文の　に入れる正しい答えを，解答群の中から選べ。
pm03_2.png
a に関する解答群
  • pm03_3a.png
  • pm03_3i.png
  • pm03_3u.png
  • pm03_3e.png
解答選択欄
  • a:
  • a=イ

解説

  • グループ化を行う前に以下の条件式で行を絞っています。
    AND 商品表.単価 * 販売明細表.個数 >10000
    グループ化を行う前の単価×個数は販売明細表の1行ごとの購入額を示しています。つまり、この条件に合致するのは、1回の購入で1つのコーヒーが10,000円以上購入された行だけであるため不適切です。
  • 正しい。まずWHERE句で日付と分類で絞った後、グループ化を行っています。そして
    SUM(商品表.単価 * 販売明細表.個数) >= 10000
    販売明細表の行ごとに計算した購入金額を合計したものが10,000円以上であること
    COUNT(*) >= 5
    行数、すなわち購入履歴が5件以上あることの2つの条件でグループを絞っています。
  • HAVING句で参照される列はグループ化列または集計関数でなければなりません。このSQL文では、
    商品表.分類 = 'コーヒー'
    の部分で、グループ化されていない列を条件式に使用しているので誤りです。
  • 「ウ」と同様に、HAVING句でグループ化されていない"販売日"列と"分類"列を使用しているので誤りです。
広告

設問2

商品表の単価を何回でも変更できるようにする。併せて，販売時点の単価が分かるように，販売明細表の項目として販売時点の単価を追加することにした。変更した販売明細表の表構成を，図2に示す。商品表の単価の変更は，当日の受付時間前に行う。販売時点の単価の追加によって得ることができる情報として最も適切な答えを，解答群の中から選べ。
pm03_4.png
解答群
  • ある時，ある商品をある会員が購入した単価と，その直後に変更された単価との価格差
  • 実際に購入された商品の，販売時点の単価の変遷
  • 全ての商品の，単価の変遷
  • 全ての商品の，直近の単価変更日の前日における単価
解答選択欄
  • 　
  • 　イ

解説

販売時点単価は購入された時点のみで記録されるため、単価が変更されてもそれ以後にその商品の購入がないケースではデータベース上に記録が残りません。また販売単価が変更された日時も分かりません。
  • 単価変更直後に購入されている商品でなければ比較はできません。
  • 正しい。実際に販売が行われたケースに限り販売時点単価の変遷を確認できます。
  • 商品によっては単価変更後の販売記録が販売明細表にない可能性もあるため得られません。
  • 単価変更日の前日に購入されていない商品からは前日の単価を取得できません。
広告

設問3

商品表の単価を変更できるようにした後の販売状況を把握するために，2017年の1月1日から6月30日までの半年間を対象に，商品表の分類別の販売額の合計(合計販売額)を会員の年齢ごとに求めて，出力したい。年齢は2017から生年を引いた値とする。次のSQL文の　に入れる正しい答えを，解答群の中から選べ。ここで，b1～ b3に入れる答えは，bに関する解答群の中から組合せとして正しいものを選ぶものとする。
pm03_5.png
b に関する解答群
pm03_6.png
解答選択欄
  • b:
  • b=オ

解説

設問のSQLはFROM句の表としてSELECT文で導出した結果表を用いています。SQL文全体を主問合せと元表と作成する部分とに分離してみると次のような構造が見えてきます。
pm03_9.png
3つの空欄がありますがb2から考えていきます。元表を導出するSELECT文を実行すると以下のような結果セットが得られます。
pm03_10.png
"年齢"と"分類"は指定済ですが、販売額の合計を得るための属性がありません。販売額の合計は「販売時単価×個数」で求められるため、b2には"販売時単価"と"個数"の組合せが入ります。商品表の"単価"はSQL実行時における商品単価であり、販売時の単価とは異なる可能性があるので不適切です。また販売表の"販売額"は複数の商品を含む1つの注文に対応した金額であるため不適切です。

∴b2＝販売明細表.販売時単価，販売明細表.個数

この時点で選択肢は「オ」「カ」に絞られます。次にGROUP BY句で指定するb3について考えます。GROUP BYの対象となる元表(FACTTB)は、年齢、分類、販売時単価、個数の4列から成っており"合計販売額"という列は存在しません。
pm03_11.png
GROUP BYで指定可能な列は元表内に存在する列に限られるため、GROUP BY句で"販売合計額"列を指定している「カ」は不適切と分かります。

∴b3＝年齢，分類

したがって空欄に入る適切な組合せは「オ」です。
広告

設問4

　入荷情報を管理するシステム(以下，入荷管理システムという)を販売管理システムと同時に運用開始している。入荷管理システムで利用するデータベースの表構成を図3に示す。
　ビュー入荷集計表は運用開始から現在までの入荷数の総数を表示する。さらに販売総数を把握するためにビュー販売集計表を，最新の在庫数を把握するためにビュー在庫表を作成する。ビュー在庫表は一度でも入荷した商品は在庫数ゼロでも表示する仕様である。データベースに追加する表の構成を，図4に示す。
　次の記述中の　に入れる正しい答えを，解答群の中から選べ。ここで，c1とc2に入れる答えは，cに関する解答群の中から組合せとして正しいものを選ぶものとする。

　図1～図3の表を用いて，図4のビュー販売集計表を作成するための必要最小限の表の数はc1である。図4のビュー在庫表は，ビュー販売集計表を用いて作成する。このとき，ビュー在庫表を作成するための必要最小限の表の数は，ビュー販売集計表も含めてc2である。
pm03_7.png
c に関する解答群
pm03_8.png
解答選択欄
  • c:
  • c=ア

解説

〔c1について〕
"ビュー販売集計表"には、商品番号と販売総数の2つの列があります。設問の表のうち商品ごとの販売総数は"販売明細表"に記録されているので、"ビュー販売集計表"を得るには"販売明細表"の個数を商品番号ごとに集計すれば足ります。

〔c2について〕
"ビュー在庫表"には、商品番号と在庫数の2つの列があります。現在の在庫数は入荷総数から出荷総数と差し引いた数ですから、在庫数は"ビュー入荷集計表"と"ビュー販売集計表"を結合して入荷総数と出荷総数の差異を計算することによって得られます。つまり"ビュー在庫表"の作成には2つの表で足ります。

c1＝1，c2＝2 なので適切な組合せは「ア」です。
広告
広告

前の問題次の問題

▲
Pagetop