edited by
51,498 views
128 votes
128 votes

$R(A,B,C,D)$ is a relation. Which of the following does not have a lossless join, dependency preserving $BCNF$ decomposition?

  1. $A \rightarrow B, B \rightarrow CD$
  2. $A \rightarrow B, B \rightarrow C, C \rightarrow D$
  3. $ AB \rightarrow C, C \rightarrow AD$
  4. $A \rightarrow BCD$
edited by

6 Answers

Best answer
174 votes
174 votes

taking up option A first :
We have, R(A, B, C, D) and the Functional Dependency set = {A→B, B→CD}.
Now we will try to decompose it such that the decomposition is a Lossless Join, Dependency Preserving and new relations thus formed are in BCNF.
We decomposed it to R1(A, B) and R2(B, C, D). This decomposition satisfies all three properties we mentioned prior.

taking up option B :
we have, R(A, B, C, D) and the Functional Dependency set = {A→B, B→C, C→D}.
we decomposed it as R1(A, B), R2(B, C) and R3(C, D). This decomposition too satisfies all properties as decomposition in option A.

taking up option D :
we have, R(A, B, C, D) and the Functional Dependency set = {A→BCD}.
This set of FDs is equivalent to set = {A→B, A→C, A→D} on applying decomposition rule which is derived from Armstrong's Axioms
we decomposed it as R1(A, B), R2(A, C) and R3(A, D). This decomposition also satisfies all properties as required.

taking up option C :
we have, R(A, B, C, D) and the Functional Dependency set = {AB→C, C→AD}.
we decompose it as R1(A, B, C) and R2(C, D). This preserves all dependencies and the join is lossless too, but the relation R1 is not in BCNF. In R1 we keep ABC together otherwise preserving {AB→C} will fail, but doing so also causes {C→A} to appear in R1. {C→A} violates the condition for R1 to be in BCNF as C is not a superkey. Condition that all relations formed after decomposition should be in BCNF is not satisfied here.

We need to identify the INCORRECT, Hence mark option C.

selected by
50 votes
50 votes
(C) is the answer. Because of AB $\to$ C and C $\to$ A, we cannot have A, B and C together in any BCNF relation- in relation ABC, C is not a super key and C$\to$ A exists violating BCNF condition. So, we cannot preserve  AB $\to$ C dependency in any decomposition of ABCD.

For (A) we can have AB, BCD, A and B the respective keys
For (B) we can have AB, BC, CD, A, B and C the respective keys
For (D) we can have ABCD, A is key
12 votes
12 votes
(A) A->B, B->CD

AB and BCD, B is the key of second and hence decomposition is lossless.

(B) A->B, B->C, C->D

AB, BC, CD B is the key of second and C is the key of third, hence lossless.

(C) AB->C, C->AD

ABC, CD. C is key of second, but C->A violates BCNF condition in ABC as C is not a key. We cannot decompose ABC further as AB->C dependency would be lost. Hence the ANSWER.

(D) A ->BCD

Already in BCNF.
1 votes
1 votes

Initially FD X-> Y is in BCNF if

X is superkey

A) A is candidate key but B->CD voilates since B is not super key (not BCNF)

B)A is candidate key but B->C,C->D voilates since B,C are not super key (not BCNF)

C)AB , CB are candidate keys but C->D voilates since C alone is not SuperKey  (not BCNF)

D) A is candidate Key and satisfies BCNF

Therefore Option D is BCNF

A,B,C are not BCNF

Answer:

Related questions

46 votes
46 votes
6 answers
2
Kathleen asked Sep 14, 2014
8,529 views
Which of the following relational calculus expression is not safe?$\left\{t \mid \exists u \in R_1\left(t[A] = u[A]\right) \land \neg \exists s \in R_2 \left(t[A] = s[A]\...
7 votes
7 votes
3 answers
3
go_editor asked Feb 8, 2018
1,986 views
Consider a relation examinee (regno, name, score), where regno is the primary key to score is a real number.Write an SQL query to list the regno of examinees who have a s...