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