データベース11: 正規化(1/2) - 望ましくない関係スキーマ

Slide 1

Slide 1 text

正規化（1/2）⼭本祐輔名古屋市⽴⼤学データサイエンス学部 yusuke_yamamoto@acm.org 第11回データベース 2024年7月1日〜望ましくない関係スキーマ

Slide 2

Slide 2 text

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

Slide 3

Slide 3 text

関係データモデルあるゆるデータを表（=関係）として表現するモデル - データに冗⻑性がない - データのもつ⼀貫性制約（正しさに関する制約）が保持するのが容易

Slide 4

Slide 4 text

関係スキーマ関係関係スキーマインスタンス = + 関係の名前と関係がもつ属性，⼀貫性制約に関する情報 (" A! , … , A" , {'! , … , '# }) 記法関係名属性⼀貫性制約の集合 " A! , … , A" ⼀貫性制約が⾃明 or 考慮しないとき

Slide 5

Slide 5 text

関係データモデル上の⼀貫性制約一貫性制約ドメイン制約キー制約参照制約データ従属性実世界を正しく反映すべく DB上のデータが満たすべき規則 …

Slide 6

Slide 6 text

典型的な関係データモデル学生ID 姓名入学年所属 s00001 川澄桜 2023 A学部 s00002 山畑滝子 2024 B学部 s00003 田辺通 2024 C学部科目ID 科目名開講年度 c0001 線形代数 2023 c0002 線形代数 2024 c0003 統計学入門 2024 科目ID 学生ID 成績 c0001 s00001 不可 c0002 s00001 良 c0002 s00002 優 c0003 s00003 可学⽣科⽬履修 … … … データの正しさを担保するためにデータを複数の表に分割

Slide 7

Slide 7 text

データに冗⻑性がない⼀貫性制約を保持するのが容易 + 正規化された関係スキーマ正規化された関係スキーマをつくるのが関係データベース設計の⽬標

Slide 8

Slide 8 text

⾮正規形と第1正規形ドメイン要素として原⼦値以外の値を取りうる関係非正規形の関係ドメイン要素として原⼦値のみ取りうる関係第1正規形の関係購買ID 購買品目売上登録日 T1 はーいお茶, きのこの里, のど飴 530 2020/07/15 T2 午前の紅茶 130 2020/09/25 T3 麦茶, おにぎり 250 2021/02/16 T100 きのこの里 200 2023/01/08 … 購買履歴（⾮正規系の関係）購買履歴（第1正規形）購買ID 購買品目売上登録日 T1 はーいお茶 130 2020/07/15 T1 きのこの里 230 2020/07/15 T1 のど飴 170 2020/07/15 T2 午前の紅茶 130 2020/09/25 T3 麦茶 120 2021/02/16 T3 おにぎり 130 2021/02/16 … 「購買品⽬」の値として集合を許してしまっている正規化

Slide 9

Slide 9 text

第1正規形第2正規形第3正規形ボイス・コッド正規形第4正規形第5正規形正規化にはレベル分けがある⾮正規形本授業まではボイス・コッド正規形まで扱う

Slide 10

Slide 10 text

実体関連モデルから関係スキーマへの変換 1

Slide 11

Slide 11 text

データモデリングの流れ概念モデリング論理モデリング対象世界をざっくりとモデル化 DBMSに応じて概念モデルを変換実体関連モデル関係データモデル実体関連モデルを⽤いると関係スキーマを機械的に導出可能 ! A! , … , A" ! A! , … , A" ! A! , … , A"

Slide 12

Slide 12 text

以前作成した実体関連図商品商品ID 商品名価格発売⽇ユーザユーザ名⽒名 email 住所購⼊希望登録⽇製造メーカー企業名 email TEL ショッピングサイトにおける「ユーザが購⼊希望の商品」「商品の製造メーカー」の情報の管理

Slide 13

Slide 13 text

実体関連モデルから関係スキーマの導出パターン 1. 実体型 2. 関連型 3. 汎化・継承 4. 弱実体・弱関連型

Slide 14

Slide 14 text

実体関連モデルから関係スキーマの導出パターン 1. 実体型 2. 関連型 3. 汎化・継承 4. 弱実体・弱関連型

Slide 15

Slide 15 text

「実体型」の関係スキーマへの変換⼿順 1. 実体型名を関係スキーマの関係名にする 2. 属性を関係スキーマの属性にする 3. キーを関係スキーマの主キーにする商品商品ID 価格商品名商品 ( 商品ID , 商品名, 価格 ) 関係スキーマ変換

Slide 16

Slide 16 text

関係スキーマとインスタンスの対応関係（1/2）商品ID 名称単価 P1 はーいお茶 130 P2 午前の紅茶 130 P3 健康麦茶 150 P1000 きのこの里 200 商品商品 ( 商品ID , 価格 , 商品名 ) …

Slide 17

Slide 17 text

Q1: 関係スキーマへの変換（1/4） Q. 以下は，架空のショッピングサイトにおける購買履歴を管理するシステムのER図である．図中の実体型「ユーザ」「商品」「注⽂」「メーカー」を関係スキーマに変換せよ．

Slide 18

Slide 18 text

Q1: 関係スキーマへの変換（1/4） A. ユーザ ( ユーザ名, ⽒名, 住所, email ) 商品 ( 商品ID, 商品名, 発売⽇, 価格 ) メーカー ( 企業名, TEL, email ) 注文 ( 注⽂ID, 注⽂⽇ )

Slide 19

Slide 19 text

実体関連モデルから関係スキーマの導出パターン 1. 実体型 2. 関連型「多対多関連」パターン「多対1関連」パターン「1対1関連」パターン 3. 汎化・継承 4. 弱実体・弱関連型

Slide 20

Slide 20 text

「関連型」の関係スキーマへの変換⼿順 – 多対多関連パターン 1. 関連名を関係スキーマの関係名にする 2. 関連型とつながるすべての実体型のキーを関連型の主キーとする 3. 関連型の属性を関係スキーマの属性に追加購入希望( ユーザ名 , 商品ID , ) 登録⽇ 4. 主キーに関する参照整合性制約を追加する購⼊希望.ユーザ名 ⊆ ユーザ.ユーザ名購⼊希望.商品ID ⊆ 商品.商品ID 商品ユーザユーザ名⽒名 email 住所購⼊希望登録⽇商品名価格発売⽇商品ID 参照制約

Slide 21

Slide 21 text

関係スキーマとインスタンスの対応関係（2/2）ユーザ名氏名 email 住所 U1 山畑滝子 … … U2 川澄桜 … … U3 北千種 … … ユーザ購入希望 ( ユーザ名 , 商品ID , 登録⽇) … 商品ID 名称単価 P1 はーいお茶 130 P2 午前の紅茶 130 P3 健康麦茶 150 商品 … ユーザ名商品ID 登録日 U1 P1 2024/06/21 U1 P1 2024/06/29 U2 P3 2024/07/01 購⼊希望 …

Slide 22

Slide 22 text

「関連型」の関係スキーマへの変換⼿順 – 多対1関連パターン 1. 関連名を関係スキーマの関係名にする 2. 関連型とつながる実体型のうち「多」の実体型のキーを関連型の主キーとする 3.「1」の実体型のキー&関連型属性をスキーマ属性として追加製造 ( 商品ID , ) 企業名 4. 主キーに関する参照整合性制約を追加する製造.商品ID ⊆ 商品.商品ID 製造.企業名 ⊆ メーカー.企業名メーカー商品商品ID 商品名価格発売⽇製造 TEL email 企業名

Slide 23

Slide 23 text

「関連型」の関係スキーマへの変換⼿順 – 1対1関連パターン 1. 関連名を関係スキーマの関係名にする 2. 関連型とつながる実体型のうち、いずれかの「1」の実体型のキーを関連型の主キーとする 3. 残りの実体型のキー&関連型属性をスキーマ属性として追加ロゴ ( 企業名 , ) 画像ID 4. 主キーに関する参照整合性制約を追加するロゴ.画像ID ⊆ 画像.画像ID 製造.企業名 ⊆ メーカー.企業名メーカー画像画像ID サイズファイルパスロゴ TEL email 企業名

Slide 24

Slide 24 text

Q2: 関係スキーマへの変換（2/4） Q. 以下のER図中の関連型「明細」を関係スキーマに変換せよ．

Slide 25

Slide 25 text

Q2: 関係スキーマへの変換（2/4） A. 明細 ( 注⽂ID, 商品ID, 数量 ) 明細.注⽂ID ⊆ 注⽂.注⽂ID 明細.商品ID ⊆ 商品.商品ID

Slide 26

Slide 26 text

Q3: 関係スキーマへの変換（3/4） Q. 以下のER図中の関連型「購買履歴」を関係スキーマに変換せよ．

Slide 27

Slide 27 text

Q3: 関係スキーマへの変換（3/4） A. 購買履歴 ( 注⽂ID, ユーザ名 ) 購買履歴.注⽂ID ⊆ 注⽂.注⽂ID 購買履歴.ユーザ名 ⊆ ユーザ.ユーザ名

Slide 28

Slide 28 text

実体関連モデルから関係スキーマの導出パターン 1. 実体型 2. 関連型 3. 汎化・継承 4. 弱実体・弱関連型

Slide 29

Slide 29 text

「汎化・継承」の関係スキーマへの変換⼿順 1. 継承先の実体型名を関係スキーマの関係名にする 2. 継承元の実体型のキーを関係スキーマの主キーにする 3. 継承先の実体型の属性を関係スキーマの属性に追加 4. 主キーに関する参照整合性制約を追加するプレミアムユーザ( ユーザ名 , ) IsA プレミアムユーザユーザユーザ名⽒名 email 住所登録⽇登録⽇プレミアムユーザ.ユーザ名 ⊆ ユーザ.ユーザ名

Slide 30

Slide 30 text

Q4: 関係スキーマへの変換（4/4） Q. 以下のER図中の実体型「有料会員」を関係スキーマに変換せよ．

Slide 31

Slide 31 text

Q4: 関係スキーマへの変換（4/4） A. 有料会員 ( ユーザ, 登録⽇ ) 有料会員.ユーザ名 ⊆ ユーザ.ユーザ名

Slide 32

Slide 32 text

実体関連モデルから関係スキーマの導出パターン 1. 実体型 2. 関連型 3. 汎化・継承 4. 弱実体・弱関連型

Slide 33

Slide 33 text

「弱実体型」の関係スキーマへの変換⼿順 1. 弱実体型名を関係スキーマの関係名にする 2. 部分キーおよび弱関連でつながる実体型のキーを関係スキーマの主キーにする 3. 弱実体型の属性を関係スキーマの属性に追加 4. 主キーに関する参照整合性制約を追加する家族 ( ユーザ名 , ⽒名 , ) ユーザユーザ名⽒名 email 住所続柄同居家族⽒名続柄家族.ユーザ名 ⊆ ユーザ.ユーザ名

Slide 34

Slide 34 text

「弱関連型」の関係スキーマへの変換⼿順 1. 弱関連型名を関係スキーマの関係名にする 2. 弱実体型の部分キーおよび弱関連でつながる実体型のキーを関係スキーマの主キーにする 3. 弱関連型の属性を関係スキーマの属性に追加 4. 主キーに関する参照整合性制約を追加する 5. 弱実体型に対応する関係でタプルを把握できる場合，作成したスキーマを削除する同居 ( ユーザ名 , ⽒名 ) ユーザユーザ名⽒名 email 住所同居家族⽒名続柄家族 ( ユーザ名 , ⽒名 , ) 続柄削除

Slide 35

Slide 35 text

更新時異状 2 Update anomaly

Slide 36

Slide 36 text

不完全な関係スキーマはデータベース更新時に不都合が生じる更新時異状実体関連図の質によっては望ましくない関係スキーマが得られることも…

Slide 37

Slide 37 text

3種類の更新時異状更新時異状挿入時異状削除時異状修正時異状

Slide 38

Slide 38 text

例: 架空ショッピングサイトの購買履歴を扱うデータベース購買 ( 購買ID , ユーザ, 購⼊商品, 単価, 数量 ) 購買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 ⼩売店店主の⼭畑さん⼭畑さんたちが作成した関係スキーマこのテーブル1つで購買履歴や商品情報を管理!?

Slide 39

Slide 39 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 NULL NULL たきのこの里山 250 NULL 新商品「たきのこの⾥⼭」をDBに登録するために， (NULL, NULL, たきのこの⾥⼭, 2050, NULL) を追加 × キー制約違反でエラー

Slide 40

Slide 40 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 ユーザU2が購買ID T3ののど飴について払い戻しを求めたので，購買記録を削除 DBから「のど飴の商品情報」が失われる (NULL, NULL, のど飴, 170, NULL)を挿⼊しても⼿遅れ

Slide 41

Slide 41 text

Q5: 異状（2/2） Q. 架空の家具販売店「⼋⾼家具」では、以下のような関係スキーマを⽤いて営業記録をつけはじめた．営業記録 ( ユーザ, 商品 , 連絡先, メーカー, 営業担当 ) 関係表「営業記録」のみを⽤いてデータ管理したときに⽣じる，削除時異状の例を挙げよ．

Slide 42

Slide 42 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 はーいお茶の単価が120円の間違いだったので，データを修正したい 120円 1つの商品の修正なのに購買履歴をすべて修正するのは冗⻑

Slide 43

Slide 43 text

Q6: 異状（1/2） Q. 架空の家具販売店「⼋⾼家具」では、以下のような関係スキーマを⽤いて営業記録をつけはじめた．営業記録 ( ユーザ, 商品 , 連絡先, メーカー, 営業担当 ) 関係表「営業記録」のみを⽤いてデータ管理したときに⽣じる，更新時異状の例を挙げよ．

Slide 44

Slide 44 text

情報無損失分解 3 Information-lossless decomposition

Slide 45

Slide 45 text

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 NULL NULL たきのこの里山 250 NULL × × 120円 ×

Slide 46

Slide 46 text

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 NULL NULL たきのこの里山 250 NULL × × 120円 × なぜ更新時異状が起きるのか？

Slide 47

Slide 47 text

更新時異状が発⽣する理由独⽴して扱える情報を 1つのテーブル内に混在させているから A. 購買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 商品の基礎情報は独⽴して扱えるはず…

Slide 48

Slide 48 text

独⽴した情報を扱えるよう関係テーブルを射影する（1/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 SELECT DISTINCT 購⼊商品, 単価 FROM 購買履歴;

Slide 49

Slide 49 text

独⽴した情報を扱えるよう関係テーブルを射影する（2/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 SELECT DISTINCT 購買ID, ユーザ, 購⼊商品, 数量 FROM 購買履歴;

Slide 50

Slide 50 text

射影されたテーブルを使ったデータベースの更新購買ID ユーザ購入商品数量 T1 U1 はーいお茶 3 T2 U1 きのこの里 2 T3 U2 のど飴 1 T4 U2 きのこの里 1 T5 U3 はーいお茶 1 T6 U4 タケノコの山 1 購入商品単価はーいお茶 130 きのこの里 230 のど飴 170 タケノコの山 200 タケノコの里山 250 関係「購買履歴」関係「商品」

Slide 51

Slide 51 text

Slide 52

Slide 52 text

Slide 53

Slide 53 text

分解後のテーブルを内部結合してみる（1/2）購買ID ユーザ購入商品数量 T1 U1 はーいお茶 3 T2 U1 きのこの里 2 T3 U2 のど飴 1 T4 U2 きのこの里 1 T5 U3 はーいお茶 1 T6 U4 タケノコの山 1 購入商品単価はーいお茶 130 きのこの里 230 のど飴 170 タケノコの山 200 関係「購買履歴」関係「商品」 SELECT * FROM 購買履歴 INNER JOIN 商品 USING (購⼊商品); ⋈ 購⼊商品（内部結合）

Slide 54

Slide 54 text

分解後のテーブルを内部結合してみる（2/2） SELECT * FROM 購買履歴 INNER JOIN 商品 USING (購⼊商品); 購買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 分解前のテーブル

Slide 55

Slide 55 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 56

Slide 56 text

Slide 57

Slide 57 text

情報“損失”分解（1/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 分解前のテーブル

Slide 58

Slide 58 text

情報“損失”分解（1/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 SELECT DISTINCT 購買ID, ユーザ FROM 購買履歴;

Slide 59

Slide 59 text

情報“損失”分解（1/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 SELECT DISTINCT ユーザ, 購⼊商品, 単価, 数量 FROM 購買履歴;

Slide 60

Slide 60 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 61

Slide 61 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 62

Slide 62 text

Slide 63

Slide 63 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 期末試験今後の予定 66