正規化理論ことはじめ -数学的背景から理解する正規化の初手-

by kubo ayumu

Slide 1

Slide 1 text

正規化理論ことはじめ   -数学的背景から理解する正規化の初手- 

Slide 2

Slide 2 text

© 2022 for LANCERS, Inc. All Rights Reserved 2 2 久保路（くぼあゆむ）・ ~ 2020.03 大学院で数学を研究・ 2020.04 ~ 2021.12 BIチームに所属しSalesforce管理・開発・ 2022.01~ 現在 QAチームに所属しCakePHP２→４への　バージョンアッププロジェクトなどに参画中・クエリチューニングをきっかけに　RDBの勉強にハマる・クラフトビールが好き

Slide 3

Slide 3 text

Slide 4

Slide 4 text

Slide 5

Slide 5 text

Slide 6

Slide 6 text

Slide 7

Slide 7 text

Slide 8

Slide 8 text

RDBにおける用語の整理 

Slide 9

Slide 9 text

© 2022 for LANCERS, Inc. All Rights Reserved 9 ● いくつかの集合(ドメイン)の組み合わせ(の部分集合)を指す ● 例名前の集合 D_1 = {太郎,花子,二郎}, 年齢の集合 D_2 = {20,25} の組み合わせから R(D_1,D_2) = { (太郎,20), (花子,25), (二郎,20) } のようなリレーションができる ● リレーションは表として実現することができるリレーションとは

Slide 10

Slide 10 text

Slide 11

Slide 11 text

Slide 12

Slide 12 text

Slide 13

Slide 13 text

Slide 14

Slide 14 text

Slide 15

Slide 15 text

© 2022 for LANCERS, Inc. All Rights Reserved 15 ● リレーションの属性名の集合Kが候補キーであるとは次を満たすこと ○ リレーションの任意の行 t と sに対して tのKの値とsのKの値が一致すれば、tとsは完全一致する → Kの値さえ決まれば、行が一意に定まる ○ Kの自身を除く部分集合に対しては、上記は成り立たない → Kを分解してしまうと、行を一意に識別できなくなってしまう候補キーとは

Slide 16

Slide 16 text

Slide 17

Slide 17 text

Slide 18

Slide 18 text

Slide 19

Slide 19 text

Slide 20

Slide 20 text

© 2022 for LANCERS, Inc. All Rights Reserved 20 個人番号姓名年齢住所生年月日性別郵便番号星座 00001 山田太郎 26 東京都墨田区押上１丁目１−２ 1996/01/01 男 1310045 山羊座 00002 田中花子 22 東京都港区芝公園４丁目２−８ 2000/01/01 女 1050011 山羊座各ドメインを組み合わせてリレーション「日本人」が作れる

Slide 21

Slide 21 text

Slide 22

Slide 22 text

Slide 23

Slide 23 text

Slide 24

Slide 24 text

Slide 25

Slide 25 text

Slide 26

Slide 26 text

© 2022 for LANCERS, Inc. All Rights Reserved 26 ● リレーションの属性名の集合Kが候補キーであるとは次を満たすこと ○ リレーションの任意の行 t と sに対して tのKの値とsのKの値が一致すれば、tとsは完全一致する → Kの値さえ決まれば、行が一意に定まる ○ Kの自身を除く部分集合に対しては、上記は成り立たない → Kを分解してしまうと、行を一意に識別できなくなってしまう候補キーとは

Slide 27

Slide 27 text

Slide 28

Slide 28 text

第１正規化 

Slide 29

Slide 29 text

Slide 30

Slide 30 text

Slide 31

Slide 31 text

Slide 32

Slide 32 text

Slide 33

Slide 33 text

Slide 34

Slide 34 text

Slide 35

Slide 35 text

Slide 36

Slide 36 text

Slide 37

Slide 37 text

Slide 38

Slide 38 text

Slide 39

Slide 39 text

Slide 40

Slide 40 text

Slide 41

Slide 41 text

理想的な分解 

Slide 42

Slide 42 text

Slide 43

Slide 43 text

Slide 44

Slide 44 text

Slide 45

Slide 45 text

Slide 46

Slide 46 text

© 2022 for LANCERS, Inc. All Rights Reserved 46 顧客名商品名商品番号数量単価金額 A企業テレビ 1 3 200,000 600,000 B企業テレビ 1 10 200,000 2,000,000 B企業洗濯機 2 5 70,000 350.000 ● タプル挿入時異状 →新しい商品を追加しようとしても、顧客名が定まらないと挿入できない ● タプル削除時異状 →B企業の洗濯機の注文行を消すと、洗濯機の単価情報を抹消される ● タプル修正時異状 →テレビの単価が変更になったとすると、2行分更新する必要があるリレーション注文

