今更聞けないデータベースの正規化と正規形の種類についてまとめ

これは何？

• リレーショナル・データベース(RDB)の正規化について、書きながら理解を整理するためにまとめたものです。
• ネタ本は下記です。
  • 理論から学ぶデータベース実践入門 ~リレーショナルモデルによる効率的なSQL
• この本自体は柏手ものの素晴らしい仕事なのですが、正確ゆえにやや難解な印象があったため、雑に分かりやすく理解・説明することを試みます。

読むと役に立つかもしれない人

• テーブル設計しないといけなくなった人
• DBのテーブルは「正規化すべき」だと思うがいまいち理由ややり方が分かっていない
• 正規形にどんなものがあるかとにかく知りたい
• 「ボイスコッド正規形」って言ってみたい

基本となる概念

候補キー

• SQLでいう主キー
  • 候補キーはリレーショナルモデルの用語
  • (注)書籍ではリレーショナルモデルとSQLの用語を厳密に書き分けています
• 候補キー以外のキーを**「非キー」**と呼ぶ

スーパーキー

• 候補キー＋いくつかの非キー
  • 候補キーを全て含むキーの集合
• スーパーキーから非キーを除くと候補キー
  • スーパーキー=候補キー＋N個の非キー (N≧0)
  • 候補キーはスーパーキーの一種 (N=0の場合)

無損失分解

• 元に戻せる(情報が失われない)リレーション(≒テーブル)の分解のこと
• 換言すると、分解後の情報から、分解前が復元できるような、可逆の分解のこと

関数従属性(Functional Dependency, FD)

• Aの値が分かれば、Bの値も決まるという性質
  • Bは重複してもOK
  • Aは複数の要素の組み合わせでもOK
• 例えば…
  • 「スポーツ」が決まれば、「競技人数」が決まる
  • 「国」が決まれば、「タイムゾーン」が決まる
  • 「漫画」と「巻数」が決まれば、「発売日」が決まる
• 分かりやすいし、重要
• 後述する2NF～BCNFでは関数従属性を取り除き、無損失分解する

結合従属性(Join Dependency, JD)

• 候補キー自体に冗長性が含まれている性質
  • 要は無損失分解の余地がある状態のことっぽい
• 分かりにくいし、あまり重要でない
• 後述する4NF～6NFでは結合従属性を取り除き、無損失分解する

多値従属性(MultiValued Dependency, MVD)

• 結合従属性の一種
• カラムを①,②,③の3つの部分集合に分けた時、①②と①③で無損失分解できる状態
  • 関数従属性になぞらえると、Aの値が分かれば、Bの値の候補が決まる、みたいな感じ？(自信なし)
• 例えば…
  • 「ビル」が〇〇ビルなら「フロア」が決まる(1～3階のどれか)
  • 「ビル」が〇〇ビルなら「利用可能路線」が決まる(X線かY線か)
  • 分解前は、〇〇ビルに対して、6レコード(=3*2)あるイメージ
  • この場合、ビル・フロアと、ビル・利用可能路線の2つのリレーションに無損失分解できる

具体例

• 以降は以下のような(イマイチな)テーブルをもとに説明していきます。
• 登場する正規形の全てを1つの(イマイチな)テーブルで説明することができないので、2つ例を用意します。
• 書き方が微妙ですが、<>付きの列が、候補キーのイメージです。
• この状態で例1は第1正規形、例2はボイスコッド正規形です。(後述)

(例1)社員テーブル

<所属>	<名前>	会社最寄り駅	部	オフィス	趣味	趣味の道具
本社	鈴木	新宿	営業部	本社ビル11階	釣り	釣り竿
本社	田中	新宿	営業部	本社ビル12階	テニス	ラケット
本社	渡辺	新宿	開発部	本社ビル13階	麻雀	雀卓
本社	渡邉	新宿	開発部	本社ビル12階	麻雀	雀卓
大阪支社	佐藤	梅田	開発部	大阪支社ビル5階	釣り	釣り竿
大阪支社	山田	梅田	クリエイティ部	大阪支社ビル6階	釣り	釣り竿
大阪支社	田中	梅田	ポジティ部	大阪支社ビル5階	テニス	ラケット

(例2)プロジェクトテーブル

<社員名>	<プロジェクト>	<スキル>	<成果物>
鈴木	Aプロジェクト	AWS	要件定義書
鈴木	Aプロジェクト	Web	要件定義書
鈴木	Bプロジェクト	Web	テスト仕様書
田中	Aプロジェクト	AWS	要件定義書
鈴木	Aプロジェクト	AWS	基本設計書
鈴木	Aプロジェクト	Web	基本設計書
鈴木	Bプロジェクト	Web	テストエビデンス
田中	Aプロジェクト	AWS	基本設計書

正規形の種類

第1正規形(1NF)

