DBの正規化について

正規形と正規化

正規形(normal form):(冗長性と非一貫性の排除された)効率性と一貫性のあるデータ形式

正規化(normalization):DBを正規形にすること

関数従属

Xを入力するとYが出力される場合、YはXに従属すると表現される。

{X}→{Y}

会社IDから会社名が一意に特定できる場合、会社名は会社IDに従属する

{会社ID}→{会社名}

第一正規形:スカラ値の原則

■第一正規形の定義

一つのセルに一つの値しか含まれていない状態が実現されている状態が実現されている。

■NG

会社ID会社名担当者名
Account001ねこ株式会社田中
鈴木
渡邊
Account002いぬ株式会社佐藤
髙橋
伊藤

■OK1

会社ID会社名
Account001ねこ株式会社
Account002いぬ株式会社
担当者ID会社ID(外部キー)担当者名
Contact001Account001田中
Contact002Account001鈴木
Contact003Account001渡邊
Contact004Account002佐藤
Contact005Account002髙橋
Contact006Account002伊藤

■OK2

会社ID会社名担当者名1担当者名2担当者名3
Account001ねこ株式会社田中鈴木渡邊
Account002いぬ株式会社佐藤髙橋伊藤

■OK3

会社ID会社名担当者名
Account001ねこ株式会社田中
Account001ねこ株式会社鈴木
Account001ねこ株式会社渡邊
Account002いぬ株式会社佐藤
Account002いぬ株式会社髙橋
Account002いぬ株式会社伊藤

■第一正規形の必要性

一つのセルに複数の値が入っている場合、レコードの主キーと列情報から一意の値を取り出すことができない。

第二正規形:部分関数従属の排除(=完全関数従属の実現)

■第二正規形の定義

第一正規形である + 特定の非キー属性が主キーの一部に従属している状態(=部分関数従属)が排除され、全ての非キー属性が主キーに従属している状態(=完全関数従属)が実現されている。

■NG

会社ID会社名担当者ID担当者名
Account001ねこ株式会社Contact001田中
Account001ねこ株式会社Contact002鈴木
Account001ねこ株式会社Contact003渡邊
Account002いぬ株式会社Contact004佐藤
Account002いぬ株式会社Contact005髙橋
Account002いぬ株式会社Contact006伊藤
ここで会社名は主キー(会社ID+担当者ID)ではなく、会社IDに従属している。

■OK

会社ID会社名
Account001ねこ株式会社
Account002いぬ株式会社
担当者ID会社ID(外部キー)担当者名
Contact001Account001田中
Contact002Account001鈴木
Contact003Account001渡邊
Contact004Account002佐藤
Contact005Account002髙橋
Contact006Account002伊藤

■第二正規形の必要性

・主キーの構成要素が欠如している場合、情報の登録ができない。(例:担当者のいない会社の情報を登録できない。もし登録しようとすると、主キーを構成する担当者ID項目がNULLになってしまう。)

・部分関係従属の出力要素が冗長である。(例:会社IDから一意に特定可能なはずの会社名の情報を毎回毎回ビューではないテーブルに値として保管する必要がない)

