SQL 正規化について

## 正規化をなぜ行うのか

https://www.katalog.tokyo/?p=557

### 正規化とは？

__RDB__ で使う __テーブル設計ルール__ です。
__業務系__ のシステムでは __RDB__ を __必ず__ 使います。
システムを作る上で __必須ともいえる知識__ です。

テーブルを作成する時には、正規化の規則にそって作りましょう。
またSQLを使って __データを取り出す時__ に、正規化の知識があると何のデータがどこのテーブルに保存されているか __感覚的にわかる__ ようになります。

### メリット

+ データを登録/更新/削除した時のトラブルが少なくなります。

正規化の副産物として、 __誰が設計してもほぼ同じになる__ というメリットがあります。
正規化を知っていれば、初めて見るデータベースでも構成が予想できます。
例えば、「注文書」と聞いた時に必要なテーブルを考える際、「注文書ヘッダ、注文書明細、業者マスタ、商品マスタ」と推測できます。

### デメリット
+ テーブルの数が多くなります。
+ 一般の人には、どこに何のデータがあるかわかりにくくなります。
+ データ参照時のパフォーマンスが悪くなります。

Excelでデータをもらう時に、１つの表にまとまっていた方が使いやすいですが、正規化すると１つの表が複数個に分割されます。
データを参照する場合は正規化しない方がわかりやすいです。

### 正規化の手順

正規化は、__「同じデータを２重に持たない」__ という考えを元にしてます。
第一、第二、第三の順で作業をしていくと正規化できます。

第一正規化の手順
列の項目の繰り返しを探し、行方向に変換する
行の項目の繰り返しを探し、表に分割する

第二正規化の手順
主キーを探す
主キーの項目に注目し、関係関数従属する項目を探す

第三正規化の手順
主キー項目以外で、関係関数従属する項目を探す


## 正規化を理解するための前提知識

http://sql.main.jp/norm02.html

### 関数従属について

y=xでxが例えば1だとyは1です。y=2xならxが1でyは2。
xが決まればyが決まる。このような状態を「yはxに __関数従属__ している」といいます。

### キーについて

リレーショナルデータベースは、 __キーとそれ以外の項目__ からなっています。 __キー__ というのは __それがわかれば、他の値が決定する__ という、先ほど説明した __関数従属させる側__ の項目のことです。

キーにはいくつかの種類があって、テーブルの中から、 __レコードを特定できるキー項目__ のことを __候補キー__ といいます。その中でも中心的なキー項目を主キーといいます。キー項目は、一つの項目からなることもありますし、複数の項目が合せてキーということもあります。

同じ項目であってもテーブルによって主キーは変わります。

### 関数従属とキーの関係

正規化されたテーブルは __キー項目とそれ以外の項目のみ__ です。
逆に言うと、 __正規化__ とは、 __そのテーブルの主キーと、それに関数従属する項目のみにしてやるプロセス__ といえます。

## 非正規形から第一正規化の手順

#### 参考サイトは例を挙げて説明しているのでわかりやすい。

http://sql.main.jp/norm03.html

https://www.katalog.tokyo/?p=667

__第一正規形__ は __表に繰り返しの項目をもたない形__ です。

同じ項目が繰り返しているものを、繰り返しグループといいます。
この繰り返しグループを __「行」と「列」から__ 共になくしてやると第一正規形の完成です。

繰り返しグループの排除は、繰り返している項目を別表に分割して別表にすると完了です。
その際にリレーションが失われないように注意をしなくてはなりません。
そのため、従属する項目（ここでは主キー）を付けて別表にします。

### １. 注文書の項目一覧を表に書き出す

項目名とデータをすべて表に書き出します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1322050/4057825b-2df1-b1c5-8fdf-977e31468d5c.png)


### 2. 列の項目の繰り返しを探し、変換する

上の表を __一行で表す場合__ 、以下の引用図のように商品名、数量、単価を __繰り返して__ 表示させる必要があります。
（図は間違えているので注意。商品名２＝鉛筆、数量２＝２　が正しい）

この状態だと、 __販売できる数が設置した列の数の分を上限として制限され__ てしまいます。
第一正規化はこの問題を解決します。

まず、__列__ に注目して繰り返しがなくなるようにします。
繰り返しているのは黄色の項目です。
1.で示した引用図のように、商品データを縦に持つように変換します。
（3.の引用図を参照のこと）

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1322050/bffca219-1df1-01f4-932a-0be5521edede.png)


### 3. 行の項目の繰り返しを探し、表に分割する

商品データを縦に持つように変換し、「注文番号、事業名、住所蘭」（赤字部分）を埋めると下の引用図のようになります。
ここでは、「行」の繰り返しをなくす作業にとりかかります。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1322050/37cf6163-e408-2131-b671-83799e4a2483.png)

行のデータを見ると黄色の項目の「注文番号、業者名、住所」が繰り返しているのがわかります。
__黄色__ 項目と ___緑___ の項目をわけて、__「注文書ヘッダ」__ と ___「注文書明細」___ の表を作ります。

__表を2つにわけると__ 2つの表のデータの __関係性がわからなくなります__ 。
「注文書明細」表に「注文書ヘッダ」表の __主キー「注文番号」__ を追加します。
こうすることで、ノートをどこの業者から買ったか知りたい時に「注文番号」から調べられます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1322050/392971d1-1c20-a691-50a6-b44b6df8ed19.png)


これで第一正規化が終了です。

## 第二正規化の手順

