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

キーワード・論点の整理

• 参照制約（外部キー制約）
  親テーブルの主キーまたはユニークキーを参照し、一貫性を保つ制約です。書式は
  FOREIGN KEY (子テーブルの列) REFERENCES 親テーブル(親列)
• 親テーブルの選定
  子テーブル（ここでは“個人会員”／“法人会員”）の列が、どのテーブルの主キーを参照すべきかを判断します。
• 問題箇所
  表２・表３中の空欄 (a),(b) に、参照先となる「親テーブル名(親列名)」を補う必要があります。

解答：表２・表３

• (a) 地域(地域コード)
• (b) 社員(社員ID)

なぜその解答になるのか

1. 図１より、「地域」テーブルには主キーとして 地域コード が定義されており、

   「地域（地域コード, 地域名, …）」
   が示されています。
   したがって、会員テーブル側の 会員地域コード は親テーブル「地域」の 地域コード を参照すべきです。

2. 同じく図１より、「社員」テーブルには主キーとして 社員ID が定義されています。

   「社員（社員ID, 社員名, 店舗コード, …）」
   法人会員テーブルの 担当社員ID は、その社員を示すキーであるため、
   親テーブル「社員」の 社員ID を参照します。

受験者が誤りやすいポイント

• 「地域コード」を持つテーブルは他にも「店舗」「会員」などに出てきますが、参照先は“地域”テーブルの主キーであることを確認する必要があります。
• 「社員ID」を参照するのは“社員”テーブルであり、店舗マスターや会員マスターではありません。
• 外部キー参照先の括弧内には必ず親テーブル側の主キーまたはユニークキーを指定する、という基本ルールを忘れないこと。

試験対策として覚えておくべきポイント

• 参照制約の書式：
  FOREIGN KEY (子列) REFERENCES 親テーブル(親列) [ON DELETE ○○]
• 参照先は常に親テーブルの主キーまたはユニークキーであること。
• ER図やテーブル一覧から、どのテーブルのどの列が主キーかを正確に読み取る練習をしておくこと。
• ON DELETE オプション（RESTRICT／SET NULL／CASCADE）は、要件に応じて適切に設定する。

キーワードと論点整理

• 「個人会員番号及び法人会員番号の付与は、互いに無関係でそれぞれ独自に行っている」（問題文より）
  → 同じ８桁番号が個人会員と法人会員で重複する可能性
• 会員テーブルの主キーに「会員番号」だけを使うと重複を防げない
• その結果、「主キー重複違反」が発生する

解答の根拠（問題文引用）

1. 個人・法人の番号付与方法について：

   「個人会員番号及び法人会員番号の付与は、互いに無関係でそれぞれ独自に行っている。」

2. 会員区分追加の必要性：

   「既存の会員番号をそのまま移行したのでは不都合が起きることが分かったので、“会員” テーブルに会員区分を追加し…」

受験者が誤りやすいポイント

• 「会員区分を追加しないと何が困るか」の設問で
  → 「会員区分がないと検索時に区別できない」などと答えやすいが、
  本問は 「主キー重複」 という制約違反が起きる点が焦点です。
• 重複の原因は「番号付与が無関係」という文言の読み落とし

試験対策として覚えるべきポイント

• 主キー制約は 一意性 を保証する。
• 異なるエンティティ（ここでは個人会員と法人会員）を同一カラムで区別せずに統合すると、
  番号が偶然一致した際に主キー重複エラーとなる。
• 統合テーブルには必ず「区分カラム」を追加し、主キーに含めることで一意性を保つ。

キーワード・論点整理

• NOT NULL 制約（必須入力／NULL 許容）の設定
• 参照制約（FOREIGN KEY）の ON DELETE 動作
• チェック制約（CHECK）の条件分岐
• 会員区分（'A'：個人会員、'B'：法人会員）による属性の有無

解答一覧

なぜその解答になるのか

1. 会員区分・各属性の NOT NULL 設定（c～g）
   • 会員区分 (c)：
     「Fさんは…“会員”テーブルに会員区分を追加し、個人会員には 'A' …法人会員には 'B'」とあり、必ず値を持つため Y。
   • 会員番号 (d)：
     「個人会員番号、法人会員番号…それぞれ独自に付与」「統合に伴う…変更は必要最小限」とあるので元の NOT NULL を維持し Y。
   • 会員地域コード (e)：
     「会員地域コードが設定される」＝全会員に必須。元の両テーブルとも FOREIGN KEY で NOT NULL を前提にしているため Y。
   • 性別 (f)：
     個人会員のみ「性別：男性 ‘M’，女性 ‘F’」が設定される一方、法人会員にはそもそも性別がない。NULL許容で N。
   • 年代 (g)：
     同様に年代も個人会員のみ。法人会員では不要なので N。
   • 担当社員 ID (h)：
     法人会員のみ「担当する社員が…1名決められる」。個人会員では NULL となるため N。
