データベースの正規化の基本
はじめに
データベース設計の正規化のやり方を学んだので、メモとして残します。
正規化とは
データベースで保持するデータの冗長性と非一貫性を排除した状態にすること。これによって余計な処理が発生したり、複数箇所に同じデータが存在したり、タイムラグによってデータが消えてしまうなどの問題を減らすことができる。
この記事では第3正規形までを扱います
正規化の手順
第1正規形
目標：各フィールドに値が1つだけ入り、主キーが決まれば1つの値が決まる状態にします。
例として、以下のテーブルを第1正規形にします。
扶養者
| 社員ID | 社員名 | 子 |
|---|---|---|
| 000A | 加藤 | 達夫 信二 |
| 000B | 藤本 | |
| 001F | 三島 | 一郎 二郎 |
これだと値を1つに決められないので以下のように行を追加します。
扶養者
| 社員ID | 社員名 | 子 |
|---|---|---|
| 000A | 加藤 | 達夫 |
| 000A | 加藤 | 信二 |
| 000B | 藤本 | |
| 001F | 三島 | 一郎 |
| 001F | 三島 | 二郎 |
これでも主キーによって値を一意に決めることはできません。
- 社員IDだけ=>{社員ID、社員名}が{000A, 加藤}, {000A, 鈴木}のように同じ社員IDで異なる社員名を持つことがある
- 社員ID＋社員名=>社員は特定できるが、子が複数いる場合にデータを一意に決められない
社員ID＋社員名＋子の３つを指定すれば値を一意に決められそうですが、主キーはNULLにはできません。 しかしこの場合すべての社員が子を持ってるとは限らず、NULLになることがあります。
これは扶養者テーブルが社員と扶養者という2つのエンティティの情報を含んでいるためです。
そのため、子を別テーブルに分割します。
社員
| 社員ID | 社員名 |
|---|---|
| 000A | 加藤 |
| 000B | 藤本 |
| 001F | 三島 |
扶養者
| 社員ID | 子 |
|---|---|
| 000A | 達夫 |
| 000A | 信二 |
| 001F | 一郎 |
| 001F | 二郎 |
これによって、各テーブルで主キーが決まれば値が決まるようになりました
第2正規形
目標：使われない主キーをなくし、すべての主キーによって値が決まる状態にします。
※部分関数従属を解消し、完全関数従属のみの状態
- 主キーの一部だけで値が決まる場合：部分関数従属
- すべての主キーによって値が決まる場合：完全関数従属
以下のテーブルを第2正規形にします。
社員
| 会社コード | 会社名 | 社員ID | 社員名 | 年齢 | 部署コード | 部署名 |
|---|---|---|---|---|---|---|
| C0001 | A商事 | 000A | 加藤 | 40 | D01 | 開発 |
| C0001 | A商事 | 000B | 藤本 | 32 | D02 | 人事 |
| C0001 | A商事 | 001F | 三島 | 50 | D03 | 営業 |
| C0002 | B化学 | 000A | 斉藤 | 47 | D03 | 営業 |
| C0002 | B化学 | 009F | 田島 | 25 | D01 | 開発 |
| C0002 | B化学 | 010A | 渋谷 | 33 | D04 | 総務 |
このテーブルは主キー{会社コード、社員ID}がわかれば値が１つに決まるので、第1正規形を満たしています。
しかし、会社名を決めるには会社コードだけが必要で、社員IDは使われません。
※それ以外の値は全ての主キーで決まります。
このテーブルを第２正規形にするには、一部しか使われていない主キーと、それによって決まる列（部分関数従属になってるキー列と従属列）をテーブルに切り出します。
今回は社員テーブルから会社コードと会社名を別のテーブルに分けます。
※会社コードがないと社員テーブルで特定できなくなるので残す
社員
| 会社コード | 社員ID | 社員名 | 年齢 | 部署コード | 部署名 |
|---|---|---|---|---|---|
| C0001 | 000A | 加藤 | 40 | D01 | 開発 |
| C0001 | 000B | 藤本 | 32 | D02 | 人事 |
| C0001 | 001F | 三島 | 50 | D03 | 営業 |
| C0002 | 000A | 斉藤 | 47 | D03 | 営業 |
| C0002 | 009F | 田島 | 25 | D01 | 開発 |
| C0002 | 010A | 渋谷 | 33 | D04 | 総務 |
会社
| 会社コード | 会社名 |
|---|---|
| C0001 | A商事 |
| C0002 | B化学 |
これで、それぞれのテーブルで全てのキーを使って特定できるようになり第2正規形になりました。
第2正規形にするメリット
以前のテーブルだと、社員の情報が不明の会社Cをテーブルには追加できません。なぜなら主キーは{会社名、社員ID}であり、情報が不明だと{会社C、NULL}になってしまうためです。
また、{C0001, A商事}の他に{C0001, A商社}というように、データがブレて登録される可能性があります。
これらの問題を、第2正規形にしたことで、社員情報がわからなくても会社Cを会社テーブルに登録でき、それを読むことでデータがブレる心配がなくなりました。
第3正規形
目標： 非主キーの値が主キーのみによって決まる状態 にします。
※段階的な関数従属性がない状態
第2正規形は以下です（再掲）
社員
| 会社コード | 社員ID | 社員名 | 年齢 | 部署コード | 部署名 |
|---|---|---|---|---|---|
| C0001 | 000A | 加藤 | 40 | D01 | 開発 |
| C0001 | 000B | 藤本 | 32 | D02 | 人事 |
| C0001 | 001F | 三島 | 50 | D03 | 営業 |
| C0002 | 000A | 斉藤 | 47 | D03 | 営業 |
| C0002 | 009F | 田島 | 25 | D01 | 開発 |
| C0002 | 010A | 渋谷 | 33 | D04 | 総務 |
会社
| 会社コード | 会社名 |
|---|---|
| C0001 | A商事 |
| C0002 | B化学 |
社員テーブルの部署名を見ると、「開発、人事、営業、総務」の4つあります。しかし、社員が1人もいないだけで、それ以外の部署があるかもしれません。
現状の社員テーブルでは社員がいない部署の登録はできません。なぜなら主キーは{会社コード、社員ID}であり、{部署コード・部署名}だけ入力しても社員IDがNULLになってしまうためです。
こうなる理由は、社員テーブルの中にまだ隠れた関係が残っているためです。具体的には
- 部署コードが決まれば部署名が決まる
- {会社コード、社員ID}が決まれば部署コードが決まる
となり、以下のように段階的な関係があります。
|会社コード、社員ID|→|部署コード|→|部署名|
この不都合を解消するには、非主キーとそれによって決まる列を別のテーブルに分割をします。
今回は社員テーブルから部署コードと部署名を別のテーブルに分けます。
社員
| 会社コード | 社員ID | 社員名 | 年齢 | 部署コード |
|---|---|---|---|---|
| C0001 | 000A | 加藤 | 40 | D01 |
| C0001 | 000B | 藤本 | 32 | D02 |
| C0001 | 001F | 三島 | 50 | D03 |
| C0002 | 000A | 斉藤 | 47 | D03 |
| C0002 | 009F | 田島 | 25 | D01 |
| C0002 | 010A | 渋谷 | 33 | D04 |
部署
| 部署コード | 部署名 |
|---|---|
| D01 | 開発 |
| D02 | 人事 |
| D03 | 営業 |
| D04 | 総務 |
会社
| 会社コード | 会社名 |
|---|---|
| C0001 | A商事 |
| C0002 | B化学 |
これによって、すべてのテーブルで非キー列はキー列に対してのみ従属するようになり、第3正規形になりました
まとめ
- 第1正規化は、主キーによって値が一意に決まるようにすること
- 第2正規化は、使われない主キーをなくし、全ての主キーを使って値が一意に決まるようにすること
- 第3正規化は、非主キーによって決まる関係をなくし、主キーのみによって決まるようにすること
最後に
場合によっては正規化しないほうがいいこともあるようで、どういうときにそうなるのかも今後勉強します
間違いなどありましたらコメントいただけるとありがたいです！
Discussion