データベース

正規化入門#03(第2正規形)

こんにちは、cocomaruです。

前回は非正規形の表を第1正規形へ変形するところまで行いました。

今回は次の正規形である、第2正規形に正規化していきたいと思います。

第1正規形での更新時異状

まず第2正規形を行う前に、現在の関係表では更新時異状が起こる事を学んでいきましょう。

正規化入門#01(正規化とは?その目的と種類について)でも説明しましたが、
正規化とは更新時異状を発生させない為の理論でした。

では、このままではどういった更新時異状が発生するか説明します。

・関係(受講講座) ※第1正規形にした関係

受講コード 受講日 学生コード 学生名 講座コード 講座名 受講料 受講数 支払合計
1001 2019-04-01 S001 山田 C001 数学 5,000 3 15,000
1001 2019-04-01 S001 山田 C002 国語 5,000 2 10,000
1002 2019-04-01 S002 佐藤 C001 数学 5,000 1 5,000
1002 2019-04-01 S002 佐藤 C003 英語 8,000 2 16,000
1002 2019-04-01 S002 佐藤 C005 社会 4,000 1 4,000
1003 2019-04-01 S003 鈴木 C004 理科 6,000 3 18,000
1004 2019-05-30 S003 鈴木 C005 社会 4,000 4 16,000

上記の表では次の3種類の更新時異状が起こります。

タプル挿入時異状

講座を受講するにはどのような講座を公開するか決めなければなりません。

以下のように講座情報を登録したい。

受講コード 講座コード 講座名 受講料
C001 数学 5,000

※「-」はNULL値

ただし受講が決まる前に講座を登録しようしても主キーの一部である{受講コード}が決定していませんので、
主キーにNULL値を登録しようとしてエラーになるため登録できません

タプル更新時異状

{受講コード}の「1001」の{学生コード}を「S001」から「S010」へ更新しようとする場合、
{受講コード}の「1001」のタプルは2行あるため、両方一度に更新する必要があります。

1行のみ更新してしまうとデータの整合性が取れない状態になってしまいます。

以下のように1行のみ更新しまうとデータの整合性が壊れる

受講コード 学生コード 講座コード
1001 S010 C001
1001 S001 C002

「両方一度に更新すれば良いだけなのに、何が問題なの?」
と思った方もいるかもしれませんが、確かに両方一度に更新すれば矛盾は生じませんが、
あくまで正規化とは更新時異状を排除する為のものであり、正規化を行う上でこの不都合を取り除かなければなりません。

タプル削除時異状

{学生コード}「S003」の情報を削除すると、{受講コード}「1003と1004」の情報自体も削除され、
また{講座コード}「C004、C005」の講座情報も削除されてしまい受講情報と講座情報の両方の情報が保持できなくなります

第2正規形とは

第2正規形の定義については次の通りです。

第2正規形の定義

関係Rが第2正規形であるとは、次の二つの条件を満たす時をいう。

  • 関係Rは第1正規形である
  • 関係Rの全ての非キー属性はRの各候補キーに完全関数従属している(部分関数従属していない)

完全関数従属とは何でしょう?
データベース用語集というサイトから引用すると

完全関数従属とは、関数従属(ある属性が定まると他の属性が定まるという関係)の一つで、主キー全体に従属する関係のことをいいます。また部分関数従属でない関数従属を完全関数従属と表現されるケースもあります。

引用元:データベース用語集

こういう用語を調べるときに、いつも思うのですが完全に人に理解されようと思ってないですよね…。それとも私の理解が悪いだけですかね;;

とりあえず簡単にいうと、AとBの属性が決まるとCが決まることを「{A, B} -> C」と表すと、
「A -> C」, 「B -> C」という関数従属性は成立しないとき、Cは{A, B}に完全関数従属していると言います。

わかりましたかね…。

とりあえず言葉の定義は追々覚えていくとして関係表を正規化していきましょう!!

えっと第2正規形の条件をもう一度見ると、まず一番目の「関係Rは第1正規形である」というのはすでに前回行いましたよね?!

じゃあ次は「関係Rの全ての非キー属性はRの各候補キーに完全関数従属している(部分関数従属していない)」ですが、この条件を満たすことで関係(受講講座)は第2正規形になりそうです!

候補キーと非キー属性について

まず候補キーと非キー属性について整理しましょう。

候補キーに関しては正規化入門#02(第1正規形)で説明したように以下になります。

・{受講コード, 講座コード}
・{受講コード, 講座名}

ここで主キーを{受講コード, 講座コード}とします。

非キー属性とは候補キー以外の属性になりますので、
{受講日}、{学生コード}、{学生名}、{受講料}、{受講数}、{支払合計}が非キー属性になります。

第2正規形を行う

候補キーと非キー属性が明確になったところで、第2正規化を行っていきましょう!

条件としては非キー属性は候補キーに対して完全関数従属をしていることでした。

これは、それぞれの非キー属性に対して、候補キーの一部のキーが部分関数従属していないことという意味なので、それぞれ以下の関数従属がないか見ていきましょう。

※以下の関係がなければ第2正規形になっている!
{受講コード} -> {非キー属性} の関数従属がないこと
{講座コード} -> {非キー属性} の関数従属がないこと

ではそれぞれの非キー属性が完全関数従属しているか見ていきましょう。

受講コード 受講日 学生コード 学生名 講座コード 講座名 受講料 受講数 支払合計

色が識別している通り、
・{受講コード} -> {受講日, 学生コード, 学生名}
・{講座コード} -> {受講料}
と候補キーの一部であるそれぞれのキーに対して非キーが関数従属していることがわかります。
{講座名}は候補キーの一部なので、検討対象外

受講コードと講座コードに対して関数従属している属性が判明したため、これらを第2正規形として変形したいと思います。

・関係(受講講座)

受講コード 受講日 学生コード 学生名 講座コード 講座名 受講料 受講数 支払合計
1001 2019-04-01 S001 山田 C001 数学 5,000 3 15,000
1001 2019-04-01 S001 山田 C002 国語 5,000 2 10,000
1002 2019-04-01 S002 佐藤 C001 数学 5,000 1 5,000
1002 2019-04-01 S002 佐藤 C003 英語 8,000 2 16,000
1002 2019-04-01 S002 佐藤 C005 社会 4,000 1 4,000
1003 2019-04-01 S003 鈴木 C004 理科 6,000 3 18,000
1004 2019-05-30 S003 鈴木 C005 社会 4,000 4 16,000


第2正規形を行うと…!?

・関係(受講)

受講コード 受講日 学生コード 学生名
1001 2019-04-01 S001 山田
1002 2019-04-01 S002 佐藤
1003 2019-04-01 S003 鈴木
1004 2019-05-30 S003 鈴木

・関係(受講講座)

受講コード 講座コード 受講数 支払合計
1001 C001 3 15,000
1001 C002 2 10,000
1002 C001 1 5,000
1002 C003 2 16,000
1002 C005 1 4,000
1003 C004 3 18,000
1004 C005 4 16,000

・関係(講座)

講座コード 講座名 受講料
C001 数学 5,000
C002 国語 5,000
C003 英語 8,000
C004 理科 6,000
C005 社会 4,000

さいごに

第2正規形を行うことで、全ての非キー属性は候補キーに対して完全関数従属するところまで変形ができました。

最後に第2正規形のポイントを抑えておきましょう。

  • 第2正規形では、全ての非キー属性が各候補キーに対して完全関数従属している
  • 第2正規形にするために、部分関数従属性は排除する

次回は第3正規形まで進めます。