【ITニュース解説】Understanding Database Normalization (1NF, 2NF, 3NF) with SQL Examples

データベースの正規化は、情報を効率的かつ正確に管理するために非常に重要な概念である。システムが扱うデータをデータベースに保存する際、ただ情報を羅列するだけでは、様々な問題が発生する可能性があるため、特定のルールに従ってデータを整理し、構造化していく作業が正規化である。これにより、データの重複を減らし、データの矛盾を防ぎ、データベースを扱いやすくする狙いがある。

まず、正規化されていない「非正規化」の状態のデータベースがどのような問題を抱えるかから考える。例えば、ある学校の学生、コース、講師の情報をすべて一つの大きなテーブルにまとめて記録するとする。このテーブルには、StudentID（学生ID）、StudentName（学生名）、CourseID（コースID）、CourseName（コース名）、InstructorName（講師名）といった情報がすべて含まれているとしよう。この状態では、同じ情報が何度も繰り返し登場することがよくある。例えば、アリスという学生が複数のコースを取っていれば、アリスの名前や学籍番号がそのコースの数だけ繰り返し記載される。また、特定のコース名や担当講師名も、そのコースを受講する学生の数だけ繰り返し記録されることになる。

このような繰り返しが多い状態では、「データの異常」と呼ばれる問題が発生しやすくなる。具体的には三つの異常が挙げられる。一つ目は「挿入異常」で、例えば新しいコースを開設したいと思っても、そのコースを登録する学生がまだ一人もいないと、コースの情報をデータベースに追加できないという問題がある。二つ目は「更新異常」で、もしジョン先生が担当するコースの名前が変更になった場合、ジョン先生が担当する全てのコースの行に対して、コース名を一つ一つ手動で更新しなければならない。もし一つでも更新し忘れると、データベース内で情報に矛盾が生じてしまう。三つ目は「削除異常」で、もしアリスが全てのコースを履修停止して、アリスに関する情報がデータベースから削除された場合、アリスが受講していたコースの情報自体も一緒に失われてしまう可能性がある。これらは、データが重複して格納されていることや、論理的に異なる情報がごちゃ混ぜになっていることによって引き起こされる問題である。

これらの問題を解決するために、データベースの情報を整理していくプロセスを「正規化」と呼ぶ。最初のステップが「第一正規形（1NF）」である。第一正規形のルールは、「繰り返しグループを排除し、各値が分割不可能な最小単位であること（原子性）」を保証することだ。先の非正規化されたテーブルでは、各セルに入っている情報がすでに分割できない最小単位だったので、原子性の観点では満たされている状態だった。しかし、概念的には、もし一つの学生が複数のコースを取っている場合に、例えば「コース1、コース2」のように一つのセルに複数の値が入っているような状態であれば、それを分割して、各コースごとに独立した行として表現し直す必要がある。この例では、非正規化されたテーブルが最初からある程度整理されていたため、具体的な構造の変更は少ないように見えるが、重要なのは、この段階で「StudentIDとCourseIDの組み合わせ」を主キーとして設定し、各行が一意に特定できるようになる点だ。これにより、どの学生がどのコースを受講しているかという情報が明確に定義される。

第一正規形をクリアしたら、次に「第二正規形（2NF）」を目指す。第二正規形のルールは、「主キーの一部だけに依存する非主キー属性を排除すること」、つまり「部分関数従属を排除すること」である。これは、複数の項目を組み合わせた「複合主キー」を持つテーブルで特に意識されるルールだ。前の段階のテーブルでは、「StudentIDとCourseID」が複合主キーだった。ここで各項目が何に依存しているかを考えてみる。「StudentName」は「StudentID」さえ分かれば特定できる情報であり、「CourseID」には依存しない。同様に「CourseName」や「InstructorName」は「CourseID」さえ分かれば特定できる情報で、「StudentID」には依存しない。このように、複合主キーの一部（StudentIDだけ、あるいはCourseIDだけ）に依存している情報があると、それが部分関数従属の状態だと言える。この問題を解決するために、テーブルを三つに分割する。一つ目は「Students」テーブルで、StudentIDとStudentNameだけを格納し、StudentIDを主キーとする。二つ目は「Courses」テーブルで、CourseID、CourseName、InstructorNameを格納し、CourseIDを主キーとする。そして三つ目は、元のテーブルの主キーだったStudentIDとCourseIDだけを残した「StudentCourse」テーブルを作り、この二つを複合主キーとする。StudentsテーブルとCoursesテーブルは、それぞれStudentCourseテーブルから参照される形で、「外部キー」として設定される。これで、学生の情報、コースの情報、そして学生とコースの関係がそれぞれ独立したテーブルで管理されるようになり、更新異常や挿入異常のリスクが大きく減少する。

