Normal Form Design 

Algorithm

Example
  1. Compute the canonical cover C
  2. Decompose the original relation into a set of 3NF relations

    3. that have the following properties 
    • Preservation of the fds in C 
    • Lossless Join 
    • Minimal number of relations 
  3. Check for BCNF 
 Let r(A, B, C, D, E, F) be a relation with the following fds:
  1. AB C,
  2. A B,
  3. B D,
  4. C D,
  5. D C.

Canonical Cover

Let F be a set of fds. Every F' that is a subset of F and that implies all the  fds in F is said to be a cover  for F. If no proper subset of F' is a cover for F, then F' is said to be a minimal cover for F.  Minimal covers are not unique: for 3NF design we use a particular kind of minimal cover, called  a canonical cover. Canonical covers are  computed as follows: 
  1. Decompose fds into non-trivial fds that have only one attribute at the right side. 
  2. Reduce left sides. For each X A in   F,  where B in X, and X- {B} A is implied by F, Replace X A with X- {B} A.
  3. Eliminate redundant fds. An fd f in F is redundant if f is implied by F- {f}. 

    4. (an fd can be redundant  only if there exist another fd with the same left side; if an fd is not redundant in the original set, it cannot be redundant in later steps)
 Step 1. Decompose fds. No change 

Step 2. Reduce left side: fds with only one attribute on the left side need not be considered . 
The question for AB Cis: can it can be replaced by (a) A C, [orby (b) B C ? ]. 

Now A+ = {ABDC}. Thus 1, can be replace by (a). 

Step3:  Eliminate redundant fds 

    Is (a) redundant? A+ = {A, B, D, C}. Thus (a) is redundant and it is eliminated. 

    is 2 redundant? A+ = {A}: 2 is not redundant, 

    is 3 redundant B+ = {B}: 3 is not redundant, 

    is 4 redundant? C+ = {C}: 4 is not redundant, 

    is 5 redundant? D+ = {D}: D is not redundant. 

Thus a canoninical cover consists of the following fds: {2, 3, 4, 5}. 

Decomposition of relation r(U)

Let  C be a canonical cover: Then perform the following steps: 
  1. Preservation of of the fds of r(U):

    2.    Compute S as follows S = {X È {A} | X A in C }.
  2. Lossless Join Property: 

    3. If the key of some relation in S is also the key of the original relation r(U) we are done. Otherwise, add W, where W is a key of the original relation. 
  3. Minimize the count of the relations produced:

    4. Join any two relations in S  whose  keys are in one-to-one correspondence.  Thus if X is the key for the first relation and Y the key for the second one, it must be that X Y, Y X. Repeat this step while applicable. 
 
Decomposition into 3NF
  1. S = {(A,B), (B,D), (C,D), (D,C)} 

    2. with respective keys: A, B, C, and D (also undescored)
  2. None of these keys is a key for the original relation. AEF is a key of such a relation and therefore (A,E, F) is added to the decomposition. 
    3. Now merging relations with equivalent keys:  D C and C D,  thus we merge those two relations and get: 
3NF Schema = {(A,B), (B,D), (C,D), (A,E,F)} with respective keys, A, B, both C and D, and AEF.
Checking for BCNF Every fd has a key as its left side. 
Thus we have BCNF