２．３正規化理論

データベーススペシャリスト試験のもっとも重要な正規化理論について学習、まとめます。

正規化理論は

正規化理論は、関係モデルでテーブル設計を行う場合の理論的指針となる。

２．３．１正規化の意義

（１）１事実複数箇所の問題点

ある関係で一つの属性Xの値を決めると、別の属性Ｙの値が一意に決まる関係を関数従属という。

関係Rでは、主キーの従業員番号が決まると、一意に指名、所属コード、所属名称が決まる（関数従属）。さらに、主キー以外の属性である所属コードに所属名称が関数従属する。主キー以外の属性に関数従属する属性があるとき、関係Rに推移的関数従属性があるという。推移的関数従属性があると、関係Rは同じ値の｛所属コード、所属名称｝の値を、複数の行にわたって寿福して保持することになる。

この関係Rwo更新（挿入、更新、削除）しようとすると、事前登録ができない、重複更新が必要、ある関係が喪失するなど、データの矛盾をきたし、データ管理上の問題が生じる。

①事前登録

事前に所属コードとその所属名称を登録することができない。ある従業員がその所属部門に配属された時点でしか登録できず、運用上問題。

②重複更新、重複登録

関係Rの所属名称を更新する場合、同時に同じ｛所属コード、所属名称｝の値を持つ行を複数個所重複して更新しなければなない。また関係Rにある行を追加する場合には、すでに、特定の｛所属コード、所属名称｝の値を持つ行が存在しても、重複して登録しなければならない。登録・更新のコストがかかるうえ、更新漏れがあるとデータの整合性が失われる。

③関係の喪失

関係R上のある行を削除すると仮定する。この場合、ただ一つしかない｛所属コード、所属名称｝の関係が失われる可能性がある。１つの従業員を削除すると一つしかない所属も失われる。この関係が失われると、データに矛盾をきたし、データ整合性上の問題が生じる。

（２）１事実１箇所

正規化理論は、こうした１事実が複数箇所にある関係を分解し、関係を１事実１箇所（1 fact in 1 place）の単純な形式にするための　理論的指針を与える。正規化理論に基づいて、関係Rを分解すると１事実１箇所の状態が得られる。

２．３．２関数従属性

（１）関数従属の定義

関係Rの属性X,Yにおいて、Xの値が決まるとYの値が一意（ただ一つ）に決まるとき、ＸとＹの間には関数従属性（function dependency）がある、あるいはYはXに関数従属するといい、X→Yと書く。別の言い方をすればＸがＹを一意に決定するので、ＸはＹを関数決定する。ＸはＹを関数決定すので、Ｘを決定項（determinant）、Ｙを被決定項（resultant）という。

①X→Yは、XとYの値にN対１、１対１の対応関係があることを意味し、Xに対応するYの値は重複してもよい。

②X→Y、Y→Xの場合は、XとYは１対１に対応する。

（２）関数従属性図

（３）関数従属性に関する推論則

関係Rが複雑な関数従属性を含む場合、次に示す関数従属性に関する推論則（inference rule）を用いることで、候補キーや推移的関数従属性などを導くことできる。

①反射率：Uを関係Rの属性集合とする。Y⊆X⊆Uのとき、X→Yは関数従属である。これは右辺のYが左辺のXに含まれるならばX→Yであること、つまり、XとYとの間に自明（trivial）関数従属性があることを示す。

②増加率：X→Yが成立し、かつＺ⊆Ｕのとき、ＸＺ→ＹＺが成り立つ。Ｘ，Ｙ，Ｚは属性の集合、ＸＺはＸ∪Ｚの略記。

③推移率：X→YとY→Zが成立すると、X→Zが成立。以上から次のルールが導きだされる。

④合併率：X→Y、X→Zが成立、X→YZが成立。

⑤分解率：X→YZが成立、X→Y、X→Zが成立。

⑥擬推移率：X→Y1、Y1Y2→Zが成立、XY2→Ｚが成立。

（４）完全関数従属

関係Rの属性X、Yにおいて、YがXに関数従属で、かつYがXのどの真部分集合にも関数従属でないとき、YはXに完全関数従属（あるいは全関数従属）という。なお真部分集合は次のように定義、X’⊂XかつX’‡XのときX’はXの真部分集合である。

２．３．３第一正規形

（１）非正規形

学生は関係の中に｛科目名、成績、先生｝という繰り返し項目を含んでいる。関係の要素の中に繰り返し項目を含んでいるような関係を非正規形（non-normal form）という。より一般的に言えば、関係の属の定義域が集合の集合（べき集合）であるとき、このような属性を含む関係を非正規形という。関係を要素とする定義域を非単純定義域という。つまり、非正規形は関係の要素の中に関係を含んだもの。非正規形は意味的には理解しやすいが、冗長で重複更新の問題が発生する。一方、属性の定義域の集合の中心集合を含まないものを単純定義域といい、次に述べる第一正規形以降の正規形はすべて単純定義域からなっている。

