Tweet
Tweet

Slide 1

Slide 1 text

正規化（2/2） ⼭本 祐輔 名古屋市⽴⼤学 データサイエンス学部 [email protected] 第12回 データベース 2024年7月8日 〜 データ従属性にもとづく正規化

Slide 2

Slide 2 text

講義ノート https://bit.ly/3xqTSds

Slide 3

Slide 3 text

情報無損失分解 購買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更新の影響を極⼒⼩さくできる

Slide 4

Slide 4 text

情報 損失 分解（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 (ユーザ);

Slide 5

Slide 5 text

情報 損失 分解（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 を 「ユーザ」で結合した表 関係「購買」には なかったレコード が増えている 分解の軸となった「ユーザ」は、その値決まっても それに対応する値（購入商品など）が一意に決まらない 失敗要因

Slide 6

Slide 6 text

情報 損失 分解（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 を 「ユーザ」で結合した表 関係「購買」には なかったレコード が増えている 分解の軸となった「ユーザ」は、その値決まっても それに対応する値（購入商品など）が一意に決まらない 失敗要因 どうすれば情報無損失分解できる？

Slide 7

Slide 7 text

情報無損失分解 関数従属性に着目すれば情報無損失分解が可能に!! 関数従属性

Slide 8

Slide 8 text

関数従属性 1 Functional dependency

Slide 9

Slide 9 text

データ従属性（復習） データ従属性 関数従属性 多値従属性 結合従属性 データ間に成⽴する制約

Slide 10

Slide 10 text

データ従属性（復習） データ従属性 関数従属性 多値従属性 結合従属性 データ間に成⽴する制約 属性Xの値が決まると 属性Yの値も⼀意に決まる制約 例1 住所 郵便番号 都道府県 郵便番号 例2

Slide 11

Slide 11 text

例 ユーザ 連絡先 商品 メーカー 営業担当 営業成績 kawasumi kawa@... ソファベッド A社 岡崎 A kawasumi kawa@... 本棚(大) B社 豊田 S yamabata yb@... 観葉植物（大） C社 春日井 C tanabee tanab@.. オフィスデスク D社 尾張 A tanabee tanab@.. 本棚（大） B社 西尾 B north1000 kita@.. ロッカー大 C社 豊田 S 営業記録 … 営業記録 ( ユーザ, 商品 , 連絡先, メーカー, 営業担当, 営業成績 )

Slide 12

Slide 12 text

例 ユーザ 連絡先 商品 メーカー 営業担当 営業成績 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 : 営業担当 → 営業成績 この関係スキーマ上が保持する関数従属性 関数従属性は対象世界に存在する制約として定められる 営業記録 ( ユーザ, 商品 , 連絡先, メーカー, 営業担当, 営業成績 )

Slide 13

Slide 13 text

FDダイアグラム: 関数従属性（と属性）を可視化した図 FD1 : ユーザ, 商品 → 営業担当 FD2 : ユーザ→ 連絡先 FD3 : 商品 → メーカー FD4 : 営業担当 → 営業成績 商品 ユーザ 連絡先 営業担当 営業成績 メーカー FD4 FD2 FD3 FD1 FDダイアグラム化 従属性の左辺が集合 の場合は丸で囲む

Slide 14

Slide 14 text

補⾜：FDダイアグラムと関係スキーマの対応 商品 ユーザ 連絡先 営業担当 営業成績 メーカー FD4 FD2 FD3 FD1 営業記録 ( ユーザ, 商品 , 連絡先, メーカー, 営業担当, 営業成績 ) ユーザ 連絡先 商品 メーカー 営業担当 営業成績 kawasumi kawa@... ソファベッド A社 岡崎 A kawasumi kawa@... 本棚(大) B社 豊田 S yamabata yb@... 観葉植物（大） C社 春日井 C …

Slide 15

Slide 15 text

Q1: 主キー Q. サブスクリプション型⾳楽ストリーミングサービス Orange Musicでは，楽曲に対するユーザの評価スコア を関係「評価」で管理している． 関係「評価」のス キーマは以下の通りである． 評価 ( ユーザ, 楽曲, アーティスト, 収録アルバム, ジャンル, スコア ) FD1 : ユーザ, 楽曲 → スコア FD2 : 楽曲 → 収録アルバム FD3 : 楽曲 → ジャンル FD4 : 収録アルバム → アーティスト 関係「評価」において 主キーと考えられる属性は何か？

