Finding Schema Normal Forms

Tom Kelliher, CS 318

Apr. 3, 2002

Administrivia

Announcements

Assignment

Read 9.1--3.

From Last Time

Normal forms; properties of decompositions.

Outline

1. Preliminaries: minimal covers.

2. 3NF synthesis.

3. Finding BCNF schemas.

Coming Up

Triggers.

Preliminaries

Minimal Cover

1. Sets of FDs aren't unique. It'd be nice to have a canonical set.

   Minimal covers come close.

2. Let be a set of FDs. The set of FDs is a minimal cover of if:
   1. is equivalent to .

   2. All FDs in have the form where A is a singleton attribute.

   3. It is not possible to make smaller by either:
      1. Deleting an FD from .

      2. Deleting an attribute from the LHS of one of 's FDs.

3. Algorithm for finding a minimal cover of :
   1. Compute := where the FDs in have singleton RHSs.

      Note: Just apply FD decomposition.

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

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

   The resulting is a minimal cover of .

4. Example. Find a minimal cover of:

   

Synthesizing 3NF Schemas

1. Algorithm works by collecting individual attributes into groups, forming relations, as opposed to decomposing relations.

2. Algorithm. Given
   1. Find a minimal cover, , of .

   2. Partition into sets where the FDs in a all have the same LHS. Minimize .

   3. For each , the corresponding relation schema is where contains all the attributes mentioned in .

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

   1. in 3NF?

   2. dependency preserving?

   3. lossless?

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

Finding BCNF Schemas

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

2. 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:

   1. After an iteration, why will no longer violate BCNF?

   2. Why might this not be dependency preserving?

   3. Why is this lossless?

3. Example. Find a BCNF decomposition of where and contains: