データベースの正規化とその重要性

データベースの正規化は、データベースの冗長性を減らし、データの整合性を保つためのプロセスです。

正規化にはいくつかの段階（正規形）がありますが、それぞれの段階で特定のルールに従ってテーブル構造を設計します。

以下に、主な正規形とそのSQLを使った具体例を記載します。

第1正規形（1NF: First Normal Form）

1NFの要件は、テーブルの各列が単一の値のみを含むことです。

つまり、各フィールドは原子的でなければなりません。

例

次のテーブルは非正規化された形です（1NFではない）。

StudentID	Name	Courses
1	John Doe	Math, Science
2	Jane Smith	English, History

このテーブルを1NFにするには、Courses列を分解します。

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(100)
);

CREATE TABLE StudentCourses (
    StudentID INT,
    Course VARCHAR(100),
    PRIMARY KEY (StudentID, Course),
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID)
);

INSERT INTO Students (StudentID, Name) VALUES
(1, 'John Doe'),
(2, 'Jane Smith');

INSERT INTO StudentCourses (StudentID, Course) VALUES
(1, 'Math'),
(1, 'Science'),
(2, 'English'),
(2, 'History');

第二正規形（2NF: Second Normal Form）

2NFの要件は、1NFを満たしており、かつ全ての非キー属性が完全な候補キーに対して従属していることです。

部分従属が存在してはいけません。

例

次のテーブルは1NFですが、2NFではありません（非キー属性が部分従属しています）。

StudentID	Course	Instructor
1	Math	Prof. Smith
1	Science	Prof. Johnson
2	English	Prof. Brown
2	History	Prof. White

Instructor列はCourseに部分従属しているため、2NFにするにはテーブルを分割します。

CREATE TABLE Courses (
    Course VARCHAR(100) PRIMARY KEY,
    Instructor VARCHAR(100)
);

INSERT INTO Courses (Course, Instructor) VALUES
('Math', 'Prof. Smith'),
('Science', 'Prof. Johnson'),
('English', 'Prof. Brown'),
('History', 'Prof. White');

第三正規形（3NF: Third Normal Form）

3NFの要件は、2NFを満たしており、かつ全ての非キー属性が他の非キー属性に対して従属していないことです。

例

次のテーブルは2NFですが、3NFではありません（InstructorDepartmentがInstructorに従属しています）。

Course	Instructor	InstructorDepartment
Math	Prof. Smith	Mathematics
Science	Prof. Johnson	Science
English	Prof. Brown	Literature
History	Prof. White	History

InstructorDepartment列はInstructorに従属しているため、3NFにするにはInstructorとその属性を別テーブルに分割します。

CREATE TABLE Instructors (
    Instructor VARCHAR(100) PRIMARY KEY,
    Department VARCHAR(100)
);

INSERT INTO Instructors (Instructor, Department) VALUES
('Prof. Smith', 'Mathematics'),
('Prof. Johnson', 'Science'),
('Prof. Brown', 'Literature'),
('Prof. White', 'History');

ALTER TABLE Courses
ADD CONSTRAINT fk_instructor FOREIGN KEY (Instructor) REFERENCES Instructors(Instructor);

Boyce-Codd正規形（BCNF: Boyce-Codd Normal Form）

BCNFは、3NFをさらに厳密にした形で、全ての決定キーが候補キーであることを要求します。

第4正規形（4NF: Fourth Normal Form）

4NFの要件は、BCNFを満たしており、多重従属が存在しないことです。

多重従属とは、1つのキーに対して複数の独立した非キー属性が従属することです。

第5正規形（5NF: Fifth Normal Form）

5NFの要件は、4NFを満たしており、かつジョイン依存性（join dependency）が存在しないことです。

すなわち、データが複数のテーブルに分解されても、元のテーブルを復元するためのジョイン操作が必要ないことです。

正規化のプロセスは、データの整合性と冗長性のバランスを取るために重要です。

ただし、過度な正規化はパフォーマンスに悪影響を与えることもあるため、実際のシステム設計ではデータベースの利用状況に応じて適度に正規化を行うことが推奨されます。

正規化を適切に行わないと生じる問題

正規化を適切に行わない場合、データベースに以下のようなさまざまな問題が生じる可能性があります。

データの冗長性

冗長性は、同じデータが複数の場所に重複して存在することです。冗長性が高いと以下の問題が発生します。

• ストレージの浪費
  同じ情報を何度も保存するため、無駄にディスクスペースを消費します。
• データの不整合性
  データを更新する際に、重複している全てのレコードを同時に更新しなければならないため、更新漏れが生じやすくなり、データの一貫性が保てなくなります。

例

次のテーブルでは、各学生の情報と履修科目が重複しています。

StudentID	Name	Address	Course
1	John Doe	123 Maple St	Math
1	John Doe	123 Maple St	Science
2	Jane Smith	456 Oak St	English
2	Jane Smith	456 Oak St	History

更新異常（Update Anomalies）

更新異常は、データの更新操作が複雑になり、データの一貫性を保つことが難しくなる問題です。

例

上記の例で、John Doeの住所を変更する場合、すべてのJohn Doeのレコードを更新する必要があります。もし一部のレコードを更新し忘れた場合、データの不整合が生じます。

挿入異常（Insertion Anomalies）

挿入異常は、新しいデータを挿入する際に、不必要なデータも同時に挿入しなければならない問題です。

例

新しい学生がまだ履修している科目がない場合、その学生の情報を追加するために、ダミーの科目データを挿入しなければならない状況が生じます。

削除異常（Deletion Anomalies）

削除異常は、不要なデータを削除する際に、必要なデータも一緒に削除されてしまう問題です。

例

ある学生が唯一履修している科目を削除する場合、その学生の全ての情報がデータベースから消えてしまう可能性があります。

データの不整合性

正規化が不十分な場合、データベース内のデータの一貫性が保たれず、信頼性が低下します。

例

同じ情報が複数の場所に保存されている場合、これらの情報が異なる値を持つことになり、データの信頼性が失われます。

具体例と解決策

以下の例で、正規化を行わない場合の問題と、それを解決するための方法を説明します。

非正規化テーブル

CREATE TABLE StudentCourses (
    StudentID INT,
    Name VARCHAR(100),
    Address VARCHAR(100),
    Course VARCHAR(100),
    Instructor VARCHAR(100)
);

このテーブルには、冗長性、更新異常、挿入異常、削除異常が含まれています。

正規化されたテーブル

これを正規化して、問題を解決します。

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(100),
    Address VARCHAR(100)
);

CREATE TABLE Courses (
    CourseID INT PRIMARY KEY,
    CourseName VARCHAR(100),
    Instructor VARCHAR(100)
);

CREATE TABLE StudentCourses (
    StudentID INT,
    CourseID INT,
    PRIMARY KEY (StudentID, CourseID),
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

このように正規化することで、以下のような利点があります。

• データの冗長性の削減
  各情報が一度しか保存されないため、無駄が減ります。
• データの整合性の向上
  更新時に全ての関連データを変更する必要がなく、一貫性が保たれます。
• 挿入・削除異常の解消
  必要な情報だけを個別に挿入・削除できるため、無駄なデータの挿入や、必要なデータの誤削除が防止されます。

正規化はデータベース設計において重要なプロセスであり、データの一貫性と効率性を保つために不可欠です。