（２）第１正規形（１NF)

関係Rがその属性に繰り返し項目（非単純定義域）を一つも含まないものを第１正規形（first normal form）という。ただし、第１正規形は後述する部分従属性を含む。

（３）第１正規化

関係Rの中の繰り返し項目を取り除くことによって第１正規形が得られる。繰り返し項目を含む非正規形を第１正規形へ導く手順のことを第１正規化といい、次の２つがある。

①フラットなテーブルにする方法

繰り返し項目を含めて、非正規形を単にフラットな表にして第１正規形を得られる。ただし、全体のキーは｛固定部分のキー、繰り返し項目部分のキー｝の複合キーになる。

②繰り返し項目を分解する方法

第１正規形にするとは、関係の中の繰り返し項目を排除することなので、繰り返し項目に元の関係のキーを埋め込んで、関係を分解していく。こうすることで第１正規形が得られる。この場合、分解された繰り返し項目を含む側の関係のキーは｛元の関係のキー、繰り返し項目部分のキー｝の複合キーとなる。

２．３．４第２正規形

（１）部分関数従属性

関係Rにおいて非キー属性がキーの部分集合に関数従属することを、関係Ｒは部分関数従属性（partial functional dependency）を含む、あるいは非キー属性はキーに部分関数従属するという。キーの一部（新部分集合）に部分関数従属するといわないので注意。非キー属性（non-prime attribute）といは、関数Rのどの候補キーにも属さない属性のこと。第１正規形であるが第２正規形ではなく、キーが複数の属性（複合キー）からなる関係はこの部分関数従属性を含む。

（２）第２正規形（２NF)

関係Rが第１正規形で、関係Rの非キー属性はいずれも、いかなる候補キーにも完全関数従属であるとき、これを第２正規形という。

完全関数従属という条件から第２正規形（second normal form）は部分関数従属性を含まないが推移的関数従属性を含む。

（３）第２正規形

関係Rの非キー属性がキーの真部分集合に関数従属するならば、この関数従属性部分を元の関係から分解することによって第２正規形が得られる。第１正規形から第２正規形に分解することを第２正規化という。

①第２正規形への分解例その１

フラットなテーブルにする方法を用いて得られた第１正規形”学生”を、第２正規形へ分解。

学生（学生番号、氏名、学年、学部、学部所在地、科目名、成績、先生）は次の部分関数従属性を持つ

学生番号→指名、学年、学部、学部所属地

科目名→先生

学生（学生場番号、氏名、学年、学部、学部所在地）

科目（科目名、先生）

履修（学生番号、科目名、成績）

第２正規形への分解例その２

繰り返し項目を分解する方法によって得られた第１正規形の履修を第２正規形へ分解。履修（学生番号、科目名、成績）において、成績は候補キーである｛学生番号、科目名｝に関数従属する。

履修（学生番号、科目名、成績）

科目（科目名、先生）

２．３．５第３正規形とBCNF

（１）推移的関数従属性（transitive functional dependency）

関係Rの互いに重複しない属性X,Y,Zにおいて、YはXに関数従属し、ZはYに関数従属し、XはYに関数従属しないとき、ZはXに推移的関数従属するという。

X→Y、Y→Z、Y→Xならば、ZはXに推移的関数従属（X→Z）。

（２）推移的関数従属性の留意点

①関係Rの属性X,Y,Zにおいて、X,Y,Zは互いに１対１対応

X⇔Y⇔Zならば、ZはXに推移的関数従属しない

Xを候補キーとすれば、Y,Zも候補キー

②関係Rの属性X,Y,Zにおいて、X,Yが１対１対応

X⇔Y→Zならば、ZはXに推移的関数従属しない

この場合、Xを候補キーとすればYも候補キーであり、ZはXとYに直に関数従属する（推移的関数従属しない）

③関係Rの属性X,Y,Zにおいて、YとZが１対１対応

X→Y⇔Zならば、ZXに完全推移的関数従属しない

X→Y⇔Zは、Z→Z⇔Yの方にも変更できる。X→Yと同時にX→Zでもある。Y,ZはXに直に関数従属する。

④関係Rの属性X,Y,Zにおいて、YがXの真部分集合に関数従属

⑤関係Rの属性X,Y,Zにおいて、Xの属性とYの属性が一部重複

⑥関係Rの属性X,Y,Zにおいて、ZがYまたはXに属するとき

（３）弾３正規形（３NF)

