Transcript 正規化

第５章 データベースの設計
５．１
５．２
５．３
５．４
データベース設計の概要
ERモデルとスキーマ設計
正規化
一貫性制約
５．３ 正規化
１．正規化の必要性
① 設計者によってERモデルの記述が異なるケース
が生じる。
② 得られた関係スキーマに問題が生じることがある。
③ ERモデルを用いずに設計した場合、直感的には
正しくても問題が生じることがある。
最も多いのは、重複（冗長性）があることで、デー
タベースの内容更新時に、さまざまな異常が起き
るケース。
例題
学生1人で複数の学科を履修するので、
学生の主キーは、｛氏名，科目｝
氏名
学科
住所
学年
科目名
成績
更新異常の例
(a) 変更異常
(b) 挿入異常
(c) 削除異常
氏名
学科
住所
学年
科目名
成績
(a) 変更異常
情報を変更する際，
重複するすべての情報を変更しないと，
同一であるべき情報間に矛盾が生じる。
学生が転居し住所を変更する場合，
履修科目分のタプルをすべて変更
氏名
学科
住所
学年
科目名
成績
(b) 挿入異常
学生が１つも科目を履修していない場合，学
生の住所などの情報を含んだタプルを挿入で
きない。
科目名がキーになっているため，科目名に値が
ないタプルを挿入できない
氏名
学科
住所
学年
科目名
成績
(c) 削除異常
ある学生の履修科目が１つしかない場合，学生
の情報を含むタプルが1つのとき，その履修科目
を削除すると，住所等の情報が失われてしまう。
氏名
学科
住所
学年
科目名
成績
(d) 更新異常を起こさないために
科目，成績と，住所は本来関係がない。
学生の所属学科や住所の表と履修関係の表を分ける。
更新異常を起きないような関係に変形することを
正規化，そのような形を正規形という。
氏名 学科
住所
学年
氏名 科目名
成績
【用語】正規形：normal form, 正規化：normalize
(e) 正規形の種類
第１正規形
第２正規形
第３正規形
ＢＣＮＦ（Boyce-Codd正規形）
第４正規形
第５正規形
(f) 第１正規形
属性の定義域は構造を持たない単純な値で
あり，値の集合などであってはならない。
【第1正規形の例】
＜山本栄一，ソフトウェア工学＞
【第1正規形でない例】
＜山本栄一，{ソフトウェア工学，人工知能｝＞
(g) 複合定義域や集合を許す場合
① 複合値を１つの値と考えて，第1正規形を
満足させればよい。
② 集合の場合，１つのタプルを複数のタプル
に分けることで第1正規形にする。
第１正規形を満たさない関係を非正規関係と
いう。
２．関数従属
(a) 関数従属（ＦＤ：functional dependency）
関係Rのスキーマが与えられ，その属性集合XとYに対して，Xに
属する属性値が同一であれば，Yに対する属性の値もすべて同一
であるときYはXに関数従属するという。
（Xの値によりYの値が一意に定まるとき）
Xを決定項，Yを従属項といい X→Y と書く。
(b) 完全関数従属
YがXに関数従属し，
かつXの真部分集合（Xと同一でない部分集合）
には関数従属しないとき，
YがXに完全関数従属するという。
（注意）関数従属とキーの関係
候補キーの値が決まれば，タプルの値が決まる。
すべての属性は候補キーに関数従属する？
この場合，スキーマの性質として考えるべきであ
る。「ある時点の関係の内容でたまたま成立して
いる」と考えるべき。
関係の内容が更新されても常に成立しているとき
「関数従属する」という。
関数従属の例
誕生日と年齢
部品番号と部品名
学籍番号と氏名
(c) 関係の分解と結合
関係スキーマRの部分集合SとTにおいて，
SとTの和がRになり，
SとTに共通な属性が存在するとき，
SとTをRの分解という。
関係Rは，関係SとTの自然結合の部分集合となる
（一致ではないことに注意）
(d) 無損失分解
（information lossless decomposition）
関係Rを分解したSとTの自然結合が
Rに戻るような分解
３． 関数従属に基づく正規系
関数従属に用いて定義される属性は、以下の３種類
①第２正規形
②第３正規形
③Boyce- Codd 正規形
(a) 第２正規形
関係が第１正規形で、かつキーでない属性が各候補
キーに完全関数従属しているとき第２正規形という。
①住所は主キー（候補キー）「氏名」には、関数従属している。
②住所は、候補キーの「科目名」には、関数従属していない。
第２正規形ではない ⇒無損失分解によって第２正規形にする
氏名
学科
住所
学年
科目名
成績
(a) 第２正規形
関係が第１正規形で、かつキーでない属性が各候補
キーに完全関数従属しているとき第２正規形という。
①住所は主キー（候補キー）「氏名」には、関数従属している。
②住所は、候補キーの「科目名」には、関数従属していない。
第２正規形ではない
氏名
学科
住所
学年
科目名
成績
第２正規形にする
無損失分解によって第２正規形にする。
氏名
氏名 学科
学科
住所
住所
学年
学年
科目名
氏名 科目名
成績
成績
(b) 推移的関数従属
(1)例題とする表
発注部品番号が決まると、
部品名、発注業者名、発送地、発送連絡先が決まる。
発注
部品番号
部品名
発注
業者名
発送地
発注連絡先
D0012
DVDドライブ
北村電装
東京
03-3543-4568
E2114
電源パック
キャスパック
横浜
0463-92-9876
M3454
増設メモリ
システックス
名古屋
052-242-3321
H5213
ハードディスク
日装電子
大阪
06-6947-1234
（２）第２正規形で発生する操作異常
①削除操作における異常
「北村電装」を表から削除したとき、「北村電装」の発送地、連
絡先のデータがなくなってしまう。
②変更操作における異常
「キャスパック」の発送地が「名古屋」から「静岡」に変わったとき
複数の行を変更する必要がある。
発注
部品番号
部品名
発注
業者名
発送地
発注連絡先
D0012
DVDドライブ
北村電装
東京
03-3543-4568
E2114
電源パック
キャスパック
名古屋
052-242-3321
M3454
増設メモリ
キャスパック
名古屋
052-242-3321
H5213
ハードディスク
日装電子
大阪
06-6947-1234
なぜか？
間接的な隠れた従属性が隠れている。
発注
部品番号
部品名
発注
業者名
発送地
発注連絡先
D0012
DVDドライブ
北村電装
東京
03-3543-4568
E2114
電源パック
キャスパック
名古屋
052-242-3321
M3454
増設メモリ
キャスパック
名古屋
052-242-3321
H5213
ハードディスク
日装電子
大阪
06-6947-1234
（３）推移的関数従属性
Transitive Functional Dependency
間接的な隠れた従属性
＝従属性がある属性を経由して推移的な従属性がある。
【推移的関数従属性の定義】
関係Rの異なった属性または属性集合をX, Y, Z とするとき、
X→Y かつ not(Y→ X) で Y→Zのとき、
X→Z かつ not(Z→ X) が成り立つとき
X→Z を推移的従属性といい、
Z は X に推移的に従属する
(c) 第３正規形
【第３正規形が満たすべき条件】これを満たすよう分解
①関係Rは第２正規形である。
②関係Rの候補キーに含まれないすべての属性は、Rのいかなる
候補キーにも推移的に従属しない。
発注
部品番号
部品名
発注
業者名
D0012
DVDドライブ
北村電装
E2114
電源パック
キャスパック
M3454
増設メモリ
キャスパック
H5213
ハードディスク
日装電子
発注
業者名
発送地
発注連絡先
北村電装
東京
03-3543-4568
キャスパック
横浜
0463-92-9876
日装電子
大阪
06-6947-1234
(d) Boyce-Codd正規形
（１）例題とする表
配属された時点での配属履歴表
社員番号
部署名
責任者番号
561011
システム開発部
442123
561012
総合企画部
443256
552032
総務部
324001
552033
技術営業部
443020
（３）第３正規形の操作異常
①挿入操作における異常
新たに責任者番号「385007」を責任者として、「公共システム営
業部」を新設したので、表に挿入しようとしても社員がまだ配属され
ていないので社員番号をもつ社員は存在しない。また、部下なしの
部署名を挿入できない。
②削除操作における異常。
「総合企画」がたまたま部下１人、上司１人だとすると、社員番号
「561012」が退職したのでその行を削除しようとすると責任者番号
の情報が表から失われる。
社員番号
部署名
責任者番号
561011
システム開発
442123
561012
総合企画
443256
552032
総務
324001
552033
技術営業
443020
なぜか？
推移的な関数従属性と異なる循環的な従属関係が、主キーと他の
キーの間に存在する。
社員番号
部署名
責任者番号
561011
システム開発
442123
561012
総合企画
443256
552032
総務
324001
552033
技術営業
443020
（３）分解
①関係Rは第１正規形である。
②関数従属のすべての決定項は候補キーである。
上司表
部署責任者表
部署名
責任者番号
社員番号
責任者番号
561011
442123
システム開発
442123
561012
443256
総合企画
443256
552032
324001
総務
324001
552033
443020
技術営業
443020
または、配属表
社員番号
部署名
561011
システム開発
561012
総合企画
552032
総務
552033
技術営業
(e) 第４正規形
（１）結合のわな
表を分解し、結合することを考える
年別製造者番号表 A
製造者
製造
番号
年
部品管理表 P
部品
番号
製造者
番号
製造
年
128
33001
2010
128
33001
2012
128
51230
2010
128
51230
2012
256
51230
2011
33001
2010
33001
2012
51230
2010
51230
2012
51230
2011
A.製造者番号
=B.製造者番号
分解
部品製造者番号表 B
部品
製造者
番号
番号
128
33001
128
51230
256
51230
結合
幽霊組の発生
部品
番号
製造者
番号
製造
年
128
33001
2010
128
33001
2012
128
51230
2010
128
51230
2012
128
51230
2011
256
51230
2010
256
51230
2012
256
51230
2011
幽霊組が発生しないようにするには
分解方法を変える
部品製造年表 A
部品管理表 P
部品
番号
製造者
番号
製造
年
128
33001
2010
128
33001
2012
128
51230
2010
128
51230
2012
256
51230
2011
分解
部品
番号
製造
年
128
2010
128
2012
128
2010
128
2012
256
2011
部品製造者番号表 B
部品
製造者
番号
番号
128
33001
128
51230
256
51230
幽霊組は発生しない！
A.部品番号
=B.部品番号
結合
部品
番号
製造者
番号
製造
年
128
33001
2010
128
33001
2012
128
51230
2010
128
51230
2012
256
51230
2011
なぜか？
ひとつの部品番号128と製造者番号3301
または51230が決まると、複数の製造年
の値2010と2012が決まる。
多値従属の関係
年別製造者番号表 A
製造者
製造
番号
年
部品管理表 P
部品
番号
製造者
番号
製造
年
128
33001
2010
128
33001
2012
128
51230
2010
128
51230
2012
256
51230
2011
33001
2010
33001
2012
51230
2010
51230
2012
51230
2011
A.製造者番号
=B.製造者番号
分解
部品製造者番号表 B
部品
製造者
番号
番号
128
33001
128
51230
256
51230
結合
幽霊組の発生
部品
番号
製造者
番号
製造
年
128
33001
2010
128
33001
2012
128
51230
2010
128
51230
2012
128
51230
2011
256
51230
2010
256
51230
2012
256
51230
2011
（２）多値従属性の定義
属性Xの実現性が決まると属性Zが決まるとき、
属性Xに属性集合Yを加えて決まる属性が
同じ実現値をもつとき
属性Zは属性Xに多値従属という。
ひとつの部品番号の値
に対して多数の値が決
まる（多値従属）
部品番号に製造年の値を加えて
も同じ値が求まる。
部品番号
製造者番号
製造年
128
33001
2010
128
33001
2012
128
51230
2010
128
51230
2012
256
51230
2011
128は２つの製造者に発注し、
どの製造年から見ても製造業者は同じである。
互いに独立
（３）自明な多値従属性について
属性Xに多値従属する属性Yが空集合のとき
自明な多値従属という。
部品
番号
製造者
番号
製造
年
128
33001
2010
128
33001
2012
128
51230
2010
128
51230
2012
256
51230
2011
(製造者番号 = 51230 のみ)
幽霊要素が発生
(部品番号 = 128のみ)
自明な多値従属
製造年
製造年
:幽霊要素
2012
2012
2011
2011
2010
2010
128 256
製造者番号
33001 51230
製造者番号
（４）第４正規形
①関係Rは第１正規形である。
②関係Rには、自明な多値従属しか存在しない。
部品製造年表 A
部品管理表 P
部品
番号
製造者
番号
製造
年
128
33001
2010
128
33001
2012
128
51230
2010
128
51230
2012
256
51230
2011
部品
番号
製造
年
128
2010
128
2012
128
2010
128
2012
256
2011
第4正規形
部品製造者番号表 B
部品
製造者
番号
番号
128
33001
128
51230
256
51230
ひとつの要素が欠如すると幽霊要素が
発生する場合がある。
(f) 第５正規形
（１）結合のわな
部品製造年表 A
部品管理表 P
部品
番号
製造者
番号
製造
年
128
33001
2010
128
33001
2012
128
51230
2010
256
51230
2011
部品
番号
製造
年
128
2010
128
2012
128
2010
256
2011
幽霊組が発生！
A.部品番号
=B.部品番号
分解
部品製造者番号表 B
部品
製造者
番号
番号
128
33001
128
51230
256
51230
結合
部品
番号
製造者
番号
製造
年
128
33001
2010
128
33001
2012
128
51230
2010
128
51230
2012
256
51230
2011
（２）結合属性の定義
関係Rを射影（分解）してできた
関係S1, S2,・・・, Sn を用い、
自然な結合を行うと、
元の関Rが復元されるとき、
関係S1, S2,・・・, Sn は
結合従属にあるという。
結合従属関係にある表を「結合のわな」が生じないよう
分解する工夫を行う。
（３）第５正規形
①関係Rは第１正規形である。
②結合従属にある他の関係との自然な結合によって、元の関係が復元できる。
部品管理表 P
射影： {部品番号，製造者番号｝
部品番号
製造者番号
製造年
128
33001
2010
128
33001
2012
128
51230
2010
256
51230
2011
射影： {部品番号，製造者年｝
分解
部品製造者番号表 A
部品
製造者
番号
番号
射影： {製造者番号，製造年｝
分解
部品製造年表 B
部品
製造年
番号
製造者製造年表 C
製造者
番号
製造
年
128
33001
128
2010
128
2010
128
51230
128
2012
128
2012
256
51230
256
2011
128
2010
256
2011
元の関係を復元
結合
部品製造者番号表 A
部品
製造者
番号
番号
部品製造年表 B
部品
製造年
番号
製造者製造年表 C
128
33001
128
2010
製造者
番号
製造
年
128
51230
128
2012
128
2010
256
51230
256
2011
128
2012
128
2010
256
2011
結合：A [部品番号 = 部品番号] B
部品番号
製造者番号
製造年
128
33001
2010
128
33001
2012
128
51230
2010
256
51230
2011
結合：A [ 製造者番号 + 製造年 =
製造者番号 + 製造年] C
部品番号
製造者番号
製造年
128
33001
2010
128
33001
2012
128
51230
2010
256
51230
2011
(g) データ要素そのものについて
あるデータ要素から別のデータ要素を導くことができる場合、
①時系列で変化しないデータ要素
② 実現値の元の数が多いデータ要素
③ より本質的なデータ要素
を選択する。
勤務年数と入社年月日
年齢と誕生日
成績評価とテスト点数
人口密度と人口，面積
→
→
→
→
入社年月日
誕生日
テスト点数
人口，面積
(h) まとめ
①以下の順に条件が強くなる。
第１正規形→第２正規形→第３正規形
→BCNF→第４正規形→第５正規形
② 一般に強い正規形に変形すると更新以上が発生する可能性
が減少するが、検索効率が低下する。
③ どのレベルまで正規化を適用するかは、更新異常の防止と
検索効率のトレードオフである。
第１正規形
第２正規形
第３正規形
BCNF
第４正規形
第５正規形