達人に学ぶDB設計　まとめ

Posted at 2024-06-11

はじめに

この度、業務でDB設計をすることになったので、この本を読んでみました。
この記事はこの本を読んだメモになります！

第1章：データベース設計の基礎

データ中心アプローチ（DOA）とプロセス中心アプローチ（POA）

DOA（データ中心アプローチ）:
データ中心アプローチは、システムの設計においてデータそのものを中心に考える手法です。データの構造や意味を明確に定義し、それに基づいてシステムを構築します。このアプローチは、データの整合性と一貫性を重視し、データモデリングを重要視します。
POA（プロセス中心アプローチ）:
プロセス中心アプローチは、システムの設計において業務プロセスや機能を中心に考える手法です。業務フローや機能要件を基にシステムを設計し、プロセスの効率化や最適化を図ります。このアプローチは、ユーザーの操作や業務手順を重視します。

3層スキーマアーキテクチャ

参考になる記事
データベース設計の3層スキーマとは？ - Qiita

外部スキーマ:
ユーザーやアプリケーションから見たデータの表示方法を定義します。外部スキーマは、ユーザーの視点に合わせてデータの表示やアクセス権限を制御します。
概念スキーマ:
データベース全体の論理構造を定義します。概念スキーマは、エンティティ、属性、およびそれらの関係を含むデータモデルを提供し、データの一貫性と整合性を確保します。
内部スキーマ:
データの物理的な保存方法を定義します。内部スキーマは、ストレージ構造、インデックス、アクセスパス、および物理的なデータ配置を含みます。

3層スキーマアーキテクチャは、データの独立性を確保し、データベースの設計と管理を容易にします。これにより、論理データ構造と物理データ構造を分離し、システムの柔軟性と保守性を向上させます。

Web 3モデル

Web 3モデルは、分散型アーキテクチャを採用し、データの所有権やプライバシーをユーザーに委ねる新しいWebの形態です。これにより、中央集権的なサービスプロバイダーに依存せず、ユーザー自身がデータを管理できます。

第2章：論理設計・物理設計

概念スキーマの設計（論理設計）

論理設計は、データベースの概念スキーマを設計するプロセスです。これにより、データの論理構造と関係性を明確に定義します。以下の手順で行われます。

エンティティの抽出:
システムで管理するデータの実体（エンティティ）を抽出します。エンティティは、データベース内で独立して存在するオブジェクトや概念を表します。
エンティティの定義:
抽出したエンティティの属性を定義し、各エンティティの特性を明確にします。属性は、エンティティが持つ情報の具体的な要素です。
正規化:
データの冗長性を排除し、データの一貫性と整合性を確保するために、エンティティを正規化します。正規化は、データベースの設計を改善し、データの重複を減らすプロセスです。
ER図の作成:
エンティティとその関係を視覚的に表現するER図（エンティティ・リレーションシップ図）を作成します。ER図は、データベース設計の全体像を把握するのに役立ちます。

物理設計

物理設計は、データベースの物理的な構造と配置を決定するプロセスです。これにより、データの保存、アクセス、および管理を最適化します。

テーブル定義:
論理設計で定義したエンティティを基に、具体的なテーブル構造を定義します。テーブルは、エンティティの属性を列として持ち、レコードとしてデータを保存します。
インデックス定義:
データの検索性能を向上させるために、インデックスを定義します。インデックスは、特定の列に対して高速な検索を可能にするデータ構造です。
ハードウェアのサイジング:
データベースシステムが適切に動作するために必要なハードウェアリソース（CPU、メモリ、ストレージ）を決定します。
ストレージの冗長構成決定:
データの可用性と耐障害性を確保するために、ストレージの冗長構成を決定します。RAID（Redundant Array of Independent Disks）などの技術を使用して、データの冗長性を持たせます。
ファイルの物理的配置の決定:
データベースファイルの物理的な配置を決定し、データのアクセス性能を最適化します。

