Gate: Normalization

Question

Consider a relation $R (A, B, C, D, E, F, G, H) $     where each attribute is atomic, and following functional dependencies exist.
Following relation is in $1 NF, $ I help me to decompose relation into $BCNF$

$CH\rightarrow G$

$A\rightarrow BC$

$B\rightarrow CFH$

$E \rightarrow A$

$F\rightarrow EG$

Comments

We get these 3 CHG, ABCEFH and AD relations in BCNF which is lossless and all fd’s can be preserved.

Answer 1

For the given relation R (A,B,C,D,E,F,G,H) we have the following candidate keys: AD, BD, DE, DF.

Step 1:

A--> BC is a partial dependency.

Let us decompose R(A,B,C,D,E,F,G,H) to R1(A,B,C,E,F,G,H) and R2(A,D)

Union of R1 and R2 has all the attributes of R and the intersection A is the primary key of R1

Till now all functional dependencies are preserved.

R2(A,D) is in BCNF

In R1, A,B, E and F are candidate keys, However CH is not a super key. So we decompose further.

Step 2:

R1(A,B,C,E,F,G,H) decomposes into R3(A,B,C,E,F,H) and R4(C,H,G)

Union of R3 and R4 has all attributes of R2 and the intersection CH is primary key of R4

Dependencies in R4: CH-->G
CH is super key of R4, so R4 is in BCNF

Dependencies in R3: A-->BC, B-->CFH , E-->A, F-->EG
A,B,E,F are super keys in R3 so R3 is in BCNF

 

R (A,B,C,D,E,F,G,H) can be thus decomposed into R'(A,D) , R''(A,B,C,E,F,H) and R'''(C,H,G)

Answer 2

R1(A,B),R2(CHG),R3(EA),R4(FE),R5(CDFH)