Decomposition example into 3NF by tkz53d

VIEWS: 55 PAGES: 4

									Decomposition example into 3NF


Remember it is always possible to decompose a relation
with functional dependencies F into a decomposed set of
tables which is 3NF, dependency preserving, and loss-less
join. The notes describe algorthims which are quite formal
and perhaps better suited to computer implementation
where the problems are complex. Below is a more intuitive
approach which is ussually sufficient for simple problems
where you can easily draw the dependencies and inspect
them for properties. That is, the approach below embodies
the algorthms but the steps are carried out by simple
inspection.


Consider the following example.
R=A,B,C,D,E,F,G
F= { AB->CD, C->EF, G->A, G->F, CE->F }
Step One, Find the attributes that could be keys for the
relation since no primary key is identified (i.e. underlined).
   G must be in any key since it is not determined by
anything (does not appear in the right side of any
dependency)
   B must be in any key for the same reason.
   Since GB+ contains all the others (i.e. GB determine
everything else by transitivity) GB is a key.
   Since any key must contain GB, GB is in fact the only
minimal key.
Step Two, Clean up the set of dependencies by removing
any that are implied by others (duplicates), or have
unecessary attributes in their left hand sides. You can also
combine dependencies with the same left hand sides. (In
effect we are finding a minimal cover, but by inspection)
So F will reduce as follows.
combine G->F and G->A into G->AF
combine C->EF and CE->F into C->EF only (E is not
necessary in CE->F, because C->F alone)
So we have F'c = { AB->CD, C->EF, G->AF }
Step Three. Make a table for each of these dependencies
with the left hand of the dependency being a key.
R1=ABCD, R2=CEF, R3=GAF.
If none of these tables contains the original key (GB) make
a table for the key. So we have as our decompostion:
R1=ABCD, R2=CEF, R3=GAF, R4=GB.



Claim: R1,R2,R3,R4 forms a lossless join, 3nf,
dependency preserving decompostion.
Proof:
Each of the minimal (necessary) dependencies appears with
one of the tables so they are all there. No other
dependencies are introduced that were not in our minimal
set so we did not add new constraints.
None of the tables will have any depencency which violates
3nf (i.e. has a left side that is neither a superkey nor a
proper subset of key)
The tables can be re-joined in such a way that whenever
two tables are joined, the attributes they have in common is
a key, or superkey of one of them. Intuitively, if XY is the
original key. Then any dependency is of the form XY->...,
X->..., XW->..., or W->,... In the first two cases the tables
can be joined with our table that has the key, in case 3 W
must be dependent directly or transitively on XY or X or Y
otherwise W would have to be in the key as well. Finally in
case W->..., W must be transitively dependent on XY or
part of XY otherwise W would have to be part of the key.
In any other these cases an appropriate join order can be
found.
For example: Lets join the 4 relations to reform the
original:
R34=R3*R4=GABF, i.e. GB * GAF have G in common
which is a superkey of GAF.
R134=R1*R34=GABCDF, i.e. ABCD * GABF have AB
in common which is a superkey of ABCD
R1234 = R2 * R134=GABCDEF, i.e. CEF * GABCDF
have CF in common which is a superkey of CEF




Example 2
Example: Consider R(ABCDEF) subject to the following
FDs F = { A BCE, B DE, ABD CF, AC DE }
/Give a lossless, dependeny preserving 3NF
decomposition also /
Solution: Find a canonical cover for ,F.
  1. After Left Reduction:

    Rule ABD CF becomes A CF

    Rule AC DE becomes A DE

    F= { A BCE, B DE, A CF, A DE }
  2. After Right Reduction:

    Rule A BCE becomes A B

    Rule A DE becomes A E

    F= { A B, B DE, A CF, A E }
  3. After Removing Redundant Rule(s):

Rule A E is redundant (Trans. on A B, B E )

         F= { A B, B DE, A CF } same as

         F= { A BCF, B DE }


Final Partition  = ( ABCF, BDE )

								
To top