**Tom Kelliher, CS 318**

**Apr. 3, 2002**

Read 9.1--3.

Normal forms; properties of decompositions.

- Preliminaries: minimal covers.
- 3NF synthesis.
- Finding BCNF schemas.

Triggers.

- Sets of FDs aren't unique. It'd be nice to have a canonical set.
Minimal covers come close.

- Let be a set of FDs. The set of FDs is a minimal
cover of if:
- is equivalent to .
- All FDs in have the form
where
**A**is a singleton attribute. - It is not possible to make smaller by either:
- Deleting an FD from .
- Deleting an attribute from the LHS of one of 's FDs.

- Deleting an FD from .

- is equivalent to .
- Algorithm for finding a minimal cover of :
- Compute := where the FDs in have
singleton RHSs.
Note: Just apply FD decomposition.

- Remove redundant attributes from the RHSs of FDs of .
Note on computation: To check to see if in of is redundant, check whether or not is entailed by .

- Remove redundant FDs from .
Note on computation: To check to see if an FD of is redundant, create which is just without the candidate redundant FD. Check whether or not the candidate FD is entailed by .

- Compute := where the FDs in have
singleton RHSs.
- Example. Find a minimal cover of:

- Algorithm works by collecting individual attributes into groups,
forming relations, as opposed to decomposing relations.
- Algorithm. Given
- Find a minimal cover, , of .
- Partition into sets where the FDs in a
all have the same LHS. Minimize .
- For each , the corresponding relation schema is where contains all
the attributes mentioned in .
- If any of the is a superkey of
**R**, we are done. (Check .)Otherwise, let be a key of . Add to the schema set.

Why is the resulting schema

- in 3NF?
- dependency preserving?
- lossless?

- Find a minimal cover, , of .
- It would appear the LHSs of FDs of any of the relations form
superkeys of their respective relations. Why aren't the schemas
necessarily in BCNF?
Consider the following schema : and contains

This will synthesize into two relations. The third FD actually will apply to both relations.

- A good overall strategy: synthesize a 3NF schema. If it is also in
BCNF, we are done. Otherwise, perform BCNF decomposition on the 3NF.
Recall, the 3NF will be dependency preserving. A dependency preserving BCNF schema is our first choice.

- Algorithm. Given .
Decomposition :=

**while**there is a schema in Decomposition that is not in BCNF**do**

/* Assume in violates BCNF. */

Replace in Decomposition with schemas and

where and are all the FDs of that involve only

attributes from their respective schemas

**end**Questions:

- After an iteration, why will
no longer violate BCNF?
- Why might this not be dependency preserving?
- Why is this lossless?

- After an iteration, why will
no longer violate BCNF?
- Example. Find a BCNF decomposition of where and contains:

Tue Apr 2 12:11:09 EST 2002