リレーショナル データベース設計: DBMS

リレーショナル データベース設計: 総合ガイド

リレーショナル データベース設計は効果的なデータベース システムの基礎であり、冗長性を削減し、データの整合性を維持しながら、データを効率的に編成することに重点を置いています。この記事では、分解、正規化、関数の依存関係、キーについて徹底的に説明し、リレーショナル データベースの設計原則を完全に理解できるようにします。

---

リレーショナル データベース設計における分解

分解とは、冗長性を排除し、一貫性を向上させ、パフォーマンスを最適化するために、大きなリレーション (テーブル) をより小さく意味のあるリレーションに分割するプロセスです。これは正規化の重要な側面です。

分解の種類

1. 非可逆分解:

   • 分解されたリレーションを結合することによって元のテーブルを完全に再構築できない場合、分解は 損失を伴います。
   • これは、分解中に一部のデータまたは関係が失われた場合に発生します。
   • 例: 次の表を考えてみましょう。

    EmployeeID | ProjectID | ProjectManager
    ---------------------------------------
    E1         | P1        | M1
    E2         | P1        | M1
   

   これを次のように分解すると:

   • 表 1: 従業員 ID |プロジェクトID
   • 表 2: プロジェクト ID |プロジェクトマネージャー これらのテーブルを再結合すると、データの重複または不整合が生じ、損失のある分解が発生する可能性があります。

2. 可逆分解:

   • データを失わず、不整合が生じることなく、分解されたリレーションを結合することによって元のテーブルを完全に再構築できる場合、分解は ロスレスです。
   • これは、分解ですべての関数の依存関係が保持される場合、または主要な属性が分解された各関係に含まれる場合に達成されます。

---

機能の依存関係

機能依存関係 (FD) は、1 つの属性 (または属性のセット) の値が別の属性 (または属性のセット) の値を決定する関係における 2 つの属性間の関係を記述します。これは、リレーショナル データベースの設計と正規化における基本的な概念です。

意味：

X と Y をリレーション R 内の属性のセットとする。関数依存関係 X → Y は、R 内の任意の 2 つのタプル (行) について、タプルが X の値に一致する場合、 Y の値についても同意する必要があります。

• X: 行列式 (左側の属性)。
• Y: 依存 (右側の属性)。

例：

学生情報を格納するテーブルを考えてみましょう:

StudentID | Name    | Major
----------------------------
S1        | Alice   | CS
S2        | Bob     | EE
S3        | Alice   | CS

ここでは、StudentID → 名前、専攻です。StudentID は名前と専攻の両方を一意に決定するためです。

関数の依存関係のプロパティ:

1. 再帰性: Y が X の部分集合の場合、X → Y。
2. 拡張: X → Y の場合、XZ → YZ (両側に属性を追加すると依存関係が維持されます)。
3. 推移性: X → Y および Y → Z の場合、X → Z。

---

リレーショナル データベースのキー

キーは、テーブル内のレコードを一意に識別し、データの整合性を確保するために不可欠です。

キーの種類:

1. スーパーキー:

   • リレーション内のタプルを一意に識別できる 1 つ以上の属性のセット。
   • 例: EmployeeID と Name という属性を持つテーブルでは、{EmployeeID}、{EmployeeID, Name} がスーパーキーです。

2. 候補キー:

   • 最小限のスーパーキー。つまり、その適切なサブセットもスーパーキーではありません。
   • 例: {EmployeeID} がタプルを一意に識別できる場合、それは候補キーです。

3. 主キー:

   • タプルを一意に識別するためにデータベース設計者によって選択された候補キー。
   • 例: Employee テーブルの EmployeeID。

4. 外部キー:

   • あるテーブルの属性 (または属性のセット) が別のテーブルの主キーを参照し、テーブル間の関係を確立します。
   • 例: Employee テーブルのDepartmentID は、Department テーブルのDepartmentID を参照します。

5. 複合キー:

   • 2 つ以上の属性で構成される主キー。
   • 例: 学生登録のテーブル内の (StudentID, CourseID)。

6. 一意のキー:

   • 列 (または列の組み合わせ) 内のすべての値が一意であることを保証するキー制約。

---

正規化と正規形

正規化は、属性と関係を整理して冗長性と依存性を減らし、データの整合性を確保するプロセスです。これは、連続する正規形の基準を段階的に満たすことによって達成されます。

標準形 (包括的な概要)

第一正規形 (1NF)

定義:

次の基準を満たす場合、関係は 第一正規形 (1NF) であると言われます:

