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

模範解答

解説

解答の論理構成

1. 外部キーの基本
   • 外部キーは “子表の列 → 親表の主キー” という関係で定義されます。
2. “会員地域コード” の参照先を特定
   • 【問題文】には “地域（地域コード, 地域名, …）” という表が記載されています。
   • “会員地域コード” は地域を示す列なので、親表は

     地域

     、主キーは

     地域コード

     。
   • よって (a) =

     地域(地域コード)

     。
3. “担当社員ID” の参照先を特定
   • 【問題文】に “社員（社員ID, 社員名, 店舗コード, …）” が示されており、

     社員ID

     が主キーです。
   • “担当社員ID” はここを参照するのが自然で、削除時は “ON DELETE SET NULL” として表中に示されています。
   • よって (b) =

     社員(社員ID)

     。

誤りやすいポイント

• “会員地域コード” を “店舗地域コード” と取り違え、

  店舗

  表を参照先にしてしまう。
• “担当社員ID” を “店舗” 表の列と誤解し、

  店舗(店舗コード)

  と書いてしまう。
• 外部キー句に列名だけ書き、

  表名(列名)

  の形式を忘れる。

FAQ

模範解答

解説

解答の論理構成

1. 前提確認
   • 統合前は「個人会員」と「法人会員」が別テーブル。
   • 付番は「互いに無関係でそれぞれ独自に行っている。」
2. 統合後の設計意図
   • 「会員番号」を主キーにしたいが、区分列を追加しない案を想定。
3. 発生し得る事象
   • 同じ "CHAR(8)" が個人と法人の両方に存在すると、「個人会員番号と法人会員番号が重複する場合」が生じる。
4. 主キーの性質
   • 主キーは「重複不可」。よって INSERT 時に「主キー重複違反」が起き、移行処理や新規登録が失敗する。
5. したがって
   • 区分列を追加せずに移行すると「会員番号の登録が主キー重複違反で失敗する」不都合が発生する、という結論になる。

誤りやすいポイント

• 「番号は８桁で十分だから重複しない」と思い込み、独立付番という条件を見落とす。
• 不都合を「検索しづらい」などと曖昧に書き、主キー制約違反という具体的な障害を挙げない。
• 会員区分を追加すれば解決する前提を忘れ、解答に区分列自体を書いてしまう。

FAQ

模範解答

解説

解答の論理構成

1. 主キー列の NULL 可否
   図2で主キーは「会員区分, 会員番号」。主キー列は NULL を取れないため【c は Y】。会員番号も既に Y 表記。
2. 会員地域コードの NULL 可否
   個人・法人とも必須で、元の二表とも FOREIGN KEY が付いていた。【d は Y】。
3. 性別・年代・担当社員ID の NULL 可否
   • 個人会員: 性別と年代必須、担当社員ID は存在してはいけない。
   • 法人会員: 性別と年代は存在してはいけない、担当社員ID は NULL 可。
     よって列自体は NULL 許容にし、CHECK で分岐させるので【e,f,g は N】。
4. 外部キーの削除時動作
   個人会員→年代: 元表が「ON DELETE RESTRICT」なので【h:RESTRICT】。
   法人会員→社員: 元表が「ON DELETE SET NULL」なので【i:SET NULL】。
5. 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

担当社員ID IS NULL

模範解答

解説

解答の論理構成

1. 外部結合の方向を決める
   図3は

   FROM （あ）  
   LEFT OUTER JOIN （い） ON …
   

   となっており、左側（あ）が親テーブル、右側（い）が子テーブルです。実行結果【表5】では “M3” に売上が無い場合でも 1 行表示しているため、売上が必ず存在するとは限らない “店舗” を左側に置く必要があります。
   解答群のうち “店舗M” は ② なので（あ）＝②、残る（い）は “売上U” ① になります。
2. 結合キー（う，え）
   店舗表と売上表を結ぶキーは共通の “店舗コード” です。店舗表側は “M.店舗コード” ⑥、売上表側は “U.店舗コード” ④ となり、順不同なので（う）＝⑥，（え）＝④ もしくは逆でも可です。
3. 絞り込み条件（お，か，き）
   JOIN 句内の追加条件として
   ・年を “2020” に限定 → ⑧
   ・月を “03” に限定 → ⑨
   ・商品コードを “P1”，“P2” に限定 → ⑩
   が必要です。（お，か，き）は順不同なので ⑧，⑨，⑩ の並びは任意です。
4. 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

模範解答

解説

解答の論理構成

1. 図3は店舗マスタＭと売上Ｕを

   LEFT OUTER JOIN

   している。【図3：「LEFT OUTER JOIN」】