第二正規形をクリアしたら、最終的に「第三正規形（3NF）」を目指す。第三正規形のルールは、「非主キー属性が別の非主キー属性に依存する状態を排除すること」、つまり「推移的関数従属を排除すること」だ。第二正規形で作った「Courses」テーブルを見てみる。このテーブルにはCourseID（主キー）、CourseName、InstructorNameが含まれていた。ここで「InstructorName」に注目すると、InstructorNameは特定のコースに紐づく情報ではなく、特定の講師に紐づく情報であるべきだと考えられる。CourseID → InstructorName という依存関係はあるものの、InstructorNameは直接CourseIDに依存するのではなく、むしろInstructorIDという講師固有の識別子に依存するべき情報だと捉えられる。これが推移的関数従属の状態だ。もし同じ講師が複数のコースを担当する場合、InstructorNameがCoursesテーブルに重複して記録されることになるし、もし講師の名前が変わった場合、Coursesテーブルの複数の行を更新しなければならないという更新異常が再び発生する可能性がある。この問題を解決するために、「Instructors」という新しいテーブルを導入する。このテーブルにはInstructorIDとInstructorNameを格納し、InstructorIDを主キーとする。そして、元のCoursesテーブルからInstructorNameを削除し、代わりにInstructorIDを外部キーとしてCoursesテーブルに持たせる。CoursesテーブルはCourseID、CourseName、InstructorIDを持つ形になる。これで、最終的には「Students」「Instructors」「Courses」「StudentCourse」という四つのテーブルが完成する。それぞれのテーブルが独立した情報を管理し、必要な情報だけを持つようになる。InstructorNameはInstructorsテーブルで一元管理され、もし講師の名前が変わっても、Instructorsテーブルの一行を更新するだけで済む。

このように正規化されたデータベースにデータを挿入する際は、それぞれのテーブルに適切な情報をINSERT文を使って追加する。例えば、新しい学生を追加するならStudentsテーブルに、新しい講師を追加するならInstructorsテーブルに、新しいコースを追加するならCoursesテーブルに、そしてどの学生がどのコースを取るかという関係を追加するならStudentCourseテーブルに、それぞれ対応するデータを挿入する。そして、例えば「どの学生がどのコースを、どの講師から受講しているか」といった、複数のテーブルにまたがる情報を取り出したい場合は、「JOIN」というSQLの機能を使うことになる。JOINは、共通のキー（主キーと外部キー）を使って複数のテーブルを連結し、一つの大きな結果セットとして情報を取り出すための命令だ。例えば、StudentsテーブルとStudentCourseテーブルをStudentIDで連結し、さらにStudentCourseテーブルとCoursesテーブルをCourseIDで連結し、最後にCoursesテーブルとInstructorsテーブルをInstructorIDで連結することで、必要な情報を一覧として表示することができる。

データベースの正規化は、データの冗長性を排除し、データの整合性を保ち、データベースの管理や運用を効率的に行うための非常に重要な設計原則である。1NF、2NF、3NFと段階的に正規化を進めることで、データの異常を防ぎ、より堅牢で信頼性の高いデータベースシステムを構築することが可能となる。システムエンジニアを目指す上で、この正規化の考え方を理解し、実践することは、効率的で保守性の高いシステムを設計するための基礎となるだろう。