データベースと応用システム：リレーションの正規化

Transcript

1. (c)長岡技術科学大学 電気系 1 データベースと応用システム リレーションの正規化 山本和英 長岡技術科学大学 電気系
2. (c)長岡技術科学大学 電気系 2 正規化とは • 原則：一つの事実を一か所に保管する • 目的：情報の冗長性(＝重複)の排除 – 重複更新の回避 – 矛盾の発生を防止 • 第１、第２、第３、ボイス・コッド、第４、第５正規形 の６段階 • 無損失分解（＝可逆的な分解）を行う • 実際には第３正規形まで正規化する場合が多い
3. (c)長岡技術科学大学 電気系 3 正規化の利点と欠点 （利点） • データの冗長性が排除され、更新時の不整合が 防止できる • 表の意味が明確になり、理解しやすい （欠点） • 表が増える • SQLで結合を多用するため動作が遅くなる
4. (c)長岡技術科学大学 電気系 4 伝票 番号 店舗 ID 店舗 名 店舗 住所 売上 日 売上明細 合計 消 費 税 請求 額 担 当 者 I D 担当者 氏名 商品 コード 商品名 単価 数 量 小計 1 2 長岡 長岡 市.. . 3/5 M1 マウス 100 3 300 3900 50 3950 3 佐藤 C2 HDD 500 6 3000 K4 キー ボード 300 2 600 2 1 新潟 新潟 市.. . 3/6 M2 マウス 200 1 200 1100 30 1130 2 小林 U7 USBメ モリ 300 3 900 3 2 長岡 長岡 市.. . 3/6 L3 無線 LAN 200 1 200 400 10 410 3 佐藤 M1 マウス 100 2 200 最初の表（非正規形）
5. (c)長岡技術科学大学 電気系 5 非正規形→第１正規形 • すべての項目を単一の値（スカラ値）にする – 表中に埋め込まれている表（べき集合）をなくす – 赤部分を行分割する 伝票 番号 店舗 ID 店舗 名 店舗 住所 売上 日 売上明細 合計 消費 税 請求 額 担当 者ID 担当 者氏 名 商品 コー ド 商品 名 単価 数量 小計 1 2 長岡 長岡 市... 3/5 M1 マウス 100 3 300 3900 50 3950 3 佐藤 C2 HDD 500 6 3000 K4 キーボー ド 300 2 600 2 1 新潟 新潟 市... 3/6 M2 マウス 200 1 200 1100 30 1130 2 小林 U7 USBメモ リ 300 3 900 3 2 長岡 長岡 市... 3/6 L3 無線 LAN 200 1 200 400 10 410 3 佐藤 M1 マウス 100 2 200
6. (c)長岡技術科学大学 電気系 6 伝 票 番 号 店 舗 ID 店舗名 店舗 住所 売 上 日 商品 コー ド 商品 名 単価 数 量 小計 合計 消 費 税 請求 額 担 当 者 I D 担当者 氏名 1 2 長岡 長岡 市... 3/5 M1 マウス 100 3 300 3900 50 3950 3 佐藤 1 2 長岡 長岡 市... 3/5 C2 HDD 500 6 3000 3900 50 3950 3 佐藤 1 2 長岡 長岡 市... 3/5 K4 キー ボード 300 2 600 3900 50 3950 3 佐藤 2 1 新潟 新潟 市... 3/6 M2 マウス 200 1 200 1100 30 1130 2 小林 2 1 新潟 新潟 市... 3/6 U7 USBメ モリ 300 3 900 1100 30 1130 2 小林 3 2 長岡 長岡 市... 3/6 L3 無線 LAN 200 1 200 400 10 410 3 佐藤 3 2 長岡 長岡 市... 3/6 M1 マウス 100 2 200 400 10 410 3 佐藤 第１正規形
7. (c)長岡技術科学大学 電気系 7 第１正規形→第２正規形 • 部分関数従属性を排除する • 伝票番号（黄色）→店舗IDなど（青色）は従属して いるのでここだけ分離して【売上】テーブルを作成 伝 票 番 号 店 舗 ID 店舗名 店舗住 所 売 上 日 商品 コー ド 商品 名 単価 数 量 小計 合計 消費 税 請求額 担 当 者 ID 担当者 氏名 1 2 長岡 長岡 市... 3/5 M1 マウス 100 3 300 3900 50 3950 3 佐藤 1 2 長岡 長岡 市... 3/5 C2 HDD 500 6 3000 3900 50 3950 3 佐藤
8. (c)長岡技術科学大学 電気系 8 第１正規形→第２正規形 • 商品コード→商品名、単価も従属しているので分 離して【商品】テーブルを作成 • この結果、【売上ヘッダ】（従来のテーブル）【売 上】【商品】の３枚のテーブルになる 伝 票 番 号 店 舗 ID 店舗名 店舗 住所 売 上 日 商品 コード 商品名 単価 数 量 小計 合計 消 費 税 請求 額 担 当 者 I D 担当者 氏名 1 2 長岡 長岡 市... 3/5 M1 マウス 100 3 300 3900 50 3950 3 佐藤 1 2 長岡 長岡 市... 3/5 C2 HDD 500 6 3000 3900 50 3950 3 佐藤
9. (c)長岡技術科学大学 電気系 9 商品コード 商品名 単価 M1 マウス 100 C2 HDD 500 K4 キーボード 300 M2 マウス 200 U7 USBメモリ 300 L3 無線LAN 200 第２正規形 伝票 番号 店舗 ID 店舗名 店舗住所 売上日 合計 消費 税 請求額 担当 者ID 担当者氏名 1 2 長岡 長岡市... 3/5 3900 50 3950 3 佐藤 2 1 新潟 新潟市... 3/6 1100 30 1130 2 小林 3 2 長岡 長岡市... 3/6 400 10 410 3 佐藤 伝票番号 商品コード 数量 小計 1 M1 3 300 1 C2 6 3000 1 K4 2 600 2 M2 1 200 2 U7 3 900 3 L3 1 200 3 M1 2 200 【売上ヘッダ】 【商品】 【売上明細】
10. (c)長岡技術科学大学 電気系 10 第２正規形→第３正規形 • 推移的関数従属性を排除する – 【売上ヘッダ】から店舗ID→店舗名、店舗住所を分離 – 【売上ヘッダ】から担当者ID→担当者を分離 伝票 番号 店舗 ID 店舗名 店舗住所 売上日 合計 消費 税 請求額 担当 者ID 担当者氏名 1 2 長岡 長岡市... 3/5 3900 50 3950 3 佐藤 2 1 新潟 新潟市... 3/6 1100 30 1130 2 小林 3 2 長岡 長岡市... 3/6 400 10 410 3 佐藤 伝票 番号 店舗 ID 店舗名 店舗住所 売上日 合計 消費 税 請求額 担当者 ID 担当者氏名 1 2 長岡 長岡市... 3/5 3900 50 3950 3 佐藤 2 1 新潟 新潟市... 3/6 1100 30 1130 2 小林 3 2 長岡 長岡市... 3/6 400 10 410 3 佐藤
11. (c)長岡技術科学大学 電気系 11 商品コード 商品名 単価 M1 マウス 100 C2 HDD 500 K4 キーボード 300 M2 マウス 200 U7 USBメモリ 300 L3 無線LAN 200 第３正規形（途中） 伝票 番号 店舗 ID 売上 日 合計 消費 税 請求 額 担当 者ID 1 2 3/5 3900 50 3950 3 2 1 3/6 1100 30 1130 2 3 2 3/6 400 10 410 3 伝票番号 商品コード 数量 小計 1 M1 3 300 1 C2 6 3000 1 K4 2 600 2 M2 1 200 2 U7 3 900 3 L3 1 200 3 M1 2 200 店舗 ID 店舗名 店舗住所 1 新潟 新潟市... 2 長岡 長岡市... 担当 者ID 担当者 氏名 2 小林 3 佐藤 【売上ヘッダ】 【商品】 【売上明細】 【担当者】 【店舗】
12. (c)長岡技術科学大学 電気系 12 第２正規形→第３正規形（続き） • 導出項目（算出できる項目）は削除 – 【売上ヘッダ】の合計、消費税、請求額 – 【売上明細】の小計 伝票 番号 店舗 ID 売上 日 合計 消費 税 請求 額 担当 者ID 1 2 3/5 3900 50 3950 3 2 1 3/6 1100 30 1130 2 3 2 3/6 400 10 410 3 伝票番号 商品コード 数量 小計 1 M1 3 300 1 C2 6 3000 1 K4 2 600 2 M2 1 200 2 U7 3 900 3 L3 1 200 3 M1 2 200 【売上ヘッダ】 【売上明細】
13. (c)長岡技術科学大学 電気系 13 商品コード 商品名 単価 M1 マウス 100 C2 HDD 500 K4 キーボード 300 M2 マウス 200 U7 USBメモリ 300 L3 無線LAN 200 第三正規形（完成） 伝票番号 店舗ID 売上日 担当者ID 1 2 3/5 3 2 1 3/6 2 3 2 3/6 3 伝票番号 商品コード 数量 1 M1 3 1 C2 6 1 K4 2 2 M2 1 2 U7 3 3 L3 1 3 M1 2 店舗 ID 店舗名 店舗住所 1 新潟 新潟市... 2 長岡 長岡市... 担当 者ID 担当者 氏名 2 小林 3 佐藤 【売上明細】 【商品】 【商品ヘッダ】 【店舗】 【担当者】
14. (c)長岡技術科学大学 電気系 14 ボイスコッド正規形 • BCNF(Boyce-Codd Normal Form)とも呼ぶ • 第三正規形から、非キー→キーの関数従属性を排 除したもの – 推移的関数従属は非キー→非キーの間にのみ成り立 つ。よって第三正規形には非キー→キーの関数従属性 が含まれている可能性がある。
15. (c)長岡技術科学大学 電気系 15 ボイスコッド正規形ではない例 • 社員は２つ以上のプロジェ クトに所属することができる • プロジェクトの責任者は複 数いても構わない • 各責任者は一つのプロジェ クトのみ責任を持つ 社 員ID プロ ジェクト 責任 者 1 A 小林 2 A 田中 2 B 佐藤 3 A 小林 3 B 佐藤 4 B 池田
16. (c)長岡技術科学大学 電気系 16 表の分析 • 属性：（社員ID、プロジェクト、責任者） • 主キー – （社員ID、プロジェクト） • 関数従属性 – （社員ID、プロジェクト） → 責任者 – 責任者 → プロジェクト • 部分関数従属、推移的関数従属はないので第三 正規形である。 • 表中に責任者 → プロジェクトの関数従属性を持 つのでボイスコッド正規形ではない。
17. (c)長岡技術科学大学 電気系 17 いつものように表を分解してみる （分解誤りの例） 社員ID プロ ジェクト 1 A 2 A 2 B 3 A 3 B 4 B 責任者 プロ ジェクト 小林 A 田中 A 佐藤 B 池田 B
18. (c)長岡技術科学大学 電気系 18 この表分解の問題点 • 無損失分解でなくなっている – 例：社員ＩＤが２番のプロジェクトＡにおける責任者は、 オリジナルでは「田中」一人のはずが、分解後は「小 林」と「田中」の２名に見える。 • 社員とプロジェクトの関係が多対多関係なのが原 因 • 社員ＩＤ→責任者の関数従属性が保持された分 解を行う必要がある。
19. (c)長岡技術科学大学 電気系 19 ボイスコッド正規形（完成） 社員ID 責任者 1 小林 2 田中 2 佐藤 3 小林 3 佐藤 4 池田 責任者 プロ ジェクト 小林 A 田中 A 佐藤 B 池田 B
20. (c)長岡技術科学大学 電気系 20 第四正規形 • ボイスコッド正規形から複数の多値従属性を分離 したもの。 • 多値従属性とは、あるキーを決めると値の集合が 対応すること。 – Ａ →→ Ｂなどと矢印２個で表現する。 • 表中に複数の多値従属性がある場合に、それぞれ を別の表に分離することで得られる。
21. (c)長岡技術科学大学 電気系 21 第四正規形でない表の例 • 各社員は１つ以上のプ ロジェクトに所属する • 各社員は１つ以上の製 品を担当する • プロジェクトと製品は 全く無関係 社員ＩＤ→→プロジェク ト、社員→→製品ＩＤ なので第四正規形で はない。 社員ID プロ ジェクト 製品 ID 1 A 101 1 A 102 2 A 101 2 B 101 3 A 102 4 B 103
22. (c)長岡技術科学大学 電気系 22 第四正規形（完成） 社員ID 製品 ID 1 101 1 102 2 101 2 101 3 102 4 103 社員ID プロ ジェクト 1 A 1 A 2 A 2 B 3 A 4 B
23. (c)長岡技術科学大学 電気系 23 第五正規形 • 第四正規形であって、複数の多値従属性を持つ 属性間でさらに多値従属性がある場合に、これを 表現したもの • 例：前ページの表で、プロジェクトと製品ＩＤ間に 多値従属性がある場合 • 第五正規形にするには、第四正規形の表に加えて 新たに多値従属している関係を表す表を書けばよ い。
24. (c)長岡技術科学大学 電気系 24 第五正規形（完成） 社員ID 製品 ID 1 101 1 102 2 101 2 101 3 102 4 103 社員ID プロ ジェクト 1 A 1 A 2 A 2 B 3 A 4 B プロ ジェクト 製品 ID A 101 A 102 B 101 B 103