応用情報技術者過去問題 平成28年春期 午後問6

問題文解答用紙を別タブで開けます(印刷可能)。

問6 データベース

コンビニエンスストアにおけるデータウェアハウス構築及び分析に関する次の記述を読んで,設問1〜4に答えよ。

 W社は,コンビニエンスストアを全国展開する企業である。店舗ごとの売上を分析するために,データウェアハウスを構築することになった。

〔売上ファクト表の作成〕
 売行きが悪い商品を見つけるために,販売実績と在庫実績のデータを1日単位で集計して売上ファクト表を作成する。
 販売実績と在庫実績のデータは一つのデータベースによって管理されており,新たに追加するデータウェアハウスのデータも同じデータベース内に格納する。データベースのE-R図の抜粋を図lに,各エンティティの概要を表1に示す。
pm06_1.gif/image-size:452×622
 このデータベースでは,E-R図のエンティティ名を表名にし,属性名を列名にして,適切なデータ型で表定義した関係データベースによって,データを管理する。

 売上ファクト表に挿入するデータを抽出するSQL文を図2に示す。
 なお,店舗に在庫はあるが販売実績がない商品は日間販売数量を 0 とする。関数 COALESCE(A,B) は,AがNULLでないときはAを,AがNULLのときはBを返す。
pm06_2.gif/image-size:491×318
〔売行きが悪い商品分類の一覧の作成〕
 店舗ごとの月間の売行きが悪い商品分類の一覧を作成するために,図3のSQL文を作成した。一覧は,売上年月が新しいものから,店舗IDを昇順にして,平均在庫数量が多い順に表示させる。
 なお,関数 TO_YYYYMM は日付型の引数を受け,年月を6文字の文字列として返す。
pm06_3.gif/image-size:482×187
〔売行きが悪い商品分類の一覧を作成するSQL文の不具合〕
 図3のSQL文を,過去の実績データを用いてテストしたところ,複数の商品分類の平均販売数量に誤った値が見つかった。そこで,幾つかの店舗における販売及び在庫管理の運用方法を確認したところ,店舗や商品によって在庫数量を記録する頻度にばらつきがあることが判明した。ある店舗では,販売実績が少ない商品は1日3回ではなく,1週間に1回だけ,在庫数量を記録していた。この点に注目して,処理を見直すことにした。まず,①図2中のある副問合せを抜き出して,その結果を新たに作成した表に格納する。次に,この表に②不足しているデータを追加する。図2中のある副問合せをこうして得られた表と置き換えることで,問題を解決することができた。

設問1

図1のE-R図中のacに入れる適切なエンティティ間の関連及び属性名を答え,E-R図を完成させよ。
 なお,エンティティ間の関連及び属性名の表記は,図1の凡例に倣うこと。

-解答入力欄-

  • a:
  • b:
  • c:

-解答例・解答の要点-

  • a:販売時単価
  • b:
  • c:

-解説-

aについて〕
表1「各エンティティの概要」の"販売詳細"の説明には「顧客に販売した商品の数量や販売時単価を記録」とあります。販売詳細エンティティには販売時単価を表す属性がないので、追加する必要があります。したがって、aには「販売時単価」が当てはまります。

a=販売時単価

bについて〕
商品エンティティと購入詳細エンティティには共通の属性"商品ID"があります。この属性"商品ID"は、商品エンティティ側では主キーであり、商品を一意に特定します。また、販売詳細エンティティ側では外部キーであり、1つの商品が異なる日時に購入されることが考えられます(つまり、販売詳細エンティティには同一の商品IDの値を持つ複数のレコードが存在しえるということです)。
原則として、ある主キーをもつエンティティと、その主キーを外部キーに持つエンティティのカーディナリティ(多重度)は、主キー側を"1"、外部キー側を"多"とした「1対多」になります。したがって、bには「←」が当てはまります。

b=←
pm06_4.gif/image-size:334×122
cについて〕
bと同様に、主キーと外部キーの関係からカーディナリティを導きます。

商品分類エンティティと商品エンティティには共通の属性"商品分類ID"があります。この属性"商品分類ID"は、商品分類エンティティ側では主キーであり、商品分類を一意に特定します。また、商品エンティティ側では外部キーであり、1つの商品分類に複数の商品が属することが考えられます(つまり、商品エンティティには同一の商品分類IDの値を持つ複数のレコードが存在しえるということです)。
したがって、商品分類エンティティと商品エンティティのカーディナリティは1対多であり、cには「←」が当てはまります。

c=←
pm06_5.gif/image-size:225×105

設問2

図2中のdfに入れる適切な字句又は式を答えよ。なお,表の列名には必ずその表の別名を付けて答えよ。

-解答入力欄-

  • d:
  • e:
  • f:

-解答例・解答の要点-

  • d:LEFT OUTER JOIN
  • e:ST.店舗ID = SS.店舗ID
  • f:ST.商品ID = SS.商品ID

-解説-

図2のSQL文は、大まかに次の構造をもっています。
pm06_6.gif/image-size:491×318

