Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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

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

1. 実体関連モデルから関係スキーマへの変換
2. 更新時異状
3. 情報無損失分解

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

Y. Yamamoto

July 01, 2024
Tweet

More Decks by Y. Yamamoto

Other Decks in Technology

Transcript

  1. 関係スキーマ 関係 関係スキーマ インスタンス = + 関係の名前と関係がもつ属性,⼀貫性制約に関する情報 (" A! ,

    … , A" , {'! , … , '# }) 記法 関係名 属性 ⼀貫性制約の集合 " A! , … , A" ⼀貫性制約が⾃明 or 考慮しないとき
  2. 典型的な関係データモデル 学生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 可 学⽣ 科⽬ 履修 … … … データの正しさを担保するためにデータを複数の表に分割
  3. ⾮正規形と第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 … 「購買品⽬」の値として 集合を許してしまっている 正規化
  4. 以前作成した実体関連図 商品 商品ID 商品名 価格 発売⽇ ユーザ ユーザ名 ⽒名 email

    住所 購⼊希望 登録⽇ 製造 メーカー 企業名 email TEL ショッピングサイトにおける「ユーザが購⼊希望の商品」 「商品の製造メーカー」の情報の管理
  5. 関係スキーマとインスタンスの対応関係(1/2) 商品ID 名称 単価 P1 はーいお茶 130 P2 午前の紅茶 130

    P3 健康麦茶 150 P1000 きのこの里 200 商品 商品 ( 商品ID , 価格 , 商品名 ) …
  6. Q1: 関係スキーマへの変換(1/4) A. ユーザ ( ユーザ名, ⽒名, 住所, email )

    商品 ( 商品ID, 商品名, 発売⽇, 価格 ) メーカー ( 企業名, TEL, email ) 注文 ( 注⽂ID, 注⽂⽇ )
  7. 「関連型」の関係スキーマへの変換⼿順 – 多対多関連パターン 1. 関連名を関係スキーマの関係名にする 2. 関連型とつながるすべての実体型のキーを 関連型の主キーとする 3. 関連型の属性を関係スキーマの属性に追加

    購入希望( ユーザ名 , 商品ID , ) 登録⽇ 4. 主キーに関する参照整合性制約を追加する 購⼊希望.ユーザ名 ⊆ ユーザ.ユーザ名 購⼊希望.商品ID ⊆ 商品.商品ID 商品 ユーザ ユーザ名 ⽒名 email 住所 購⼊希望 登録⽇ 商品名 価格 発売⽇ 商品ID 参照制約
  8. 関係スキーマとインスタンスの対応関係(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 購⼊希望 …
  9. 「関連型」の関係スキーマへの変換⼿順 – 多対1関連パターン 1. 関連名を関係スキーマの関係名にする 2. 関連型とつながる実体型のうち「多」の実体型のキーを 関連型の主キーとする 3.「1」の実体型のキー&関連型属性をスキーマ属性として追加 製造

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

    ロゴ ( 企業名 , ) 画像ID 4. 主キーに関する参照整合性制約を追加する ロゴ.画像ID ⊆ 画像.画像ID 製造.企業名 ⊆ メーカー.企業名 メーカー 画像 画像ID サイズ ファイルパス ロゴ TEL email 企業名
  11. 「弱関連型」の関係スキーマへの変換⼿順 1. 弱関連型名を関係スキーマの関係名にする 2. 弱実体型の部分キーおよび弱関連でつながる 実体型のキーを関係スキーマの主キーにする 3. 弱関連型の属性を関係スキーマの属性に追加 4. 主キーに関する参照整合性制約を追加する

    5. 弱実体型に対応する関係でタプルを把握できる場合, 作成したスキーマを削除する 同居 ( ユーザ名 , ⽒名 ) ユーザ ユーザ名 ⽒名 email 住所 同居 家族 ⽒名 続柄 家族 ( ユーザ名 , ⽒名 , ) 続柄 削除
  12. 例: 架空ショッピングサイトの購買履歴を扱うデータベース 購買 ( 購買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つで購買履歴や商品情報を管理!?
  13. 挿⼊時異状 購買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) を追加 × キー制約違反でエラー
  14. 削除時異状 購買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)を挿⼊しても⼿遅れ
  15. Q5: 異状(2/2) Q. 架空の家具販売店「⼋⾼家具」では、以下のような 関係スキーマを⽤いて営業記録をつけはじめた. 営業記録 ( ユーザ, 商品 ,

    連絡先, メーカー, 営業担当 ) 関係表「営業記録」のみを⽤いてデータ管理したと きに⽣じる,削除時異状の例を挙げよ.
  16. 修正時異状 購買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つの商品の修正なのに購買履歴をすべて修正するのは冗⻑
  17. Q6: 異状(1/2) Q. 架空の家具販売店「⼋⾼家具」では、以下のような 関係スキーマを⽤いて営業記録をつけはじめた. 営業記録 ( ユーザ, 商品 ,

    連絡先, メーカー, 営業担当 ) 関係表「営業記録」のみを⽤いてデータ管理したと きに⽣じる,更新時異状の例を挙げよ.
  18. 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円 ×
  19. 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円 × なぜ更新時異状が起きるのか?
  20. 更新時異状が発⽣する理由 独⽴して扱える情報を 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 商品の基礎情報は独⽴して扱えるはず…
  21. 独⽴した情報を扱えるよう関係テーブルを射影する(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 購買履歴;
  22. 独⽴した情報を扱えるよう関係テーブルを射影する(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 購買履歴;
  23. 射影されたテーブルを使ったデータベースの更新 購買ID ユーザ 購入商品 数量 T1 U1 はーいお茶 3 T2

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

    U1 きのこの里 2 T3 U2 のど飴 1 T4 U2 きのこの里 1 T5 U3 はーいお茶 1 T6 U4 タケノコの山 1 購入商品 単価 はーいお茶 130 きのこの里 230 のど飴 170 タケノコの山 200 タケノコの里山 250 関係「購買履歴」 関係「商品」 うまくテーブルを分解すれば更新時異状が発⽣しない 削除時異状ナシ 挿⼊時異状ナシ 120 修正時異状なし
  25. 射影されたテーブルを使ったデータベースの更新 購買ID ユーザ 購入商品 数量 T1 U1 はーいお茶 3 T2

    U1 きのこの里 2 T3 U2 のど飴 1 T4 U2 きのこの里 1 T5 U3 はーいお茶 1 T6 U4 タケノコの山 1 購入商品 単価 はーいお茶 130 きのこの里 230 のど飴 170 タケノコの山 200 タケノコの里山 250 関係「購買履歴」 関係「商品」 うまくテーブルを分解すれば更新時異状が発⽣しない 削除時異状ナシ 挿⼊時異状ナシ 120 修正時異状なし
  26. 分解後のテーブルを内部結合してみる(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 (購⼊商品); ⋈ 購⼊商品 (内部結合)
  27. 分解後のテーブルを内部結合してみる(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 分解前のテーブル
  28. 情報無損失分解 購買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更新の影響を極⼒⼩さくできる
  29. 情報無損失分解 購買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更新の影響を極⼒⼩さくできる 分解の仕⽅がいい加減だと 情報“損失”分解になりえる…
  30. 情報“損失”分解(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 分解前のテーブル
  31. 情報“損失”分解(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 購買履歴;
  32. 情報“損失”分解(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 購買履歴;
  33. 情報“損失”分解(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 (ユーザ);
  34. 情報“損失”分解(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 を 「ユーザ」で結合した表 関係「購買」には なかったレコード が増えている 分解の軸となった「ユーザ」は、その値決まっても それに対応する値(購入商品など)が一意に決まらない 失敗要因
  35. 情報“損失”分解(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 を 「ユーザ」で結合した表 関係「購買」には なかったレコード が増えている 分解の軸となった「ユーザ」は、その値決まっても それに対応する値(購入商品など)が一意に決まらない 失敗要因 どうすれば情報無損失分解できる?
  36. 回 実施日 トピック 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