データウェアハウスに関する次の記述を読んで，設問1〜3に答えよ。
A社は，家庭日用品，DIY用品などを販売するホームセンタを，全国の主要都市に展開している。A社では，RDBMSの機能を用いた販売情報分析システムを運用しており，Fさんがテーブルの設計を見直すことになった。
〔業務の概要〕
（1）A社の営業本部は，全国を10地域に分けて販売情報を分析している。
（2）A社の店舗は，全部で300店あり，店舗が一つもない地域はない。
（3）分析対象の商品は100,000点あり，商品分類によって分類される。商品には，時期と地域によってよく売れるものもあれば，全く売れないものもある。
（4）商品分類には10個の大分類と200個の小分類がある。大分類が家庭日用品ならば，小分類の一つは鍋である。小分類が一つもない大分類，商品が一つもない小分類はない。商品は，小分類に分類後，その分類が変更されることはない。
（5）会員には個人会員と法人会員があり，会員地域コードが設定される。法人会員には，担当する社員が登録後に1名決められる。分析対象は10,000会員である。
〔見直し前の主なテーブル〕
見直し前の主なテーブル構造を図1に，主な列の意味制約を表1に示す。
図1 見直し前の主なテーブル構造（一部省略）
| 列名 | 列の意味 |
| 年，月，日 | 3列で日付を表す。それぞれ'YYYY'形式，'MM'形式，'DD'形式のCHAR型。 |
| 個人会員番号， 法人会員番号 |
個人会員又は法人会員をそれぞれ識別する8桁の番号。 ただし，個人会員番号及び法人会員番号の付与は，互いに無関係でそれぞれ独自に行っている。 |
| 性別 | 男性:'M'，女性:'F' |
| 年代， 年齢下限， 年齡上限 |
年代は，個人会員の年齢の層を識別する数値で，年齢の下限値と上限値によって区別する。年代には，次のいずれかが設定される。 20歳未満：1，20歳代：2，30歳代：3，40歳代：4，50歳代：5，60歳代：6，70歳以上:7 |
| 販売額， クーポン額 |
会員がレジで受け取るレシートに商品ごとに印字された金額。販売額は商品の店頭価格。 クーポン額は，会員が広告チラシ又はスマートフォンでクーポンを提示したときに適用した金額で，適用しなかった場合，NULLが設定される。 なお，売上額は，会員が支払った金額（販売額からクーポン額を差し引いた金額）である。 |
〔見直しの方針〕
1.テーブルの統合
これまで個人会員及び法人会員に関する情報をそれぞれ別テーブルに記録していたが，販売情報を分析するSQL文を簡素にするため，次のように統合する。
・“個人会員”，“法人会員”テーブルを“会員”テーブルに統合する。
・“個人売上”，“法人売上”テーブルを“売上”テーブルに統合する。
なお，会員番号の付与方法は変えないものとし，また，統合に伴うテーブルの定義（列名，データ型，制約など）の変更は必要最小限とする。
2.サマリテーブルの作成
これまで分析用SQL文は，図1中のテーブルを直接アクセスしていたが，処理時間を改善するため，“売上”テーブルを集計したサマリテーブルを作成する。
〔テーブルの統合〕
“個人会員”，“法人会員”テーブルに定義されていた制約は，それぞれ表2，3のとおりであった。
| 制 約 |
参照制約 | FOREIGN KEY 会員地域コード REFERENCES a ON DELETE RESTRICT FOREIGN KEY 年代 REFERENCES 年代（年代） ON DELETE RESTRICT |
| 検査制約 | CHECK（性別IN（'M'，'F'）） |
| 制 約 |
参照制約 | FOREIGN KEY 会員地域 コード REFERENCES a ON DELETE RESTRICT FOREIGN KEY 担当社員ID REFERENCES b ON DELETE SET NULL |
見直し後の“会員”，“売上”テーブルのテーブル構造を，図2に示す。
Fさんが調べたところ，既存の会員番号をそのまま移行したのでは不都合が起きることが分かったので，“会員”テーブルに会員区分を追加し，個人会員には'A'の値を，法人会員には'B'の値をそれぞれ設定することにした。
図2 見直し後の“会員”，“売上”テーブルのテーブル構造（一部省略）
Fさんは，次の規則に基づいてテーブル定義表を作成し，テーブルを定義した。
（1）データ型欄には，データ型を記入する。
（2）NOT NULL欄には，NOT NULL制約を設定する場合にYを記入し，そうでなければNを記入する。
（3）格納長欄には，RDBMSの仕様に従って格納長を記入する。
（4）索引の種類と構成列欄には，作成する索引を記入する。
・索引の種類には，P（主キー索引），U（ユニーク索引），NU（非ユニーク索引）のいずれかを記入し，各索引の構成列には構成列の順番に1からの連番を記入する。
・制約欄には，参照制約，検査制約を，SQLの構文で記入する。
Fさんが作成した見直し後の“会員”テーブルのテーブル定義表を表4に示す。
表4 分析B2用SQL文の構文
〔見直し後の販売情報の分析〕
販売情報の分析では，例えば，販売実績が非常に少なかったケースを調べる目的で，次のような分析（分析B1）を行っている。テーブルの設計を見直した後の分析B1用SQL文の構文を図3に，実行結果を表5に示す。
分析B1 2020年3月の店舗コード別商品コード別売上額を調べる。ただし，店舗コードはM1，M2及びM3に，商品コードはP1及びP2に限定する。
図3 分析B1用SQL文の構文（未完成）
| 店舗コード | 商品コード | 売上額 |
| M1 | P1 | 10,000 |
| M1 | P2 | 10,000 |
| M2 | P1 | 9,000 |
| M3 | なし | 0 |
〔サマリテーブルの作成〕
Fさんが，処理時間の改善を要望された分析用SQL文の目的を調べ，最大結果行数を見積もった結果を表6に示す。また，表6中の分析のうち，分析B2用SQL文の構文を，図4に示す。
| 分析名 | 分析用SQL文の目的 | 最大結果行数 |
| B2 | 2019年の四半期名別会員区分別会員地域コード別売上額 | 80 |
| B3 | 2020年3月と2019年3月の店舗地域コード別大分類コード別売上額 | イ |
| B4 | 2019年の月別店舗コード別大分類コード別売上額 | ロ |
| B5 | 個人会員について2020年3月の店舗コード別性別年代別売上額 | ハ |
| B6 | 過去5年間の年月別小分類コード別売上額 | 12,000 |
図4 分析B2用SQL文の構文
設問1 〔テーブルの統合〕について，（1）〜（3）に答えよ。
（1）表2，3中の a ， b に入れる適切な字句を答えよ。
解答・解説
解答例
a：地域(地域コード)
b：社員(社員 ID)
解説
ー
（2）会員区分を追加することなく既存の会員番号をそのまま移行すれば，どのような場合にどのような不都合が起きると考えられるか，それぞれ25字以内で述べよ。
解答・解説
解答例
場合：個人会員番号と法人会員番号が重複する場合
不都合：会員番号の登録が主キー重複違反で失敗する
解説
ー
（3）表4に示したNOTNULL欄中の c 〜 g 及び制約欄中の h 〜 m に入れる適切な字句を答えよ。
解答・解説
解答例
c：Y
d：Y
e：N
f：N
g：N
h：RESTRICT
i：SET NULL
j：性別 IN (‘M’,‘F’)
k：年代 IS NOT NULL
l：担当社員 ID IS NULL
m：年代 IS NULL
解説
ー
設問2 〔見直し後の販売情報の分析〕について，（1），（2）に答えよ。
（1）図3中の あ 〜 く に入れる適切な字句を，解答群の中から選び，記号で答えよ。
①売上 U
②店舗 M
③商品 S
④U.店舗コード
⑤U.商品コード
⑥M.店舗コード
⑦S.商品コード
⑧U.年='2000'
⑨U.月='03'
⑩U.商品コード IN('P1','P2')
⑪U.店舗コード IN('M1','M2','M3')
⑫M.店舗コード IN('M1','M2','M3')
解答・解説
解答例
あ：②
い：①
う：⑥
え：④
お：⑧
か：⑨
き：⑩
く：⑫
解説
ー
（2）COALESCE関数は，1番目の引数がNULLでないときはその値，NULLのときは2番目の引数を返す関数である。図3中の選択リスト中の販売額又はクーポン額がNULLになるのはどのような場合か，本文中の用語を用いて，それぞれ25字以内で述べよ。
解答・解説
解答例
販売額：分析対象の商品が全く売れなかった店舗の場合
クーポン額：分析対象の商品にクーポンを適用しなかった場合
解説
ー
設問3 〔サマリテーブルの作成〕について，1），（2）に答えよ。
（1）表6中の イ 〜 ハ に入れる適切な字句を答えよ。
なお，結果行数を見積もるとき，分析対象の期間中，現在の店舗コード，店舗地域コード，小分類コード，大分類コード，商品コード，会員番号及び会員地域コードの数に変動がなかったと仮定すること。
解答・解説
解答例
イ：200
ロ：36,000
ハ：4,200
解説
ー
（2）図1中の“個人売上”，“法人売上”テーブル以外のテーブルを総称して，次元テーブルという。表6中の全ての分析について，次元テーブルと一つのサマリテーブルだけから売上額を集計できるようにしたい。Fさんは，サマリテーブルの候補のうち，最小列数かつ最小行数となるサマリテーブル“S”のテーブル構造を，次のように設計した。 A に入れる複数の列名を答えよ。ただし，列名は次元テーブルから選ぶこと。
S（年，月，店舗コード，会員区分，会員地域コード， A ，売上額）
解答・解説
解答例
性別，年代，小分類コード
解説
ー
IPA公開情報
出題趣旨
データウェアハウスは，販売情報の分析を行うために構築され，広く利用されている。本問では，RDBMS に構築したデータウェアハウスを題材に，次元テーブルを統合する場合の考慮点，分析に利用する SQL 構文の基本的な特徴と考慮点，及び適切なデータマートの設計に必要となる性能見積りを理解しているかを問う。
採点講評
問 3 では，RDBMS の機能を用いた販売情報分析システムを題材に，データウェアハウスについて出題し た。全体として正答率は高かった。
設問 1(3)では，見直し後の“会員”テーブルの年代は外部キーであり，取り得る列値が限定されているにも かかわらず，IN 述語又は BETWEEN 述語を検査制約に追加する解答が散見された。一方，性別は外部キーで はないので，取り得る列値を限定する IN 述語を検査制約に定義する必要がある。参照制約の外部キーであるかどうかによって検査制約の定義に違いが出ることを理解してほしい。
設問 2(1)では，ON 句と WHERE 句の述語を逆にした解答が散見された。結合前のテーブルに適用すべき述語を ON 句に，結合後の結果行に適用すべき述語を WHERE 句に指定することを理解してほしい。 設問 3(2)では，正答に含まれる小分類コードを答えているにもかかわらず，大分類コードを追加した誤答が散見された。“小分類”テーブルの外部キーが大分類コードであることに気が付けば，正答を得られたはずである。データマートの目的の一つは分析性能の改善である。その改善効果を設計局面で性能見積りによって適切に評価できる能力を身に付けてほしい。
