データベース12: 正規化(2/2) - データ従属性に基づく正規化

1. 関数従属性
2. 関数従属性にもとづく関係の正規化
3. 望ましい関係スキーマを得る手順

講義ノートURL
https://dbnote.hontolab.org/content/db-design/02.html

Transcript

1. 正規化（2/2） ⼭本 祐輔 名古屋市⽴⼤学 データサイエンス学部 yusuke_yamamoto@acm.org 第12回 データベース 2024年7月8日 〜 データ従属性にもとづく正規化
2. 講義ノート https://bit.ly/3xqTSds
3. 情報無損失分解 購買ID ユーザ 購入商品 単価 数量 T1 U1 はーいお茶 130 3 T2 U1 きのこの里 230 2 T3 U2 のど飴 170 1 T4 U2 きのこの里 230 1 T5 U3 はーいお茶 130 1 T6 U4 タケノコの山 200 1 購買ID ユーザ 購入商品 数量 T1 U1 はーいお茶 3 T2 U1 きのこの里 2 T3 U2 のど飴 1 T4 U2 きのこの里 1 T5 U3 はーいお茶 1 T6 U4 タケノコの山 1 購入商品 単価 はーいお茶 130 きのこの里 230 のど飴 170 タケノコの山 200 ⋈ 購⼊商品 = ある関係を情報を失うことなく2つの関係に分解 内部結合によって元テーブルを 復元できる分解が可能 情報無損失分解によってDB更新の影響を極⼒⼩さくできる
4. 情報 損失 分解（1/2） 購買ID ユーザ T1 U1 T2 U1 T3 U2 T4 U2 T5 U3 T6 U4 購買1 ユーザ 購入商品 単価 数量 U1 はーいお茶 130 3 U1 きのこの里 230 2 U2 のど飴 170 1 U2 きのこの里 230 1 U3 はーいお茶 130 1 U4 タケノコの山 200 1 購買2 ⋈ ユーザ SELECT * FROM 購買1 INNER JOIN 購買2 USING (ユーザ);
5. 情報 損失 分解（2/2） 購買ID ユーザ 購入商品 単価 数量 T1 U1 はーいお茶 130 3 T1 U1 きのこの里 230 2 T2 U1 はーいお茶 130 3 T2 U1 きのこの里 230 2 T3 U2 のど飴 130 1 T3 U2 きのこの里 230 1 T4 U2 のど飴 130 1 T4 U2 きのこの里 230 1 T5 U3 はーいお茶 130 1 T6 U4 タケノコの山 200 1 購買1 と 購買2 を 「ユーザ」で結合した表 関係「購買」には なかったレコード が増えている 分解の軸となった「ユーザ」は、その値決まっても それに対応する値（購入商品など）が一意に決まらない 失敗要因
6. 情報 損失 分解（2/2） 購買ID ユーザ 購入商品 単価 数量 T1 U1 はーいお茶 130 3 T1 U1 きのこの里 230 2 T2 U1 はーいお茶 130 3 T2 U1 きのこの里 230 2 T3 U2 のど飴 130 1 T3 U2 きのこの里 230 1 T4 U2 のど飴 130 1 T4 U2 きのこの里 230 1 T5 U3 はーいお茶 130 1 T6 U4 タケノコの山 200 1 購買1 と 購買2 を 「ユーザ」で結合した表 関係「購買」には なかったレコード が増えている 分解の軸となった「ユーザ」は、その値決まっても それに対応する値（購入商品など）が一意に決まらない 失敗要因 どうすれば情報無損失分解できる？
7. 情報無損失分解 関数従属性に着目すれば情報無損失分解が可能に!! 関数従属性
8. 関数従属性 1 Functional dependency
9. データ従属性（復習） データ従属性 関数従属性 多値従属性 結合従属性 データ間に成⽴する制約
10. データ従属性（復習） データ従属性 関数従属性 多値従属性 結合従属性 データ間に成⽴する制約 属性Xの値が決まると 属性Yの値も⼀意に決まる制約 例1 住所 郵便番号 都道府県 郵便番号 例2
11. 例 ユーザ 連絡先 商品 メーカー 営業担当 営業成績 kawasumi kawa@... ソファベッド A社 岡崎 A kawasumi kawa@... 本棚(大) B社 豊田 S yamabata yb@... 観葉植物（大） C社 春日井 C tanabee tanab@.. オフィスデスク D社 尾張 A tanabee tanab@.. 本棚（大） B社 西尾 B north1000 kita@.. ロッカー大 C社 豊田 S 営業記録 … 営業記録 ( ユーザ, 商品 , 連絡先, メーカー, 営業担当, 営業成績 )
12. 例 ユーザ 連絡先 商品 メーカー 営業担当 営業成績 kawasumi kawa@... ソファベッド A社 岡崎 A kawasumi kawa@... 本棚(大) B社 豊田 S yamabata yb@... 観葉植物（大） C社 春日井 C tanabee tanab@.. オフィスデスク D社 尾張 A tanabee tanab@.. 本棚（大） B社 西尾 B north1000 kita@.. ロッカー大 C社 豊田 S 営業記録 … FD1 : ユーザ, 商品 → 営業担当 FD2 : ユーザ→ 連絡先 FD3 : 商品 → メーカー FD4 : 営業担当 → 営業成績 この関係スキーマ上が保持する関数従属性 関数従属性は対象世界に存在する制約として定められる 営業記録 ( ユーザ, 商品 , 連絡先, メーカー, 営業担当, 営業成績 )
13. FDダイアグラム: 関数従属性（と属性）を可視化した図 FD1 : ユーザ, 商品 → 営業担当 FD2 : ユーザ→ 連絡先 FD3 : 商品 → メーカー FD4 : 営業担当 → 営業成績 商品 ユーザ 連絡先 営業担当 営業成績 メーカー FD4 FD2 FD3 FD1 FDダイアグラム化 従属性の左辺が集合 の場合は丸で囲む
14. 補⾜：FDダイアグラムと関係スキーマの対応 商品 ユーザ 連絡先 営業担当 営業成績 メーカー FD4 FD2 FD3 FD1 営業記録 ( ユーザ, 商品 , 連絡先, メーカー, 営業担当, 営業成績 ) ユーザ 連絡先 商品 メーカー 営業担当 営業成績 kawasumi kawa@... ソファベッド A社 岡崎 A kawasumi kawa@... 本棚(大) B社 豊田 S yamabata yb@... 観葉植物（大） C社 春日井 C …
15. Q1: 主キー Q. サブスクリプション型⾳楽ストリーミングサービス Orange Musicでは，楽曲に対するユーザの評価スコア を関係「評価」で管理している． 関係「評価」のス キーマは以下の通りである． 評価 ( ユーザ, 楽曲, アーティスト, 収録アルバム, ジャンル, スコア ) FD1 : ユーザ, 楽曲 → スコア FD2 : 楽曲 → 収録アルバム FD3 : 楽曲 → ジャンル FD4 : 収録アルバム → アーティスト 関係「評価」において 主キーと考えられる属性は何か？
16. Q1: 主キー A. サブスクリプション型⾳楽ストリーミングサービス Orange Musicでは，楽曲に対するユーザの評価スコア を関係「評価」で管理している． 関係「評価」のス キーマは以下の通りである． 主キーは { ユーザ, 楽曲 } 評価 ( ユーザ, 楽曲, アーティスト, 収録アルバム, ジャンル, スコア ) FD1 : ユーザ, 楽曲 → スコア FD2 : 楽曲 → 収録アルバム FD3 : 楽曲 → ジャンル FD4 : 収録アルバム → アーティスト
17. Q2: FDダイアグラム Q. 関係「評価」における以下の4つの関数従属性を FDダイアグラムを⽤いて表現せよ． FD1 : ユーザ, 楽曲 → スコア FD2 : 楽曲 → 収録アルバム FD3 : 楽曲 → ジャンル FD4 : 収録アルバム → アーティスト
18. Q2: FDダイアグラム Q. 関係「評価」における以下の4つの関数従属性を FDダイアグラムを⽤いて表現せよ． FD1 : ユーザ, 楽曲 → スコア FD2 : 楽曲 → 収録アルバム FD3 : 楽曲 → ジャンル FD4 : 収録アルバム → アーティスト A. ユーザ 楽曲 収録アルバム スコア アーティスト FD2 FD3 FD1 ジャンル FD4
19. 関数従属性にもとづく正規化 2 Normalization using functional dependency
20. データに冗⻑性がない ⼀貫性制約を保持するのが容易 + 正規化された関係スキーマ 正規化された関係スキーマをつくるのが関係データベース設計の⽬標 （テーブルの中で保持する制約がシンプルになるほど扱いやすい）
21. 第1正規形 第2正規形 第3正規形 ボイス・コッド正規形 第4正規形 第5正規形 正規化にはレベル分けがある ⾮正規形 関数従属性を使えばボイス・コッド正規形まで導ける 正規化の前後でデータの内容や⼀貫性制約は失われてはいけない
22. キーの概念の復習（1/4） 超キー 関係Rにおける属性集合のうち，それらの属性値が決まれば 関係Rのタプルを⼀意に特定できるもの 学籍番号 氏名 学部 年齢 S1 川澄 桜 経済学部 23 S2 山畑 滝子 文学部 20 S3 田辺 通 薬学部 18 S4 田辺 瑞穂 DS学部 19 S5 山畑 川名 DS学部 27 学⽣ {学籍番号} {学籍番号, ⽒名} {学籍番号, ⽒名, 学部} … 超キーは複数ある
23. キー（候補キー） 関係Rにおける属性集合のうち，それらの属性値が決まれば 関係Rのタプルを⼀意に特定できるものうち，極⼩なもの 学籍番号 氏名 学部 年齢 S1 川澄 桜 経済学部 23 S2 山畑 滝子 文学部 20 S3 田辺 通 薬学部 18 S4 田辺 瑞穂 DS学部 19 S5 山畑 川名 DS学部 27 学⽣ 最も⼩さい部分集合 {学籍番号} {学籍番号, ⽒名} {学籍番号, ⽒名, 学部} … キーは「学籍番号」 キーの概念の復習（2/4）
24. キーの概念の復習（3/4） キー（候補キー） 関係Rにおける属性集合のうち，それらの属性値が決まれば 関係Rのタプルを⼀意に特定できるものうち，極⼩なもの 学籍番号 Passport番号 氏名 学部 S1 TR1234xxx 川澄 桜 経済学部 S2 XS4321xxx 山畑 滝子 文学部 S3 KK7743xxx 田辺 通 薬学部 S4 VU9802xxx 田辺 瑞穂 DS学部 S5 AB5230xxx 山畑 川名 DS学部 学⽣ キーは複数あり得る キー = 学籍番号 Passport番号 or
25. キーの概念の復習（4/4） 主キー 候補キーのうち，値として未定義や空値（NULL値）を取る 可能性がなく，かつ運⽤上最も都合のよいキーの1つ 学籍番号 Passport番号 氏名 学部 S1 TR1234xxx 川澄 桜 経済学部 S2 XS4321xxx 山畑 滝子 文学部 S3 KK7743xxx 田辺 通 薬学部 S4 VU9802xxx 田辺 瑞穂 DS学部 S5 AB5230xxx 山畑 川名 DS学部 学⽣ パスポートを 持っていない人もいそう 主キーは 「学籍番号」
26. 第1正規形 第2正規形 第3正規形 ボイス・コッド正規形 正規化にはレベル分けがある ⾮正規形 本講義の⽬標はボイス・コッド正規形まで
27. 商品 ユーザ 営業 担当 ソファベッド kawasumi 岡崎 本棚(大) kawasumi 豊田 本棚（大） tanabee 西尾 ロッカー大 north1000 豊田 … ボイス・コッド正規形 関係スキーマRにおいて成⽴する⾃明でない どの関数従属性X→Y においても § X が R の超キーである とき、Rはボイス・コッド正規形であるという 商品 ユーザ 営業担当 FD1 R ( 商品, ユーザ , 営業担当 ) ボイス・コッド正規形の例 FD1 の左辺である{商品, ユーザ}はRのキーそのもの
28. ボイス・コッド正規形ではない例（1/2） 商品 ユーザ 営業担当 営業成績 FD4 FD1 R ( 商品, ユーザ , 営業担当, 営業成績 ) FD4 の左辺である { 営業担当 } が関係Rのキーではない 理由 （FD1 はボイス・コッド正規形の条件を満たしている） {商品, ユーザ} は関係R のキー
29. ボイス・コッド正規形ではない例（2/2） R ( 商品, ユーザ , メーカー, 営業担当, 連絡先 ) - FD2 の左辺である { ユーザ } がキーではない 理由 {商品, ユーザ} は関係R のキー 商品 ユーザ 連絡先 営業担当 メーカー FD2 FD3 FD1 - FD3 の左辺である { 商品 } がキーではない
30. ボイス・コッド正規形 関係スキーマRにおいて成⽴する⾃明でない どの関数従属性X→Y においても § X が R の超キーである とき、Rはボイス・コッド正規形であるという 商品 ユーザ 営業担当 FD1 R ( 商品, ユーザ, 営業担当 ) ボイス・コッド正規形の例 商品 ユーザ 営業 担当 ソファベッド kawasumi 岡崎 本棚(大) kawasumi 豊田 本棚（大） tanabee 西尾 ロッカー大 north1000 豊田 … BCNFはすべてのFDがキー出発のものに限定している
31. ボイス・コッド正規形 関係スキーマRにおいて成⽴する⾃明でない どの関数従属性X→Y においても § X が R の超キーである とき、Rはボイス・コッド正規形であるという 商品 ユーザ 営業担当 FD1 R ( 商品, ユーザ, 営業担当 ) ボイス・コッド正規形の例 商品 ユーザ 営業 担当 ソファベッド kawasumi 岡崎 本棚(大) kawasumi 豊田 本棚（大） tanabee 西尾 ロッカー大 north1000 豊田 … ボイス・コッド正規形は、 関係上のあらゆる関数従属性の左辺を 候補キーの上位集合に限定することで、 関数従属性に関するあらゆる冗長性を 排除している
32. 関数従属性による情報無損失分解 属性集合U上の関係Rにおいて、関数従属性X→Y が 成⽴するならば、関係Rは § 関係R を属性集合X ∪ Y 上に射影した関係R1 の2つの関係に情報無損失分解できる 上記定理を⽤いれば、関係の正規化ができる § 関係R を属性集合X ∪ (U – Y ) 上に射影した関係R2 定理
33. 例：関数従属性による情報無損失分解 購買 ( 購買ID , ユーザ, 購⼊商品, 単価, 数量) FD: 購⼊商品 → 単価 購買ID ユーザ 購入商品 単価 数量 T1 U1 はーいお茶 130 3 T2 U1 きのこの里 230 2 T4 U2 きのこの里 230 1 T5 U3 はーいお茶 130 1 T6 U4 タケノコの山 200 1 購買
34. 例：関数従属性による情報無損失分解（1/2） 購買 ( 購買ID , ユーザ, 購⼊商品, 単価, 数量) FD: 購⼊商品 → 単価 U = { 購買ID, ユーザ, 購⼊商品, 単価, 数量 } X = 購⼊商品 Y = 単価 X ∪ Y = { 購⼊商品, 単価 } U − Y = {購買ID, ユーザ, 購⼊商品, 数量 } X ∪ (U−Y) = {購買ID, ユーザ, 購⼊商品, 数量 } 定理に当てはめる
35. 例：関数従属性による情報無損失分解（2/2） 購買 ( 購買ID , ユーザ, 購⼊商品, 単価, 数量) FD: 購⼊商品 → 単価 情報無損失分解 R2 ( 購買ID , ユーザ, 購⼊商品, 数量) R1 ( 購⼊商品 , 数量) FD: 購⼊商品 → 単価 FDの両辺にある要素 からなる関係 元の属性集合から FDの右辺を除いた属性からなる関係
36. 分解法による関係の正規化 1. Rが⾮正規形の場合，第1正規形に変換 分解法 関係Rの属性集合と R上の関数従属性を⽤いて、 Rをボイス・コッド正規形に⾄るまで分解する⼿法 2. いずれかの関数従属性に着⽬ 3. ⼿順2で選んだ関数従属性を使って 関係Rを情報無損失分解 4. ⼿順3で得られた関係がボイス・コッド正規形 （BCNF）になるまで⼿順2-3を繰り返す ⼿順 FDダイアグラム端にある (BCNFの条件を邪魔する) FDから着⼿するのがコツ
37. 分解法の適⽤例（1/5） 営業記録 ( ユーザ, 商品, 連絡先, メーカー, 営業担当, 営業成績 ) FD1 : ユーザ, 商品 → 営業担当 FD2 : ユーザ→ 連絡先 FD3 : 商品 → メーカー FD4 : 営業担当 → 営業成績 関係「営業記録」は第1正規形 (1NF)… 商品 ユーザ 連絡先 営業担当 営業成績 メーカー FD4 FD2 FD3 FD1 キー
38. 分解法の適⽤例（2/5） 商品 ユーザ 連絡先 営業担当 営業成績 メーカー FD4 FD2 FD3 FD1 1NF 営業担当 営業成績 FD4 商品 ユーザ 連絡先 営業担当 メーカー FD2 FD3 FD1 BCNF 1NF FD4 で分解 次の分解ターゲット
39. 分解法の適⽤例（3/5） 商品 ユーザ 連絡先 営業担当 メーカー FD2 FD3 FD1 1NF ユーザ 連絡先 FD2 BCNF 商品 ユーザ 営業担当 メーカー FD3 FD1 1NF FD2 で分解 次の分解ターゲット
40. 分解法の適⽤例（4/5） 1NF 商品 メーカー FD3 BCNF 商品 ユーザ 営業担当 メーカー FD3 FD1 BCNF FD3 で分解 商品 ユーザ 営業担当 FD1
41. 分解法の適⽤例（5/5） 営業担当 営業成績 FD4 BCNF ユーザ 連絡先 FD2 BCNF 商品 メーカー FD3 BCNF BCNF 商品 ユーザ 営業担当 FD1 R1 ( 営業担当 , 営業成績 ) FD4 : 営業担当 → 営業成績 R3 ( 商品 , メーカー ) FD3 : 商品 → メーカー R2 ( ユーザ , 連絡先 ) FD4 : ユーザ → 連絡先 R4 ( 商品, ユーザ , 営業担当 ) FD1 : 商品, ユーザ→営業担当 FDを保存しつつ更新時異状の影響を最⼩化するスキーマを導出!
42. Q3: 関数従属性にもとづく正規化 Q. 下記FDダイアグラムを⽤いて、 関係スキーマ「評価」をBCNFへ分解せよ． なお、分解過程と正規化レベルも⽰すこと． ユーザ 楽曲 収録アルバム スコア FD2 FD3 FD1 ジャンル FD4 アーティスト
43. A3: 関数従属性にもとづく正規化 (1/4) 1NF 収録 アルバム アーティスト FD4 BCNF 1NF FD4 で分解 次の分解ターゲット ユーザ 楽曲 収録アルバム スコア FD2 FD3 FD1 ジャンル FD4 アーティスト ユーザ 楽曲 スコア FD2 FD3 FD1 ジャンル 収録 アルバム
44. A3: 関数従属性にもとづく正規化 (2/4) 楽曲 収録アルバム FD2 BCNF 1NF FD2 で分解 次の分解ターゲット ユーザ 楽曲 スコア FD3 FD1 ジャンル 1NF ユーザ 楽曲 スコア FD2 FD3 FD1 ジャンル 収録 アルバム
45. A3: 関数従属性にもとづく正規化 (3/4) 楽曲 ジャンル FD3 BCNF BCNF FD3 で分解 ユーザ 楽曲 スコア FD1 1NF ユーザ 楽曲 スコア FD3 FD1 ジャンル
46. A3: 関数従属性にもとづく正規化 (4/4) 楽曲 収録アルバム FD2 BCNF 楽曲 ジャンル FD3 BCNF 収録 アルバム アーティスト FD4 BCNF BCNF ユーザ 楽曲 スコア FD1 R1 ( 楽曲 , 収録アルバム ) FD2 : 楽曲 → 収録アルバム R3 ( 収録アルバム , アーティスト ) FD4 : 収録アルバム → アーティスト R2 ( 楽曲 , ジャンル ) FD3 : 楽曲 → ジャンル R4 ( ユーザ, 楽曲 , スコア ) FD1 : ユーザ, 楽曲→スコア
47. Q4: 分解⼿順と結果の関係 (1/3) Q. 関係「営業記録」に関する下記FDダイアグラムを ⽤いて、関係「営業記録」をBCNFへ分解せよ． ただし，分解法はFD2 ，FD3 ，FD4 の順で適⽤せよ． なお、分解過程と正規化レベルも⽰すこと． 商品 ユーザ 連絡先 営業担当 営業成績 メーカー FD4 FD2 FD3 FD1
48. A4: 分解⼿順と結果の関係 (1/3) 商品 ユーザ 連絡先 営業担当 営業成績 メーカー FD4 FD2 FD3 FD1 1NF ユーザ 連絡先 FD2 BCNF 1NF FD2 で分解 次の分解ターゲット 商品 ユーザ 営業 担当 営業 成績 メーカー FD4 FD3 FD1
49. A4: 分解⼿順と結果の関係 (2/3) 商品 メーカー FD3 BCNF 第2正規形 (2NF) FD3 で分解 商品 ユーザ 営業 担当 営業 成績 FD4 FD1 1NF 商品 ユーザ 営業 担当 営業 成績 メーカー FD4 FD3 FD1
50. 第2正規形 関係スキーマRにおいて成⽴する⾃明でない どの関数従属性 X → Y においても 条件1. X が R の超キーである のいずれかを満たすとき、Rは第2正規形である 第2正規形の例 条件2. Y が R のキー属性である 条件3. X が R のキーの部分集合でない 商品 ユーザ 営業担当 営業成績 FD4 FD1 2NFはBCNFの中間⽣成物なので重要性が低い キーから関数従属性 をたどれば， すべての属性が決まる
51. A4: 分解⼿順と結果の関係 (3/3) 営業 担当 営業 成績 FD4 BCNF FD4 で分解 商品 ユーザ 営業 担当 FD1 2NF 商品 ユーザ 営業 担当 営業 成績 FD4 FD1 BCNF
52. 分解法の適⽤例（5/5） 営業担当 営業成績 FD4 BCNF 営業担当 営業成績 FD4 BCNF 商品 メーカー FD3 BCNF BCNF 商品 ユーザ 営業担当 FD1 R1 ( 営業担当 , 営業成績 ) FD4 : 営業担当 → 営業成績 R3 ( 商品 , メーカー ) FD3 : 商品 → メーカー R2 ( 営業担当 , 営業成績 ) FD4 : 営業担当 → 営業成績 R4 ( 商品, ユーザ , 営業担当 ) FD1 : 商品, ユーザ→営業担当 FDを保存しつつ更新時異状の影響を最⼩化するスキーマを導出!! 実際にはすべての関係を ボイス・コッド正規形に導けないことも…
53. ボイス・コッド正規形まで分解できない例（1/5） 営業記録 ( ユーザ, 商品, 連絡先, メーカー, 営業担当, 営業成績 ) FD1 : ユーザ, 商品 → 営業担当 FD2 : ユーザ→ 連絡先 FD3 : 商品 → メーカー FD4 : 営業担当 → 営業成績 商品 ユーザ 連絡先 営業担当 営業成績 メーカー FD4 FD2 FD3 FD1 FD5 : 営業担当 → 商品 FD5 営業担当ごとに担当商品が決まっているのは⾃然な制約
54. ボイス・コッド正規形まで分解できない例（2/5） 商品 ユーザ 連絡先 営業担当 営業成績 メーカー FD4 FD2 FD3 FD1 1NF 営業担当 営業成績 FD4 商品 ユーザ 連絡先 営業担当 メーカー FD2 FD3 FD1 BCNF 1NF FD4 で分解 次の分解ターゲット FD5 FD5
55. ボイス・コッド正規形まで分解できない例（3/5） 1NF ユーザ 連絡先 FD2 BCNF 1NF FD2 で分解 次の分解ターゲット 商品 ユーザ 連絡先 営業担当 メーカー FD2 FD3 FD1 FD5 商品 ユーザ 営業担当 メーカー FD3 FD1 FD5
56. ボイス・コッド正規形まで分解できない例（4/5） 1NF 商品 メーカー FD3 BCNF FD3 で分解 商品 ユーザ 営業担当 FD1 商品 ユーザ 営業担当 メーカー FD3 FD1 FD5 FD5
57. これはボイス・コッド正規形？ 商品 ユーザ 営業担当 FD1 FD5 FD5の左辺がこの関係における超キーでない… BCNFの定義 関係スキーマRにおいて成⽴する⾃明でない どの関数従属性X→Y においても § X が R の超キーである とき、Rはボイス・コッド正規形であるという
58. 情報無損失分解の定理を適⽤すると 商品 ユーザ 営業担当 FD1 FD5 営業担当 商品 FD5 BCNF 営業担当 ユーザ BCNF （関数従属性なし） FD5 で分解 情報無損失分解できてもFD情報が失われるのはマズい… FD1 が失われてしまった!!
59. ボイス・コッド正規形（復習） 関係スキーマRにおいて成⽴する⾃明でない どの関数従属性X→Y においても § X が R の超キーである とき、Rはボイス・コッド正規形である 商品 ユーザ 営業担当 FD1 ボイス・コッド正規形の例
60. 第3正規形の導⼊ 関係スキーマRにおいて成⽴する⾃明でない どの関数従属性 X → Y においても 条件1. X が R の超キーである のいずれかを満たすとき、Rは第3正規形である 第3正規形の例 条件2. Y が R の候補キーの要素である 追加のOR条件 商品 ユーザ 営業担当 FD1 FD5 条件2に相当 条件1に相当 ボイス・コッド正規形の例 商品 ユーザ 営業担当 FD1 条件1に相当
61. ボイス・コッド正規形まで分解できない例（5/5） 1NF 商品 メーカー FD3 BCNF FD3 で分解 商品 ユーザ 営業担当 FD1 商品 ユーザ 営業担当 メーカー FD3 FD1 FD5 FD5 第3正規形(3NF)
62. ボイス・コッド正規形まで分解できない例（5/5） 営業担当 営業成績 FD4 BCNF ユーザ 連絡先 FD2 BCNF 商品 メーカー FD3 BCNF 3NF 商品 ユーザ 営業担当 FD1 R1 ( 営業担当 , 営業成績 ) FD4 : 営業担当 → 営業成績 R3 ( 商品 , メーカー ) FD3 : 商品 → メーカー R2 ( ユーザ , 連絡先 ) FD4 : ユーザ → 連絡先 R4 ( 商品, ユーザ , 営業担当 ) FD1 : 商品, ユーザ→営業担当 FD5 FD5 : 営業担当 → 商品
63. Q5: 関数従属性にもとづく正規化2 Q. 関係スキーマR およびその関数従属性が与えられ たとき， R から3NFもしくはBCNFを導出せよ． なお，途中過程も⽰すこと． また，分解の過程で 得られる関係スキーマに正規化レベルを付与せよ． FD1 : A, B → C FD2 : C → B FD3 : C → D FD4 : C → E FD5 : E → F R (A, B , C, D, E, F)
64. A4: 関数従属性にもとづく正規化2 A B E C D FD4 FD2 FD5 FD1 2NF D F FD5 BCNF FD5 で分解 次の分解ターゲット FD3 F A B E C D FD4 FD2 FD1 2NF FD3
65. A4: 関数従属性にもとづく正規化2 C E FD4 BCNF FD4 で分解 次の分解ターゲット A B C D FD2 FD1 2NF FD3 A B E C D FD4 FD2 FD1 2NF FD3
66. A4: 関数従属性にもとづく正規化2 C D FD3 BCNF FD3 で分解 A B C FD2 FD1 3NF A B C D FD2 FD1 2NF FD3
67. A4: 関数従属性にもとづく正規化2 D F FD5 BCNF C E FD4 BCNF C D FD3 BCNF 3NF A B C FD1 R1 ( D , F ) FD4 : D → F R3 ( C , D ) FD3 : C → D R2 ( C , E ) FD4 : C→ E R4 ( A, B, C ) FD1 : A, B → C FD2 : C → B FD2
68. Q6: 関数従属性にもとづく正規化3 Q. 下記のような関係スキーマR およびその関数従属性が 与えられたとき， R から3NFもしくはBCNFを導出せ よ． なお，途中過程も⽰すこと． また，分解の過程 で得られる関係スキーマに正規化レベルを付与せよ． あるスポーツ団体では，以下の関係スキーマRとその 関数従属性に基づき．団体に属する選⼿やチームに 関する情報を管理する関係データベースを設計しよう としている． FD1 : 選⼿ → 出⾝校 FD2 : チーム → チーム創⽴年 FD3 : チーム, 年度 → 監督 FD4 : 選⼿, 年度 → チーム R (選⼿, 年度 , 出⾝校, チーム, 監督, チーム創⽴年)
69. A5: 関数従属性にもとづく正規化3 FD4 FD2 FD1 FD3 選⼿ 年度 チーム 出⾝校 チーム 創⽴年 監督 1NF FD2 チーム チーム 創⽴年 FD4 FD1 FD3 選⼿ 年度 チーム 出⾝校 監督 1NF BCNF FD2 で分解
70. A5: 関数従属性にもとづく正規化3 FD1 選⼿ 出⾝校 FD4 FD1 FD3 選⼿ 年度 チーム 出⾝校 監督 1NF BCNF FD1 で分解 FD4 FD3 選⼿ 年度 チーム 監督 2NF
71. A5: 関数従属性にもとづく正規化3 FD3 年度 監督 BCNF FD3 で分解 FD4 FD3 選⼿ 年度 チーム 監督 2NF チーム FD4 選⼿ 年度 チーム BCNF
72. A5: 関数従属性にもとづく正規化3 チーム チーム 創⽴年 FD2 BCNF 選⼿ 出⾝校 FD1 BCNF R1 ( チーム , チーム創⽴年 ) FD2 : チーム → チーム創⽴年 R2 ( 選⼿ , 出⾝校 ) FD1 : 選⼿ → 出⾝校 FD3 年度 監督 BCNF チーム FD4 選⼿ 年度 チーム BCNF R3 ( チーム, 年度 , 監督 ) FD3 : チーム, 年度 → 監督 R4 ( 選⼿, 年度 , チーム ) FD4 : 選⼿, 年度 → チーム
73. 望ましいスキーマを得る手順 3 Procedure to obtain ideal relational schema
74. 望ましい関係スキーマを導出する⼿順まとめ 概念 モデリング 実体関連図 関係スキーマ (3NF or BCNF) FDダイアグラム スキーマ変換 + 正規化 直接スキーマ変換 実体関連モデルとFDダイアグラムの併用がオススメ 情報無損失分解 データの意味/構造を捉える 属性間の制約を捉える 全属性集合をもつ 1NFを分解
75. 第1正規形 第2正規形 第3正規形 ボイス・コッド正規形 第4正規形 第5正規形 正規化のレベル ⾮正規形 ⼀般には3NF or BCNFまで正規化できればOK (多値従属性による分解で導出) (結合従属性による分解で導出)
76. 回 実施日 トピック 1 04/15 ガイダンス：データベースを使わない世界 2 04/22 データベースの概念 3 04/29（祝） 関係データモデル 4 05/13 SQL (1/3) 5 05/20 SQL (2/3) 6 05/27 SQL (3/3) 7 06/03 SQL演習 – レポート課題1 8 06/10 実体関連モデル (1/3) 9 06/17 実体関連モデル (2/3) 10 06/24 実体関連モデル (3/3) 11 07/01 正規化 (1/2) 12 07/08 正規化 (2/2) 13 07/15（祝） データベース設計演習 – レポート課題2 14 07/22 索引付け 15 07/29 NoSQL 16 08/05 期末試験 今後の予定 76