2. 参照制約の ON DELETE 動作（h,i）
   • 会員地域コード → 参照先 a ON DELETE RESTRICT
     「FOREIGN KEY 会員地域コード REFERENCES a ON DELETE RESTRICT」（表2,3 より）
   • 担当社員 ID → 参照先 b ON DELETE SET NULL
     「FOREIGN KEY 担当社員ID REFERENCES b ON DELETE SET NULL」（表3 より）
3. CHECK 制約の条件（j～m）
   図表からCHECK式は以下のように２つの OR 節に分岐している。

   CHECK (
     ( 会員区分 = 'A' AND 〈j〉 AND 〈k〉 AND 〈l〉 )
     OR
     ( 会員区分 = 'B' AND 性別 IS NULL AND 〈m〉 )
   )
   

   • 会員区分='A'（個人会員）側
     ・性別は必ず 'M' または 'F' → 性別 IN ('M','F')（j）
     ・年代は必ず設定 → 年代 IS NOT NULL（k）
     ・担当社員 ID は使わない → 担当社員 ID IS NULL（l）
   • 会員区分='B'（法人会員）側
     ・年代も性別も使わない → 年代 IS NULL（m）

受験者が誤りやすいポイント

1. NULL 許容／非許容の判断
   • 「元テーブルで NULL 許容だったか」をそのまま移行するのではなく、統合後の業務要件（個人会員か法人会員か）から再検討する。
2. ON DELETE 動作の混同
   • RESTRICT と SET NULL は役割が逆。元定義を正確に把握しないと誤答しやすい。
3. CHECK 制約内の AND／OR の組み合わせ
   • 会員区分ごとに AND がまとまっていること、括弧位置に注意する。

試験対策として覚えておくべきポイント

• 統合後のテーブルでは各種属性の NULL 許容を業務要件ベースで再設定する。
• FOREIGN KEY の ON DELETE オプション（CASCADE, SET NULL, RESTRICT）は元定義のまま移行する。
• 複雑な CHECK 制約は「会員区分ごとの条件セット」を意識して設計・読み取る。
• 問われている箇所(c)～(m)を整理する際、設問文の該当記述を必ず引用し、論理を明確に説明する練習をする。

キーワード・論点整理

• LEFT OUTER JOIN の利用
  店舗テーブルをベースに売上テーブルを外部結合し、売上がない店舗も結果に含める。
• ON句 vs WHERE句のフィルタ
  • ON句：結合する行を限定
  • WHERE句：最終結果を限定
• NULL処理
  • COALESCE による NULL 値の代替（商品コード、販売額・クーポン額）
• エイリアスと結合条件
  • 店舗テーブルに M、売上テーブルに U の別名を付与
  • M.店舗コード = U.店舗コード で結合

解答の根拠

分析B1：2020年3月の店舗コード別商品コード別売上額を調べる。ただし、店舗コードはM1,M2及びM3に、商品コードはP1及びP2に限定する。

SELECT M.店舗コード,
       COALESCE( U.商品コード, 'なし' ) 商品コード,
       SUM( COALESCE( U.販売額, 0 ) - COALESCE( U.クーポン額, 0 )) 売上額
FROM   □あ□
       LEFT OUTER JOIN □い□
         ON □う□ = □え□
        AND □お□
        AND □か□
        AND □き□
WHERE  □く□
GROUP BY M.店舗コード, U.商品コード

選択肢の検討／誤りやすいポイント

• 結合対象のテーブル
  あ に 売上U を誤って入れると、LEFT OUTER JOIN のベースが売上テーブルになり、売上がない店舗を拾えなくなる。
• キーの向き
  M.店舗コード = U.店舗コード の左右を入れ替えると構文エラーではないが可読性・暗黙の結合方向が変わり誤解を招く。