データベースの物理設計では、整合性とパフォーマンスのバランスを取ることが重要です。

RAID: データの冗長性を持たせるためのディスクアレイ技術。
バックアップ設計: データの損失に備えて、定期的なバックアップを計画・実施します。
- フルバックアップ: データベース全体をバックアップします。
- 差分バックアップ: 前回のフルバックアップ以降に変更されたデータのみをバックアップします。
- 増分バックアップ: 前回のバックアップ以降に変更されたデータのみをバックアップします。
クラスタリング: 複数のサーバーを連携させて、システムの可用性とパフォーマンスを向上させます。

第3章：論理設計と正規化

テーブルの定義

主キー:
テーブル内の各レコードを一意に識別する列です。主キーは一意であり、NULL値を持つことができません。
外部キー:
他のテーブルの主キーを参照する列です。外部キーは、テーブル間のリレーションシップを確立し、データの整合性を保つ役割を果たします。
参照整合性規約:
データの整合性を保つためのルールです。外部キーの値は、参照先のテーブルの主キーに存在しなければなりません。
カスケード:
外部キーが変更・削除された時に、関連する子テーブルのデータも自動的に変更・削除する機能です。

正規化

正規化は、データの重複をなくし、一貫性と効率性を高めるプロセスです。以下の手順で行います：

第一正規形 (1NF):
各カラムに単一の値が入っていることを確認します。もし複数の値が含まれている場合、検索が困難になります。
第二正規形 (2NF):
完全関係従属性を持つことを確認します。部分的関係従属とは、非キー属性が複合主キーの一部に依存している状態を指します。これを解消するためにテーブルを分割します。
- 例:
CourseID StudentID CourseName StudentName Grade

C1 S1 Math Alice A

C1 S2 Math Bob B

C2 S1 Science Alice B

ここでは、複合主キーは (CourseID,StudentID)(CourseID, StudentID)(CourseID,StudentID) です。

部分的関係従属の発生
- CourseName は CourseID のみに依存しています（部分的関係従属）。
- StudentName は StudentID のみに依存しています（部分的関係従属）。
この状態では、テーブルは第二正規形（2NF）を満たしていません。

2NFへの正規化

部分的関係従属を解消して、2NFにするために、次のようにテーブルを分割します：
1. Courses テーブル:
  
  CourseID CourseName
  
  C1 Math
  
  C2 Science
2. Students テーブル:
  
  StudentID StudentName
  
  S1 Alice
  
  S2 Bob
3. Enrollments テーブル（元のテーブルの結合関係を保持する）:
  
  CourseID StudentID Grade
  
  C1 S1 A
  
  C1 S2 B
  
  C2 S1 B
これにより、各非キー属性が複合主キーのすべての部分に完全に依存するようになり、部分的関係従属が解消されます。
- 2NFへの正規化:
これにより、各非キー属性が複合主キーのすべての部分に完全に依存するようになり、部分的関係従属が解消されます。
第三正規形 (3NF):
推移的関係従属を解消します。推移的関係従属とは、非キー属性が他の非キー属性に依存する場合を指します。
- 具体例
不正規化のテーブル

例えば、次のようなテーブルを考えます：

学生ID 名前学部ID 学部名

1 太郎 10 工学部

2 花子 20 経済学部

3 次郎 10 工学部

ここで：
- 学生ID は主キーです。
- 学部ID が 学生ID に従属しています（学生ID → 学部ID）。
- 学部名 が 学部ID に従属しています（学部ID → 学部名）。
推移的関係従属の発生

上記のテーブルでは、学生ID が 学部名 に推移的に従属しています（学生ID → 学部ID → 学部名）。これは推移的関係従属の例です。

CourseID	StudentID	CourseName	StudentName	Grade
C1	S1	Math	Alice	A
C1	S2	Math	Bob	B
C2	S1	Science	Alice	B

