勉強日記

チラ裏

達人に学ぶDB設計 徹底指南書 ch3 論理設計と正規化〜なぜテーブルは分割する必要があるのか？ 3/3

RDB 勉強メモ 閃乱カグラ

www.shoeisha.co.jp

ボイス-コッド正規形

3次と4次の狭間

  • 非公式にだが「3.5次正規形」とも
  • 第3正規形をより厳密にしたものと考えられる
  • 非キーからキーへの関数従属をなくしたもの
    • cf. 第3正規形: 非キーから非キーへの関数従属をなくしたもの
  • 第3正規形だがボイス-コッド正規形でない例
社員ID(PK) チームコード(PK) チーム補佐
000A 001 123W
000B 001 456Z
000B 002 003O
001F 001 123W
001F 002 003O
003O 002 999Y
  • 業務ルール
    1. 社員とチームとは多対多の関連
    2. チーム補佐
      • チームにおいて社員をサポートする人
      • 兼任不可(チーム補佐 "多" -- "1" チーム)
  • 業務ルール1より、主キーは{社員ID, チームコード}
{社員ID, チームコード} -> {チーム補佐}
  • 業務ルール2より、下記の関数従属も存在する
{チーム補佐} -> {チームコード}
  • 非キーからキーへの関数従属。

ボイス-コッド正規形でないときに起きる問題

  • チーム補佐が担当チームを変えるときに複数行の更新が発生(冗長性)
  • チーム補佐とチームとの関連(A)が、社員に依存している
    • 社員がチームに参加するまで、関連Aを登録できない
    • 社員がチームから外れたときにレコードを削除すると、関連Aも削除されてしまう

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

ボイス-コッド正規形への分解時には気をつけないと非可逆な分解を行ってしまうことがある

失敗例

社員ID(PK) チームコード(PK)
000A 001
000B 001
000B 002
001F 001
001F 002
003O 002
チーム補佐(PK) チームコード
123W 001
456Z 001
003O 002
999Y 002
  • チームコードでJOINすると12行になってしまう
    • 多対多の結合になってしまったため
  • 【補】チーム補佐は社員を補佐する人なのに、チーム補佐と社員の関連がどっか行ってしまった

正しいBCNF

社員-チーム補佐

社員ID(PK) チーム補佐(PK)
000A 123W
000B 456Z
000B 003O
001F 123W
001F 003O
003O 999Y

チーム補佐-チーム

チーム補佐(PK) チームコード
123W 001
456Z 001
003O 002
999Y 002
  • 結合のキーをチーム補佐にしたことで、結合が1対多になった
    • 不当に行が増えることはない
  • 問題はまだ完全には解決していない
    • 社員とチームの関連が壊れてしまった
    • 社員-チーム補佐テーブルに(社員ID,チーム補佐)=(000A,003O)を追加するとおかしくなる
      • 補佐003Oが補佐できるのはチーム002のみ(チーム補佐-チームテーブルより)
      • 社員000Aはチーム001にしか所属していなかった(ボイス-コッド正規化前のテーブルより)
      • 社員000Aが、暗黙にチーム002にも所属することになる
      • 業務ルール的にそれは正しいのか？
        • 正しくないのなら、アプリケーション側で禁止しなければならなくなる

第4正規形

  • 関連エンティティ絡みで生ずる考え方
社員ID(PK) チームコード(PK) 製品コード(PK)
000A 001 P1
000A 001 P2
000B 001 P1
000B 002 P1
001F 001 P2
001F 002 P2
003O 002 P2
003O 002 P3
  • 表しているもの
    • 社員の所属チーム
    • 社員が開発にあたっている製品
  • 主キーだけなのでBNCFまでは成立している

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

  • 多値従属性
    • キーを決めると集合が定まる
      • cf. 関数従属性: キーを決めると非キーの値が決まる
    • 例: 社員000Aに対してチームコードは{001}
{社員ID} ->-> {チームコード}
{社員ID} ->-> {製品コード}

こうも書ける:

{社員ID} ->-> {チームコード|製品コード}
  • 業務ルール上、チームと製品との間には特に関係がないため、チームと製品との間には従属性なし

第4正規化を行う

  • 独立な多値従属性が複数存在するテーブルを分割

社員-チーム

社員ID(PK) チームコード(PK)
000A 001
000B 001
000B 002
001F 001
001F 002
003O 002

社員-製品

社員ID(PK) 製品コード(PK)
000A P1
000A P2
000B P1
001F P2
003O P2
003O P3
  • 社員が異動したとき更新が1行で済む(冗長性の排除)
    • 更新不整合の可能性がない
    • 更新コスト最小
  • 担当する製品の変更でも同様
  • 可逆的
    • 「社員ID」をキーにしたINNER JOINで元に戻る

第4正規形の意義

関連エンティティに含まれる関連は1つだけにすること

  • そも複数の関連を1つのテーブルで表現することに無理がある
  • ので、「自然な流れ」で「第3正規形であるが第4正規形でない」テーブルが作られることはあまりない
  • 関連エンティティを作る場合は、そこに含まれる関連は1つだけにすること
    • そうすれば自然に第4正規形になる

第5正規形

  • 業務ルール追加: 「チームによっても扱う製品が異なる」
  • 下記の多値従属性が追加される
{チームID} ->-> {製品コード}
  • 下記2つのテーブル(第4正規形)だけからではチーム-製品間の従属性がわからない

社員-チーム

社員ID(PK) チームコード(PK)
000A 001
000B 001
000B 002
001F 001
001F 002
003O 002

社員-製品

社員ID(PK) 製品コード(PK)
000A P1
000A P2
000B P1
001F P2
003O P2
003O P3

第5正規化を行う

  • 関連エンティティ追加する

チーム-製品間

チームコード(PK) 製品コード(PK)
001 P1
001 P2
001 P1
002 P1
001 P2
002 P2
002 P2
002 P3
  • 関連と関連エンティティとを1対1対応させれば自然に第5正規形になる

正規化についてのまとめ

正規化の3つのポイント

  • ポイント1
    • 更新時の不都合/不整合を排除するために行う
  • ポイント2
    • 従属性を見抜くことで可能になる
      • 部分関数従属がある -> 第2正規化せよ
      • 推移的関数従属がある -> 第3正規化せよ
      • 多値従属性がある -> 第4正規形に反する設計をしていないか？
    • 業務分析の必要性
      • 従属性は業務ロジックで決まること
  • ポイント3
    • いつでも非正規形に戻せる
      • 高次の正規形は低次の正規形のサブセット

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

  • 第3正規形までは原則そう
  • 関連エンティティは関連とエンティティが1対1に対応するように注意
    • 守れば自然と第4/5正規形になる
  • 正規化の欠点
    • 結合の多用によるパフォーマンス悪化
  • あえて非正規化することもある

コラム: 正規化って常識じゃないの？に対して

(前略)総じて、常識の意味を正確かつ形式的に述べることが偉業であることに気づいていない。 (C.デイト)

  • トンデモ設計をする人間は世の中にいるのである

常識は、多くの人に広めてこそ常識として価値を持つようになるのだ