テーブルの正規化について【第3正規系まで】
概要
テーブルの正規化について改めて復習し整理したのでメモ。
正規化の目的
更新(データ登録、INSERTも)時の不都合を防ぐために実施する。
正規化には第５まであるが、第３正規形まで実施すると第４、５まで満たすことが多いため第３までは必ず実施する。
正規化
第１正規形
１つのセルには複数のデータを含めず、１つの値のみ含める
セルに複数の値があると検索が複雑化してしまうなどのデメリットがある。(where句の複雑化)
以下のテーブルは社員ID３のように子供を複数持つため、第１正規形ではない。
◼︎社員テーブル
| 社員ID | 社員名 | 子供 |
|---|---|---|
| 1 | 小笠原 | せいや |
| 2 | 梅津 | しょう |
| 3 | 岡林 | ようた ゆうや |
| 4 | 山本 |
第１正規形に変換すると以下のようになる。
◼︎社員テーブル
| 社員ID | 社員名 |
|---|---|
| 1 | 小笠原 |
| 2 | 梅津 |
| 3 | 岡林 |
| 4 | 山本 |
◼︎子供テーブル
| 社員ID | 子供 |
|---|---|
| 1 | せいや |
| 2 | しょう |
| 3 | ようた |
| 3 | ゆうや |
INNER JOINで子供を持つ社員だけ、LEFT OUTER JOINで子供を持たない社員も取得することが可能。
第２正規形
テーブル内の部分関数従属を全て完全関数従属にする。
関数従属はよくy=f(x)とか出てくるが、要はAの値が決まればBの値がわかるという方がイメージしやすかった。
以下の社員テーブルを考える。主キーは社員IDと会社ID
◼︎社員テーブル
| 社員ID | 社員名 | 会社ID | 会社名 | 年齢 |
|---|---|---|---|---|
| 1 | 小笠原 | AAA | A商事 | 39 |
| 2 | 梅津 | AAA | A商事 | 32 |
| 3 | 岡林 | BBB | B商事 | 20 |
| 4 | 山本 | CCC | C商事 | 43 |
会者IDが決まれば会社名もわかる。つまり会社名は会社IDに従属している、という感じ。
たとえば年齢は「○○商事の誰」という情報がなければわからないため、主キーである社員IDと会社IDに従属している。(完全関数従属)
部分関数従属とは「会社名が主キーの一部である会社IDのみに従属している」状態であり、第2正規形ではこれを解消し以下の形にする。
※社員IDがわからなくても会社IDさえ決まれば会社名もわかるから、部分的に従属していると言える
◼︎社員テーブル
| 社員ID | 社員名 | 会社ID | 年齢 |
|---|---|---|---|
| 1 | 小笠原 | AAA | 39 |
| 2 | 梅津 | AAA | 32 |
| 3 | 岡林 | BBB | 20 |
| 4 | 山本 | CCC | 43 |
◼︎会社テーブル
| 会社ID | 会社名 |
|---|---|
| AAA | A商事 |
| AAA | A商事 |
| BBB | B商事 |
| CCC | C商事 |
この形にすることで、以下のメリットが得られる。
社員がまだ存在しない会社D商事を追加する場合を考える。
第二正規化前のテーブルにD商事の情報を追加しようとすると以下の形になる。
◼︎社員テーブル
| 社員ID | 社員名 | 会社ID | 会社名 | 年齢 |
|---|---|---|---|---|
| 1 | 小笠原 | AAA | A商事 | 39 |
| 2 | 梅津 | AAA | A商事 | 32 |
| 3 | 岡林 | BBB | B商事 | 20 |
| 4 | 山本 | CCC | C商事 | 43 |
| DDD | D商事 |
しかし主キーにはNULLを追加することができないため、この形にするのは不可能。
※今回だと社員がいないので、主キーである社員IDがNULLになってしまう。
第二正規形ではこのような問題を解消することが可能。
第3正規系
推移的関数従属を解消する
社員テーブルに部署IDと部署名がある場合を考える。
社員IDと会社IDが決まれば部署IDが決まり、部署名は部署IDで決まる。
※A商事の小笠原さんという情報がわかれば部署がわかる
よって２段階の従属が発生しており、この状態を推移的関数従属という。
◼︎社員テーブル
| 社員ID | 社員名 | 会社ID | 年齢 | 部署ID | 部署名 |
|---|---|---|---|---|---|
| 1 | 小笠原 | AAA | 39 | 111 | 営業 |
| 2 | 梅津 | AAA | 32 | 222 | 開発 |
| 3 | 岡林 | BBB | 20 | 333 | 総務 |
| 4 | 山本 | CCC | 43 | 111 | 営業 |
◼︎会社テーブル
| 会社ID | 会社名 |
|---|---|
| AAA | A商事 |
| AAA | A商事 |
| BBB | B商事 |
| CCC | C商事 |
第3正規系に変換した形は以下。
◼︎社員テーブル
| 社員ID | 社員名 | 会社ID | 年齢 | 部署ID |
|---|---|---|---|---|
| 1 | 小笠原 | AAA | 39 | 111 |
| 2 | 梅津 | AAA | 32 | 222 |
| 3 | 岡林 | BBB | 20 | 333 |
| 4 | 山本 | CCC | 43 | 111 |
◼︎会社テーブル
| 会社ID | 会社名 |
|---|---|
| AAA | A商事 |
| AAA | A商事 |
| BBB | B商事 |
| CCC | C商事 |
◼︎部署テーブル
| 部署ID | 部署名 |
|---|---|
| 222 | 開発 |
| 333 | 総務 |
| 111 | 営業 |
第3正規系にするメリットは第２正規系と同様、NULLの値を追加する場合の対応が可能なこと
※まだ社員がいない部署を追加する場合、第3正規化の前だと社員IDなどがNULLのレコードを追加しなければならない。しかし、主キーの制約でそれは不可。
正規化の欠点と非正規化
正規化によってパフォーマンスの低下が発生することもある。
SQLにおいて、テーブルの結合はコストのかかる動作になる。
正規化を行なった数万レコードのテーブル同士を結合してSELECTすると処理時間が増えてしまう。
その場合、テーブルにカラムを追加すること(サマリーデータ)で結合せずに済むパターンもあるので、考慮してみる。
参考書籍
Discussion