カテゴリー
DB

損失分解

以下のようにテーブルをいい加減に分割してしまえば、損失分解になる

この分解は、元のテーブルを復元不可能な分解です。

両方のテーブルを結合するためのキーが存在しないからです。

しかも、年齢部署テーブルに主キーがないという点で、実際にこのように分解する価値はない。

RDBにおいて、主キーを持たないテーブルを作ることは許されない。

次のように、年齢部署テーブルに「社員ID」を主キーとしてもたせれば無損失分解となる。

これは正規化ではない(垂直分割)が、元のテーブルを復元可能な無損失分解です。

つまり、無損失分解であれば必ず正規化になるわけではない。

損失分解、無損失分解、正規化の関係を図示すると以下のようになる

参考:

達人に学ぶDB設計 徹底指南書 初級者で終わりたくないあなたへ

カテゴリー
DB

正規化は常にするべきか?

・第3正規形までは、原則として行う

・関連エンティティが存在する場合は、関連とエンティティが1対1に対応するよう注意する

一般的に正規化を行えば行うほど、以下のメリットがある

メリット1

データの冗長性が排除され、更新時の不整合を防止できる

メリット2

テーブルの持つ意味が明確になり、開発者が理解しやすい

データベースの第1目的はデータを整合的な状態で保持することにあるため、何よりもデータ不整合を防止することに主眼が置かれる。

正規化はそのために考え出された方法論です。

一方、正規化の主な欠点は以下のようにパフォーマンスに関わるものです。

デメリット

テーブルの数が増えるため、SQL文で結合を多様することになり、パフォーマンスが悪化する

この欠点は、しばしば無視できないため、パフォーマンス向上のため、あえて正規形を低次なものに届ける設計が採用されることはあります。

参考:

達人に学ぶDB設計 徹底指南書 初級者で終わりたくないあなたへ

カテゴリー
DB

正規化の3つのポイント

ポイント1

正規化とは更新時の不都合/不整合を排除するために行う

正規化を行う目的は、何よりも更新(データ登録=INSERTも含む)時の不都合を防ぐためのもの。

また、データの冗長性を排除して、人間のオペレーションミスによるデータ不整合を防ぐ目的もある。

ポイント2

正規化は従属性を見抜くことで可能になる

正規化を行うためには、テーブル内部の従属性の関係を見抜く必要がある。

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

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

が存在していれば、まず正規化の対象になる。

また、

多値従属性が存在していたら、自分が第4正規形に反する設計をしていないか注意をする。

ただし、こうした従属性はテーブルを形式だけ見ていてもわからない。

どの列がとのキーに従属しているか、ということが業務ロジック(ビジネスルール)で決まるので、各列が業務上どのような意味と関係を持っているか、ということを調べなければならない。(業務分析の必要性)

ポイント3

正規形はいつでも非正規形に戻せる

正規化によって分割されたテーブルは、いつでも非正規化テーブルに復元することができる。

これは正規化が情報を完全に保存する無損失分解だから。

参考:

達人に学ぶDB設計 徹底指南書 初級者で終わりたくないあなたへ

カテゴリー
DB

第5正規化を行う

第5正規形

社員-チームテーブル

社員-製品テーブル

チーム-製品テーブル

・1つの関連につき1つのテーブルを作る

・関連と関連エンティティは一対一に対応させる

参考:

達人に学ぶDB設計 徹底指南書 初級者で終わりたくないあなたへ

カテゴリー
DB

第5正規形

第5正規形は、第4正規形をもう少し発展させたもの

「社員-チーム-製品」テーブルは

{社員} →→ {チーム}

{社員} →→ {製品}

という多値従属性があった。

もし、さらに

{チーム} →→ {製品}

という多値従属性があったら?

つまり、チームによっても扱う製品が異なるという業務上のルールが存在する場合です。

チームと製品の従属性

参考:

達人に学ぶDB設計 徹底指南書 初級者で終わりたくないあなたへ

カテゴリー
DB

第4正規化を行う

第4正規形

{社員ID} →→ {チームコード}

{社員ID} →→ {製品コード}

を表すテーブルに分離する

社員-チームテーブル

社員とチーム間の多値従属性を表す

社員-製品テーブル

社員と製品間の多値従属性を表している

この形式ならは、

社員「000A」がチームを「001」から「002」に異動しても、1レコードだけの更新で済む。

社員「000A」が担当する製品を「P1」から「P2」に変更しても、1レコードだけの更新で済む。

この分解は可逆的であるため、いつでも元のデーブルに戻すことができる。

第4正規形の意義

参考:

達人に学ぶDB設計 徹底指南書 初級者で終わりたくないあなたへ

カテゴリー
DB

多値従属性~キーと集合の対応

「社員-チーム-製品」テーブルの問題

・社員「000A」がチーム「001」から「002」へ異動した場合、複数行に対する更新が発生する。