2. 外部結合の主表側（店舗）に従表（売上）が無いと、従表列は

   NULL

   になる。したがって「販売額」「クーポン額」が共に

   NULL

   になるのは “売上が1件も無い” パターン。
3. ただしクーポン額には、売上明細が存在しても

   NULL

   になるケースが定義されている。表1には「クーポン額…適用しなかった場合、NULL が設定される」とある。
4. 以上より
   ・販売額：売上明細自体が無い＝「分析対象の商品が全く売れなかった店舗の場合」。
   ・クーポン額：売上明細はあるが「会員がクーポンを提示せず、適用しなかった場合」。

誤りやすいポイント

• クーポン額

  NULL

  を「売上が無い」と混同しやすい。列定義由来の

  NULL

  か結合由来の

  NULL

  かを区別する。

• COALESCE

  を「

  IS NULL

  の短縮形」と誤解し、戻り値が常に非

  NULL

  だと決め付ける。
• 外部結合を見落として「販売額は常にゼロ以上」と判断してしまう。

FAQ

SUM

COALESCE

NULL

SUM

NULL

NULL

WHERE クーポン額 IS NOT NULL

IS NULL

模範解答

解説

解答の論理構成

1. 前提確認
   • 「全国を 10 地域」（業務の概要(1)）
   • 「分析対象の商品は 100,000 点… 大分類には10個」（業務の概要(3)(4)）
   • 「A社の店舗は,全部で300店」（業務の概要(2)）
   • 年代は「1〜7」の７区分（表1 年代）
   • 性別は

     'M'

     ,

     'F'

     の２区分（表1 性別）
   • 結果行数は「現在の店舗コード…の数に変動がなかった」と仮定（設問指示）
2. （イ）B3 の最大結果行数
   • グループ化対象：店舗地域コード別 ＋ 大分類コード別
   • 期間：「2020年3月 と 2019年3月」＝２か月分
   • 10 地域 × 10 大分類 × 2 ＝ 200
3. （ロ）B4 の最大結果行数
   • グループ化対象：月別 ＋ 店舗コード別 ＋ 大分類コード別
   • 月は「2019年の月別」＝ 12
   • 店舗 300 × 大分類 10 × 月 12 ＝ 36,000
4. （ハ）B5 の最大結果行数
   • グループ化対象：店舗コード別 ＋ 性別別 ＋ 年代別
   • 店舗 300
   • 性別 2 (

     'M'

     ,

     'F'

     )
   • 年代 7
   • 300 × 2 × 7 ＝ 4,200

誤りやすいポイント

• 「店舗地域コード＝店舗数」と誤解し、10 と 300 を取り違える。
• 年代を“6 区分”と勘違い（70 歳以上を忘れる）。
• B3 の対象期間を１か月と読んで 100 と計算。
• “商品”や“小分類”の件数を掛け算に入れてしまう（大分類だけで良い）。

FAQ

'B'

模範解答

解説

解答の論理構成

1. サマリテーブル “S” の既定列
   問題文に示された定義は
   「S(年, 月, 店舗コード, 会員区分、会員地域コード,(A), 売上額)」。
   ここに追加する (A) を決める。
2. 必須列を洗い出す
   表6 で最も粒度が細かい分析は次の２つ。
   ・B5「個人会員について2020年3月の店舗コード別性別年代別売上額」
   ・B6「過去5年間の年月別小分類コード別売上額」
   これらを再集計なしで得るには
   「性別」「年代」「小分類コード」を保持するしかない。
3. 最小列・最小行かの検証
   (1) 会員属性
   - B5 で「性別」「年代」が求められる。
   - 他分析は「性別」「年代」を使わないが、列を削ると B5 が再集計不可。
   (2) 商品属性
   - B3 で「大分類コード」が必要。
   - 「小分類コード」は大分類→小分類の親子関係があるので、大分類は小分類から導出可能。
   - 行数比較
   商品コード：100,000 点
   小分類コード：200 個
   よって「小分類コード」を採用する方が行数を大幅に抑えられる。
   (3) 以上より最小列数・行数を同時に満たす選択が決定。
4. 結果
   (A) に入れる列は「性別, 年代, 小分類コード」となる。

誤りやすいポイント

• 「大分類コード」をそのまま入れてしまい列数は増えないが、B6 の再集計が不可能になる。
• 「商品コード」を入れると列は一つで済むと誤解しがちだが、行数が “100,000 点” に膨れ上がり最小行数条件を満たせない。
• B5 が個人会員だけを対象としているため「性別」「年代」はフィルタで扱えると勘違いし、列から外してしまうケース。

FAQ