正規化

このページでは、部分関数従属や推移的関数従属などの関数従属と、第1〜第3正規化の手順、正規化で防げる更新時異常、非正規化がわかります。

• 一言で言うと：
  • 表の中の重複をなくして、更新したときに矛盾が起きないように表を分ける話
  • 分け方の手がかりは、「この列が決まればあの列が決まる」という関数従属

手がかり（関数従属：部分・完全・推移的）→ 分け方（第1正規化 → 第2正規化 → 第3正規化）→ 分けすぎたら戻す（非正規化）

覚え方の軸

• 正規化は「繰返し → 部分 → 推移」の順に取り除く
  • 第1正規化は、繰返し部分をなくす（非正規形 → 第1正規形）
  • 第2正規化は、主キーの一部に決まってしまう列（部分関数従属）を別の表に出す
  • 第3正規化は、主キー以外の列に決まってしまう列（推移的関数従属）を別の表に出す
• 関数従属は「何が決まれば何が決まるか」
  • x → y：
    • xが決まればyが決まる（xが決定項、yが従属項）
  • 部分関数従属は、複合キーの一部だけでyが決まる。完全関数従属は、キー全部がないとyが決まらない
  • 推移的関数従属は、x → y → z と間接的に決まる
• 第2正規化がいるのは「主キーが複合キーのときだけ」
  • 主キーが1つの属性なら、部分関数従属は起きないので、第1正規形ならすでに第2正規形
• 正規化しないと起きる更新時異常は3つ
  • 修正時異常（何行も直す必要）、挿入時異常（登録できない）、削除時異常（消したくないものまで消える）
• 分けた表には、元の表の主キーを外部キーとして残す（結合で元に戻せるように）

関数従属：何が決まれば何が決まるか（★）

• 関数従属：
  • ある属性xの値が決まると、他の属性yの値が一意に決まる関係
  • 「x → y」と書く
• 独立属性（決定項）：
  • 「x → y」のx
• 従属属性（従属項）：
  • 「x → y」のy
• 関数従属が大事な理由：
  • 1つの表の中の属性間にある関数従属に注目して整理（正規化）すると、整合性を保ちやすいデータベースを設計できる
• 関数従属の例え：
  • 「郵便番号 → 町名」。郵便番号が決まれば町名が決まる。でも町名が決まっても郵便番号は1つに決まるとは限らない
• 多値従属：
  • xに対して1つのyではなく、複数の値を持つ1つの集合が決まる従属関係
  • 「x →→ y」と書く
  • 例えば、「社員 →→ 資格」（1人の社員に、複数の資格の集合が決まる）

① 部分関数従属

• 部分関数従属：
  • 「x → y」の関係で、yが、xの真部分集合にも関数従属するとき、yはxに部分関数従属するという
• 具体的には：
  • 独立属性xがx1とx2の2つの属性でできている場合、{x1, x2} → y が成り立ち、かつ、x1 → y または x2 → y のどちらかが成り立つなら、{x1, x2}とyの間に部分関数従属がある
  • 部分関数従属は、独立属性xが複数の属性でできている場合に起こりうる
• 真部分集合：
  • 集合x1がxの部分集合で、かつ x1 ＝ x ではないとき、x1はxの真部分集合という
• 自明な関数従属性：
  • x1がxの部分集合なら、「x → x1」は必ず成り立つ。このような関数従属性
  • 例えば、{売上番号, 商品番号} → 商品番号 は当たり前に成り立つ

② 完全関数従属

• 完全関数従属：
  • 「x → y」の関係で、yが、xのどの真部分集合にも関数従属しないとき
• 単一の属性なら常に完全：
  • 独立属性xが1つの属性でできている場合は、常に完全関数従属が成り立つ

完全関数従属と部分関数従属

• ①だけ成立：
  • ① {x1, x2} → y だけが成り立つなら、完全関数従属
• ①と②が成立：
  • ① {x1, x2} → y と ② x2 → y が両方成り立つなら、部分関数従属

③ 推移的関数従属

• 推移的関数従属：
  • 直接ではなく、間接的に関数従属している関係
  • 属性x、y、zで、「x → y」「y → z」「y →/ x」が成り立つとき、zはxに推移的関数従属しているという
