#5 データベースを学ぶ【正規化理論編】

こんにちは。
連日の猛暑にすっかりやられ、休日はエアコンの効いた部屋から出られない日々を過ごす佐野です。

さて、システム開発において堅牢なシステムを構築するためには、適切なデータベース設計は欠かせません。
そこで、今回は自分なりにデータベースの正規化についてまとめてみました。
基本情報技術者試験の勉強にもなると思うので、ぜひ最後までお付き合いください。

本記事で登場する用語の説明

属性

属性とは、テーブル（表）を構成する列の論理的な名前や意味のことです。例えば、大学で使用されるシステムで学生を管理するテーブルでは、学籍番号や名前、所属学部などの情報が扱われるでしょう。これらは、データモデルの段階では属性、実際のテーブル上ではカラム（列）と呼ばれることが多いです。

主キー

テーブルの各行を識別するための属性または属性の組です。学籍番号が一意に定まれば学生が一意に定まるので、学生テーブルの主キーとしては学籍番号が考えられます。

外部キー

別のテーブルを参照するための属性または属性の組です。例えば、学部テーブルと学生テーブルがあり、学部テーブルの主キーは学部番号とします。学生テーブルで学生が所属する学部を管理したい場合、学生テーブルに学部番号を持たせることで学生から学部を参照できます。このとき、学生テーブルの学部番号が外部キーとなります。

関数従属

ある属性Aが一意に定まるとき、それによって属性Bも一意に定まる場合、属性Bは属性Aに関数従属するといいます。例えば、学籍番号が一意に定まれば学生氏名も一意に定まるため、学生氏名は学籍番号に関数従属しているといえます。

なぜ正規化が必要なのか

データベースの設計において、正規化が必要となる主な理由は更新異常を防ぐためです。
例えば、塾で使うシステムに講座を管理する次のような講座テーブルがあるとします。ただし、各教師は1つの科目の複数の講座を担当し、各講座を担当する教師は1人のみとします。なお、太字の列名が主キーです。

講座（講座番号、講座名、教師番号、教師名、教師担当科目）

このようなテーブル構造はあまり好ましくありません。なぜなら、教師の名前や担当科目が変わった場合に、講座テーブルの該当する教師のすべての行を更新しなければならないからです。どれか1行でも更新漏れがある場合、更新異常となりデータベースが矛盾した状態になります。

例を表1、表2に示します。
教師番号T001の鈴木太郎さんの名前が佐藤太郎に変更となったとします。この場合、1行目と3行目を更新しなければなりませんが、仮に1行目の更新が行われなかった場合、表2のようになります。

これを防ぐために、次のように講座テーブルと教師テーブルに分解します。黄色のマーカーが付いた属性が外部キーです。

講座（講座番号、講座名、教師番号）
教師（教師番号、教師名、教師担当科目）

このようにすれば、教師名や担当科目が変更された場合に教師テーブルの1行を変更すればよくなります。

正規化された状態を正規形と呼び、段階に応じて第1正規形、第2正規形、第3正規形と呼びます（実は他にもあります）。先ほどの正規化前の講座テーブルは第2正規形、正規化後は第3正規形です。

表1　好ましくない講座テーブル

講座番号	講座名	教師番号	教師名	教師担当科目
M001	数学上級講座	T001	鈴木太郎	数学
M002	数学中級講座	T002	高橋一郎	数学
M003	数学初級講座	T001	鈴木太郎	数学

表2　更新漏れの講座テーブル

講座番号	講座名	教師番号	教師名	教師担当科目
M001	数学上級講座	T001	鈴木太郎	数学
M002	数学中級講座	T002	高橋一郎	数学
M003	数学初級講座	T001	佐藤太郎	数学

正規化の手順

では、正規化の考え方を具体的な例に沿って確認していきましょう。ここでは、講座とそれを担当する教師、およびその役割を管理するテーブルを題材とします。

教師講座担当（講座番号、教師番号、講座名、生徒上限数、開講曜日、開講日時、教師名、役割番号、役割名）

ただし、要件は次のとおりです。

・講座は講座番号で識別する。講座名、生徒上限数、開講曜日、開講日時を持ち、一週間に一度開講される。
・教師は教師番号で識別する。教師名を持つ。
・役割は役割番号で識別する。役割名を持つ。
・各講座は複数の教師が担当することがある。
・各講座において各教師は1つの役割を持つ。

第1正規化

第1正規化では、繰り返し項目を排除します。
繰り返し項目とは、1行のレコードにおいて1つの列に複数の値が格納された項目のことです。
今回の例では繰り返し項目は含まれないため、第1正規形の条件は満たされています。

一方、例えば同じ講座が同じ時間に一週間で複数回開講される場合、開講曜日が繰り返し項目となり第1正規形の条件を満たさないことになります。

第2正規化

第2正規化では、部分関数従属を排除します。
部分関数従属とは、主キーが複数の属性から構成される場合に、非キー属性（主キーでない属性）が主キーの一部のみに関数従属している状態を指します。

主キーが複数の属性で構成される場合、非キー属性は主キーすべての値が定まって初めて一意に定まるべきです。一部の属性のみに関数従属している場合、それは部分関数従属となり、第2正規形の条件を満たしません。
そのため、第2正規化では主キーを構成する属性の一部のみに関数従属する属性を別のテーブルに分解することで部分関数従属を排除します。

では、教師講座担当テーブルで考えてみましょう。このテーブルの主キーは講座番号と教師番号の2つです。この2つのうち1つが定まることで一意に定まる属性が存在するか確認します。
まず、講座番号が一意に定まると、講座名、生徒上限数、開講曜日、開講日時が一意に定まります。そして、教師番号が一意に定まると、教師名が一意に定まります。よって、第2正規形を満たしていません。

部分関数従属を排除するために、次のように講座テーブルと教師テーブルを新たに作ります。

講座（講座番号、講座名、生徒上限数、開講曜日、開講日時）
教師（教師番号、教師名）
教師講座担当（講座番号、教師番号、役割番号、役割名）

教師講座担当テーブルの講座番号および教師番号は主キーかつ外部キーとなります。これで第2正規化が完了しました。

第3正規化

第3正規化では、推移的関数従属を排除します。
推移的関数従属について説明します。属性A、B、Cがあり、属性Aが一意に定まるとそれにより属性Bも一意に定まるとします。このとき、属性Bが一意に定まることで属性Cも一意に定まるような場合、属性Cは属性Aに推移的に関数従属するといいます。

今回の例で考えてみましょう。まず、講座テーブルは講座番号以外の属性は講座番号が一意に定まることで一意に定まります。また、教師テーブルも教師番号が一意に定まることで教師名が定まるので、推移的関数従属は含まれません。

一方、教師講座担当テーブルでは、講座番号と教師番号が定まることで役割番号が定まりますが、役割番号が定まることで役割名が定まります。すなわち、推移的関数従属性が見出せます。
そこで、次のように新たに役割テーブルを作成して教師講座担当テーブルを第3正規形にします。

講座（講座番号、講座名、生徒上限数、開講曜日、開講日時）
教師（教師番号、教師名）
役割（役割番号、役割名）
教師講座担当（講座番号、教師番号、役割番号）

教師講座担当テーブルの役割番号は外部キーとなります。これで第3正規化が完了です。