Slide 16

Slide 16 text

Q1: 主キー A. サブスクリプション型⾳楽ストリーミングサービス Orange Musicでは，楽曲に対するユーザの評価スコア を関係「評価」で管理している． 関係「評価」のス キーマは以下の通りである． 主キーは { ユーザ, 楽曲 } 評価 ( ユーザ, 楽曲, アーティスト, 収録アルバム, ジャンル, スコア ) FD1 : ユーザ, 楽曲 → スコア FD2 : 楽曲 → 収録アルバム FD3 : 楽曲 → ジャンル FD4 : 収録アルバム → アーティスト

Slide 17

Slide 17 text

Q2: FDダイアグラム Q. 関係「評価」における以下の4つの関数従属性を FDダイアグラムを⽤いて表現せよ． FD1 : ユーザ, 楽曲 → スコア FD2 : 楽曲 → 収録アルバム FD3 : 楽曲 → ジャンル FD4 : 収録アルバム → アーティスト

Slide 18

Slide 18 text

Q2: FDダイアグラム Q. 関係「評価」における以下の4つの関数従属性を FDダイアグラムを⽤いて表現せよ． FD1 : ユーザ, 楽曲 → スコア FD2 : 楽曲 → 収録アルバム FD3 : 楽曲 → ジャンル FD4 : 収録アルバム → アーティスト A. ユーザ 楽曲 収録アルバム スコア アーティスト FD2 FD3 FD1 ジャンル FD4

Slide 19

Slide 19 text

関数従属性にもとづく正規化 2 Normalization using functional dependency

Slide 20

Slide 20 text

データに冗⻑性がない ⼀貫性制約を保持するのが容易 + 正規化された関係スキーマ 正規化された関係スキーマをつくるのが関係データベース設計の⽬標 （テーブルの中で保持する制約がシンプルになるほど扱いやすい）

Slide 21

Slide 21 text

第1正規形 第2正規形 第3正規形 ボイス・コッド正規形 第4正規形 第5正規形 正規化にはレベル分けがある ⾮正規形 関数従属性を使えばボイス・コッド正規形まで導ける 正規化の前後でデータの内容や⼀貫性制約は失われてはいけない

Slide 22

Slide 22 text

キーの概念の復習（1/4） 超キー 関係Rにおける属性集合のうち，それらの属性値が決まれば 関係Rのタプルを⼀意に特定できるもの 学籍番号 氏名 学部 年齢 S1 川澄 桜 経済学部 23 S2 山畑 滝子 文学部 20 S3 田辺 通 薬学部 18 S4 田辺 瑞穂 DS学部 19 S5 山畑 川名 DS学部 27 学⽣ {学籍番号} {学籍番号, ⽒名} {学籍番号, ⽒名, 学部} … 超キーは複数ある

Slide 23

Slide 23 text

キー（候補キー） 関係Rにおける属性集合のうち，それらの属性値が決まれば 関係Rのタプルを⼀意に特定できるものうち，極⼩なもの 学籍番号 氏名 学部 年齢 S1 川澄 桜 経済学部 23 S2 山畑 滝子 文学部 20 S3 田辺 通 薬学部 18 S4 田辺 瑞穂 DS学部 19 S5 山畑 川名 DS学部 27 学⽣ 最も⼩さい部分集合 {学籍番号} {学籍番号, ⽒名} {学籍番号, ⽒名, 学部} … キーは「学籍番号」 キーの概念の復習（2/4）

Slide 24

Slide 24 text

キーの概念の復習（3/4） キー（候補キー） 関係Rにおける属性集合のうち，それらの属性値が決まれば 関係Rのタプルを⼀意に特定できるものうち，極⼩なもの 学籍番号 Passport番号 氏名 学部 S1 TR1234xxx 川澄 桜 経済学部 S2 XS4321xxx 山畑 滝子 文学部 S3 KK7743xxx 田辺 通 薬学部 S4 VU9802xxx 田辺 瑞穂 DS学部 S5 AB5230xxx 山畑 川名 DS学部 学⽣ キーは複数あり得る キー = 学籍番号 Passport番号 or

Slide 25

Slide 25 text

