Dependency Preservation and Normal Forms

Question

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?

Answer 1

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

Instead, the dependency:

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.

Answer 2

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.

Answer 3

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.