CourseID	CourseName
C1	Math
C2	Science

StudentID	StudentName
S1	Alice
S2	Bob

CourseID	StudentID	Grade
C1	S1	A
C1	S2	B
C2	S1	B

学生ID	名前	学部ID	学部名
1	太郎	10	工学部
2	花子	20	経済学部
3	次郎	10	工学部

これにより、データの冗長性が減り、データの一貫性が向上します。

内部結合と外部結合

内部結合（INNER JOIN）

内部結合は、2つのテーブルの間で一致するレコードを結合する方法です。結合条件に一致するレコードのみが結果セットに含まれます。

例:

sqlコードをコピーする
SELECT Customers.CustomerName, Orders.Product, Orders.Quantity
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

このクエリは、CustomersテーブルとOrdersテーブルをCustomerID列で結合し、両方のテーブルに存在する一致するレコードのみを取得します。

外部結合（OUTER JOIN）

外部結合には、左外部結合（LEFT OUTER JOIN）、右外部結合（RIGHT OUTER JOIN）、完全外部結合（FULL OUTER JOIN）の3種類があります。外部結合は、結合条件に一致しないレコードも結果セットに含めます。

左外部結合（LEFT JOIN）:
左側のテーブルのすべてのレコードと、右側のテーブルの一致するレコードを返します。右側のテーブルに一致するレコードがない場合、結果セットにはNULLが含まれます。
- 例:
```
sqlコードをコピーする
SELECT Customers.CustomerName, Orders.Product, Orders.Quantity
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;
```
  このクエリは、Customersテーブルのすべてのレコードを返し、Ordersテーブルの一致するレコードがない場合はNULLを返します。
右外部結合（RIGHT JOIN）:
右側のテーブルのすべてのレコードと、左側のテーブルの一致するレコードを返します。左側のテーブルに一致するレコードがない場合、結果セットにはNULLが含まれます。
- 例:
```
sqlコードをコピーする
SELECT Customers.CustomerName, Orders.Product, Orders.Quantity
FROM Customers
RIGHT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;
```
  このクエリは、Ordersテーブルのすべてのレコードを返し、Customersテーブルの一致するレコードがない場合はNULLを返します。
完全外部結合（FULL OUTER JOIN）:
左側と右側の両方のテーブルのすべてのレコードを返し、どちらかのテーブルに一致するレコードがない場合はNULLを返します。
- 例:
```
sqlコードをコピーする
SELECT Customers.CustomerName, Orders.Product, Orders.Quantity
FROM Customers
FULL OUTER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;
```
  このクエリは、CustomersテーブルとOrdersテーブルのすべてのレコードを返し、どちらかのテーブルに一致するレコードがない場合はNULLを返します。

第4章：ER図

【DB設計】　ER図の知識

ER図（エンティティ・リレーションシップ図）:
エンティティとリレーションシップを視覚的に表現した図です。ER図は、データベースの設計を視覚的に理解しやすくします。
- 1対1の関係:
  2つのテーブルの主キーが一致する場合。
- 1対多の関係:
  あるテーブルの主キーが他のテーブルの外部キーとして含まれる場合。
  例: 社員テーブルの列に会社IDが含まれていた場合、会社は社員に対して1になり、社員は多になります。
- 多対多の関係:
  関連実体を作成して表現します。関連実体は、多対多の関係を管理する中間テーブルです。
IE表記法:
ER図を描く際の標準的な表記方法です。エンティティ、リレーションシップ、属性を明確に表現します。

第5章：正規化のデメリットとバランス

正規化のデメリット:
正規化しすぎると、データを取得する際に複数のテーブルを結合する必要があり、パフォーマンスが低下する可能性があります。
正規化のメリット:
正規化することで、データのリアルタイム性が向上し、情報を更新する際の手間を省くことができます。データの冗長性が減り、一貫性と整合性が確保されます。

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up

達人に学ぶDB設計 まとめ