実践！DB逆設計 ～レシートからER図を起こす～

Presentation on theme: "実践！DB逆設計 ～レシートからER図を起こす～"— Presentation transcript:

1 実践！DB逆設計 ～レシートからER図を起こす～
2013/7/20 SAT SQLWrold 遥佐保

2 自己紹介 名前と経歴を言う 出没コミュニティ
Room metro（東京・大阪） ExceptionalC++読書会（大阪） SQLWorld （大阪） Microsoft MVP for Client App Dev [Jan, Dec,2013]

3 本日の目的 DB設計の基本を理解する 第３正規化まで出来るようになる ER図が書けるようになる

4 Topics 正規化は必要？ 正規化の種類 正規化の考え方 関数従属 第1正規形 第2正規形 第3正規形 E-R図 演習１ 演習２
スーパータイプ、サブタイプ 演習3 演習4(レシート) まとめ

5 正規化ってどんな時に必要なの？ 資格管理のテーブルがあるとします 社員（社員コード、社員名、 資格１、資格２、資格３） 問題点１：
　　　　資格１、資格２、資格３） 問題点１： 　３つしか資格管理ができない

6 正規化ってどんな時に必要なの？ 社員（社員コード、社員名、 資格１、資格２、資格３） 問題点２： ＳＱＬが煩雑になる
　　　　資格１、資格２、資格３） 問題点２： 　ＳＱＬが煩雑になる 　例）ＤＢ資格を持っている人を抽出 SELECT 資格１, 資格２, 資格３ FROM 社員 　WHERE 資格１=‘DB’,資格2=‘DB’,資格3=‘DB’,

7 正規化ってどんな時に必要なの？ 社員（社員コード、社員名、 資格１、資格２、資格３） 問題点３： 領域に無駄が多い 社員コード 資格１
　　　　資格１、資格２、資格３） 問題点３： 　領域に無駄が多い 社員コード 資格１ 資格２ 資格３ 001 DB 002 NW 003 SW 004

8 正規化ってどんな時に必要なの？ 社員（社員コード、社員名） 社員資格（社員コード、資格） 汎用性ＵＰ、簡易なＳＱＬ、領域削減、
更新時異常の防止など、色んなメリットがあります 社員コード 資格１ 001 DB 002 NW

9 正規化しよう！

10 正規形の種類 非正規形 第1正規形 第2正規形 第3正規形 ボイスコッド正規形 第4正規形 第5正規形 ここまでで良いのよ

11 正規形の考え方 「第3正規形である」ということは…
「第1正規形の条件も第2正規形の条件も満たしており、かつ第3正規形の条件を満たしている」ということ 第1正規形 第2正規形 第3正規形

12 正規化の手順 非正規形 繰り返しの排除 第1正規形 部分関数従属の排除 第２正規形 推移的関数従属の排除 第３正規形

13 非正規形とは "繰り返しがある"状態 普通はExcelでこんな表を作ってしまうけど１行に対して複数行存在するので、このままではＤＢに登録できません 受注番号 年月日 顧客コード 顧客名 商品コード 商品名 受注数 T0100 2013/7/20　 A0753 山田太郎 S001 Nexus7 1 S002 iPhone5 3 S003 MediasW T0101 2013/7/20 C0231 田中玲子 2

14 第1正規形とは ＜定義＞“繰り返しがない”こと 関数従属図を書こう！ 受注 （ 受注番号、年月日、顧客コード、
顧客名 商品コード 商品名 受注数 T0100 2013/7/20　 A0753 山田太郎 S001 Nexus7 1 2013/7/20 S002 iPhone5 3 S003 MediasW T0101 C0231 田中玲子 2 受注 （ 受注番号、年月日、顧客コード、 顧客名、商品コード 、商品名、受注数） 関数従属図を書こう！

15 関数従属 顧客コード 顧客名 「顧客コード」が決まれば、 「顧客名」が特定出来る →「顧客名は顧客コードに関数従属する」

16 関数従属の用語 CはAに部分関数従属する Dは{A,B}に完全関数従属する A C B D

17 関数従属の用語 GはEに推移的関数従属する （ただし、F→Eでないとき） E F G

18 関数従属の用語 候補キー 行を一意に特定できる属性または属性の組み合わせ 不要な属性は含まない

19 第1正規形：関数従属図 受注番号 年月日 顧客コード 顧客名 商品コード 受注数 商品名 受注明細 （ 受注番号、年月日、顧客コード、
T0100 2013/7/20　 A0753 山田太郎 S001 Nexus7 1 2013/7/20 S002 iPhone5 3 S003 MediasW T0101 C0231 田中玲子 2 受注明細 （ 受注番号、年月日、顧客コード、 顧客名、商品コード 、商品名、受注数） 受注番号 年月日 商品コード 顧客コード 顧客名 受注数 商品名

20 正規化の手順 非正規形 繰り返しの排除 第1正規形 部分関数従属の排除 第２正規形 推移的関数従属の排除 第３正規形

