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

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

---

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

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

分解の種類

1. 非可逆分解:

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

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

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

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

2. 可逆分解:

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

---

機能の依存関係

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

意味：

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. 一意のキー:

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

---

正規(guī)化と正規(guī)形

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

標準形 (包括的な概要)

第一正規(guī)形 (1NF)

定義:

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

1. 原子性: すべての屬性 (列) には原子値が含まれている必要があります。これは、各列の値が分割できず、さらに分解できないことを意味します。
2. 単一値エントリ: テーブルの各列には単一のデータ型の値が含まれている必要があり、どの列にもセット、リスト、配列を含めることはできません。
3. 行の一意性: 各行は一意である必要があります。つまり、テーブルには行を區(qū)別するための主キーが必要です。
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 正規(guī)形 (2NF)

定義:

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

1. それはすでに 第一正規(guī)形 (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 正規(guī)形 (3NF)

定義:

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

1. これは 第 2 正規(guī)形 (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

---

ボイス?コッド正規(guī)形 (BCNF)

定義:

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

1. それは 第 3 正規(guī)形 (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 を実現(xiàn)するには、テーブルを 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 正規(guī)形 (4NF)

定義:

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

1. それは ボイス?コッド正規(guī)形 (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

現(xiàn)在、2 つの複數値の依存関係は個別に処理されます。

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

---

第 5 正規(guī)形 (5NF)

定義:

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

1. これは 第 4 正規(guī)形 (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 になります。このテーブルは、元のテーブルと同じ情報を表しますが、より正規(guī)化された形式で表されており、各屬性は候補キーに完全に依存しており、不適切な分解による冗長性は存在しません。

---

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

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

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

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