戦国IT - 情報処理技術者試験の過去問対策サイト
お知らせお問い合わせ料金プラン

データベーススペシャリスト 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に示す。
データベーススペシャリスト試験（令和2年 午後1 問3 図1）
データベーススペシャリスト試験（令和2年 午後1 問3 表1）
〔見直しの方針〕
1．テーブルの統合 　これまで個人会員及び法人会員に関する情報をそれぞれ別テーブルに記録していたが、販売情報を分析する SQL文を簡素にするため、次のように統合する。 　・“個人会員”、“法人会員” テーブルを “会員” テーブルに統合する。 　・“個人売上”、“法人売上” テーブルを “売上” テーブルに統合する。 　なお、会員番号の付与方法は変えないものとし、また、統合に伴うテーブルの定義 (列名、データ型、制約など) の変更は必要最小限とする。
2．サマリテーブルの作成 　これまで分析用 SQL 文は、図1中のテーブルを直接アクセスしていたが、処理時間を改善するため、“売上”テーブルを集計したサマリテーブルを作成する。 　
〔テーブルの統合 〕 “個人会員”、“法人会員” テーブルに定義されていた制約は、それぞれ表 2,3のとおりであった。
データベーススペシャリスト試験（令和2年 午後1 問3 表2）
データベーススペシャリスト試験（令和2年 午後1 問3 表3）
　見直し後の“会員”、“売上” テーブルのテーブル構造を、図2に示す。 　Fさんが調べたところ、既存の会員番号をそのまま移行したのでは不都合が起きることが分かったので、“会員” テーブルに会員区分を追加し、個人会員には 'A' の値を、法人会員には 'B' の値をそれぞれ設定することにした。
データベーススペシャリスト試験（令和2年 午後1 問3 図2）
　Fさんは、次の規則に基づいてテーブル定義表を作成し、テーブルを定義した。
(1) データ型欄には、データ型を記入する。 (2) NOT NULL 欄には、NOT NULL 制約を設定する場合に Y を記入し、そうでなければN を記入する。 (3) 格納長欄には、RDBMS の仕様に従って格納長を記入する。 (4) 索引の種類と構成列欄には、作成する索引を記入する。 　・索引の種類には、P (主キー索引)、U (ユニーク索引)、NU (非ユニーク索引)のいずれかを記入し、各索引の構成列には構成列の順番に1からの連番を記入する。 　・制約欄には、参照制約、検査制約を、SQL の構文で記入する。 　Fさんが作成した見直し後の “会員” テーブルのテーブル定義表を、表4 に示す。
データベーススペシャリスト試験（令和2年 午後1 問3 表4）
〔見直し後の販売情報の分析〕 　販売情報の分析では、例えば、販売実績が非常に少なかったケースを調べる目的で、次のような分析 (分析 B1) を行っている。 テーブルの設計を見直した後の分析 B1用SQL文の構文を図3に、実行結果を表5に示す。 　分析B1：2020年3月の店舗コード別商品コード別売上額を調べる。ただし、店舗コードはM1,M2及びM3に、商品コードはP1及びP2に限定する。
　データベーススペシャリスト試験（令和2年 午後1 問3 図3）
データベーススペシャリスト試験（令和2年 午後1 問3 表5）
〔サマリテーブルの作成〕 　Fさんが、処理時間の改善を要望された分析用 SQL 文の目的を調べ、最大結果行数を見積もった結果を、表 6 に示す。 また、表6中の分析のうち、分析 B2 用 SQL文の構文を、図4に示す。
データベーススペシャリスト試験（令和2年 午後1 問3 表6）
データベーススペシャリスト試験（令和2年 午後1 問3 図4）

設問1：〔テーブルの統合〕 について、(1)〜(3)に答えよ。

（1）表2, 3中の(a)、(b)に入れる適切な字句を答えよ。

模範解答

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

解説