• フィルタの句の選択
  • U.商品コード IN（'P1','P2'） を WHERE 句に入れると、LEFT OUTER JOIN の NULL 行（売上なし）が除外され、店舗 M3 が結果に出なくなる。
  • 店舗コードの絞り込みは WHERE 句で行っても LEFT OUTER JOIN の前提に影響しないため、正しく WHERE に記述する。

試験対策としてのまとめ

1. 外部結合の特性把握
   • ON句でフィルタすると、結合行の有無に影響
   • WHERE句でフィルタすると、結合後の結果から除外
2. NULL 値処理
   • COALESCE を使って集計・表示用に NULL を適切に置き換える
3. テーブルエイリアスの付け方
   • 別名（M, U, C, K など）を統一して見やすく記述
4. SQL 分析問題の解き方
   • 要件（年・月・店舗・商品などの絞り込み）を ON句／WHERE句に正しく振り分け
   • GROUP BY 句に SELECT 句で使用した列を漏れなく記載

1. 模範解答のキーワードと論点整理

• COALESCE関数の動作
  “COALESCE 関数は, 1番目の引数が NULL でないときはその値, NULL のときは2番目の引数を返す関数である。”
• 販売額が NULL になるケース
  • 分析 B1 の SQL 文では LEFT OUTER JOIN を使ってすべての「店舗コード×商品コード」の組み合わせを出力しているため、該当する売上明細がない場合に U.販売額 が NULL になる。
• クーポン額が NULL になるケース
  • 問題文より “クーポン額は、会員が広告チラシ又はスマートフォンでクーポンを提示したときに適用した金額で、適用しなかった場合、NULL が設定される。”

2. 解答の論理的説明

2.1 COALESCE関数の役割

COALESCE 関数は, 1番目の引数が NULL でないときはその値, NULL のときは2番目の引数を返す関数である。

COALESCE( U.販売額, 0 )

2.2 販売額が NULL になる理由

• 図3 の SQL 文は

  FROM □あ□ LEFT OUTER JOIN □い□ ON … 
  

  として LEFT OUTER JOIN を使い、「分析対象のすべての店舗×商品」を網羅します。
• その結果、ある店舗でその商品がまったく売れていなければ、結合先（売上テーブル）に該当行がなく、U.販売額 に NULL が入ることになります。
• したがって「分析対象の商品が全く売れなかった店舗の場合」に U.販売額 が NULL になります。

2.3 クーポン額が NULL になる理由

• 問題文の定義より

  “クーポン額は…適用しなかった場合、NULL が設定される。”

• すなわち、会員がクーポンを提示しなかった取引では、売上テーブルの U.クーポン額 が NULL になります。
• 後段で

  COALESCE( U.クーポン額, 0 )
  

  を使うことで、NULL の場合には 0 を適用し、実額計算を正しく行っています。

2.4 まとめ表

3. 受験者が誤りやすいポイント

1. 販売額に NULL が入らないと誤解
   データ自体の制約で 販売額 が NOT NULL だと思い込み、外部結合による NULL 発生を見落としがちです。
2. クーポン額の NULL 条件を混同
   「クーポンを適用しなかった場合」が NULL になる、という仕様を押さえずに COALESCE を使う理由を理解できないことがあります。

4. 試験対策として覚えておくべきポイント

• COALESCE の基本動作と NULL を置き換える意義
• 外部結合（LEFT OUTER JOIN）では結合先がない行に NULL が入ること
• 問題文にある「NULL が設定される場合」の文言は必ずそのまま引用して理解すること
• 集計・計算時は COALESCE を使って NULL を扱いやすい値に変換するパターンが多いこと

模範解答のキーワード・論点整理

解答に至る論理的な説明

1. 【前提】

   「分析対象の期間中、現在の店舗コード, 店舗地域コード, 小分類コード, 大分類コード, 商品コード, 会員番号及び会員地域コードの数に変動がなかったと仮定すること。」

   この文言により，それぞれのコード数は固定値として計算できます。
2. B3 の計算
   〈問題文より〉

   「分析名：B3 2020年3月と2019年3月の店舗地域コード別大分類コード別売上額」

   • 店舗地域コードの種類数 = 10 （「全国を 10 地域に分けて販売情報を分析している」）
   • 大分類コードの種類数 = 10 （「商品分類には10個の大分類がある」）
   • 期間の月数 = 2 （2020年3月，2019年3月の２か月）
   したがって

   10 × 10 × 2 ＝ 200
   イ：200

