論理設計の学習をする機会があったので、調べてみました。
ご指摘やアドバイスをいただけると嬉しいです。
要件定義
目的を決める
- どんなシステムを作りたいのか
- どんなデータを管理したいのか
エンティティ（情報のグループ）を決める
どんなデータを保存するかを決める
- 生徒（名前、学年、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 |