SQLの正規化について学ぶ

正規化は、データの重複を減らし、データベースの整合性を保つために行います。以下に、第一正規化、第二正規化、第三正規化を具体的な例とともに説明します。

1. 第一正規化 (1NF)

第一正規化の目標は、テーブルのすべての列が原子的な値（単一の値または、スカラー）を持ち、複数の値を持つことがないようにすることです。

例

以下のような学生情報テーブルを考えます：

学生ID	氏名	電話番号
1	山田太郎	090-1111-2222, 080-3333-4444
2	佐藤花子	070-5555-6666

このテーブルは1NFではありません。なぜなら、「電話番号」列が複数の値を持っているからです。

1NFにするための変更：

学生ID	氏名	電話番号
1	山田太郎	090-1111-2222
1	山田太郎	080-3333-4444
2	佐藤花子	070-5555-6666

ここでは、「電話番号」ごとに別の行を作成しました。これで、各セルが単一の値しか持たないため、1NFが満たされました。

2. 第二正規化 (2NF)

第二正規化は、テーブルが既に1NFであり、かつ、すべての非キー属性が主キー全体に完全に依存していることを保証するものです。部分関数従属とも言います。

例

以下のような「注文」テーブルを考えます：

注文ID	商品ID	商品名	価格	注文日
1	A001	ノート	500	2024-08-01
2	A002	ペン	100	2024-08-01
3	A001	ノート	500	2024-08-02

このテーブルでは、「商品名」と「価格」が「商品ID」に依存していますが、主キー（「注文ID」と「商品ID」の組み合わせ）全体に依存しているわけではありません。つまり、「商品名」と「価格」は「注文ID」に依存していないため、2NFではありません。

2NFにするための変更：

1. 商品情報テーブル：

商品ID	商品名	価格
A001	ノート	500
A002	ペン	100

1. 注文情報テーブル：

注文ID	商品ID	注文日
1	A001	2024-08-01
2	A002	2024-08-01
3	A001	2024-08-02

このようにして、非キー属性（「商品名」と「価格」）が主キーの一部にのみ依存している状態を解消しました。

3. 第三正規化 (3NF)

第三正規化は、テーブルが2NFであり、かつ非キー属性間に推移的関数従属がないことを保証します。つまり、非キー属性が他の非キー属性に依存してはならないということです。

例

以下のような「社員」テーブルを考えます：

社員ID	社員名	部署ID	部署名	部署所在地
1	田中一郎	D001	営業	東京
2	山田次郎	D002	開発	大阪
3	鈴木三郎	D001	営業	東京

このテーブルでは、「部署名」と「部署所在地」が「部署ID」に依存していますが、「部署名」が「部署所在地」にも依存していると見なせます。これは推移的関数従属の例であり、3NFではありません。
なぜなら、社員名をNULLもしくは空白（ブランク）だと、部署ID、部署名、部署所在地も同様にNULLもしくは空白（ブランク）となります。これを推移的関数従属といいます。

3NFにするための変更：

1. 部署情報テーブル：

部署ID	部署名	部署所在地
D001	営業	東京
D002	開発	大阪

1. 社員情報テーブル：

社員ID	社員名	部署ID
1	田中一郎	D001
2	山田次郎	D002
3	鈴木三郎	D001

これで、非キー属性間の依存関係を排除し、3NFが満たされました。

SQLの正規化には、第一正規化（1NF）、第二正規化（2NF）、第三正規化（3NF）に続くさらなる段階が存在します。それらにはボイス・コッド正規化（BCNF）、第四正規化（4NF）、第五正規化（5NF）があります。これらの正規化を適用することで、データの重複や矛盾をさらに減らし、データベースの設計を最適化することができます。以下、それぞれの正規化について具体的な例を使って説明します。

4. ボイス・コッド正規化 (BCNF)

ボイス・コッド正規化（BCNF）は、3NFのより厳しい形式です。BCNFは、各非キー属性が、ただ1つの候補キー（スーパーキー）にのみ完全に依存することを要求します。

