データベーススペシャリスト 2020年 午後1 問03

　A社は、家庭日用品、DIY用品などを販売するホームセンタを、全国の主要都市に展開している。 A社では、RDBMS の機能を用いた販売情報分析システムを運用しており,Fさんがテーブルの設計を見直すことになった。 　

〔業務の概要〕 (1) A社の営業本部は、全国を 10 地域に分けて販売情報を分析している。 (2) A社の店舗は、全部で300店あり、店舗が一つもない地域はない。 (3) 分析対象の商品は 100,000点あり、商品分類によって分類される。 商品には、時期と地域によってよく売れるものもあれば、全く売れないものもある。 (4) 商品分類には10個の大分類と200個の小分類がある。 大分類が家庭日用品ならば、小分類の一つは鍋である。 小分類が一つもない大分類、商品が一つもない小分類はない。商品は、小分類に分類後、その分類が変更されることはない。 (5) 会員には個人会員と法人会員があり、会員地域コードが設定される。 法人会員には、担当する社員が登録後に1名決められる。 分析対象は 10,000 会員である。 　

〔見直し前の主なテーブル〕 　見直し前の主なテーブル構造を図1に、主な列の意味 制約を表1に示す。

〔見直しの方針〕

1．テーブルの統合 　これまで個人会員及び法人会員に関する情報をそれぞれ別テーブルに記録していたが、販売情報を分析する SQL文を簡素にするため、次のように統合する。 　・“個人会員”、“法人会員” テーブルを “会員” テーブルに統合する。 　・“個人売上”、“法人売上” テーブルを “売上” テーブルに統合する。 　なお、会員番号の付与方法は変えないものとし、また、統合に伴うテーブルの定義 (列名、データ型、制約など) の変更は必要最小限とする。

2．サマリテーブルの作成 　これまで分析用 SQL 文は、図1中のテーブルを直接アクセスしていたが、処理時間を改善するため、“売上”テーブルを集計したサマリテーブルを作成する。 　

〔テーブルの統合 〕 “個人会員”、“法人会員” テーブルに定義されていた制約は、それぞれ表 2,3のとおりであった。

　見直し後の“会員”、“売上” テーブルのテーブル構造を、図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に限定する。

　 ↩設問2(1) ↩設問2(2)

〔サマリテーブルの作成〕 　Fさんが、処理時間の改善を要望された分析用 SQL 文の目的を調べ、最大結果行数を見積もった結果を、表 6 に示す。 また、表6中の分析のうち、分析 B2 用 SQL文の構文を、図4に示す。