• 条件
  • 順序付けのない行と列がある
  • 重複する行はない
  • 列の値はすべて1つ存在する
• 補足
  • 第1正規形の必要条件は、リレーションの必要条件
  • 列の値のNULLは認めない(見解が分かれるところ)
  • 列の値が1つとは、「意味を持ったパーツ」なので、カンマ区切りとかで色々詰め込むのはよろしくない
• (例1)社員テーブルの場合
  • 第1正規形の条件はすべてクリア。
  • 行と列があり、特に順序は定義されていない(定義されていないかは見ただけでは分からないが…)
  • 同じ行は無いし、全部値が入っている
• (例2)プロジェクトテーブルの場合
  • 同様に第1正規形の条件はすべてクリア。

第2正規形(2NF)

• 第1正規形から、候補キーの一部→非キーの関数従属性を除いたもの
  • 候補キーの一部であって全部ではない
  • 候補キーが1つしかない1NFは自動的に2NFになる
• (例1)社員テーブルの場合
  • 候補キーの一部である「所属」から「会社最寄り駅」に関数従属性があるため、第2正規形の条件を満たさない。
  • 以下のように、所属・最寄り駅テーブルを切り出せば第2正規形。

<所属>	会社最寄り駅
本社	新宿
大阪支社	梅田

• (例2)プロジェクトテーブルの場合
  • 非キーが存在しないため、自動的に第2正規系の条件をクリア。

第3正規形(3NF)

• 第2正規形から、非キー→非キーの関数従属性を除いたもの
• (例1)社員テーブルの場合
  • 非キー「趣味」から別の非キー「趣味の道具」に関数従属性があるため、第3正規形の条件を満たさない。
  • 以下のように、趣味・趣味の道具テーブルを切り出せば第3正規形。

<趣味>	趣味の道具
釣り	釣り竿
テニス	ラケット
麻雀	雀卓

• (例2)プロジェクトテーブルの場合
  • 非キーが存在しないため、自動的に第3正規系の条件をクリア。

ボイスコッド正規形(BCNF)

• 第3正規形から、非キー→候補キーの一部の関数従属性を除いたもの
  • これも候補キーの一部であって全部ではない
  • 3NFだけどBCNFでない時、候補キーは複数パターンがありえる状態
• (例1)社員テーブルの場合
  • 非キー「オフィス」から候補キー「所属」に関数従属性があるため、ボイスコッド正規形の条件を満たさない。
  • オフィスの方が所属より詳細な情報なので、こちらを候補キーにしても良かった！
  • 所属＆名前、オフィス＆名前のどちらも候補キーになりえる。
  • 以下のように、社員テーブルの候補キーを組み替えつつ、オフィス・所属テーブルを切り出せばボイスコッド正規形。
  • この状態の社員テーブルは実は第5正規形の条件も満たしています。(後述)

<オフィス>	<名前>	部	趣味
本社ビル11階	鈴木	営業部	釣り
本社ビル12階	田中	営業部	テニス
本社ビル13階	渡辺	開発部	麻雀
本社ビル12階	渡邉	開発部	麻雀
大阪支社ビル5階	佐藤	開発部	釣り
大阪支社ビル6階	山田	クリエイティ部	釣り
大阪支社ビル5階	田中	ポジティ部	テニス

<オフィス>	所属
本社ビル11階	本社
本社ビル12階	本社
本社ビル13階	本社
大阪支社ビル5階	大阪支社
大阪支社ビル6階	大阪支社

• (例2)プロジェクトテーブルの場合
  • 非キーが存在しないため、自動的にボイスコッド正規形の条件をクリア。

第4正規形(4NF)

• BCNFから、多値従属性を除いたもの
  • 非キーがない、かつ候補キーが複数のBCNFの場合のみ考慮が必要
• あまり現れない
• (例1)社員テーブルの場合
  • 非キーがあるため第4正規系の条件をクリア。
• (例2)プロジェクトテーブルの場合
  • よく見ると、「プロジェクト」から「社員名」と「スキル」、「プロジェクト」から「成果物」に多値従属性がある。
  • プロジェクト・社員名・スキルと、プロジェクト・成果物の2テーブルに無損失分解できる。
  • これらは全て候補キーなので、関数従属性ではないことに注意。
  • 以下のように2テーブルに分割すると第5正規形。

<社員名>	<プロジェクト>	<スキル>
鈴木	Aプロジェクト	AWS
鈴木	Aプロジェクト	Web
鈴木	Bプロジェクト	Web
田中	Aプロジェクト	AWS

<プロジェクト>	<成果物>
Aプロジェクト	要件定義書
Aプロジェクト	基本設計書
Bプロジェクト	テスト仕様書
Bプロジェクト	テストエビデンス

第5正規形(5NF)

