応用情報技術者試験の過去問&キーワードを動画2443本の動画で解説!スキマ時間に動画!※2017年9月11日現在

PR広告

平成28年度春 応用情報技術者試験 午後問6 データベース 設問1 合格率アップ!動画付き解説

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

W社は、コンビニエンスストアを全国展開する企業である。

店舗ごとの売上を分析するために、データウェアハウスを構築することになった。

〔売上ファクト表の作成〕

売行きが悪い商品を見つけるために、販売実績と在庫実績のデータを1日単位で集計して売上ファクト表を作成する。

販売実績と在庫実績のデータは一つのデータベースによって管理されており、新たに追加するデータウェアハウスのデータも同じデータベース内に格納する。

データベースのE-R図の抜粋を図1に、各エンティティの概要を表1に示す。

平成28年度春 応用情報技術者試験 午後問6 データベース 合格率アップ!動画付き解説!

平成28年度春 応用情報技術者試験 午後問6 データベース 合格率アップ!動画付き解説!

このデータベースでは、E-R図のエンティティ名を表名にし、属性名を列名にして、適切なデータ型で表定義した関係データベースによって、データを管理する。

売上ファクト表に挿入するデータを抽出するSQL文を図2に示す。

なお、店舗に在庫はあるが販売実績がない商品は日間販売量を0とする。関数COALESCE(A,B)はAがNULLでないときはAを、AがNULLのときはBを返す。

SELECT 
    ST.確認年月日
  , ST.店舗ID
  , ST.商品ID
  , COALESCE(SS.日間販売数量, 0)
  , ST.日間在庫数量
FROM 
  (SELECT 
       SC.確認年月日
	 , SC.店舗ID
	 , SC.商品ID
	 , AVG(SC.在庫数量) AS 日間在庫数量
   FROM 在庫 SC
   GROUP BY 
       SC.確認年月日
	 , SC.店舗ID
	 , SC.商品ID) ST
   d
  (SELECT 
       SL.販売年月日
	 , SL.店舗ID
	 , SL.商品ID
	 , SUM(SD.販売数量) AS 日間販売数量
   FROM 販売 SL
     INNER JOIN 販売詳細 SD ON SL.販売ID = SD.販売ID
   GROUP BY 
       SL.販売年月日
	 , SL.店舗ID
	 , SD.商品ID
   ) SS
   ON  ST.確認年月日 = SS.販売年月日
   AND e
   AND f

図2 売上ファクト表に挿入するデータを抽出するSQL文

〔売行きが悪い商品分類の一覧の作成〕

店舗ごとの月間の売行きが悪い商品分類の一覧を作成するために、図3のSQL文を作成した。

一覧は、売上年月が新しいものから、店舗IDを昇順にして、平均在庫数量が多い順に表示させる。

なお、関数TO_YYYYMMは日付型の引数を受け、年月を6文字の文字列として返す。

SELECT 
    SF.売上年月
  , SF.店舗ID
  , IT.商品分類ID
  , AVG(SF.日間販売数量) AS 平均販売数量
  , AVG(SF.日間在庫数量) AS 平均在庫数量
  , ST.日間在庫数量
FROM 
  (SELECT 
       TO_YYYYMM(SA.売上年月日) AS 売上年月
	 , SA.店舗ID
	 , SA.商品ID
	 , SA.日間販売数量
	 , SA.日間在庫数量
   FROM 売上ファクト SA) SF
  INNER JOIN 商品 IT ON SF.商品ID = IT.商品ID
GROUP BY 
    SF.売上年月
  , SF.店舗ID
  , IT.商品分類ID 
g

図3 売行きが悪い商品分類の一覧を作成するSQL文

〔売行きが悪い商品分類の一覧を作成するSQL文の不具合〕

図3のSQL文を、過去の実績データを用いてテストしたところ、複数の商品分類の平均販売数量に誤った値が見つかった。

そこで、幾つかの店舗における販売及び在庫管理の運用方法を確認したところ、店舗や商品によって在庫数量を記録する頻度にばらつきがあることが判明した。

ある店舗では、販売実績が少ない商品は1日3回ではなく、1週間に1回だけ、在庫数量を記録していた。

この点に注目して、処理を見直すことにした。

まず、①図2中のある副問合せを抜き出して、その結果を新たに作成した表に格納する。

次に、この表に②不足しているデータを追加する。

図2中のある副問合せをこうして得られた表と置き換えることで、問題を解決することができた。

設問1

図1のE-R図中のacに入れる適切なエンティティ間の関連及び属性名を答え、E-R図を完成させよ。

解説

ER-図の問題です。

aには「販売時単価」が入ります!

「表1 各エンティティの概要」より、販売詳細エンティティを見ると

「顧客に販売した商品の数量や販売時単価を記録」とあります

「販売した商品の数量」は販売数量であり、E-R図にあります。

「販売時単価」がE-R図にないので、aには「販売時単価」が入ります!


bは「←」となります!

例:売上ファクトエンティティの外部キー(主キーでもあるので実線ですが)「商品ID」は商品エンティティの主キー商品IDを参照しています。このとき、売上ファックト(多)←商品(1)となります。

このことを踏まえて商品エンティティと商品分類エンティティをみます。

商品エンティティの「商品分類ID」は外部キーであり、商品分類エンティティの「商品分類ID」主キーを参照しています。

従って商品(多)←商品分類(1)となります!

従って、E-R図でbは「←」となります!


cは「←」となります!

同様に販売詳細エンティティと商品エンティティをみます。

販売詳細エンティティの「商品ID」外部キーであり、商品エンティティの「商品ID」主キーを参照しています。

従って販売詳細(多)←商品(1)となります!

従って、E-R図でcは「←」となります!

平成28年度春 応用情報技術者試験 午後 目次


タグ: ,

PR広告

フェイスブックコメント

難解な応用情報技術者試験午前で80点中48点を取る優しい方法

平成28年度春 応用情報技術者試験 午後 テキスト・動画解説

平成28年度春 応用情報技術者試験 午後 動画解説再生リスト

平成28年度春 応用情報技術者試験 午前 テキスト・動画解説

平成28年度秋 応用情報技術者試験 午前 動画解説再生リスト

平成27年度秋 応用情報技術者試験 午前 テキスト・動画解説

平成27年度秋 応用情報技術者試験 午前 動画解説再生リスト

平成27年度春 応用情報技術者試験 午後 テキスト・動画解説

平成27年度春 応用情報技術者試験 午後 動画解説再生リスト

平成27年度春 応用情報技術者試験 午前 テキスト・動画解説

平成27年度春 応用情報技術者試験 午前 動画解説再生リスト

平成26年度秋 応用情報技術者試験 午前 テキスト・動画解説

平成26年度秋 応用情報技術者試験 午前 動画解説再生リスト

平成26年度春 応用情報技術者試験 午前 テキスト・動画解説

平成26年度春 応用情報技術者試験 午前 動画解説再生リスト