【テーブル設計】第1正規形〜第5正規形のまとめ

だいぶ昔に「達人に学ぶDB設計徹底指南書」を読んだのですが、今の理解をまとめておこうと思いました。

■ 第1正規形 (スカラ値の原則)

一つのセルには単一の値を格納する。

問題となるテーブル

問題点

• 子のカラムに複数値(非スカラ値)が入っている。
  • 主キーが子カラムの値を一意に決定できない。

正規化

まずは、子の数だけ行を増やして、セルの値がスカラ値(単一値)となるように正規化します。

しかしながら、この形だと主キーを決められません。
社員IDには重複があるので主キーには設定できません。(子 は 社員ID に対する関数従属性を満たしていない )

※ 主キー : レコードを一意に識別できる列または、列の組み合わせ。主キーは値が重複してはならない。
※ 関数従属性 : 「入力 X に対して出力 Y が一意に決まる関係」(YはXに従属する)のこと 。この関係を満たすことを 関数従属性を満たす という。

そこで、テーブルを分割することで、主キーで一意のレコードを決定できるようにします。
今回の例だと、 社員テーブル と 扶養者テーブル に分割することで、{社員ID} -> {社員名} 、 {社員ID,子} -> {社員ID,子}という関数従属性を満たすようになります。(主キーは主キーに従属する)

正規化したテーブルを非正規形に戻すクエリ

正規化したテーブルは非正規形に戻すこともできます。

/* 内部結合によって子供が存在する社員とその子供を抽出する */
SELECT 社員.社員ID, 社員.社員名, 扶養者.子 FROM 社員 INNER JOIN 扶養者 ON 社員.社員ID = 扶養者.社員ID;

/* 外部結合によって、全社員とその子供を抽出する */
SELECT 社員.社員ID, 社員.社員名, 扶養者.子 FROM 社員 LEFT OUTER JOIN 扶養者 ON 社員.社員ID = 扶養者.社員ID;

■第2正規形 (部分関数従属)

テーブル内の部分関数従属を解消し、完全関数従属のみのテーブルを作る。

※ 部分関数従属 : 主キーの一部の列に対して非キーが従属している状態
※ 完全関数従属 : 主キー対して非キーが従属し、部分関数従属がない状態

問題となるテーブル

問題点

• {会社コード} -> {会社名} という部分関数従属性が存在している。
  • 社員 がいないと、 会社名 を登録することができないという運用上の問題が発生。

正規化

部分関数従属している列(会社名)を別のテーブルに切り出します。
第2正規化とは 社員 と 会社 という異なるレベルのエンティティ(実体)をきちんとテーブルとして分離する作業です。
会社名 を 会社 テーブルに独立させることで {会社コード} -> {会社名} となり、部分関数従属性が解消され、完全関数従属となります。

正規化したテーブルを非正規形に戻すクエリ

正規化したテーブルは非正規形に戻すこともできます。

SELECT
    社員.会社コード, 会社.会社名, 社員.社員ID, 社員.社員名, 社員.年齢, 社員.部署コード, 社員.部署名
FROM 社員 INNER JOIN 会社 
    ON 社員.会社コード = 会社.会社コード;

■第3正規形 (推移的関数従属)

非キー列が非キー列に従属しないようにテーブルを分割することで、推移的関数従属を取り除く。

※ 推移的関数従属 : テーブル内に存在する段階的な従属関係のこと。非キー列に対して、非キー列が従属していることが問題。

問題となるテーブル

問題点

• 部署名 は 部署コード に関数従属しており、 部署コード は会社コード,社員ID (主キー)に関数従属している。つまり {会社コード,社員ID} -> {部署コード} -> {部署名} という推移的関数従属が存在している。
  • 社員 がいないと、 部署 を登録することができないという運用上の問題が発生する。

正規化

部署コード という非キーに関数従属している 部署名 を別テーブルに切り出します。
部署名 を 部署テーブル に独立させることで、 {部署コード} -> {部署名} となり、推移的関数従属が解消されます。

正規化したテーブルを非正規形に戻すクエリ

SELECT
    社員.会社コード, 社員.社員ID, 社員.社員名, 社員.年齢, 社員.部署コード, 部署.部署名
FROM 社員 INNER JOIN 部署 
    ON 社員.部署コード = 部署.部署コード;

■ボイス-コッド正規形

非キー列から主キーへの関数従属をなくす

問題となるテーブル

このテーブルの主キーは {社員ID,案件コード} で社員 とアサインされている 案件 の関係を示しています。
社員は複数の案件にアサインできるため、 {社員ID,案件コード} -> {マネージャー} というような主キーから非キーへの関数従属性があります。

一方、このテーブルに「複数のチームのマネージャーを兼任できない」というルールが存在すると仮定すると {マネージャー} -> {案件コード} という非キーからキーへの関数従属が存在することになります。

問題点

• {マネージャー} -> {案件コード} という非キーからキーへの関数従属が存在する。
  • 社員 が登録されないと 案件 と マネージャー の関係を登録できない。
  • マネージャー が担当 案件 を変える場合に複数行の更新が発生する。
  • 社員 が案件から外れたときにレコードを削除すると、 案件 と マネージャー の関連も削除される危険性がある。

正規化

※ この正規化では、 {社員ID,案件コード} -> {マネージャー} という関数従属性を保持するよう注意してテーブルを分割する必要があります。

