令和2年度10月 データベーススペシャリスト 午後Ⅰ 問3

統合設計は前提条件との対応で考える

テーブルを統合したり集計用の表を用意したりする場面では、設計の良し悪しは記述された業務上の前提条件に照らして初めて判断できます。自分の一般知識で補わず、問題に示された条件を根拠として一つずつ対応付ける姿勢で読み進めましょう。

この問題で問われていること

確認すべき観点は一つ、「設計判断の根拠が問題中に示された前提条件のどこにあるか」です。統合や集計の手段そのものより、その手段を選ぶ理由となる条件がどれかを先に特定し、そのうえで各判断が条件と矛盾しないかを照らし合わせてください。

視点

データウェアハウスでは、複数の業務システムに由来するデータを分析目的で一か所に集めます。その際の設計上の選択には何らかの理由があり、その理由は業務ルールやデータの性質として問題側に与えられるのが一般的です。まず条件を拾い出し、次にそれがどの判断に結びつくかという順で頭の中に対応関係を作ると、根拠のない推測に頼らずに検討できます。

確認ポイント

• 問題中に示された業務上の前提条件やデータの性質を、判断の前にすべて拾い出したか
• 検討している各判断について、それを支える前提条件がどこに書かれているかを指し示せるか
• 問題に書かれていない事情を自分の経験や一般論で補って判断していないか

解説

ホームセンタの販売情報分析システムで、個人と法人に分かれた会員表・売上表を統合し、サマリテーブルを作る事例です。統合時の主キーの一意性、会員区分ごとのNOT NULLと検査制約、店舗を基準にした外結合の書き方、集計行数の見積り、最小構成のサマリテーブル設計が問われています。公式解答例では、会員区分を主キーに加える理由、ON句とWHERE句の使い分け、次元テーブルからたどれる列はサマリに持たないことが要点になっています。

問題の全体像

A社は、RDBMSで販売情報分析システムを運用しています。見直しの内容は次の二つです。一つ目は、分析用SQLを簡素にするため、個人会員・法人会員の表を“会員”に、個人売上・法人売上の表を“売上”に統合することです。二つ目は、処理時間を改善するため、売上を集計したサマリテーブルを作ることです。条件として、会員番号の付与方法は変えず、表定義の変更は必要最小限とされています。出題趣旨では、次元テーブルを統合するときの考慮点、分析用SQL構文の基本的な特徴と考慮点、データマート設計に必要な性能見積りの理解を問うとされています。採点講評では、全体として正答率は高かったと述べられています。

設問1(1)

図1では、個人会員・法人会員の会員地域コードと、法人会員の担当社員IDが外部キー（点線の下線）になっています。会員地域コードは地域を表すので、参照先は“地域”テーブルの主キーである地域コードです。担当社員IDの参照先は“社員”テーブルの社員IDです。表2の「年代(年代)」と同じく「表名(列名)」の形で書くので、公式解答例では、aは地域(地域コード)、bは社員(社員ID)です。

設問1(2)（場合）

着目するのは表1の記述です。個人会員番号と法人会員番号は、どちらも8桁で、互いに無関係にそれぞれ独自に付与されています。このため、同じ番号の個人会員と法人会員が存在し得ます。公式解答例では、不都合が起きる場合を「個人会員番号と法人会員番号が重複する場合」としています。答案では「両者の番号が同じ値になる」ことを25字以内で示します。

設問1(2)（不都合）

会員区分を加えずに統合すると、“会員”テーブルの主キーは会員番号だけになります。そこに既存の個人会員と法人会員の番号をそのまま登録すると、重複する番号で主キーの一意性に違反し、登録できません。公式解答例では「会員番号の登録が主キー重複違反で失敗する」としています。このため、Fさんは会員区分（個人は‘A’、法人は‘B’）を追加し、図2では{会員区分、 会員番号}を主キーにしています。答案では、主キー重複で登録に失敗することを押さえます。

設問1(3)