解答の論理構成

  1. 外部キーの基本
    • 外部キーは “子表の列 → 親表の主キー” という関係で定義されます。
  2. “会員地域コード” の参照先を特定
    • 【問題文】には “地域（地域コード、地域名、…）” という表が記載されています。
    • “会員地域コード” は地域を示す列なので、親表は 
      地域
      、主キーは 
      地域コード
      。
    • よって (a) = 
      地域(地域コード)
      。
  3. “担当社員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 に更新します。
Q: “地域” と “店舗” の使い分けが分かりません。
A: “地域” は全国を “10 地域” に区分した管理単位、店舗は実際の “300 店” の実店舗です。会員は地域単位で管理されるため “会員地域コード” の参照先は “地域” 表になります。
Q: “社員ID” を参照しているのは法人会員だけですか?
A: はい。【問題文】に “法人会員には、担当する社員が登録後に1名決められる” とあり、この担当者を “担当社員ID” で表しています。
関連キーワード: 外部キー、参照制約、正規化、データ整合性

設問1：〔テーブルの統合〕 について、(1)〜(3)に答えよ。

（2）会員区分を追加することなく既存の会員番号をそのまま移行すれば、どのような場合にどのような不都合が起きると考えられるか、それぞれ 25字以内で述べよ。

模範解答

場合：個人会員番号と法人会員番号が重複する場合 不都合：会員番号の登録が主キー重複違反で失敗する

解説

解答の論理構成

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

誤りやすいポイント

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

FAQ

Q: 会員区分を付ければ本当に重複問題は解決しますか？
A: はい。「会員区分＋会員番号」を複合主キーにすることで、同じ８桁番号が存在しても区分が異なれば一意になります。
Q: 主キーではなくユニーク索引にすれば良いのでは？
A: いずれにしても一意性制約を持つ列集合に重複値が入れば違反になります。区分を含めない限り問題は残ります。
Q: 番号体系を統合する方法は検討しないのですか？
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

解説

解答の論理構成

  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

Q: 担当社員ID を個人会員に NULL のみとするのはなぜですか？
A: 【問題文】に「法人会員には、担当する社員が登録後に1名決められる」とあり、個人会員には担当社員の概念がありません。そのため CHECK 制約で個人会員時は 
担当社員ID IS NULL
とします。
Q: 性別列を NULL 禁止にして CHECK で ‘M’、’F’ を限定してはいけませんか？
A: 法人会員行では性別が存在してはいけないため、列自体を NOT NULL にすると法人会員を登録できなくなります。NULL を許容したうえで CHECK で場合分けするのが正解です。
Q: 外部キーに 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'）

模範解答

あ：② い：① う：⑥ え：④ お：⑧ か：⑨ き：⑩ く：⑫

解説

解答の論理構成

  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

Q: 外部結合で売上ゼロ行を作るとき、限定条件はすべて JOIN 句に書くべきですか？
A: 欠落行を保持したい側（店舗）の列に対する条件だけを WHERE 句に書き、相手側（売上）の列に対する条件は JOIN 句に書くのが原則です。
Q: “U.商品コード IN('P1'、'P2')” を WHERE 句に置いた場合の結果は？
A: “M3” のように該当商品の売上が無い店舗が完全に除外され、実行結果に “なし” 行が出力されません。
Q: “COALESCE” と “SUM” を組み合わせる理由は？
A: “COALESCE” で NULL を 0 に置き換えてから “SUM” することで、クーポン未使用時（NULL 値）の差し引き額を正しく計算できます。
関連キーワード: OUTER JOIN, COALESCE, グループ化、WHERE と JOIN の違い、NULL 取り扱い

設問2：〔見直し後の販売情報の分析〕 について、(1)、(2)に答えよ。

（2）COALESCE 関数は、1番目の引数が NULL でないときはその値、NULL のときは2番目の引数を返す関数である。 図3 中の選択リスト中の販売額又はクーポン額が NULL になるのはどのような場合か、本文中の用語を用いて、それぞれ25字以内で述べよ。

