正規化

作成日時：2024-08-25
更新日時：2025-11-04

正規化はなんのためにするのか

• 操作時異状(更新/挿入/削除)を防ぐ
• データの整合性と一貫性を保つ
• レコードサイズも小さくなるので、1ページに大量データが収まる
  ⇒メモリ上にデータが存在する確率が高まり、パフォーマンスが向上する。
• 単一責務
• FKなどの制約で整合性担保

あえてやらないパターン

• その時点の情報保持
  • マスターが履歴を持ってもいいけど遅くなる
• 結合を減らす

正規形

第1正規形

下記の条件を満たす場合。

リレーションR(A1, A2, A3...)は、その各ドメインdom(Ai) (i=1...n)がシンプルである

• ドメイン：異なる元の集合(set)。
• 元：集合の中の要素
• ドメインがシンプル。アトム。
  • 直積だったりべき集合(部分集合)が存在しない
  • 属性が全て単一値を取る
  • それ以上分割できない。
  • 繰り返し項目が無い、カラムの中にリレーションが存在しないこと。
    • 存在する場合は、非第1正規形または入れ子型リレーションという。
  • {受注, 受注明細[明細1, 明細2...]}は明細が冪集合なので非第1正規形

第2正規形

下記の条件を満たす場合。

1. Rは第1正規形である
2. Rの全ての非キー属性はRの各候補キーに完全関数従属していること

部分関数従属する非キーがあってはならない

第3正規形

下記の条件を満たす場合。

1. Rは第2正規形である
2. Rの全ての非キー属性はRのいかなる候補キーにも推移的に関数従属しない

ボイス・コッド正規形

下記のいずれかを満たす場合。

1.X -> Yは自明な関数従属である。
2.XはRのスーパーキーである

• 自明な関数従属
  • ニュアンスは違うが、YがXの部分集合。
• スーパーキー
  • 候補キーを含む属性集合
• 候補キーが非キーに関数従属しているとかはボイス・コッド正規形ではない
• 情報無損失分解ではあるが関数従属性保存ではない。
  • 第3正規形にとどめて、アプリで更新異常を防ぐが。
  • BCNFにして、アプリで元の関数従属性をチェックするか。
• 関数従属性保存
  • 元々のリレーションシップが持っていた関数従属性が失われる

第4正規形

下記のいずれかを満たす場合。

1. X->->Yは自明な多値従属性である
2. XはRのスーパーキーである

第5正規形

下記のいずれかを満たす場合。

1. *(X1, X2, X3..., Xi)*は自明な結合従属性である
2. 各XiはRのスーパーキーである

• 自明な結合従属性
  • X1, X2...のひとつがRそのもの
  • つまり元の表がそのままいる
• 結合従属性保存ではない
  • 分解後の1テーブルにデータを挿入した後、自然結合すると分解前に存在しなかったタプルが存在する

第6正規形

Sixth normal form - Wikipedia

J.C.Dateが提唱。
Rに保持される全ての結合従属性(結合依存関係)が自明なものである場合に限り、第6正規形 (6NF)。
非自明な結合従属性が存在しないこと。
どういう組み合わせでも元の表が存在する。
つまり、もう分解できない状態(PK + 非キーが0か1個)。

R(PK, A, B) は (PK,A), (PK,B) に無損失分解できるので5NF。
R(PK, A, B) はまだ「非自明な結合従属性」が存在するため6NFではない。
(PK,A), (PK,B)はそれぞれ5NF。

R(PK, A)は自明なので6NF。

PKのみのテーブルは必要か

場合による。主にNULLを消すか否かで。
例えば、R(PK, A, B)において下記のデータがあるとする。

X1(PK,A), X2(PK,B) に分解する際、それぞれ3レコードが作成される。
しかし、A,BがNULLのデータは”そのテーブルに存在しない”という形で表現できるので、

X1

X2