NOT NULL欄は、個人会員と法人会員の両方で必ず値が入るかどうかで判断します。cの会員区分は主キーの構成列なのでY、dの会員地域コードは両方の会員に設定されるのでYです。eの性別とfの年代は個人会員だけの属性なので、法人会員ではNULLになります。このためe、fはNです。gの担当社員IDは法人会員だけの属性で、しかも登録後に決まるのでNです。参照制約は、変更を最小限にする方針に従い、表2・表3の定義を引き継ぎます。hはRESTRICT、iはSET NULLです。検査制約では、会員区分‘A’の行に性別 IN (‘M’、‘F’)、年代 IS NOT NULL、担当社員ID IS NULLを課します（j〜lは順不同）。会員区分‘B’の行には、性別 IS NULLに加えて年代 IS NULLを課すので、mは年代 IS NULLです。担当社員IDは登録後に決まるので、法人側では条件を付けません。採点講評によると、外部キーの年代にINやBETWEENを加えた誤答が散見されました。年代は参照制約で取り得る値が限られていますが、外部キーではない性別はIN述語で値を限定する必要があります。この違いを押さえてください。

設問2(1)

表5では、2020年3月に対象商品の売上がないM3も「なし」「0」として出力されています。したがって、店舗を基準にして売上を外結合します。公式解答例では、あは②店舗 M、いは①売上 Uです。結合条件は⑥M.店舗コード＝④U.店舗コードです（う・えは順不同）。売上側の絞込みである⑧U.年＝'2020'、⑨U.月＝'03'、⑩U.商品コード IN（'P1'、'P2'）はON句に書きます（お〜きは順不同）。これらをWHERE句に書くと、売上がなくNULLになったM3の行が除かれてしまうからです。一方、店舗の絞込みは結合後の行に対して行うので、WHERE句にくの⑫M.店舗コード IN（'M1'、'M2'、'M3'）を書きます。採点講評によると、ON句とWHERE句の述語を逆にした誤答が散見されました。結合前の表に適用する述語はON句に、結合後の結果行に適用する述語はWHERE句に書きます。

設問2(2)（販売額）

外結合では、左側の店舗に一致する売上行がないと、売上側の列はすべてNULLになります。表5のM3の行がこれに当たります。公式解答例では、販売額がNULLになるのは「分析対象の商品が全く売れなかった店舗の場合」としています。業務の概要(3)の「全く売れない」という本文中の用語を使い、対象期間・対象商品の売上がない店舗であることを25字以内で示します。

設問2(2)（クーポン額）

表1によると、クーポン額には、会員がクーポンを提示して適用した金額が入り、適用しなかった場合はNULLが設定されます。公式解答例では「分析対象の商品にクーポンを適用しなかった場合」としています。売上行があってもクーポン額はNULLになり得るので、COALESCEで0に置き換えてから販売額との差を合計しています。答案では「クーポンを適用しなかった」ことを押さえます。

設問3(1)

最大結果行数は、集計キーごとの値の数を掛け合わせて求めます。B2の検算は、四半期4×会員区分2×会員地域10＝80です。B6の検算は、60か月×小分類200＝12、000です。同じ考え方で、イ（B3）は2か月×店舗地域10×大分類10＝200、ロ（B4）は12か月×店舗300×大分類10＝36、000、ハ（B5）は店舗300×性別2×年代7＝4、200となります。店舗が一つもない地域はないので、店舗地域は10とします。公式解答例では、イは200、ロは36、000、ハは4、200です。

設問3(2)

B2〜B6の集計キーを、次元テーブルとの結合で導けるように並べます。四半期名は年・月からカレンダで、店舗地域コードは店舗コードから店舗で、大分類コードは小分類コードから小分類でたどれます。B5には性別と年代が必要で、B6には小分類コードが必要です。行数を最小にするため、会員番号や商品コードのような細かい列は持ちません。公式解答例では、Aは「性別，年代，小分類コード」です。採点講評によると、大分類コードを追加した誤答が散見されました。“小分類”テーブルが外部キーとして大分類コードを持つので、大分類コードは不要です。

学習のポイント

独自に採番された識別子を統合するときの主キー設計、サブタイプごとのNULL制約と検査制約の書き方、外結合で条件をON句とWHERE句のどちらに書くか、次元の値の数から集計行数を見積もる方法を重点的に練習してください。