キーの概念の復習（4/4） 主キー 候補キーのうち，値として未定義や空値（NULL値）を取る 可能性がなく，かつ運⽤上最も都合のよいキーの1つ 学籍番号 Passport番号 氏名 学部 S1 TR1234xxx 川澄 桜 経済学部 S2 XS4321xxx 山畑 滝子 文学部 S3 KK7743xxx 田辺 通 薬学部 S4 VU9802xxx 田辺 瑞穂 DS学部 S5 AB5230xxx 山畑 川名 DS学部 学⽣ パスポートを 持っていない人もいそう 主キーは 「学籍番号」

Slide 26

Slide 26 text

第1正規形 第2正規形 第3正規形 ボイス・コッド正規形 正規化にはレベル分けがある ⾮正規形 本講義の⽬標はボイス・コッド正規形まで

Slide 27

Slide 27 text

商品 ユーザ 営業 担当 ソファベッド kawasumi 岡崎 本棚(大) kawasumi 豊田 本棚（大） tanabee 西尾 ロッカー大 north1000 豊田 … ボイス・コッド正規形 関係スキーマRにおいて成⽴する⾃明でない どの関数従属性X→Y においても § X が R の超キーである とき、Rはボイス・コッド正規形であるという 商品 ユーザ 営業担当 FD1 R ( 商品, ユーザ , 営業担当 ) ボイス・コッド正規形の例 FD1 の左辺である{商品, ユーザ}はRのキーそのもの

Slide 28

Slide 28 text

ボイス・コッド正規形ではない例（1/2） 商品 ユーザ 営業担当 営業成績 FD4 FD1 R ( 商品, ユーザ , 営業担当, 営業成績 ) FD4 の左辺である { 営業担当 } が関係Rのキーではない 理由 （FD1 はボイス・コッド正規形の条件を満たしている） {商品, ユーザ} は関係R のキー

Slide 29

Slide 29 text

ボイス・コッド正規形ではない例（2/2） R ( 商品, ユーザ , メーカー, 営業担当, 連絡先 ) - FD2 の左辺である { ユーザ } がキーではない 理由 {商品, ユーザ} は関係R のキー 商品 ユーザ 連絡先 営業担当 メーカー FD2 FD3 FD1 - FD3 の左辺である { 商品 } がキーではない

Slide 30

Slide 30 text

ボイス・コッド正規形 関係スキーマRにおいて成⽴する⾃明でない どの関数従属性X→Y においても § X が R の超キーである とき、Rはボイス・コッド正規形であるという 商品 ユーザ 営業担当 FD1 R ( 商品, ユーザ, 営業担当 ) ボイス・コッド正規形の例 商品 ユーザ 営業 担当 ソファベッド kawasumi 岡崎 本棚(大) kawasumi 豊田 本棚（大） tanabee 西尾 ロッカー大 north1000 豊田 … BCNFはすべてのFDがキー出発のものに限定している

Slide 31

Slide 31 text

ボイス・コッド正規形 関係スキーマRにおいて成⽴する⾃明でない どの関数従属性X→Y においても § X が R の超キーである とき、Rはボイス・コッド正規形であるという 商品 ユーザ 営業担当 FD1 R ( 商品, ユーザ, 営業担当 ) ボイス・コッド正規形の例 商品 ユーザ 営業 担当 ソファベッド kawasumi 岡崎 本棚(大) kawasumi 豊田 本棚（大） tanabee 西尾 ロッカー大 north1000 豊田 … ボイス・コッド正規形は、 関係上のあらゆる関数従属性の左辺を 候補キーの上位集合に限定することで、 関数従属性に関するあらゆる冗長性を 排除している

Slide 32

Slide 32 text

関数従属性による情報無損失分解 属性集合U上の関係Rにおいて、関数従属性X→Y が 成⽴するならば、関係Rは § 関係R を属性集合X ∪ Y 上に射影した関係R1 の2つの関係に情報無損失分解できる 上記定理を⽤いれば、関係の正規化ができる § 関係R を属性集合X ∪ (U – Y ) 上に射影した関係R2 定理

Slide 33

Slide 33 text

例：関数従属性による情報無損失分解 購買 ( 購買ID , ユーザ, 購⼊商品, 単価, 数量) FD: 購⼊商品 → 単価 購買ID ユーザ 購入商品 単価 数量 T1 U1 はーいお茶 130 3 T2 U1 きのこの里 230 2 T4 U2 きのこの里 230 1 T5 U3 はーいお茶 130 1 T6 U4 タケノコの山 200 1 購買

