GATE CSE 2022 | Question: 4

Question

In a relational data model, which one of the following statements is $\text{TRUE}?$

• A. A relation with only two attributes is always in $\text{BCNF}.$
• B. If all attributes of a relation are prime attributes, then the relation is in $\text{BCNF}.$
• C. Every relation has at least one non-prime attribute.
• D. $\text{BCNF}$ decompositions preserve functional dependencies.

Comments

A relation with only two attributes is always in BCNF.

https://stackoverflow.com/questions/33455459/how-is-every-binary-relation-bcnf

For any relation to be in BCNF, the following must holds for any functional dependency $X \rightarrow Y :$

Either $X \rightarrow Y $ is a Trivial Functional Dependency (i.e. $Y \subseteq X $) OR $X$ is a superkey for schema $R.$

A relation with only two attributes is always in BCNF.

For Example, Assume relation $R = \{A,B \}$ with two attributes. The only possible Non-trivial FD's are $ \{A \} \rightarrow \{B \}$ and $ \{B \} \rightarrow \{A \}.$
So, there are four possible cases:

1. No Non-trivial FD's hold in $R.$ i.e. $ \{Cand.Key = AB \},$ Since it is an all key relation it's always in $BCNF.$

2. Only Non-trivial FD $A \rightarrow B$ holds. In this case, $ \{Cand.Key = A \},$ and relation satisfies BCNF.

3. Only Non-trivial FD $B \rightarrow A$ holds. In this case, $ \{Cand.Key = B \},$ and relation satisfies BCNF.

4. Both $A \rightarrow B$ and $B \rightarrow A$ holds. In this case, $ \{Cand.Keys = A,B \},$ and
   relation satisfies BCNF.

Hence, every Binary Relation (A relation with two attributes) is always in BCNF!

Answer 1

Option A : A relation with only two attributes is always in BCNF

let relation R(A,B), then possible FD’s are

1. A → B, in this case A is minimal Superkey, So this relation should be in BCNF
2. B → A, in this case B is minimal Superkey, So this relation should be in BCNF
3. AB → AB , in this case AB is minimal Superkey, So this relation should be in BCNF

So, given statement is Correct.

Alternatively we can think like, We can’t further divide a relation which had only two attributes, then it must be in BCNF.

 

Option B : If all the attributes of a relation are Prime attributes, then the relation is in BCNF

classic counter example : R(A,B,C) with FD set = {AB → C, C → A}

in this relation all attributes are prime but this relation is not in BCNF due to “ C → A ”

Correct statement is : If all the attributes in the relation are Prime attributes, then it is in 3NF.

 

Option C : Every relation has at least one non-prime attribute

counter example : R(A,B,C) with FD set = {AB → C, C → A}

in this relation all attributes are prime

Correct statement is : Every relation has atleast one prime attribute.

 

Option D : BCNF decompositions preserve functional dependencies

for any relation, we can guarentee there exist atleast one decomposition which is in 3NF, lossless and Dependency Preserving. But we can not guarentee that atleast one decomposition which is BCNF, lossless and Dependency Preserving.

 

Option A is correct

Comments

@Shaik Masthan For option C, I agree that the original statement given in ques is False. But, in last you said “Every relation has atleast one prime attribute.” this is also wrong. 

Eg: consider cyclic FD. 

===================================================

I have one further question to ask (I have one doubt too): 

R(A, B) { A → B) Then what is B in this FD, can we call it a Non prime attribute ? 

---

@rooney

what happens when there're cyclic FDs ? Still there exist one primary key, rt ?

 

for second question, yes B is non prime attribute.

---

@Shaik Masthan

Prime attribute: it is a part of candidate key which is also composite. If there exist a single attribute which is CK. then its not a prime attribute. 

Yes, their exist more than one primary key, but no Prime attribute (given that there are no pairs in relation)

Eg: R(a,b,c){ a→ b, b→c, c → a}   Here, all are primary key and none of them is prime attribute. 

___________

Thank you! 

 

Answer 2

Option A is true.

proof of option A: https://stackoverflow.com/questions/33455459/how-is-every-binary-relation-bcnf

For option B ,

Counter example https://gateoverflow.in/167899/If-every-attribute-of-r-is-prime-attribute-then

Option C,

It is also false As,

R(a,b,c) is a relation and The FD set is { a->b,b->c,c->a}

Now the candidate keys for the relation are a,b,c.

And it is fine even the Relation is in BCNF.

So option C is also false.

Option D is false as we know BCNF is lossless but does not preserve Functional dependency.