Normal Forms and Decompositions

Tom Kelliher, CS 318

Apr. 1, 2002

Administrivia

Announcements

Assignment

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.

Outline

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 .

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 .

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.

Lossless Decompositions

1. We need:

   

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

2. This is always true:

   

   Why?

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 !

Conclusions

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.