1. 原子性: すべての属性 (列) には原子値が含まれている必要があります。これは、各列の値が分割できず、さらに分解できないことを意味します。
2. 単一値エントリ: テーブルの各列には単一のデータ型の値が含まれている必要があり、どの列にもセット、リスト、配列を含めることはできません。
3. 行の一意性: 各行は一意である必要があります。つまり、テーブルには行を区別するための主キーが必要です。
4. 繰り返しグループの禁止: テーブルには、同じ属性 (Item1、Item2 など) に対して複数の列を含めることはできません。また、単一のセルに複数の値を格納することもできません。

説明:

• 原子値: 各セル内のデータは最も単純な形式である必要があります。たとえば、複数の項目を 1 つのセルに格納するのではなく、各項目が独自の行を占める必要があります。
• 繰り返しグループ: これは、複数の列または行が同じタイプのデータを表すため、テーブルが 1NF に準拠しなくなります。
• 主キー: 主キーにより、各行が一意に識別可能になります。これは、リレーショナル データベースの基本要件です。

例:

非準拠表 (1NF に含まれない):

 EmployeeID | ProjectID | ProjectManager
 ---------------------------------------
 E1         | P1        | M1
 E2         | P1        | M1

• Items 列には複数の値 (例: 「ペン、ノートブック」) が含まれているため、アトミック性に違反しています。
• アイテムは個別の行ではなく単一のセルに保存されるため、繰り返しグループが存在します。

準拠表 (1NF 内):

StudentID | Name    | Major
----------------------------
S1        | Alice   | CS
S2        | Bob     | EE
S3        | Alice   | CS

• ここでは、Items 列がアトミック値に分割されており、各項目が個別の行に表示されています。
• どのセルにも複数の値が含まれないため、アトミック性が確保されます。
• テーブルには繰り返しグループや配列がないため、1NF に準拠しています。

---

第 2 正規形 (2NF)

定義:

次の場合、関係は 第 2 正規形 (2NF) になります:

1. それはすでに 第一正規形 (1NF) になっています (つまり、多値グループや繰り返しグループはありません)。
2. すべての非主属性は、主キー全体に完全に機能的に依存します。

• 非プライム属性: 候補キーの一部ではない属性。
• 完全に機能的に依存: 非主属性は、複合主キーの一部ではなく全体に依存する必要があります。

説明:

• 部分依存は、非主属性が複合主キー全体ではなく、その一部のみに依存する場合に発生します。
• 2NF は、関係をより小さな関係に分解することで部分的な依存関係を排除します。これにより、非主属性が主キー全体または別の候補キーのみに依存するようになります。

この手順により、部分的な依存関係によって生じる冗長性が軽減され、データがより適切に整理されます。

例:

非準拠表 (2NF に含まれない):

学生のコース情報を格納するテーブルを考えてみましょう:

 EmployeeID | ProjectID | ProjectManager
 ---------------------------------------
 E1         | P1        | M1
 E2         | P1        | M1

• 複合主キー: (StudentID、CourseID)。
• 部分的な依存関係:
  • 講師と学科は主キー全体 (StudentID、CourseID) ではなく、CourseID のみに依存します。

プライム以外の属性 (講師と部門) は部分的に複合キーに依存しているため、これは 2NF に違反します。

準拠表 (2NF 内):

部分的な依存関係を削除するには、テーブルを 2 つの関係に分解します。

1. 学生コース表:

StudentID | Name    | Major
----------------------------
S1        | Alice   | CS
S2        | Bob     | EE
S3        | Alice   | CS

1. コース詳細表:

OrderID | Items
-------------------
1       | Pen, Notebook
2       | Pencil

---

第 3 正規形 (3NF)

定義:

次の場合、関係は 第 3 正規形 (3NF) になります:

1. これは 第 2 正規形 (2NF) です (つまり、部分的な依存関係はありません)。
2. 推移的な依存関係は存在しません。これは次のことを意味します。
   • 他の非プライム属性に依存する非プライム属性はありません。
   • 非プライム属性は、別の非プライム属性を介するのではなく、候補キーのみに依存する必要があります。

• 非プライム属性: 候補キーの一部ではない属性。
• 推移的な依存関係: 非プライム属性が別の非プライム属性を通じて候補キーに間接的に依存する依存関係。

説明:

3NF では、冗長性を減らし、データの一貫性を向上させるために、推移的な依存関係を排除します。

• 推移的な依存関係の例: A → B および B → C の場合、A → C は推移的な依存関係です。これは、C が A を通じて B に間接的に依存していることを意味します。
• B への変更により C の更新時に異常が発生する可能性があるため、このような依存関係により冗長性が生じます。

例:

非準拠表 (3NF に含まれない):

OrderID | Item
---------------
1       | Pen
1       | Notebook
2       | Pencil

候補キー: StudentID は各行を一意に識別します。

• 問題: HOD 属性は StudentID に直接依存するのではなく、学部に依存します。
  • StudentID → 学部 (直接の依存関係)。
  • 部門 → HOD (推移的依存関係)。
  • つまり、StudentID → HOD は推移的な依存関係です。

