Suppose a set of functional dependencies F holds over a relation R . We find that the dependencies violate the conditions of 2NF and decompose the relation into R1 and R2. After the process what can i say about the set R. In other words are all the dependencies preserved if not how to we find the new set of dependencies.
Do we need to specify the dependency set for each of the decomposed relation?
As an example let us consider the set
R = {a, b, c, d, e, f, g , h , i , j}
F = {AB -> C, A -> DE, B -> F, F -> GH, D -> IJ }
Computing the closure we find AB to be a candidate key of R
this leads to the violation of 2NF by the dependency A -> DE We compute A+ = {A , D , E , I , J }
the Decomposition therefore is R1 = {B , C , F , G ,H} R2 = { A , D , E , I , J}
After this step i am unsure how to check the new relations for violations , which set of dependencies do i take as a reference for checking for violations.
The way we have constructed R2 ensures no violation of 2NF but what about the Relation R1?
Best Answer
Given your decomposition, a projection of F over R2 is:
while the dependencies that hold on R1 are:
Instead, the dependency:
is lost.
The reason is that the attributes of this dependency are in two different tables, and there is no way to obtain it, since the tables do not have attributes in common.
A decomposition that preserves the dependencies can be obtained by applying the analysis algorithm to produce the Boyce-Codd Normal Form, or the synthesis algorithm to produce the Third Normal Form.