関係データベースの正規化とは?第1正規形、第2正規形、第3正規形の違いについてまとめました。
【はじめに】正規化の役割
正規化は、関係データベースを構築する際にデータの重複や矛盾を排除して、データの整合性と一貫性を保つために行います。
矛盾の例を、各人の誕生月と所属している部活動のテーブルを例に紹介します。
| 氏名 | 誕生月 | 部活動 |
|---|---|---|
| 志摩 | 10 | 帰宅部 |
| 各務原 | 3 | 野外活動サークル |
| 大垣 | 8 | 野外活動サークル |
| 犬山 | 3 | 野外活動サークル |
「野外活動サークル」が部の要件を満たし「野外活動部」に変わったとします。
このとき、以下テーブルのように一部「野外活動サークル」を「野外活動部」に更新ミスがあるとデータに矛盾が生じます。
| 氏名 | 誕生月 | 部活動 |
|---|---|---|
| 志摩 | 10 | 帰宅部 |
| 各務原 | 3 | 野外活動サークル(更新ミス) |
| 大垣 | 8 | 野外活動部 |
| 犬山 | 4 | 野外活動部 |
このように、複数のレコードが同じデータを重複すると、矛盾が発生しやすくなります。
正規化は、このようなデータの重複を排除し、更新ミス等で矛盾が発生しないテーブルを設計するために行います。
補足
関係データベースは関係モデル(データの関係を数学モデルで表現したもの)をコンピュータ上に実装したものです。
関係モデルと関係データベースは次のように対応しています。
| 関係モデル | 関係データベース |
|---|---|
| 関係(relation) | 表（テーブル） |
| 属性(attribute) | 列（カラム） |
| タプル(tuple)・組 | 行（レコード） |
| 定義域(domain) | 文字列型・整数型 |
【正規形】第1正規形、第2正規形、第3正規形の違い
関係データベースの正規化は基本的に以下の3段階に分けて行います。
| 段階 | 説明 |
|---|---|
| 第１正規化 | 繰り返し項目をなくす。全ての属性が単一値である状態(第1正規形)になる。 |
| 第２正規化 | 第1正規形を満たし、テーブルをすべての候補キーにおいて部分関数従属性が存在しない状態(第2正規形)にします。 |
| 第３正規化 | 第2正規形を満たし、かつ、主キーからの推移的関数従属が存在しない状態(第3正規形)。 |
正規化手順を理解するには、RDB理論上の基本用語(以下表)を理解しておく必要があります。
| 用語 | 説明 |
|---|---|
| 主キー | 表内のレコードを一意に決めることができる特性をもつ属性。主キーではNULL値の存在が許されない。 |
| 候補キー | 主キーやユニークキーのカラムの集合。候補キーにはNULL値が許される。 |
| 非キー属性 | 候補キー(主キーやユニークキー)に含まれないカラム。 |
| 関数従属性 | あるレコードで、特定のカラムAの値が決まれば、別のカラムBも特定できるような関係。例えば氏名(カラムA) ← 部活動(カラムB)なら、「部活動は氏名に関数従属している」といい、氏名を確認すれば部活動も特定できることになります。 |
| 部分関数従属性 | ある非キー属性が、候補キーのー部に関数従属していること。 |
| 推移関数従属性 | あるテーブルの非キー属性Aが特定の非キー属性Bに関数従属していること。 |
| 外部キー制約 | 関係データベースの2つのテーブルの間の参照整合性を保つための制約で、SQLでは設定する属性に対して「FOREIGN KEY句」を指定します。正規化された表同士を外部キーで結合することでデータの一貫性・正当性を保つことができます。 |
| 整合性制約 | データベースでは格納されているデータの整合性を保つために、表中の行と列、または表と表の間において整合性制約と呼ばれるものが定義されています。 |
| 参照制約 | 整合性制約の1つで、外部キーを持つレコードを追加する場合に、その外部キーの値は参照先のレコードの主キーとして存在するものでなければならない。また、別表から主キーの値を参照されている行は削除することができないという制限が課すものです。 |
部分関数従属性
例えば以下のテーブル(候補キー:氏名、部活動)の場合、「部費」は「部活動」に関係従属しています。
このように、候補キーの一部に関係従属していることを「部分関係従属」といいます。
| 氏名 | 誕生月 | 愛車 | 部活動 | 部費 |
|---|---|---|---|---|
| 志摩 | 10 | Vivo | 帰宅部 | 0円 |
| 各務原 | 3 | ボードウォーク | 野外活動サークル | 1000円 |
| 大垣 | 8 | エスケープR3 | 野外活動サークル | 1000円 |
| 犬山 | 4 | レイル700 | 野外活動サークル | 1000円 |
【第1正規化】テーブルで表現
第1正規化では、データの集合をテーブルで表現(1つのカラムに1つの値を入れた状態)することです。
| 氏名 | 誕生月 | 愛車 | 部活動 | 部費 |
|---|---|---|---|---|
| 志摩 | 10 | Vivo | 帰宅部 | 0円 |
| 各務原 | 3 | ボードウォーク | 野外活動サークル | 1000円 |
| 大垣 | 8 | エスケープR3 | 野外活動サークル | 1000円 |
| 犬山 | 4 | レイル700 | 野外活動サークル | 1000円 |
【第2正規化】テーブルで表現
第2正規化では、テーブルをすべての候補キーにおいて部分関数従属性が存在しない状態にします。
部費が部活動に対して部分関数従属しているので、２つのテーブルに分割して、部分関数従属性を排除します。
| 氏名 | 誕生月 | 愛車 | 部活動 |
|---|---|---|---|
| 志摩 | 10 | Vivo | 帰宅部 |
| 各務原 | 3 | ボードウォーク | 野外活動サークル |
| 大垣 | 8 | エスケープR3 | 野外活動サークル |
| 犬山 | 4 | レイル700 | 野外活動サークル |
| 部活動 | 部費 |
|---|---|
| 帰宅部 | 0円 |
| 野外活動サークル | 1000円 |
| 野外活動サークル | 1000円 |
| 野外活動サークル | 1000円 |
【第3正規化】推移関数従属性が存在しない状態にする
第3正規系化では、テーブル内の推移関数従属性(非キー属性→非キー属性の関数従属)を排除します。
ここでは、非候補キー(愛車)が非候補キー(誕生月)に関係従属しています(推移関数従属)。
この場合も、２つのテーブルに分割して、推移関数従属性を排除します。
| 氏名 | 誕生月 | 部活動 |
|---|---|---|
| 志摩 | 10 | 帰宅部 |
| 各務原 | 3 | 野外活動サークル |
| 大垣 | 8 | 野外活動サークル |
| 犬山 | 4 | 野外活動サークル |
| 誕生月 | 愛車 |
|---|---|
| 10 | Vivo |
| 3 | ボードウォーク |
| 8 | エスケープR3 |
| 4 | レイル700 |
| 部活動 | 部費 |
|---|---|
| 帰宅部 | 0円 |
| 野外活動サークル | 1000円 |
| 野外活動サークル | 1000円 |
| 野外活動サークル | 1000円 |
コメント