この構造は冗長性をもたらします。CS 部門の HOD が変更されると、複数の行を更新する必要があります。

準拠表 (3NF 内):

推移的な依存関係を解決するには、テーブルを 2 つの関係に分解します。

1. 学生部門表:

 EmployeeID | ProjectID | ProjectManager
 ---------------------------------------
 E1         | P1        | M1
 E2         | P1        | M1

1. 部門-HOD テーブル:

StudentID | Name    | Major
----------------------------
S1        | Alice   | CS
S2        | Bob     | EE
S3        | Alice   | CS

---

ボイス・コッド正規形 (BCNF)

定義:

次の場合、関係は ボイス-コッド正規形 (BCNF) になります:

1. それは 第 3 正規形 (3NF) です (つまり、部分的または推移的な依存関係は存在しません)。
2. すべての行列式は候補キーです。

• Determinant: 別の属性が機能的に依存する属性 (または属性のセット)。
• 候補キー: リレーション内の各タプルを一意に識別できる最小限の属性セット。

3NF と BCNF の主な違い:

• 3NF では、プライム以外の属性が候補キーに機能的に依存する依存関係がいくつか許可されていますが、BCNF では、すべての行列式が候補キーであることを保証することで、そのような異常を排除します。

説明:

BCNF は 3NF よりも厳密で、関係が 3NF を満たす可能性があるものの、BCNF に違反する依存関係によって生じる冗長性が依然として存在する状況に対処します。

BCNF が必要な場合:

• BCNF は、非候補キー属性によって候補キーの一部が決定され、冗長性や異常が生じる場合に必要です。

例:

非準拠表 (BCNF に含まれていない):

OrderID | Items
-------------------
1       | Pen, Notebook
2       | Pencil

機能の依存関係:

1. コースID → 講師
2. インストラクター→ルーム

候補キー: コースID

問題:

• 決定要因である Instructor は候補キーではなく、Room を決定します。
• すべての行列式が候補キーであるわけではないため、これは BCNF に違反します。

準拠表 (BCNF 内):

BCNF を実現するには、テーブルを 2 つのリレーションに分解します。

1. コース-インストラクター表:

OrderID | Item
---------------
1       | Pen
1       | Notebook
2       | Pencil

1. インストラクタールームテーブル:

StudentID | CourseID | Instructor | Department
----------------------------------------------
S1        | C1       | Dr. Smith  | CS
S2        | C2       | Dr. Jones  | EE
S1        | C2       | Dr. Jones  | EE

---

第 4 正規形 (4NF)

定義:

次の場合、関係は 第 4 正規形 (4NF) になります:

1. それは ボイス・コッド正規形 (BCNF) です (つまり、部分的、推移的、またはその他の異常はありません)。
2. 複数値の依存関係はありません。

• 多値依存関係 (MVD): テーブル内の 1 つの属性が複数の独立した属性セットを決定する場合、多値依存関係が存在します。言い換えれば、リレーションに互いに関連性のない 2 つ以上の独立した多値属性が含まれている場合、4NF に違反します。

説明:

4NF の主な目標は、多値の依存関係を排除することです。この依存関係は、レコードに直接関係はないものの、同じキーに依存しているために一緒に表示される 2 つ以上の独立した属性が含まれている場合に発生します。

• 同じ情報の複数のコピーが行内で繰り返されるため、この種の依存関係は冗長性をもたらします。
• リレーションを分解して MVD を削除することで、冗長性が排除され、データベースの一貫性が向上します。

主要コンセプト:

• 4NF では、リレーションには候補キーに依存する 2 つ以上の多値属性があってはなりません。テーブルを適切に分解して、複数値の依存関係をそれぞれ削除する必要があります。

例:

非準拠表 (4NF に含まれない):

学生、学生が受講するコース、参加しているクラブに関する情報を保存するテーブルについて考えてみましょう。

 EmployeeID | ProjectID | ProjectManager
 ---------------------------------------
 E1         | P1        | M1
 E2         | P1        | M1

候補キー: StudentID

複数値の依存関係:

• StudentID はコースのセットとクラブのセットの両方を決定で​​きますが、これらのセットは互いに独立しています。
  • StudentID → {Courses} (StudentID と Course の間の複数値の依存関係)
  • StudentID → {Clubs} (StudentID と Clubs の間の複数値の依存関係)

StudentID がコースとクラブの両方を独立して決定するため、この表は 4NF に違反します。これにより、同じ StudentID がコースとクラブの異なる組み合わせで複数回繰り返されるため、冗長性が生じます。

準拠表 (4NF 内):

テーブルを 4NF に準拠させるには、テーブルを 2 つのテーブルに分解して複数値の依存関係を排除する必要があります。

