データーベース設計練習(第1〜3正規化)

1. はじめに

リレーショナルデーターベースを本格的に扱った実務経験がないので、正規化を勉強します。
『達人に学ぶDB設計 徹底指南書 ～初級者で終わりたくないあなたへ』にて正規化を勉強していますので、手を動かして練習してみました。

2. 使用するデーター

使用するデーターは、ChatGPTに作ってもらった以下のデーターとします。
第1正規化以前のものです。

受注ID	顧客名	顧客住所	商品名	商品数量	商品価格	担当者名	担当者部署
1001	佐藤 太郎	東京都新宿区	ペン, ノート	2, 3	100, 200	鈴木 花子	営業部
1002	鈴木 次郎	大阪府大阪市	消しゴム	5	50	田中 一郎	法務部
1003	佐藤 太郎	東京都新宿区	シャープペン, ノート	1, 2	150, 200	鈴木 花子	営業部

3. 第1正規化 (1NF)

定義

すべての属性の値がスカラ値のみを持つこと。

第1正規化前

正規化前の以下の表では、カラム「商品名」「商品数量」「商品価格」が配列として格納されており、1つのセルに2つ以上のデーターが入っている箇所があります。

受注ID	顧客名	顧客住所	商品名	商品数量	商品価格	担当者名	担当者部署
1001	佐藤 太郎	東京都新宿区	ペン, ノート	2, 3	100, 200	鈴木 花子	営業部
1002	鈴木 次郎	大阪府大阪市	消しゴム	5	50	田中 一郎	法務部
1003	佐藤 太郎	東京都新宿区	シャープペン, ノート	1, 2	150, 200	鈴木 花子	営業部

第1正規化後

以下のように列は増えますが、1つのカラムに1つのデーターが格納されます。

受注ID	顧客名	顧客住所	商品名	商品数量	商品価格	担当者名	担当者部署
1001	佐藤 太郎	東京都新宿区	ペン	2	100	鈴木 花子	営業部
1001	佐藤 太郎	東京都新宿区	ノート	3	200	鈴木 花子	営業部
1002	鈴木 次郎	大阪府大阪市	消しゴム	5	50	田中 一郎	法務部
1003	佐藤 太郎	東京都新宿区	シャープペン	1	150	鈴木 花子	営業部
1003	佐藤 太郎	東京都新宿区	ノート	2	200	鈴木 花子	営業部

4. 追加の識別子(ID)を追加

教科書的には既にIDが振ってあることが殆どだと思うのですが、振ってなかったので自分で振ってみます。
以下のIDを振ります。

• 顧客ID
• 商品ID
• 担当者ID
• 部署ID

受注ID	顧客ID	顧客名	顧客住所	商品ID	商品名	商品数量	商品価格	担当者ID	担当者名	部署ID	担当者部署
1001	C001	佐藤 太郎	東京都新宿区	P001	ペン	2	100	S001	鈴木 花子	D001	営業部
1001	C001	佐藤 太郎	東京都新宿区	P002	ノート	3	200	S001	鈴木 花子	D001	営業部
1002	C002	鈴木 次郎	大阪府大阪市	P003	消しゴム	5	50	S002	田中 一郎	D002	法務部
1003	C001	佐藤 太郎	東京都新宿区	P004	シャープペン	1	150	S001	鈴木 花子	D001	営業部
1003	C001	佐藤 太郎	東京都新宿区	P002	ノート	2	200	S001	鈴木 花子	D001	営業部

5. 主キーを決定

実際の主キーの決定は業務フロー（業務ルール）に依存します。
ここでは、主キーによって他のすべてのカラムが一意に決定されるように主キーを選びます。
イメージとしては、「受注ID」だけを主キーにしてしまうと、複数の「商品ID」が単一の「受注ID」結びついてしまう(1対多)ので、「商品ID」も主キーにします。
以下を複合キーとします。

• 受注ID
• 商品ID

6. 第2正規化　(2NF)

定義

第2正規化とは、すべての非キー属性が主キーに対して完全関数従属している状態。

部分関数従属箇所を別テーブルに切り出すことを考えます

第2正規化前

受注ID (pk)	顧客ID	顧客名	顧客住所	商品ID (pk)	商品名	商品数量	商品価格	担当者ID	担当者名	部署ID	担当者部署
1001	C001	佐藤 太郎	東京都新宿区	P001	ペン	2	100	S001	鈴木 花子	D001	営業部
1001	C001	佐藤 太郎	東京都新宿区	P002	ノート	3	200	S001	鈴木 花子	D001	営業部
1002	C002	鈴木 次郎	大阪府大阪市	P003	消しゴム	5	50	S002	田中 一郎	D002	法務部
1003	C001	佐藤 太郎	東京都新宿区	P004	シャープペン	1	150	S001	鈴木 花子	D001	営業部
1003	C001	佐藤 太郎	東京都新宿区	P002	ノート	2	200	S001	鈴木 花子	D001	営業部

完全関数従属

• { 受注ID, 商品Id } → 商品数量

部分関数従属

• { 受注ID } → 顧客Id, 顧客名, 顧客住所
• { 受注ID } → 担当者ID, 担当者名, 部署Id, 担当者部署
• { 商品ID } → 商品名, 商品価格d

第2正規化後

上の3つの部分関数従属を切り出します。

顧客テーブル

顧客ID (pk)	顧客名	顧客住所
C001	佐藤 太郎	東京都新宿区
C002	鈴木 次郎	大阪府大阪市

担当者部署テーブル

担当者ID (pk)	担当者名	部署ID	担当者部署
S001	鈴木 花子	D001	営業部
S002	田中 一郎	D002	法務部

商品テーブル

商品ID (pk)	商品名	商品価格
P001	ペン	100
P002	ノート	200
P003	消しゴム	50
P004	シャープペン	150

発注テーブル

受注ID (pk)	商品ID (pk)	顧客ID	商品数量	担当者ID	部署ID
1001	P001	C001	2	S001	D001
1001	P002	C001	3	S001	D001
1002	P003	C002	5	S002	D002
1003	P004	C001	1	S001	D001
1003	P002	C001	2	S001	D001

7. 第3正規化 (3NF)

定義

すべての非キー属性が、主キーに対して推移的に依存していないこと。

第3正規化前

第2正規化を行なって生じた担当者部署テーブルのテーブルに注目します。
以下のテーブルには推移的関数従属が含まれています。

担当者部署テーブル

担当者ID (pk)	担当者名	部署ID	担当者部署
S001	鈴木 花子	D001	営業部
S002	田中 一郎	D002	法務部

推移的関数従属

• { 担当者ID } → 部署ID → 担当者部署

部署テーブルとして、この推移的関数従属部分を切り出します。

第3正規化後

担当者テーブル

担当者ID	担当者名	部署ID
S001	鈴木 花子	D001
S002	田中 一郎	D002

部署テーブル

部署ID	担当者部署
D001	営業部
D002	法務部