【ITニュース解説】Database Normalization in Oracle SQL — From 1NF to 3NF with Example

データベース正規化とは、リレーショナルデータベースの設計において、データの冗長性を排除し、データの一貫性を高め、データ操作時に発生しうる異常（アノマリー）を回避するための体系的なプロセスである。これは、堅牢で効率的なデータベースを構築するために不可欠な概念であり、システムエンジニアを目指す者にとって基本的な知識となる。

正規化の重要性を理解するためには、まず正規化されていないデータベースが抱える問題点、すなわちデータ異常について知る必要がある。例えば、学生、コース、担当講師の情報がすべて一つの大きなテーブルに格納されている状況を考えてみる。このテーブルでは、以下のような三つの種類の異常が発生しうる。一つ目は「挿入異常」である。これは、まだどの学生も履修していない新しいコースの情報を登録したい場合、そのコースを履修する学生の情報も同時に登録しなければならず、コース単独での登録ができないという問題だ。二つ目は「更新異常」である。もし特定の講師の電話番号が変更になった場合、その講師が担当するすべてのコースを履修している学生のレコードを一つずつ探し出し、電話番号を更新する必要がある。もし更新漏れがあれば、データベース内のデータに矛盾が生じてしまう。三つ目は「削除異常」である。ある学生が退学し、その学生のレコードを削除した際、もしその学生が履修していたコースを他の誰も履修していなかったり、そのコースを担当する講師情報がその学生のレコードにしか存在しなかったりすると、学生情報の削除によって、意図せずコース情報や講師情報まで失われてしまう可能性がある。これらのデータ異常は、データベースの管理を複雑にし、データの信頼性を低下させる大きな要因となる。

これらの問題を解決するために、データベースは段階的に正規形に変換される。最初の段階は「第一正規形（1NF）」である。第一正規形は、リレーショナルデータベースの最も基本的な要件であり、テーブルの各フィールド（列）が単一の値を保持し、繰り返しグループを含まないことを意味する。つまり、一つのセルに複数のデータが詰め込まれていたり、同じような列が複数繰り返されていたりする構造を解消する。提示された記事の例で言えば、StudentCourseInfoという名前の、StudentID、StudentName、CourseID、CourseName、Instructor、InstructorPhoneといった情報を一列に持つテーブルは、既にこの第一正規形を満たしている状態だと言える。

Copy
1CREATE TABLE StudentCourseInfo (
2    StudentID VARCHAR2(10),
3    StudentName VARCHAR2(50),
4    CourseID VARCHAR2(10),
5    CourseName VARCHAR2(50),
6    Instructor VARCHAR2(50),
7    InstructorPhone VARCHAR2(15)
8);

次に「第二正規形（2NF）」への変換を行う。第二正規形は、第一正規形の条件を満たした上で、テーブルの主キーを構成する一部の列のみに依存する非キー列（部分関数従属）が存在しないことを要求する。先のStudentCourseInfoテーブルで、仮にStudentIDとCourseIDの組み合わせを主キーとすると、StudentNameはStudentIDのみに依存し、CourseName、Instructor、InstructorPhoneはCourseIDのみに依存している。これが部分関数従属であり、データ冗長性や更新異常の原因となる。第二正規形にするためには、この部分関数従属を解消し、関連性の高い情報を別のテーブルに分離する。具体的には、学生に関する情報（StudentID, StudentName）をStudentsテーブルに、コースに関する情報（CourseID, CourseName, Instructor, InstructorPhone）をCoursesテーブルに、そして学生とコースの関連付け（誰がどのコースを履修しているか）をStudentCoursesという結合テーブルに分割する。StudentsテーブルではStudentIDが主キーとなり、CoursesテーブルではCourseIDが主キーとなる。StudentCoursesテーブルではStudentIDとCourseIDの複合キーが主キーとなり、それぞれStudentsテーブルとCoursesテーブルを参照する外部キーとして設定される。

Copy
1-- Studentsテーブルの作成
2CREATE TABLE Students (
3    StudentID VARCHAR2(10) PRIMARY KEY,
4    StudentName VARCHAR2(50)
5);
6
7-- Coursesテーブルの作成
8CREATE TABLE Courses (
9    CourseID VARCHAR2(10) PRIMARY KEY,
10    CourseName VARCHAR2(50),
11    Instructor VARCHAR2(50),
12    InstructorPhone VARCHAR2(15)
13);
14
15-- StudentCourses結合テーブルの作成
16CREATE TABLE StudentCourses (
17    StudentID VARCHAR2(10),
18    CourseID VARCHAR2(10),
19    PRIMARY KEY (StudentID, CourseID),
20    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
21    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
22);