関係Rが第２正規形で、非キー属性が候補キーに推移的関数従属しないものを第３正規形（third normal form）という。なお第３正規形は任意のキーの要素間に完全関数従属ではない関数従属性がっても構わない

（４）第３正規化

関係Rの非キー属性がキーに推移的関数従属するならば、この推移的関数従属性部分を元の関係から分解することのによって第３正規形が得られる。第２正規形から第３正規形に分解することを第３正規化という。

学生（学生番号、氏名、学年、学部、学部所在地）においては、キー以外の属性である学部と学部所在地に、学部→学部所在地という関数従属性がある。学生番号→学部→学部所在地という推移的関数従属性を持つので、次のように分解される。

学生（学生番号、氏名、学年、学部）

学部（学部、学部所在地）

（５）ボイス・コッド正規形（BCNF:Boyce-Codd Normal Form)

第１正規形の関係Rのいかなる属性の組Xに対しても、Xに含まれない属性の組がXに関数従属するときは、Rのすべての属性はXに関数従属する。このとき関係Rはボイス・コッド正規形（BCNF)であるという。

BCNFでいう関数従属は、完全関数従属（部分関数従属を含まない）を意味する。また推移的関数従属も含まないことを意味する。したがって第１、２正規形はBCNFでない。なぜなら第１正規形は部分関数従属性を含み、第２正規形は推移的関数従属性を含むため。

BCNFへの損失なしの分解は元の関係に含まれた関数従属性を保存するとは限らない

（６）第３正規形とBCNFの違い

仕入（第３正規形）（業者番号、業者名、商品番号、数量）

候補キー：｛業者番号、商品番号｝、｛業者名、商品番号｝。商品番号は重複キー。

↓

業者（BCNF)（業者番号、業者名）

仕入（BCNF)（業者番号、商品番号、数量）

（７）適切な分解と不適切な分解

正規化の過程では、元の関係を射影によって分解するが、結合すると再び元の関係が得られるような分解を、損失なしの分解という。損失なしの分解さえ行えば特に不都合はないように思えるが、この場合にも、適切な分解、不適切な分解という問題がある

（８）関数従属性が保存しないＢＣＮＦへの損失なしの分解

２．３．６第４正規形

(1)多値従属性

関係Rの社員番号と子供や趣味の間には１第多数の対応関係がある。社員番号が決まると、その子供の集合が決まり、同時に（社員番号、趣味）の値に対しても、子供の集合が決まる。より正確には（社員番号、趣味）に対応してる子供は社委員番号にだけ依存し、趣味の値には関係しない（独立している）。このような場合、社員番号と子供の間には多値従属性（multi-valued dependency）がある、あるいは子供は社員番号に多値従属するといい、社員番号→→子供と書く。

さらに社員番号、および（社員番号、子供）の値に対して趣味の集合も決まる。すなわち、社員番号→→子供であれば社員番号→→趣味であり、これを多値従属性の対称性という。対称性のある多値従属性のことを自明でない多値従属性（not trival multi-valued dependency）といい、社員番号→→子供｜趣味と書く。

（２）第４正規形への分解

関係R（X,Y,Z)がX→→Y｜Zとなる自明でない多値従属性を含む場合、関係Rを分解して得られる。Ｒ１（Ｘ，Ｙ）、Ｒ２（Ｘ，Ｚ）を第４正規形（forth normal form）という。

R1、R2は、X→→Y（X→→Y|θ）あるいはX→→Zの関係だけが残り、対称性が存在しない。これを自明な多値従属性（trivial multi-valued dependency）という。

（３）第４正規形（４NF)

関係Rの中に多値従属性A→→Bが存在するときは、津にRのすべての属性XがAに関数従属する場合（A→X）に、関係Rは第４正規形であると定義される。

なお、第４正規形にするといことは、繰り返し項目を排除することであり、実際にはこれ以前の正規化の段階で取り除かれる場合が多い。

２．３．７第５正規形

（１）結合従属性

関数従属性や多値従属性を含む関係では、これらを２つの射影（関係）に損失なしに分解ｓちあが、次に述べる結合従属性（join dependency）を含む関係は、これを３つ以上の射影に損失なしに分解する。３つ以上の射影に分解できることを、３－分解可能（three-decomposition）、ｎ－分解可能（n-decomposition）という。

（２）第５正規形（５NF、projection/join normal form）

再結合すると元の関係を生成できるような射影によって得られるR1,R2,・・・、Rｍを第５正規形（fifth normal form）という。関係Ｒiの候補キーをＸとすると、関係Ｒi内のすべての結合（すなわち元の関係との結合従属性）が、XがRiの候補キーであるとういこと自体によって暗黙にいみされている場合、その関係Riは、第５正規形である。