正規化
自分の理解のために正規化についてちょっとまとめてみます。
- 正規化とはリレーショナルデータベースのキレイな設計方針の一つです。
- 正規化の目的は、1 fact in 1 place (一事実一箇所) にすることで、データの更新時の不整合、データの冗長性を排除することです。
- 正規化には第1正規化〜第5正規化まであり、その他にボイスコット正規化などもあります。
- この中で一般的に使われるのは、第3正規化までです。
なお、以下の説明(特に定義の部分)は、情報処理教科書データベース(ISBN:4798104957)を参考にしました(この参考書、説明が小難しくなくていいかも)。
非正規化
非正規化は第1正規化にもなってない状態です。
例えば次の、コンビニのお買い物伝票テーブルの例(大分簡略化してます)は、非正規化のテーブルの例です。
伝票
伝票番号 | 商品コード | 商品名 | 単価 | 数量 | 小計 | 合計 | 販売員コード | 販売員名 |
0001 | AAAA | 飲むヨーグルト | 200 | 1 | 200 | 600 | AAAA | 山田太郎 |
BBBB | 1Mカテキン茶 | 200 | 2 | 400 | ||||
0002 | CCCC | バナナ弁当 | 500 | 1 | 500 | 700 | BBBB | ジョン万次郎 |
BBBB | 1Mカテキン茶 | 200 | 1 | 200 |
このデータをRDBのテーブルで表現しようと思ったら、1つのレコードに
商品コード1、商品名1、商品コード2、商品名2、、、
といった「繰り返し項目」が存在することになります。
「繰り返し項目の存在」は、
- 拡張性に乏しい(1度に買える商品の最大数が決まっている)
- 検索処理を複雑(商品ごとの統計をとったりするSQLが複雑よ)
といったデメリットがあります。
この繰り返し項目は、非単純属性と呼ばれます。
第1正規化
第1正規化の定義
- すべての属性が、単純属性である。
つまり、繰り返し項目をなくした状態です。
先のテーブルから繰り返し項目部分を「明細」として別テーブルに分割します。
伝票
伝票番号 | 合計 | 販売員コード | 販売員名 |
---|---|---|---|
0001 | 600 | AAAA | 山田太郎 |
0002 | 700 | BBBB | ジョン万次郎 |
明細
伝票番号 | 商品コード | 商品名 | 単価 | 数量 | 小計 |
---|---|---|---|---|---|
0001 | AAAA | 飲むヨーグルト | 200 | 1 | 200 |
0001 | BBBB | 1Mカテキン茶 | 200 | 2 | 400 |
0002 | CCCC | バナナ弁当 | 500 | 1 | 500 |
0002 | BBBB | 1Mカテキン茶 | 200 | 1 | 200 |
第2正規化
第2正規化の定義
- 第1正規化であること
- すべての非キー属性は、候補キーに対して、部分関数従属していないこと(完全関数従属であること)
関数従属というのは、A→B(Aが決まればBも決まる、BがAに従属している)という関係です。
例えば、商品コード→商品名、が関数従属です。
上記明細表では、(伝票番号、商品コード)という組が主キーになりますが、
この2つに対して従属してるのはいいですが、
このうちの一部(どちらか)に従属しているというのは少し問題です。
この一部に従属しているという意味で、部分関数従属と呼ばれます。
第2正規化にするには、上の明細テーブルから商品テーブルを分離します。
商品
商品コード | 商品名 | 単価 |
---|---|---|
AAAA | 飲むヨーグルト | 200 |
BBBB | 1Mカテキン茶 | 150 |
CCCC | バナナ弁当 | 500 |
明細
伝票番号 | 商品コード | 数量 | 小計 |
---|---|---|---|
0001 | AAAA | 1 | 200 |
0001 | BBBB | 2 | 300 |
0002 | CCCC | 1 | 500 |
0002 | BBBB | 1 | 200 |
もし1Mカテキン茶の名前を変えたいときは、商品テーブルの該当レコードのみ更新すればよいことがわかります。
第3正規形
第3正規化の定義
- 第2正規化であること
- すべての非キー属性は、候補キーに対して、推移的関数従属していないこと
推移的関数従属というのは、A→B→C (Aが決まればBもきまる、Bが決まればCも決まる)という関係です。
例えば、上の伝票テーブルの、伝票番号→販売員コード→販売員名 というのは、推移的関数従属の関係です。A→B と B→C を分けることで、より、One Fact One Place に近づきます。
第3正規形にすると、伝票テーブルから、販売員テーブルが分離されます。
販売員
販売員コード | 販売員名 |
---|---|
AAAA | 山田太郎 |
BBBB | ジョン万次郎 |
伝票
伝票番号 | 合計 | 販売員コード |
---|---|---|
0001 | 600 | AAAA |
0002 | 700 | BBBB |
また、第3正規形では、商品テーブルの小計や、伝票の合計などの、
他の値から導きだせる導出項目も取り除きます。
これらは、別テーブルにするのでなく、カラムから削除します。
ここまでで第3正規形です。
ボイスコット正規形、第4、5の正規形もありますが、
疲れたのでやめときます。
あー、なんか問題とくのめんどくさい現実逃避な気がしてきた。。。