• 第4正規形から、候補キー内の結合従属性を除いたもの
  • 非キーがない、かつ候補キーが複数のBCNFの場合のみ考慮が必要
  • 上記でない場合は、そもそもBCNFにした時点で5NFになっている
• ここまでやれば十分
• (例1)社員テーブルの場合
  • 非キーがあるため第5正規系の条件をクリア。
• (例2)プロジェクトテーブルの場合
  • 第4正規形の前者のテーブルをよく見ると、「プロジェクト」と「社員名」と「スキル」には互いに従属性がある。
  • プロジェクト・社員名と、プロジェクト・スキルと、社員名・スキルの3テーブルに無損失分解できる。
  • これらは全て候補キーなので、関数従属性ではないことに注意。
  • 以下のように3テーブルに分割すると第5正規形。
  • この状態のこれらのテーブルは第6正規形も満たしています。(後述)

<社員名>	<プロジェクト>
鈴木	Aプロジェクト
鈴木	Bプロジェクト
田中	Aプロジェクト

<プロジェクト>	<スキル>
Aプロジェクト	AWS
Aプロジェクト	Web
Bプロジェクト	Web

<社員名>	<スキル>
鈴木	AWS
鈴木	Web
田中	Web

第6正規形(6NF)

• 第5正規形から無慈悲にあらゆる結合従属性を除いたもの
• 分解した結果、全てのテーブルは非キーが0または1個になる
  • 逆に6NFにできる5NFは、非キーが複数あるもの、つまりBCNFにした段階で5NFを満たしたものに限られる
  • BCNFにした段階で5NFを満たさない場合、非キーが0個なので5NFに正規化すると同時に6NFを満たす(はず…)
• ここまでやると逆に使いにくい
• (例1)社員テーブルの場合
  • ボイスコッド正規形にした(結果的に5NFを満たした)社員テーブルにはまだ非キーが2個残っています。(「部」と「趣味」)
  • 結合従属性を取り除かなきゃ！(使命感)
  • 以下のように非キーが1個になるように分解すれば第6正規形の条件をクリア。

<オフィス>	<名前>	部
本社ビル11階	鈴木	営業部
本社ビル12階	田中	営業部
本社ビル13階	渡辺	開発部
本社ビル12階	渡邉	開発部
大阪支社ビル5階	佐藤	開発部
大阪支社ビル6階	山田	クリエイティ部
大阪支社ビル5階	田中	ポジティ部

<オフィス>	<名前>	趣味
本社ビル11階	鈴木	釣り
本社ビル12階	田中	テニス
本社ビル13階	渡辺	麻雀
本社ビル12階	渡邉	麻雀
大阪支社ビル5階	佐藤	釣り
大阪支社ビル6階	山田	釣り
大阪支社ビル5階	田中	テニス

• (例2)プロジェクトテーブルの場合
  • 非キーが0個なので第6正規系の条件をクリア。

具体例の正規化の変遷まとめ

• (例1)社員テーブルと、(例2)プロジェクトテーブルは、正規化を経るごとに以下のように変遷します。(列名のみ記載)
• 太字が候補キーです。

(例1)社員テーブル

• 1NF
  • 所属、社員名、会社最寄り駅、部、オフィス、趣味、趣味の道具
• 2NF
  • 所属、社員名、部、オフィス、趣味、趣味の道具
  • 所属、会社最寄り駅
• 3NF
  • 所属、社員名、部、オフィス、趣味
  • 所属、会社最寄り駅
  • 趣味、趣味の道具
• BCNF～5NF
  • オフィス、社員名、部、趣味
  • オフィス、所属
  • 所属、会社最寄り駅
  • 趣味、趣味の道具
• 6NF
  • オフィス、社員名、部
  • オフィス、社員名、趣味
  • オフィス、所属
  • 所属、会社最寄り駅
  • 趣味、趣味の道具

(例2)プロジェクトテーブル

• 1NF～BCNF
  • 社員名、プロジェクト、スキル、成果物
• 4NF
  • 社員名、プロジェクト、スキル
  • プロジェクト、成果物
• 5NF～BCNF
  • 社員名、プロジェクト
  • 社員名、スキル
  • プロジェクト、スキル
  • プロジェクト、成果物

雑なまとめ

• 素直にテーブル定義考えるとだいたい非キーがあるから、関数従属性を除いていけば自然と5NFまで正規化できそう。
• 非キーが無い場合は、結合従属性が無いか検討するべきだが、だいたい不自然な状態になっていると思うので一回テーブル設計を考え直した方が早いかもしれない。
• テーブルの例を考えるのが一番大変だった…。

参考

• 理論から学ぶデータベース実践入門 ~リレーショナルモデルによる効率的なSQL
• 「特殊な正規形」を理解する
  • 4NFと5NFの正規形については、こちらが分かりやすい