試験とか受けるたびに正規化について覚えなおしている気がするので、
デスペ受けるついでに人に説明する→記憶に定着する、という期待を狙って書いてみる。
簡単な説明
正規化のポイントは関数従属
関数従属について
数学で関数というものを習っていると思うが、
二つの変数 x と y があり、入力 x に対して、出力 y の値を決定する規則
を関数という。
例えば、下記の例にxに適当な数字を入れれば、yの値が決まる。
y = 4x
この「X」にたいして「Y」が一つ決まるの関係のことを
YはXに対して関数従属する、といい
X \to Y
と表現する
数学の世界に限ったことではなく
個人を特定する数字、本を特定する数字、など現実世界にもある。
「マイナンバー」が決まれば「日本人」が特定できる(マイナンバー→個人)
「ISBN」が決まれば「本」が特定できる(ISBN->本)
正規化を理解するコツはデータベース上にある、余計な関数従属を見抜くことにある。
第2正規型
主キーの一部 → 非キー列
の関数従属を部分関数従属という。
第2正規型とはこの部分関数従属がない状態のことをいう。
主キーの一部、という記述から分かるように、そもそも第2正規化かどうか、という問題はキー列が複数あるテーブルに対してしか発生しようがない。
まず第2正規型かどうか？という状況になったときは、そのテーブルに存在する全てのキー列をペアに、非キー列の値が一意に決まる以前に、
一部のキーだけで値が決まってしまう非キー列があるかどうか
を意識するのが第2正規型かどうかを考えるポイントとなる。
第2正規型にしないと何が起こるのか？
式の例では日付、商品cdがキー列のテーブルで
商品cd->商品名の関数従属精があるとする
販売実績テーブル
| _日付_ | _商品cd_ | 商品名 | 売上 |
|---|---|---|---|
| 20161225 | 109 | クソスマスケーキ | 300000 |
| 20161225 | 110 | 靴下 | 400000 |
| 20161225 | 109 | クソスマスケーキ | 60000 |
| 20161225 | 112 | ソリ | 3000 |
問題点1.関数従属のある列の更新が大変
たとえば109の商品「クソスマスケーキ」に誤字が発覚したため、更新してほしいとの要望が発生した。
上記の場合、1行目、2行目の2箇所を修正しなければならない。
データが多くなれば、出現場所全てを修正しなければならないので、データがちぐはぐになる危険性がある。
問題点2.売上のない商品を登録できない。
例の商品実績テーブルは日付がキー列のためnullにすることができない。
このテーブルだけだと、販売実績のない商品、つまり日付と売上の無いレコードをシステム上で扱うことが出来ない。
対策
下記のように関数従属のある列を適切に切り分ければ、変更箇所は商品テーブルの一箇所で済み、
販売実績のない商品は商品テーブルの方に登録することもできる。
販売実績テーブル
| _日付_ | _商品cd_ | 売上 |
|---|---|---|
| 20161225 | 109 | 300000 |
| 20161225 | 110 | 400000 |
| 20161225 | 109 | 60000 |
| 20161225 | 112 | 3000 |
商品テーブル
| _商品cd_ | 商品名 |
|---|---|
| 109 | クリスマスケーキ |
第2正規型まとめ
第2正規型とは、部分関数従属(キーの一部→非キー列)がない状態のことである。
部分関数従属がない状態、つまり全ての主キー、非キー列間に関数従属性がなりたっていることを完全関数従属という。
第3正規型
非キー列 → 非キー列
の関数従属を推移関数従属という。
推移関数従属、というとわかりにくいが、
主キー → 非キー列1 → 非キー列2
簡略化: 主キー → 非キー列2
という風に主キーから段階的に推移するイメージができると
「推移関数従属」という言葉と意味が覚えられると思う。
下記の販売実績テーブルではオプションcd → オプション名に関数従属があるとする。
(主キー → オプションcd → オプション名 に推移関数従属がある)
販売実績テーブル
| _日付_ | _商品cd_ | 売上 | オプションcd | オプション名 |
|---|---|---|---|---|
| 20161225 | 109 | 300000 | 1 | 蝋燭セット |
| 20161225 | 110 | 400000 | 2 | 洗濯ネット |
| 20161225 | 109 | 60000 | 1 | 蝋燭セット |
| 20161225 | 112 | 3000 | 4 | トナカイ3匹 |
| 20161225 | 109 | 60000 | 5 | 和蝋燭 |
第3正規型にしないとなにが起こるのか？
更新が大変&売上のないオプションが登録できない、
第2正規形とここはほぼ同じ内容になるので省略
※今後の学習次第で特別に加筆の必要に気付いた場合、加筆します。
第3正規型まとめ
第2正規型で非キー列が主キーに、非推移的に関数従属する時、
第3正規型である、といえる。
正規型まとめ
もちろん下位の正規型の条件を満たす、という前提があるが、
完結にまとめると以下のようになる。
第2正規型:キーの一部 → 非キー列に関数従属性がないこと
第3正規型:非キー列 → 非キー列の関数従属精がないこと
正規化のデメリット
正規化すればするほど、更新コスト&リスクが減る代わりに検索コストが増加する。
検索スピードに課題がある場合、ハードウェアや構成に手を入れる選択肢の他に、
予算がない場合はあえて非正規化して対策する、という場合があるらしい。
補足
デースペは問題にBCNF,4NF,5NFが出てきているので、そちらも理解しておく必要がある。