sql_データモデリングと正規化

15データモデリングと正規化

DB設計において重要な、データモデリングと正規化について学びます。

データモデリングとは

データモデリングは、情報をデータベースに効果的に格納するための構造を設計するプロセスです。このプロセスでは、ビジネス要件を分析し、それに基づいてデータベースの構造を設計します。データモデリングは、データベースの設計と開発の基盤となります。

データモデリングの手順

一般的に、データモデリングは以下の手順で行います。

１．要件収集
・ビジネス要件やユーザーのニーズを理解し、どのデータを管理する必要があるかを特定します。
・ステークホルダーと協力して、必要なデータ要素、業務プロセス、ルールを収集します。

２．概念データモデル（Conceptual Data Model）

・データの高レベルな構造を設計します。
・エンティティ（実体）とそれらの間の関係を定義します。
・例：学生、コース、教師などのエンティティとその相互関係。

３．論理データモデル（Logical Data Model）
・概念データモデルを詳細化し、具体的なデータベースの構造を設計します。
・エンティティの属性を定義し、エンティティ間の関係を詳細化します。
・例：学生エンティティには、名前、年齢、学生IDなどの属性が含まれます。

４．物理データモデル（Physical Data Model）
・論理データモデルを実際のデータベースの設計に変換します。
・テーブル、カラム、データ型、インデックス、制約などを定義します。
・例：SQLを使用してテーブルを作成し、インデックスや制約を設定します。

正規化とは

正規化は、データベース設計におけるデータの冗長性を減らし、一貫性を確保するためのプロセスです。正規化は、データを正しく分割し、関係を適切に定義することで、データの重複を排除し、データの整合性を保ちます。

正規化は、論理データモデルの設計段階で行われます。この段階で、データの一貫性と効率的な管理を確保するために、データの構造を見直し、整理します。正規化は、データの重複や矛盾を避けるために重要です。

正規化のプロセス

正規化には、主に以下のプロセスがあります。各段階を正規形（Normal Form）と呼びます。

第一正規形（1NF）:

各フィールドが単一の値を持つようにテーブルを設計します。
重複する行や繰り返しグループを排除します。
例：複数の電話番号を持つ場合、電話番号ごとに新しい行を作成します。

第二正規形（2NF）:

第一正規形を満たし、すべての非キー属性が主キーに完全従属するようにテーブルを設計します。
部分従属性を排除します。
例：学生とコースの登録情報が一つのテーブルにある場合、学生テーブルとコーステーブルに分割し、登録情報を別のテーブルに移動します。

第三正規形（3NF）:

第二正規形を満たし、すべての非キー属性が主キーに直接従属するようにテーブルを設計します。
推移従属性を排除します。
例：学生の住所情報が学生テーブルに含まれている場合、住所情報を別のテーブルに移動します。

ボイスコッド正規形（BCNF）:

第三正規形を満たし、すべての決定子が候補キーであるようにテーブルを設計します。
例：複数の候補キーが存在する場合、それぞれの候補キーが完全な関係を持つように分割します。

データモデリングから正規化までの流れ

ここまで学校に関連するテーブルを作成してきたので、ここではもう1度学校に関する情報のデータモデリングから正規化までの流れを実践したいと思います。

1. 要件収集

まず、学校のデータベースに必要な要件を収集します。以下の要件を例として考えます：

学生情報（名前、年齢、学年、住所など）
教師情報（名前、年齢、専門科目など）
コース情報（コース名、コースコード、担当教師など）
学生の成績情報（学生、コース、成績）

2. 概念データモデル（Conceptual Data Model）

概念データモデルでは、主要なエンティティとそれらの関係を定義します。

エンティティ

学生（Student）
教師（Teacher）
コース（Course）
成績（Grade）

関係

学生は複数のコースを受講する
教師は複数のコースを担当する
成績は学生とコースの関係を表す

3. 論理データモデル（Logical Data Model）

次に、エンティティの属性を詳細化し、エンティティ間の関係を具体化します。

関係：

Student と Grade は 1対多（1人の学生が複数の成績を持つ）
Teacher と Course は 1対多（1人の教師が複数のコースを担当する）
Course と Grade は 1対多（1つのコースに複数の成績がある）

3－1．第一正規形

第一正規形は、テーブルの各フィールドが単一の値を持つことを要求します。

3－2．第二正規形

第二正規形は、第一正規形を満たし、すべての非キー属性が主キーに完全従属することを要求します。

studentsテーブル

coursesテーブル

enrollmentsテーブル

3－3．第三正規形

第三正規形は、第二正規形を満たし、すべての非キー属性が主キーに直接従属することを要求します。

studentsテーブルを以下のように分割する。

studentsテーブル

addressesテーブル

4. 物理データモデル（Physical Data Model）

物理データモデルでは、論理データモデルを実際のデータベース設計に変換します。以下のようにテーブルを定義します。

データ型（例：INT, VARCHAR, DATEなど）
主キー・外部キー制約
インデックスの設計
パフォーマンスやストレージ最適化の考慮
データベース製品に依存する構文や制約の適用

この段階で、システム上で実際に動作するスキーマが完成します。

まとめ

データモデリングと正規化は、データベース設計の重要なプロセスです。

データモデリングでは、ビジネス要件に基づいてエンティティやその関係性を定義し、正規化により冗長性を排除して整合性の高いデータ構造を構築します。

これにより、保守性と拡張性に優れたデータベース設計が可能となります。