Slide 34

Slide 34 text

例：関数従属性による情報無損失分解（1/2） 購買 ( 購買ID , ユーザ, 購⼊商品, 単価, 数量) FD: 購⼊商品 → 単価 U = { 購買ID, ユーザ, 購⼊商品, 単価, 数量 } X = 購⼊商品 Y = 単価 X ∪ Y = { 購⼊商品, 単価 } U − Y = {購買ID, ユーザ, 購⼊商品, 数量 } X ∪ (U−Y) = {購買ID, ユーザ, 購⼊商品, 数量 } 定理に当てはめる

Slide 35

Slide 35 text

例：関数従属性による情報無損失分解（2/2） 購買 ( 購買ID , ユーザ, 購⼊商品, 単価, 数量) FD: 購⼊商品 → 単価 情報無損失分解 R2 ( 購買ID , ユーザ, 購⼊商品, 数量) R1 ( 購⼊商品 , 数量) FD: 購⼊商品 → 単価 FDの両辺にある要素 からなる関係 元の属性集合から FDの右辺を除いた属性からなる関係

Slide 36

Slide 36 text

分解法による関係の正規化 1. Rが⾮正規形の場合，第1正規形に変換 分解法 関係Rの属性集合と R上の関数従属性を⽤いて、 Rをボイス・コッド正規形に⾄るまで分解する⼿法 2. いずれかの関数従属性に着⽬ 3. ⼿順2で選んだ関数従属性を使って 関係Rを情報無損失分解 4. ⼿順3で得られた関係がボイス・コッド正規形 （BCNF）になるまで⼿順2-3を繰り返す ⼿順 FDダイアグラム端にある (BCNFの条件を邪魔する) FDから着⼿するのがコツ

Slide 37

Slide 37 text

分解法の適⽤例（1/5） 営業記録 ( ユーザ, 商品, 連絡先, メーカー, 営業担当, 営業成績 ) FD1 : ユーザ, 商品 → 営業担当 FD2 : ユーザ→ 連絡先 FD3 : 商品 → メーカー FD4 : 営業担当 → 営業成績 関係「営業記録」は第1正規形 (1NF)… 商品 ユーザ 連絡先 営業担当 営業成績 メーカー FD4 FD2 FD3 FD1 キー

Slide 38

Slide 38 text

分解法の適⽤例（2/5） 商品 ユーザ 連絡先 営業担当 営業成績 メーカー FD4 FD2 FD3 FD1 1NF 営業担当 営業成績 FD4 商品 ユーザ 連絡先 営業担当 メーカー FD2 FD3 FD1 BCNF 1NF FD4 で分解 次の分解ターゲット

Slide 39

Slide 39 text

分解法の適⽤例（3/5） 商品 ユーザ 連絡先 営業担当 メーカー FD2 FD3 FD1 1NF ユーザ 連絡先 FD2 BCNF 商品 ユーザ 営業担当 メーカー FD3 FD1 1NF FD2 で分解 次の分解ターゲット

Slide 40

Slide 40 text

分解法の適⽤例（4/5） 1NF 商品 メーカー FD3 BCNF 商品 ユーザ 営業担当 メーカー FD3 FD1 BCNF FD3 で分解 商品 ユーザ 営業担当 FD1

Slide 41

Slide 41 text

分解法の適⽤例（5/5） 営業担当 営業成績 FD4 BCNF ユーザ 連絡先 FD2 BCNF 商品 メーカー FD3 BCNF BCNF 商品 ユーザ 営業担当 FD1 R1 ( 営業担当 , 営業成績 ) FD4 : 営業担当 → 営業成績 R3 ( 商品 , メーカー ) FD3 : 商品 → メーカー R2 ( ユーザ , 連絡先 ) FD4 : ユーザ → 連絡先 R4 ( 商品, ユーザ , 営業担当 ) FD1 : 商品, ユーザ→営業担当 FDを保存しつつ更新時異状の影響を最⼩化するスキーマを導出!

Slide 42

Slide 42 text

