# Chapter 15 Relational Database Design Algorithm and Further

Document Sample

```					          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

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