・部分関係従属の出力要素が任意の値になる可能性がある。(例:会社名に関して"いぬ株式会社"・"いぬ 株式会社"・"dog株式会社"のように表記ゆれが発生する可能性がある。

第三正規形:推移的関数従属の排除

■第三正規形の定義

第二正規形である + の非キー属性がその他の非キー属性に従属している状態(=推移的関数従属)が排除されている。

■NG

担当者ID会社ID(外部キー)担当者名部署ID部署名
Contact001Account001田中Department001営業
Contact002Account001鈴木Department002開発
Contact003Account001渡邊Department003人事
Contact004Account002佐藤Department001営業
Contact005Account002髙橋Department002開発
Contact006Account002伊藤Department003人事

■OK

担当者ID会社ID(外部キー)担当者名部署ID(外部キー)
Contact001Account001田中Department001
Contact002Account001鈴木Department002
Contact003Account001渡邊Department003
Contact004Account002佐藤Department001
Contact005Account002髙橋Department002
Contact006Account002伊藤Department003
部署ID部署名
Department001営業
Department002開発
Department003人事

■第三正規形の必要性

・推移的関数従属の可能な出力の選択肢が、主キーのあるテーブルに依存する。(例:担当者のいない部署の情報を登録できない。)

・推移的関数従属の出力要素が冗長である。(例:部署IDから一意に特定可能なはずの部署名の情報を毎回毎回ビューではないテーブルに値として保管する必要がない)

・推移的関数従属の出力要素が任意の値になる可能性がある。(例:部署名に関して"営業"・"営業部"・"Sales"のように表記ゆれが発生する可能性がある。

ボイスコッド正規形

■ボイスコッド正規形の定義

第三正規形である + 非キーからキーへの関数従属が排除され、全ての関係従属の決定項が主キーである状態が実現されている。

■NG

会社ID部署ID部長
Account001Department001田中太郎
Account001Department002鈴木一郎
Account001Department003渡邊二郎
Account002Department001佐藤三郎
Account002Department002髙橋四郎
Account002Department003伊藤五郎
部長の値は主キーの構成要素にも非キーにも従属していない。しかし、部長名から一意に部署を特定することは可能で、部署は部長に従属している。

■OK

会社ID部署ID
Account001Department001
Account001Department002
Account001Department003
Account002Department001
Account002Department002
Account002Department003
部長部署ID
田中太郎Department001
鈴木一郎Department002
渡邊二郎Department003
佐藤三郎Department001
髙橋四郎Department002
伊藤五郎Department003

■ボイスコッド正規形の必要性

・主キーの構成要素が欠如している場合、情報の登録ができない。(例:部署情報が欠如している場合、部長の情報をDBに登録できない)

・部長が部署を変更する場合、複数行の情報更新が必要となる。

・会社が部署を再編した場合などに、部長の情報が丸ごと消える可能性がある。

第四正規形

■第四正規形の定義

ボイスコッド正規形である + 同一テーブル内での複数の多値従属性が実現されている。

■NG

営業チーム社員商談
Team1田中太郎Department001
Team1鈴木一郎Department001
Team1渡邊二郎Department002
Team2佐藤三郎Department003
Team2髙橋四郎Department003
Team2伊藤五郎Department004
営業チーム:社員=1:多 & 営業チーム:商談=1:多

■OK

営業チーム社員
Team1田中太郎
Team1鈴木一郎
Team1渡邊二郎
Team2佐藤三郎
Team2髙橋四郎
Team2伊藤五郎
営業チーム商談
Team1Department001
Team1Department001
Team1Department002
Team2Department003
Team2Department003
Team2Department004

■第四正規形の必要性

・全列の情報が揃うまで情報登録ができない

・情報変更があった際に複数行を更新する必要がある(例:商談の担当チームの変更があった場合、変更のあった商談に関する全ての列の情報を更新する必要がある)

第五正規形

※上記の第四正規形の例では、①どの営業チームにどの社員が所属しているか②どの営業チームがどの商談を担当しているか、の情報を取得できても③どの社員がどの商談を担当しているのか、の情報は取得できない。

■第五正規形の定義

第四正規形である + 結合従属性の決定項が主キーのみである。

■OK

営業チーム社員
Team1田中太郎
Team1鈴木一郎
Team1渡邊二郎
Team2佐藤三郎
Team2髙橋四郎
Team2伊藤五郎
営業チーム商談
Team1Department001
Team1Department001
Team1Department002
Team2Department003
Team2Department003
Team2Department004
社員商談
田中太郎Department001
鈴木一郎Department001
渡邊二郎Department002
佐藤三郎Department003
髙橋四郎Department003
伊藤五郎Department004

Other

Posted by regardie