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

キーワードと論点整理

解答の根拠

1. a：顧客グループコード
   「1人の営業部社員は, 一つの顧客グループを担当する。」
   → 営業部社員表 (営業部社員) に対応する顧客グループを示す外部キーとして 「顧客グループコード」 を保持
2. b：担当営業部コード
   「一つの営業部は, 複数の顧客グループを担当する。…一つの顧客グループを, 複数の営業部が担当することはない。」
   → 顧客グループ表 (顧客グループ) にその担当営業部を示す外部キーとして 「担当営業部コード」 を保持
3. c・d：分割元案件番号／統合先案件番号
   「案件ごとに, 分割の場合は分割元案件番号を, 統合の場合は統合先案件番号を記録する。」
   → 案件表 (案件) 中の空欄 c・d に、「分割元案件番号」・「統合先案件番号」 を設定
   （設問では順不同で可）
4. e：社員番号
   「案件の変更は, 担当営業部の社員が実施する。…案件の変更時, その変更履歴及び変更を実施した社員を記録する。」
   → 案件変更履歴表 (案件変更履歴) に操作社員を示す外部キーとして 「社員番号」 を保持
5. f：社員番号
   「案件詳細の変更は, 担当営業部の社員又は担当工事部の社員のいずれかが実施する。…変更履歴及び変更を実施した社員を記録する。」
   → 案件詳細変更履歴表 (案件詳細変更履歴) に操作社員を示す外部キーとして 「社員番号」 を保持
6. g：案件番号
   「受注ごとに, …対応する案件番号などを記録する。」
   → 受注表 (受注) に対応案件を示す外部キーとして 「案件番号」 を保持
7. h：担当工事部コード
   「受注明細として, 担当工事部ごとの受注明細名, 受注明細額などを記録する。」
   → 受注明細表 (受注明細) に担当工事部を示す外部キーとして 「担当工事部コード」 を保持

誤りやすいポイント

• c・dの混同
  どちらが「分割元案件番号」でどちらが「統合先案件番号」か迷いやすいが、設問文で「順不同」と明記されているため、両方を埋めれば正解になります。
• e・fとも「社員番号」
  両履歴表に同じ属性名を使うため、同じキーを２回書くことに違和感を覚えやすいですが、どちらも「変更を実施した社員」を指す外部キーです。
• gの取り違え
  受注表の g を「顧客番号」としてしまいがちですが、「顧客番号」ではなく「案件番号」を記録します。

試験対策ポイント

• 業務ルール ⇔ 外部キー
  業務要件で「～を担当する」「～に対応する」と記載されている場合、該当するリレーションに外部キーを追加する設計パターンを確実に押さえましょう。
• 分割／統合の履歴管理
  分割元・統合先の関係は必ずレコード上に持たせ、「同時実行なし」「無効フラグ」といったルールも合わせてチェックするクセをつけると、スキーマ設計ミスを防げます。
• 履歴テーブルのキー設計
  変更履歴には必ず「変更を実施した社員番号」を登録する――このルールは案件／案件詳細どちらでも同様です。
• 試験では順不同表記に注意
  「順不同」「どちらでもよい」といった指示がある場合、正解要素を漏れなく挙げることが重要です。

模範解答の核心キーワードと論点

• サブタイプ／スーパタイプ（特殊化・一般化）の扱い
  （例）「部」をスーパタイプとし、「営業部」「工事部」をサブタイプ化
  「社員」をスーパタイプとし、「営業部社員」「工事部社員」をサブタイプ化
• 業務ルールから導かれるリレーションシップと基数（１：１／１：Ｎ／Ｎ：１）
  （例）「顧客グループ」と「営業部」は１対Ｎ、「顧客」と「顧客グループ」はＮ対１
• エンティティ間の履歴管理のリレーション
  （例）「案件」と「案件変更履歴」は１対Ｎ、「案件詳細」と「案件詳細変更履歴」は１対Ｎ
• 受注関連の関係
  「案件」と「受注」は１対１（案件ごとに１つの受注を記録）
  「受注」と「受注明細」は１対Ｎ
  「受注明細」と「案件詳細」はＮ対１

各リレーションシップの論理的説明

1. 部 ⇔ 営業部／工事部（特殊化）
   引用：「三つの営業部と九つの工事部がある。部は,部コードで一意に識別する。」
   → 「部」をスーパタイプ、「営業部」「工事部」をそれぞれサブタイプとして特殊化。