http://sql.main.jp/norm04.html

https://www.katalog.tokyo/?p=667

一度に販売する数の制限はなくなりましたが、このままでは無駄な領域が発生します。例えば、XYZ会社が3日続けて購入した場合、顧客名、住所が同じデータなのに、一つのテーブルに重複してあることになります。ヒット商品が出て、大量に同じ商品が購入された場合も同じです。このような状態はデータ容量を無駄に使うのでなくしたいですね。さらに、更新処理を行う場合、複数箇所のデータを更新しなくてはならず、無駄が多いです。

第二正規化はこのような無駄を解決します。

方法は、 __主キーを元に表を分割__ します。
主キーというのは __重複のないデータの項目__ の事をいいます。

例えば都道府県の名前は主キーです。
同じ名前の県はないからです。

人の名前は主キーにはなりません。
同姓同名の人がいる可能性があるからです。

このような主キーの項目を探して、別の表に分割する作業が第二正規化と第三正規化です。

第二正規化：主キーの項目内に分割できる項目がない状態
第三正規化：主キー以外の項目にも分割できる項目がない状態

### 1. 主キーを探す
__重複しない値__ の __主キー__ を探します。

「注文書ヘッダ」表で、業者名は主キーなるでしょうか？
同じ業者に何回も発注したら、業者名は複数でてきます。
一行に特定できないので業者名は主キーとは違います。

このように考えると主キーは
+ 注文書ヘッダ表：「注文番号」
+ 注文書明細表　：「注文番号」「商品名」

となります。第一正規化完了時点の引用図の青色の項目です。

ちなみに、「注文書明細表」は「注文番号」「商品名」の2つ　__セットで主キー__ となります。
このことを __複合キー__ といいます。

### 2. 複合キーに注目し、主キーの中から関係関数従属の候補を探す

__関係関数従属__ とは __Aが決まるとBの値が決まること__ をいいます。

チェックするのは __複合キーのテーブルだけ__ で大丈夫です。

その理由は __主キーが1つの項目のテーブルは、すでに分割済みのため__ です。
例えば、「注文書ヘッダ」表は注文番号が決まると業者名が特定できます。

「注文書明細」表の主キー「注文番号」と「商品名」に着目します。
この２つの項目の __全部の組合せ__ を書き出します。


|  項目の組合せ    |  検討対象 |  説明 |  
| ----           | ----    | ----  | 
|  注文番号、商品名 |  対象外  |  すでに「注文書明細表」表としては分割済みのため対象外 |  
|  注文番号       |  対象外  | 「注文書ヘッダ」表としてすでに分割済みのため対象外  |  
|  商品名         |  検討対象 |  商品名が決まると確定する項目がないか確認が必要  |  
		
		
### 3. 関係関数従属する項目を主キー以外から探す

整理したことで、商品名を確認すればいいことがわかります。

次に __候補キー__ の「商品名」 __と他の項目の一覧__ を書き出します。
商品名のノートを考えたときに、
・数量が１つに決まるか？
・単価が1つに決まるか？
と __一つずつ考えていきます__ 。
整理した結果「単価」が「商品名」に関係関数従属することがわかります。

|候補キー|	関係する項目	| 結果	|  確認結果 |
| ---- | ----  | ----  |  ---- |
| 商品名 |	数量	| 関係なし |	注文数量は1個だったりも5個だったりも考えられる。<br> 商品名が決まったからといって数量が同じ値になるとは言えない。 |
| 商品名	| 単価	| 関係関数従属 |	商品名が決まると単価が決まる。 |

商品名と単価を別の表に分けると下記の引用図のようになります。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1322050/e4d1a833-9923-6b03-ac15-1c2ad9e223c0.png)


これで第二正規化が終了です。

### 第三正規化の手順
#### 主キー項目以外で、関係関数従属する項目を探す

__主キー以外の項目の組合せ__ を書き出し、 __一つずつ検討__ していきます。

三段論法で言うAはBである、BはCである、ゆえにAはCであるといったとき、__CはAに推移関数従属している__ と言います。
この関係の組み合わせの有無を第三正規化の段階で判断します。

主キー以外の項目が2つ以上あるのは「注文書ヘッダ」表のみなので、
「注文書ヘッダ」表に注目します。

| 候補キー |	項目 |	結果 |	確認結果 |
| ---- | ---- | ---- | ---- |
| 業者名	| 住所 |	関係関数従属 | 業者名が決まると住所が１つに決まる |
| 住所 | 業者名 | 関係なし | |東京都にたくさんの業者がいるので違う |

「業者名」と「住所」の関係性が確認できたので、下の引用図のように別表に切り出します。

__＊注意点__
「業者名」から見た「住所」だけでなく、 __逆から見て__ 「住所」から「業者名」も __確認__ してください。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1322050/9e8c912b-c592-5128-3fe8-3a2dcce643de.png)



これで第三正規化が終了です。

#### 手順まとめ
#### 第一正規化では「行」と「列」の繰り返しをなくします。
#### 第二正規化と第三正規化では、Aが決まるとBが1つに決まる項目を別の表にします。
#### 要注意：中には推移的関数従属が無い場合もあります。そのときは第二正規形にした時点で、第三正規形まで完了します。


実際の現場でのテーブル設計方法は、もっと手を抜いて設計するとのこと。
それについては以下のページを参考のこと。

https://www.katalog.tokyo/?p=5347

## 参考サイト

http://sql.main.jp/

https://www.katalog.tokyo/?p=557