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

要件定義

目的を決める

どんなシステムを作りたいのか
どんなデータを管理したいのか

エンティティ（情報のグループ）を決める

どんなデータを保存するかを決める

生徒（名前、学年、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

クラブテーブル（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	高橋

クラブテーブル（Clubs）

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

顧問テーブル（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を使って生徒テーブルとクラブテーブル、顧問テーブルを順番に結合する

生徒とクラブを結びつけるために、生徒の生徒IDとクラブの生徒IDを一致させる
クラブの顧問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