関係データベースは正規形で正規化?
前回の勉強内容
勉強のきっかけになった過去問
"受注明細"表は,どのレベルまでの正規形の条件を満足しているか。ここで,実線の下線は主キーを表す。
受注明細
受注番号 明細番号 商品コード 商品名 数量 015867 1 TV20006 20型テレビ 20 015867 2 TV24005 24型テレビ 10 015867 3 TV28007 28型テレビ 5 015868 1 TV28007 28型テレビ 8 ア. 第1正規形 イ. (正解)第2正規形 ウ. 第3正規形 エ. 第4正規形
出典 : 応用情報技術者試験 平成29年春期 午前 問27
正規化は、データベースを使いやすく整えることです。
同じ内容が複数のテーブルに分散していると、項目を更新するのが大変になるだけでなくディスク領域が浪費されてしまうので「データの重複」がなくなるように設計します。
各テーブルの関係性を保てるように「従属関係を定義」したりします。
このようにデータベースを作ることで「冗長なデータ」「矛盾する従属関係」を排除し、使いやすくデータを整理してデータベースを作っていくことを「正規化」といいます。
なので、正規化していないと使いにくいデータベースになります。
非正規形は、関係データベースに登録できない残念な状態です。
正規化していなくて、1行に複数データが何度も出てくるような状態を「非正規形」といいます。
例えば以下のようなデータは、1つの受注情報に[明細番号]で始まる明細情報が複数あって「非正規形」となります。
受注番号 | 受注日 | 明細番号 | 商品コード | 商品名 | 数量 | 明細番号 | 商品コード | 商品名 | 数量 | 明細番号 | 商品コード | 商品名 | 数量 |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
015867 | 2023-09-01 | 1 | TV20006 | 20型テレビ | 20 | 2 | TV24005 | 24型テレビ | 10 | 3 | TV28007 | 28型テレビ | 5 |
015868 | 2023-11-19 | 1 | TV28007 | 28型テレビ | 8 |
例えば以下のようなデータも、「非正規形」となり1行に複数の明細情報があって関係データベースに入れることもできません。
受注番号 | 受注日 | 明細番号 | 商品コード | 商品名 | 数量 | |
---|---|---|---|---|---|---|
015867 | 2023-09-01 | 1 | TV20006 | 20型テレビ | 20 | |
2 | TV24005 | 24型テレビ | 10 | |||
3 | TV28007 | 28型テレビ | 5 | |||
015868 | 2023-11-19 | 1 | TV28007 | 28型テレビ | 8 |
正規形は、正規化するやり方の段階です。
正規形は数字が大きいほどいい感じに正規化できてます。
第1正規形は、繰り返す項目をなくした状態です。
「1行に複数データが何度も出てくるような状態」を解消するだけで「第1正規形」になります。
例えば、「1つの受注情報に明細情報が複数」だったのを「明細情報1つ1つに受注情報をくっつける」と「第1正規形」になります。
受注番号 | 受注日 | 明細番号 | 商品コード | 商品名 | 数量 |
---|---|---|---|---|---|
015867 | 2023-09-01 | 1 | TV20006 | 20型テレビ | 20 |
015867 | 2023-09-01 | 2 | TV24005 | 24型テレビ | 10 |
015867 | 2023-09-01 | 3 | TV28007 | 28型テレビ | 5 |
015868 | 2023-11-19 | 1 | TV28007 | 28型テレビ | 8 |
なので、「繰返し属性が存在しない」にはなるのですが、「一部しか違わないレコードが半端なくある状態」となります。
第2正規形は、部分関数従属している項目を別テーブルへ切り出した状態です。
レコードを一意にすることができる項目の組み合わせを「候補キー」といいます。
この「候補キー」の1つの項目値が決まると他の項目値も決まる関係のことを「部分関数従属」といいます。
「繰り返す項目をなくした」だけの「第1正規形」から「部分関数従属している項目を別テーブルへ切り出した状態」が「第2正規形」となります。
「第1正規形」の例を見ると、{受注番号, 明細番号}が候補キーになり、[受注日]は[受注番号]に「部分関数従属」している項目です。
ponsuke-tarou.hatenablog.com
「部分関数従属」している[受注日]を別テーブルにして・・・
受注番号 | 受注日 |
---|---|
015867 | 2023-09-01 |
015868 | 2023-11-19 |
元のテーブルで[受注番号]に外部キーを作って[受注日]を抜き出すことができ、「第2正規形」になります。
受注番号 | 明細番号 | 商品コード | 商品名 | 数量 |
---|---|---|---|---|
015867 | 1 | TV20006 | 20型テレビ | 20 |
015867 | 2 | TV24005 | 24型テレビ | 10 |
015867 | 3 | TV28007 | 28型テレビ | 5 |
015868 | 1 | TV28007 | 28型テレビ | 8 |
第3正規形は、推移的関数従属している項目を別テーブルへ切り出した状態です。
推移的関数従属とは、1つの項目値が決まると玉突き的に他の項目値も決まる関係のことです。
「第2正規形」の例を見ると、{受注番号, 明細番号}が決まると1つの明細に1つの商品なので[商品コード]が決まります。
[商品コード]が決まると[商品名]が決まります。
このように玉突き的に値が決まるのが「推移的関数従属」です。
「推移的関数従属」している[商品名]を別テーブルにして・・・
商品コード | 商品名 |
---|---|
TV20006 | 20型テレビ |
TV24005 | 24型テレビ |
TV28007 | 28型テレビ |
元のテーブルで[商品コード]に外部キーを作って[商品名]を抜き出すことができ、「第3正規形」になります。
受注番号 | 明細番号 | 商品コード | 数量 |
---|---|---|---|
015867 | 1 | TV20006 | 20 |
015867 | 2 | TV24005 | 10 |
015867 | 3 | TV28007 | 5 |
015868 | 1 | TV28007 | 8 |
他に知っておいたほうがいいことがあります。
やり過ぎると無駄にテーブルが多くなってしまうので、アプリケーションに合わせて正規化しましょう。
第 3 正規形を遵守することが理論上望ましくても、常に実用的とは限りません。<省略>
理論上、正規化は実践する価値があります。 しかし、小さいテーブルを数多く作成するとパフォーマンスが低下したり、開くことのできるファイルおよびメモリの容量を超える場合があります。
データベースの正規化の基礎
完全に正規化されたデータベースへのクエリ(検索質問)はパフォーマンスが良くないことがある。
関係の正規化 - Wikipedia
データ検索・データ更新の頻度やリレーションの階層などと、実際の業務バランスを見て正規化は行いましょう。
場合によっては意図的に正規化を行わず性能を向上させる設計も必要です。
www.fellow-ship.com
もっと正規化した正規形はあるけどあんまり使わないらしいです。
ボイスコッド正規形 (BCNF) とも呼ばれる第 4 正規形、および第 5 正規形が存在しますが、実用的な設計と見なされることはほとんどありません。 これらの規則を無視すると、完全なデータベース設計とはならないことがありますが、機能的には影響しません。
データベースの正規化の基礎
ボイス・コッド正規形
ほとんどの場合第3正規形と等価であり、複数の属性からなる候補キーが複数存在する場合にのみ差異が生じうる。<省略>
第4正規形
候補キーではない属性への多値従属性をもった属性があってはならない。<省略>
第5正規形
その関係が第4正規形であり、さらにその関係に含まれる結合従属性の決定項が候補キーのみである
関係の正規化 - Wikipedia
わからない用語はわからないので調べました。
スカラ値とは、「もう分割できない!」とか「それ以上分解して操作する必要ない!」っていう値です。
scalarは「単体」を意味します。<省略>
簡単にイメージできるのは,プログラミング言語におけるchar,int,floatといった単一の値だけを保持するデータ型でしょう。反対に,配列,リスト,レコードなど複合的な値を保持するデータ型は,非スカラ型と呼ばれます。
gihyo.jp
導出項目とは、合計値みたいに既にある項目から計算とかすればわかる項目です。
他のデータ項目から処理や計算により導き出せる項目のことをいいます。
blog.goo.ne.jp
更新時異常とは、更新したら情報がなくなったり矛盾が発生することです。
商品テーブル作ってないもんだから、薬草の値段変えたかったら、薬草の取引に関する部分全部変えなきゃダメとか、新しい商品追加したくても、取引明細テーブルにしか商品情報が書いてないから、登録できないとか、アホみたいなこと
データベースの正規化(正規形)とはなんぞや – gomokulog
第2正規形で起こる更新時異常には次の2種類があります。
(1) 行の修正に伴って修正前に格納されていた情報が無くなってしまう。<省略>
(2) 非キー属性に関数従属している属性の値を修正するときに、該当する行の全てを修正しなければならない。<省略>更新処理を誤るとデータの不整合が生じる。
データベーススペシャリスト平成22年春期問7 更新するときに発生する問題|データベーススペシャリスト.com
おわりに
このぺーじでは、いろんなサイトにある解説で理解しやすいなって思ったところを集めまくって勉強しています。
わかりやすい解説をしていただいているサイト様に心から感謝です。