7 正規化理論
| 正規形 | Abbreviation | 定義 |
|---|---|---|
| 第一正規形 | 1NF | ドメインがシンプルであること |
| 第二正規形 | 2NF | 1NFを満たし、全ての非キー属性が各候補キーに完全関数従属していること |
| 第三正規形 | 3NF | 2NFを満たし、全ての非キー属性が各候補キーに推移的関数従属しないこと |
7.1 第一正規形
リレーショナルデータベースを設計するには、一般的に実体関連モデルを用いてER図を作成し、そこからリレーションスキーマを導出する。得られたリレーションスキーマは、全て第一正規形を満たさなければならない。
定義 7.1 リレーションスキーマ\(\boldsymbol{R}(A_1, A_2, \ldots, A_n)\)が第1正規形（1NF, first normal form）であるとは，任意のドメインがシンプルであることをいう．
シンプルはドメインの元が原子的（atomic），即ち分解不可能（nondecomposable）な値であることを指す．
7.2 第二正規形
7.2.1 更新時異状
しかし、リレーションスキーマが第1正規形を満たしていても、様々な更新時異状（update anomaly）が発生する可能性がある。
例えば、以下のようなリレーションを考える。「学籍番号」と「科目番号」を主キーとする。
| 学籍番号 | 科目番号 | 科目名 | 成績 |
|---|---|---|---|
| 2501 | CS101 | 計算機科学 | 92 |
| 2501 | CS102 | プログラミング | 85 |
| 2502 | CS101 | 計算機科学 | 88 |
| 2502 | CS103 | データベース | 90 |
- 挿入時異状: 新しい科目「CS104 人工知能概論」を追加したいが、まだ受講者がいないため、挿入できない。
- 削除時異状: (2502, CS103, データベース, 90)を削除すると、科目「データベース」の情報が失われる。
- 修正時異状: 科目名「計算機科学」を「コンピュータサイエンス」に変更したいが、複数の行を修正する必要がある。
7.2.2 関数従属性
定義 7.2 リレーションスキーマ\(\boldsymbol{R}(X, Y, Z)\)において、関数従属性（functional dependency）\(X \to Y\)が存在するとは、\(\boldsymbol{R}\)の任意のインスタンス\(R\)に対して、次の条件が成り立つことをいう。
\[ \forall r_1, r_2 \in R, \quad (r_1[X] = r_2[X] \implies r_1[Y] = r_2[Y]) \]
つまり、\(X \to Y\)が存在する場合、任意のインスタンス\(r_1\)と\(r_2\)において、\(X\)の値が同じならば、\(Y\)の値も同じであることを意味する。
定義 7.3 関数従属性\(X \to Y\)において、\(X\)の任意の真部分集合\(X'\)に対して、\(X' \not\to Y\)が成り立つとき、\(Y\)は\(X\)に完全関数従属しているという。
例 7.1 成績(学籍番号, 科目番号, 科目名, 成績)において、学籍番号と科目番号の組み合わせが主キーであるとする。次のような完全関数従属性が存在する。
- {学籍番号, 科目番号} \(\to\) {成績}
- {科目番号} \(\to\) {科目名}
例 7.2 下記のリレーションスキーマでは、完全関数従属性を定義してみよう。
- 注文(顧客ID, 商品番号, 商品名, 単価, 数量, 金額)
- 成績(学籍番号, 科目番号, 科目名, 成績, 単位数, 担当教員, 判定)
- 供給(供給者ID, 商品番号, 在庫数, 所在地, 供給者名, 電話番号)
7.2.3 正規化
定義 7.4 リレーションスキーマ\(\boldsymbol{R}\)が第2正規形（2NF, second normal form）であるとは，次の条件を満たすことをいう．
- \(\boldsymbol{R}\)は第1正規形である．
- \(\boldsymbol{R}\)の全ての非キー属性が，\(\boldsymbol{R}\)の全ての候補キーに完全関数従属している．
例 7.3 成績(学籍番号, 科目番号, 科目名, 成績)
科目名は主キーの真部分集合である{科目番号}に完全従属しているので、リレーション成績は第二正規形ではない。
そこで、科目番号\(\to\)科目名という関数従属を用いて、成績を成績(学籍番号, 科目番号, 成績)と科目(科目番号, 科目名)に分解すると、どちらも第二正規形になる。
例 7.4 下記のリレーションスキーマを第二正規形に正規化してみよう。
- 注文(顧客ID, 商品番号, 商品名, 単価, 数量, 金額)
- 成績(学籍番号, 科目番号, 科目名, 成績, 単位数, 担当教員, 判定)
- 供給(供給者ID, 商品番号, 在庫数, 所在地, 供給者名, 電話番号)
7.3 第三正規形
7.3.1 更新時異状
第二正規形を満たしていても、更新時異状が発生する可能性がある。
例えば、以下のようなリレーションを考える。「学籍番号」を主キーとする。
| 学籍番号 | 氏名 | 学部 | キャンパス |
|---|---|---|---|
| 2501 | 山田太郎 | 理工学部 | 小金井 |
| 2502 | 佐藤花子 | 情報学部 | 小金井 |
| 2503 | 鈴木一郎 | 経済学部 | 多摩 |
| 2504 | 田中二郎 | 文学部 | 市ヶ谷 |
| 2505 | 高橋三郎 | 文学部 | 市ヶ谷 |
ここで、学籍番号\(\to\)氏名, 学籍番号\(\to\)学部, 学籍番号\(\to\)キャンパスとする。主キーは学籍番号であるため、このリレーションは第二正規形である。
- 挿入時異状: 新しい学部「データサイエンス学部」ができ、キャンパスが小金井と決定したとする。学籍番号が決まっていないため、挿入できない。
- 削除時異状: 学籍番号2501の学生が退学した場合、(理工学部, 小金井)の情報が失われる。
- 修正時異状: 文学部を多摩キャンパスに移動したとする。複数の行を修正する必要がある。
7.3.2 推移的関数従属性
定義 7.5 リレーションスキーマ\(\boldsymbol{R}(X, Y, Z)\)において、\(X \to Y\)と\(Y \to Z\)が存在するとき、\(X \to Z\)が成立する。
例 7.5 学生(学籍番号, 氏名, 学部, キャンパス)には
\[ \text{学籍番号} \to \text{学部}\\ \text{学部} \to \text{キャンパス} \]
があるため、推移的関数従属性
\[ \text{学籍番号} \to \text{キャンパス} \]
が存在する。
7.3.3 正規化
定義 7.6 リレーションスキーマ\(\boldsymbol{R}\)が第3正規形（3NF, third normal form）であるとは，次の条件を満たすことをいう．
- \(\boldsymbol{R}\)は第2正規形である．
- \(\boldsymbol{R}\)の全ての非キー属性が，\(\boldsymbol{R}\)の全ての候補キーに推移的関数従属していない．
例 7.6 学生(学籍番号, 氏名, 学部, キャンパス)には、キャンパスが学籍番号に推移的に従属するので、第3正規形ではない。
\(\text{学部} \to \text{キャンパス}\)という関数従属を用いて、学生(学籍番号, 氏名, 学部)と学部(学部, キャンパス)に分解すると、どちらも第3正規形になる。
例 7.7 下記のリレーションスキーマを第3正規形に正規化してみよう。
- 社員(社員番号, 氏名, 部署, 勤務地)
- 社員(社員番号, 氏名, 職位, 給与)
7.4 練習
下記のリレーションスキーマは第一正規形を満たしているが、第二正規形や第三正規形を満たしていない。問題1から3を解いて、第三正規形に正規化してみよう。同じ職位の場合は、同じ給与であるとする。プロジェクトにおいての社員の仕事時間は、プロジェクト番号と社員番号の組み合わせで一意に決まるとする。
- プロジェクト(プロジェクト番号, プロジェクト名, 社員番号, 社員名, 職位, 給与, 仕事時間)
- リレーションスキーマを完全関数従属性を定義してみよう。
- 定義した完全関数従属性を用いて、リレーションスキーマを第2正規形に正規化してみよう。
- 第2正規形に正規化したリレーションスキーマを用いて、第3正規形に正規化してみよう。