第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に接続している状態で実行する想定で書く。

psql

-- 共通スキーマが揃っていることの確認（第1回の投入結果）
\dt

この回で新しく出てくる用語

正規形の判定はこれらの語の関係で決まる。本編で順に詳しく扱うが、先に一覧で押さえておく。 回をまたいで使う語は用語集にもまとめている。

本編

4.1 ER図の基本 — 実体・属性・関係を絵にする

ER図（Entity-Relationship Diagram）は、業務に登場する実体（エンティティ、例: 顧客・注文）と、実体同士の関係（リレーションシップ）を可視化する図である。テーブル定義だけを見ていると見落としがちな「1人の顧客が何件の注文を持てるか」「注文には必ず顧客が紐づくか」といった多重度（カーディナリティ）を、図にすると一目で確認できる。

よく使われる記法が「鳥の足（crow's foot）記法」で、線の端の記号が「1」または「多」、実線・破線が「必須（NOT NULL相当）」「任意（NULLを許容）」を表す。共通スキーマの customers と orders の関係を例にすると次のようになる。

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 を例に、隠れている関数従属を先に書き出しておく。

注文ID                → 注文日時、顧客メール
顧客メール             → 顧客地域
商品名                 → カテゴリ名、単価
(注文ID, 商品名)       → 数量

この4本の矢印が、これから行う正規化のすべての判断根拠になる。「注文ID → 顧客地域」も成り立つように見えるが、これは「注文ID → 顧客メール → 顧客地域」という2段階の間接的な従属（推移従属）であり、直接の従属ではないことに注意する。この区別が3NFの判定で効いてくる。

4.3 わざと崩したテーブルを作る — order_flat

正規化前の状態を体感するために、注文明細・顧客・商品の情報をすべて1枚に詰め込んだ「注文Excel風テーブル」を作る。実務でExcelやCSVからテーブルを起こすと、しばしばこの形になる。

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つの異なる実体が混ざっているからこそ、単一の自然な主キーが存在しないのであり、これ自体が設計の悪さの兆候である。

SELECT * FROM order_flat ORDER BY order_id, product_name;

 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であり、かつすべての非キー属性が候補キー全体に完全従属する（部分従属が存在しない）」ことを要求する。部分従属を解消するには、その部分キーが決定する属性を別テーブルに切り出せばよい。

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違反になる。

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箇所にだけ書く」という正規化の精神の延長線上にある判断だと考えればよい。

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図としてまとめると次のようになる。

CATEGORIES ---< PRODUCTS ---< ORDER_ITEMS >--- ORDERS >--- CUSTOMERS

各表の列は次のとおり（PK = 主キー、UK = 一意キー、FK = 外部キー）。

order_items に unit_price が含まれている点に注意する。ここまでの分解では単価は products 側（product_name → unit_price）に置いていたが、共通スキーマの order_items.unit_price はそれとは別の事実、すなわち「注文した時点の価格」を保持する列である。products.price は「現在の価格」を表す。同じ列名・同じ型に見えても意味する事実が異なるため、これは正規化の違反ではなく意図的な設計である。もし過去の注文金額を常に products.price から計算する設計にすると、値上げのたびに過去の注文合計が変わってしまうという、正規化とは別種の不整合が起きる。価格の時系列については第6回で product_prices を使った有効期間モデルとして扱う。

4.7 主キーの選び方 — 自然キー vs サロゲートキー

ここまでの分解は customer_email や product_name のような、業務上の意味を持つ属性（自然キー）を主キーとして進めてきた。関数従属の議論そのものは、主キーが自然キーかサロゲートキーかを問わない。しかし物理的なテーブル設計では、共通スキーマのように bigint GENERATED BY DEFAULT AS IDENTITY のサロゲートキーを主キーに採用することが多い。

顧客の email は「業務上ほぼ一意で、認証にも使う」意味のある自然キーの候補だが、それでも共通スキーマでは主キーを id（サロゲート）にし、email には別途 UNIQUE 制約を張っている。メールアドレスは変更されうるため、これを主キーにすると変更のたびに参照側の外部キーをすべて連鎖更新する必要があり運用が煩雑になる。一方 products.name（商品名）はそもそも業務的に一意である保証がない（同名の別商品がカタログに存在しうる）ため、自然キーの候補にすらならない。共通スキーマの products.name にUNIQUE制約が付いていないのは、この「商品名の重複を許容する」という設計判断の表れである。

サロゲートキーを選ぶときにもっとも起きやすい失敗は、自然キー側のUNIQUE制約を張り忘れることである。主キーが一意なのは id だけであり、id をサロゲートにしたからといって email の一意性が自動的に保証されるわけではない。

