正規化理論について

bmf-san データベース
#正規化理論#正規化

正規化とは

データの重複をなくし、データの論理的不整合を防ぐように設計すること。

#　前提

キー

  • 主キー
    • 行を一意に識別する識別子
  • 複合キー
    • 複数の属性を組み合わせて構成される主キー
  • 外部キー
    • 別のテーブルを参照するためのキー
  • 候補キー
    • 行を一意に識別できる性質を持つ属性の集合で、既約であり、極小（必要最小限の属性数）であるもの
  • スーパーキー
    • 行を一意に識別できる性質を持つ属性と余分な属性の組み合わせ
  • 既約
    • 余分な属性がない状態（≒属性をそれ以上減らすことができない）こと
社員番号 ID 名前 性別 住所 電話番号
1 1001 山田太郎 男 東京都千代田区 03-1234-5678
2 1002 田中花子 女 東京都渋谷区 03-2345-6789
3 1003 鈴木次郎 男 東京都新宿区 03-3456-7890
4 1004 佐藤三郎 男 東京都港区 03-4567-8901
5 1005 高橋四郎 男 東京都目黒区 03-5678-9012

主キー：{社員番号, ID}

候補キー：{社員番号, ID}, {電話番号}

スーパーキー：{社員番号, ID}, {社員番号, ID, 名前}, {社員番号, ID, 氏名, 性別} etc...

##　関数従属性

  • 関数従属性
    • Aが決まればBも決まるという性質のこと
  • 部分関数従属性
    • ある属性がいずれかの候補キー1つに対して従属している性つを持っていること
  • 完全関数従属性
    • 非キー属性が全て主キーに対して関数従属している性質を持っていること
  • 推移的関数従属性
    • 非キー属性間の関数従属性で、Aが決まればBも決まり、Bが決まるとCも決まるという性質を持っていること
  • 結合従属性
    • 分解したリレーションを結合すると元のリレーションに戻る性質のこと（≒無損失分解）
  • 多値従属性
    • AとCの属性について、BとCが独立でBがAに依存している関係性
    • 結合従属性の特殊なケース
  • 無損失分解
    • 分解後も分解したものを結合することで元のリレーションを再構築できる形でリレーションを分解すること

正規形

第1正規形（1NF）

  • 行が上下に、列が左右に順序付けされていない
    • SQLの仕様としては順序があるが、順序に依存したクエリを書かないようにすれば良い
      • ex. SELECT *を避ける、ORDER BYの引数でカラム位置をしてする（ORDER BY 1）など
  • 重複行が存在しない
  • NULLが含まれていない
  • 1つの列にはドメイン（データ型）を満たす1つの値だけが含まれている

第2正規形（2NF）

  • 1NFを満たしている
  • 部分関数従属性を取り除き、完全関数従属性となっていること
    • 全ての非キー属性が候補キーに完全関数従属となっている

第3正規形（3NF）

  • 2NFを満たしている
  • 推移的関数従属性を取り除いていること
    • 全ての非キー属性が候補キーに対して推移的関数従属となっていない

ボイスコッド正規形（BCNF）

  • 3NFを満たしていること
  • 候補キーの部分関数従属と推移的関数従属が取り除かれていること
  • 自明ではない関数従属性が全て取り除かれ、関数従属性による無損失分解がそれ以上できないこと
    • 自明 {受注番号, 商品番号} →{商品番号}
    • 自明ではない {商品名} → {材料名}

第4正規形（4NF）

  • 多値従属性による正規化

第5正規形（5NF）

  • 自明ではない、または暗黙的ではない全ての結合従属性が取り除かれた状態

正規化

1NF

非正規系

伝票番号 製品番号 製品名
1 A001A002A003 リンゴミカンバナナ
2 A004A005A006 ブドウナシイチゴ

1NF

伝票番号 製品番号 製品名
1 A001 リンゴ
1 A002 ミカン
1 A003 バナナ
2 A004 ブドウ
2 A005 ナシ
2 A006 イチゴ

2NF

2NF前

伝票番号 製品番号 製品名
1 A001 リンゴ
1 A002 ミカン
1 A003 バナナ
2 A004 ブドウ
2 A005 ナシ
2 A006 イチゴ

候補キーである{伝票番号, 製品番号}と非キー属性である製品名が部分関数従属している。

2NF 売上明細テーブル

伝票番号 製品番号
1 A001
1 A002
1 A003
2 A004
2 A005
2 A006

商品テーブル

製品番号 製品名
A001 リンゴ
A002 ミカン
A003 バナナ
A004 ブドウ
A005 ナシ
A006 イチゴ

部分関数従属は候補キーが複合キーの場合にのみ発生するので、候補キーが単一属性である場合は発生しない。

3NF

3NF前

伝票番号 製品番号 顧客番号 顧客名
1 A001 B1 リンゴ商事
1 A002 B1 ミカン商事
1 A003 B1 バナナ商事
2 A004 C1 ブドウ商事
2 A005 C1 ナシ商事
2 A006 C1 イチゴ商事

主キーが{伝票番号, 製品番号}の時、{伝票番号, 顧客番号}→{顧客番号}→{顧客名}と推移的従属している。

3NF 売上明細テーブル

伝票番号 製品番号
1 A001
1 A002
1 A003
2 A004
2 A005
2 A006

顧客テーブル

顧客番号 顧客名
B1 リンゴ商事
B1 ミカン商事
B1 バナナ商事
C1 ブドウ商事
C1 ナシ商事
C1 イチゴ商事

BCNF

BCNF前

名前 科目 担任
ボブ 数学 山田
トム 数学 佐藤
ジョン 数学 鈴木
ジョン 英語 安藤

主キーが{名前, 科目}の時、{担任}→{科目}の関数従属性があり、決定項（A→BのAのこと）がスーパーキーではない。

BCNF 受講テーブル

名前 科目
ボブ 数学
トム 数学
ジョン 数学
ジョン 英語

担任テーブル

担任 科目
山田 数学
佐藤 数学
鈴木 数学
安藤 英語

{名前, 科目}→{担任}の情報が失われたため、ジョンの数学の担任が誰か分からなくなった。

4NF

4NF前

名前 趣味 好物
田中 野球 ラーメン
鈴木 サッカー 寿司
佐藤 バスケ カレー

主キーが{名前, 趣味, 好物}であるとき、{名前}→{趣味}→{好物}と複数の属性が決まる。

4NF 趣味テーブル

名前 趣味
田中 野球
鈴木 サッカー
佐藤 バスケ

好物テーブル

名前 好物
田中 ラーメン
鈴木 寿司
佐藤 カレー

5NF

5NF前

店舗 在庫商品 製造元
東京 TV A社
東京 TV B社
東京 PC A社
神奈川 TV A社

{店舗}→{在庫商品}, {店舗}→{製造元}, {在庫商品}→{製造元}と複数に分解できる。

5NF 在庫テーブル

店舗 在庫商品
東京 TV
東京 TV
東京 PC
神奈川 TV

仕入れ先テーブル

店舗 仕入先
東京 A社
東京 B社
東京 A社
神奈川 A社

メーカーテーブル

店舗 製造元
東京 A社
東京 B社
東京 A社
神奈川 A社

所感

BCNF以降の理解が浅いのでちょっと自信がない。。。

参考