・すべての列が主キーの一部を構成するため、NULLを指定できない。(所属チームは決まったけど、担当製品が決まっていない社員は登録できない等)

・このテーブルの従属性は、関数従属性と異なる特徴がある

・従属性がキーと非キーの間でなくキーと集合の間に成立している

例えば

社員IDを決めると、チームは「複数」決まる

社員IDを決めると、製品は「複数」決まる

このようなキーと集合との対応を多値従属性と呼び「→→」で表す

{社員ID} →→ {チームコード}

{社員ID} →→ {製品コード}

この2つの従属性をまとめて以下のように表す

{社員ID} →→ {チームコード¦製品コード}

参考:

達人に学ぶDB設計 徹底指南書 初級者で終わりたくないあなたへ

カテゴリー
DB

第4正規形

第3正規形(ボイスーコッド正規形)のテーブル

このテーブルは部分関数従属、推移的関数従属、非キー列からキー列への関数従属もない

社員-チーム

{社員、チームコード}という2つの列が主キー

主キーしかないテーブル

このようにエンティティ同士の関連を表現するエンティティを関連エンティティと呼ぶ

製品コード列を追加

1人の社員が複数の製品開発に携わることもあれば、1つの製品を複数の社員が共同開発することもある

社員-チーム-製品テーブル

{社員ID,チームコード,製品コード}の3列で主キーが構成される

主キーだけのテーブル

主キーだけで構成されるのは、関連エンティティ

参考:

達人に学ぶDB設計 徹底指南書 初級者で終わりたくないあなたへ

カテゴリー
DB

ボイス-コッド正規化を行う

ボイス-コッド正規形を満たしていないテーブル

社員-チーム-リーダー

これをボイス-コッド正規化する

ボイス-コッド正規形したテーブル

社員-チーム補佐

チーム補佐-チーム

「チーム補佐」をキーにすることで、結合が「1対多」になった。

しかし、まだ問題がある。

「社員-チーム補佐」テーブルに「社員:000A」と「補佐:003O」のペアを登録することができてしまう。

しかし、「チーム補佐-チーム」テーブルを見ると、補佐「003O」が担当できるチームは「002」だけなので、現在はチーム「001」にしか所属していない社員「000A」がチーム「002」にも所属するようになることを暗黙に意味してしまう。

それが許される場合もあり、たとえば「003O」さんの補佐を受けるということはチーム「002」への参加を意味するというルールが業務的に決められている場合もある。

しかし、そのようなルールが存在しない場合、このような「ありえない」組み合わせのレコードを登録することがないよう、アプリケーション側で制御する必要がある。

参考:

達人に学ぶDB設計 徹底指南書 初級者で終わりたくないあなたへ

カテゴリー
DB

ボイス-コッド正規形

BCNF(Boyce-Codd normal form)

第3正規形のすぐ上位に位置する正規形

非公式にが「第3.5正規形」という呼び方もされる。

ボイス-コッド正規形を満たしていないテーブル

社員-チーム-リーダー

・テーブルの主キーは、社員IDとチームコード

・社員は複数のチームに同時に所属できる

・関数従属性

{社員ID, チームコード} → {チーム補佐}

・チーム補佐は、社員を各チームにおいて補佐する

・同じチームに複数人いることもあるが、1人が複数のチームを兼任できない

・チーム補佐からチームコードに対する関数従属がある(非キーからキーへの関数従属

{チーム補佐} → {チームコード}

・このテーブルは、部分関数従属および推移的関数従属はない。したがって第3正規形

・しかし、非キーからキーへの関数従属がある状態なので、ボイス-コッド正規形ではない

ボイス-コッド正規形とは非キーからキーへの関数従属をなくした状態を指す

ボイス-コッド正規形でないとおこる問題

・チーム補佐が担当チームを変える場合に複数行の更新が発生する(データの冗長性)

・社員がチームに参加するまで、チーム補佐とチームの関連を登録できない

・社員がチームから外れたときにレコードを削除すると、チーム補佐とチームの関連も削除される危険がある

誤ったボイス-コッド正規化「社員-チーム-リーダー」テーブル

社員-チーム

チーム補佐-チーム

この分解には重大な問題がある。

この2つのテーブルからは元のテーブルに戻せない。

枠で囲った部分が存在してはならないレコードとして含まれてしまう。

→上記でテーブルの分解が、可逆的でなかった(=非可逆的)ため

→チームコードをキーにした結合が「多対多」の関連になってしまったため

正規化は必ず可逆的な操作でなければならない

ボイス-コッド正規形への変換時は、気をつけないとこうした非可逆的な分解をおこなってしまう可能性がある

正規化は常に「1対多」の関連を生むようにテーブルを分割する必要がある。

参考:

達人に学ぶDB設計 徹底指南書 初級者で終わりたくないあなたへ