データベーススペシャリスト 2020年 午後1 問03
データウェアハウスに関する次の記述を読んで、設問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．テーブルの統合
これまで個人会員及び法人会員に関する情報をそれぞれ別テーブルに記録していたが、販売情報を分析する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に限定する。
〔サマリテーブルの作成〕
Fさんが、処理時間の改善を要望された分析用SQL文の目的を調べ、最大結果行数を見積もった結果を、表6に示す。 また、表6中の分析のうち、分析B2用SQL文の構文を、図4に示す。
設問1：〔テーブルの統合〕について、(1)〜(3)に答えよ。
問題文を見る（1）表2, 3中の(a)、(b)に入れる適切な字句を答えよ。
模範解答
a：地域(地域コード)
b：社員(社員ID)
解説
解答の論理構成
- 外部キーの基本
- 外部キーは “子表の列 → 親表の主キー” という関係で定義されます。
- “会員地域コード” の参照先を特定
- 【問題文】には “地域（地域コード、地域名、…）” という表が記載されています。
- “会員地域コード” は地域を示す列なので、親表は 地域、主キーは 地域コード。
- よって (a) = 地域(地域コード)。
- “担当社員ID” の参照先を特定
- 【問題文】に “社員（社員ID, 社員名、店舗コード、…）” が示されており、社員IDが主キーです。
- “担当社員ID” はここを参照するのが自然で、削除時は “ON DELETE SET NULL” として表中に示されています。
- よって (b) = 社員(社員ID)。
誤りやすいポイント
- “会員地域コード” を “店舗地域コード” と取り違え、店舗 表を参照先にしてしまう。
- “担当社員ID” を “店舗” 表の列と誤解し、店舗(店舗コード) と書いてしまう。
- 外部キー句に列名だけ書き、表名(列名) の形式を忘れる。
FAQ
Q: “ON DELETE RESTRICT” と “ON DELETE SET NULL” の違いは?
A: “RESTRICT” は親行が削除されるときに子行が存在すれば削除を拒否します。“SET NULL” は親行が削除されたとき子行の外部キー列をNULLに更新します。
A: “RESTRICT” は親行が削除されるときに子行が存在すれば削除を拒否します。“SET NULL” は親行が削除されたとき子行の外部キー列をNULLに更新します。
Q: “地域” と “店舗” の使い分けが分かりません。
A: “地域” は全国を “10地域” に区分した管理単位、店舗は実際の “300店” の実店舗です。会員は地域単位で管理されるため “会員地域コード” の参照先は “地域” 表になります。
A: “地域” は全国を “10地域” に区分した管理単位、店舗は実際の “300店” の実店舗です。会員は地域単位で管理されるため “会員地域コード” の参照先は “地域” 表になります。
Q: “社員ID” を参照しているのは法人会員だけですか?
A: はい。【問題文】に “法人会員には、担当する社員が登録後に1名決められる” とあり、この担当者を “担当社員ID” で表しています。
A: はい。【問題文】に “法人会員には、担当する社員が登録後に1名決められる” とあり、この担当者を “担当社員ID” で表しています。
関連キーワード: 外部キー、参照制約、正規化、データ整合性
設問1：〔テーブルの統合〕について、(1)〜(3)に答えよ。
問題文を見る（2）会員区分を追加することなく既存の会員番号をそのまま移行すれば、どのような場合にどのような不都合が起きると考えられるか、それぞれ25字以内で述べよ。
模範解答
場合：個人会員番号と法人会員番号が重複する場合
不都合：会員番号の登録が主キー重複違反で失敗する
解説
解答の論理構成
- 前提確認
- 統合前は「個人会員」と「法人会員」が別テーブル。
- 付番は「互いに無関係でそれぞれ独自に行っている。」
- 統合後の設計意図
- 「会員番号」を主キーにしたいが、区分列を追加しない案を想定。
- 発生し得る事象
- 同じ "CHAR(8)" が個人と法人の両方に存在すると、「個人会員番号と法人会員番号が重複する場合」が生じる。
- 主キーの性質
- 主キーは「重複不可」。よって INSERT 時に「主キー重複違反」が起き、移行処理や新規登録が失敗する。
- したがって
- 区分列を追加せずに移行すると「会員番号の登録が主キー重複違反で失敗する」不都合が発生する、という結論になる。
誤りやすいポイント
- 「番号は8桁で十分だから重複しない」と思い込み、独立付番という条件を見落とす。
- 不都合を「検索しづらい」などと曖昧に書き、主キー制約違反という具体的な障害を挙げない。
- 会員区分を追加すれば解決する前提を忘れ、解答に区分列自体を書いてしまう。
FAQ
Q: 会員区分を付ければ本当に重複問題は解決しますか？
A: はい。「会員区分＋会員番号」を複合主キーにすることで、同じ8桁番号が存在しても区分が異なれば一意になります。
A: はい。「会員区分＋会員番号」を複合主キーにすることで、同じ8桁番号が存在しても区分が異なれば一意になります。
Q: 主キーではなくユニーク索引にすれば良いのでは？
A: いずれにしても一意性制約を持つ列集合に重複値が入れば違反になります。区分を含めない限り問題は残ります。
A: いずれにしても一意性制約を持つ列集合に重複値が入れば違反になります。区分を含めない限り問題は残ります。
Q: 番号体系を統合する方法は検討しないのですか？
A: 既存システムへの影響が大きく、問題文でも「会員番号の付与方法は変えない」と明記されているため、今回は区分列の追加が妥当です。
A: 既存システムへの影響が大きく、問題文でも「会員番号の付与方法は変えない」と明記されているため、今回は区分列の追加が妥当です。
関連キーワード: 主キー、一意性制約、テーブル統合、重複チェック、参照制約
設問1：〔テーブルの統合〕について、(1)〜(3)に答えよ。
問題文を見る（3）表4に示した NOT NULL欄中の(c)〜(g)及び制約欄中の(h)〜(m)に入れる適切な字句を答えよ。（j, k, lは順不同）
模範解答
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
解説
解答の論理構成
- 主キー列のNULL可否
図2で主キーは「会員区分、会員番号」。主キー列はNULLを取れないため【cはY】。会員番号も既にY表記。 - 会員地域コードのNULL可否
個人・法人とも必須で、元の二表とも FOREIGN KEY が付いていた。【dはY】。 - 性別・年代・担当社員IDのNULL可否
- 個人会員: 性別と年代必須、担当社員IDは存在してはいけない。
- 法人会員: 性別と年代は存在してはいけない、担当社員IDはNULL可。
よって列自体は NULL 許容にし、CHECK で分岐させるので【e,f,g は N】。
- 外部キーの削除時動作
個人会員→年代: 元表が「ON DELETE RESTRICT」なので【h:RESTRICT】。
法人会員→社員: 元表が「ON DELETE SET NULL」なので【i:SET NULL】。 - CHECK 制約
会員区分 = 'A'（個人）の時に満たす条件
・性別が ‘M’、’F’ のいずれか→【j】
・年代がNULLでない→【k】
・担当社員IDがNULL→【l】
会員区分 = 'B'（法人）の時は
・性別 IS NULL, 年代 IS NULL →【m】
担当社員IDには条件を設けずNULLも値有りも許可。
誤りやすいポイント
- NOT NULLにし過ぎる
個人会員に必須だからと列を NOT NULLにすると、法人会員挿入時に失敗します。 - CHECK で “IS NOT NULL” と “= NULL” を混同
SQLでは “= NULL” は偽になります。NULL可否を判定するときは IS NULL / IS NOT NULLを用います。 - ON DELETE 動作の取り違え
RESTRICTと SET NULLは真逆の振る舞い。元表の仕様を写し間違えないよう注意。
FAQ
Q: 担当社員IDを個人会員にNULLのみとするのはなぜですか？
A: 【問題文】に「法人会員には、担当する社員が登録後に1名決められる」とあり、個人会員には担当社員の概念がありません。そのため CHECK 制約で個人会員時は 担当社員ID IS NULL とします。
A: 【問題文】に「法人会員には、担当する社員が登録後に1名決められる」とあり、個人会員には担当社員の概念がありません。そのため CHECK 制約で個人会員時は 担当社員ID IS NULL とします。
Q: 性別列を NULL 禁止にして CHECK で ‘M’、’F’ を限定してはいけませんか？
A: 法人会員行では性別が存在してはいけないため、列自体を NOT NULL にすると法人会員を登録できなくなります。NULL を許容したうえで CHECK で場合分けするのが正解です。
A: 法人会員行では性別が存在してはいけないため、列自体を NOT NULL にすると法人会員を登録できなくなります。NULL を許容したうえで CHECK で場合分けするのが正解です。
Q: 外部キーにRESTRICTと SET NULLのどちらを選ぶ目安は？
A: 子表側列がNULLを許容しない場合はRESTRICT、許容する場合かつ連動削除を避けたい場合に SET NULLを使うのが一般的です。本設問では元テーブルの仕様をそのまま踏襲しています。
A: 子表側列がNULLを許容しない場合はRESTRICT、許容する場合かつ連動削除を避けたい場合に SET NULLを使うのが一般的です。本設問では元テーブルの仕様をそのまま踏襲しています。
関連キーワード: 外部キー制約、主キー、CHECK制約、NULL制約、テーブル統合
設問2：〔見直し後の販売情報の分析〕について、(1)、(2)に答えよ。
問題文を見る（1）図3中の（あ）〜（く）に入れる適切な字句を、解答群の中から選び、記号で答えよ。（う、えは順不同、お、か、きは順不同）
解答群
①売上U
②店舗M
③商品S
④U.店舗コード
⑤U.商品コード
⑥M.店舗コード
⑦S.商品コード
⑧U.年 = '2020'
⑨U.月 = '03'
⑩U.商品コード IN ('P1', 'P2')
⑪U.店舗コード IN ('M1', 'M2', 'M3')
⑫M.店舗コード IN ('M1', 'M2', 'M3')
模範解答
あ：②
い：①
う：⑥
え：④
お：⑧
か：⑨
き：⑩
く：⑫
解説
解答の論理構成
-
外部結合の方向を決める
図3はFROM （あ）
LEFT OUTER JOIN （い） ON …となっており、左側（あ）が親テーブル、右側（い）が子テーブルです。実行結果【表5】では “M3” に売上が無い場合でも1行表示しているため、売上が必ず存在するとは限らない “店舗” を左側に置く必要があります。
解答群のうち “店舗M” は ② なので（あ）＝②、残る（い）は “売上U” ① になります。 -
結合キー（う、え）
店舗表と売上表を結ぶキーは共通の “店舗コード” です。店舗表側は “M.店舗コード” ⑥、売上表側は “U.店舗コード” ④ となり、順不同なので（う）＝⑥、（え）＝④ もしくは逆でも可です。 -
絞り込み条件（お、か、き）
JOIN 句内の追加条件として
・年を “2020” に限定 → ⑧
・月を “03” に限定 → ⑨
・商品コードを “P1”、“P2” に限定 → ⑩
が必要です。（お、か、き）は順不同なので ⑧、⑨、⑩ の並びは任意です。 -
WHERE 句（く）
外部結合後に店舗を “M1”、“M2”、“M3” に限定する必要があります。JOIN 句で限定すると外部結合の意味がなくなるため、WHERE 句で “M.店舗コード IN('M1'、'M2'、'M3')” を指定します。解答群で該当するのは ⑫ なので（く）＝⑫ となります。
誤りやすいポイント
- WHERE 句に “U.店舗コード IN('M1'、'M2'、'M3')” を置くと、売上が無い “M3” が除外されてしまい 0 円行が得られません。
- “年”、“月” の条件を WHERE 句に書くと LEFT OUTER JOIN が内側結合のように振舞い欠損行が消えるため、JOIN 句側に移す必要があります。
- 商品コードの限定を “S.商品コード” ⑦ と誤選択しやすいですが、図3に商品テーブルは登場していません。
FAQ
Q: 外部結合で売上ゼロ行を作るとき、限定条件はすべて JOIN 句に書くべきですか？
A: 欠落行を保持したい側（店舗）の列に対する条件だけを WHERE 句に書き、相手側（売上）の列に対する条件は JOIN 句に書くのが原則です。
A: 欠落行を保持したい側（店舗）の列に対する条件だけを WHERE 句に書き、相手側（売上）の列に対する条件は JOIN 句に書くのが原則です。
Q: “U.商品コード IN('P1'、'P2')” を WHERE 句に置いた場合の結果は？
A: “M3” のように該当商品の売上が無い店舗が完全に除外され、実行結果に “なし” 行が出力されません。
A: “M3” のように該当商品の売上が無い店舗が完全に除外され、実行結果に “なし” 行が出力されません。
Q: “COALESCE” と “SUM” を組み合わせる理由は？
A: “COALESCE” でNULLを0に置き換えてから “SUM” することで、クーポン未使用時（NULL値）の差し引き額を正しく計算できます。
A: “COALESCE” でNULLを0に置き換えてから “SUM” することで、クーポン未使用時（NULL値）の差し引き額を正しく計算できます。
関連キーワード: OUTER JOIN, COALESCE, グループ化、WHERE と JOIN の違い、NULL 取り扱い
設問2：〔見直し後の販売情報の分析〕について、(1)、(2)に答えよ。
問題文を見る（2）COALESCE 関数は、1番目の引数がNULLでないときはその値、NULLのときは2番目の引数を返す関数である。 図3中の選択リスト中の販売額又はクーポン額がNULLになるのはどのような場合か、本文中の用語を用いて、それぞれ25字以内で述べよ。
模範解答
販売額：分析対象の商品が全く売れなかった店舗の場合
クーポン額：分析対象の商品にクーポンを適用しなかった場合
解説
解答の論理構成
- 図3は店舗マスタＭと売上Ｕを LEFT OUTER JOIN している。【図3：「LEFT OUTER JOIN」】
- 外部結合の主表側（店舗）に従表（売上）が無いと、従表列はNULLになる。したがって「販売額」「クーポン額」が共にNULLになるのは “売上が1件も無い” パターン。
- ただしクーポン額には、売上明細が存在してもNULLになるケースが定義されている。表1には「クーポン額…適用しなかった場合、NULLが設定される」とある。
- 以上より
・販売額：売上明細自体が無い＝「分析対象の商品が全く売れなかった店舗の場合」。
・クーポン額：売上明細はあるが「会員がクーポンを提示せず、適用しなかった場合」。
誤りやすいポイント
- クーポン額NULLを「売上が無い」と混同しやすい。列定義由来のNULLか結合由来のNULLかを区別する。
- COALESCE を「IS NULLの短縮形」と誤解し、戻り値が常に非NULLだと決め付ける。
- 外部結合を見落として「販売額は常にゼロ以上」と判断してしまう。
FAQ
Q: 商品もクーポンも無いとき、SUM の結果はどうなりますか？
A: COALESCE で0に置き換えた後に集計するため、売上額は0になります。
A: COALESCE で0に置き換えた後に集計するため、売上額は0になります。
Q: NULLを0に変換せずに SUM した場合との違いは？
A: 変換しないとNULLを含む計算は結果がNULLになるため、売上額が正しく0として扱えません。
A: 変換しないとNULLを含む計算は結果がNULLになるため、売上額が正しく0として扱えません。
Q: クーポン適用の有無を判定したい場合は？
A: WHERE クーポン額 IS NOT NULL で適用済み、IS NULL で未適用を抽出できます。
A: WHERE クーポン額 IS NOT NULL で適用済み、IS NULL で未適用を抽出できます。
関連キーワード: LEFT OUTER JOIN, COALESCE, NULL値処理、集計関数、検査制約
設問3：〔サマリテーブルの作成〕について、(1)、(2)に答えよ。
問題文を見る（1）表6中の（イ）〜（ハ）に入れる適切な字句を答えよ。
なお、結果行数を見積もるとき、分析対象の期間中、現在の店舗コード、店舗地域コード、小分類コード、大分類コード、商品コード、会員番号及び会員地域コードの数に変動がなかったと仮定すること。
模範解答
イ：200
ロ：36,000
ハ：4,200
解説
解答の論理構成
-
前提確認
- 「全国を10地域」（業務の概要(1)）
- 「分析対象の商品は100,000点… 大分類には10個」（業務の概要(3)(4)）
- 「A社の店舗は、全部で300店」（業務の概要(2)）
- 年代は「1〜7」の7区分（表1年代）
- 性別は 'M'、'F' の2区分（表1性別）
- 結果行数は「現在の店舗コード…の数に変動がなかった」と仮定（設問指示）
-
（イ）B3の最大結果行数
- グループ化対象：店舗地域コード別 ＋ 大分類コード別
- 期間：「2020年3月 と2019年3月」＝2か月分
- 10地域 × 10大分類 × 2 ＝ 200
-
（ロ）B4の最大結果行数
- グループ化対象：月別 ＋ 店舗コード別 ＋ 大分類コード別
- 月は「2019年の月別」＝ 12
- 店舗300 × 大分類10 × 月12 ＝ 36,000
-
（ハ）B5の最大結果行数
- グループ化対象：店舗コード別 ＋ 性別別 ＋ 年代別
- 店舗300
- 性別2 ('M'、'F')
- 年代7
- 300 × 2 × 7 ＝ 4,200
誤りやすいポイント
- 「店舗地域コード＝店舗数」と誤解し、10と300を取り違える。
- 年代を“6区分”と勘違い（70歳以上を忘れる）。
- B3の対象期間を1か月と読んで100と計算。
- “商品”や“小分類”の件数を掛け算に入れてしまう（大分類だけで良い）。
FAQ
Q: 小分類200個はどこで使うのですか？
A: 今回のB3〜B5はすべて「大分類」または「小分類」を指定していません。表6の対象列に含まれていなければ計算に入れません。
A: 今回のB3〜B5はすべて「大分類」または「小分類」を指定していません。表6の対象列に含まれていなければ計算に入れません。
Q: 実際に存在しない組合せも数えるのはなぜ？
A: 設問が「最大結果行数」を求めているためです。あり得る組合せを上限として計算します。
A: 設問が「最大結果行数」を求めているためです。あり得る組合せを上限として計算します。
Q: B5で法人会員を除外する根拠は？
A: 「個人会員について2020年3月の…」と明記されています。したがって性別と年代がNULLになり得る法人会員 'B' は計算対象外です。
A: 「個人会員について2020年3月の…」と明記されています。したがって性別と年代がNULLになり得る法人会員 'B' は計算対象外です。
関連キーワード: グループ化、主キー、集計関数、参照制約、データ設計
設問3：〔サマリテーブルの作成〕について、(1)、(2)に答えよ。
問題文を見る模範解答
A：性別、年代、小分類コード
解説
解答の論理構成
-
サマリテーブル “S” の既定列
問題文に示された定義は
「S(年、月、店舗コード、会員区分、会員地域コード、(A)、売上額)」。
ここに追加する (A) を決める。 -
必須列を洗い出す
表6で最も粒度が細かい分析は次の2つ。
・B5「個人会員について2020年3月の店舗コード別性別年代別売上額」
・B6「過去5年間の年月別小分類コード別売上額」
これらを再集計なしで得るには
「性別」「年代」「小分類コード」を保持するしかない。 -
最小列・最小行かの検証
(1) 会員属性
- B5で「性別」「年代」が求められる。
- 他分析は「性別」「年代」を使わないが、列を削るとB5が再集計不可。
(2) 商品属性
- B3で「大分類コード」が必要。
- 「小分類コード」は大分類→小分類の親子関係があるので、大分類は小分類から導出可能。
- 行数比較
商品コード：100,000点
小分類コード：200個
よって「小分類コード」を採用する方が行数を大幅に抑えられる。
(3) 以上より最小列数・行数を同時に満たす選択が決定。 -
結果
(A) に入れる列は「性別、年代、小分類コード」となる。
誤りやすいポイント
- 「大分類コード」をそのまま入れてしまい列数は増えないが、B6の再集計が不可能になる。
- 「商品コード」を入れると列は一つで済むと誤解しがちだが、行数が “100,000点” に膨れ上がり最小行数条件を満たせない。
- B5が個人会員だけを対象としているため「性別」「年代」はフィルタで扱えると勘違いし、列から外してしまうケース。
FAQ
Q: 「大分類コード」はサマリテーブルに含めなくても JOIN だけで取得できますか？
A: はい。「小分類」テーブルには “大分類コード” が存在するため、小分類コードを保持していれば結合で導出できます。
A: はい。「小分類」テーブルには “大分類コード” が存在するため、小分類コードを保持していれば結合で導出できます。
Q: 会員区分が ‘A’（個人）だけのときでも性別・年代列をNULLにできますか？
A: いいえ。B5のように個人会員を対象にした分析では集計キーになるため、NULLだとグループ化できません。
A: いいえ。B5のように個人会員を対象にした分析では集計キーになるため、NULLだとグループ化できません。
Q: 売上額を再計算するときクーポン額控除は再度必要ですか？
A: サマリテーブルに格納する時点で “販売額－クーポン額” を計算した値を置けば、後続分析SQLがシンプルになります。
A: サマリテーブルに格納する時点で “販売額－クーポン額” を計算した値を置けば、後続分析SQLがシンプルになります。
関連キーワード: サマリテーブル、グループ化キー、派生属性、階層型分類、非正規化