2. 社員 ⇔ 営業部社員／工事部社員（特殊化）
   引用：「社員は,社員番号で一意に識別する。」「社員は,営業部又は工事部のいずれか一つの部に所属する。」
   → 「社員」をスーパタイプ、「営業部社員」「工事部社員」をサブタイプとして特殊化。
3. 社員 ⇔ 部（所属関係：Ｎ対１）
   引用：「社員は…営業部又は工事部のいずれか一つの部に所属する。」
   → 社員(Ｎ) ： 部(１)
4. 営業部 ⇔ 顧客グループ（１対Ｎ）
   引用：「一つの営業部は,複数の顧客グループを担当する。 一つの顧客グループを,複数の営業部が担当することはない。」
   → 営業部(１) ： 顧客グループ(Ｎ)
5. 営業部社員 ⇔ 顧客グループ（Ｎ対１）
   引用：「1人の営業部社員は,一つの顧客グループを担当する。 一つの顧客グループを、複数の営業部社員が担当する場合がある。」
   → 営業部社員(Ｎ) ： 顧客グループ(１)
6. 顧客グループ ⇔ 顧客（１対Ｎ）
   引用：「顧客は…顧客グループのいずれか一つに所属する。」
   → 顧客グループ(１) ： 顧客(Ｎ)
7. 顧客 ⇔ 案件（１対Ｎ）
   引用：「案件ごとに…顧客…を記録する。」
   → 顧客(１) ： 案件(Ｎ)
8. 案件 ⇔ 案件状態（Ｎ対１）
   引用：「案件ごとに…案件状態…を記録する。」
   → 案件状態(１) ： 案件(Ｎ)
9. 案件 ⇔ 案件詳細（１対Ｎ）
   引用：「案件詳細は…案件詳細全体で一意な案件詳細番号で識別し…記録する。」
   → 案件(１) ： 案件詳細(Ｎ)
10. 案件 ⇔ 案件変更履歴（１対Ｎ）
    引用：「案件の変更時,その変更履歴及び変更を実施した社員を記録する。」
    → 案件(１) ： 案件変更履歴(Ｎ)
11. 案件詳細 ⇔ 案件詳細変更履歴（１対Ｎ）
    引用：「案件詳細についても同様に記録する。」
    → 案件詳細(１) ： 案件詳細変更履歴(Ｎ)
12. 案件 ⇔ 受注（１対１）
    引用：「案件状態が‘受注’となった時点で、案件ごとに受注として記録する。…対応する案件番号などを記録する。」
    → 案件(１) ： 受注(１)
    （受注前はレコードなし、受注後は１件）
13. 受注 ⇔ 受注明細（１対Ｎ）
    引用：「受注明細は、受注番号,受注明細番号で一意に識別する。」
    → 受注(１) ： 受注明細(Ｎ)
14. 受注明細 ⇔ 案件詳細（Ｎ対１）
    引用：「受注明細として,担当工事部ごとの…記録する。」
    → 担当工事部単位の受注明細は、該当する案件詳細に紐づく
    → 案件詳細(１) ： 受注明細(Ｎ)

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

• 「案件⇔受注」を１対１とするか１対Ｎとするか
  → 問文の「案件ごとに受注として記録する」を読み落とすと、複数受注を想定し誤答しやすい。
• サブタイプとのリレーションをスーパタイプではなくサブタイプ同士に誤って書く
  → サブタイプ識別可能ならスーパタイプ⇔サブタイプで表現
• 受注明細と案件詳細の関連を見落とす
  → 「担当工事部ごと」という文言から、工事部単位で分かれた明細は必ず案件詳細と紐づく

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

• 業務ルールを丁寧に読み、エンティティ間の「主キー」「外部キー」となる属性に着目する
• 「一つのAは複数のBを…」→A：１、B：Ｎ、「Aは一つのBを…複数のAがBを…」→A：Ｎ、B：１
• サブタイプ（特化）／スーパタイプ（一般化）の取り扱い
  → 同じ外部キーを共有する仲間はスーパタイプにまとめ、サブタイプは追加属性のみ管理
• 履歴管理（変更履歴）は必ず元エンティティとの１対Ｎ
• 受注フェーズに入るとそれ以降は変更されない旨を読んで、１対１の関係とオプショナルキーを理解すること