例

以下の「学生とコース」テーブルを考えます：

学生ID	コースID	講師ID
1	C001	T001
2	C002	T002
1	C002	T002
3	C001	T001

このテーブルでは、複数の「学生ID」が同じ「コースID」に登録することができます。また、各「コースID」には特定の「講師ID」が関連付けられています。しかし、「講師ID」は「コースID」に依存しており、かつ「学生ID, コースID」の組み合わせが複数の候補キーとなるため、BCNFに違反しています。

BCNFにするための変更：

1. 学生コース登録テーブル：

学生ID	コースID
1	C001
2	C002
1	C002
3	C001

1. コース講師テーブル：

コースID	講師ID
C001	T001
C002	T002

これにより、すべての非キー属性がそれぞれの候補キーに対して完全に依存しており、BCNFが満たされました。

5. 第四正規化 (4NF)

第四正規化（4NF）は、多重値依存を排除することを目的としています。4NFでは、1つのテーブル内に同じ主キーに関連付けられた複数の独立した事実がないことを要求します。

例

以下の「プロジェクト割り当て」テーブルを考えます：

社員ID	プロジェクト	スキル
1	P001	Java
1	P002	Python
2	P001	SQL
2	P003	Excel

このテーブルでは、「社員ID」に対して「プロジェクト」と「スキル」という2つの独立した属性が関連付けられており、それぞれが多重値依存を持っています。4NFでは、このような多重値依存は許されません。

4NFにするための変更：

1. 社員プロジェクトテーブル：

社員ID	プロジェクト
1	P001
1	P002
2	P001
2	P003

1. 社員スキルテーブル：

社員ID	スキル
1	Java
1	Python
2	SQL
2	Excel

これにより、「社員ID」に対する「プロジェクト」と「スキル」の独立性が保たれ、多重値依存が解消されます。

6. 第五正規化 (5NF)

第五正規化（5NF）は、ジョイン（結合）依存を排除することを目的としています。5NFでは、すべてのジョインが損失なしに分解可能であることを要求します。

例

以下の「契約」テーブルを考えます：

契約ID	供給者ID	部品ID	顧客ID
1	S001	P001	C001
2	S002	P002	C001
3	S001	P002	C002
4	S002	P001	C003

このテーブルでは、「供給者ID」、「部品ID」、および「顧客ID」間に複雑な関係が存在します。これを5NFにするためには、すべてのジョインが損失なしに分解可能である必要があります。

5NFにするための変更：

1. 契約供給者テーブル：

契約ID	供給者ID
1	S001
2	S002
3	S001
4	S002

1. 契約部品テーブル：

契約ID	部品ID
1	P001
2	P002
3	P002
4	P001

1. 契約顧客テーブル：

契約ID	顧客ID
1	C001
2	C001
3	C002
4	C003

これにより、各関係が独立し、すべてのジョインが損失なしに行えるようになり、5NFが満たされました。

まとめ

• 第一正規化 (1NF): 各フィールドは単一の値を持ち、繰り返しグループがないようにします。
• 第二正規化 (2NF): 1NFを満たし、非キー属性は主キー全体に完全に依存する必要があります。
• 第三正規化 (3NF): 2NFを満たし、非キー属性間の推移的依存関係がないようにします。
• ボイス・コッド正規化 (BCNF): 3NFを満たし、各非キー属性が候補キーに完全に依存している。
• 第四正規化 (4NF): 多重値依存を排除し、1つのテーブルに複数の独立した事実がない。
• 第五正規化 (5NF): すべてのジョインが損失なしに分解可能であること。

これらの正規化のルールを使ってデータベースを設計することで、データの整合性を保ち、データの重複を減らし、データベースの保守性を向上させることができます。
また、これらの正規化を適用することで、データベースの設計をより効率的かつ保守しやすくすることができます。正規化の深いレベルに進むほど、データの重複や矛盾が減り、データベースの整合性が向上します。