SQL 正規化について

正規化をなぜ行うのか

正規化とは？

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

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

メリット

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

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

デメリット

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

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

正規化の手順

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

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

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

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

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

関数従属について

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

キーについて

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

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

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

関数従属とキーの関係

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

第二正規化の手順

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

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

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

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

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

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

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

1. 主キーを探す

重複しない値 の 主キー を探します。

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

このように考えると主キーは

• 注文書ヘッダ表：「注文番号」
• 注文書明細表　：「注文番号」「商品名」

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

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

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

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

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

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

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

項目の組合せ	検討対象	説明
注文番号、商品名	対象外	すでに「注文書明細表」表としては分割済みのため対象外
注文番号	対象外	「注文書ヘッダ」表としてすでに分割済みのため対象外
商品名	検討対象	商品名が決まると確定する項目がないか確認が必要

3. 関係関数従属する項目を主キー以外から探す

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

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

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

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

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

第三正規化の手順

主キー項目以外で、関係関数従属する項目を探す

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

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

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

候補キー	項目	結果	確認結果
業者名	住所	関係関数従属	業者名が決まると住所が１つに決まる
住所	業者名	関係なし

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

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

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

手順まとめ

第一正規化では「行」と「列」の繰り返しをなくします。

第二正規化と第三正規化では、Aが決まるとBが1つに決まる項目を別の表にします。

要注意：中には推移的関数従属が無い場合もあります。そのときは第二正規形にした時点で、第三正規形まで完了します。

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

参考サイト