キーワードと論点整理

• どのテーブルを使って集計を行うのか
• テーブル間を結合（JOIN）するための 結合条件（外部キーと主キーの対応）
• SELECT句およびGROUP BY句に出現させる グルーピング列

解答（穴埋め対応表）

なぜこの解答になるのか

1. 集計の対象テーブルとグルーピング

• SQL① の要件
  「有効な案件（無効フラグが０である案件）について，顧客グループ名ごと案件状態名ごとに受注見込額を集計する」
  したがって SELECT，GROUP BY にはまず 顧客グループ名 と 案件状態名 を置きます（ア＝顧客グループ名，イ＝案件状態名）。
• SQL② の要件
  「有効な案件及び案件詳細（いずれも無効フラグが０）について，顧客グループ名ごと案件状態名ごと工事内容名ごとに売上見込額を集計する」
  → SELECT と GROUP BY に 顧客グループ名（ア），案件状態名（イ），工事部名（コ）を並べます。
  ※「工事内容名」と書かれていますが，概念モデル・関係スキーマでは工事部を表す「部」テーブルの部名で集計すると解釈できます。

2. テーブル間の結合条件

3. WHERE句の組み立て例（SQL①）

FROM 案件 ウ
  JOIN 顧客 エ
    ON ウ.顧客番号 = エ.顧客番号
  JOIN 顧客グループ オ
    ON エ.顧客グループコード = オ.顧客グループコード
  JOIN 案件状態 カ
    ON ウ.案件状態コード = カ.案件状態コード
WHERE ウ.無効フラグ = 0

4. WHERE句の組み立て例（SQL②）

FROM 案件 ウ
  JOIN 顧客 エ
    ON ウ.顧客番号 = エ.顧客番号
  JOIN 顧客グループ オ
    ON エ.顧客グループコード = オ.顧客グループコード
  JOIN 案件状態 カ
    ON ウ.案件状態コード = カ.案件状態コード
  JOIN 案件詳細 サ
    ON ウ.案件番号 = サ.案件番号
  JOIN 部 シ
    ON サ.担当工事部コード = シ.部コード
WHERE ウ.無効フラグ = 0
  AND サ.無効フラグ = 0

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

1. グルーピング列の取り違え
   • SQL②で「工事内容名」とだけ書かれているので，案件詳細名 と混同しがちですが，実際には「工事部名（部名）」でグループ化します。
2. テーブル「部」と「工事部」の混同
   • 関係スキーマでは，工事部 部門の属性を持つ「工事部」テーブルとは別に，部コードや部名を持つ「部」テーブルが存在します。
   • 工事部名を取り出すには「部」テーブルの部名カラムを参照します。
3. JOIN条件の抜け漏れ
   • 外部キー対応を忘れると多重集計や Cartesian product のリスクがあります。
   • SQL②では案件詳細と部の結合条件を必ず入れること。

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

• 関係データモデルでは，外部キー = 主キー の組合せでテーブルを結合する
• 集計系SQLは，SELECTとGROUP BYに同じ列を列挙する
• 問題文の業務要件（「どの属性でグルーピングするか」「無効フラグ＝０」など）を抜けなく反映する
• 類似用語（例：「工事部」 vs 「部」）を混同しないようにスキーマ図を正確に読む

キーワード・論点整理

• 未記録の案件詳細
• 登録（記録）タイミングの違い

解説

「商談が進み, 案件を担当する工事部が決定した時点で、案件詳細を記録する。」
とあり、工事部が未決定の案件には案件詳細が存在しません。
また、
「案件状態が‘受注’となった時点で, …これ以降,案件及び案件詳細が変更されることはない。」
ともあるように、登録先が異なるタイミングでデータが作成されるため、両者の合計値が一致しない場合があります。

受験者のひっかけポイント

• 「受注見込額」と「売上見込額」の対象テーブル（案件／案件詳細）を混同しやすい
• 案件詳細登録は“受注”前の段階で行われるという前提を見落とすと、なぜ差異が生じるか理解できない

試験対策ポイント

• ヘッダ（案件）と明細（案件詳細）の登録契機の違いを必ず確認する
• データ不整合が起こる典型例として、「登録タイミングのズレ」を押さえる
• 問題文中の「～時点で記録する」という文言はデータ発生のトリガーを示していると覚えておくこと