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

## これは何？

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

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

- テーブル設計しないといけなくなった人
- 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](https://amzn.to/2JShYET)
- [「特殊な正規形」を理解する ](https://www.atmarkit.co.jp/ait/articles/1703/01/news183.html)
  - 4NFと5NFの正規形については、こちらが分かりやすい