Docstoc

Chapter 15 Relational Database Design Algorithm and Further

Document Sample
Chapter 15 Relational Database Design Algorithm and Further Powered By Docstoc
					          Csci455
reza@aero.und.edu




                    1
    Properties of Decomposition
    1. dependency preservation property
    2. the lossless (or nonadditive) join property
    4NF
    5NF




                                                     2
    DB deign algorithm works with universal
    relation schema
    ◦ R={A1, A2,…, An}
    ◦ F is a set of FDs
    ◦ using F and the set of design algorithms, R can
      be decomposed
         D= {R1, R2,…Rm}
   Attribute preservation condition
    ◦   i=1
               m   Ri =R




                                                        3
   Dependency Preservation Condition (DPC)
    w.r.t FD = {fd1,…fdn}
    ◦ For each fd FD,fd must either appeared directly
      in one Ri D= {R1,…Rn}
    ◦ Or, fd MUST be inferred from some Ri
    ◦ (( R1(F))...   Rm(F)))+ = F+
      Where
         Ri(F) means the projection of FD on Ri
    ◦ A dependency UVFD+ is in  Ri(F) means all
      the attributes in U and V are also in Ri


                                                         4
◦ If DPC does not hold, then some FDs is lost
◦ To test the DPC
  join two or more relations in D to get a
   relation having LHS and RHS attributes of
   the Lost Dependency
  Check the new relation to see if the FD
   holds
  Not practical




                                                5
   This property guarantees that no spurious
    tuples are generated when a natural join
    operation is performed




                                                6
    A decomposition of R into two schema, R1.X,
    and R2.Y is said to be lossless-join
    decomposition w.r.t FD
    ◦ if the following condition holds
    ◦ D: *R1(r),…,Rm (r) = r.
      i.e., the original relation, r, can be recovered by
       performing natural joint operations on .Ris
   Lossless means loss of information
   If decomposition does not hold lossless join
    property, we may get additional spurious
    tuples

                                                             7
 S P D        S    P    P D      S    P D
s1 p1 d1      s1   p1   p1 d1    s1   p1 d1
s2 p2 d2      s2   p2   p2 d2    s2   p2 d2
s3 p1 d3      s3   p1   p1 d3    s3   p1 d3
Instance r   SP(r)     PD(r)   s1   p1 d3
                                 s3   p1 d1
                                 SP(r)⋈ PD(r)




                                                  8
DNUMBER is common to both decomposed relations;
it is a key in one relation, and is FK in another relation




                              DNUMBERDNAME, DMGRSSN

                                                             9
   Multivalued dependency(MVD) XY on R,
    ◦ where XYR, and Z = (R – (XY)) specifies the
      following conditions on r(R)
   t3[X]= t4[X]= t1[X]= t2[X]
   t3[Y]=t1[Y] and t4[Y] = t2[Y]
   t3[Z]=t2[Z] and t4[Z] = t1 [Z]




                                                       10
   Trivial MVD:
    ◦ An MVD XY in R is called trivial MVD if
    ◦ a) Y is a subset of X or
    ◦ b) XY=R
   Non-trivial MVD
    ◦ An MVD that satisfies neither (a) nor (b)




                                                  11
S P D      S P     S D
s1 p1 d2   s1 p1   s1 d1
s1 p2 d1   s1 p2   s1 d2
s1 p1 d1   SP      PD
s1 p2 d2
SPD




                           12
   R is in 4NF w.r.t to set of dependency F if, for
    every nontrivial MVD XY in F+, X is a
    superkey for R
   Violated when a relation contains undesirable
    MVD
   Used to identify and decompose such
    relations




                                                       13
   Join dependency (JD),
    ◦ denoted by JD(R1,R2,…,Rm) on R,
    ◦ specifies a constraint that every r(R) (i.e., legal
      state) should have the following property
      *(   R1(r),…,      Rm(r))   =r




                                                            14
   Trivial JD:
    ◦ for any JD(R1,…,Rn) where Ri = R
    ◦ Called trivial because it has lossless join property
      for any r( R) without any constraint on R




                                                             15
A relation R is in 5NF (PJNF) w.r.t to F, if, for
 every non-trivial join dependency JD in F+,
 every Ri is a superkey of R




                                                    16
                             Super key


S P     J   S    P    P    J                S     J
s1 p1   X   s1   p1   p1   X                s1    X
s1 p2   Y   s1   p2   p2   Y                s1    Y
s2 p1   Y   s2   p1   p1   Y                s2    Y
s3 p2   Z   s3   p2   p2   Z                s3    Z
s2 p3   X   s2   p3   p3   X                s2    X
s2 p1   X    SP        PJ                       SJ
s1 p1   Y                   Constraint holds:
                            whenever a s supplies part p,
SPJ                         and project j uses part p,
                            and supplier s supplies at least one part to project j, then s will
                            also supplying part p to j
                                                                                                  17

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:1
posted:2/12/2012
language:
pages:17