Slide 47

Slide 47 text

© 2022 for LANCERS, Inc. All Rights Reserved 47 顧客名商品名商品番号数量単価金額 A企業テレビ 1 3 200,000 600,000 B企業テレビ 1 10 200,000 2,000,000 B企業洗濯機 2 5 70,000 350.000 ● タプル挿入時異状 →新しい商品を追加しようとしても、顧客名が定まらないと挿入できない ● タプル削除時異状 →B企業の洗濯機の注文行を消すと、洗濯機の単価情報を抹消される ● タプル修正時異状 →テレビの単価が変更になったとすると、2行分更新する必要があるリレーション注文

Slide 48

Slide 48 text

© 2022 for LANCERS, Inc. All Rights Reserved 48 顧客名商品名商品番号数量単価金額 A企業テレビ 1 3 200,000 600,000 B企業テレビ 1 10 200,000 2,000,000 B企業洗濯機 2 5 70,000 350.000 リレーション注文 - 掃除機 3 - 90000 - 掃除機を新たに売り出すことになったので以下のようなレコードを追加したい

Slide 49

Slide 49 text

Slide 50

Slide 50 text

Slide 51

Slide 51 text

Slide 52

Slide 52 text

© 2022 for LANCERS, Inc. All Rights Reserved 52 顧客名商品名商品番号数量単価金額 A企業テレビ 1 3 200,000 600,000 B企業テレビ 1 10 200,000 2,000,000 B企業洗濯機 2 5 70,000 350.000 ● タプル挿入時異状 →新しい商品を追加しようとしても、顧客名が定まらないと挿入できない ● タプル削除時異状 →B企業の洗濯機の注文行を消すと、洗濯機の単価情報を抹消される ● タプル修正時異状 →テレビの単価が変更になったとすると、2行分更新する必要があるリレーション注文

Slide 53

Slide 53 text

Slide 54

Slide 54 text

Slide 55

Slide 55 text

Slide 56

Slide 56 text

Slide 57

Slide 57 text

Slide 58

Slide 58 text

Slide 59

Slide 59 text

Slide 60

Slide 60 text

Slide 61

Slide 61 text

Slide 62

Slide 62 text

Slide 63

Slide 63 text

© 2022 for LANCERS, Inc. All Rights Reserved 63 リレーション注文顧客名商品名商品番号数量単価金額 A企業テレビ 1 3 200,000 600,000 B企業テレビ 1 10 200,000 2,000,000 B企業洗濯機 2 5 70,000 350.000 顧客名商品名商品番号 A企業テレビ 1 B企業テレビ 1 B企業洗濯機 2 商品番号数量単価金額 1 3 200,000 600,000 1 10 200,000 2,000,000 2 5 70,000 350.000

Slide 64

Slide 64 text

Slide 65

Slide 65 text

© 2022 for LANCERS, Inc. All Rights Reserved 65 顧客名商品名商品番号数量単価金額 A企業テレビ 1 3 200,000 600,000 A企業テレビ 1 10 200,000 2,000,000 B企業テレビ 1 3 200,000 600,000 B企業テレビ 1 10 200,000 2,000,000 B企業洗濯機 2 5 70,000 350.000 顧客名商品名商品番号 A企業テレビ 1 B企業テレビ 1 B企業洗濯機 2 商品番号数量単価金額 1 3 200,000 600,000 1 10 200,000 2,000,000 2 5 70,000 350.000

Slide 66

Slide 66 text

Slide 67

Slide 67 text

© 2022 for LANCERS, Inc. All Rights Reserved 67 顧客名商品名商品番号数量単価金額 A企業テレビ 1 3 200,000 600,000 A企業テレビ 1 10 200,000 2,000,000 B企業テレビ 1 3 200,000 600,000 B企業テレビ 1 10 200,000 2,000,000 B企業洗濯機 2 5 70,000 350.000 顧客名商品名商品番号 A企業テレビ 1 B企業テレビ 1 B企業洗濯機 2 商品番号数量単価金額 1 3 200,000 600,000 1 10 200,000 2,000,000 2 5 70,000 350.000 余計な行が増えてしまった

Slide 68

Slide 68 text

Slide 69

Slide 69 text

Slide 70

Slide 70 text

Slide 71

Slide 71 text

Slide 72

Slide 72 text

Slide 73

Slide 73 text

Slide 74

Slide 74 text

Slide 75

Slide 75 text

