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

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

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

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

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

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

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

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

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

正規化しよう！

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

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

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

非正規形とは • "繰り返しがある"状態 • 普通はExcelでこんな表を作ってしまうけど１行に対して複数行存在するので、このままではＤＢに登録できません

第1正規形とは ＜定義＞“繰り返しがない”こと受注（受注番号、年月日、顧客コード、顧客名、商品コード、商品名、受注数）関数従属図を書こう！

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

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

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

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

第1正規形：関数従属図 受注明細（受注番号、年月日、顧客コード、顧客名、商品コード、商品名、受注数）受注番号年月日顧客名顧客コード商品コード受注数商品名

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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