はじめに
応用情報技術者試験を受ける予定のため、データベースの正規化を学習しているのですが、参考書に記載されている第一正規化、第二正規化、第三正規化の意味がとてもわかりづらく理解しづらいと思いました。
そこで、自身の理解の定着の意味もこめて解説します。
第一正規化
テキストには
繰り返し部分が取り除かれたもの
とあります。
これだけでは意味がわからないので例を出します。
例:
あるテーブルに、生徒の名前と彼らが取っている科目のリストがあるとします。
| 生徒名 | 科目 |
|---|---|
| 山田太郎 | 数学, 英語 |
| 佐藤花子 | 英語, 歴史 |
上記のテーブルは第一正規化に違反しています。それを第一正規化に合致させるためには以下のように変更します。
| 生徒名 | 科目 |
|---|---|
| 山田太郎 | 数学 |
| 山田太郎 | 英語 |
| 佐藤花子 | 英語 |
| 佐藤花子 | 歴史 |
まとめ:
- 各列は原子的な（分割できない）値を持つべきです。
- 同じ行内で値のリストや繰り返しを持ってはいけません。
- 各エントリは一意の識別子（プライマリキー）によって識別されるべきです。
第二正規化
テキストには
第1正規形の表から、部分関数従属している列が切り出されたもの
とあります。
関数従属とは。主キーが決まると、列の値が一意に定まる関係のことを呼びます。
部分関数従属とは。複合キーの一部の項目だけで、列の値が一意に定まる関係のことを呼びます。第1正規形においては、主キー(複合キー)からレコードを特定が可能ですが、主キーの一部分に対して、上記の関数従属する項目のため、部分関数従属と呼ばれている
これだけでは全く意味がわからないので、例を出します。
例:
学生が取る科目とその科目の教師のテーブルがあるとします。
| 生徒ID | 科目 | 教師 |
|---|---|---|
| 1 | 数学 | 田中 |
| 1 | 英語 | 斎藤 |
| 2 | 英語 | 斎藤 |
ここで、科目と教師の間には強い関係があることがわかります。生徒IDと科目の組み合わせがプライマリキーであるとして、教師の列はプライマリキーの一部（科目）にしか依存していません。これは第二正規化に違反します。
第二正規化に合わせるためには、以下の2つのテーブルに分けるべきです。
生徒と科目のテーブル:
| 生徒ID | 科目 |
|---|---|
| 1 | 数学 |
| 1 | 英語 |
| 2 | 英語 |
科目と教師のテーブル:
| 科目 | 教師 |
|---|---|
| 数学 | 田中 |
| 英語 | 斎藤 |
まとめ:
- テーブルは第一正規化に合致していること。
- 部分的な依存関係が存在していないこと。すなわち、プライマリキーの一部にしか依存しない列が存在してはいけない。
第三正規化
テキストには
第2正規形の表から、主キー以外の列に関数従属している列が切り出されたもの
とあります。
例:
従業員とその役職、役職の給与のテーブルがあるとします。
| 従業員ID | 役職 | 給与 |
|---|---|---|
| 1 | マネージャー | 500万 |
| 2 | エンジニア | 400万 |
ここで、給与は役職に直接依存しています。このテーブルを第三正規化にするためには、役職と給与の関係を別のテーブルに分離すべきです。
従業員と役職のテーブル:
| 従業員ID | 役職 |
|---|---|
| 1 | マネージャー |
| 2 | エンジニア |
役職と給与のテーブル:
| 役職 | 給与 |
|---|---|
| マネージャー | 500万 |
| エンジニア | 400万 |
まとめ:
- テーブルは第二正規化に合致していること。
- トランジティブ依存（非キー列が他の非キー列に依存している状態）が存在していないこと。
第二正規化と第三正規化の違い
これだけでは第二正規化と第三正規化の違いがいまいち理解できなかったので、さらに両者の違いを説明します。
商品と取引の例
ある店舗で商品の取引を記録するシステムがあり、以下のようなテーブルがあるとします。
| 取引ID | 商品名 | 単価 | 数量 | 取引金額 |
|---|---|---|---|---|
| 1 | りんご | 100円 | 3 | 300円 |
| 2 | バナナ | 150円 | 2 | 300円 |
このテーブルでは、商品ごとの単価と、その取引における数量に基づいて「取引金額」が計算されます。
第二正規化
問題点:
取引IDと商品名の組み合わせがこのテーブルのプライマリキーだとします。この場合、「単価」は商品名にのみ依存しており、取引IDとは無関係です。このような部分的な依存関係が存在すると、第二正規化に違反します。
正規化:
商品情報と取引情報を別々のテーブルに分割します。
第二正規化後のテーブル
商品テーブル:
| 商品名 | 単価 |
|---|---|
| りんご | 100円 |
| バナナ | 150円 |
取引テーブル:
| 取引ID | 商品名 | 数量 | 取引金額 |
|---|---|---|---|
| 1 | りんご | 3 | 300円 |
| 2 | バナナ | 2 | 300円 |
このステップで「単価」が取引テーブルから商品テーブルに移動されました。
第三正規化
問題点:
「取引金額」は、他の非キー列（単価と数量）に依存しています。このようなトランジティブな依存関係があると、第三正規化に違反します。
正規化:
実際には上記のテーブル構造において「取引金額」列は冗長です。なぜなら、単価と数量から計算できるためです。従って、第三正規化を達成するためには、取引テーブルから「取引金額」列を削除すれば良いです。
第三正規化後のテーブル
商品テーブル:
| 商品名 | 単価 |
|---|---|
| りんご | 100円 |
| バナナ | 150円 |
取引テーブル:
| 取引ID | 商品名 | 数量 |
|---|---|---|
| 1 | りんご | 3 |
| 2 | バナナ | 2 |
このステップで「取引金額」が取引テーブルから削除されました。取引金額は「単価」と「数量」から計算できるため、テーブル内に冗長な情報として持つ必要はありません。
まとめ
- 第二正規化は「部分的な依存関係」を排除することに焦点を当てています。すなわち、プライマリキーの一部にしか依存しない非キー列を排除することです。
- 第三正規化は「トランジティブな依存関係」を排除することに焦点を当てています。つまり、非キー列が他の非キー列に依存しているような状況を排除することです。
なぜ正規化が必要なのか？
正規化により、データベースの冗長性と矛盾が削減され、データの整合性が向上します。
最後に
現在受託開発会社でエンジニアをしながら、自分でサービスを開発しています。
日々技術や個人開発の発信をしているので、よかったらフォローをお願いします。
また、この記事にいいねやコメントなどいただけると励みになります。
よろしくお願いします!