# Dependency Preservation and Normal Forms

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?

• You have different attributes in R and in F. In F you have H, I and J, which are not present in R, and this is not possible. I and J are present in R2, but H disappear! There are errors in your data, errors for which your question is meaningless and cannot be ansered. Commented Oct 26, 2016 at 5:43
• @Renzo will cross check and update Commented Oct 26, 2016 at 6:20

Given your decomposition, a projection of F over R2 is:

``````D → J I
A → D E
``````

while the dependencies that hold on R1 are:

``````B → F
F → G H
``````

``````A B → C
``````

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.

• Thanks for your reply. The steps you took can be thought of taking the closure of the union of the projections of the FD's which hold on the decomposed relations is it? secondly does the 2NF doesn't always gives a decomposition which is dependency preserving? have i got the concepts clear? Commented Oct 27, 2016 at 5:07
• @ShubhamSinghrawat, 1) Yes (A way to see if some dependencies are lost is to see if the closure of the union of the projections of the FD is a cover of the original set of dependencies); 2) Yes: actually there is no formal algorithm to produce 2NF with properties like dependency or data preservation in database books. The 2NF has only historical interest, not practical o teorethical one, only BCNF and 3NF (and higher normal forms) have been studied in depth for their implications. Commented Oct 27, 2016 at 5:40

Given R(A B C D E F G H I J ) and the FD's specified above We would get three relations after normalizing it till 2NF: Since the FD: A->DE is not satisfying 2NF Finding A closure we get (ADEIJ). Since the FD B-> F also does not satisfy 2NF Finding B Closure we get (BFGH). Hence we get the relations: R1(ADEIJ) and R2(BFGH). A is the Candidate Key for R1; B is the Candidate Key for R2 What remains from R is C. So we get the third relation R3 (ABC).

This is a dependency preserving and loss less decomposition.

We could get there relation: Since A- DE is not satisfying 2NF Finding A Closure (ADEIJ) Since B-F also does not satisfy 2NF Finding B Closure (BFGH) What remains in R is C Hence weshould get three relations: R1 (ADEIJ) R2 (BFGH) R3 (ABC) Now this would be dependency preserving and loss less decomposition.