Q3: 関数従属性にもとづく正規化 Q. 下記FDダイアグラムを⽤いて、 関係スキーマ「評価」をBCNFへ分解せよ． なお、分解過程と正規化レベルも⽰すこと． ユーザ 楽曲 収録アルバム スコア FD2 FD3 FD1 ジャンル FD4 アーティスト

Slide 43

Slide 43 text

A3: 関数従属性にもとづく正規化 (1/4) 1NF 収録 アルバム アーティスト FD4 BCNF 1NF FD4 で分解 次の分解ターゲット ユーザ 楽曲 収録アルバム スコア FD2 FD3 FD1 ジャンル FD4 アーティスト ユーザ 楽曲 スコア FD2 FD3 FD1 ジャンル 収録 アルバム

Slide 44

Slide 44 text

A3: 関数従属性にもとづく正規化 (2/4) 楽曲 収録アルバム FD2 BCNF 1NF FD2 で分解 次の分解ターゲット ユーザ 楽曲 スコア FD3 FD1 ジャンル 1NF ユーザ 楽曲 スコア FD2 FD3 FD1 ジャンル 収録 アルバム

Slide 45

Slide 45 text

A3: 関数従属性にもとづく正規化 (3/4) 楽曲 ジャンル FD3 BCNF BCNF FD3 で分解 ユーザ 楽曲 スコア FD1 1NF ユーザ 楽曲 スコア FD3 FD1 ジャンル

Slide 46

Slide 46 text

A3: 関数従属性にもとづく正規化 (4/4) 楽曲 収録アルバム FD2 BCNF 楽曲 ジャンル FD3 BCNF 収録 アルバム アーティスト FD4 BCNF BCNF ユーザ 楽曲 スコア FD1 R1 ( 楽曲 , 収録アルバム ) FD2 : 楽曲 → 収録アルバム R3 ( 収録アルバム , アーティスト ) FD4 : 収録アルバム → アーティスト R2 ( 楽曲 , ジャンル ) FD3 : 楽曲 → ジャンル R4 ( ユーザ, 楽曲 , スコア ) FD1 : ユーザ, 楽曲→スコア

Slide 47

Slide 47 text

Q4: 分解⼿順と結果の関係 (1/3) Q. 関係「営業記録」に関する下記FDダイアグラムを ⽤いて、関係「営業記録」をBCNFへ分解せよ． ただし，分解法はFD2 ，FD3 ，FD4 の順で適⽤せよ． なお、分解過程と正規化レベルも⽰すこと． 商品 ユーザ 連絡先 営業担当 営業成績 メーカー FD4 FD2 FD3 FD1

Slide 48

Slide 48 text

A4: 分解⼿順と結果の関係 (1/3) 商品 ユーザ 連絡先 営業担当 営業成績 メーカー FD4 FD2 FD3 FD1 1NF ユーザ 連絡先 FD2 BCNF 1NF FD2 で分解 次の分解ターゲット 商品 ユーザ 営業 担当 営業 成績 メーカー FD4 FD3 FD1

Slide 49

Slide 49 text

A4: 分解⼿順と結果の関係 (2/3) 商品 メーカー FD3 BCNF 第2正規形 (2NF) FD3 で分解 商品 ユーザ 営業 担当 営業 成績 FD4 FD1 1NF 商品 ユーザ 営業 担当 営業 成績 メーカー FD4 FD3 FD1

Slide 50

Slide 50 text

第2正規形 関係スキーマRにおいて成⽴する⾃明でない どの関数従属性 X → Y においても 条件1. X が R の超キーである のいずれかを満たすとき、Rは第2正規形である 第2正規形の例 条件2. Y が R のキー属性である 条件3. X が R のキーの部分集合でない 商品 ユーザ 営業担当 営業成績 FD4 FD1 2NFはBCNFの中間⽣成物なので重要性が低い キーから関数従属性 をたどれば， すべての属性が決まる

Slide 51

Slide 51 text

A4: 分解⼿順と結果の関係 (3/3) 営業 担当 営業 成績 FD4 BCNF FD4 で分解 商品 ユーザ 営業 担当 FD1 2NF 商品 ユーザ 営業 担当 営業 成績 FD4 FD1 BCNF

Slide 52

Slide 52 text

