【DB設計】三層スキーマと論理設計についてまとめる
はじめに
DB設計における3層スキーマの概念と論理設計についてまとめていこうと思います。
(初心者なので、間違えているところがあれば教えていただけると助かります)
三層スキーマとは
- 外部スキーマ(ビュー層)
- 概念スキーマ(論理層)
- 内部スキーマ(物理層)
これらを合わせて3層スキーマと呼びます。
DB設計におけるスキーマとは、「データベースの設計図」や「構造の定義」という意味です。
外部スキーマ
外部スキーマとは、個々のユーザーやアプリケーションが利用するデータの見方（ビュー）を表すスキーマのことです。ユーザーによって必要とする情報は異なるため、営業部門は顧客情報や受注データを参照し、経理部門は売上や支払情報を参照するといったように、それぞれに最適化されたビューを提供します。
概念スキーマ
概念スキーマとは、データベース全体の論理的構造を統一的に定義したスキーマのことです。外部スキーマと内部スキーマをつなぐ中核的な存在であり、データベース全体の一貫性を保証します。テーブルやエンティティ、そしてそれらの関係性を示すER図などは概念スキーマに該当します。この設計を行うことを論理設計と呼びます。
内部スキーマ
内部スキーマとは、データベースを物理的にどのように格納・処理するかを記述したスキーマのことです。この設計を物理設計と呼びます。
主キーと外部キーについて
ここで、主キーと外部キーについてまとめたと思います。
主キーとは
主キー（Primary Key）は、テーブルの中で各行を一意に識別するための列（または列の組み合わせ）です。一つのテーブルには1つの主キー制約しか持てませんが、その主キーが**複数の列から構成される（複合主キー）**こともあります。
主キーとして設定された列には、以下の2つの重要な制約があります。
-
一意性（Unique Constraint）→ 同じテーブル内で、主キーの値が重複することはありません。
-
非NULL性（NOT NULL Constraint）→ 主キーの列にNULL値（無効な値、データがない状態）を格納することはできません。
外部キーとは
外部キー(Foreign key)とは、あるテーブルの列（カラム）が、別のテーブルの主キーを参照するための仕組みです。外部キーの最も重要な役割は、「参照整合性」を保つことです。ここでいう参照整合性とは、親テーブルに存在しないデータを子テーブルが持つことが無いようにするための制約のことです。
外部キー制約が設定されている場合、参照整合性を守るために、通常は子テーブルのデータを先に削除してから親テーブルのデータを削除する必要があります。
ただし、外部キー定義時に ON DELETE CASCADEを指定している場合は、親を削除するとそれに紐づく子行も自動的に削除されます。
論理設計について
論理設計の流れは以下のようになってます。
- エンティティの抽出
- エンティティの定義
- 正規化
- ER図の作成
1. エンティティの抽出
エンティティとは、データベースで管理すべき対象（人・物・出来事など）を指します。
簡易的なTODOリストを例にして考えると、、エンティティの抽出は以下のようになると考えられます。
- ユーザー (User) → アプリを利用する人。タスク、リストはユーザーに紐づきます。
- リスト(list) → プライベート、仕事等。
- タスク (Task) → 実行すべき個々のTODO項目。
2. エンティティの定義
抽出したエンティティについて、属性（項目）や識別子（主キー）を明確にすることです。
１で抽出したエンティティに関係ありそうな属性を洗い出してみると・・・
- ユーザー → ユーザーID、メールアドレスなど
- リスト → リストID、リスト名、色など
- タスク → タスクID、タスク名、状態、作成日時など
3. 正規化
正規化とは、データを冗長なく整理し、矛盾や不整合を防ぐために表を分割・再構造化する作業のことです。原則第三正規形まで行えば十分とされています。
- 第1正規形 → 一つのセルの中には、一つの値しか含まないようにすることです。例えば下記のような状態だと、１つのセルの中に２つ以上の値が入っていては、RDBでは規則違反になってしまう・・・
| user_id | user_name | lists_with_tasks_and_status |
|---|---|---|
| 1 | Alice | プライベート:[買い物:TODO, 洗濯:In Progress], 仕事:[レポート作成:TODO, 会議準備:In Progress] |
| 2 | Bob | プライベート:[掃除:Completed] |
よって、１つのセルに対して１つの値が入るように行を追加していき、第1正規形に変換すると・・・
| user_id | user_name | list_name | task_name | status |
|---|---|---|---|---|
| 1 | Alice | プライベート | 買い物 | TODO |
| 1 | Alice | プライベート | 洗濯 | In Progress |
| 1 | Alice | 仕事 | レポート作成 | TODO |
| 1 | Alice | 仕事 | 会議準備 | In Progress |
| 2 | Bob | プライベート | 掃除 | Completed |
- 第2正規形 → 部分関数従属を排除し、主キー全体に従属するようにします。主キーの一部の列に対して従属する列がある場合、この関係を部分関数従属といいます。
第1正規形に変換したものでは、タスクごとにuser_nameが 繰り返し保存されていて冗長等の問題点があります。
部分関数従属を排除するために、ユーザーとタスクとリストを別テーブルに分けます。
Users テーブル（user情報を独立させる）
| user_id | user_name |
|---|---|
| 1 | Alice |
| 2 | Bob |
Lists テーブル（リスト情報を独立させる）
| list_id | user_id | list_name | list_color |
|---|---|---|---|
| 1 | 1 | プライベート | 青 |
| 2 | 1 | 仕事 | 赤 |
| 3 | 2 | プライベート | 青 |
Tasks テーブル（タスク情報）
| task_id | list_id | task_name | status |
|---|---|---|---|
| 1 | 1 | 買い物 | TODO |
| 2 | 1 | 洗濯 | In Progress |
| 3 | 2 | レポート作成 | TODO |
| 4 | 2 | 会議準備 | In Progress |
| 5 | 3 | 掃除 | Completed |
こうすることで、user_name → user_id に完全従属するようにし、Tasks テーブルは task_id を主キーにし、タスクごとに一意に管理できるようになりました。
- 第3正規形 → 推移的関数従属を排除し、非キー属性は主キーのみに従属させます。推移的関数従属とは、テーブル内に段階的な従属関係があることを指します。わかりやすくいうと、「A → B かつ B → C が成り立つとき、A → C」となる関係のことです。
今回は、将来statusに従属する属性（status_description、status_colorなど）を追加する可能性を考慮し、推移的関数従属を予防するためStatusテーブルに分離していきます。
よって、第3正規形に変換すると・・・
Users テーブル
| user_id | user_name |
|---|---|
| 1 | Alice |
| 2 | Bob |
Tasks テーブル
| task_id | list_id | task_name | status_id |
|---|---|---|---|
| 1 | 1 | 買い物 | 1 |
| 2 | 1 | 洗濯 | 2 |
| 3 | 2 | レポート作成 | 1 |
| 4 | 2 | 会議準備 | 2 |
| 5 | 3 | 掃除 | 3 |
Status テーブル
| status_id | status_name |
|---|---|
| 1 | TODO |
| 2 | In Progress |
| 3 | Completed |
Lists テーブル
| list_id | user_id | list_name | list_color |
|---|---|---|---|
| 1 | 1 | プライベート | 青 |
| 2 | 1 | 仕事 | 赤 |
| 3 | 2 | プライベート | 青 |
4. ER図の作成
エンティティとそれらの関係を図で表現したものがER図です。テーブル同士の関係性として、1対1,1対多、多対多があります。ER図は主に以下の要素で構成されます。
- エンティティ：データの集まりのこと
- アトリビュート：エンティティの中の属性情報のこと
- リレーション：エンティティ同士の関係を表現する線のこと
- カーディナリティ：リレーションの詳細を表現する記号のこと
先ほどの第3正規形に変換したものを考えてみると・・・
まとめ
- 3層スキーマは、外部スキーマ、概念スキーマ、内部スキーマで構成されている。
- 主キーとは、テーブル内で行を一意に識別するためのキー。重複不可・NULL不可。
- 外部キーとは、別テーブルの主キーを参照するためのキー。参照整合性を保証する。
- 論理設計については以下の手順で行う。
- エンティティの抽出
- エンティティの定義
- 正規化
- ER図の作成
- 正規化は「従属性を見抜くこと」で実現できる。これにより冗長性を排除し、整合性を保てる。
Discussion