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  |

2. 注文情報テーブル：

| 注文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   | 開発     | 大阪       |

2. 社員情報テーブル：

| 社員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     |

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

| コース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        |

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

| 社員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     |

2. 契約部品テーブル：

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

3. 契約顧客テーブル：

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

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

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

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