これらは結合すれば、元のテーブルに復元できる。PK-cを除けば。
PK-cの情報を復元するためには、PKのみのテーブルが必要になる。

NULLのレコードを残すならば、PKのみのテーブルはいらない。
結合すればすべてのPKを取得できるから。

メモ

• ビッグデータの分析で使っていたとか、いないとか。
  • カラムナフォーマット的な感じで
  • レコードサイズが小さければ、ページにデータが多く収まったりはする
• 暗黙の結合従属の分解も、垂直パーティショニングっぽい。
• NULLが消えるし、テーブルサイズは小さくなるし。
  →NULLが消えるかどうかは設計による。

時系列

そもそも6NFは時系列DBを考える過程でできたらしい。

• 第六正規形 - Wikipedia
• Temporal database - Wikipedia

履歴管理・時系列

例えば、ユーザー(ID, 連番, 名前, 電話番号…)というテーブルがあって、履歴管理する場合。
何か1項目でも更新したら、更新していないカラムの分も全てコピーして登録することになる。

6NFにしたら追加されるデータは最小で済むよね。
電話番号(ID, 連番, 電話番号)
のような感じで。

時系列も同様。
センサーX(機器ID, 日時, 値)
センサーY(機器ID, 日時, 値)
みたいな。

ドメインキー正規形(DKNF)

ドメインキー正規形 - Wikipedia

Wikipediaがわかりやすい。
1NF～6NFとは別の概念。

データベースにはドメイン制約とキー制約以外の制約が含まれていないことを要求します。

リレーションスキーマRに存在する全ての制約が、そのリレーションRのドメイン制約とキー制約によって論理的に導出される場合、そのリレーションはDKNFである。
DKNFは「導出できるもの」を「キーとドメイン」で表現することを目指す。
Wikipediaのサンプルがいい例。

導出することで更新時異状をなくす感じ。
FDによる導出もある意味、これに含まれる。

• ドメイン制約: カラムの取りうる値
• キー制約: 一意性、外部キー

DKNFを行ったとて自動でチェックされるわけではない。
現状のRDBMSでは、すべての整合性制約をDKNFだけで表現できない。
できたとしても、DBの負荷が大きい。
正直アプリ側でチェックしたほうが楽かつ柔軟。

理想論と言えば理想論。
アサーションが実装されればいいね。

基本的に第3正規形は第5正規形まで成立している

商品マスタ{商品ID(PK), 商品名}

上記リレーションは第3正規形まで分解した結果。
商品ID→→商品名(要素1つだけの集合)|Φととれるため
このリレーションは第4正規形を満たしていると言える。
商品マスタ{商品ID(PK), 商品名, 料金}
とかならば、自明ではないが、商品IDがスーパーキーだから第4正規形。

また、自明な結合従属性を持つため、第5正規形を満たしていると言える。
(更にいうなら第6正規形も)

多値従属性

Rを関係とし、A, B, Cを、Rの属性集合の任意の部分集合とする。
Rのある（A値、C値）対に対応するB値の集合がA値だけに依存し、C値には独立かつそのときに限り、
BはAに多値従属しているといい、次のように表す。
A→→B

もしくは、
リレーションRの任意の集合ABCにおいて、その射影AB,ACに情報無損失分解できる場合
Rには多値従属性が存在するといえる。

簡単に言えば、ある項目が決まれば複数の集合が決定されるやつ。
スーパーキーを考えると面倒になるので、いったん忘れる。

自明な多値従属性

• A→→B|Φ(空集合)であるとき
  • 簡単に言えば、多値従属性が1個の場合
• BがAの部分集合であるとき

2NF→3NFは情報無損失分解だが関数従属性は保存されない

R(A, B, C, D)において、
A->B, A->C, B->D, C->Dの関数従属がある場合、
R1(A, B, C)とR2(B->D)に分解すると、A->C->Dの関数従属性が保存されない。

3NF->BCNFの分解で失われるものと同じイメージ。