4. リレーショナルモデルと 正規化

4. リレーショナルモデルと
正規化
講師: 大川 昌弘
[email protected]
更新日: 2015/05/10
1
正規化
• 冗長性を排除し、異常の発生を防ぐ
• 正規形の種類
–
–
–
–
–
–
第1正規形(1NF: Normal Form)
第2正規形(2NF)
第3正規形(3NF)
ボイスコッド正規形(BCNF)
第4正規形(4NF)
第5正規形(5NF)
• 重要な概念
– 関数従属性(FD: Functional Dependency)
2
第1正規形
• 以下のように定義される(どちらも言っていることは同じ)
– すべての属性値がスカラ値(それ以上分解できない値)であること
– すべてのドメインが、モデリング上は分解不可能な単純値のみを持
つ集合であること
• 非正規形の例(商品購入情報)
購入ID
顧客ID
購入日
商品ID
B00001
C007651
2012/02/18
P1234, P2578
B00002
C003541
2012/04/10
P3376, P9124, P5796
購入ID
顧客ID
購入日
商品ID
B00001
C007651
2012/02/18
P1234
B00001
C007651
2012/02/18
P2578
B00002
C003541
2012/04/10
P3376
B00002
C003541
2012/04/10
P9124
B00002
C003541
2012/04/10
P5796
• 第1正規形
3
異常(anomaly)
• 悪い設計のデータベースでは、以下の種類
の異常が発生する
– 更新異常(update anomaly)
– 削除異常(deletion anomaly)
– 挿入異常(insertion anomaly)
4
更新異常(update anomaly)
• 例:第1正規形の以下のリレーション
– 商品情報(商品ID, 商品名, 製造元, 問い合わせ先)
商品ID
商品名
製造元
問い合わせ先
B00001
商品A
会社1
03-1234-5678
B00002
商品B
会社1
03-1234-5678
B00003
商品C
会社2
03-5555-9876
B00004
商品D
会社2
03-5555-9876
• 更新異常
– ある事実を表す値が複数ある場合、それを変更する際、該当するす
べてを変更しないと異常となる
– 上記の例では、ある製造元の問い合わせ先が変更となった場合、2
つのタプルを更新する必要がある
5
削除異常(deletion anomaly)
• 例:第1正規形の以下のリレーション
– 商品情報(商品ID, 商品名, 製造元, 問い合わせ先)
商品ID
商品名
製造元
問い合わせ先
B00001
商品A
会社1
03-1234-5678
B00002
商品B
会社1
03-1234-5678
B00003
商品C
会社2
03-5555-9876
B00004
商品D
会社2
03-5555-9876
• 削除異常
– ある事実を表す値を削除すると、別の事実を表す値も消えてしまうこ
とを削除異常という
– 上記の例では、「会社1」の商品をすべてリニューアルするため、登録
されているすべての商品を削除すると、「会社1」の問い合わせ先も消
えてしまう
6
挿入異常(insertion anomaly)
• 例:第1正規形の以下のリレーション
– 商品情報(商品ID, 商品名, 製造元, 問い合わせ先)
商品ID
商品名
製造元
問い合わせ先
B00001
商品A
会社1
03-1234-5678
B00002
商品B
会社1
03-1234-5678
B00003
商品C
会社2
03-5555-9876
B00004
商品D
会社2
03-5555-9876
• 挿入異常
– ある事実を表す値を挿入できないことを挿入異常という
– 上記の例では、新たに「会社3」と取り引きを行うこととしたが、具体的
に扱う商品がまだ決まってない場合、製造元と問い合わせ先が登録
できない
7
関数従属性(FD)
• 定義
– ある属性集合Xの値が決まると、属性集合Yが一
意に決まるとき、YはXに関数従属するといい、
X→Y
と表記する
8
完全関数従属
• 関数従属性 X → Y で、Xの任意の真部分集合X’ (X’ ⊂ X)
について X’ → Y が成立しないとき、Y は X に完全関数従
属するという
– 例
A
B
C
D
• AB → C
• A↛C
• B↛C
– C は {A, B} に完全関数従属する
9
部分関数従属
• 属性集合 X の真部分集合 X’ について、X’ → Y が成立
するとき、Y は X に部分関数従属するという
–例
A
B
C
D
• B→C
– C は {A, B} に部分関数従属する
10
推移的関数従属
• X → Y および Y → Z が成立するとき、Z は X に推移的
関数従属するという
–例
A
B
C
D
• A→B
• B→C
– C は A に推移的関数従属する
11
例
• 正規化前
– 商品購入情報(購入ID, 購入日, 顧客ID, 顧客名)
– 商品購入明細情報(購入ID, 商品ID, 商品名, 個数)
12
第2正規形
• 定義
– 第1正規形で、すべての非キー属性がキーの真部分集合に関数従属しない
• 第2正規形への変換
– 真部分集合に関数従属するものを別リレーションとする
• 例
キー
A
B
C
A
B
D
B
C
D
キー
キー
13
第2正規形への変換
• 例
– 商品購入明細情報(購入ID, 商品ID, 商品名, 個数)
キー
購入ID
商品ID
商品名
個数
– 関数従属
(1) {購入ID,商品ID} → {商品名,個数}
(2) 商品ID → 商品名
– 第2正規形
• (2)がキー{購入ID,商品ID}の真部分集合「商品ID」に関数従属しているので、(2)を
別リレーションとする(リレーション名は適当な名前を付ける)
• 商品購入明細情報(購入ID, 商品ID, 個数)
• 商品情報(商品ID, 商品名)
14
第3正規形
• 定義
– 第2正規形で、すべての非キー属性がキーに推
移的関数従属しない
• 別の定義
– リレーションRで成立するすべての自明でない関
数従属 X → Y について
• Xが超キーであるか、または、
• Y がキーの要素(キー属性、素属性)である
18
第3正規形への変換アルゴリズム
• 以下のアルゴリズムに従ってリレーションを分解する
– すべての関数従属 X → Y について、XY をスキーマとする
リレーションを定義
– 上記で定義されたリレーションのいずれにも元のリレー
ションのキーが含まれない場合、元のリレーションのキー
をスキーマとするリレーションも定義
19
第3正規形への変換例
• スキーマ
– 商品購入情報(購入ID, 購入日, 顧客ID, 顧客名)
キー
購入ID
購入日
顧客ID
顧客名
• 関数従属
(1) {購入ID} → {購入日,顧客ID}
(2) 顧客ID → 顧客名
• 第3正規形への変換
(1) 商品購入情報(購入ID, 購入日, 顧客ID)
(2) 顧客情報(顧客ID, 顧客名)
– 元のリレーションのキー {購入ID} は分解されたリレーション(1)に含ま
れるので、以上で変換終了
20
第1正規形とその正規化
関数従属
1NFの例
A
B
C
D
E
キー
キーの真部分集合に関数従属
正規化
関数従属
A
B
C
D
キー
B
キー
関数従属
E
24
第2正規形とその正規化
関数従属
2NFの例
A
B
C
D
E
キー
推移的関数従属 ( AB → D → E )
正規化
関数従属
A
B
C
D
キー
D
キー
関数従属
E
25
END
34