Normal Forms and Decompositions

Tom Kelliher, CS 318

Apr. 1, 2002




Read 8.7--8.

Sample end-to-end application due Friday.

Each group must demonstrate its relations are in BCNF or 3NF (or show how to decompose the relations into BCNF or 3NF --- no need to implement) by the end of the semester.

From Last Time

Entailment checking.


  1. Normal Forms.

  2. Properties of Decompositions.

Coming Up

Synthesis/decomposition of BCNF and 3NF.

Normal Forms

Normal forms eliminate degrees of redundancy.

Example relation: (SSN, Name, Address, Hobby). FDs?

Example decomposed relation: (SSN, Name, Address), (SSN, Hobby).

Boyce-Codd Normal Form

A relational schema is in BCNF if for every FD either of the following is true:

  1. .

  2. is a superkey of .

Are either of the examples in BCNF?

Third Normal Form

A relational schema is in BCNF if for every FD either of the following is true:

  1. .

  2. is a superkey of .

  3. for some key of .

Which is true: all BCNF schemas are in 3NF, vice-versa, or none of the above?

Properties of Decompositions

  1. What is a decomposition?

  2. Lossless decompositions.

  3. Dependency preserving decompositions.

  4. Conclusions.

Definition of a Decomposition

A decomposition of is a set of schemas:

such that the following hold:

  1. .

  2. entails for all i.

The decomposition of a relation instance is defined similarly.

Lossless Decompositions

  1. We need:

    Why? Consider the ``ultimate'' redundancy eliminating ``decomposition'' of the example relation.

  2. This is always true:


  3. So we need to show:

  4. A binary decomposition will be lossless if either of the following is true:
    1. .

    2. .

    The justification isn't that hard, but we'll skip it.

Dependency-Preserving Decompositions

  1. Consider the schema HasAccount (AccountNumber, ClientId, OfficeId) with FDs:
    1. ClientId, OfficeId AccountNumber

    2. AccountNumber OfficeId

    It has been decomposed into: (AccountNumber, OfficeId) and (AccountNumber, ClientId). What about the FDs?

  2. A decomposition is dependency-preserving iff

    How do we show this?

  3. Decompositions which are not dependency-preserving require extra work on updates!

  4. Consider and one of the schemas of the decomposition: . We define:

    The idea is to use this projection to define .

  5. Computing these projections is exponential in the size of !


  1. All things being equal, BCNF is preferable to 3NF.

  2. Not all BCNF decompositions are dependency-preserving.

    A problem in update-intensive environments.

  3. When BCNF decomposition results in a dependency-preserving set of relations, use the BCNF.

    Otherwise, consider using 3NF.

Thomas P. Kelliher
Sun Mar 31 11:31:30 EST 2002
Tom Kelliher