Finding a relation in 3NF but not in BCNF

 

I've been reading many different sources on how to differentiate relations that are in 3NF/BCNF. And I've so far this is my understanding...

I will use this relation as an example...

R = {A, B, C, D, E}

and

F = {A -> B, BC - > E, ED -> A} .

Firstly we must find the keys of the relation. I used this video to help me do that. And I got

Keys = {ACD, BCD, CDE}

Now to make sure R is in BCNF , we must make sure that the left hand side of every functional dependency in F is one of the Keys . We instantly know this is not the case, because the first FD is A -> B and A is not one of the keys. So it is not in BCNF.

Now to make sure R is in 3NF , we must make sure that the left hand side of every functional dependency in F is one of the Keys OR the right hand side of every functional dependency in F is a subset of one of the Keys . If you look at the right hand side of every FD, they are B , E and A . These are each a subset of a Key , so this means that it is in 3NF .

So this is one of the rare cases (according to wiki) where a relation is in 3NF but not in BCNF . Is this method correct? Is it reliable? Am I missing anything?

First you need to learn superkeys, candidate keys, and primary attributes.

However, this rule of thumb helps:

A 3NF table that does not have multiple overlapping candidate keys is guaranteed to be in BCNF.

In other words, if the candidate keys in a 3NF relation are

  • all atomic, or
  • non-atomic but non-overlapping,

    • it is guaranteed that the relation is in BCNF.

    The simplest relation which violates BCNF but meets 3NF has the following functional dependencies:

    A,B -> CC -> B

    In this case, candidate keys are (A,B) and (A,C) .
    It meets 3NF because

  • the right-hand-side of all functional dependencies is a primary attribute .

    • It violates BCNF because

  • C -> B , but the left-hand-side is not a superkey .

    • BCNF:

    X->Y where Y can be prime or non-prime
    Here,X must be a super key

    3NF :

    X->Y where Y is non-prime
    then,
    X must be a super key
    else
    X need not be super key

    Hope,this helps

    链接地址: http://www.djcxy.com/p/20438.html

    上一篇: 设置UITextView的文本会导致崩溃

    下一篇: 在3NF中找到关系，但在BCNF中找不到