午後問6問題文と解答用紙を別タブで開けます(印刷可能)。データベースコンビニエンスストアにおけるデータウェアハウス構築及び分析に関する次の記述を読んで,設問1〜4に答えよ。W社は,コンビニエンスストアを全国展開する企業である。店舗ごとの売上を分析するために,データウェアハウスを構築することになった。売行きが悪い商品を見つけるために,販売実績と在庫実績のデータを1日単位で集計して売上ファクト表を作成する。販売実績と在庫実績のデータは一つのデータベースによって管理されており,新たに追加するデータウェアハウスのデータも同じデータベース内に格納する。データベースのE-R図の抜粋を図lに,各エンティティの概要を表1に示す。このデータベースでは,E-R図のエンティティ名を表名にし,属性名を列名にして,適切なデータ型で表定義した関係データベースによって,データを管理する。売上ファクト表に挿入するデータを抽出するSQL文を図2に示す。AがNULLでないときはAを,AがNULLのときはBを返す。店舗ごとの月間の売行きが悪い商品分類の一覧を作成するために,図3のSQL文を作成した。一覧は,売上年月が新しいものから,店舗IDを昇順にして,平均在庫数量が多い順に表示させる。日付型の引数を受け,年月を6文字の文字列として返す。図3のSQL文を,過去の実績データを用いてテストしたところ,複数の商品分類の平均販売数量に誤った値が見つかった。そこで,幾つかの店舗における販売及び在庫管理の運用方法を確認したところ,店舗や商品によって在庫数量を記録する頻度にばらつきがあることが判明した。ある店舗では,販売実績が少ない商品は1日3回ではなく,1週間に1回だけ,在庫数量を記録していた。この点に注目して,処理を見直すことにした。1図2中のある副問合せを抜き出して,その結果を新たに作成した表に格納する。次に,この表に2不足しているデータを追加する。図2中のある副問合せをこうして得られた表と置き換えることで,問題を解決することができた。設問1図1のE-R図中のa〜cに入れる適切なエンティティ間の関連及び属性名を答え,E-R図を完成させよ。エンティティ間の関連及び属性名の表記は,図1の凡例に倣うこと。表1「各エンティティの概要」の"販売詳細"の説明には「顧客に販売した商品の数量や販売時単価を記録」とあります。販売詳細エンティティには販売時単価を表す属性がないので、追加する必要があります。したがって、aには「販売時単価」が当てはまります。商品エンティティと購入詳細エンティティには共通の属性"商品ID"があります。この属性"商品ID"は、商品エンティティ側では主キーであり、商品を一意に特定します。販売詳細エンティティ側では外部キーであり、1つの商品が異なる日時に購入されることが考えられます(つまり、販売詳細エンティティには同一の商品IDの値を持つ複数のレコードが存在しえるということです)。原則として、ある主キーをもつエンティティと、その主キーを外部キーに持つエンティティのカーディナリティ(多重度)は、主キー側を"1"、外部キー側を"多"とした「1対多」になります。したがって、bには「←」が当てはまります。〔cについて〕bと同様に、主キーと外部キーの関係からカーディナリティを導きます。商品分類エンティティと商品エンティティには共通の属性"商品分類ID"があります。この属性"商品分類ID"は、商品分類エンティティ側では主キーであり、商品分類を一意に特定します。商品エンティティ側では外部キーであり、1つの商品分類に複数の商品が属することが考えられます(つまり、商品エンティティには同一の商品分類IDの値を持つ複数のレコードが存在しえるということです)。したがって、商品分類エンティティと商品エンティティのカーディナリティは1対多であり、cには「←」が当てはまります。設問2図2中のd〜fに入れる適切な字句又は式を答えよ。表の列名には必ずその表の別名を付けて答えよ。SS.商品ID-解説-図2のSQL文は、大まかに次の構造をもっています。SS.販売年月日」とあるので、ST(αの部分の副問合せ:日間在庫数量を求めている)とSS(βの部分の副問合せ:日間販売数量を求めている)は何かしらの結合が行われると考えられます。〔売上ファクト表の作成〕には「店舗に在庫はあるが販売実績がない商品は日間販売数量を0とする。」とあるので、SS側にST側と対応するレコードがない場合でも、結合後に日間販売数量列の値として"0"を入力する必要があります。そのためには、まずSTとSSを左外部結合し、SS側にST側と対応するレコードがない場合は結合後の日間販売数量列がNULLになるようにします。次に、SELECT句でCOALESCE関数によりNULLの部分を"0"に置き換えるのです。JOIN」でも構いません)。JOIN〔e、fについて〕STとSSを左外部結合する際の結合条件が入ります。売上ファクト表は、(売上)年月日、店舗ID、商品IDによって販売数量や在庫数量を一意に特定します。そのため、STとSSの外部結合時に年月日、店舗ID、商品IDで結合する必要があります。図2のSQL文には年月日で結合する記述はありますが、店舗IDや商品IDで結合する記述がないので、追加する必要があります。SS.商品ID」が当てはまります(順不同)。SS.商品ID設問3図3中のgに入れる適切な字句又は式を答えよ。表の列名には必ずその表の別名を付けて答えよ。〔売行きが悪い商品分類の一覧の作成〕には「一覧は,売上年月が新しいものから,店舗IDを昇順にして,平均在庫数量が多い順に表示させる。BY句」を追加する必要があります。降順に並び替えます(昇順にする場合は列名の直後にASCを記述し、降順にする場合は列名の直後にDESCを記述しますが、ASCの方は省略可能です)。DESC」が当てはまります。DESC設問4〔売行きが悪い商品分類の一覧を作成するSQL文の不具合〕について,(1),(2)に答えよ。本文中の下線1に該当する副問合せは図2中のどの位置にあるか。本文中の下線2とはどのようなデータか。販売及び在庫管理の運用方法は変更しないこと。(33文字)-解説-〔売行きが悪い商品分類の一覧を作成するSQL文の不具合〕には「ある店舗では,販売実績が少ない商品は1日3回ではなく,1週間に1回だけ,在庫数量を記録していた。これは、店舗によっては日別の在庫レコードが存在しない商品があることを意味しています。図2のSQL文で在庫数量を求めているのは"α"の部分です。したがって、下線1に該当する副問合せは「α」です。"α"の中にAVGで日間在庫数量を集計している部分がありますが、在庫表に当該日のレコードがないとAVGの結果がnullになってしまいます。売上ファクト表の日間在庫数量にnullがあると、図3のSQL文で平均在庫数量を正しく求めることができません。このため日間在庫数量として適切な値が入力されるように、処理を見直す必要があります。在庫確認を行わない日には在庫表のレコードが作成されませんが、(1)で述べたように、図3のSQL文で平均在庫数量を正しく算出するためには、在庫表のレコードが存在しない日であっても日次の在庫数量データが必要となります。毎日最低1回は在庫確認を行うように運用方法を変更すれば済みそうですが、設問の指示に「販売及び在庫管理の運用方法は変更しないこと。」とあるので、現状の運用方法のままで解決する方法を考えます。ここで、〔売上ファクトの作成〕の「販売実績と在庫実績のデータは一つのデータベースによって管理されており,新たに追加するデータウェアハウスのデータも同じデータウェア内に格納する。これはデータウェアハウスから在庫実績を参照可能ということであり、在庫表に当該日のレコードが記録されていなくても、同じデータベース内に格納されている在庫実績から、日次の在庫数量を導出できるということです。したがって、"α"の表に追加されるべき不足するデータとは「在庫数量を記録していない日の商品の在庫数量を実績から導出したデータ」となります。Reserved.▲Pagetop。