論理設計・正規化を初心者向けに分かりやすく解説

論理設計の学習をする機会があったので、調べてみました。
ご指摘やアドバイスをいただけると嬉しいです。


## 要件定義
目的を決める
- どんなシステムを作りたいのか
- どんなデータを管理したいのか

## エンティティ（情報のグループ）を決める
どんなデータを保存するかを決める
- 生徒（名前、学年、IDなどの情報）
- クラブ（クラブ名、顧問の名前などの情報）
- 顧問（名前、電話番号、IDなどの情報）

## エンティティ間の関係（リレーションシップ）を決める
- 1対1：生徒IDと生徒の関係
- 多対1：生徒とクラブの関係
- 多対多：生徒が複数クラブに入っている場合

## 属性を決める
エンティティにどんな情報が入るかを決める
- 生徒エンティティの属性：生徒名、学年、生徒ID
- クラブエンティティの属性：クラブ名、顧問、クラブID
- 顧問エンティティの属性：顧問名、電話番号、顧問ID

## 正規化をする（データの整理）
データがぐちゃぐちゃにならないように、テーブルの構造をきれいに整理する
- 生徒とクラブ、顧問を別々のテーブルに分ける
- 生徒がどのクラブに入っているかを別のテーブルに保存（多対多の関係を管理するため）

## 制約を決める
データのルールを決める
- 生徒IDは重複してはならない
- 学年は必ず記入しなくてはならない

# 論理設計の具体例
### エンティティの決定
- 生徒テーブル（Students）
  - ID（主キー）
  - 生徒名
  - 学年
- クラブ
  - クラブID（主キー）
  - クラブ名
  - 顧問ID（外部キー）
- 顧問テーブル（Advusors）
  - 顧問ID（主キー）
  - 顧問名
  - 電話番号
- 入部リストテーブル（Memberships）
  - 生徒ID（外部キー）
  - クラブID（外部キー）
  ※ 複合主キー（生徒ID、クラブID）で一意性を保つ
  - 入部日
  - 役割
### リレーションシップの決定
- 生徒とクラブの関係は多対多（1人の生徒は複数のクラブに入ることができる）
- 中間テーブル「入部リスト」を使う（多対多の関係を整理するため）

# 正規化の手順
## 第1正規形（1NF）
テーブルの中のデータが繰り返しなしで、全ての情報が1つのセルに入っている状態にする
- テーブルの中に同じ情報が繰り返されないようにする
- 全ての列（カラム）は単一の値を持つ


### 1NF前

| 生徒ID | 生徒名 |　クラブ名 |クラブ名|
| :----: | :----: |:----: |:----: |
| 1 | 山田 |野球部 | サッカー部 |
| 2 | 田中 |サッカー部|
| 3 | 高橋 |テニス部|


### 1NF後

| 生徒ID | 生徒名 |　クラブ名 |
| :----: | :----: |:----: |
| 1 | 山田 |野球部|
| 1 | 山田 |サッカー部|
| 2 | 田中 |サッカー部|
| 3 | 高橋 |テニス部|

## 第2正規形（2NF）
1NFを満たしていて、部分的に依存（主キーの一部だけに依存）する情報がないことを目的とする
- 1NFの状態にした後、主キーの一部に依存している情報を別のテーブルに分ける
- 主キー全体に依存しているデータだけを1つのテーブルにまとめる
- 入部リストテーブルを導入することで生徒とクラブの関係を整理し、多対多を管理する

### 2NF前
顧問名が生徒IDに依存している状態

| 生徒ID | 生徒名 |　クラブ名 |顧問名|
| :----: | :----: |:----: |:----: |
| 1 | 山田 |野球部|鈴木|
| 1 | 山田 |サッカー部|佐藤|
| 2 | 田中 |サッカー部|佐藤|
| 3 | 高橋 |テニス部|小林|

### 2NF後
顧問名はクラブに依存しているべき
顧問名がクラブごとに管理されて、生徒テーブルは生徒情報だけ、クラブテーブルにはクラブ情報と顧問情報が入る
- 生徒テーブル（Students）

| 生徒ID | 生徒名 |学年|
| :----: | :----: |:----: |
| 1      | 山田   |5|
| 2      | 田中   |6|
| 3      | 高橋   |4|

<br>

- クラブテーブル（Clubs）

