normalisation in DBMS

Question

For the relation schema R(A, B, C, D, E) with the following FDs

FD1: {A,B,C} → {D,E} 
FD2: {B,C,D} → {A,E} 
FD3: {C} → {D}

I Decomposed R into a set of BCNF relations.
I got candidate keys as {A,B,C}, {B,C,D} respectively.. how do I find the subset of {A,B,C}?

My solution :
FD3 violates BCNF. Decomposing R using FD3.

R1(C,D) with FDs: 
    FD3, 
    CK: {C} 
R2(A,B,C,E) 
    with new FD: {A,B,C} → E (Decomposed from FD1) , 
    CK: {A,B,C} {BC}+ = {BC}

Not a candidate key {ABC}+ = {ABCDE}
A candidate key {BCD}+ = {ABCDE}
A candidate key Candidate Keys = {ABC}, {BCD}

The result of the decomposition consists of R1 and R2.

But I was said there still exists a subset of {A,B,C} I’m unable to find it.

score 3 · Answer 1 · answered Jan 09 '22 at 11:32

Assuming that the FD are a cover of the functional dependencies of R, you can find a minimal cover of them:

B C -> A
B C -> E
C -> D

From this minimal cover, one can find easily the only candidate key, which is {B, C}.

As you have correctly said, the dependency C -> D violates the BCNF, so we can decompose R in R1, R2:

R1 = {C D}, with the the projected dependency C -> D, and C the only candidate key
R2 = {A B C E}, the projected dependencies B C -> A, B C -> E, and the only candidate key B C

Both R1 and R2 are in BCNF.

normalisation in DBMS

1 Answers1