# Normal Forms and Decompositions

Tom Kelliher, CS 318

Apr. 1, 2002

### Assignment

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 .

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:

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.

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