正規化理論（1NF〜BCNF）

定義

正規化（Normalization）：データの冗長性を排除し、更新異常（Update Anomaly）を防ぐためにテーブルを分割する設計プロセス。E.F. Coddが1970年代に定義した理論に基づく。

更新異常とは何か

正規化の「なぜ」を理解する出発点。

非正規化テーブル（問題を含む）:
┌──────────┬──────────┬──────────────┬─────────┬──────────────┐
│ order_id │ customer │ customer_addr│ product │ product_price│
├──────────┼──────────┼──────────────┼─────────┼──────────────┤
│    1     │  Alice   │ 東京都新宿区  │  Apple  │     100      │
│    2     │  Alice   │ 東京都新宿区  │ Banana  │      80      │
│    3     │  Bob     │ 大阪府大阪市  │  Apple  │     100      │
└──────────┴──────────┴──────────────┴─────────┴──────────────┘

挿入異常: Aliceが注文していない商品のデータを登録できない
削除異常: order_id=3を削除するとBobの情報も失われる
更新異常: Appleの価格を変えると全行を更新しなければならない
          → 更新漏れで価格の不一致が生じる

第1正規形（1NF）

ルール：各セルには単一の原子的な値しか持たない。繰り返しグループを排除する。

❌ 1NF違反（配列・繰り返しグループ）:
┌──────────┬──────────┬─────────────────────────────┐
│ order_id │ customer │ products                    │
├──────────┼──────────┼─────────────────────────────┤
│    1     │  Alice   │ Apple(100), Banana(80)      │  ← 複数値
└──────────┴──────────┴─────────────────────────────┘

✅ 1NF準拠（繰り返しを行に展開）:
┌──────────┬──────────┬─────────┬───────┐
│ order_id │ customer │ product │ price │
├──────────┼──────────┼─────────┼───────┤
│    1     │  Alice   │  Apple  │  100  │
│    1     │  Alice   │ Banana  │   80  │
└──────────┴──────────┴─────────┴───────┘

第2正規形（2NF）

前提：1NFを満たしていること。
ルール：主キーの一部に対する部分関数従属を排除する（複合主キーの場合に発生）。

❌ 2NF違反:
主キー = (order_id, product)
                ↓ 部分従属
customer → order_id だけで決まる（productは不要）
price    → product  だけで決まる（order_idは不要）

┌──────────┬─────────┬──────────┬───────┐
│ order_id │ product │ customer │ price │  ← customerとpriceが部分従属
└──────────┴─────────┴──────────┴───────┘

✅ 2NF準拠（部分従属を別テーブルへ）:
orders:
┌──────────┬──────────┐
│ order_id │ customer │
└──────────┴──────────┘

products:
┌─────────┬───────┐
│ product │ price │
└─────────┴───────┘

order_items:
┌──────────┬─────────┐
│ order_id │ product │  ← 複合主キー（全体に従属する項目のみ）
└──────────┴─────────┘

第3正規形（3NF）

前提：2NFを満たしていること。
ルール：推移的関数従属を排除する（主キー → 非キー → 非キーという連鎖）。

❌ 3NF違反:
┌──────────┬──────────┬─────────────┬──────────┐
│ order_id │ customer │ customer_zip│ city     │
└──────────┴──────────┴─────────────┴──────────┘
  主キー → customer → customer_zip → city
                       ↑推移的従属：customer_zipからcityが決まる

✅ 3NF準拠（推移従属を別テーブルへ）:
orders:
┌──────────┬──────────┬─────────────┐
│ order_id │ customer │ customer_zip│
└──────────┴──────────┴─────────────┘

zip_codes:
┌─────────────┬──────────┐
│ customer_zip│ city     │
└─────────────┴──────────┘

一般的なルール（Armstrong の公理の直感版）：
「非キー属性は主キーだけに従属し、他の非キー属性には従属しない」

ボイス-コッド正規形（BCNF）

前提：3NFを満たしていること。
ルール：すべての非自明な関数従属において、決定項が候補キーであること。3NFより厳格。

❌ BCNF違反の例:
学生は複数の科目を受講し、科目ごとに担当教員が1人
1人の教員は1つの科目だけ担当する

(学生, 科目) → 教員  ← 候補キー
(学生, 教員) → 科目  ← 候補キー
教員 → 科目          ← 決定項が候補キーでない！（BCNF違反）

┌────────┬────────┬────────┐
│ student│ subject│ teacher│
├────────┼────────┼────────┤
│ Alice  │ Math   │ Yamada │
│ Alice  │ English│ Tanaka │
│ Bob    │ Math   │ Yamada │  ← Mathの教員はYamadaという情報が重複
└────────┴────────┴────────┘

✅ BCNF準拠:
enrollments:
┌────────┬────────┐
│ student│ teacher│
└────────┴────────┘

teacher_subjects:
┌────────┬────────┐
│ teacher│ subject│
└────────┴────────┘

3NF ⊃ BCNF（BCNFは3NFより強い条件）。ただしBCNFは分解後に元のFDが保存されないことがある。

正規形のまとめ

正規形	排除する問題	チェックポイント
1NF	繰り返しグループ・複数値	各セルに1つの値だけか
2NF	部分関数従属	非キー属性が主キー全体に従属しているか
3NF	推移的関数従属	非キー属性が別の非キー属性に従属していないか
BCNF	3NFより厳格な従属	すべての決定項が候補キーか

意図的な非正規化

正規化が「常に正しい」わけではない。パフォーマンスのために非正規化を選ぶ場面がある。

非正規化が有効なケース:

1. 読み取り専用の集計（DWH・ダッシュボード）
   正規化されたOLTPデータをDWHでスタースキーマ（非正規化）に変換

2. 頻繁に参照するカラムのコピー（キャッシュとして）
   orders.customer_name ← customersテーブルからコピー
   JOINなしで注文一覧を表示できる
   → ただし顧客名変更時に更新が必要

3. カウンターカラム
   posts.comment_count ← commentsの集計値を事前計算
   SELECT COUNT(*) のたびのフルスキャンを回避

非正規化のトレードオフ:
  書き込みの複雑さ増加 ↔ 読み取りの高速化
  更新異常のリスク ↔ JOINコストの削減

実務的な判断基準

正規化を優先する:
  - OLTP（書き込みが多い）
  - データ整合性が最重要
  - スキーマが頻繁に変わる

非正規化を選ぶ:
  - OLAP（読み取りが多い、書き込みは少ない）
  - クエリパフォーマンスが優先
  - 読み取りパターンが固定されている
  - マテリアライズドビューやdbtで管理できる

出典・参考文献

E.F. Codd, “A Relational Model of Data for Large Shared Data Banks” (1970)
C.J. Date, An Introduction to Database Systems (2003)
Ramakrishnan & Gehrke, Database Management Systems (2002)

定義