最後に「第三正規形（3NF）」への変換を行う。第三正規形は、第二正規形の条件を満たした上で、主キー以外の列が、主キー以外の別の列に依存する「推移的関数従属」が存在しないことを要求する。先ほどのCoursesテーブルでは、CourseIDが主キーだが、InstructorPhoneはInstructor（講師名）に依存しており、InstructorもまたCourseIDに依存している。「CourseID → Instructor → InstructorPhone」という連鎖的な依存関係が存在する。これも更新異常の原因となる。講師の電話番号が変更された場合、その講師が担当するすべてのコースのレコードを更新する必要があるためだ。第三正規形にするためには、この推移的関数従属を解消し、講師に関する情報を独立したInstructorsテーブルとして分離する。Instructorsテーブルには、講師を一意に識別するInstructorIDを主キーとして、InstructorNameとInstructorPhoneを含める。そして、CoursesテーブルからはInstructorとInstructorPhoneの列を削除し、代わりにInstructorsテーブルのInstructorIDを参照する外部キーInstructorIDを追加する。これにより、講師の電話番号が変更されても、Instructorsテーブルの該当レコードを一度更新するだけで済むようになる。

Copy
1-- Instructorsテーブルの作成
2CREATE TABLE Instructors (
3    InstructorID INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
4    InstructorName VARCHAR2(50),
5    InstructorPhone VARCHAR2(15)
6);
7
8-- Coursesテーブルの再定義 (講師情報が分離された後)
9CREATE TABLE Courses (
10    CourseID VARCHAR2(10) PRIMARY KEY,
11    CourseName VARCHAR2(50),
12    InstructorID INT,
13    FOREIGN KEY (InstructorID) REFERENCES Instructors(InstructorID)
14);
15
16-- Studentsテーブル (変更なし)
17CREATE TABLE Students (
18    StudentID VARCHAR2(10) PRIMARY KEY,
19    StudentName VARCHAR2(50)
20);
21
22-- StudentCourses結合テーブル (変更なし)
23CREATE TABLE StudentCourses (
24    StudentID VARCHAR2(10),
25    CourseID VARCHAR2(10),
26    PRIMARY KEY (StudentID, CourseID),
27    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
28    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
29);

これらのテーブル構造が定義されたら、実際のデータを各テーブルに挿入していく。この際、外部キーで参照される側のテーブル（例：StudentsやInstructors）から先にデータを挿入し、その後に外部キーを持つテーブル（例：CoursesやStudentCourses）にデータを挿入する順序を守る必要がある。

Copy
1-- 学生データを挿入
2INSERT INTO Students VALUES ('S01', 'Arjun');
3INSERT INTO Students VALUES ('S02', 'Priya');
4INSERT INTO Students VALUES ('S03', 'Kiran');
5
6-- 講師データを挿入
7INSERT INTO Instructors (InstructorName, InstructorPhone)
8VALUES ('Dr.Kumar', '9876543210');
9INSERT INTO Instructors (InstructorName, InstructorPhone)
10VALUES ('Dr.Mehta', '9123456780');
11INSERT INTO Instructors (InstructorName, InstructorPhone)
12VALUES ('Dr.Rao', '9988774455');
13
14-- コースデータを挿入（InstructorIDで講師を参照）
15INSERT INTO Courses (CourseID, CourseName, InstructorID)
16VALUES ('C101', 'DBMS', 1);
17INSERT INTO Courses (CourseID, CourseName, InstructorID)
18VALUES ('C102', 'Data Mining', 2);
19INSERT INTO Courses (CourseID, CourseName, InstructorID)
20VALUES ('C103', 'AI', 3);
21
22-- 学生とコースの関連付けを挿入
23INSERT INTO StudentCourses VALUES ('S01', 'C101');
24INSERT INTO StudentCourses VALUES ('S01', 'C102');
25INSERT INTO StudentCourses VALUES ('S02', 'C103');

データが各正規化されたテーブルに格納された後、元の大きなテーブルで表示していたような学生名、コース名、講師名といった情報を取得したい場合は、SQLのJOIN句を用いて複数のテーブルを結合して表示する。これにより、分割されたテーブルから必要な情報を統合して取得できる。

Copy
1SELECT
2    s.StudentName,
3    c.CourseName,
4    i.InstructorName
5FROM
6    Students s
7JOIN
8    StudentCourses sc ON s.StudentID = sc.StudentID
9JOIN
10    Courses c ON sc.CourseID = c.CourseID
11JOIN
12    Instructors i ON c.InstructorID = i.InstructorID;

このSQL文は、Students、StudentCourses、Courses、Instructorsの四つのテーブルを、それぞれのテーブル間で定義された主キーと外部キーの関係に基づいて結合し、最終的に学生名、コース名、講師名を一つの結果セットとして取得する。

このようにデータベースを正規化することで、データの冗長性が大幅に削減され、データの一貫性が維持しやすくなる。また、データの挿入、更新、削除時の異常を効果的に防ぐことができ、データベースの保守性や信頼性が向上する。これらの正規化の概念と具体的な適用方法は、データベース設計の基盤となる非常に重要なスキルである。