応用情報技術者過去問題 平成28年春期 午後問6
⇄問題文と設問を画面2分割で開く⇱問題PDF問6 データベース
コンビニエンスストアにおけるデータウェアハウス構築及び分析に関する次の記述を読んで，設問1～4に答えよ。
W社は，コンビニエンスストアを全国展開する企業である。店舗ごとの売上を分析するために，データウェアハウスを構築することになった。
〔売上ファクト表の作成〕
売行きが悪い商品を見つけるために，販売実績と在庫実績のデータを1日単位で集計して売上ファクト表を作成する。
販売実績と在庫実績のデータは一つのデータベースによって管理されており，新たに追加するデータウェアハウスのデータも同じデータベース内に格納する。データベースのE-R図の抜粋を図1に，各エンティティの概要を表1に示す。
このデータベースでは，E-R図のエンティティ名を表名にし，属性名を列名にして，適切なデータ型で表定義した関係データベースによって，データを管理する。
売上ファクト表に挿入するデータを抽出するSQL文を図2に示す。
なお，店舗に在庫はあるが販売実績がない商品は日間販売数量を 0 とする。関数 COALESCE(A，B) は，AがNULLでないときはAを，AがNULLのときはBを返す。
〔売行きが悪い商品分類の一覧の作成〕
店舗ごとの月間の売行きが悪い商品分類の一覧を作成するために，図3のSQL文を作成した。一覧は，売上年月が新しいものから，店舗IDを昇順にして，平均在庫数量が多い順に表示させる。
なお，関数 TO_YYYYMM は日付型の引数を受け，年月を6文字の文字列として返す。
〔売行きが悪い商品分類の一覧を作成するSQL文の不具合〕
図3のSQL文を，過去の実績データを用いてテストしたところ，複数の商品分類の平均販売数量に誤った値が見つかった。そこで，幾つかの店舗における販売及び在庫管理の運用方法を確認したところ，店舗や商品によって在庫数量を記録する頻度にばらつきがあることが判明した。ある店舗では，販売実績が少ない商品は1日3回ではなく，1週間に1回だけ，在庫数量を記録していた。この点に注目して，処理を見直すことにした。まず，①図2中のある副問合せを抜き出して，その結果を新たに作成した表に格納する。次に，この表に②不足しているデータを追加する。図2中のある副問合せをこうして得られた表と置き換えることで，問題を解決することができた。
W社は，コンビニエンスストアを全国展開する企業である。店舗ごとの売上を分析するために，データウェアハウスを構築することになった。
〔売上ファクト表の作成〕
売行きが悪い商品を見つけるために，販売実績と在庫実績のデータを1日単位で集計して売上ファクト表を作成する。
販売実績と在庫実績のデータは一つのデータベースによって管理されており，新たに追加するデータウェアハウスのデータも同じデータベース内に格納する。データベースのE-R図の抜粋を図1に，各エンティティの概要を表1に示す。
売上ファクト表に挿入するデータを抽出するSQL文を図2に示す。
なお，店舗に在庫はあるが販売実績がない商品は日間販売数量を 0 とする。関数 COALESCE(A，B) は，AがNULLでないときはAを，AがNULLのときはBを返す。
店舗ごとの月間の売行きが悪い商品分類の一覧を作成するために，図3のSQL文を作成した。一覧は，売上年月が新しいものから，店舗IDを昇順にして，平均在庫数量が多い順に表示させる。
なお，関数 TO_YYYYMM は日付型の引数を受け，年月を6文字の文字列として返す。
図3のSQL文を，過去の実績データを用いてテストしたところ，複数の商品分類の平均販売数量に誤った値が見つかった。そこで，幾つかの店舗における販売及び在庫管理の運用方法を確認したところ，店舗や商品によって在庫数量を記録する頻度にばらつきがあることが判明した。ある店舗では，販売実績が少ない商品は1日3回ではなく，1週間に1回だけ，在庫数量を記録していた。この点に注目して，処理を見直すことにした。まず，①図2中のある副問合せを抜き出して，その結果を新たに作成した表に格納する。次に，この表に②不足しているデータを追加する。図2中のある副問合せをこうして得られた表と置き換えることで，問題を解決することができた。
設問1
図1のE-R図中のa～cに入れる適切なエンティティ間の関連及び属性名を答え，E-R図を完成させよ。
なお，エンティティ間の関連及び属性名の表記は，図1の凡例に倣うこと。
なお，エンティティ間の関連及び属性名の表記は，図1の凡例に倣うこと。
解答入力欄
- a：
- b：
- c：
解答例・解答の要点
- a：販売時単価
- b：←
- c：←
解説
〔aについて〕
表1「各エンティティの概要」の"販売詳細"の説明には「顧客に販売した商品の数量や販売時単価を記録」とあります。販売詳細エンティティには販売時単価を表す属性がないので、追加する必要があります。したがって、空欄には「販売時単価」が当てはまります。
∴a＝販売時単価
〔bについて〕
商品エンティティと購入詳細エンティティには共通の属性"商品ID"があります。この属性"商品ID"は、商品エンティティ側では主キーであり、商品を一意に特定します。また、販売詳細エンティティ側では外部キーであり、1つの商品が異なる日時に購入されることが考えられます（つまり、販売詳細エンティティには同一の商品IDの値を持つ複数のレコードが存在しえるということです）。
原則として、ある主キーをもつエンティティと、その主キーを外部キーに持つエンティティのカーディナリティ（多重度）は、主キー側を"1"、外部キー側を"多"とした「1対多」になります。したがって、空欄には「←」が当てはまります。
∴b＝←
〔cについて〕
bと同様に、主キーと外部キーの関係からカーディナリティを導きます。
商品分類エンティティと商品エンティティには共通の属性"商品分類ID"があります。この属性"商品分類ID"は、商品分類エンティティ側では主キーであり、商品分類を一意に特定します。また、商品エンティティ側では外部キーであり、1つの商品分類に複数の商品が属することが考えられます（つまり、商品エンティティには同一の商品分類IDの値を持つ複数のレコードが存在しえるということです）。
したがって、商品分類エンティティと商品エンティティのカーディナリティは1対多であり、空欄には「←」が当てはまります。
∴c＝←
表1「各エンティティの概要」の"販売詳細"の説明には「顧客に販売した商品の数量や販売時単価を記録」とあります。販売詳細エンティティには販売時単価を表す属性がないので、追加する必要があります。したがって、空欄には「販売時単価」が当てはまります。
∴a＝販売時単価
〔bについて〕
商品エンティティと購入詳細エンティティには共通の属性"商品ID"があります。この属性"商品ID"は、商品エンティティ側では主キーであり、商品を一意に特定します。また、販売詳細エンティティ側では外部キーであり、1つの商品が異なる日時に購入されることが考えられます（つまり、販売詳細エンティティには同一の商品IDの値を持つ複数のレコードが存在しえるということです）。
原則として、ある主キーをもつエンティティと、その主キーを外部キーに持つエンティティのカーディナリティ（多重度）は、主キー側を"1"、外部キー側を"多"とした「1対多」になります。したがって、空欄には「←」が当てはまります。
∴b＝←
bと同様に、主キーと外部キーの関係からカーディナリティを導きます。
商品分類エンティティと商品エンティティには共通の属性"商品分類ID"があります。この属性"商品分類ID"は、商品分類エンティティ側では主キーであり、商品分類を一意に特定します。また、商品エンティティ側では外部キーであり、1つの商品分類に複数の商品が属することが考えられます（つまり、商品エンティティには同一の商品分類IDの値を持つ複数のレコードが存在しえるということです）。
したがって、商品分類エンティティと商品エンティティのカーディナリティは1対多であり、空欄には「←」が当てはまります。
∴c＝←
設問2
図2中のd～fに入れる適切な字句又は式を答えよ。なお，表の列名には必ずその表の別名を付けて答えよ。
解答入力欄
- d：
- e：
- f：
解答例・解答の要点
- d：LEFT OUTER JOIN
- e：ST.店舗ID = SS.店舗ID
- f：ST.商品ID = SS.商品ID
解説
図2のSQL文は、大まかに次の構造をもっています。
STは、在庫表から店舗・商品・日別に在庫数量の平均を集計した中間表、SSは、販売表から店舗・商品・日別に販売数量を集計した中間表です。
〔dについて〕
図2のSQL文の下から3行目には「ON ST.確認年月日 = SS.販売年月日」とあるので、STとSSは何かしらの結合が行われると考えられます。〔売上ファクト表の作成〕には「店舗に在庫はあるが販売実績がない商品は日間販売数量を0とする」とあるので、SS側にST側と対応するレコードがない場合でも、結合後に日間販売数量列の値として"0"を入力する必要があります。そのためには、まずSTとSSを左外部結合し、SS側にST側と対応するレコードがない場合は結合後の日間販売数量列がNULLになるようにします。次に、SELECT句のCOALESCE関数によりNULLの部分を"0"に置き換えるのです。したがって、空欄には「LEFT OUTER JOIN」が当てはまります（「LEFT JOIN」でも構いません）。
∴d＝LEFT OUTER JOIN
〔e、fについて〕
STとSSを左外部結合する際の結合条件が入ります。
売上ファクト表は、（売上）年月日、店舗ID、商品IDによって販売数量や在庫数量を一意に特定します。そのため、STとSSの外部結合時に年月日、店舗ID、商品IDで結合する必要があります。図2のSQL文には年月日で結合する記述はありますが、店舗IDや商品IDで結合する記述がないので、追加する必要があります。したがって、2つの空欄には「ST.店舗ID = SS.店舗ID」及び「ST.商品ID = SS.商品ID」が当てはまります（順不同）。
∴e、f＝ST.店舗ID = SS.店舗ID、ST.商品ID = SS.商品ID
STは、在庫表から店舗・商品・日別に在庫数量の平均を集計した中間表、SSは、販売表から店舗・商品・日別に販売数量を集計した中間表です。
図2のSQL文の下から3行目には「ON ST.確認年月日 = SS.販売年月日」とあるので、STとSSは何かしらの結合が行われると考えられます。〔売上ファクト表の作成〕には「店舗に在庫はあるが販売実績がない商品は日間販売数量を0とする」とあるので、SS側にST側と対応するレコードがない場合でも、結合後に日間販売数量列の値として"0"を入力する必要があります。そのためには、まずSTとSSを左外部結合し、SS側にST側と対応するレコードがない場合は結合後の日間販売数量列がNULLになるようにします。次に、SELECT句のCOALESCE関数によりNULLの部分を"0"に置き換えるのです。したがって、空欄には「LEFT OUTER JOIN」が当てはまります（「LEFT JOIN」でも構いません）。
∴d＝LEFT OUTER JOIN
〔e、fについて〕
STとSSを左外部結合する際の結合条件が入ります。
売上ファクト表は、（売上）年月日、店舗ID、商品IDによって販売数量や在庫数量を一意に特定します。そのため、STとSSの外部結合時に年月日、店舗ID、商品IDで結合する必要があります。図2のSQL文には年月日で結合する記述はありますが、店舗IDや商品IDで結合する記述がないので、追加する必要があります。したがって、2つの空欄には「ST.店舗ID = SS.店舗ID」及び「ST.商品ID = SS.商品ID」が当てはまります（順不同）。
∴e、f＝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の方は省略可能です
∴g＝ORDER BY SF.売上年月 DESC，SF.店舗ID ASC，平均在庫数量 DESC
〔売行きが悪い商品分類の一覧の作成〕には「一覧は，売上年月が新しいものから，店舗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
設問4
〔売行きが悪い商品分類の一覧を作成するSQL文の不具合〕について，(1)，(2)に答えよ。
解答入力欄
解答例・解答の要点
- α
- 在庫数量を記録していない日の商品の在庫数量を実績から導出したデータ (33文字)
解説
- 〔売行きが悪い商品分類の一覧を作成するSQL文の不具合〕には「ある店舗では，販売実績が少ない商品は1日3回ではなく，1週間に1回だけ，在庫数量を記録していた」とあります。つまり、店舗によっては在庫確認をサボっていて在庫記録が存在しない日があるということです。
在庫記録がない店舗・商品・日の組みについては、ST（日間在庫数量）のレコードとしても存在しないので、もしSS（日間販売数量）にその店舗・商品・日があったとしても、左外部結合で残らず売上ファクト表に挿入するデータから漏れてしまいます。これにより実際の販売数量の平均と、売上ファクト表から求めた販売数量の平均が異なる値になってしまう不具合が生じることとなります。
この不具合は、上記の欠けているレコードを補完する形で在庫表にレコードを追加すれば解消できるので、変更する必要があるのは日間在庫数量を求めている"α"の部分です。したがって、下線①に該当する副問合せは「α」です。
∴α - (1)で述べたように、不足しているデータとは在庫記録がなかった日の在庫数量データです。1日3回の在庫確認を徹底すれば済みそうですが、在庫変動が乏しい商品をチェックするのは手間ですし、設問の指示に「販売及び在庫管理の運用方法は変更しないこと」とあるので、現状の運用方法のままで解決する方法を考えます。
在庫記録がない場合であっても、日ごとの在庫数は、直前に記録された在庫数量と販売表および販売詳細表の販売実績から論理的に導出できるので、在庫記録がなかった日の在庫数量データとしてこのデータを挿入すれば期待どおりに動作するようになります。したがって、"α"の表に追加されるべき不足するデータとは「記録していない日の在庫数量を、直前に記録した日の在庫数量と販売数量から求めたデータ」になります。
∴在庫数量を記録していない日の商品の在庫数量を実績から導出したデータ
