正規化とは？
データベースの正規化とは、
「データの重複や矛盾を防ぐために、表（テーブル）の構造を整理すること」です。
例えば、社員の情報と部署の情報を1つの表に詰め込むと、
部署名を変えるたびに複数行を修正しなければならず、更新ミスが起こりやすくなります。
こうした「更新異常・削除異常・挿入異常」を防ぐために、
正規化を行って表を分割・整理します。
正規化が必要な理由
冗長なデータ構造の問題点
| 問題の種類 | 内容 |
|---|---|
| 更新異常 | 一部のデータだけ修正して他が古いままになる |
| 挿入異常 | 一部の情報がないとデータを登録できない |
| 削除異常 | 不要データを削除したら必要な情報まで消える |
例えば以下のような表があるとします
| 社員ID | 社員名 | 部署名 | 部署所在地 |
|---|---|---|---|
| 001 | 佐藤 | 営業部 | 東京 |
| 002 | 鈴木 | 営業部 | 東京 |
| 003 | 田中 | 開発部 | 大阪 |
営業部の所在地が東京から横浜に変わった時、全ての「営業部」の行を修正しなければなりません。もし1行でも修正漏れがあると、「営業部は東京と横浜の両方にある」という矛盾が発生します。これが更新異常です。
新しい部署「人事部」を追加したいが、まだ所属社員がいない場合、この表では社員がいないと部署を登録できない。部署情報だけを登録できないのは不便です。これが挿入異常です。
社員「田中（開発部）」が退職した場合、その行を削除すると、開発部という部署情報まで一緒に消えてしまいます。結果として、「開発部という部署が存在しない」ように見えてしまいます。これが削除異常です。
正規化の目的
→ これらの問題を解決し「1つの事実を1か所にだけ保存する」こと。
この考え方が、後の第1〜第3正規形につながります。
未正規化の状態
未正規化のテーブル
| 社員ID | 社員名 | 部署コード | 所属部署 | プロジェクトID | プロジェクト名 | プロジェクトID | プロジェクト名 |
|---|---|---|---|---|---|---|---|
| 001 | 佐藤 | E01 | 営業部 | P001 | A | P002 | B |
| 002 | 鈴木 | D02 | 開発部 | P003 | C | ||
| 003 | 田中 | E01 | 営業部 | P002 | B | P004 | D |
このテーブルを以降で正規化していきます。
第1正規形：繰り返しをなくす
第1正規形では、各列に1つの値しか入れないようにします。
繰り返しになっていた担当部署を行で分けました。
| 社員ID | 社員名 | 部署コード | 所属部署 | プロジェクトID | プロジェクト名 |
|---|---|---|---|---|---|
| 001 | 佐藤 | E01 | 営業部 | P001 | A |
| 001 | 佐藤 | E01 | 営業部 | P002 | B |
| 002 | 鈴木 | D02 | 開発部 | P003 | C |
| 003 | 田中 | E01 | 営業部 | P002 | B |
| 003 | 田中 | E01 | 営業部 | P004 | D |
第1正規形＝「繰り返しを行で分ける」
第2正規形：部分関数従属をなくす
第2正規形では、「主キーの一部にしか依存しない列」を分けます。
ここでは主キーが「社員ID＋プロジェクトID」ですが、「社員名」「部署コード」「所属部署」は社員IDに依存しており、「プロジェクト名」はプロジェクトIDに依存しています。この二つを分けます。
社員テーブル
| 社員ID | 社員名 | 部署コード | 部署名 |
|---|---|---|---|
| 001 | 佐藤 | E01 | 営業部 |
| 002 | 鈴木 | D02 | 開発部 |
| 003 | 田中 | E01 | 営業部 |
プロジェクトテーブル
| プロジェクトID | プロジェクト名 |
|---|---|
| P001 | A |
| P002 | B |
| P003 | C |
| P004 | D |
社員プロジェクトテーブル
| 社員ID | プロジェクトID |
| 001 | P001 |
| 001 | P002 |
| 002 | P003 |
| 003 | P002 |
| 003 | P004 |
第2正規形＝「主キーの一部に依存する列を切り離す」
第3正規形：推移的関数従属をなくす
第3正規形では、主キー以外の列同士の依存関係をなくします。
つまり、「非キー列が他の非キー列に依存している」状態を解消します。
第2正規形の社員テーブルの「部署名」は「社員名」→「部署コード」→「部署名」のように推移的に依存しているためいるため、部署情報を独立させます。
社員テーブル
| 社員ID | 社員名 | 部署コード（FK） |
|---|---|---|
| 001 | 佐藤 | E01 |
| 002 | 鈴木 | D02 |
| 003 | 田中 | E01 |
部署テーブル
| 部署コード | 部署名 |
|---|---|
| E01 | 営業部 |
| D02 | 開発部 |
プロジェクトテーブル
| プロジェクトID | プロジェクト名 |
|---|---|
| P001 | A |
| P002 | B |
| P003 | C |
| P002 | B |
| P004 | D |
社員プロジェクトテーブル
| 社員ID | プロジェクトID |
| 001 | P001 |
| 001 | P002 |
| 002 | P003 |
| 003 | P002 |
| 003 | P004 |
第3正規形＝「非キー列どうしの依存をなくす」
正規化まとめ
| 正規形 | 解消する問題 | キーとの関係 |
|---|---|---|
| 第1正規形 | 繰り返し | 列に単一の値 |
| 第2正規形 | 部分従属 | 複合キーの一部に依存 |
| 第3正規形 | 推移従属 | 非キー列に依存 |
最後に
これまで第２正規化と第３正規化の理解が曖昧でしたが、自分で具体例を考えてみると以前よりは理解できたような気がしています。
