正規化とは

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

前提

キー

• 主キー
  • 行を一意に識別する識別子
• 複合キー
  • 複数の属性を組み合わせて構成される主キー
• 外部キー
  • 別のテーブルを参照するためのキー
• 候補キー
  • 行を一意に識別できる性質を持つ属性の集合で、既約であり、極小（必要最小限の属性数）であるもの
• スーパーキー
  • 行を一意に識別できる性質を持つ属性と余分な属性の組み合わせ
• 既約
  • 余分な属性がない状態（≒属性をそれ以上減らすことができない）こと

主キー：{社員番号, 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

非正規系

1NF

2NF

2NF前

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

2NF 売上明細テーブル

商品テーブル

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

3NF

3NF前

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

3NF 売上明細テーブル

顧客テーブル

BCNF

BCNF前

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

BCNF 受講テーブル

担任テーブル

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

4NF

4NF前

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

4NF 趣味テーブル

好物テーブル

5NF

5NF前

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

5NF 在庫テーブル

仕入れ先テーブル

メーカーテーブル

所感

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

参考

• amzn.to - 理論から学ぶデータベース実践入門 ~リレーショナルモデルによる効率的なSQL (WEB+DB PRESS plus)
• e-words.jp - 部分関数従属 【partial functional dependency】
• datascience-lab.sakura.ne.jp - さまざまな種類の「キー」を理解する
• poppingcarp.com - 主キー、候補キー、外部キー、スーパーキーなどのいろんなキーについて｜データベースの基礎
• koseki2580.github.io - 正規化
• zenn.dev - イラストで理解するデータベースの正規化
• youtube.com - データベースの正規化（第1正規形、第2正規形、第3正規形）
• tabibou.com - 【データベース】第１正規化～第5正規化、ボイスコッド正規化例
• poppingcarp.com - 主キー、候補キー、外部キー、スーパーキーなどのいろんなキーについて｜データベースの基礎