3. B4 の計算
   〈問題文より〉

   「分析名：B4 2019年の月別店舗コード別大分類コード別売上額」

   • 月数 = 12 （「2019年の月別」）
   • 店舗コードの種類数 = 300 （「店舗は,全部で300店」）
   • 大分類コードの種類数 = 10
   したがって

   12 × 300 × 10 ＝ 36,000
   ロ：36,000

4. B5 の計算
   〈問題文より〉

   「分析名：B5 個人会員について2020年3月の店舗コード別性別年代別売上額」

   • 期間の月数 = 1 （2020年3月）
   • 店舗コードの種類数 = 300
   • 性別の種類数 = 2 （男性M・女性F）
   • 年代の種類数 = 7 （「年代には…20歳未満：1、…70歳以上：7」）
   したがって

   1 × 300 × 2 × 7 ＝ 4,200
   ハ：4,200

誤りやすいポイント

• 「店舗地域コード」と「店舗コード」の混同
  B3 は「店舗地域コード別」，B4/B5 は「店舗コード別」です。種類数（10 vs 300）がまったく異なるため注意。
• 期間の把握ミス
  B3 → 2か月，B4 → 12か月，B5 → 1か月。月数を見落とすと桁が大きくずれます。
• 年代の種類数
  「年代には…7」と明示されていますが，「20歳代・30歳代…」といった名称だけ覚えていて数を勘違いしやすい点。

試験対策として覚えておくべきポイント

1. GROUP BY のキー項目数を正確に抜き出す
   分析名の文章から「○○別」というグルーピング条件を一つずつピックアップする。
2. 各コード・分類の総数を確認
   問題文序盤に示される「全国を10地域」「店舗は300店」「大分類10件」「年代7件」などは，集計行数を決める基本情報です。
3. 対象期間の把握
   「2019年の月別」→12か月，「2020年3月と2019年3月」→2か月，「2020年3月」→1か月，など，期間の長さを確実に読み取る。
4. 掛け算で行数を見積もる
   行数＝（期間の単位数）×（各グルーピング項目の種類数）の積として計算することを習慣づける。

解説のポイント整理

• テーマ：次元テーブルとサマリテーブルを組み合わせ，表６の分析（B2～B6）をすべてサポートする最小のサマリテーブル構造を設計する
• サマリテーブル S の定義
  S(年, 月, 店舗コード, 会員区分, 会員地域コード, （A）…, 売上額)
• 求める A の列
  „性別“, „年代“, „小分類コード“

なぜ「性別, 年代, 小分類コード」なのか

1. B2（四半期名別 会員区分別 会員地域コード別）
   • サマリに「年」「月」を持たせることで，図４のカレンダ次元と結合し「四半期名」を取得可能
     （問題文「B2：2019年の四半期名別会員区分別会員地域コード別売上額」，図４参照）
2. B3/B4（大分類コード別）
   • サマリに「小分類コード」を持つと，次元テーブル「小分類」→「大分類」への参照で「大分類コード」を導出できる
     （問題文「B3：…大分類コード別売上額」「B4：…大分類コード別売上額」）
3. B5（性別・年代別）
   • 個人会員の分析で「性別」「年代」によるグループ化が必要
     （問題文「B5：…店舗コード別性別年代別売上額」）
4. B6（小分類コード別）
   • 直接「小分類コード」で集計
     （問題文「B6：…年月別小分類コード別売上額」）

性別, 年代, 小分類コード

受験者が誤りやすいポイント

1. 大分類コードを直接持たせる
   大分類単位の分析があるからと大分類コードを持たせると，B6（小分類単位集計）ができず，小分類から大分類を導出する柔軟性も失われます。
2. 商品コードを持たせる
   小分類コードより粒度の細かい商品コードを持つと行数が増大し，サマリテーブルが大きくなるため不適切です。
3. 会員区分だけで済むと思う
   法人会員には「性別」「年代」が存在しないため，個人会員分析（B5）を行えません。

試験対策として覚えておくべき知識

• サマリテーブル設計では，必要最小限のキー列を選び，かつその粒度が最も細かい次元を選択し，上位粒度は次元テーブルから導出する。
• データウェアハウスでは，「最低粒度（最小単位）のキー」を保持し，「上位粒度の情報は次元テーブル結合で取得」するのが原則。
• 各分析がグルーピングに用いる列を洗い出し，すべてをカバーする最小の列集合を求める演習を繰り返す。

サマリテーブル S のイメージ

• A に該当する列：性別, 年代, 小分類コード