Transcript 商品コード

実践！DB逆設計
～レシートからER図を起こす～
2013/7/20 SAT
SQLWrold 遥佐保
自己紹介
名前と経歴を言う
出没コミュニティ
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’,
正規化ってどんな時に必要なの？
社員（社員コード、社員名、
資格１、資格２、資格３）
問題点３：
領域に無駄が多い
社員コード
資格１
001
DB
002
NW
DB
003
SW
NW
004
資格２
資格３
DB
正規化ってどんな時に必要なの？
社員（社員コード、社員名）
社員資格（社員コード、資格）
社員コード
資格１
001
DB
002
NW
002
DB
汎用性ＵＰ、簡易なＳＱＬ、領域削減、
更新時異常の防止など、色んなメリットがあり
ます
正規化しよう！
正規形の種類
非正規形
第1正規形
ここまでで良いのよ
第2正規形
第3正規形
ボイスコッド正規形
第4正規形
第5正規形
正規形の考え方
「第3正規形である」ということは…
「第1正規形の条件も第2正規形の条件も満
たしており、かつ第3正規形の条件を満たして
いる」ということ
第1正規形
第2正規形
第3正規形
正規化の手順
非正規形
繰り返しの排除
第1正規形
部分関数従属の排除
第２正規形
推移的関数従属の排除
第３正規形
非正規形とは
"繰り返しがある"状態
普通はExcelでこんな表を作ってしまうけど１行
に対して複数行存在するので、このままではＤ
Ｂに登録できません
受注番号 年月日
T0100
T0101
2013/7/20
2013/7/20
顧客コー
顧客名
コード
A0753
C0231
商品
コード
商品名
受注数
S001
Nexus7
1
山田太郎 S002
iPhone5
3
S003
MediasW
1
Nexus7
2
田中玲子 S001
第1正規形とは
＜定義＞“繰り返しがない”こと
受注番号 年月日
顧客コー
顧客名
コード
T0100
2013/7/20
A0753
T0100
2013/7/20
T0100
T0101
商品
コード
商品名
受注数
山田太郎 S001
Nexus7
1
A0753
山田太郎 S002
iPhone5
3
2013/7/20
A0753
山田太郎 S003
MediasW
1
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
T0100
2013/7/20
T0100
T0101
商品
コード
商品名
受注数
山田太郎 S001
Nexus7
1
A0753
山田太郎 S002
iPhone5
3
2013/7/20
A0753
山田太郎 S003
MediasW
1
2013/7/20
C0231
田中玲子 S001
Nexus7
2
受注明細 （ 受注番号、年月日、顧客コード、
顧客名、商品コード 、商品名、受注数）
受注番号
商品コード
年月日
顧客コード
受注数
商品名
顧客名
正規化の手順
非正規形
繰り返しの排除
第1正規形
部分関数従属の排除
第２正規形
推移的関数従属の排除
第３正規形
第2正規形とは
＜定義＞
第1正規形であること
全ての非キー属性が候補キーに完全関数従
属していること
（もしくは部分関数従属していないこと）
受注番号
商品コード
年月日
顧客コード
受注数
商品名
ここやで！
顧客名
第2正規形に分解する
部分関数従属を外に出した
受注番号
商品コード
受注数
商品コード
商品名
受注番号
年月日
顧客コード
顧客名
第2正規形のテーブル
受注明細 （ 受注番号、商品コード 、受注数）
商品 （ 商品コード、商品名）
受注（受注番号、年月日、顧客コード、顧客名）
受注番号
商品コード
受注数
商品コード
商品名
受注番号
年月日
顧客コード
顧客名
正規化の手順
非正規形
繰り返しの排除
第1正規形
部分関数従属の排除
第２正規形
推移的関数従属の排除
第３正規形
第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正規化手順である「推移的関数
従属を排除」などしてはいけない
A
C
B
D
演習4 レシートからER図を作成する
実際に、レシートを見て
正規化＆Ｅ－Ｒ図を作成しましょう！
まとめ
第3正規化までは出来るようになろう！
第1、第2、第3正規化の定義
ER図の書き方は、主キーから外部キーを引く
だけ！
レシートからのDB起こしは、トップダウン or
ボトムアップで！