分解法の適⽤例（5/5） 営業担当 営業成績 FD4 BCNF 営業担当 営業成績 FD4 BCNF 商品 メーカー FD3 BCNF BCNF 商品 ユーザ 営業担当 FD1 R1 ( 営業担当 , 営業成績 ) FD4 : 営業担当 → 営業成績 R3 ( 商品 , メーカー ) FD3 : 商品 → メーカー R2 ( 営業担当 , 営業成績 ) FD4 : 営業担当 → 営業成績 R4 ( 商品, ユーザ , 営業担当 ) FD1 : 商品, ユーザ→営業担当 FDを保存しつつ更新時異状の影響を最⼩化するスキーマを導出!! 実際にはすべての関係を ボイス・コッド正規形に導けないことも…

Slide 53

Slide 53 text

ボイス・コッド正規形まで分解できない例（1/5） 営業記録 ( ユーザ, 商品, 連絡先, メーカー, 営業担当, 営業成績 ) FD1 : ユーザ, 商品 → 営業担当 FD2 : ユーザ→ 連絡先 FD3 : 商品 → メーカー FD4 : 営業担当 → 営業成績 商品 ユーザ 連絡先 営業担当 営業成績 メーカー FD4 FD2 FD3 FD1 FD5 : 営業担当 → 商品 FD5 営業担当ごとに担当商品が決まっているのは⾃然な制約

Slide 54

Slide 54 text

ボイス・コッド正規形まで分解できない例（2/5） 商品 ユーザ 連絡先 営業担当 営業成績 メーカー FD4 FD2 FD3 FD1 1NF 営業担当 営業成績 FD4 商品 ユーザ 連絡先 営業担当 メーカー FD2 FD3 FD1 BCNF 1NF FD4 で分解 次の分解ターゲット FD5 FD5

Slide 55

Slide 55 text

ボイス・コッド正規形まで分解できない例（3/5） 1NF ユーザ 連絡先 FD2 BCNF 1NF FD2 で分解 次の分解ターゲット 商品 ユーザ 連絡先 営業担当 メーカー FD2 FD3 FD1 FD5 商品 ユーザ 営業担当 メーカー FD3 FD1 FD5

Slide 56

Slide 56 text

ボイス・コッド正規形まで分解できない例（4/5） 1NF 商品 メーカー FD3 BCNF FD3 で分解 商品 ユーザ 営業担当 FD1 商品 ユーザ 営業担当 メーカー FD3 FD1 FD5 FD5

Slide 57

Slide 57 text

これはボイス・コッド正規形？ 商品 ユーザ 営業担当 FD1 FD5 FD5の左辺がこの関係における超キーでない… BCNFの定義 関係スキーマRにおいて成⽴する⾃明でない どの関数従属性X→Y においても § X が R の超キーである とき、Rはボイス・コッド正規形であるという

Slide 58

Slide 58 text

情報無損失分解の定理を適⽤すると 商品 ユーザ 営業担当 FD1 FD5 営業担当 商品 FD5 BCNF 営業担当 ユーザ BCNF （関数従属性なし） FD5 で分解 情報無損失分解できてもFD情報が失われるのはマズい… FD1 が失われてしまった!!

Slide 59

Slide 59 text

ボイス・コッド正規形（復習） 関係スキーマRにおいて成⽴する⾃明でない どの関数従属性X→Y においても § X が R の超キーである とき、Rはボイス・コッド正規形である 商品 ユーザ 営業担当 FD1 ボイス・コッド正規形の例

Slide 60

Slide 60 text

第3正規形の導⼊ 関係スキーマRにおいて成⽴する⾃明でない どの関数従属性 X → Y においても 条件1. X が R の超キーである のいずれかを満たすとき、Rは第3正規形である 第3正規形の例 条件2. Y が R の候補キーの要素である 追加のOR条件 商品 ユーザ 営業担当 FD1 FD5 条件2に相当 条件1に相当 ボイス・コッド正規形の例 商品 ユーザ 営業担当 FD1 条件1に相当

Slide 61

Slide 61 text

ボイス・コッド正規形まで分解できない例（5/5） 1NF 商品 メーカー FD3 BCNF FD3 で分解 商品 ユーザ 営業担当 FD1 商品 ユーザ 営業担当 メーカー FD3 FD1 FD5 FD5 第3正規形(3NF)

Slide 62

Slide 62 text