1. 学生コース表:

StudentID | Name    | Major
----------------------------
S1        | Alice   | CS
S2        | Bob     | EE
S3        | Alice   | CS

1. 学生クラブテーブル:

OrderID | Items
-------------------
1       | Pen, Notebook
2       | Pencil

現在、2 つの複数値の依存関係は個別に処理されます。

• 学生-コース テーブルには、学生と学生が受講するコース間の関係が保存されます。
• Student-Club テーブルには、学生と学生が参加しているクラブとの関係が保存されます。

---

第 5 正規形 (5NF)

定義:

以下の場合、関係は 第 5 正規形 (5NF) であり、射影結合正規形 (PJNF) とも呼ばれます:

1. これは 第 4 正規形 (4NF) です (つまり、複数値の依存関係は存在しません)。
2. 情報を失わずにさらに分解することはできません。つまり、リレーションには 結合依存関係 や ロスレス結合分解 が含まれていません。

• 結合依存関係 (JD): 結合依存関係は、リレーションが 2 つ以上のリレーションに分解できる場合に発生しますが、それらを再び結合しても情報は失われません。言い換えれば、結合依存関係は、リレーションをサブリレーションに分割できるが、データを失うことなく元のリレーションを再構築できる場合に存在します。

説明:

5NF は 結合依存関係 を扱い、データを損失することなく、分解された部分からすべての情報を再構築できる方法でデータが分解されることを保証します。 5NF のリレーションは、その重要な結合依存関係がすべてその候補キーによって暗示されるように設計されています。

• ロスレス結合分解: リレーションがより小さなリレーションに分解されてから再結合されると、データを失うことなく元のリレーションを完全に再構築できます。情報の損失を引き起こさずにそれをさらに分解できない場合、その関係は 5NF に属します。
• 非自明な結合依存関係: 結合依存関係が自明に満たされない場合 (つまり、リレーションのすべての属性が結合依存関係に存在するわけではない)、結合依存関係は自明ではありません。

もっと簡単に言うと、5NF は不適切な分解によって生じる冗長性がないことを保証することに関係しています。これにより、リレーションが分解され、後で再び結合された場合でも、元のデータがすべて損失や曖昧さなしに利用できることが保証されます。

例:

非準拠表 (5NF に含まれていない):

さまざまなプロジェクトにどのサプライヤーがどの部品を供給しているかに関する情報を格納するテーブルを考えてみましょう。

 EmployeeID | ProjectID | ProjectManager
 ---------------------------------------
 E1         | P1        | M1
 E2         | P1        | M1

候補キー: (サプライヤー、部品、プロジェクト)

依存関係に参加:

上記の関係は、情報を失うことなくより小さな関係に分解できるため、結合依存関係があります。たとえば、テーブルは 3 つのサブリレーションに分解できます:

1. サプライヤー部品表:

 EmployeeID | ProjectID | ProjectManager
 ---------------------------------------
 E1         | P1        | M1
 E2         | P1        | M1

1. サプライヤー - プロジェクト テーブル:

StudentID | Name    | Major
----------------------------
S1        | Alice   | CS
S2        | Bob     | EE
S3        | Alice   | CS

1. パートプロジェクトテーブル:

OrderID | Items
-------------------
1       | Pen, Notebook
2       | Pencil

テーブルをこれらの小さなリレーションに分解しても、これら 3 つの小さなリレーションに対して自然結合を実行して元のテーブルを再作成できます。

ただし、この分解は可能であるため、5NF に違反します。 5NF に違反する理由は、特定のプロジェクトにどのサプライヤーがどの部品を供給したかに関する情報が複数の行にまたがって冗長に保存されるためです。同じ事実を複数回保存していますが、これは不必要であり、不一致が生じる可能性があります。

準拠表 (5NF 内):

5NF を達成するには、情報を失わずにリレーションをさらに分解できないようにテーブルを分解します。

1. サプライヤー-部品-プロジェクトテーブル:

OrderID | Item
---------------
1       | Pen
1       | Notebook
2       | Pencil

この形式では、データを失わずにそれ以上分解できないため、リレーションは 5NF になります。このテーブルは、元のテーブルと同じ情報を表しますが、より正規化された形式で表されており、各属性は候補キーに完全に依存しており、不適切な分解による冗長性は存在しません。

---

リレーショナル デザインの主要な概念

• 多値依存関係: 1 つの属性が複数の独立した値を決定する場合。
• 結合依存関係: 結合中に偽のタプルが作成されないようにします。
• 依存関係の保持: すべての関数の依存関係が分解後に保持されるようにします。

この包括的なガイドでは、リレーショナル データベース設計をマスターし、効率的で一貫性があり、異常のないデータベース システムを保証します。

以上がリレーショナル データベース設計: DBMSの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。