Finding Schema Normal Forms
Tom Kelliher, CS 318
Apr. 3, 2002
Normal forms; properties of decompositions.
- Preliminaries: minimal covers.
- 3NF synthesis.
- Finding BCNF schemas.
- 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.
- Algorithm for finding a minimal cover of :
The resulting is a minimal cover of .
- Compute := where the FDs in have
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
- 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.
Otherwise, let be a key of . Add to the schema set.
Why is the resulting schema
- in 3NF?
- dependency preserving?
- 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 :
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 .
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
- After an iteration, why will
no longer violate BCNF?
- Why might this not be dependency preserving?
- Why is this lossless?
- Example. Find a BCNF decomposition of where and contains:
Thomas P. Kelliher
Tue Apr 2 12:11:09 EST 2002