| クラブID | クラブ名 |顧問ID|
| :----: | :----: |:----: |
| 10 | 野球部 |100|
| 20 | サッカー部 |200|
|30|テニス部|300|

- 入部リストテーブル（Memberships）

| 生徒ID | クラブID |入部日|役割|
| :----: | :----: |:----: |:----: |
| 1 | 100| 2024-01-10 |部長|
| 1 | 200| 2024-02-15 |一般|
| 2 | 200 |2024-03-01 |会計|
| 3 | 300 |2024-05-10 |一般|

## 第3正規形（3NF）
2NFを満たしていて推移的依存をなくすことが目的
推移依存とは、ある情報が他の情報に依存していて、その情報がまた別の情報に依存していること

- 2NFを満たした後、推移的に依存している情報を他のテーブルに分ける
- 1つのテーブルに直接関係のあるデータだけを持たせる

### 3NF前
顧問名が顧問電話番号に依存している状態
顧問名を変更すると顧問電話番号も変更しなくてはならない
| 生徒ID | 生徒名 |　クラブ名 |顧問名|顧問電話番号|
| :----: | :----: |:----: |:----: | :----: |
| 1 | 山田 |野球部|鈴木|070-1234-5678|
| 2 | 田中 |サッカー部|佐藤|080-1234-5678|
| 3 | 高橋 |テニス部|小林|090-1234-5678|

### 3NF後
顧問名と顧問電話番号が分けられて、同じ電話番号が繰り返し登録されるのを防ぐことができる
顧問の電話番号が変わっても、1回の更新で済む
- 生徒テーブル（Students）

| 生徒ID | 生徒名 |
| :----: | :----: |
| 1      | 山田   |
| 2      | 田中   |
| 3      | 高橋   |
<br>

- クラブテーブル（Clubs）

| クラブID | クラブ名 |顧問ID|
| :----: | :----: |:----: |
| 10 | 野球部 |100|
| 20 | サッカー部 |200|
|30  |テニス部|300|
<br>

- 顧問テーブル（Advisors）

| 顧問ID | 顧問名 |顧問電話番号|
| :----: | :----: |:----: |
| 100 | 鈴木 |070-1234-5678|
| 200 | 佐藤 |080-1234-5678|
| 300 | 小林 |090-1234-5678|


## まとめ
- 第1正規形（1NF）：繰り返しがないようにデータを整理する
- 第2正規形（2NF）：部分的に依存している情報を分けて整理する
- 第3正規形（3NF）：推移的依存を解消して、直接関係のある情報だけをテーブルにまとめる

## テーブル結合（JOIN）
複数のテーブルから関連する情報を1つの結果として表示できる

生徒テーブルとクラブテーブルと顧問テーブルの結合

### 結合の目的
生徒がどのクラブに入っているか、そのクラブの顧問を一度に知ることができる

### 結合の流れ
- 生徒テーブルとクラブテーブルを結合して、どのクラブに生徒が所属しているかを確認
- クラブに対応する顧問テーブルを結合して、顧問の情報も一度に取得する

### SQLでの結合
必要な情報を取得するためにJOIN（結合）を使う
JOINを使って生徒テーブルとクラブテーブル、顧問テーブルを順番に結合する
1. 生徒とクラブを結びつけるために、生徒の生徒IDとクラブの生徒IDを一致させる
1. クラブの顧問IDと顧問テーブルの顧問IDを一致させて、顧問の情報を取得する

```
SELECT 
    Students.生徒名, 
    Clubs.クラブ名, 
    Advisors.顧問名, 
    Advisors.顧問電話番号,
    Memberships.役割,
    Memberships.入部日
FROM Memberships
JOIN Students ON Memberships.生徒ID = Students.生徒ID
JOIN Clubs ON Memberships.クラブID = Clubs.クラブID
JOIN Advisors ON Clubs.顧問ID = Advisors.顧問ID;
```
### クエリ実行結果

| 生徒名 | クラブ名 |顧問名|顧問電話番号|
| :----: | :----: |:----: |:----: |
| 山田 | 野球部| 鈴木 |070-1234-5678|
| 山田 | サッカー部| 佐藤 |080-1234-5678|
| 田中 | サッカー部 |佐藤|080-1234-5678|
| 高橋 | テニス部 |小林|090-1234-5678|