Slide 76

Slide 76 text

Slide 77

Slide 77 text

Slide 78

Slide 78 text

Slide 79

Slide 79 text

© 2022 for LANCERS, Inc. All Rights Reserved 79 個人番号姓名住所生年月日性別郵便番号 00001 山田太郎東京都墨田区押上１丁目１−２ 1996/01/01 男 1310045 00002 田中花子東京都港区芝公園４丁目２−８ 2000/01/01 女 1050011 生年月日年齢星座 1996/01/01 26 山羊座 2000/01/01 22 山羊座

Slide 80

Slide 80 text

Slide 81

Slide 81 text

Slide 82

Slide 82 text

Slide 83

Slide 83 text

Slide 84

Slide 84 text

© 2022 for LANCERS, Inc. All Rights Reserved 84 ● 実は関数従属性に沿った分解は必ず情報無損失分解となっていることが知られている ● 情報無損失分解の存在と多値従属性の存在が同値であることと関数従属性が多値従属性の特別な場合であることから従う ● つまりリレーション内の関数従属性に沿って、適切に分解をしていけばよい →これが第２正規化・第３正規化のはじまり

Slide 85

Slide 85 text

第２正規化 

Slide 86

Slide 86 text

Slide 87

Slide 87 text

Slide 88

Slide 88 text

Slide 89

Slide 89 text

© 2022 for LANCERS, Inc. All Rights Reserved 89 学籍番号学生名受講科目担当教員教員番号 100 田中世界史浜田 A1 100 田中数学松本 B2 101 遠藤数学松本 B2 102 山﨑数学松本 B2 102 山﨑世界史浜田 A1 リレーション受講 ※以下の関数従属性が存在しているとする・学籍番号→学生名　　　・受講科目→担当教員　　・受講科目 →教員番号・担当教員→教員番号　　・教員番号 →担当教員

Slide 90

Slide 90 text

Slide 91

Slide 91 text

Slide 92

Slide 92 text

Slide 93

Slide 93 text

Slide 94

Slide 94 text

Slide 95

Slide 95 text

Slide 96

Slide 96 text

Slide 97

Slide 97 text

Slide 98

Slide 98 text

Slide 99

Slide 99 text

Slide 100

Slide 100 text

Slide 101

Slide 101 text

Slide 102

Slide 102 text

Slide 103

Slide 103 text

Slide 104

Slide 104 text

Slide 105

Slide 105 text

Slide 106

Slide 106 text

Slide 107

Slide 107 text

© 2022 for LANCERS, Inc. All Rights Reserved 107 顧客名商品名商品番号数量単価金額 A企業テレビ 1 3 200,000 600,000 B企業テレビ 1 10 200,000 2,000,000 B企業洗濯機 2 5 70,000 350.000 リレーション注文 ※以下の関数従属性が存在しているとする・商品番号→商品名　　　・商品名 →商品番号・商品番号→単価　　　　・数量＆単価 →金額・顧客＆商品番号→数量

Slide 108

Slide 108 text

Slide 109

Slide 109 text

Slide 110

Slide 110 text

© 2022 for LANCERS, Inc. All Rights Reserved 110 顧客名商品名商品番号数量単価金額 A企業テレビ 1 3 200,000 600,000 B企業テレビ 1 10 200,000 2,000,000 B企業洗濯機 2 5 70,000 350.000 リレーション注文候補キーが各非キー属性に対して完全関数従属しているかを調べていく →候補キーの一部から非キー属性への関数従属性　が存在するかを調べる

Slide 111

Slide 111 text

Slide 112

Slide 112 text

© 2022 for LANCERS, Inc. All Rights Reserved 112 顧客名商品名商品番号数量単価金額 A企業テレビ 1 3 200,000 600,000 B企業テレビ 1 10 200,000 2,000,000 B企業洗濯機 2 5 70,000 350.000 「金額」は候補キーの片方だけでは決まらない ※仮定されている関数従属性・商品番号→商品名　　　・商品名 →商品番号・商品番号→単価　　　　・数量＆単価→金額・顧客＆商品番号→数量

Slide 113

Slide 113 text

Slide 114

Slide 114 text

© 2022 for LANCERS, Inc. All Rights Reserved 114 顧客名商品名商品番号数量単価金額 A企業テレビ 1 3 200,000 600,000 B企業テレビ 1 10 200,000 2,000,000 B企業洗濯機 2 5 70,000 350.000 「数量」は候補キーの片方だけでは決まらない ※仮定されている関数従属性・商品番号→商品名　　　・商品名 →商品番号・商品番号→単価　　　　・数量＆単価 →金額・顧客＆商品番号→数量

