GATE Overflow Test Series | Databases | Test 1 | Question: 27

Question

Consider relation $R(A,B,C,D,E,F,G,H)$ and the functional dependencies $\{AB \rightarrow C, BD \rightarrow EF, A \rightarrow G, F \rightarrow H\}.$ If $2\;NF$ decomposition of $R$ requires minimum $'a'$ relations, and $3\;NF$ decomposition requires minimum $'b'$ relations, the value of $a\times b =$ _____

Comments

Correct answer is 20

R(ABD)  R(ABC) R(BDEFH) R(AG) is 2NF

R(ABD) R(ABC) R(BDEF) R(FH) R(AG) is 3NF

 

4*5 =20

only mistake i did while solving is not storing one entire table for the CK itself

Answer 1

The only candidate key is $\{ABD\}.$

So, $AB \to C, BD \to EF$ and $A \to G$ violates 2NF. To make the relation in 2NF, we can decompose $R$ to $R_1(A,B,C), R_2(A,B,D),R_3(A,G), R_4(B,D,E,F,H)$

In the above decomposition we do not have any partial FD but transitive dependency $F \to H$ is there. To avoid this $R_4$ must be split to $R_5(B,D,E,F)$ and $R_6(F,H).$

Thus $a = 4, b = 5 \implies a \times b = 20.$

Comments

Please take a look at this

---

By including R(ABD) you can have dependency preserving and lossless join. As there you can’t have a key for X(Any relation) and R(BDEFH) which can identify any (BDEFH) or say X.

---

@Deepak Poonia  sir
given solution is incorrect, answer should a = 3, b = 4..
wrong thing is that, given solution has not been update since 2020