データベースの正規化について

はじめに

wikipediaで関係の正規化を読んでいて、中々理解が出来なかったので、第1～第5正規形とボイス・コッド正規形についてまとめます。

正規化とは？

データの重複を排除し、整合性と効率性を持たせること。

メリット

・データの変更時に生じるデータ不整合を防げる。
・データを変更するときに最小限の労力でできる。
・テーブルの持つ意味が明確になる。
・メンテナンスの効率化を図ることができる。

デメリット

・アクセス速度が低下する場合がある。
・テーブルが増える。
・SQL文が複雑になる可能性がある。

非正規形

正規化が行われておらず、1行の中に複数の繰り返し項目が存在すること。

例1：繰り返し現れる列があるパターン

例2：1つのフィールドに複数の値があるパターン

第1正規形

関係がスカラ値のみを持つもの。

第1正規形にするには以下の処理をする。
・一つのフィールドに一つの値のみにする。
・繰り返し現れる列を別の表に分割する(同じ意味を持つ列を複数作らない)。
・導出項目を削除する(他の項目から算出できるため、冗長になる)。

※スカラ値：それ以上分割できない単一の値のこと。
※導出項目：他の列から算出できる項目のこと。

非正規形の例1または、例2を正規化すると以下のようになる。


・例1は繰り返し現れる列を、例2は一つのフィールドに複数の値あるものを注文票と注文明細に分割。
・列の「金額」は「単価*数量」で算出できるため削除。

第2正規形

第1正規形の表から部分関数従属性を排除し、主キー列の値が決まれば主キー以外の列の値が決まるような表に分割したもの。

第2正規形にするには以下の処理をする。
・第1正規形の表にする。
・複合主キーがある表は片方のみに関数従属する列を別の表に分割する。
・主キー列の値が決まれば非キー(主キー以外)の列の値が決まるようにする。

※関数従属性：「ある列の値Xが決まると、別の列の値Yが自動的に決まる」という関係。
※部分関数従属性：AとBからなる複合主キーの場合、「AまたはBが決まるとYが決まる」という関係。

第1正規化したものを第2正規化すると以下のようになる。



注文票は以下のため、分割しない。
・複合主キーがないため部分関数従属性があるものはないと判断できる。
・主キーである注文ID列の値が決まれば非キー(主キー以外)列の値が全て決まる。

注文明細表は以下のため、注文明細表と商品表に分割する。
・商品ID(複合主キー)のみに商品名と単価が関数従属する。

第3正規形

第2正規形の表から推移関数従属性を排除したもの。

第3正規形にするには以下の処理をする。
・第2正規形の表にする。
・非キーに関係従属する非キーの列を別の表に分割する。

※推移関数従属性：以下条件を満たすときの関係(Xは主キー、Y、Zは非キーとする)。
・「Xが決まるとYが決まり、Yが決まるとZが決まる」、「Xが決まると、Zが決まる」が成立する。
・「Yが決まると、Xが決まる」、「Zが決まると、Xが決まる」は成立しない。

第2正規化したものを第3正規化すると以下のようになる。

注文票は以下のため、注文票と顧客表に分割する。
・注文ID(主キー)に顧客ID(非キー)が関数従属する。
・顧客ID(非キー)に、顧客名(非キー)、顧客住所(非キー)、電話番号(非キー)が関数従属する。

注文明細表と商品表は以下のため、分割しない。
・非キー間での関数従属性が含まれていない。

ボイスコッド正規形

第3正規形の表から非キーへ関数従属する主キーの関係を排除したもの。

ボイスコッド正規形にするには以下の処理をする。
・第3正規形の表にする。
・非キーが決定項で複合主キーの片方が従属項のものは別の表に分割する。

※決定項：「Xが決まると、Yが決まる」のXのこと(Yは従属項)。

ボイスコッド正規化の例
以下を要件とする
・受講者とコース名が決まると講師が決まる。
・一人の講師は一つのコースを担当する。
・一人の受講者は複数のコースを受講することができる。

正規化前

正規化後


・非キー列の講師に主キー列のコース名が関数従属するため、受講者と担当講師に分割する

第4正規形

表の列が全て主キーで、自明でない多値従属性を排除したもの。
※第4正規形の条件を満たしたときは、基本的に第5正規形を満たす

第4正規形にするには以下の処理をする。
・全ての列が主キーの表にする。
・「1対n」の関係を持つ列が1組になる表に分割する。

※多値従属性：Aが決まるとBの集合が決まる関係のこと(「1:n」の関係)。
※自明でない多値従属性：一つの表の中に複数の多値従属性が存在すること。
※自明な多値従属性：一つの表の中に一つの多値従属性が存在すること。

第4正規化の例
正規化前

正規化後


以下のため、表を分割する。
・表の列が全て主キーで、非キーが存在しない。
・「業種と商品名、業種と社名」と一つの表に複数の多値従属性がある。

第5正規形

第4正規形で、自明でない結合従属性を排除したもの。

第5正規形にするには以下の処理をする。
・第4正規形の表にする。
・分割した表を元の表に戻せるようにする。

※結合従属性：分解した表を、元の表に戻すことができる性質のこと。
※自明でない結合従属性：分割した表を、元の表に戻すことができないこと。

第5正規化の例
正規化前

正規化後



以下のため、表を分割する。
・表の列が全て主キーで、非キーが存在しない。
・「店名と料理名、店名と食材、料理名と食材」と一つの表に複数の多値従属性がある。

最後に

最後までご覧いただき誠にありがとうございます。
今回、第1～第5正規形とボイス・コッド正規形についてまとめてみましたが、正直なところ第4～第5正規形はまだ理解があやしいです。
ですので、間違っているところがありましたら、ご指摘いただければ幸いです。

参考記事

関係の正規化
RDBの正規化理論
「特殊な正規形」を理解する