データベーススペシャリスト午後I 事例記述令和2年 10月
午後I 問3
問3 データウェアハウスに関するデータベース設計・実装
テーマ：データウェアハウスにおけるテーブル統合・SQL・サマリテーブル設計
左右2分割表示：左側の問題文をスクロールしながら右側の設問に解答できます
長文事例本文
図表クリックで拡大問3 データウェアハウスに関する次の記述を読んで，設問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のとおりであった。 表2 “個人会員”テーブルの制約の定義（未完成） | 参照制約 | FOREIGN KEY 会員地域コード REFERENCES a ON DELETE RESTRICT
FOREIGN KEY 年代 REFERENCES 年代(年代) ON DELETE RESTRICT | | 検査制約 | CHECK ( 性別 IN ( 'M', 'F' ) ) | 表3 “法人会員”テーブルの制約の定義（未完成） | 参照制約 | FOREIGN KEY 会員地域コード REFERENCES a ON DELETE RESTRICT
FOREIGN KEY 担当社員ID REFERENCES b ON DELETE SET NULL | 見直し後の“会員”，“売上”テーブルのテーブル構造を，図2に示す。 Fさんが調べたところ，既存の会員番号をそのまま移行したのでは不都合が起きることが分かったので，“会員”テーブルに会員区分を追加し，個人会員には'A'の値を，法人会員には'B'の値をそれぞれ設定することにした。
拡大
Fさんは，次の規則に基づいてテーブル定義表を作成し，テーブルを定義した。
(1) データ型欄には，データ型を記入する。
(2) NOT NULL欄には，NOT NULL制約を設定する場合にYを記入し，そうでなければNを記入する。
(3) 格納長欄には，RDBMSの仕様に従って格納長を記入する。
(4) 索引の種類と構成列欄には，作成する索引を記入する。
・索引の種類には，P（主キー索引），U（ユニーク索引），NU（非ユニーク索引）のいずれかを記入し，各索引の構成列には構成列の順番に1からの連番を記入する。
・制約欄には，参照制約，検査制約を，SQLの構文で記入する。
Fさんが作成した見直し後の“会員”テーブルのテーブル定義表を，表4に示す。
拡大
〔見直し後の販売情報の分析〕
販売情報の分析では，例えば，販売実績が非常に少なかったケースを調べる目的で，次のような分析（分析B1）を行っている。テーブルの設計を見直した後の分析B1用SQL文の構文を図3に，実行結果を表5に示す。
分析B1 2020年3月の店舗コード別商品コード別売上額を調べる。ただし，店舗コードはM1，M2及びM3に，商品コードはP1及びP2に限定する。
拡大
表5 分析B1用SQL文の実行結果
| 店舗コード | 商品コード | 売上額 |
| :--- | :--- | :--- |
| M1 | P1 | 10,000 |
| M1 | P2 | 10,000 |
| M2 | P1 | 9,000 |
| M3 | なし | 0 |
〔サマリテーブルの作成〕
Fさんが，処理時間の改善を要望された分析用SQL文の目的を調べ，最大結果行数を見積もった結果を，表6に示す。また，表6中の分析のうち，分析B2用SQL文の構文を，図4に示す。
表6 処理時間の改善を要望された分析用SQL文を調べた結果（未完成）
| 分析名 | 分析用SQL文の目的 | 最大結果行数 |
| :--- | :--- | :--- |
| B2 | 2019年の四半期名別会員区分別会員地域コード別売上額 | 80 |
| B3 | 2020年3月と2019年3月の店舗地域コード別大分類コード別売上額 | イ |
| B4 | 2019年の月別店舗コード別大分類コード別売上額 | ロ |
| B5 | 個人会員について2020年3月の店舗コード別性別年代別売上額 | ハ |
| B6 | 過去5年間の年月別小分類コード別売上額 | 12,000 |
拡大
拡大図1 見直し前の主なテーブル構造（一部省略）
20歳未満：1，20歳代：2，30歳代：3，40歳代：4，50歳代：5，60歳代：6，70歳以上：7 | | 販売額，クーポン額 | 会員がレジで受け取るレシートに商品ごとに印字された金額。販売額は商品の店頭価格。クーポン額は，会員が広告チラシ又はスマートフォンでクーポンを提示したときに適用した金額で，適用しなかった場合，NULLが設定される。なお，売上額は，会員が支払った金額（販売額からクーポン額を差し引いた金額）である。 | 〔見直しの方針〕 1. テーブルの統合 これまで個人会員及び法人会員に関する情報をそれぞれ別テーブルに記録していたが，販売情報を分析するSQL文を簡素にするため，次のように統合する。 ・“個人会員”，“法人会員”テーブルを“会員”テーブルに統合する。 ・“個人売上”，“法人売上”テーブルを“売上”テーブルに統合する。 なお，会員番号の付与方法は変えないものとし，また，統合に伴うテーブルの定義（列名，データ型，制約など）の変更は必要最小限とする。 2. サマリテーブルの作成 これまで分析用SQL文は，図1中のテーブルを直接アクセスしていたが，処理時間を改善するため，“売上”テーブルを集計したサマリテーブルを作成する。 〔テーブルの統合〕 “個人会員”，“法人会員”テーブルに定義されていた制約は，それぞれ表2，3のとおりであった。 表2 “個人会員”テーブルの制約の定義（未完成） | 参照制約 | FOREIGN KEY 会員地域コード REFERENCES a ON DELETE RESTRICT
FOREIGN KEY 年代 REFERENCES 年代(年代) ON DELETE RESTRICT | | 検査制約 | CHECK ( 性別 IN ( 'M', 'F' ) ) | 表3 “法人会員”テーブルの制約の定義（未完成） | 参照制約 | FOREIGN KEY 会員地域コード REFERENCES a ON DELETE RESTRICT
FOREIGN KEY 担当社員ID REFERENCES b ON DELETE SET NULL | 見直し後の“会員”，“売上”テーブルのテーブル構造を，図2に示す。 Fさんが調べたところ，既存の会員番号をそのまま移行したのでは不都合が起きることが分かったので，“会員”テーブルに会員区分を追加し，個人会員には'A'の値を，法人会員には'B'の値をそれぞれ設定することにした。
拡大図2 見直し後の“会員”，“売上”テーブルのテーブル構造（一部省略）
拡大表4 見直し後の“会員”テーブルのテーブル定義表（未完成・一部省略）
拡大図3 分析B1用SQL文の構文（未完成）
拡大図4 分析B2用SQL文の構文
設問一覧 ＆ 解答・演習
まずは解答を作成してみましょう。「解答例を見る」をクリックするとIPA公式解答例と採点基準が開きます。
設問1 (1)記号・選択式
表2，3中の a，b に入れる適切な字句を答えよ。
※直接入力してください
設問1 (2)記述問題
制限字数：25 字以内
会員区分を追加することなく既存の会員番号をそのまま移行すれば，どのような場合にどのような不都合が起きると考えられるか，それぞれ25字以内で述べよ。
0 / 25 文字
設問1 (3)記号・選択式
表4に示したNOT NULL欄中の c ～ g，及び制約欄中の h ～ m に入れる適切な字句を答えよ。
※直接入力してください
設問2 (1)記号・選択式
図3中の [ あ ] ～ [ く ] に入れる適切な字句を，解答群の中から選び，記号で答えよ。
※記号ボタンをクリックするか、直接入力してください
設問2 (2)記述問題
制限字数：25 字以内
COALESCE関数は，1番目の引数がNULLでないときはその値，NULLのときは2番目の引数を返す関数である。図3中の選択リスト中の販売額又はクーポン額がNULLになるのはどのような場合か，本文中の用語を用いて，それぞれ25字以内で述べよ。
0 / 25 文字
設問3 (1)記号・選択式
表6中の イ ～ ハ に入れる適切な字句を答えよ。
なお，結果行数を見積もるとき，分析対象の期間中，現在の店舗コード，店舗地域コード，小分類コード，大分類コード，商品コード，会員番号及び会員地域コードの数に変動がなかったと仮定すること。
なお，結果行数を見積もるとき，分析対象の期間中，現在の店舗コード，店舗地域コード，小分類コード，大分類コード，商品コード，会員番号及び会員地域コードの数に変動がなかったと仮定すること。
※直接入力してください
設問3 (2)記号・選択式
図1中の“個人売上”，“法人売上”テーブル以外のテーブルを総称して，次元テーブルという。表6中の全ての分析について，次元テーブルと一つのサマリテーブルだけから売上額を集計できるようにしたい。Fさんは，サマリテーブルの候補のうち，最小列数かつ最小行数となるサマリテーブル“S”のテーブル構造を，次のように設計した。A に入れる複数の列名を答えよ。ただし，列名は次元テーブルから選ぶこと。
S (年, 月, 店舗コード, 会員区分, 会員地域コード, A, 売上額)
S (年, 月, 店舗コード, 会員区分, 会員地域コード, A, 売上額)
※直接入力してください
午後I長文記述の得点力を引き上げる定番対策書Amazon・楽天 公式提携
PR
IPA 厳選対策書徹底攻略 データベーススペシャリスト教科書 令和8年度株式会社わくわくスタディワールド 瀬戸 美月 著 / インプレス
DB徹底攻略株式会社わくわくスタディワールド 瀬戸 美月 著 / インプレス
徹底攻略 データベーススペシャリスト教科書 令和8年度
💡 推薦ポイント:午後I・午後IIの概念データモデル穴埋めや排他制御・性能設計の記述問題で確実に合格点を勝ち取る実践テクニックが満載です。
¥3,278（税込）
※本リンク先はプロモーション（アフィリエイト広告）が含まれています
【出題趣旨】IPA公式発表
【採点講評】IPA公式発表
本問に関連するデータベーススペシャリスト重要用語解説
本番形式・制限時間付きで演習する
令和2年 10月 午後I（3問中2問選択・90分）を実戦演習モードで通し解答し、総合採点を受けられます。