正規化という考え方
リレーショナルデータベースでは、データの重複や矛盾が発生しないように最適化する、要はデータを取り扱いやすいようデータベースの設計を行うことが求められます。
ある会社の社員表を例にします。
社員表
| 社員番号 | 社員名 | 所属部署 |
|---|---|---|
| 0001 | 山田一郎 | 営業部 |
| 0002 | 田中次郎 | 開発部 |
| 0003 | 山本太郎 | 総務部 |
| 0004 | 北田史郎 | 開発部 |
ある日、部署名を変更することになりました（「開発部」→「企画開発部」）。
上記の場合、所属部署の列の「開発部」を全て「企画開発部」に変更しなければなりません。
では、下記の表だった時に部署名の変更があったらどう変更するでしょうか
社員表
| 社員番号 | 社員名 | 所属部署ID |
|---|---|---|
| 0001 | 山田一郎 | 1 |
| 0002 | 田中次郎 | 2 |
| 0003 | 山本太郎 | 3 |
| 0004 | 北田史郎 | 2 |
部署表
| 所属部署ID | 所属部署名 |
|---|---|
| 1 | 営業部 |
| 2 | 開発部 |
| 3 | 総務部 |
部署名の変更があったとしても、部署表の「開発部」の一行だけを「企画開発部」に変更すればよいだけになります。
こちらのほうが圧倒的にデータの矛盾や修正漏れを防ぐことができます。
非正規形
非正規形の表は、繰り返し部分を持っており、各レコードの長さがバラバラです。
例えば以下の受注伝票ですが、
こんな感じに並べてみます(横に長いので2分割しています)。
| 受注No | 受注日付 | 顧客コード | 顧客名称 | 商品コード | 商品名 | 単価 | 数量 |
|---|---|---|---|---|---|---|---|
| 001 | 2021/7/7 | A010 | A出版 | A111 | A4紙 | 50 | 12 |
| 002 | 2021/7/8 | B020 | B工業 | B211 | 鉄板 | 2000 | 10 |
| 003 | 2021/7/9 | C030 | C会社 | C311 | ボールペン | 30 | 40 |
|商品コード|商品名|単価|数量|商品コード|商品名|単価|数量|
|----|----|----|----|----|----|----|----|----|
|A112|A5紙|30|30|A113|B4紙|30|40|
|B212|バネ|100|30|B213|ナット|50|20|
C会社がボールペンしか注文していないので、レコードの長さがバラバラになっています。
原因としては、商品コード・商品名・単価・数量 列が繰り返しになっており、今のままだとリレーショナルデータベースとして管理できません。
そのため、非正規形から第1正規形～第3正規形のカタチにしていきます。
第1正規形
非正規形の表から繰り返し部分を除去したものが第1正規形となります。
| 受注No | 受注日付 | 顧客コード | 顧客名称 | 商品コード | 商品名 | 単価 | 数量 |
|---|---|---|---|---|---|---|---|
| 001 | 2021/7/7 | A010 | A出版 | A111 | A4紙 | 50 | 12 |
| 001 | 2021/7/7 | A010 | A出版 | A112 | A5紙 | 30 | 30 |
| 001 | 2021/7/7 | A010 | A出版 | A113 | B4紙 | 30 | 40 |
| 002 | 2021/7/8 | B020 | B工業 | B211 | 鉄板 | 2000 | 10 |
| 002 | 2021/7/8 | B020 | B工業 | B212 | バネ | 100 | 30 |
| 002 | 2021/7/8 | B020 | B工業 | B213 | ナット | 50 | 20 |
| 003 | 2021/7/9 | C030 | C会社 | C311 | ボールペン | 30 | 40 |
太字にした箇所の情報を補うことでバラバラの長さだったレコードが整いました。
関数従属と部分関数従属
第2正規形と第3正規形では、「関数従属」と「部分関数従属」というワードが出てきます。
先にこの単語についての説明を挟みます。
関数従属
主キーが決定することでレコード(行)を一意に特定することができる、このような関係を関数従属と言います。
| 社員番号(主キー) | 社員名 | 部署 |
|---|---|---|
| 0001 | 山田一郎 | 営業部 |
| 0002 | 田中次郎 | 開発部 |
| 主キーが決まれば社員名や部署が決まる（社員名や部署は社員番号に関数従属している） |
複合キーの場合でも、主キー（複合キー）が決定するとレコードを一意に特定することができる
| 学年(複合キー) | 組(複合キー) | 出席番号(複合キー) | 名前 |
|---|---|---|---|
| 3 | 1 | 22 | 田中一郎 |
| 3 | 1 | 23 | 田中次郎 |
複合キーが決まれば名前が決まる（名前は学年・組・出席番号に関数従属している）
部分関数従属
複合キーの一部の項目だけでレコード(行)が一意に特定できる、このような関係を部分関数従属と言います。
| 受注No(複合キー) | 受注日付 | 顧客コード | 顧客名称 | 商品コード(複合キー) | 商品名 | 単価 | 数量 |
|---|---|---|---|---|---|---|---|
| 001 | 2021/7/7 | A010 | A出版 | A111 | A4紙 | 50 | 12 |
| 001 | 2021/7/7 | A010 | A出版 | A112 | A5紙 | 30 | 30 |
受注Noが決まれば受注日付・顧客コード・顧客名称が決まる
商品コードが決まれば商品名・単価が決まる
第2正規形
第2正規形は、第1正規形の表から部分関数従属しているレコード(行)を一意に切り出したものを指します。
先ほどの第1正規形の表ですが
| 受注No | 受注日付 | 顧客コード | 顧客名称 | 商品コード | 商品名 | 単価 | 数量 |
|---|---|---|---|---|---|---|---|
| 001 | 2021/7/7 | A010 | A出版 | A111 | A4紙 | 50 | 12 |
| 001 | 2021/7/7 | A010 | A出版 | A112 | A5紙 | 30 | 30 |
| 001 | 2021/7/7 | A010 | A出版 | A113 | B4紙 | 30 | 40 |
| 002 | 2021/7/8 | B020 | B工業 | B211 | 鉄板 | 2000 | 10 |
| 002 | 2021/7/8 | B020 | B工業 | B212 | バネ | 100 | 30 |
| 002 | 2021/7/8 | B020 | B工業 | B213 | ナット | 50 | 20 |
| 003 | 2021/7/9 | C030 | C会社 | C311 | ボールペン | 30 | 40 |
「受注No」に部分関数従属しているレコード(行)が受注日付・顧客コード・顧客名称、
「商品コード」に部分関数従属しているレコード(行)が商品名・単価です。
「受注No」に部分関数従属しているレコード(行)が受注日付・顧客コード・顧客名称
こちらを分離させると
| 受注No | 受注日付 | 顧客コード | 顧客名称 |
|---|---|---|---|
| 001 | 2021/7/7 | A010 | A出版 |
| 002 | 2021/7/8 | B020 | B工業 |
| 003 | 2021/7/9 | C030 | C会社 |
先ほどの第1正規形で太字になっていた重複部分がなくなりました。
「商品コード」に部分関数従属しているレコード(行)が商品名・単価です。
次にこちらを分離させると以下のようになります。
| 商品コード | 商品名 | 単価 |
|---|---|---|
| A111 | A4紙 | 50 |
| A112 | A5紙 | 30 |
| A113 | B4紙 | 30 |
| B211 | 鉄板 | 2000 |
| B212 | バネ | 100 |
| B213 | ナット | 50 |
| C311 | ボールペン | 30 |
上記の分離した2つの表と以下の複合キーを含む表、これらが第2正規形の表になります。
| 受注No | 商品コード | 数量 |
|---|---|---|
| 001 | A111 | 12 |
| 001 | A112 | 30 |
| 001 | A113 | 40 |
| 002 | B211 | 10 |
| 002 | B212 | 30 |
| 002 | B213 | 20 |
| 003 | C311 | 40 |
第3正規形
第3正規形は、第2正規形の表から主キー以外に関数従属している列を分離したものを指します。
第2正規形で分離させた以下の表ですが、
| 受注No | 受注日付 | 顧客コード | 顧客名称 |
|---|---|---|---|
| 001 | 2021/7/7 | A010 | A出版 |
| 002 | 2021/7/8 | B020 | B工業 |
| 003 | 2021/7/9 | C030 | C会社 |
顧客コードが決まれば、顧客名称が決まります。
この場合、以下のように分離させてみましょう。
| 受注No | 受注日付 | 顧客コード |
|---|---|---|
| 001 | 2021/7/7 | A010 |
| 002 | 2021/7/8 | B020 |
| 003 | 2021/7/9 | C030 |
| 顧客コード | 顧客名称 |
|---|---|
| A010 | A出版 |
| B020 | B工業 |
| C030 | C会社 |
非正規形から第3正規形へのbefore after
非正規形
横に長いので2分割しています
| 受注No | 受注日付 | 顧客コード | 顧客名称 | 商品コード | 商品名 | 単価 | 数量 |
|---|---|---|---|---|---|---|---|
| 001 | 2021/7/7 | A010 | A出版 | A111 | A4紙 | 50 | 12 |
| 002 | 2021/7/8 | B020 | B工業 | B211 | 鉄板 | 2000 | 10 |
| 003 | 2021/7/9 | C030 | C会社 | C311 | ボールペン | 30 | 40 |
|商品コード|商品名|単価|数量|商品コード|商品名|単価|数量|
|----|----|----|----|----|----|----|----|----|
|A112|A5紙|30|30|A113|B4紙|30|40|
|B212|バネ|100|30|B213|ナット|50|20|
第3正規形
| 受注No | 受注日付 | 顧客コード |
|---|---|---|
| 001 | 2021/7/7 | A010 |
| 002 | 2021/7/8 | B020 |
| 003 | 2021/7/9 | C030 |
| 顧客コード | 顧客名称 |
|---|---|
| A010 | A出版 |
| B020 | B工業 |
| C030 | C会社 |
| 商品コード | 商品名 | 単価 |
|---|---|---|
| A111 | A4紙 | 50 |
| A112 | A5紙 | 30 |
| A113 | B4紙 | 30 |
| B211 | 鉄板 | 2000 |
| B212 | バネ | 100 |
| B213 | ナット | 50 |
| C311 | ボールペン | 30 |
| 受注No | 商品コード | 数量 |
|---|---|---|
| 001 | A111 | 12 |
| 001 | A112 | 30 |
| 001 | A113 | 40 |
| 002 | B211 | 10 |
| 002 | B212 | 30 |
| 002 | B213 | 20 |
| 003 | C311 | 40 |
終わりに
キタミ式イラストIT塾の基本情報技術者の書籍を参考にさせていただきました。
ググるのもいいですが、書籍を使うのもいいですね。
うまいこと両方使って学習していこうかなと思います