I have the below functional dependencies in minimal cover, and I am looking to put them into 3NF lossless join and dependency preserving decomposition.
FD 1: {A} -> {B,C,D,E};
FD 2: {F} -> {G,H,I,J,K};
FD 3: {G,H,I,L} -> {M};
FD 4: {N} -> {O,P,F};
FD 5: {N,B} -> {Q,L};
Key: {A,N}
The key is A,N and are the determinants of FD1 and FD4, I am unsure whether my 3NF decomposition should contain and extra table that contains all of the attributes from the FD1 and FD4 so that I can preserve the key? or that they should not be merged so that I can reduce redundancy if they were to be merged?
Thanks in advance!
In general the 3NF is found through an algorithm that details all the steps that produce a correct decomposition. Such algorithms are described in all good books on databases. For instance, in your case, the so-called “synthesis” algorithm produces the following decomposition:
R1 {A B C D E}
R2 {B L N Q}
R3 {F G H I J K}
R4 {G H I L M}
R5 {F N O P}
R6 {A N}
Note that the relation R6 contains the key of the original relation, and this is necessary to guarantee the lossless join property.