Which of the following is correct for 3nf and bcnf?

Question

Consider the following statements.

1. If relation R is in 3NF and every key is simple, then R is in BCNF
2. If relation R is in 3NF and R has only one key, then R is in BCNF

• A. Both 1 and 2 are true
• B. 1 is true but 2 is false
• C. 1 is false and 2 is true
• D. Both 1 and 2 are false

Comments

I think the only difference b/w 3nf and BCNF is a->b where b is a prime attibutes and a does not belong to set of super key.

This is possible in first case but when you apply it in case 2 it will result in trivial dependency which ultimately is a part of BCNF this 1 is false 2nd is correct.

---

yes 1st option is the answer

---

$X\rightarrow Y$

3NF: X is a super key or Y is a prime

1. R is in 3NF & every key is simple, whether it is BCNF or not ?

Let's assume it is not BCNF, hence X can't be a superkey but Y must be a prime in order to be in 3NF & it is given that every key is simple which means Y must be a CK

$\therefore X\ must\ be\ a\ CK$

Hence it is in bcnf also.

Answer 1

In 3NF, for any non-trivial FD

X -> Y

either X should be a super key of Y-X must be a prime attribute (part of some candidate key). 

In BCNF, for any non-trivial FD

X -> Y

X should be a super key. 

So, from 3NF to BCNF we lost one condition and if a relation is in 3NF but not in BCNF then this condition must hold. i.e., for some non-trivial FD,

X -> Y, 

X is not a super key and Y-X is a prime attribute. 

Now, lets take the first condition given. It says every key is simple. So, any prime attribute is a candidate key. Thus, for any X -> Y, we have X is not a super key, and Y-X is a candidate key. Now, if Y-X is a candidate key, Y must be a super key and from X -> Y, X must also be a super key. 

For the second case, we have only one key (implied to be candidate key). Suppose, there exist a non trivial FD

X -> Y 

with X not being a super key, and Y-X being a prime attribute. Now, X must contain a prime attribute here, as otherwise it cannot determine any prime attribute. (Since Y-X is a prime attribute, replace it with X in the corresponding candidate key and we get another candidate key). So, the only possibility is for X and Y to be part of the same key, and then with X -> Y, that key will be a super key and not a candidate key- to make it candidate key we have to remove Y from it. 

So, in both given cases 3NF implies BCNF. Actually only when a 3NF relation contains overlapping candidate keys, it cannot be in BCNF. In all other cases, 3NF implies BCNF. 

https://gatecse.in/demystifying-database-normalization/

Comments

Can u explain with example..

---

I agree with you on the first one but for the second one consider that we have a relation $R={\{A,B,C,D\}}$ where $AB$ is the key, and say $C->B$ then it satisfies $3nf$ right? But  it is $NOT$ in $BCNF$ right? as here we have $non-prime$  $deriving$  $a$  $prime$  $attribute$

Answer 2

Both the statements are true.

1) First of all, simple key is a key having only one attribute (unlike composite key, which is made up of two or more attributes). Condition for a relation to be in BCNF is

• for any non-trivial functional dependency, X → A, X must be a super-key.

It is given that a relation is already in 3NF i.e. there are no transitive dependencies. Now if all the keys are simple, then the keys itself will be super-keys and hence the above condition will always hold true.

On a simpler note, one easy way to check if a relation is in BCNF or not is to check if A → B where A is proper subset of one candidate key and B is proper subset of another candidate key. If this happens, then the relation is not in BCNF.

Now, in our case all keys are simple and hence their proper subsets are not possible at all. So, there is no violation of BCNF condition and hence the relation is in BCNF.

2) Again the relation is given in 3NF i.e. no transitive dependencies and having only one key. So all non-key attributes will be functionally determined by the key attribute, making the relation satisfy BCNF.

Comments

Relation R(A,B,C,D)

Key: A

FD {A-.>BCD, C->A}

Please check this?

---

AB and AC both are keys here.

---

@FlatPotato123, in that C will also be a key..along with A

Answer 3

If relation R is in 3NF and every key is simple, then R is in BCNF

given that R is 3 NF.

the FD's possible is X ---> Y

i) either X is Super Key

ii) or Y should be Prime Attribute

 

if really X is a SuperKey, then it is also BCNF

if Y is a Prime attribute without X is a superkey then it may lead to doesn't satisfy BCNF.

But they given that every Key is simple ===> Y is prime attribute means Y is a Key.

( Key is simple means, it have only one attribute. ex:- 'AB' is not simple key because it contain morethan one attribute.

Every attribute in the Key is Prime attribute, there is only one attribute in the key means, the prime attribute itself is a Key )

if Y is a key then according to the FD X ----> Y, X is also a SuperKey. ===> it is in BCNF.

 

If relation R is in 3NF and R has only one key, then R is in BCNF

for understand this, let AB is the only key of R(A,B,C,D).

then can A --> B FD is exist ? No, if it exist, we have to say A is the Key...

From these we can understand that, there is No Dependency between the attributes of Same Key.

Given that we have only one key ===> Prime Attr ---> Prime Attr FD's can't possible

then only choice is to exist a dependency, X --->Y is X should be Key ====> it is in BCNF.

 

check https://gateoverflow.in/218551/normal-forms question for more understanding the line from Arjun Sir's explanation

Actually only when a 3NF relation contains overlapping candidate keys, it cannot be in BCNF. In all other cases, 3NF implies BCNF. 

Comments

Never mind
got it
thanks for good explanation

---

hello, can you please tell me what happens if a non key attribute derives part of a key attribute in this case 3nf holds but bcnf does not holds( i am talking about the second option).

---

If this is the case then 

(AC)+ −> ABCD

hence AC, AB will be 2 keys and it is clearly mentioned in question only 1 key exists .

So only possible case is :

Prime Attribute −> Prime Attribute 
i.e A −> B , if this is the case A will be key not AB and 
A −> C will satisfy LHS is Super Key  Hence it is in BCNF

Answer 4

let we have relation with 4 FIELDS  A,B,C,D WHICH IS IN 3NF means n partial no transitive dependencies .

case 1 every key is simple i.e either field can be a candidate.key

if any one say A is key then A-> B C D AND SINCE IT IS IN 3NF B,C,D CAN NOT determine each other (else t.d will occur)  SO IT IS IN BCNF As every determinant is a c.k 

if more than one field are key say A ,B ARE c.k then A->B C D  AND B->A,C,D THEN SUCH COMBINATION IS NOT POSSIBLE AS T.D WILL OCCUR

so case 1 is always in BCNF

 

case 2 when there is only one key which may be simple(case 1) or composite say A+B =>C,D   SINCE IT IS IN 3NF C can not determine D and vice versa and B,A alone can not determine C OR D HERE there are 2 determinants A,B WHICH ARE PART OF C.K HENCE IT IS TOO IN BCNF HENCE ANS IS A

Comments

I agree with you on the first one but for the second one consider that we have a relation $R={\{A,B,C,D\}}$ where $AB$ is the key, and say $C->B$ then it satisfies $3nf$ right? But  it is $NOT$ in $BCNF$ right? as here we have $non-prime$  $deriving$  $a$  $prime$  $attribute$