ボイス・コッド正規形まで分解できない例（5/5） 営業担当 営業成績 FD4 BCNF ユーザ 連絡先 FD2 BCNF 商品 メーカー FD3 BCNF 3NF 商品 ユーザ 営業担当 FD1 R1 ( 営業担当 , 営業成績 ) FD4 : 営業担当 → 営業成績 R3 ( 商品 , メーカー ) FD3 : 商品 → メーカー R2 ( ユーザ , 連絡先 ) FD4 : ユーザ → 連絡先 R4 ( 商品, ユーザ , 営業担当 ) FD1 : 商品, ユーザ→営業担当 FD5 FD5 : 営業担当 → 商品

Slide 63

Slide 63 text

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)

Slide 64

Slide 64 text

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

Slide 65

Slide 65 text

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

Slide 66

Slide 66 text

A4: 関数従属性にもとづく正規化2 C D FD3 BCNF FD3 で分解 A B C FD2 FD1 3NF A B C D FD2 FD1 2NF FD3

Slide 67

Slide 67 text

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

Slide 68

Slide 68 text

Q6: 関数従属性にもとづく正規化3 Q. 下記のような関係スキーマR およびその関数従属性が 与えられたとき， R から3NFもしくはBCNFを導出せ よ． なお，途中過程も⽰すこと． また，分解の過程 で得られる関係スキーマに正規化レベルを付与せよ． あるスポーツ団体では，以下の関係スキーマRとその 関数従属性に基づき．団体に属する選⼿やチームに 関する情報を管理する関係データベースを設計しよう としている． FD1 : 選⼿ → 出⾝校 FD2 : チーム → チーム創⽴年 FD3 : チーム, 年度 → 監督 FD4 : 選⼿, 年度 → チーム R (選⼿, 年度 , 出⾝校, チーム, 監督, チーム創⽴年)

Slide 69

Slide 69 text

A5: 関数従属性にもとづく正規化3 FD4 FD2 FD1 FD3 選⼿ 年度 チーム 出⾝校 チーム 創⽴年 監督 1NF FD2 チーム チーム 創⽴年 FD4 FD1 FD3 選⼿ 年度 チーム 出⾝校 監督 1NF BCNF FD2 で分解

Slide 70

Slide 70 text

A5: 関数従属性にもとづく正規化3 FD1 選⼿ 出⾝校 FD4 FD1 FD3 選⼿ 年度 チーム 出⾝校 監督 1NF BCNF FD1 で分解 FD4 FD3 選⼿ 年度 チーム 監督 2NF

Slide 71

Slide 71 text

A5: 関数従属性にもとづく正規化3 FD3 年度 監督 BCNF FD3 で分解 FD4 FD3 選⼿ 年度 チーム 監督 2NF チーム FD4 選⼿ 年度 チーム BCNF

Slide 72

Slide 72 text

A5: 関数従属性にもとづく正規化3 チーム チーム 創⽴年 FD2 BCNF 選⼿ 出⾝校 FD1 BCNF R1 ( チーム , チーム創⽴年 ) FD2 : チーム → チーム創⽴年 R2 ( 選⼿ , 出⾝校 ) FD1 : 選⼿ → 出⾝校 FD3 年度 監督 BCNF チーム FD4 選⼿ 年度 チーム BCNF R3 ( チーム, 年度 , 監督 ) FD3 : チーム, 年度 → 監督 R4 ( 選⼿, 年度 , チーム ) FD4 : 選⼿, 年度 → チーム

Slide 73

Slide 73 text

望ましいスキーマを得る手順 3 Procedure to obtain ideal relational schema

Slide 74

Slide 74 text

望ましい関係スキーマを導出する⼿順まとめ 概念 モデリング 実体関連図 関係スキーマ (3NF or BCNF) FDダイアグラム スキーマ変換 + 正規化 直接スキーマ変換 実体関連モデルとFDダイアグラムの併用がオススメ 情報無損失分解 データの意味/構造を捉える 属性間の制約を捉える 全属性集合をもつ 1NFを分解

Slide 75

Slide 75 text

第1正規形 第2正規形 第3正規形 ボイス・コッド正規形 第4正規形 第5正規形 正規化のレベル ⾮正規形 ⼀般には3NF or BCNFまで正規化できればOK (多値従属性による分解で導出) (結合従属性による分解で導出)

Slide 76

Slide 76 text

回 実施日 トピック 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