Slide 115

Slide 115 text

Slide 116

Slide 116 text

© 2022 for LANCERS, Inc. All Rights Reserved 116 顧客名商品名商品番号数量単価金額 A企業テレビ 1 3 200,000 600,000 B企業テレビ 1 10 200,000 2,000,000 B企業洗濯機 2 5 70,000 350.000 「商品名」と「単価」は候補キーの片方だけで決まる ※仮定されている関数従属性・商品番号→商品名　　　・商品名→商品番号・商品番号→単価　　　　・数量＆単価→金額・顧客＆商品番号→数量

Slide 117

Slide 117 text

© 2022 for LANCERS, Inc. All Rights Reserved 117 顧客名商品名商品番号数量単価金額 A企業テレビ 1 3 200,000 600,000 B企業テレビ 1 10 200,000 2,000,000 B企業洗濯機 2 5 70,000 350.000 (顧客名,商品番号)→(商品名,単価)の関数従属性に対し　　　　商品番号→(商品名,単価)の関数従属性も成り立つので (顧客名,商品番号)は(商品名,単価)に完全関数従属していない

Slide 118

Slide 118 text

Slide 119

Slide 119 text

Slide 120

Slide 120 text

© 2022 for LANCERS, Inc. All Rights Reserved 120 顧客名商品名商品番号数量単価金額 A企業テレビ 1 3 200,000 600,000 B企業テレビ 1 10 200,000 2,000,000 B企業洗濯機 2 5 70,000 350.000 (顧客名,商品番号)→(商品名,単価)の関数従属性に対し　　　　商品番号→(商品名,単価)の関数従属性も成り立つので (顧客名,商品番号)は(商品名,単価)に完全関数従属していない

Slide 121

Slide 121 text

© 2022 for LANCERS, Inc. All Rights Reserved 121 顧客名商品名商品番号数量単価金額 A企業テレビ 1 3 200,000 600,000 B企業テレビ 1 10 200,000 2,000,000 B企業洗濯機 2 5 70,000 350.000 (顧客名,商品番号)→(商品名,単価)の関数従属性に対し　　　　商品番号→(商品名,単価)の関数従属性も成り立つので (顧客名,商品番号)は(商品名,単価)に完全関数従属していない

Slide 122

Slide 122 text

© 2022 for LANCERS, Inc. All Rights Reserved 122 顧客名商品名商品番号数量単価金額 A企業テレビ 1 3 200,000 600,000 B企業テレビ 1 10 200,000 2,000,000 B企業洗濯機 2 5 70,000 350.000 (顧客名,商品番号)→(商品名,単価)の関数従属性に対し　　　　商品番号→(商品名,単価)の関数従属性も成り立つので (顧客名,商品番号)は(商品名,単価)に完全関数従属していない部分関数従属性

Slide 123

Slide 123 text

Slide 124

Slide 124 text

© 2022 for LANCERS, Inc. All Rights Reserved 124 顧客名商品名商品番号数量単価金額 A企業テレビ 1 3 200,000 600,000 B企業テレビ 1 10 200,000 2,000,000 B企業洗濯機 2 5 70,000 350.000 顧客名商品番号数量金額 A企業 1 3 600,000 B企業 1 10 2,000,000 B企業 2 5 350.000 商品番号商品名単価 1 テレビ 200,000 2 洗濯機 70,000

Slide 125

Slide 125 text

© 2022 for LANCERS, Inc. All Rights Reserved 125 顧客名商品名商品番号数量単価金額 A企業テレビ 1 3 200,000 600,000 B企業テレビ 1 10 200,000 2,000,000 B企業洗濯機 2 5 70,000 350.000 顧客名商品番号数量金額 A企業 1 3 600,000 B企業 1 10 2,000,000 B企業 2 5 350.000 商品番号商品名単価 1 テレビ 200,000 2 洗濯機 70,000 第２正規化

Slide 126

Slide 126 text

Slide 127

Slide 127 text

最後に 

Slide 128

Slide 128 text

© 2022 for LANCERS, Inc. All Rights Reserved 128 ● 第１正規化とは、複雑性の回避のためにカラムの値を単一の値になるよに修正すること ● リレーションの理想的な分解は、情報無損失分解であり関数従属性保存であること ● 関数従属性に沿って分解すれば情報無損失分解になる ● 第２正規化は、部分関数従属性に沿って分解することであり、それは関数従属性保存となっている今日持ち帰っていただきたいこと

Slide 129

Slide 129 text

Slide 130

Slide 130 text