Given the follwing functional dependencies, it is a little bit confusing for me because third normal form says no non-prime attribute of R is transitively dependent on the primary key. So i removed the functional dependency C --> DE from table and placed it in new relation but all these attributes can also be determinded by the primary key of the relation. I think that i can't remove D and E from this table or should i remove because further BCNF also does not help in removing these attributes.Question is when i remove first functional dependency should i also remove D and E from the first table?enter image description here
To put a relation into a given NF (normal form) you should follow an algorithm that has been advised for that NF. (Eg given some FDs, there are lots of others that hold, per Armstrong's axioms; you need to deal with them too. Eg there are certain benefits to "preserving" FDs when possible, and a decomposition to 3NF components that preserves FDs is always possible; but if we decompose so that some FD's attributes are split between components, we can fail to preserve FDs.)
Note that these algorithms do not involve first normalizing to lower NFs. (That can stop "good" higher-NF designs from being the final result.)
When you do decompose to get rid of a FD X -> Y from a relation with attributes R, the decomposition will always be non-loss/non-additive if the components have attribute sets X U Y and R - Y. By repeated decompositions all your components will eventually be in the NF you want (if it is BCNF or below). But your overall decomposition won't necessarily be as "nice" as an advised algorithm would give you.