模範解答

販売額：分析対象の商品が全く売れなかった店舗の場合 クーポン額：分析対象の商品にクーポンを適用しなかった場合

解説

解答の論理構成

  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

Q: 商品もクーポンも無いとき、
SUM
の結果はどうなりますか？
A: 
COALESCE
で 0 に置き換えた後に集計するため、売上額は 0 になります。
Q: 
NULL
を 0 に変換せずに 
SUM
した場合との違いは？
A: 変換しないと 
NULL
を含む計算は結果が 
NULL
になるため、売上額が正しく 0 として扱えません。
Q: クーポン適用の有無を判定したい場合は？
A: 
WHERE クーポン額 IS NOT NULL
で適用済み、
IS NULL
で未適用を抽出できます。
関連キーワード: LEFT OUTER JOIN, COALESCE, NULL値処理、集計関数、検査制約

設問3：〔サマリテーブルの作成〕 について、(1)、(2)に答えよ。

（1）表6中の（イ）〜（ハ）に入れる適切な字句を答えよ。 　なお、結果行数を見積もるとき、分析対象の期間中、現在の店舗コード、店舗地域コード、小分類コード、大分類コード、商品コード、会員番号及び会員地域コードの数に変動がなかったと仮定すること。

模範解答

イ：200 ロ：36,000 ハ：4,200

解説

解答の論理構成

  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

Q: 小分類 200 個はどこで使うのですか？
A: 今回の B3〜B5 はすべて「大分類」または「小分類」を指定していません。表6 の対象列に含まれていなければ計算に入れません。
Q: 実際に存在しない組合せも数えるのはなぜ？
A: 設問が「最大結果行数」を求めているためです。あり得る組合せを上限として計算します。
Q: B5 で法人会員を除外する根拠は？
A: 「個人会員について2020年3月の…」と明記されています。したがって性別と年代が NULL になり得る法人会員 
'B'
は計算対象外です。
関連キーワード: グループ化、主キー、集計関数、参照制約、データ設計

設問3：〔サマリテーブルの作成〕 について、(1)、(2)に答えよ。

（2）図1 中の “個人売上”、“法人売上” テーブル以外のテーブルを総称して、次元テーブルという。 表 6 中の全ての分析について、次元テーブルと一つのサマリテーブルだけから売上額を集計できるようにしたい。 F さんは、サマリテーブルの候補のうち、最小列数かつ最小行数となるサマリテーブル “S” のテーブル構造を、次のように設計した。 A に入れる複数の列名を答えよ。 ただし、列名は次元テーブルから選ぶこと。 　S(年、月、店舗コード、会員区分、会員地域コード、(A)、売上額)

模範解答

A：性別、年代、小分類コード

解説

解答の論理構成

  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

Q: 「大分類コード」はサマリテーブルに含めなくても JOIN だけで取得できますか？
A: はい。「小分類」テーブルには “大分類コード” が存在するため、小分類コードを保持していれば結合で導出できます。
Q: 会員区分が ‘A’（個人）だけのときでも性別・年代列を NULL にできますか？
A: いいえ。B5 のように個人会員を対象にした分析では集計キーになるため、NULL だとグループ化できません。
Q: 売上額を再計算するときクーポン額控除は再度必要ですか？
A: サマリテーブルに格納する時点で “販売額－クーポン額” を計算した値を置けば、後続分析 SQL がシンプルになります。
関連キーワード: サマリテーブル、グループ化キー、派生属性、階層型分類、非正規化
戦国ITクイズ機能

\ せっかくなら /

データベーススペシャリストを
クイズ形式で学習しませんか？

クイズ画面へ遷移する→

すぐに利用可能！

©︎2025 情報処理技術者試験対策アプリ

このサイトについてプライバシーポリシー利用規約特商法表記開発者について