実践!DB逆設計 ~レシートからER図を起こす~ 2013/7/20 SAT SQLWrold 遥佐保 自己紹介 名前と経歴を言う 出没コミュニティ Room metro(東京・大阪) ExceptionalC++読書会(大阪) SQLWorld (大阪) Microsoft MVP for Client App Dev [Jan,2010 - Dec,2013] 本日の目的 DB設計の基本を理解する 第3正規化まで出来るようになる ER図が書けるようになる Topics 正規化は必要? 正規化の種類 正規化の考え方 関数従属 第1正規形 第2正規形 第3正規形 E-R図 演習1 演習2 スーパータイプ、サ ブタイプ 演習3 演習4(レシート) まとめ 正規化ってどんな時に必要なの? 資格管理のテーブルがあるとします 社員(社員コード、社員名、 資格1、資格2、資格3) 問題点1: 3つしか資格管理ができない 正規化ってどんな時に必要なの? 社員(社員コード、社員名、 資格1、資格2、資格3) 問題点2: SQLが煩雑になる 例)DB資格を持っている人を抽出 SELECT 資格1, 資格2, 資格3 FROM 社員 WHERE 資格1=‘DB’,資格2=‘DB’,資格3=‘DB’, 正規化ってどんな時に必要なの? 社員(社員コード、社員名、 資格1、資格2、資格3) 問題点3: 領域に無駄が多い 社員コード 資格1 001 DB 002 NW DB 003 SW NW 004 資格2 資格3 DB 正規化ってどんな時に必要なの? 社員(社員コード、社員名) 社員資格(社員コード、資格) 社員コード 資格1 001 DB 002 NW 002 DB 汎用性UP、簡易なSQL、領域削減、 更新時異常の防止など、色んなメリット があります 正規化しよう! 正規形の種類 非正規形 第1正規形 ここまでで良いのよ 第2正規形 第3正規形 ボイスコッド正規形 第4正規形 第5正規形 正規形の考え方 「第3正規形である」ということは… 「第1正規形の条件も第2正規形の条件も 満たしており、かつ第3正規形の条件を満 たしている」ということ 第1正規形 第2正規形 第3正規形 正規化の手順 非正規形 繰り返しの排除 第1正規形 部分関数従属の排除 第2正規形 推移的関数従属の排除 第3正規形 非正規形とは "繰り返しがある"状態 普通はExcelでこんな表を作ってしまうけど 1行に対して複数行存在するので、このま まではDBに登録できません 商品 商品名 コード 受注数 S001 Nexus7 1 2013/7/20 A0753 山田太郎 S002 iPhone5 3 MediasW 1 Nexus7 2 受注番号 年月日 T0100 顧客 コード 顧客名 S003 T0101 2013/7/20 C0231 田中玲子 S001 第1正規形とは <定義>“繰り返しがない”こと 受注番号 年月日 顧客 コード 顧客名 商品 商品名 コード 受注数 T0100 2013/7/20 A0753 山田太郎 S001 Nexus7 1 T0100 2013/7/20 A0753 山田太郎 S002 iPhone5 3 T0100 2013/7/20 A0753 山田太郎 S003 MediasW 1 T0101 2013/7/20 C0231 田中玲子 S001 Nexus7 2 受注 ( 受注番号、年月日、顧客コード、 顧客名、商品コード 、商品名、受注数) 関数従属図を書こう! 関数従属 顧客コード 顧客名 「顧客コード」が決まれば、 「顧客名」が特定出来る →「顧客名は顧客コードに関数従属する」 関数従属の用語 CはAに部分関数従属する Dは{A,B}に完全関数従属する A C B D 関数従属の用語 GはEに推移的関数従属する (ただし、F→Eでないとき) E F G 関数従属の用語 候補キー 行を一意に特定できる属性または属性の組 み合わせ 不要な属性は含まない 第1正規形:関数従属図 受注番号 年月日 顧客 コード 顧客名 商品 商品名 コード 受注数 T0100 2013/7/20 A0753 山田太郎 S001 Nexus7 1 T0100 2013/7/20 A0753 山田太郎 S002 iPhone5 3 T0100 2013/7/20 A0753 山田太郎 S003 MediasW 1 T0101 2013/7/20 C0231 田中玲子 S001 Nexus7 2 受注明細 ( 受注番号、年月日、顧客コード、 顧客名、商品コード 、商品名、受注数) 受注番号 商品コード 年月日 顧客コード 受注数 商品名 顧客名 正規化の手順 非正規形 繰り返しの排除 第1正規形 部分関数従属の排除 第2正規形 第3正規形 推移的関数従属の排除 第2正規形とは <定義> 第1正規形であること 全ての非キー属性が候補キーに完全関数 従属していること (もしくは部分関数従属していないこと) 受注番号 商品コード 年月日 顧客コード 受注数 商品名 ここやで! 顧客名 第2正規形に分解する 部分関数従属を外に出した 受注番号 商品コード 受注数 商品コード 商品名 受注番号 年月日 顧客コード 顧客名 第2正規形のテーブル 受注明細 ( 受注番号、商品コード 、受注数) 商品 ( 商品コード、商品名) 受注(受注番号、年月日、顧客コード、顧客名) 受注番号 商品コード 受注数 商品コード 商品名 受注番号 年月日 顧客コード 顧客名 正規化の手順 非正規形 繰り返しの排除 第1正規形 部分関数従属の排除 第2正規形 第3正規形 推移的関数従属の排除 第3正規形とは <定義> 第2正規形であること 推移的関数従属がないこと 受注番号 商品コード 受注番号 受注数 商品コード ここやで! 年月日 顧客コード 商品名 顧客名 第3正規形に分解する 推移的関数従属を外に出した 受注番号 商品コード 受注数 商品コード 商品名 受注番号 年月日 顧客コード 顧客コード 顧客名 第3正規形のテーブル 受注明細 ( 受注番号、商品コード 、受注数) 商品 ( 商品コード、商品名) 受注(受注番号、年月日、顧客コード) 顧客 (顧客コード、顧客名) 受注番号 商品コード 受注数 商品コード 商品名 受注番号 年月日 顧客コード 顧客コード 顧客名 E-R図 正規化した後に、E-R図を作成する *外部キーが無い場合、関連が無い *主キーから外部キーへ線を引く(1対多) *外部キーの数だけ線が存在する 資格 ( 資格番号、資格名、受験価格 ) 生徒 ( 生徒番号、生徒名 ) 試験結果 ( 資格番号、生徒番号、得点 ) 資格 試験結果 生徒 演習1 E-R図を作成する 商品(商品番号、商品名、価格) 納品(商品番号、顧客番号、納品数) ※手順1:主キー、外部キーはどれ? 演習1 商品(商品番号、商品名、価格) 納品(商品番号、顧客番号、納品数) 主キー:実線 外部キー:破線 ※手順2: 2つのテーブルから、3つめのテーブ ルが考察できるよ! 演習1 商品(商品番号、商品名、価格) 納品(商品番号、顧客番号、納品数) 顧客(顧客番号、顧客名、……) ※手順3: あとは主キーから外部キーへ 線を引くだけ! 演習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候補キーを上げましょう ポイント:まず第何正規形かを考える → その後、現在の関数スキーマを考える いきなり第3正規化手順である「推移的関 数従属を排除」などしてはいけない A C B D 演習4 レシートからER図を作成する 実際に、レシートを見て 正規化&E-R図を作成しましょう! まとめ 第3正規化までは出来るようになろう! 第1、第2、第3正規化の定義 ER図の書き方は、主キーから外部キーを 引くだけ! レシートからのDB起こしは、トップダウ ン or ボトムアップで!
© Copyright 2025 ExpyDoc