• 「y →/ x」：
  • xはyに関数従属しない（yが決まってもxは決まらない）という意味
• 完全推移的関数従属：
  • さらに「z →/ y」が成り立てば、zはxに完全推移的関数従属しているという
• 注意：
  • 属性x、y、zは、互いに重複しない
• 「y → x」が成り立つ場合：
  • 「x ←→ y → z」となり、zはxとyに直接関数従属する（推移的ではない）
• 推移的関数従属の例え：
  • 「生徒番号 → 担任の先生 → 先生の内線番号」。生徒番号から内線番号は、先生を通して間接的に決まる

各関数従属の例（★）

• 売上明細表（売上番号、商品番号、商品名、単価、数量）：
  • “商品名”と“単価”は、主キーの一部である“商品番号”だけで決まるので、主キーに部分関数従属
  • “数量”は、{売上番号, 商品番号} が両方決まらないと決まらないので、主キーに完全関数従属
• 売上表（売上番号、日付、顧客番号、顧客名）：
  • 売上番号 → 顧客番号 → 顧客名 が成り立ち、顧客番号が決まっても売上番号は決まらない
  • だから、“顧客名”は“顧客番号”を介して、主キー“売上番号”に推移的関数従属

推論律

• 推論律：
  • 複数の関数従属から、1つの関数従属を導くための規則
• 推移律：
  • A → B、B → C ならば、A → C
• 合併律：
  • A → B、A → C ならば、A → BC
• 増加律：
  • A → B ならば、AC → BC
• 反射律：
  • BがAの部分集合ならば、A → B
• 擬推移律：
  • A → B、BC → D ならば、AC → D

正規化の手順：繰返し → 部分 → 推移の順に分ける（★）

① 第1正規化

• 第1正規化：
  • 関係データベースに定義できるのは平坦な2次元の表なので、繰返し部分を持つ表は、繰返しをなくして平坦にする必要がある
  • この繰返し部分を排除する操作
• 非正規形：
  • 繰返し部分を持つ表
• 第1正規形：
  • 第1正規化の結果できた表（繰返し部分を取り除いたもの）
• 非正規形の例：
  • 売上表（売上番号、日付、顧客番号、顧客名、[商品番号、商品名、単価、数量]の繰返し）
  • 例えば、売上番号S001の行に、P01（ボールペン）とP02（ノート）の2商品が入っている
• 第1正規化のやり方：
  • 主キー“売上番号”と、繰返し部分を一意に決められる“商品番号”を複合キーにして、繰返し部分を売上明細表として別の表に分ける
  • 主キーの一部である“売上番号”を、元の表の主キーを参照する外部キーにする
• 元の表の主キーを持たせる理由：
  • 結合で元の表を再現できるようにするため
• 第1正規形でも残るもの：
  • 売上明細表には、主キーの一部である商品番号に関数従属する部分関数従属が残る
• 表記のルール：
  • 主キーには実線の下線、外部キーには破線の下線を付ける
  • 試験問題に合わせて、主キーの実線が付いている属性には、外部キーの破線は付けない

② 第1正規形の不具合（更新時異常）（★）

• 更新時異常：
  • 第1正規形の表は、基本的には関係データベースに定義できる
  • でも、データが冗長なので、データ操作のときに不具合が起きる。この不具合のこと

更新時異常の3種類（★）

種類	何が起きるか（売上明細表の例）
修正時異常	商品名「ボールペン」を「油性ボールペン」に変えるとき、該当する行をすべて同時に変えなければならない。1行でも変え忘れると、データに不整合が起きる
挿入時異常	主キーが“売上番号”と“商品番号”の複合キーなので、まだ売上のない（売上番号が空値になる）新商品「付箋」は登録できない
削除時異常	売上実績が1つしかない商品の売上データを消すと、商品データも消える（「クリップ」が1回しか売れていなければ、その行を消すと「クリップ」という商品の情報がなくなる）。逆に、商品データを残そうとすれば、売上データを消せない

• 主キーが複合キーの場合：
  • それを構成するどの項目も空値は許されない（だから売上番号が空の商品は登録できない）
• 更新時異常の例え：
  • 修正時異常は、同じ住所を何冊ものノートに書いていて、引っ越したら全部直さないといけない
  • 挿入時異常は、「まだ誰も買っていない新商品」を売上ノートにしか書けないので、載せる場所がない
  • 削除時異常は、売上ノートの1行を消したら、その商品があったこと自体がわからなくなる
