--- note: ERと関数従属。正規形を「防いでいる更新異常」で理解し、非正規テーブルをBCNFまで分解する created: 2026-08-14T10:00:00+09:00 --- # 第4回｜論理設計と正規化 > 正規化とは丸暗記のルールではなく、「どの更新異常を、なぜ防げるのか」を説明できるようになるための設計技術である。 ## この回のねらい 要求からER図を起こし、正規形の意味を「どの更新異常を防いでいるか」という観点で説明できるようにする。第1回から使ってきた `customers` / `categories` / `products` / `orders` / `order_items` という共通スキーマが、なぜあの形になっているのかを、わざと崩した「注文Excel風テーブル」を使って手を動かしながら確認する。正規化を暗記の手順としてではなく、挿入異常・更新異常・削除異常を防ぐための道具として身につけることが目標である。 ## 到達目標 - [ ] 正規形（NF: Normal Form）の各段階（1NF〜BCNF）がそれぞれ何を要求し、どの更新異常（挿入・更新・削除）を防ぐかを自分の言葉で説明できる - [ ] 与えられたテーブルの属性から関数従属を見つけ出し、`X → Y` の形で書き出せる - [ ] 非正規化されたテーブルを、関数従属にもとづいて2NF→3NF→BCNFまで段階的に分解できる - [ ] 自然キーとサロゲートキーのどちらを主キーに選ぶか、根拠（変更可能性・一意性・結合コスト等）を挙げて判断できる - [ ] 主キーをサロゲートキーにした場合でも、自然キーにUNIQUE制約が必要な理由を説明できる - [ ] どの列にNULLを許可すべきかを、業務上の意味にもとづいて判断できる ## 前提と準備 第1回で作成済みの `customers` / `categories` / `products` / `product_prices` / `orders` / `order_items` / `events` が投入済みであることを前提にする。本回はこれらのテーブルが「なぜこの形なのか」を正規化の観点から説明する回であり、新しい拡張やサーバ設定は不要である。SQLはPostgreSQL 16系で動作確認できる文法を使う。 本回の演習では、あえて設計の悪い例を再現するための一時テーブル（`order_flat` など）をいくつか作る。これらは共通スキーマの一部ではなく、この回の中だけで使い捨てる。以下はpsqlに接続している状態で実行する想定で書く。 ```bash psql ``` ```sql -- 共通スキーマが揃っていることの確認（第1回の投入結果） \dt ``` ## この回で新しく出てくる用語 正規形の判定はこれらの語の関係で決まる。本編で順に詳しく扱うが、先に一覧で押さえておく。 回をまたいで使う語は[用語集](glossary.md)にもまとめている。 | 用語 | 一行での説明 | |---|---| | 正規形（NF: Normal Form） | テーブルがどの更新異常を防げる状態にあるかを表す段階。1NF ⊃ 2NF ⊃ 3NF ⊃ BCNF の順に条件が厳しくなり、上位を満たすものは下位も満たす | | 関数従属（FD: Functional Dependency） | `X` の値が決まれば `Y` の値が一意に定まる関係。`X → Y` と書く | | 決定項 | 関数従属 `X → Y` の左辺 `X`。他の属性を決める側 | | スーパーキー | 行を一意に識別できる属性の組。最小である必要はない | | 候補キー | スーパーキーのうち、これ以上属性を減らせない最小のもの | | 素属性 | いずれかの候補キーの一部になっている属性 | | 部分従属 | 候補キー全体ではなく、その一部だけに従属している関係 | | 推移従属 | 非キー属性を経由して、間接的にキーに従属している関係 | | 無損失分解 | 分解した表をJOINすると元の表がそのまま復元できる分解 | ## 本編 ### 4.1 ER図の基本 — 実体・属性・関係を絵にする ER図（Entity-Relationship Diagram）は、業務に登場する実体（エンティティ、例: 顧客・注文）と、実体同士の関係（リレーションシップ）を可視化する図である。テーブル定義だけを見ていると見落としがちな「1人の顧客が何件の注文を持てるか」「注文には必ず顧客が紐づくか」といった多重度（カーディナリティ）を、図にすると一目で確認できる。 よく使われる記法が「鳥の足（crow's foot）記法」で、線の端の記号が「1」または「多」、実線・破線が「必須（NOT NULL相当）」「任意（NULLを許容）」を表す。共通スキーマの `customers` と `orders` の関係を例にすると次のようになる。 ```text CUSTOMERS ||--o{ ORDERS | | | +-- o{ : 0以上の多 … 1人の顧客は0件以上の注文を持つ +------ || : ちょうど1 … 1件の注文は必ずちょうど1人の顧客に属する ``` `||` は「ちょうど1」、`o{` は「0以上の多」を表す。つまりこの図は「1人の顧客は0件以上の注文を持つ。1件の注文は必ずちょうど1人の顧客に属する」と読む。ER図は設計の意図を確認する道具であり、この後の節で行う関数従属の分析と対にして使うと効果が高い。分析が終わったあと、本編の最後（4.6節末）で共通スキーマ全体のER図を改めて示す。 ### 4.2 関数従属（Functional Dependency）を読み取る 属性の集合 `X` の値が決まれば、属性 `Y` の値が一意に定まるとき、「`X` は `Y` を関数従属させる」といい `X → Y` と書く（関数従属は英語名の頭文字から **FD** とも略す）。`X` を**決定項（determinant）**と呼ぶ。テーブルの全属性を関数従属させる属性の組を**スーパーキー**と呼び、そのうち属性をこれ以上減らせない最小のものを**候補キー**と呼ぶ。「行を一意に識別できる組」がスーパーキー、「その中で無駄のないもの」が候補キーという関係である。たとえば `(order_id, product_name, qty)` は行を一意に識別できるのでスーパーキーだが、`qty` を落としても一意なので候補キーではない。 正規化の作業は、実はほとんどが「テーブルの中にどんな関数従属が隠れているかを見つけること」に尽きる。次節で使う「注文Excel風テーブル」`order_flat` を例に、隠れている関数従属を先に書き出しておく。 ```text 注文ID → 注文日時、顧客メール 顧客メール → 顧客地域 商品名 → カテゴリ名、単価 (注文ID, 商品名) → 数量 ``` この4本の矢印が、これから行う正規化のすべての判断根拠になる。「注文ID → 顧客地域」も成り立つように見えるが、これは「注文ID → 顧客メール → 顧客地域」という2段階の間接的な従属（推移従属）であり、直接の従属ではないことに注意する。この区別が3NFの判定で効いてくる。 ### 4.3 わざと崩したテーブルを作る — `order_flat` 正規化前の状態を体感するために、注文明細・顧客・商品の情報をすべて1枚に詰め込んだ「注文Excel風テーブル」を作る。実務でExcelやCSVからテーブルを起こすと、しばしばこの形になる。 ```sql DROP TABLE IF EXISTS order_flat; CREATE TABLE order_flat ( order_id bigint NOT NULL, ordered_at timestamptz NOT NULL, customer_email text NOT NULL, customer_region text NOT NULL, product_name text NOT NULL, category_name text NOT NULL, unit_price numeric(10,2) NOT NULL, qty int NOT NULL ); INSERT INTO order_flat (order_id, ordered_at, customer_email, customer_region, product_name, category_name, unit_price, qty) VALUES (1001, '2026-01-05 09:12+09', 'sato@example.com', '関東', 'ノートPC', '家電', 98000.00, 1), (1001, '2026-01-05 09:12+09', 'sato@example.com', '関東', 'マウス', '家電', 2500.00, 2), (1002, '2026-01-06 14:03+09', 'suzuki@example.com', '近畿', 'ノートPC', '家電', 98000.00, 1), (1003, '2026-01-08 10:47+09', 'sato@example.com', '関東', 'キーボード', '家電', 7000.00, 1), (1004, '2026-01-09 18:30+09', 'tanaka@example.com', '九州', 'マグカップ', 'キッチン用品', 1200.00, 3); ``` 意図的に主キーを定義していない。この表の1行を一意に識別できる単位は「注文」でも「商品」でもなく、その組み合わせ `(order_id, product_name)` でしかない。1つの表の中に「注文」「顧客」「商品」「カテゴリ」という4つの異なる実体が混ざっているからこそ、単一の自然な主キーが存在しないのであり、これ自体が設計の悪さの兆候である。 ```sql SELECT * FROM order_flat ORDER BY order_id, product_name; ``` ```text order_id | ordered_at | customer_email | customer_region | product_name | category_name | unit_price | qty ----------+------------------------+----------------------+-----------------+--------------+----------------+------------+----- 1001 | 2026-01-05 09:12:00+09 | sato@example.com | 関東 | ノートPC | 家電 | 98000.00 | 1 1001 | 2026-01-05 09:12:00+09 | sato@example.com | 関東 | マウス | 家電 | 2500.00 | 2 1002 | 2026-01-06 14:03:00+09 | suzuki@example.com | 近畿 | ノートPC | 家電 | 98000.00 | 1 1003 | 2026-01-08 10:47:00+09 | sato@example.com | 関東 | キーボード | 家電 | 7000.00 | 1 1004 | 2026-01-09 18:30:00+09 | tanaka@example.com | 九州 | マグカップ | キッチン用品 | 1200.00 | 3 (5 rows) ``` ここから正規形の判定に入る。**正規形**（normal form。`1NF` などの **NF** はこの Normal Form の略である）とは、テーブルがどの更新異常を防げる状態にあるかを表す段階のことで、1NF・2NF・3NF・BCNF の順に条件が厳しくなる。条件は入れ子になっており、上位の正規形を満たす表は下位の正規形もすべて満たす。 このテーブルはすでに**第1正規形（1NF）**を満たしている。1NFの要件は「すべての属性値が原子的であること（1つのセルに1つの値しか入らない）」であり、この表は1行=1注文明細で、どのセルにも単一の値しか入っていない。もしこれが「1行=1注文、商品はカンマ区切りで1セルに詰め込む」という表だったら（例: `商品リスト = 'ノートPC x1, マウス x2'`）、それは1NF違反であり、SQLでの検索・集計・整合性チェックがほぼ不可能になる。多対多の関係を中間テーブルなしで表そうとする失敗は、たいていこの1NF違反の形で現れる。 `order_flat` は1NFを満たしていても、次節で見るように深刻な更新異常を抱えている。 ### 4.4 第2正規形（2NF）— 候補キーの一部にしか従属しない属性を切り出す `order_flat` の候補キーは `(order_id, product_name)` である。4.2節で書き出した関数従属を見直すと、`注文日時` と `顧客メール` は候補キーの一部である `order_id` だけで決まってしまい、`product_name` は関係ない。同様に `カテゴリ名` と `単価` は `product_name` だけで決まり、`order_id` は関係ない。このように、候補キーの一部だけに従属する非キー属性がある状態を**部分従属**と呼ぶ。 **第2正規形（2NF）**は「1NFであり、かつすべての非キー属性が候補キー全体に完全従属する（部分従属が存在しない）」ことを要求する。部分従属を解消するには、その部分キーが決定する属性を別テーブルに切り出せばよい。 ```sql CREATE TABLE orders_2nf AS SELECT DISTINCT order_id, ordered_at, customer_email, customer_region FROM order_flat; ALTER TABLE orders_2nf ADD PRIMARY KEY (order_id); CREATE TABLE products_2nf AS SELECT DISTINCT product_name, category_name, unit_price FROM order_flat; ALTER TABLE products_2nf ADD PRIMARY KEY (product_name); CREATE TABLE order_items_2nf AS SELECT order_id, product_name, qty FROM order_flat; ALTER TABLE order_items_2nf ADD PRIMARY KEY (order_id, product_name); ALTER TABLE order_items_2nf ADD FOREIGN KEY (order_id) REFERENCES orders_2nf(order_id), ADD FOREIGN KEY (product_name) REFERENCES products_2nf(product_name); ``` これで `order_flat` の全情報が3つの表に無損失で分解できた（`order_items_2nf` を経由して元の表と同じ内容をJOINで復元できることは、後の課題で確認する）。ただし `orders_2nf` にはまだ `customer_region` が残っており、次節で扱う問題が残っている。 ### 4.5 第3正規形（3NF）— 推移従属を切り出す `orders_2nf` の非キー属性を見ると、`customer_region` は候補キー `order_id` に完全従属してはいるが、その従属は「`order_id → customer_email → customer_region`」という2段階を経ている。`customer_email` はキーではない非キー属性であり、それがさらに別の非キー属性 `customer_region` を決定している。このような、非キー属性が別の非キー属性を介して間接的にキーに従属する関係を**推移従属**と呼ぶ。 **第3正規形（3NF）**は「2NFであり、かつすべての非キー属性が候補キーに推移従属しない」ことを要求する。正確には、任意の関数従属 `X → A` について「`X` がスーパーキーである」か「`A` が素属性（いずれかの候補キーの一部）である」のどちらかが成り立つことが条件である。`customer_email → customer_region` はどちらの条件も満たさないため、3NF違反になる。 ```sql CREATE TABLE customers_3nf AS SELECT DISTINCT customer_email, customer_region FROM orders_2nf; ALTER TABLE customers_3nf ADD PRIMARY KEY (customer_email); ALTER TABLE orders_2nf DROP COLUMN customer_region; ALTER TABLE orders_2nf ADD FOREIGN KEY (customer_email) REFERENCES customers_3nf(customer_email); ``` `products_2nf` についても同様に、カテゴリを独立させておく。厳密なFDの観点では、`category_name` はこれ以上何も決定しないため、`product_name → category_name` は推移従属ではなく、`products_2nf` はすでに3NFを満たしている。それでもカテゴリを独立テーブルに切り出す理由は、正規形の理論的な要請というより実務上の設計判断である。カテゴリという概念を独立させておけば、カテゴリ名の変更を1箇所で完結させられ、表記ゆれ（「家電」と「家電製品」のような不一致）による更新異常を未然に防げる。「同じ事実は1箇所にだけ書く」という正規化の精神の延長線上にある判断だと考えればよい。 ```sql CREATE TABLE categories_3nf AS SELECT DISTINCT category_name FROM products_2nf; ALTER TABLE categories_3nf ADD PRIMARY KEY (category_name); ALTER TABLE products_2nf ADD FOREIGN KEY (category_name) REFERENCES categories_3nf(category_name); ``` ### 4.6 BCNF — すべての決定項が候補キーになっているか **Boyce-Codd正規形（BCNF）**は3NFをさらに厳しくした基準で、「すべての関数従属 `X → A` について `X` がスーパーキーである」ことを要求する。3NFでは `A` が素属性であれば例外として許容されたが、BCNFにはその例外がない。 ここまで分解した `customers_3nf`（決定項: `customer_email`、候補キーそのもの）、`categories_3nf`（決定項: `category_name`、候補キーそのもの）、`products_2nf`（決定項: `product_name`、候補キーそのもの）、`orders_2nf`（決定項: `order_id`、候補キーそのもの）、`order_items_2nf`（決定項: `(order_id, product_name)`、候補キーそのもの）は、どの表もすべての決定項が候補キーになっている。つまりこの分解はすでにBCNFを満たしている。 3NFとBCNFが分かれるのは、1つの表に複数の候補キーが重なり合う、やや特殊なケースに限られる。参考として、教科書でよく使われる典型例を挙げる。 | 学生 | 科目 | 担当教員 | |---|---|---| | 山田 | 数学 | 田中 | | 佐藤 | 数学 | 田中 | | 鈴木 | 英語 | 高橋 | ルールは「1人の教員は1科目だけを担当する（`担当教員 → 科目`）」「1つの科目は複数の教員が担当しうる」とする。候補キーは `(学生, 科目)` と `(学生, 担当教員)` の2つ存在し、`科目` は `(学生, 科目)` の一部なので素属性である。したがって `担当教員 → 科目` というFDは「`科目` が素属性」という条件を満たし3NFはクリアするが、`担当教員` 自体はスーパーキーではないためBCNFには違反する。「田中先生は数学を担当する」という事実が、田中先生の生徒の数だけ行に重複しており、担当科目が変われば全行を直さなければならない。共通スキーマのようなEC注文モデルではこの手の重なり合った候補キーはほとんど出てこないため、BCNFの検証は「決定項が候補キーになっているか一つずつ確認する」という機械的なチェックで済むことが多い。 分解結果をER図としてまとめると次のようになる。 ```text CATEGORIES ---< PRODUCTS ---< ORDER_ITEMS >--- ORDERS >--- CUSTOMERS ``` | 関係（1 → 多） | 読み方 | |---|---| | `CUSTOMERS` → `ORDERS` | 1人の顧客が0件以上注文する | | `ORDERS` → `ORDER_ITEMS` | 1注文が1件以上の明細を持つ | | `PRODUCTS` → `ORDER_ITEMS` | 1商品が0件以上の明細に登場する | | `CATEGORIES` → `PRODUCTS` | 1カテゴリが0件以上の商品を持つ | 各表の列は次のとおり（PK = 主キー、UK = 一意キー、FK = 外部キー）。 | 表 | 列 | |---|---| | `CUSTOMERS` | `id bigint` **PK** / `email text` **UK** / `region text` | | `ORDERS` | `id bigint` **PK** / `customer_id bigint` **FK → CUSTOMERS** / `status text` / `ordered_at timestamptz` | | `ORDER_ITEMS` | `order_id bigint` **PK, FK → ORDERS** / `product_id bigint` **PK, FK → PRODUCTS** / `qty int` / `unit_price numeric` | | `PRODUCTS` | `id bigint` **PK** / `category_id bigint` **FK → CATEGORIES** / `name text` / `price numeric` | | `CATEGORIES` | `id bigint` **PK** / `name text` **UK** | `order_items` に `unit_price` が含まれている点に注意する。ここまでの分解では単価は `products` 側（`product_name → unit_price`）に置いていたが、共通スキーマの `order_items.unit_price` はそれとは別の事実、すなわち「注文した**時点**の価格」を保持する列である。`products.price` は「現在の価格」を表す。同じ列名・同じ型に見えても意味する事実が異なるため、これは正規化の違反ではなく意図的な設計である。もし過去の注文金額を常に `products.price` から計算する設計にすると、値上げのたびに過去の注文合計が変わってしまうという、正規化とは別種の不整合が起きる。価格の時系列については[第6回](06-modeling.md)で `product_prices` を使った有効期間モデルとして扱う。 ### 4.7 主キーの選び方 — 自然キー vs サロゲートキー ここまでの分解は `customer_email` や `product_name` のような、業務上の意味を持つ属性（**自然キー**）を主キーとして進めてきた。関数従属の議論そのものは、主キーが自然キーかサロゲートキーかを問わない。しかし物理的なテーブル設計では、共通スキーマのように `bigint GENERATED BY DEFAULT AS IDENTITY` の**サロゲートキー**を主キーに採用することが多い。 | 観点 | 自然キー（例: email） | サロゲートキー（例: id） | |---|---|---| | 意味 | 業務上の識別子そのもの | 単なる連番。業務的な意味を持たない | | 変更 | 本人都合で変わりうる（メール変更等） | 原則不変。参照側のFKを直す必要がない | | 一意性 | 業務ルールとして保証されているとは限らない | DB側で機械的に一意（IDENTITY） | | 結合コスト | 型・長さによっては重くなりうる | `bigint` で固定長、索引・結合が軽い | | 可読性 | ログやURLに出しても意味が分かる | 単体では何も語らない | 顧客の `email` は「業務上ほぼ一意で、認証にも使う」意味のある自然キーの候補だが、それでも共通スキーマでは主キーを `id`（サロゲート）にし、`email` には別途 `UNIQUE` 制約を張っている。メールアドレスは変更されうるため、これを主キーにすると変更のたびに参照側の外部キーをすべて連鎖更新する必要があり運用が煩雑になる。一方 `products.name`（商品名）はそもそも業務的に一意である保証がない（同名の別商品がカタログに存在しうる）ため、自然キーの候補にすらならない。共通スキーマの `products.name` にUNIQUE制約が付いていないのは、この「商品名の重複を許容する」という設計判断の表れである。 サロゲートキーを選ぶときにもっとも起きやすい失敗は、**自然キー側のUNIQUE制約を張り忘れること**である。主キーが一意なのは `id` だけであり、`id` をサロゲートにしたからといって `email` の一意性が自動的に保証されるわけではない。 ```sql -- 失敗例: UNIQUE を付け忘れた顧客テーブル DROP TABLE IF EXISTS customers_bad; CREATE TABLE customers_bad ( id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, email text NOT NULL, region text NOT NULL ); INSERT INTO customers_bad (email, region) VALUES ('sato@example.com', '関東'); INSERT INTO customers_bad (email, region) VALUES ('sato@example.com', '関東'); ``` ```text INSERT 0 1 INSERT 0 1 ``` 同じメールアドレスの顧客が2件登録できてしまう。`id` は異なるので主キー制約には引っかからない。アプリケーション側で「メールアドレスで顧客を検索して1件だけ返ってくる」ことを前提にしたコードがあれば、この時点で静かに壊れる。 ```sql SELECT id, email FROM customers_bad WHERE email = 'sato@example.com'; ``` ```text id | email ----+------------------- 1 | sato@example.com 2 | sato@example.com (2 rows) ``` 修正は `UNIQUE` 制約の追加だが、すでに重複データが存在する場合はそのままでは追加できない。 ```sql ALTER TABLE customers_bad ADD CONSTRAINT uq_customers_bad_email UNIQUE (email); ``` ```text ERROR: could not create unique index "uq_customers_bad_email" DETAIL: Key (email)=(sato@example.com) is duplicated. ``` 制約を追加する前に、重複行をどちらか一方に統合してから制約を張る必要がある。設計段階で「サロゲートキーを主キーにしたら、対応する自然キーにUNIQUEを張ったか」を必ずセットで確認する習慣をつけるべきである。 ### 4.8 外部キーの役割 — 分解した表の整合性を守る `order_flat` を複数の表に分解できたのは、分解後もJOINで元の情報を復元できるからである。しかしそれは「`order_items.product_id` が必ず実在する `products.id` を指している」という前提があってはじめて成り立つ。**外部キー（FK）制約**は、この参照整合性をデータベース自身に強制させる仕組みである。 ```sql -- 存在しない商品を明細に登録しようとすると拒否される INSERT INTO order_items (order_id, product_id, qty, unit_price) VALUES (1, 999999999, 1, 100.00); ``` ```text ERROR: insert or update on table "order_items" violates foreign key constraint "order_items_product_id_fkey" DETAIL: Key (product_id)=(999999999) is not present in table "products". ``` FKがなければ、1枚のテーブルに全部の情報を詰め込んでいたときには「存在しない商品」という状態そのものが表現できなかったはずのバグが、複数テーブルに分解した瞬間に発生しうる。正規化によってテーブルを分割するなら、FKによる整合性の担保は分割とワンセットで考える必要がある。FKの削除時の挙動（`ON DELETE CASCADE` など）や、その他の制約オプションの詳細は[第5回](05-datatypes-constraints.md)で扱う。 ### 4.9 NULLをどう設計するか 正規化によって「1つの表は1つの実体だけを表す」という形に近づけると、NULLを許可すべき列も見えやすくなる。共通スキーマの中でも、NULLの意味は列ごとに異なる。 - `customers.region` は `NOT NULL`。地域はすべての顧客が必ず持つべき情報であり、NULLを許すと「地域不明」なのか「入力し忘れ」なのかが区別できなくなり、地域別集計にNULLが紛れ込む。 - `product_prices.valid_to` は `NULL` を許容する。ここでのNULLは欠測ではなく「現在も有効（終了日が未定）」という積極的な意味を持つ、合法的なNULLである。有効期間モデルの詳細は[第6回](06-modeling.md)で扱う。 - `events.customer_id` は `NULL` を許容する。未ログインユーザーの行動ログのように、顧客を特定できないイベントも記録したいという業務要件があるため、`NOT NULL` にはできない。詳しくは[第13回](13-scaling.md)で扱う。 NULLを許可する列が増えてきたら、その列が表す事実が本当にそのテーブルの主キーに対して常に存在するものかを疑うとよい。「特定の状態のときだけ意味を持つ属性」は、正規化の観点からも別テーブルに切り出したほうが自然なことが多い。安易にNULL許容な列を増やすのではなく、まず「その事実がない行は本当にこのテーブルに存在すべきか」を検討するのが正規化の考え方である。 ### 4.10 正規形と防ぐ更新異常の対応表 | 正規形 | 要件（一言） | 防ぐ更新異常（`order_flat` での具体例） | |---|---|---| | 1NF | 属性値が原子的（繰り返しグループ・複数値の詰め込みがない） | 1セルに複数の事実を詰め込むことによる検索不能・集計不能を防ぐ（多対多を中間テーブルなしで表す失敗の元） | | 2NF | 1NF + 候補キーの一部にしか従属しない属性がない（部分従属の排除） | 挿入異常（注文していない商品や、明細のない注文だけを表現できない）、キーの一部だけを見て他の属性を誤って解釈する矛盾を防ぐ | | 3NF | 2NF + 非キー属性同士の推移従属がない | 更新異常（`customer_region` のような事実が複数行に重複し、1箇所だけ更新すると矛盾する）、削除異常（唯一の注文行を消すと顧客の存在自体が失われる）を防ぐ | | BCNF | すべての決定項が候補キーである | 3NFでは防ぎきれない、複数の候補キーが重なり合うケース特有の更新異常（4.6節の学生・科目・教員の例）を防ぐ | ## ハンズオン 以下は `order_flat` を実際に手元で作り、異常を起こし、分解し、同じ異常が起きないことを確認する一連の演習である。4.3節の `CREATE TABLE order_flat` と `INSERT` を先に実行してから進める。 ### 課題1: 非正規化テーブルで更新異常を実際に起こす やること: `order_flat` に対して次の3つの操作を行い、それぞれどんな問題が起きるか観察する。 1. 佐藤さん（`sato@example.com`）の引っ越しにともない、地域を更新する（1行だけ更新する） 2. まだ何も注文していない新規会員（`yamada@example.com`）を登録しようとする 3. 鈴木さん（`suzuki@example.com`）の唯一の注文をキャンセル（削除）する **想定解答** 更新異常: ```sql UPDATE order_flat SET customer_region = '中部' WHERE order_id = 1001; SELECT DISTINCT customer_email, customer_region FROM order_flat WHERE customer_email = 'sato@example.com' ORDER BY customer_region; ``` ```text customer_email | customer_region ---------------------+------------------ sato@example.com | 中部 sato@example.com | 関東 (2 rows) ``` `order_id = 1001` の行だけを更新した結果、同一人物のはずの佐藤さんの地域が「中部」と「関東」の2通り存在する状態になった。`order_id = 1003` の行を更新し忘れたことが原因である。これが**更新異常**である。 挿入異常: ```sql INSERT INTO order_flat (order_id, ordered_at, customer_email, customer_region) VALUES (1005, now(), 'yamada@example.com', '東北'); ``` ```text ERROR: null value in column "product_name" of relation "order_flat" violates not-null constraint DETAIL: Failing row contains (1005, 2026-..., yamada@example.com, 東北, null, null, null, null). ``` 商品情報を伴わない「会員登録だけ済ませた顧客」という状態が、この表の構造上そもそも表現できない。商品列を`NOT NULL`から外せば挿入自体は通るが、その場合は「商品もカテゴリも単価も分からない明細行」という意味不明なレコードを許すことになり、根本的な解決にならない。これが**挿入異常**である。 削除異常: ```sql DELETE FROM order_flat WHERE order_id = 1002; SELECT * FROM order_flat WHERE customer_email = 'suzuki@example.com'; ``` ```text (0 rows) ``` 「注文をキャンセルする」という操作をしただけなのに、鈴木さんという顧客が存在した事実そのものがテーブルから消え去った。これが**削除異常**である。 ### 課題2: 1NF→2NF→3NF→BCNFまで分解する やること: 4.4節・4.5節・4.6節のSQLを順に実行し、`order_flat` を `customers_3nf` / `categories_3nf` / `products_2nf` / `orders_2nf` / `order_items_2nf` に分解する。分解後、元の `order_flat` と同じ内容がJOINで復元できることを確認する。 **想定解答** 分解SQLは4.4節・4.5節のとおり実行する。復元できることの確認は次のクエリで行う。 ```sql SELECT o.order_id, o.ordered_at, c.customer_email, c.customer_region, p.product_name, p.category_name, p.unit_price, oi.qty FROM order_items_2nf oi JOIN orders_2nf o ON o.order_id = oi.order_id JOIN customers_3nf c ON c.customer_email = o.customer_email JOIN products_2nf p ON p.product_name = oi.product_name ORDER BY o.order_id, p.product_name; ``` この結果が4.3節で確認した `order_flat` の内容（5行）と完全に一致すれば、分解によって情報が失われていない（無損失分解）ことが確認できたことになる。行数が増減していたら、`DISTINCT` の対象列やJOINキーの選び方を見直す。 ### 課題3: 分解後は同じ異常が起きないことを確認する やること: 課題1と同じ3つの操作を、分解後のテーブルに対して行い、結果を比較する。 **想定解答** 更新: 佐藤さんの地域は `customers_3nf` に1行しかないため、1回のUPDATEで確実に反映される。 ```sql UPDATE customers_3nf SET customer_region = '中部' WHERE customer_email = 'sato@example.com'; SELECT customer_email, customer_region FROM customers_3nf WHERE customer_email = 'sato@example.com'; ``` ```text customer_email | customer_region ---------------------+------------------ sato@example.com | 中部 (1 row) ``` 矛盾する2つの地域が生まれる余地がない。 挿入: 注文と無関係に、顧客だけを登録できる。 ```sql INSERT INTO customers_3nf (customer_email, customer_region) VALUES ('yamada@example.com', '東北'); ``` ```text INSERT 0 1 ``` 商品情報を要求されないため、エラーにならない。 削除: 鈴木さんの注文を消しても、顧客情報は別テーブルに残る。 ```sql DELETE FROM order_items_2nf WHERE order_id = 1002; DELETE FROM orders_2nf WHERE order_id = 1002; SELECT * FROM customers_3nf WHERE customer_email = 'suzuki@example.com'; ``` ```text customer_email | customer_region ----------------------+------------------ suzuki@example.com | 近畿 (1 row) ``` 注文を削除しても顧客の存在は失われない。3つの操作すべてで、課題1で確認した異常が再現しないことが確認できた。 演習が終わったら、一時テーブルは削除しておく。 ```sql DROP TABLE IF EXISTS order_flat, customers_bad, orders_2nf, products_2nf, order_items_2nf, customers_3nf, categories_3nf; ``` ## よくあるつまずきと対処 | 症状 | 原因 | 対処 | |---|---|---| | サロゲートキー（`id`）を導入したのに、同じメールアドレスの顧客が複数登録できてしまう | 主キーが一意なのは `id` だけで、業務上の識別子（`email` 等）には別途 `UNIQUE` を張らないと一意性が保証されない | `ALTER TABLE ... ADD CONSTRAINT ... UNIQUE (email)` を必ずセットで検討する。既存データに重複があれば先に統合してから制約を追加する | | 「正規化するとJOINが増えて遅くなるから、最初から1枚の広いテーブルで設計しよう」と考えてしまう | 正規化はデータの整合性（更新異常の防止）のための設計であり、性能とは別の関心事という区別ができていない | 正規化はまず整合性のために行い、性能はインデックス設計（[第7回](07-storage-indexes-collation.md)）や実行計画（[第8回](08-explain.md)）を計測してから対処する。適切な索引があればJOIN自体は高コストではない | | `orders` テーブルに `product_ids` を配列やカンマ区切りテキストで持たせようとする | 多対多の関係を中間テーブルなしで1つの列に押し込もうとしている（1NF違反） | `order_items` のような連関（中間）テーブルを作り、多対多を「1対多」×2に分解する。第4.3節の1NFの議論を参照 | ## 到達度チェックリスト - [ ] 与えられた非正規化テーブルから、挿入・更新・削除異常を自分で再現できる - [ ] テーブルの属性間の関数従属を `X → Y` の矢印記法で書き出せる - [ ] 1NF・2NF・3NF・BCNFがそれぞれ何を要求し、どの更新異常を防ぐかを結びつけて説明できる - [ ] 部分従属と推移従属の違いを、具体的な属性の組で説明できる - [ ] 自然キーとサロゲートキーの長所・短所を挙げ、どちらを主キーに選ぶか根拠を示せる - [ ] サロゲートキーを主キーにした場合でも、自然キーにUNIQUE制約が必要な理由を説明できる - [ ] NULLを許可してよい列とそうでない列を、業務上の理由つきで判断できる - [ ] 自分で分解したテーブルが、共通スキーマ（`customers` / `categories` / `products` / `orders` / `order_items`）と一致することを確認できる ## 宿題（次回までの自習） 1. 自分で別の非正規化テーブルの例（例: 「社員の所属部署とプロジェクト参加を1枚にまとめた表」）を1つ考え、関数従属を書き出したうえで3NFまで分解する。 2. `events` テーブル（共通スキーマ）に、もし `customer_region` 列を直接持たせる設計にしていたらどんな更新異常が起きるかを説明し、実際にはそうなっていないことの意味を確認する。 3. `products.name` にUNIQUE制約を付けるべきかどうか、共通スキーマの立場から根拠つきで意見をまとめる（次回のデータ型・制約設計の伏線として）。 ## 次回への接続 本回で決めたのは「テーブルをどう分けるか」という論理的な形である。[第5回](05-datatypes-constraints.md)では、その形の中身、つまり各列にどの型・どのCHECK制約・どのNOT NULL/UNIQUE/外部キーの詳細オプションを与えて業務ルールを守らせるかを扱う。正規化で整えた骨格に、次回で制約という筋肉をつけていくと考えるとよい。 ## 参考 - PostgreSQL公式ドキュメント: Chapter 5. Data Definition, "5.4. Constraints"（`CHECK` / `NOT NULL` / `UNIQUE` / `PRIMARY KEY` / `FOREIGN KEY`） https://www.postgresql.org/docs/current/ddl-constraints.html - PostgreSQL公式ドキュメント: "CREATE TABLE"（`GENERATED ... AS IDENTITY` を含む列定義の文法） https://www.postgresql.org/docs/current/sql-createtable.html - PostgreSQL公式ドキュメント: Chapter 5. Data Definition, "5.3. Foreign Keys" https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-FK