データベース

正規化入門#04(第3正規形)

どうも、cocomaruです。

第2正規系まで出来たところで、今回は第3正規形を行いたいと思います。

第3正規系までは非キー属性の分解を行うのですが、ここまでの正規形の知識は必ず必要ですので、是非身につけてください!

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

ではいつもの通り第2正規形までで、どのような更新時異状が発生するか考えていきましょう。

以下は前回行った第2正規形の関係表で更新時異状が起こり得る関係表です。

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

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

タプル挿入時異状

受講をする前に、新たに学生情報を登録しようとしても主キーである{受講コード}がNULLになるため、登録できません。

受講コード 受講日 学生コード 学生名
S004 加藤

※「-」はNULL値

上記のように学生情報を登録したいが主キーがNULLのためエラーになる。

タプル更新時異状

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

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

タプル削除時異状

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

第3正規形とは

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

第3正規形の定義

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

  • 関係Rは第2正規形である
  • 関係Rの全ての非キー属性はRのいかなる候補キーにも推移的に関数従属しない

推移的関数従属性とは、関係Rの異なる属性または属性の集合であるX、Y、Zについて図のような制約が成立している関数従属性です。

こちらの関係を先ほどの関係(受講)を用いて考えると、
{受講コード( X )} -> {学生コード( Y )} -> {学生名( Z )}
の関数従属性がありまる。

また{学生コード( Y )}が決まっても{受講コード( X )}は一意にはなりません。

従ってX – > Y – >Zの推移的関数従属性の条件を満たしていることになります。

第3正規形を行う

第3正規形は推移的関数従属を排除すれば完成ですので、この正規化を行なっていきましょう。

推移的関数従属性を排除

X – > Y – >ZのY -> Zの部分が関数従属していることが条件を成立しない要因となってますので、これらの関係を別の関係として切り出しましょう。
また、Yは元の関係には外部キーとして残しておきます。

・関係(受講)

受講コード 受講日 学生コード
1001 2019-04-01 S001
1002 2019-04-01 S002
1003 2019-04-01 S003
1004 2019-05-30 S003

・関係(学生)

学生コード 学生名
S001 山田
S002 佐藤
S003 鈴木

導出属性を排除

また関係(受講講座)の{支払合計}は、同関係の{受講数}と関係(講座)の{受講料}で求められる導出属性になりますので、第3正規形にする際にも導出項目は一緒に取り除きます。

・関係(受講講座)

受講コード 講座コード 受講数 支払合計
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

第3正規形後

・関係(受講)

受講コード 受講日 学生コード
1001 2019-04-01 S001
1002 2019-04-01 S002
1003 2019-04-01 S003
1004 2019-05-30 S003

・関係(学生)

学生コード 学生名
S001 山田
S002 佐藤
S003 鈴木

・関係(講座)

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

・関係(受講講座)

受講コード 講座コード 受講数
1001 C001 3
1001 C002 2
1002 C001 1
1002 C003 2
1002 C005 1
1003 C004 3
1004 C005 4

さいごに

こちらでの今まで用いてきた関係全ての正規化は完了しました。

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

  • 第3正規形では、全ての非キー属性に推移的関数従属性が存在しない
  • 第3正規形にするために、X -> Y -> Zの推移的関数従属性を排除する

次はボイスコッド正規形について触れたいと思いますが、別の関係表を元に教えていければと思います!
では次もよろしくお願いいたします。