• 次のステップ：
  • 更新時異常を防ぐため、第2正規化と第3正規化を行う

③ 第2正規化

• 第2正規化：
  • 第1正規形の表に対して行う操作
  • 主キーの一部に部分関数従属する非キー属性を、別の表に分ける
  • つまり、すべての非キー属性が、主キーに完全関数従属している状態にする操作
• 第2正規形：
  • 第2正規化の結果できた表
• 第2正規化の例：
  • 売上明細表の“商品名”と“単価”は、主キーの一部“商品番号”に部分関数従属している
  • そこで、これを商品表（商品番号、商品名、単価）として独立させる
  • 元の表を再現できるように、売上明細表（売上番号、商品番号、数量）には、商品表の主キーを参照する外部キーとして“商品番号”を残す
• 第2正規化が必要なとき：
  • 主キーが複数の属性でできている場合だけ行う
  • 1つの属性でできているなら、部分関数従属は存在しないので、すでに第2正規形
• 第2正規形の論理的定義：
  • どの非キー属性も、候補キーの真部分集合に対して関数従属しない
  • （どの非キー属性も、候補キーに完全関数従属である）

④ 第3正規化

• 第3正規化：
  • 第2正規形の表に対して行う操作
  • 主キーに推移的関数従属している非キー属性を、別の表に分ける
  • つまり、非キー属性間の関数従属をなくし、どの非キー属性も主キーに直接関数従属している状態にする操作
• 第3正規形：
  • 第3正規化の結果できた表
• 第3正規化の例：
  • 売上表（売上番号、日付、顧客番号、顧客名）は第2正規形だが、「顧客番号 → 顧客名」という非キー属性間の関数従属がある
  • そこで、“顧客番号”を主キーにした顧客表（顧客番号、顧客名）として独立させる
  • 売上表（売上番号、日付、顧客番号）には、顧客表の主キーを参照する外部キーとして“顧客番号”を残す
• 第3正規形の論理的定義：
  • どの非キー属性も、候補キーに推移的関数従属しない
  • （どの非キー属性も、候補キーに直接関数従属している）

正規化の流れのまとめ（★）

ステップ	取り除くもの	結果
第1正規化	繰返し部分	非正規形 → 第1正規形（売上表＋売上明細表）
第2正規化	主キーの一部への部分関数従属	→ 第2正規形（商品表を分ける）
第3正規化	主キーへの推移的関数従属（非キー属性間の関数従属）	→ 第3正規形（顧客表を分ける）

• 最後にできる4つの表：
  • 売上表（売上番号、日付、顧客番号）
  • 売上明細表（売上番号、商品番号、数量）
  • 商品表（商品番号、商品名、単価）
  • 顧客表（顧客番号、顧客名）
• 正規化の例え：
  • 1冊の雑多なノートを、「売上ノート」「明細ノート」「商品カタログ」「顧客名簿」に分ける。商品名を直すときは、商品カタログの1か所だけ直せばよい

正規形の種類

• 正規形の順番：
  • 第1正規形 → 第2正規形 → 第3正規形 → ボイス・コッド正規形 → 第4正規形 → 第5正規形
  • 一般的なデータベースなら、第3正規形まで正規化されていれば十分といわれている
• ボイス・コッド正規形：
  • 第3正規形でも、候補キーの真部分集合から、他の候補キーの真部分集合への関数従属、あるいは候補キー以外の属性から候補キーの真部分集合への関数従属が残っている可能性がある
  • この関係を分解したのが、ボイス・コッド正規形

⑤ 正規化と非正規化

• 正規化の目的：
  • データ操作にともなう更新時異常の発生を防ぐこと
  • 属性間の関数従属を少なくし、データの重複をなくすことで防ぐ
• 正規化の弱点：
  • 正規化を進めると、表がいくつにも分かれる
  • 必要なデータを取り出すには表の結合が必要になり、処理時間がかかる
• 非正規化：
  • 処理速度が厳しく求められる場合や、更新時異常が起きにくい場合（例えば、更新が少ない表）は、あえて正規化を進めないか、正規化したものを元に戻す
  • ここでいう非正規化は、アクセスパターンを考えたうえで、どの表を統合するか、どの属性を表間に重複させるかを考えること
