**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.

Entailment checking.

- Normal Forms.
- Properties of Decompositions.

Synthesis/decomposition of BCNF and 3NF.

Normal forms eliminate degrees of redundancy.

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

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

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

- .
- is a superkey of .

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

- .
- is a superkey of .
- for some key of .

- What is a decomposition?
- Lossless decompositions.
- Dependency preserving decompositions.
- Conclusions.

A decomposition of is a set of schemas:

such that the following hold:

- .
- entails for all
**i**.

- We need:
Why? Consider the ``ultimate'' redundancy eliminating ``decomposition'' of the example relation.

- This is always true:
Why?

- So we need to show:
- A binary decomposition will be lossless if either of the following is
true:
- .
- .

- .

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

- ClientId, OfficeId AccountNumber
- A decomposition is dependency-preserving iff
How do we show this?

- Decompositions which are not dependency-preserving require extra work
on updates!
- Consider and one of the schemas
of the decomposition: . We define:
The idea is to use this projection to define .

- Computing these projections is exponential in the size of !

- All things being equal, BCNF is preferable to 3NF.
- Not all BCNF decompositions are dependency-preserving.
A problem in update-intensive environments.

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

Sun Mar 31 11:31:30 EST 2002