まず、 要件的には {マネージャー} -> {案件コード} という関係を表したかったので、 マネージャー と 案件コード を別テーブルに切り出します。

次に、 社員ID と マネージャー の関係を表すテーブルを用意すれば、2テーブルの マネージャー カラムを通して 社員ID と 案件コード を紐づける事が可能になるため、 {社員ID,案件コード} -> {マネージャー} の情報を保持することができます。

※ 逆に社員IDと 案件コード というテーブルを作ってしまうと {社員ID,案件コード} -> {マネージャー} という関数従属性が消えてしまい、可逆性を失ってしまいます。

結論としては、 社員ID,マネージャー マネージャー,案件コード　とテーブルを分割することで {社員ID,案件コード} -> {マネージャー} という関数従属性を保持したまま、{マネージャー} -> {案件コード}という非キー列からキー列への関数従属を解消することができます。

正規化したテーブルを非正規形に戻すクエリ

SELECT
    社員-マネージャー.社員ID, 社員-マネージャー.案件コード, マネージャー-案件.マネージャー
FROM 社員-マネージャー INNER JOIN マネージャー-案件
    ON 社員-マネージャー.マネージャー = マネージャー-案件.マネージャー

■第4正規形 (多値従属性)

関連エンティティに含まれる関連は一つだけにする。(多値従属性が複数存在するテーブルを分割する)

※ 多値従属性 : Aが決まるとBの集合が決まる関係のこと。{A} ->-> {B}で表す。

問題となるテーブル

社員 と 案件 の関連を表すテーブル(関連エンティティ) に 担当製品コード 列が追加されたとします。

社員ID に対して 案件コード は複数定まり、 社員ID に対して 担当製品コード も複数定まります。(多値従属性)
案件 と 製品 の間には特に関係がないので、これを式で表すと以下のようになります。

｛社員ID｝ ->-> ｛案件コード¦製品コード｝

問題点

• 一つの関連エンティティに、 {社員ID} ->-> {案件コード} , {社員ID} ->-> {製品コード} という複数の多値従属性が成り立ってしまっている。
  • 社員000Aが案件001から002に移動になった場合、複数行の更新が発生してしまう。
  • 案件は決まったが、担当製品が決まっていない社員を登録できない。

正規化

{社員ID} ->-> {案件コード} を表すテーブルと {社員ID} ->-> {製品コード} を表すテーブルに分離し、関連エンティティに含まれる関連を一つだけにします。

■第5正規形

関連エンティティに含まれる関連は一つだけにする。(多値従属性が複数存在するテーブルを分割する)

問題となるテーブル

第4正規形のテーブルに {案件コード} ->-> {製品コード} という多値従属性が存在する場合を考えます。(案件によって扱う製品も異なるルールが存在する場合)

問題点

• 社員 - 案件``社員 - 製品 の2テーブルでは 案件 - 製品 の従属性がわからない

正規化

「案件-製品」の関係を表す関連エンティティを作ります

■ キーワード

主キー

主キーとは、「レコードを一意に識別できる列または、列の組み合わせ」です。
主キーはテーブルにおいて必ず1つ存在しなければならず、かつ1つしか存在しません。
主キーに設定した列は値が重複してはなりません。(つまり、一部でもNULLであってはならない)

外部キー

外部キーは格納する値に制約をかける役割があります。
下図では、社員テーブル.部署(外部キー)に設定できる値は 部署テーブル.部署 に存在している値に制限されます。
この制約を参照整合性制約と呼びます。

※ 外部キーが設定されている場合、データの削除は子から順に行います。この例だと 社員テーブル(子) のレコードを削除してから 部署テーブル(親)のレコードを削除します。

関数従属性

Y = f(X) のような、入力 X に対して出力 Y が一意に決まる関係を YはXに従属する と言い、この関係を満たすことを 関数従属性を満たす といいます。

正規化はテーブルの全ての列が関数従属性を満たすように整理していく作業になります。

部分関数従属

主キーの一部の列に対して非キーが従属している状態。

完全関数従属

主キー対して非キーが従属し、部分関数従属がない状態。

無損失分解

情報を完全に保持したままテーブルを分割する操作のことです。
無損失分解の場合、正規化されたテーブルは非正規形に戻すことができます。

※ 逆に言うと正規化は無損失分解でなければならない。

推移的関数従属

{会社コード,社員ID} -> {部署コード} -> {部署名} のような、テーブル内での段階的な関数従属関係を推移的関数従属といいます。
{部署コード} -> {部署名} のように非キー列に対して、非キー列が従属していることが問題となります。

関連エンティティ

「社員」や「チーム」といったエンティティどうしの関連を表現するエンティティを関連エンティティといいます。(よく言う中間テーブルというやつ)
関連エンティティに含まれる関係は一つだけでなければなりません。

※ 第4正規形、第5正規形はこの関連エンティティに対して発生します。

多値従属性

Aが決まるとBの集合が決まる関係のこと。{A} ->-> {B}で表す。 (BがAに対して関数従属しているわけではないので注意)

下記のテーブルは 社員ID を決めると チームコード が複数定まり、同様に 社員ID に対して 製品コード も複数定まります。つまり チームコード は 社員ID に多値従属しており、 製品コード も 社員ID に多値従属しています。

多値従属性は ->-> で表し、 {社員ID} ->-> {チームコード} {社員ID} ->-> {製品コード} と表します。