dについて〕
図2のSQL文の下から3行目には「ON ST.確認年月日 = SS.販売年月日」とあるので、ST(αの部分の副問合せ:日間在庫数量を求めている)とSS(βの部分の副問合せ:日間販売数量を求めている)は何かしらの結合が行われると考えられます。〔売上ファクト表の作成〕には「店舗に在庫はあるが販売実績がない商品は日間販売数量を0とする。」とあるので、SS側にST側と対応するレコードがない場合でも、結合後に日間販売数量列の値として"0"を入力する必要があります。そのためには、まずSTとSSを左外部結合し、SS側にST側と対応するレコードがない場合は結合後の日間販売数量列がNULLになるようにします。次に、SELECT句でCOALESCE関数によりNULLの部分を"0"に置き換えるのです。したがって、dには「LEFT OUTER JOIN」が当てはまります(「LEFT JOIN」でも構いません)。

d=LEFT OUTER JOIN

efについて〕
STとSSを左外部結合する際の結合条件が入ります。
売上ファクト表は、(売上)年月日、店舗ID、商品IDによって販売数量や在庫数量を一意に特定します。そのため、STとSSの外部結合時に年月日、店舗ID、商品IDで結合する必要があります。図2のSQL文には年月日で結合する記述はありますが、店舗IDや商品IDで結合する記述がないので、追加する必要があります。したがって、2つの空欄には「ST.店舗ID = SS.店舗ID」及び「ST.商品ID = SS.商品ID」が当てはまります(順不同)。

ef=ST.店舗ID = SS.店舗ID、ST.商品ID = SS.商品ID

設問3

図3中のgに入れる適切な字句又は式を答えよ。
 なお,表の列名には必ずその表の別名を付けて答えよ。

-解答入力欄-

  • g:

-解答例・解答の要点-

  • g:ORDER BY SF.売上年月 DESC,SF.店舗ID ASC,平均在庫数量 DESC

-解説-

gについて〕
〔売行きが悪い商品分類の一覧の作成〕には「一覧は,売上年月が新しいものから,店舗IDを昇順にして,平均在庫数量が多い順に表示させる。」とあります。図3のSQL文には表示順に関する記述がないので「ORDER BY句」を追加する必要があります。SELECT文で取得されたレコードを、ORDER BY句を用いて売上年月の降順、店舗IDの昇順、平均在庫数量の降順に並び替えます(昇順にする場合は列名の直後にASCを記述し、降順にする場合は列名の直後にDESCを記述しますが、ASCの方は省略可能です)。
  • 売上年月が新しいものから(降順) → SF.売上年月 DESC
  • 店舗IDを昇順にして → SF.店舗ID ASC
  • 平均在庫数量が多い順(降順)に → 平均在庫数量 DESC
したがって、gには「ORDER BY SF.売上年月 DESC, SF.店舗ID ASC, 平均在庫数量 DESC」が当てはまります。(※SF.はつけなくてもSQL文は動作しますが、問題の指示により列名の前に表名をつける必要があるので注意してください)

g=ORDER BY SF.売上年月 DESC,SF.店舗ID ASC,平均在庫数量 DESC

設問4

〔売行きが悪い商品分類の一覧を作成するSQL文の不具合〕について,(1),(2)に答えよ。
  • 本文中の下線①に該当する副問合せは図2中のどの位置にあるか。α又はβで答えよ。
  • 本文中の下線②とはどのようなデータか。40字以内で述べよ。
     なお,販売及び在庫管理の運用方法は変更しないこと。

-解答入力欄-


-解答例・解答の要点-

    • α
    • 在庫数量を記録していない日の商品の在庫数量を実績から導出したデータ (33文字)

-解説-

  • 〔売行きが悪い商品分類の一覧を作成するSQL文の不具合〕には「ある店舗では,販売実績が少ない商品は1日3回ではなく,1週間に1回だけ,在庫数量を記録していた。」とあります。これは、店舗によっては日別の在庫レコードが存在しない商品があることを意味しています。

    図2のSQL文で在庫数量を求めているのは"α"の部分です。したがって、下線①に該当する副問合せは「α」です。

    "α"の中にAVGで日間在庫数量を集計している部分がありますが、在庫表に当該日のレコードがないとAVGの結果がnullになってしまいます。売上ファクト表の日間在庫数量にnullがあると、図3のSQL文で平均在庫数量を正しく求めることができません。このため日間在庫数量として適切な値が入力されるように、処理を見直す必要があります。

    ∴α

  • 在庫確認を行わない日には在庫表のレコードが作成されませんが、(1)で述べたように、図3のSQL文で平均在庫数量を正しく算出するためには、在庫表のレコードが存在しない日であっても日次の在庫数量データが必要となります。毎日最低1回は在庫確認を行うように運用方法を変更すれば済みそうですが、設問の指示に「販売及び在庫管理の運用方法は変更しないこと。」とあるので、現状の運用方法のままで解決する方法を考えます。

    ここで、〔売上ファクトの作成〕の「販売実績と在庫実績のデータは一つのデータベースによって管理されており,新たに追加するデータウェアハウスのデータも同じデータウェア内に格納する。」に着目します。これはデータウェアハウスから在庫実績を参照可能ということであり、在庫表に当該日のレコードが記録されていなくても、同じデータベース内に格納されている在庫実績から、日次の在庫数量を導出できるということです。

    したがって、"α"の表に追加されるべき不足するデータとは「在庫数量を記録していない日の商品の在庫数量を実績から導出したデータ」となります。

    ∴在庫数量を記録していない日の商品の在庫数量を実績から導出したデータ
問6成績
【平成28年春期 午後問題】
 問1 問2 問3 問4 問5 問6 問7 問8 問9 問10 問11
© 2010-2020 応用情報技術者試験ドットコム All Rights Reserved.

Pagetop