21 第2正規形とは ＜定義＞ 第1正規形であること 全ての非キー属性が候補キーに完全関数従属していること （もしくは部分関数従属していないこと）
ここやで！ 受注番号 年月日 商品コード 顧客コード 顧客名 受注数 商品名

22 第2正規形に分解する 部分関数従属を外に出した 受注番号 商品コード 商品コード 受注数 商品名 受注番号 年月日 顧客名 顧客コード

23 第2正規形のテーブル 受注明細 （ 受注番号、商品コード 、受注数） 商品 （ 商品コード、商品名）
受注（受注番号、年月日、顧客コード、顧客名） 受注番号 商品コード 受注数 商品コード 商品名 受注番号 年月日 顧客名 顧客コード

24 正規化の手順 非正規形 繰り返しの排除 第1正規形 部分関数従属の排除 第２正規形 推移的関数従属の排除 第３正規形

25 第3正規形とは ＜定義＞ 第2正規形であること 推移的関数従属がないこと 受注番号 商品コード 受注数 商品コード 商品名 ここやで！
年月日 顧客名 顧客コード

26 第3正規形に分解する 推移的関数従属を外に出した 受注番号 商品コード 受注数 商品コード 商品名 受注番号 年月日 顧客コード 顧客コード
顧客名

27 第3正規形のテーブル 受注明細 （ 受注番号、商品コード 、受注数） 商品 （ 商品コード、商品名） 受注（受注番号、年月日、顧客コード）
顧客 （顧客コード、顧客名） 受注番号 商品コード 受注数 商品コード 商品名 受注番号 年月日 顧客コード 顧客コード 顧客名

28 Ｅ－Ｒ図 正規化した後に、Ｅ－Ｒ図を作成する ＊外部キーが無い場合、関連が無い ＊主キーから外部キーへ線を引く（１対多）
＊外部キーの数だけ線が存在する 資格 （ 資格番号、資格名、受験価格 ） 生徒 （ 生徒番号、生徒名 ） 試験結果 （ 資格番号、生徒番号、得点 ） 資格 試験結果 生徒

29 演習１　E-R図を作成する 商品（商品番号、商品名、価格） 納品（商品番号、顧客番号、納品数） ※手順１：主キー、外部キーはどれ？

30 演習１ 商品（商品番号、商品名、価格） 納品（商品番号、顧客番号、納品数） 主キー：実線 外部キー：破線 ※手順２：
　２つのテーブルから、３つめのテーブルが考察できるよ！

31 演習1 商品（商品番号、商品名、価格） 納品（商品番号、顧客番号、納品数） 顧客（顧客番号、顧客名、……） ※手順３：
　あとは主キーから外部キーへ 　線を引くだけ！

32 演習１ 商品（商品番号、商品名、価格） 納品（商品番号、顧客番号、納品数） 顧客（顧客番号、顧客名、……） 商品 納品 顧客

33 演習2 外部キーはどれだっ！？ 受注明細 （ 受注番号、商品コード 、受注数） 商品 （ 商品コード、商品名）
受注 （受注番号、年月日、顧客コード） 顧客 （顧客コード、顧客名） 受注番号 商品コード 受注数 商品コード 商品名 受注番号 年月日 顧客コード 顧客コード 顧客名

34 演習2 主キー(1)から外部キー(多)へ線を引く
受注明細 （ 受注番号、商品コード 、受注数） 商品 （ 商品コード、商品名） 受注 （受注番号、年月日、顧客コード） 顧客 （顧客コード、顧客名） 受注 受注明細 顧客 商品

35 正規化アプローチ 関数従属から考えるのはボトムアップ ER図から考えるのはトップダウン どちらで考えるかは、好み

36 スーパータイプ、サブタイプ is-a関係 自動車 Part-of関係 乗用車 バス 自動車 ハンドル タイヤ 乗用車 is a 自動車

37 スーパータイプ、サブタイプ is-a関係、part-of関係を保ちつつ排他的であること 自動車 乗用車 バス

38 スーパータイプなどの例 受注 受注明細 顧客 商品 顧客種別 お得意様 一般

39 これが第3正規化までの全てです！

40 演習３候補キーを上げましょう ポイント：まず第何正規形かを考える いきなり第3正規化手順である「推移的関数従属を排除」などしてはいけない A
→ その後、現在の関数スキーマを考える いきなり第3正規化手順である「推移的関数従属を排除」などしてはいけない A C B D

41 実際に、レシートを見て 正規化＆Ｅ－Ｒ図を作成しましょう！
演習4 レシートからER図を作成する 実際に、レシートを見て 正規化＆Ｅ－Ｒ図を作成しましょう！

42 まとめ 第3正規化までは出来るようになろう！ ER図の書き方は、主キーから外部キーを引くだけ！
第1、第2、第3正規化の定義 ER図の書き方は、主キーから外部キーを引くだけ！ レシートからのDB起こしは、トップダウン or ボトムアップで！