-- 失敗例: 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', '関東');

INSERT 0 1
INSERT 0 1

同じメールアドレスの顧客が2件登録できてしまう。id は異なるので主キー制約には引っかからない。アプリケーション側で「メールアドレスで顧客を検索して1件だけ返ってくる」ことを前提にしたコードがあれば、この時点で静かに壊れる。

SELECT id, email FROM customers_bad WHERE email = 'sato@example.com';

 id | email
----+-------------------
  1 | sato@example.com
  2 | sato@example.com
(2 rows)

修正は UNIQUE 制約の追加だが、すでに重複データが存在する場合はそのままでは追加できない。

ALTER TABLE customers_bad ADD CONSTRAINT uq_customers_bad_email UNIQUE (email);

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）制約は、この参照整合性をデータベース自身に強制させる仕組みである。

-- 存在しない商品を明細に登録しようとすると拒否される
INSERT INTO order_items (order_id, product_id, qty, unit_price)
VALUES (1, 999999999, 1, 100.00);

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回で扱う。

4.9 NULLをどう設計するか

正規化によって「1つの表は1つの実体だけを表す」という形に近づけると、NULLを許可すべき列も見えやすくなる。共通スキーマの中でも、NULLの意味は列ごとに異なる。

• customers.region は NOT NULL。地域はすべての顧客が必ず持つべき情報であり、NULLを許すと「地域不明」なのか「入力し忘れ」なのかが区別できなくなり、地域別集計にNULLが紛れ込む。
• product_prices.valid_to は NULL を許容する。ここでのNULLは欠測ではなく「現在も有効（終了日が未定）」という積極的な意味を持つ、合法的なNULLである。有効期間モデルの詳細は第6回で扱う。
• events.customer_id は NULL を許容する。未ログインユーザーの行動ログのように、顧客を特定できないイベントも記録したいという業務要件があるため、NOT NULL にはできない。詳しくは第13回で扱う。

NULLを許可する列が増えてきたら、その列が表す事実が本当にそのテーブルの主キーに対して常に存在するものかを疑うとよい。「特定の状態のときだけ意味を持つ属性」は、正規化の観点からも別テーブルに切り出したほうが自然なことが多い。安易にNULL許容な列を増やすのではなく、まず「その事実がない行は本当にこのテーブルに存在すべきか」を検討するのが正規化の考え方である。

4.10 正規形と防ぐ更新異常の対応表

ハンズオン

以下は 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）の唯一の注文をキャンセル（削除）する

想定解答

更新異常:

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;

   customer_email   | customer_region
---------------------+------------------
 sato@example.com    | 中部
 sato@example.com    | 関東
(2 rows)

order_id = 1001 の行だけを更新した結果、同一人物のはずの佐藤さんの地域が「中部」と「関東」の2通り存在する状態になった。order_id = 1003 の行を更新し忘れたことが原因である。これが更新異常である。

挿入異常:

INSERT INTO order_flat (order_id, ordered_at, customer_email, customer_region)
VALUES (1005, now(), 'yamada@example.com', '東北');

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から外せば挿入自体は通るが、その場合は「商品もカテゴリも単価も分からない明細行」という意味不明なレコードを許すことになり、根本的な解決にならない。これが挿入異常である。

削除異常:

DELETE FROM order_flat WHERE order_id = 1002;

SELECT * FROM order_flat WHERE customer_email = 'suzuki@example.com';

(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節のとおり実行する。復元できることの確認は次のクエリで行う。

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で確実に反映される。

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';

   customer_email   | customer_region
---------------------+------------------
 sato@example.com    | 中部
(1 row)

矛盾する2つの地域が生まれる余地がない。

挿入: 注文と無関係に、顧客だけを登録できる。

INSERT INTO customers_3nf (customer_email, customer_region) VALUES ('yamada@example.com', '東北');

INSERT 0 1

商品情報を要求されないため、エラーにならない。

削除: 鈴木さんの注文を消しても、顧客情報は別テーブルに残る。

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';

   customer_email    | customer_region
----------------------+------------------
 suzuki@example.com   | 近畿
(1 row)

注文を削除しても顧客の存在は失われない。3つの操作すべてで、課題1で確認した異常が再現しないことが確認できた。

演習が終わったら、一時テーブルは削除しておく。

DROP TABLE IF EXISTS order_flat, customers_bad,
  orders_2nf, products_2nf, order_items_2nf, customers_3nf, categories_3nf;

よくあるつまずきと対処

到達度チェックリスト

• 与えられた非正規化テーブルから、挿入・更新・削除異常を自分で再現できる
• テーブルの属性間の関数従属を 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回では、その形の中身、つまり各列にどの型・どの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