モデリング3層（CDM / LDM / PDM）
抽象 → 具体の流れ
最上位 — 抽象
概念モデル
Conceptual Data Model (CDM)
対象: ビジネスステークホルダー
- エンティティ名・関係のみ
- 技術的詳細なし
- ビジネス語彙・概念を反映
- 例: 顧客 ↔ 注文 ↔ 製品
→
中間 — 論理
論理モデル
Logical Data Model (LDM)
対象: データアーキテクト・アナリスト
- すべての属性を含む
- データ型・長さを定義
- 正規化 (1NF〜3NF) 適用
- 特定DBMS非依存
→
最下位 — 具体
物理モデル
Physical Data Model (PDM)
対象: DBA・開発者
- 特定DBMSに最適化
- テーブル・インデックス・制約
- 意図的な非正規化
- パーティション・ストレージ設定
試験ポイント: 「概念モデルの対象者はビジネスステークホルダー」「論理モデルは特定DBMSに依存しない」「物理モデルはDBMS固有の実装を含む」の3点が頻出。
正規化（Normalization）— 1NF → BCNF
段階的な異常除去フロー
1NF
第1正規形
繰り返しグループなし
すべての値が原子値（分割不可能）
→
2NF
第2正規形
1NF + 部分関数従属の除去
複合キーの一部のみへの依存を排除
→
3NF
第3正規形
2NF + 推移的関数従属の除去
非キー→非キーの依存を排除
→
BCNF
ボイスコッド正規形
3NF + すべての決定子が
候補キーである
| 正規形 | 解消する問題 | キーワード |
|---|
| 1NF |
繰り返しグループ・非原子値 |
Atomic values / 原子値 |
| 2NF |
部分関数従属（複合キーの一部への依存） |
Partial Dependency |
| 3NF |
推移的関数従属（非キー→非キー依存） |
Transitive Dependency |
| BCNF |
全決定子が候補キーでない問題 |
Candidate Key / Determinant |
| 4NF |
多値従属（Specialist対象） |
Multi-valued Dependency |
試験注意: Fundamentals試験は1NF〜3NF/BCNFを中心に出題。4NF/5NFはSpecialist対象。
ERDとUMLの表記法
ERD カーディナリティ記号（Crow's Foot / IE記法）
| 記号 | 意味 | 読み方 |
|---|
| || | 1（必須） | Exactly One |
| |O | 0 または 1（オプション） | Zero or One |
| }| | 1以上（必須・多） | One or Many |
| }O | 0以上（オプション・多） | Zero or Many |
記法の種類
| 記法 | 別名 | 特徴 |
|---|
| IE記法 | Crow's Foot | 最も一般的。鳥の足で「多」を表現 |
| バーカー記法 | Barker / Oracle | Oracle社方式。ソフトライン使用 |
| Chen記法 | ERDの原型 | ダイヤモンドが関係を表す |
| UML クラス図 | — | オブジェクト指向設計と整合 |
スタースキーマ vs スノーフレークスキーマ
★ スタースキーマ（非正規化ディメンション）
ディメンション = 非正規化・シンプル・高速クエリ
❄ スノーフレークスキーマ（正規化ディメンション）
ディメンション = 正規化・整合性高・JOINが多く複雑
| 観点 | スタースキーマ | スノーフレークスキーマ |
|---|
| ディメンション | 非正規化（フラット） | 正規化（階層的） |
| クエリ性能 | 高速（JOINが少ない） | やや遅い（JOINが多い） |
| データ整合性 | 冗長データあり | 高い |
| ストレージ | やや多い | 節約できる |
| 設計の複雑さ | シンプル | 複雑 |
SCD（Slowly Changing Dimension）Type 1 / 2 / 3
ディメンションの変更履歴管理戦略
| タイプ | 対処法 | 履歴 | 使いどき | 実務例 |
|---|
| Type 1 |
上書き（既存レコードを更新） |
なし |
誤り訂正・過去は気にしない |
電話番号の誤入力修正 |
| Type 2 |
新行追加 + 有効日付で管理 |
完全保持 |
完全な変更履歴が必要 |
顧客住所変更（地域別分析） |
| Type 3 |
カラム追加（前値・現値を同行に保持） |
限定的 |
変更前後の比較のみ必要 |
部門変更（現在と直前のみ） |
Type 2 の必須カラム: effective_from_dt（開始日）/ effective_to_dt（終了日、NULLが現在有効）/ is_current_flag
正規化 vs 非正規化 — トレードオフ
| 観点 | 正規化（3NF） | 非正規化 |
|---|
| データ整合性 | 高い（更新異常なし） | リスクあり（重複データ） |
| 読み取り性能 | JOINが多く遅くなりやすい | 高速（JOINが少ない） |
| 書き込み性能 | 効率的 | 冗長な更新が必要 |
| ストレージ | コンパクト | 冗長で大きい |
| 用途 | OLTP（トランザクション処理） | OLAP（分析・DWH） |
| 設計原則 | 論理モデル（整合性優先） | 物理モデル（パフォーマンス優先） |
キー設計
| 種類 | 説明 | 利点 | 欠点 |
|---|
| ナチュラルキー |
ビジネス上の意味を持つキー
例: 社員番号、マイナンバー |
意味が明確・人間が読める |
ビジネスルール変更でキー変更が必要 |
| サロゲートキー |
システムが生成する意味のないキー
例: ID = 1, 2, 3 / UUID |
安定・変更に強い・外部キー結合が簡潔 |
ビジネス的な意味なし |
DWH設計原則: サロゲートキーをDWH用PKとして使い、ナチュラルキーはNATURAL_ID列として別途保持（SCD Type 2 で必須）。
★ Specialist 深掘りセクション
モデル品質の5次元
| 次元 | 説明 |
|---|
| 完全性 | 全ビジネス概念がモデル化されているか |
| 正確性 | ビジネスルールが正確に表現されているか |
| 一貫性 | 命名規則・データ型・パターンが統一されているか |
| 簡潔性 | 冗長な要素がないか（過剰設計を避ける） |
| 安定性 | ビジネス変化に対して変更しやすい設計か |
Data Vault モデリング
| 要素 | 役割 |
|---|
| Hub | ビジネスキーを格納
customer_hub → customer_natural_id |
| Link | Hub間の関係を格納
order_customer_link |
| Satellite | 属性・全変更履歴を格納
customer_name_sat（追記のみ） |
利点: 監査性が高い / アジャイルなスキーマ変更 / 並列ロード容易
適した場面: 銀行・保険など監査要件が厳しい業務
Bi-temporal（二時制）データモデリング
Valid Time（有効時間）
ビジネス上の事実が真であった期間
例: 顧客が東京に住んでいた期間
Transaction Time（取引時間）
DBにレコードが存在した期間
例: このデータがDBに登録・更新された日時
-- Bi-temporal テーブル（両方の時制を管理）
CREATE TABLE customer_bitemporal (
customer_id INT,
name VARCHAR(100),
-- Valid Time
valid_from DATE, -- この情報がビジネス上いつから有効か
valid_to DATE, -- この情報がビジネス上いつまで有効か
-- Transaction Time
recorded_at DATETIME, -- DBにいつ書き込まれたか
superseded_at DATETIME -- この行がいつ上書きされたか（NULLは現在）
);
モデル変更ガバナンスプロセス
→
→
ステークホルダー
レビュー
Business + Technical
→
アンチパターン: 開発者がモデルを勝手に変更してからDBを変更する → ガバナンス違反。モデルが「設計図」として機能するには変更プロセスが必須。
3層モデル比較（CDM / LDM / PDM）
|
概念モデル (CDM) |
論理モデル (LDM) |
物理モデル (PDM) |
| 対象者 | ビジネスステークホルダー | データアーキテクト・アナリスト | DBA・開発者 |
| 含む内容 | エンティティ・関係のみ | 属性・データ型・正規化 | テーブル・インデックス・制約 |
| DBMS依存 | なし | なし（DB非依存） | あり（DBMS固有） |
| 正規化 | 不適用 | 1NF〜3NF適用 | 意図的に非正規化も |
| 目的 | ビジネス合意形成 | 設計の論理的整合性 | パフォーマンス最適化 |
| キーワード | Entity / Relationship | Attribute / Normalization | Table / Index / Partition |
SCD タイプ比較（1 / 2 / 3）
| タイプ | 方法 | 履歴保持 | 追加カラム例 | 使う場面 |
|---|
| Type 1 |
既存行を上書き（UPDATE） |
なし |
不要 |
誤入力訂正・過去不要 |
| Type 2 |
新行を追加（INSERT） |
完全 |
effective_from, effective_to, is_current |
住所変更・セグメント変更 |
| Type 3 |
カラムを追加（ALTER TABLE） |
限定的 |
prev_address, current_address |
変更前後の比較のみ |
最頻出: SCD Type 2 — 「有効開始日・有効終了日・現在フラグの3点セット」を覚える。
正規化ルール早見表
| 正規形 | 前提 | 追加条件 | 除去する依存関係 |
|---|
| 1NF | — | 原子値・繰り返しグループなし | 非原子値・繰り返し |
| 2NF | 1NF | 部分関数従属の除去 | A,B → C で A → C となる部分依存 |
| 3NF | 2NF | 推移的関数従属の除去 | PK → X → Y の間接依存 |
| BCNF | 3NF | 全決定子が候補キー | 候補キー以外の決定子 |
スター vs スノーフレーク — 瞬時判別
★ スタースキーマ
- • ディメンション = 非正規化（フラット）
- • JOINが少ない → クエリ高速
- • シンプルな星型構造
- • Kimball DWH の標準手法
❄ スノーフレークスキーマ
- • ディメンション = 正規化（階層的）
- • JOINが多い → 整合性高い
- • 雪の結晶のような複雑構造
- • ストレージ効率が良い
頻出キーワード一覧
| キーワード | 定義 |
|---|
| ナチュラルキー | ビジネス上の意味を持つキー（変更リスクあり） |
| サロゲートキー | システム生成の意味なし識別子（安定） |
| 候補キー | 行を一意に識別できる最小の属性集合 |
| スーパーキー | 候補キーを含む任意の属性集合 |
| 外部キー | 他テーブルの主キーを参照する属性 |
| キーワード | 定義 |
|---|
| Forward Engineering | 要件→概念→論理→物理→DDL生成 |
| Reverse Engineering | 既存DB→物理→論理→文書化 |
| ファクトテーブル | 数値指標＋ディメンションキー（DWH中心） |
| ディメンションテーブル | 分析軸（日時・顧客・製品）の詳細情報 |
| EAV | Entity-Attribute-Value（アンチパターン） |
Case 1: EC企業の注文データモデル設計
背景
日本の中規模ECプラットフォーム（月間注文数100万件）。初期モデルに以下の問題が発生:
- • キャンペーン価格・通常価格・会員価格が混在し「注文時点の価格」が追跡不可
- • 商品属性をEAVパターンで管理（アンチパターン）
- • 返品・交換処理が元設計と整合しない
アンチパターン: EAV（Entity-Attribute-Value）
CREATE TABLE product_attributes (
product_id INT,
attr_name VARCHAR(100), -- 'size', 'color', 'weight' etc.
attr_value VARCHAR(500)
);
EAVの問題点（試験で問われる）:
• データ型の制御不可（数値も文字列として格納）
• 特定属性での検索が極端に遅い
• 外部キー制約・NOT NULL制約が使えない
• 正規化の観点で1NFすら満たさない場合がある
改善された論理モデル（3NF）
-- OrderHeader（注文ヘッダー）
order_id (PK, Surrogate), customer_id (FK), order_datetime,
order_status_cd (FK → OrderStatus), channel_cd (FK → SalesChannel)
-- OrderLine（注文明細）— 注文時価格を記録！
order_line_id (PK, Surrogate), order_id (FK), product_variant_id (FK),
quantity, unit_price_at_order, discount_amount, list_price_at_order
-- ProductVariant（商品バリアント）
variant_id (PK, Surrogate), product_id (FK), size_cd, color_cd, sku
-- Product（商品マスタ）
product_id (PK, Surrogate), product_name, category_id (FK), base_price
物理モデル（パフォーマンス考慮）
CREATE TABLE order_line (
order_line_id BIGINT PRIMARY KEY,
order_id BIGINT NOT NULL,
product_variant_id INT NOT NULL,
quantity INT NOT NULL,
unit_price_at_order DECIMAL(10,2) NOT NULL,
-- 意図的な非正規化（JOINコスト削減）
product_name_snapshot VARCHAR(200), -- 注文時の商品名スナップショット
variant_desc_snapshot VARCHAR(100), -- 注文時のバリアント説明
INDEX idx_order_id (order_id),
INDEX idx_created (created_at)
) PARTITION BY RANGE (YEAR(created_at)); -- 年次パーティション
設計原則: 論理モデルは3NF（整合性優先）、物理モデルは意図的な非正規化（パフォーマンス優先）
Case 2: SCD Type 2 の完全実装
背景
DWHの顧客ディメンション設計。顧客の住所が変わった場合に「以前の住所での注文」と「新住所での注文」を正確に分析したい。
完全な SCD Type 2 設計
CREATE TABLE dim_customer (
customer_key INT PRIMARY KEY, -- Surrogate Key（DWH用）
customer_natural_id VARCHAR(20) NOT NULL, -- ナチュラルキー（元システムID）
customer_name VARCHAR(100),
address_line1 VARCHAR(200),
prefecture_cd CHAR(2),
-- SCD Type 2 制御カラム（3点セット）
effective_from_dt DATE NOT NULL, -- この行が有効になった日
effective_to_dt DATE, -- NULL = 現在有効
is_current_flag CHAR(1) DEFAULT 'Y', -- 現在有効行フラグ
dw_created_dt DATETIME,
source_system_cd VARCHAR(10)
);
正しいクエリパターン
-- 現在の顧客情報
SELECT * FROM dim_customer
WHERE is_current_flag = 'Y';
-- 特定日時点のデータ
SELECT * FROM dim_customer
WHERE customer_natural_id = 'C001'
AND effective_from_dt <= '2023-06-15'
AND (effective_to_dt > '2023-06-15'
OR effective_to_dt IS NULL);
よくある実装ミス
落とし穴 1
effective_to_dt にNULLと '9999-12-31' が混在 → クエリが複雑化
落とし穴 2
複数レコードで is_current_flag='Y' が付く（ETL冪等性の欠如）
落とし穴 3
ナチュラルキーにインデックスがない → 全履歴検索が遅い
SCDタイプ選択基準
| Type | 使いどき | 実務例 |
|---|
| Type 1 | 誤り訂正・過去は気にしない | 電話番号の誤入力修正 |
| Type 2 | 完全な変更履歴が必要 | 顧客住所変更（地域別分析） |
| Type 3 | 変更前後の比較のみ | 部門変更（現在と1つ前） |
| Type 4 | 履歴を別テーブルに分離 | 急速に変わる属性（価格等） |
| Type 6 | Type 1+2+3 組み合わせ | 複雑なケース（Kimball推奨） |
試験注意: Type 4, 5, 6 はFundamentals試験には出ない。DW&BI Specialist試験で出題。
Case 3: アンチパターン診断
アンチパターン 1: God Table（神テーブル）
-- 500カラム以上、あらゆるデータが1テーブルに
CREATE TABLE customer_all (
id INT PRIMARY KEY,
name VARCHAR(100),
address VARCHAR(500),
-- ... 497カラム続く
order_count INT, -- 集計値が入ってる！
last_order_date DATE -- 集計値が入ってる！
);
問題: NULLが多発 / 集計値が整合性を崩す / スパースデータのストレージ浪費
正しい対処
正規化してサブタイプに分離（Individual / Corporate）+ 集計値はViews or 集計テーブルに切り出す
アンチパターン 2: 意味のある複合主キー
CREATE TABLE order_item (
order_id INT,
product_code VARCHAR(20),
PRIMARY KEY (order_id, product_code) -- ビジネスキー組み合わせをPKに
);
問題: product_code体系変更でPK変更が必要 / カスケードアップデートが発生
正しい対処
サロゲートキーをPKとし、業務キーはUNIQUE制約で保証
アンチパターン 3: 汎用ステータステーブル
CREATE TABLE statuses (
entity_type VARCHAR(50), -- 'order', 'customer', 'product'
status_code VARCHAR(20),
status_name VARCHAR(100)
);
問題: 外部キー制約が貼れない / entity_typeで絞り込まないと不正参照が可能
正しい対処
エンティティごとに独立したステータステーブル（order_status, customer_status）
Case 4: データモデルレビューの実施
1
ビジネス要件との整合性
全ビジネス概念がモデルに存在するか / ビジネスルールが制約として表現されているか / ビジネスユーザーが概念を認識できるか
2
データ整合性
参照整合性が保証されているか / NOT NULL・CHECK制約が適切か / CASCADEの動作は意図通りか
3
設計の一貫性
命名規則が統一されているか（PascalCase/snake_case）/ データ型が一貫しているか / パターンが統一されているか
4
パフォーマンス考慮
高頻度クエリのインデックスが適切か / N+1問題が発生しうる設計か / パーティショニングが必要か
レビューチェックリスト
☐ エンティティ定義がBusiness Glossaryと一致
☐ 必須属性に NOT NULL が設定されている
☐ 外部キーにはインデックスが存在する
☐ 命名規則ドキュメントに準拠している
☐ SCDタイプの選択理由が文書化されている
☐ ビジネスステークホルダーのレビュー完了
☐ バージョン管理システムにモデルが保存
★ Specialist 試験 頻出シナリオ問題
問題 1
小売業DWHチームが顧客セグメント変更（Gold→Silver等）を追跡。変更前後の双方で売上集計したい。最適なSCDタイプは？
解答
SCD Type 2 — 新行追加で履歴を保持。Type 3では直前の変更しか追えない。Type 1では履歴が消える。
問題 2
製品バリアント（サイズ×カラー）の属性設計でEAVパターンを提案されたが、バリアントごとに属性数が異なる。何を推奨するか？
解答
EAVは避ける。ProductType別サブタイプテーブル、またはJSONカラムのhybridアプローチ。EAVは検索・制約・型安全性の全てで劣る。
問題 3
Reverse Engineeringの主な目的は何か？
解答
既存DBから論理・概念データモデルを再構築し文書化すること。レガシーシステムの理解、移行計画、コンプライアンス対応に使用。