リレーショナルモデルと正規化｜データエンジニアリング

🎓 レベル：基礎　|　重要度：A（必須）

📎 前提：データエンジニアリングとは・役割　|　関連：ER図とスキーマ設計・正規化と非正規化のトレードオフ

要点（BLUF）

リレーショナルモデルは、データを**テーブル（行＝レコード・列＝属性）**で表し、主キーで行を一意に識別し、外部キーでテーブル間の関係を張る。
正規化は「同じ事実を一箇所にだけ持つ」ようにテーブルを分割する設計。これで**更新時異常（一部だけ古いまま残る不整合）**を防ぐ。
目安は 第3正規形（3NF）。第1（繰り返し列を作らない）→第2（主キー全体に依存）→第3（非キー列が他の非キー列に依存しない）と段階的に冗長を削る。

概念 ── 「事実を一箇所に」

正規化のゴールはひとつ、「1つの事実は、データベースの1箇所にだけ存在する」状態です。顧客名が注文テーブルに繰り返し書かれていると、改名のとき全行を直す必要があり、漏れれば不整合になります。事実を1箇所（顧客テーブル）に集約すれば、直すのは1行で済みます。

仕組み ── 第1〜第3正規形

flowchart TB
    R["非正規（繰り返し・冗長あり）"] --> N1["第1正規形（1NF）：1セル1値・繰り返し列なし"]
    N1 --> N2["第2正規形（2NF）：複合キーの一部だけに依存する列を分離"]
    N2 --> N3["第3正規形（3NF）：非キー列が他の非キー列に依存しないよう分離"]

1NF：1つのセルに1つの値。「電話1,電話2,電話3」のような繰り返し列を作らない。
2NF：複合主キーのとき、キーの一部だけで決まる列を別テーブルへ。
3NF：非キー列が別の非キー列で決まる（推移的従属）状態を解消。例「郵便番号→住所」を注文テーブルに持たず分離。

動くSQL ── 更新時異常を見る

非正規化テーブルでは、顧客名が注文ごとに重複します。1行だけ改名すると不整合が残ります。

CREATE TABLE orders_denorm (
  order_id INTEGER PRIMARY KEY, cust_id INTEGER, cust_name TEXT, amount INTEGER
);
INSERT INTO orders_denorm VALUES
  (1,100,'Sato',1200),(2,100,'Sato',800),(3,101,'Tanaka',500);
UPDATE orders_denorm SET cust_name='Sato Hanako' WHERE order_id=1;  -- 1行だけ更新
SELECT order_id, cust_id, cust_name FROM orders_denorm;

実行結果（実機・SQLite）:

(1, 100, 'Sato Hanako')
(2, 100, 'Sato')        <- 古いまま残る＝更新時異常
(3, 101, 'Tanaka')

正規化して顧客を別テーブルに分けると、改名は1行で完結し、結合すれば全注文に反映されます。

CREATE TABLE customers (cust_id INTEGER PRIMARY KEY, cust_name TEXT);
CREATE TABLE orders (order_id INTEGER PRIMARY KEY,
  cust_id INTEGER REFERENCES customers(cust_id), amount INTEGER);
INSERT INTO customers VALUES (100,'Sato'),(101,'Tanaka');
INSERT INTO orders VALUES (1,100,1200),(2,100,800),(3,101,500);
UPDATE customers SET cust_name='Sato Hanako' WHERE cust_id=100;  -- 1行だけ
SELECT o.order_id, c.cust_name, o.amount
FROM orders o JOIN customers c ON o.cust_id=c.cust_id ORDER BY o.order_id;

実行結果（実機・SQLite）:

(1, 'Sato Hanako', 1200)
(2, 'Sato Hanako', 800)   <- 1行更新で全注文に反映（不整合なし）
(3, 'Tanaka', 500)

なぜそうするか ── 更新の一貫性

なぜ冗長を嫌うのか。冗長は更新時異常・挿入時異常・削除時異常の温床だからです。同じ事実が複数箇所にあると、片方だけ直して矛盾が生まれる。正規化は「直す箇所を1つに絞る」ことで、書き込みが多いシステム（OLTP）の整合性を守ります。

ただし正規化はタダではありません。テーブルが増えるほど、読むときに結合（JOIN）が増えて遅くなる。分析中心の用途ではあえて冗長を許す（非正規化）方が速い——この使い分けが正規化と非正規化のトレードオフの主題です。

⚠️ よくある落とし穴

何でも正規化すればよいと考える → 分析クエリで多段JOINが必要になり遅くなる。用途（更新系か分析系か）で判断。
主キーに業務的な意味のある値（メールアドレス等）を使う → 値が変わると参照が壊れる。**変わらない代理キー（サロゲートキー：連番やUUID）**が安全。
外部キー制約を張らずに運用 → 孤児レコード（参照先のない行）が混入し、JOINで静かに行が消える。

対応ラボ

data-engineering-study/labs/02_data_modeling.py（PYTHONIOENCODING=utf-8 で実行し上記出力を確認済み）。

確認できること：非正規化の更新時異常、正規化後の一箇所更新、スタースキーマ集計