• 非正規化の例え：
  • ノートを細かく分けすぎると、1つの調べものに何冊も開く必要がある。よく見る組合せは、あえて1冊にまとめておく（ただし、直すときは両方直す）

例題（正規化の判定）

• 問題：
  • 受講表（学生番号、科目番号、学生名、科目名、担当教員番号、担当教員名、成績）がある。主キーは{学生番号, 科目番号}。関数従属は「学生番号 → 学生名」「科目番号 → 科目名、担当教員番号」「担当教員番号 → 担当教員名」「{学生番号, 科目番号} → 成績」。第3正規形にすると、どんな表に分かれるか
• 考え方：
  • 繰返しはないので、すでに第1正規形
  • 第2正規化：学生名は学生番号だけで、科目名・担当教員番号・担当教員名は科目番号だけで決まる（部分関数従属）ので、学生表と科目表に分ける
  • 第3正規化：科目表の中に「担当教員番号 → 担当教員名」（非キー属性間の関数従属）があるので、教員表に分ける
• 答え：
  • 受講表（学生番号、科目番号、成績）
  • 学生表（学生番号、学生名）
  • 科目表（科目番号、科目名、担当教員番号）
  • 教員表（担当教員番号、担当教員名）
• 確かめ：
  • 元の7つの属性が、どこかの表に必ず1回は入っている（キーは結合用に重複）。外部キー（受講表の学生番号・科目番号、科目表の担当教員番号）で結合すれば、元の表に戻せる

混同しやすいペア

• 独立属性（決定項） ／ 従属属性（従属項）
  • 独立属性は、「x → y」のx（決める側）
  • 従属属性は、「x → y」のy（決まる側）
• 関数従属 ／ 多値従属
  • 関数従属は、xが決まると1つのyが決まる（x → y）
  • 多値従属は、xが決まると複数の値の集合が決まる（x →→ y）
• 部分関数従属 ／ 完全関数従属
  • 部分関数従属は、複合キーの一部（真部分集合）だけでyが決まる
  • 完全関数従属は、キーのどの真部分集合でもyが決まらない（キー全部が必要）
• 推移的関数従属 ／ 完全推移的関数従属
  • 推移的関数従属は、x → y、y → z、y →/ x が成り立つ
  • 完全推移的関数従属は、さらに z →/ y も成り立つ
• 部分集合 ／ 真部分集合
  • 部分集合は、元の集合と同じでもよい
  • 真部分集合は、元の集合と同じではない部分集合
• 推移律 ／ 擬推移律
  • 推移律は、A → B、B → C ならば A → C
  • 擬推移律は、A → B、BC → D ならば AC → D
• 合併律 ／ 増加律 ／ 反射律
  • 合併律は、A → B、A → C ならば A → BC
  • 増加律は、A → B ならば AC → BC
  • 反射律は、BがAの部分集合ならば A → B
• 非正規形 ／ 第1正規形
  • 非正規形は、繰返し部分がある
  • 第1正規形は、繰返し部分を取り除いた平坦な表
• 第1正規化 ／ 第2正規化 ／ 第3正規化
  • 第1正規化は、繰返し部分をなくす
  • 第2正規化は、部分関数従属をなくす（主キーが複合キーのときだけ必要）
  • 第3正規化は、推移的関数従属（非キー属性間の関数従属）をなくす
• 修正時異常 ／ 挿入時異常 ／ 削除時異常
  • 修正時異常は、同じデータを何行も同時に直す必要がある
  • 挿入時異常は、主キーの一部が空になるデータを登録できない
  • 削除時異常は、1行消すと、残したい別のデータまで消える
• 第3正規形 ／ ボイス・コッド正規形
  • 第3正規形は、非キー属性が候補キーに推移的関数従属しない
  • ボイス・コッド正規形は、さらに、候補キーの一部への関数従属も分解したもの
• 正規化 ／ 非正規化
  • 正規化は、表を分けて重複をなくし、更新時異常を防ぐ（結合が増えて遅くなる）
  • 非正規化は、処理速度のために、アクセスパターンを考えて表を統合したり属性を重複させたりする

---

← 06 データベースの目次に戻る