1

Database Systems, 6th ed by Ramez Elmasri and Shamkant Navathe Chapter 15, section 15.2 Properties of Relational Decompositions:

Claim 1. It is always possible to find a dependency-preserving decomposition $D$ with respect to $F$ [$F$ is the set of FDs of a relation $R$] such that each relation $R_i$ in $D$ is in 3NF

Is it possible to have multiple (different) decompositions of a relation into 3NF, as the claim seems to imply?

If yes:

Is every decomposition into 3NF dependency preserving?

If not:

What is an example where a decomposition into 3NF does not uphold the dependency preserving property?

What about the lossless join property?

philipxy
  • 156
  • 1
  • 9
Ebin J
  • 19
  • 1

1 Answers1

1
  1. Is it possible to have multiple (different) decompositions of a relation into 3NF, as the claim seems to imply?

Yes, consider for instance the relation schema R(A, B, C, D), with F, a cover of the functional dependencies holding in R, equal to:

A → B
C A → D
D → A

Both the decomposition:

R1(A B)
R2(A D)
R3(C D)

and

R1(A B)
R2(A C D)

are in 3NF (actually the first is also in BCNF).

  1. Is it the case that every decomposition (assuming the answer to first is Yes) of a relation into 3NF is dependency preserving?

No, in the first decomposition the functional dependency AC → D is not preserved. Note that both decompositions are lossless (in general, there is no interest in lossy decompositions).

Final note: since there are different possible decompositions, these and their properties depends on the algorithm used to find them. For instance, the classical “analysis” algorithm for BCNF is guaranteed to produce a lossless decomposition, but it is not guaranteed to preserve the dependencies, while the classical “synthesis” algorithm to produce the 3NF is guaranteed to be lossless and preserve the dependencies, as well (but it could find a decomposition in which not all the anomalies are eliminated).

Renzo
  • 829
  • 6
  • 10