159 views
1 1 vote

Consider $R(A,B,C,D,E)$ with $\Sigma=\{A\to B,\ AE\to D,\ B\to E,\ AD\to E,\ CD\to A,\ AB\to D,\ E\to B\}$.

Which of the following correctly gives a lossless, dependency-preserving decomposition of $R$ while achieving the highest normal form possible under these requirements?

  1. $\{ABD,\ ACD,\ BE\}$, and the decomposition is in $3$NF but not BCNF.
     
  2. $\{ABD,\ ACD,\ BE\}$, and the decomposition is in BCNF.
     
  3. $\{ABE,\ ACD\}$, and the decomposition is in BCNF.
     
  4. $\{AB,\ BE,\ CD\}$, and the decomposition is in $3$NF.

1 Answer

1 1 vote

Given $R(A,B,C,D,E)$ with $\Sigma=\{A\to B,\ AE\to D,\ B\to E,\ AD\to E,\ CD\to A,\ AB\to D,\ E\to B\}$.

From $A\to B$ and $B\to E$,

we get $A\to E$.

Also, since $A\to B$ and $AB\to D$,

we get $A\to D$.

Hence, $A\to BDE$.

Now $CD\to A$, and since $A\to BDE$, $CD\to ABCDE$.

So $CD$ is a candidate key of $R$.

Consider the decomposition $\{ABD,\ ACD,\ BE\}$.

In $ABD$, $A\to B$ and $A\to D$,

so $A$ is a key. Hence $ABD$ is in BCNF.

In $BE$, $B\to E$ and $E\to B$, so both $B$ and $E$ are keys. 

Hence $BE$ is in BCNF.

In $ACD$, $CD\to A$ and $A\to D$.

Here $A\to D$ violates BCNF because $A$ is not a superkey of $ACD$.

However, $AC$ and $CD$ are candidate keys of $ACD$, so $D$ is a prime attribute.

$\therefore A\to D$ satisfies $3$NF.

Hence $ACD$ is in $3$NF but not BCNF.

The decomposition is dependency-preserving because

$A\to B$ and $A\to D$ are preserved in $ABD$,

$B\to E$ and $E\to B$ are preserved in $BE$,

and

$CD\to A$ is preserved in $ACD$.

The remaining dependencies follow from these.

For example,

$AE\to D$ follows from $A\to D$,

$AD\to E$ follows from $A\to B$ and $B\to E$,

and

$AB\to D$ follows from $A\to D$.

The decomposition is also lossless because the synthesis contains a candidate key, namely $CD$, inside $ACD$.

Answer : A

Answer:
Position:
Show:

Related questions

1 1 vote
1 1 answer
105
105 views
GO Classes asked Sep 19
105 views
Consider $R(A,B,C,D,E,F,G)$ with $F=\{AB\to CF,\ CD\to EA,\ E\to ABC,\ B\to F,\ C\to D\}$.Suppose $R$ is decomposed into $R_1(A,B,C,D,E,G)$ and $R_2(B,F)$.Which statement...
2 2 votes
1 1 answer
100
100 views
GO Classes asked Sep 19
100 views
Which statement correctly describes the guarantee of the standard BCNF decomposition procedure?Every relation has a decomposition that is simultaneously BCNF, lossless an...
4 4 votes
1 1 answer
109
109 views
GO Classes asked Sep 19
109 views
Consider $R(A,B,C,D,E,F)$ with $F=\{A\to B,\ A\to C,\ BC\to E,\ BC\to D,\ E\to F,\ BC\to F\}$.Suppose the following BCNF decomposition is used:$R_1(B,C,E)$$R_2(B,C,F)$$R_...
2 2 votes
1 1 answer
102
102 views
GO Classes asked Sep 19
102 views
Consider $R(A,B,C,D,E,F,G)$ with $F=\{BCD\to A,\ BC\to E,\ A\to F,\ F\to G,\ C\to D,\ A\to G\}$.Which of the following statements are correct after applying the $\text{3N...