データベーススペシャリスト試験合格への道 第4回
「正規化」は
データベーススペシャリストの試験では
100%必要な知識になります。
正規形１~３の明確な違いや
関数従属性，完全関数従属，推移的関数従属の
言葉の意味があいまいな方は必ず理解しましょう！
正規化の目的
正規化の目的はデータの不整合の発生を防ぐことです。
不整合とは？
不整合とは，例えば
商品表に商品コードAの商品名は「アンパン」
になっているのに，
売上表の商品コードAの商品名は「コッペパン」
になってる状態です。
商品コードAに対する商品名を
「商品表」にも「売上表」にも
持っているためこういった不整合が起こります。
「１事実１か所」が正規化の絶対的テーマ！！
正規化では「1事実1か所」を徹底的にやっていきます。
先ほどの例では商品表と売上表に商品名が存在するため
不整合が発生していました。
そのようなことが起きないように
正規化では商品コードを商品表と売上テーブルに持たせて
商品名は商品表のみで管理するといった
データベースの設計をする手法の事です。
正規化の種類
正規化の種類にはいろいろな状態があります。
- 非正規形 DBMSでは扱えない
- 第1正規形 第１からボイスコッドまでは関数従属に着目
- 第2正規形
- 第3正規形 ここが最終形態
- ボイスコッド正規形 正規化を開発したコッドさんの名前で命名
- 第4正規形 多値従属性に着目
- 第5正規形 結合従属性に着目
非正規形はDBMSでは扱えない状態を指します。
第1正規形からボイスコッド正規形は関数従属に着目して
正規化をします。
正規化の世界では基本的には第3正規形までしかしません。
私も職場で第3正規形より先の正規形はしたことがありません。
正規化とは理論なため，学術的に考えると
ボイスコッド正規形や第４正規形，第5正規形が存在するため
基本的には第3正規形までの理解でOKです。
用語の解説
関数従属性
関数従属性とは
「〇〇と決めれば××と1つに決まる」
状態のことを言います。
例）社員番号が決まれば氏名がきまる
これは「氏名」が社員番号に関係従属する
と表現します。
試験では
「社員番号→氏名」
と書いてあると
「社員番号が決まれば氏名がきまる」状態であり
「氏名」が社員番号に関係従属する状態を表している事になります。
完全関数従属
部分関数従属しない状態のことを
完全関数従属していると表現します。
「部分関数従属」とは，
例えば「社員番号と売上番号が決まれば氏名が決まる」という
表がある場合，実は社員番号が決まるだけで
「氏名」って決まりますよねーという状態になっていることを言います。
この状態のことを部分関係従属していると表現します。
この状態から「氏名」を取り除き，
部分関係従属しているものがなくなった時に
完全関数従属している状態といいます。
推移的関数従属
推移的関数従属とは，Aが決まるとBが決まる，
Bが決まればCが決まる状態の時，Aが決まれば，
Cが決まることになりますよね。
こういった推移的に関係性がわかるものの事を言います。
例えば
社員番号が決まれば部門が決まる，
部門が決まれば部門名が決まる時，
部門名は社員番号に推移的関数従属している状態と表現します。
非正規形
見分け方
表の中に繰り返し項目がある
例）購入表が下記のようにある場合
購入
| 購入No | 顧客No | 顧客名 | 購入日 | 商品No | 商品名 | 数量 |
| 1 | 8 | 小野 | 2017/1/1 | 5 | ボール | 1 |
| 6 | スパイク | 2 |
商品No，商品名，数量が繰り返し項目となっている。
購入No，顧客No，顧客名，購入日の2行目が空白（DB実装時はNULL）
のため，表として成り立っていない。
実際に購入表をデータベースのテーブルにする場合は
購入Noが主キーになる為，主キーをNullにすることができない。
よって，非正規形はデータベースのテーブルとして
物理的に実装できない状態となる。
第1正規形
非正規形から第1正規化をすると第1正規形となる
第1正規形の条件
- 繰り返し項目を持たない
- 表のすべての項目が原始的である
第1正規化：表の分割無しでやる方法
先ほどの非正規形の表を第1正規化すると次の通りとなる
購入
| 購入No | 顧客No | 顧客名 | 購入日 | 商品No | 商品名 | 数量 |
| 1 | 8 | 小野 | 2017/1/1 | 5 | ボール | 1 |
| 1 | 8 | 小野 | 2017/1/1 | 6 | スパイク | 2 |
表を分けないパターンの場合は，先ほどの2行目の空白箇所に
同じ値を入れることで表を完成させる。
これであればDBのテーブルにも主キーなNullにならないため
実装は可能となる。
第1正規化：表を分割する方法
購入（※候補キーは太文字）
| 購入No | 顧客No | 顧客名 | 購入日 |
| 1 | 8 | 小野 |
2017/1/1 |
購入明細（※候補キーは太文字）
| 購入No | 商品No | 商品名 | 数量 |
| 1 | 5 | ボール | 1 |
| 1 | 6 | スパイク | 2 |
手順１：繰り返し項目を購入明細表として別の表に分ける。
手順２：購入明細表に分けた後も購入表との紐づけがわかるように
購入Noを購入明細表に入れる
手順３：購入Noと商品Noを候補キーとする
候補キー選定の理由
・購入Noだけがキーだと商品を1行しか登録できなくなる
・商品Noだけがキーだと異なる購入Noで同じ商品Noだ登録できなくなる
（一度しか購入でいない商品などありえない）
更新時異状（異常）
更新時異状とは，正規化が正しく行われていないためにおこる
更新時の問題の事です。
第1正規形ではどのような問題が発生するかを理解することで
第2正規形，第3正規形へと進化させる理由となります。
正規化が行われていないために発生する
更新時異状は，更新時に発生する者なので
表への追加，更新，削除を行ったときに発生します。
したがって，参照しかしない表の場合は
正規化されていなくても問題になりません。
例えば全国のコンビニの大量の購入情報を
データベースに登録し，登録後は
解析にしか使用しないのであれば，
更新しないため，更新時異状は発生しません。
そのため，あえて正規化をしないケースというのも存在します。
なぜなら正規化を行い，表を分けていくと，
結合するコストがかかります。これは処理のパフォーマンスに
影響するため，あえて正規化をある部分でやめて
パフォーマンスを優先するという設計もあります。
ただこれは物理設計レベルの話なので
手順としては論理設計で完全に正規化を行った後に
物理設計工程で正規化を戻すという手順となります。
挿入時（追加）
第1正規形でのデータ追加時の更新時異状
| 購入No | 顧客No | 顧客名 | 購入日 | 商品No | 商品名 | 数量 |
| 1 | 8 | 小野 | 2017/1/1 | 5 | ボール | 1 |
| 1 | 8 | 小野 | 2017/1/1 | 6 | スパイク | 2 |
・新しい商品を登録したくなっても登録できない
新しい商品が出来ても，それを登録する表が購入表しかない場合
商品を登録しようとしても購入Noが存在しないため登録できません。
（商品表を作ればいいじゃんっていう話は第2正規形以降の話なので
その話はご勘弁ください。あくまでも購入表しかない場合の
問題点として理論上の解説ですので。。。）
・新しい顧客を登録したくなっても登録できない
同様の理由で顧客単体の登録もできません。
削除時の更新時異状
| 購入No | 顧客No | 顧客名 | 購入日 | 商品No | 商品名 | 数量 |
| 1 | 8 | 小野 | 2017/1/1 | 5 | ボール | 1 |
| 1 | 8 | 小野 | 2017/1/1 | 6 | スパイク | 2 |
・行の削除に合わせて，商品No，商品名の情報も消える場合がある
購入表しかない場合，1行目を削除してしまうと
ボールという情報はなくなってしまいます。
更新時の更新時異状
・一部の情報の更新し忘れによる問題
| 購入No | 顧客No | 顧客名 | 購入日 | 商品No | 商品名 | 数量 |
| 1 | 8 | 小野 | 2017/1/1 | 5 | ボール | 1 |
| 1 | 8 | 小野 | 2017/1/1 | 6 | スパイク | 2 |
| 2 | 11 | 高原 | 2017/1/1 | 5 | ボール | 1 |
この状態から3行目の「ボール」だけを「サッカーボール」にかえたら
1行目の「ボール」はそのままとなってしまう。
| 購入No | 顧客No | 顧客名 | 購入日 | 商品No | 商品名 | 数量 |
| 1 | 8 | 小野 | 2017/1/1 | 5 | ボール | 1 |
| 1 | 8 | 小野 | 2017/1/1 | 6 | スパイク | 2 |
| 2 | 11 | 高原 | 2017/1/1 | 5 | サッカーボール | 1 |
このように更新時異状が発生するため
第1正規形では問題があるという事がわかりました。
それでは第2正規形の話に進みましょう。
第2正規形
第2正規形は「関数従属」に着目します。
関数従属とは「〇〇と決まれば××と１つに決まる」ことを言います。
第2正規形であると言える条件
- 第1正規形の条件を満たしている
- すべての非キー属性が候補キーに関して完全関数従属している
1番目は当然ですよね。でもデータベーススペシャリストの試験では
記述式でこういったことを回答する必要がありますので
必ず覚えておく必要があります。
2番目の
「すべての非キー属性が候補キーに関して完全関数従属している」
というのは，候補キーの項目に対して部分関係従属していない
状態の事です。
部分関係従属とは候補キーの一部が決定するだけで
決定する項目なのか，候補キーのすべてが決定しないと
決定しない項目なのか？という視点でチェックします。
第１正規形の状態
購入
| 購入No | 顧客No | 顧客名 | 購入日 |
| 1 | 8 | 小野 | 2017/1/1 |
購入明細
| 購入No | 商品No | 商品名 | 数量 |
| 1 | 5 | ボール | 1 |
| 1 | 6 | スパイク | 2 |
購入表の候補キーは「購入No」のため，
そもそも部分関係従属は起こりえません。
候補キーが単一の場合は候補キーの一部なんて
いう状態はないからです。
今回の例で言うと
購入明細は購入Noと商品Noで候補キーとなっていますね。
購入明細（購入No，商品No，商品名，数量）
この場合，商品Noが決まるだけで商品名って決まりますよね。
だからこの場合，商品名は商品Noに部分関係従属していると言います。
第2正規化の手順
手順１ 候補キーに着目する
手順２ 候補キーが単一の表は無視する（この時点で第2正規形決定）
手順３ 候補キーが複数ある場合，
その一部で決定する項目があるかどうかをチェック！
（候補キーが2個ならその内1個でわかる項目はないか？
3個ならその内1個や2個でわかる項目はないか？）
手順４ 手順３で発見した項目を別の表に分けて，
紐づけが保てるようにリンク用項目を新しい表に付ける
第１正規形の状態
購入
| 購入No | 顧客No | 顧客名 | 購入日 |
| 1 | 8 | 小野 | 2017/1/1 |
購入明細
| 購入No | 商品No | 商品名 | 数量 |
| 1 | 5 | ボール | 1 |
| 1 | 6 | スパイク | 2 |
第２正規化後の状態
購入（変更なし）
| 購入No | 顧客No | 顧客名 | 購入日 |
| 1 | 8 | 小野 | 2017/1/1 |
購入明細
| 購入No | 商品No | 数量 |
| 1 | 5 | 1 |
| 1 | 6 | 2 |
商品（新しい表）
| 商品No | 商品名 |
| 5 | ボール |
| 6 | スパイク |
これで第2正規形となりました。
第2正規形で更新時異状を確認
第1正規形では次のような更新時異状が発生する可能性がありました。
- 新しい商品を登録したくなっても登録できない
- 新しい顧客を登録したくなっても登録できない
- 行の削除に合わせて，商品No，商品名の情報も消える場合がある
- 一部の情報の更新し忘れによる問題
第2正規形ではどうなったかを確認しましょう。
- 新しい商品を登録したくなっても登録できない
→OK
商品表が新たにできたので登録可能。 - 新しい顧客を登録したくなっても登録できない
→まだNG - 行の削除に合わせて，商品No，商品名の情報も消える場合がある
→OK
商品表が出来たのでOK。でも顧客情報は消える。 - 一部の情報の更新し忘れによる問題
→商品名はOK，顧客名はNG
このような感じです。要するに顧客情報に関してはまだ
更新時異状が出ますよね。
以上が第2正規形です。
引き続き第3正規形に行きましょう。
第3正規形
第3正規形の条件
- 第2正規形の条件を満たしている
- いかなる非キー属性も候補キーに対して推移的関数従属をしない
1番目は当然ですね。
2番目の解説をします。
第2正規形の状態で，候補キーの一部で決定される項目はなくなりました。
今回着目するのは，候補キー以外の項目が決まれば
導き出される項目があるかどうかをチェックします。
「Aが決まるとBが決まる。Bが決まればCが決まる場合
Aが決まるとCが決まる。」という状態のCがあるかをチェックします。
第2正規形の状態
購入
| 購入No | 顧客No | 顧客名 | 購入日 |
| 1 | 8 | 小野 | 2017/1/1 |
購入明細
| 購入No | 商品No | 数量 |
| 1 | 5 | 1 |
| 1 | 6 | 2 |
商品（新しい表）
| 商品No | 商品名 |
| 5 | ボール |
| 6 | スパイク |
購入表の顧客名は購入Noが決まれば決まりますが，
顧客Noが決まっても決まりますよね。
そして顧客Noは候補キーではありません。
という事は候補キー以外の項目が決まれば
値が決まるという状態になるので
購入Noが決まれば顧客Noが決まる，顧客Noが決まれば
顧客名が決まります。これを推移的関数従属といいます。
第3正規化とはこの推移的関数従属を取り除くことを言います
第3正規化すると次のようになります。
購入
| 購入No | 顧客No | 購入日 |
| 1 | 8 | 2017/1/1 |
購入明細
| 購入No | 商品No | 数量 |
| 1 | 5 | 1 |
| 1 | 6 | 2 |
顧客
| 顧客No | 顧客名 |
| 8 | 小野 |
商品
| 商品No | 商品名 |
| 5 | ボール |
| 6 | スパイク |
顧客表を追加し，購入表の顧客Noと紐づくようにします。
これで理論上更新時異状が発生しない状態になりました。
以上で第3正規形は完了です。
SQLやデータベースについてもっと学びたい方は次の記事でおすすめの書籍を紹介しています↓
<< 1 2 3 4 5 6 7 8 9 10 11 12 >>
