BCNF & Lossless Decomposition

Document Sample
BCNF & Lossless Decomposition Powered By Docstoc
					BCNF & Lossless Decomposition

         Prof. Sin-Min Lee
   Department of Computer Science
               Normalization
 Review on Keys
  •   superkey: a set of attributes which will uniquely
      identify each tuple in a relation
  •   candidate key: a minimal superkey
  •   primary key: a chosen candidate key
  •   secondary key: all the rest of candiate keys
  •   prime attribute: an attribute that is a part of a
      candidate key (key column)
  •   nonprime attribute: a nonkey column
             Normalization
 Functional Dependency Type by Keys
  • ‘whole (candidate) key  nonprime attribute’: full
    FD (no violation)
  • ‘partial key  nonprime attribute’: partial FD
    (violation of 2NF)
  • ‘nonprime attribute  nonprime attribute’:
    transitive FD (violation of 3NF)
  • ‘not a whole key  prime attribute’: violation of
    BCNF
         Functional Dependencies
• Let R be a relation schema
         R and   R
• The functional dependency
       
  holds on R iff for any legal relations r(R), whenever two tuples t1
  and t2 of r have same values for , they have same values for .

       t1[] = t2 []  t1[ ] = t2 [ ]
               A   B


               1   4
               1   5
               3   7


• On this instance, A  B does NOT hold, but B  A does hold.
                   1. Closure
• Given a set of functional dependencies, F, its
  closure, F+ , is all FDs that are implied by FDs in
  F.

• e.g. If A  B, and B  C,
•     then clearly A  C
             Armstrong’s Axioms
• We can find F+ by applying Armstrong’s
  Axioms:
  – if   , then               (reflexivity)
  – if   , then             (augmentation)
  – if   , and   , then    (transitivity)


• These rules are
  – sound (generate only functional dependencies that
    actually hold) and
  – complete (generate all functional dependencies that
    hold).
               Additional rules
• If    and   , then     (union)
• If    , then    and    (decomposition)
• If    and    , then    
  (pseudotransitivity)


The above rules can be inferred from Armstrong’s
  axioms.
                     Example
• R = (A, B, C, G, H, I)
  F={ AB
       AC
     CG  H
     CG  I
       B  H}
• Some members of F+
  – AH
      • by transitivity from A  B and B  H
  – AG  I
      • by augmenting A  C with G, to get AG  CG
                  and then transitivity with CG  I
  – CG  HI
      • by augmenting CG  I to infer CG  CGI,
        and augmenting of CG  H to infer CGI  HI,
                    and then transitivity
       2. Closure of an attribute set
• Given a set of attributes A and a set of FDs F, closure of A
  under F is the set of all attributes implied by A

• In other words, the largest B such that:
•     AB
• Redefining super keys:
•     The closure of a super key is the entire relation
  schema
• Redefining candidate keys:
•     1. It is a super key
•    2. No subset of it is a super key
Computing the closure for A
• Simple algorithm

• 1. Start with B = A.
• 2. Go over all functional dependencies,  
   , in F+
• 3. If   B, then
•           Add  to B
• 4. Repeat till B changes
• R = (A, B, C, G, H, I)   Example
  F={ AB
        AC
      CG  H
      CG  I
        B  H}

• (AG) + ?
•      1. result = AG
  2. result = ABCG    (A  C and A  B)
  3. result = ABCGH (CG  H and CG  AGBC)
  4. result = ABCGHI (CG  I and CG  AGBCH


Is (AG) a candidate key ?
    1. It is a super key.
    2. (A+) = BC, (G+) = G.
     Uses of attribute set closures
• Determining superkeys and candidate keys

• Determining if A  B is a valid FD
 •    Check if A+ contains B


• Can be used to compute F+
            Database Normalization
Functional dependency (FD)             X      Y means that if
there is only one possible value of Y for every value of X, then
Y is Functionally dependent on X.

Is the following FDs hold?              X          Y          Z
                                        10         B1         C1
   X      Y   Y      Z      10         B2         C2
                                        11         B4         C1
  Z      Y    Y      X
                                        12         B3         C4
                                        13         B1         C1
                                        14         B3         C4
         Database Normalization
• Functional Dependency is “good”. With functional
  dependency the primary key (Attribute A) determines the
  value of all the other non-key attributes (Attributes
  B,C,D,etc.)
• Transitive dependency is “bad”. Transitive dependency
  exists if the primary/candidate key (Attribute A) determines
  non-key Attribute B, and Attribute B determines non-key
  Attribute C.
• If a relation schema has more than one key, each is called a
  candidate key
• An attribute in a relation schema R is called prim if it is a
  member of some candidate key of R
       First Normal Form (1NF)
Each attribute must be atomic (single value)
  • No repeating columns within a row (composite attributes)
  • No multi-valued columns.

1NF simplifies attributes
  • Queries become easier.
                       1NF
         Deptno      Dname             Location
           10           IT        Leeds, Bradford, Kent

           20        Research         Hundredfold
           30        Marketing           Leeds


                         Deptno         Location
Deptno    Dname              10            Leeds
                             10          Bradfprd
  10        IT
                             10            Kent
  20      Research
                             20        Hundredfold
  30     Marketing
                             30            Leeds
       Second Normal Form (2NF)
Each attribute must be functionally dependent on
the primary key.
• If the primary key is a single attribute, then the relation is in 2NF
• The test for 2NF involves testing for FDs whose left-hand-side
  attribute are part of the primary key
• Disallow partial dependency, where non-keys attributes depend on
  part of a composite primary key
• In short, remove partial dependencies


2NF improves data integrity.
  • Prevents update, insert, and delete anomalies.
                                2NF
      PNo     PName   PLoc     EmpNo EName       Salary    Address   HoursNo


Given the following FDs:
PNo , Em pNo      HoursNo
PNo      Dnam e Loc
                     ,
Em pNo      Nam e, Salary, Address


Assuming all attributes are atomic, is the above relation in
   the 1NF, 2NF ?
        Relation X1                                Relation X3
    PNo       PName     PLoc               PNo            EmpNo      HoursNo

Relation X2
EmpNo     EName       Salary     Address
       Third Normal Form (3NF)
Remove transitive dependencies.
Transitive dependency
   A non-prime    attribute is dependent on another, non-prime
   attribute or attributes
   Attribute is the result of a calculation


Examples:
       Area code attribute based on City attribute of a customer
       Total price attribute of order entry based on quantity attribute
       and unit price attribute (calculated value)


Solution:
• Any transitive dependencies are moved into a smaller table.
              Transitive Dependence
Give a relation R,               EmpNo       EName          Salary       Address
Assume the following FD hold:
                                    Ename     Address
Note : Both Ename and Address attributes are non-key attributes in R, and since
Address depends on a non-Prime attribute Name, which depends on the primary
key(EmpNo), a transitive dependency exists
      EmpNo      Ename, Ename     Addresst EmpNo      Address
                                                   ,

                        R1                                R2
        EmpNo        EName       Salary             Ename      Address


 Note : If address is a prime attribute Then R is in 3NF
           Modification Anomalies
Patron Patron Book Book           Book Borrow Due      Return
Name Address ID    Title          Author Date Date     Date
Smith    12 Elk    AAA   Peace    Bart   2/4    2/18   2/15
Jones    25 Sun    BBB   War      Hine   2/4    2/18   2/19
Hart     73 Sera   CCC   System   Vang   2/5    2/19   2/23
Hicks    22 Main   AAA   Peace    Bart   2/12   2/25   2/28
Rice     69 Witt   DDD   Spring   Lyon   2/6    2/20   2/8
Jones    25 Sun    CCC   System   Vang   1/26   2/7    2/6
  • What happens when you want to
        – add a new book?
        – change the address of a patron?
        – delete a patron record?
      Modification Anomalies
• Deletion anomaly
  – deleting one fact about an entity deletes a fact
    about another entity
• Insertion anomaly
  – cannot insert one fact about an entity unless a
    fact about another entity is also added
• Update anomaly
  – changing one fact about an entity requires
    multiple changes to a table
Referential Integrity Constraint

• When we split a relation, we must pay
  attention to the references across the newly
  formed relations
• E.g., a book must exist before it can be
  checked out:
   – CHECKOUT [BookID] Í BOOK [BookID]
• The DBMS or the applications will have to
  check/enforce constraints
    Boyce-Codd Normal Form
• Every determinant is a candidate key
  – ADVISER(SID,Major,Fname)




  – STU-ADV(SID,Fname)
    ADV-SUBJ(Fname,Subject)
        Multi-valued Dependency
• Two or more functionally independent multi-
  valued attributes are dependent on another
  attribute
  – EMPLOYEE(Name,Dependent,Project)

• Data redundancy and modification anomalies
• 4NF: BCNF & no multi-valued dependencies
   – EMPLOYEE(Name,Dependent)
   – EMPLOYEE(Name, Project)
       Database Normalization
• Boyce-Codd Normal Form (BCNF)
  – A relation is in Boyce-Codd normal form (BCNF) if
    every determinant in the table is a candidate key.
       (A determinant is any attribute whose value determines
       other values with a row.)

  – If a table contains only one candidate key, the 3NF
    and the BCNF are equivalent.
  – BCNF is a special case of 3NF.
A Table That Is In 3NF But Not In BCNF




 Figure 5.7
The Decomposition of a Table Structure to Meet
BCNF Requirements




  Figure 5.8
        Lossless-join Decomposition
●   For the case of R = (R1, R2), we require that
    for all possible relations r on schema R
       r = R1 (r ) |X| R2 (r )
●   A decomposition of R into R1 and R2 is
    lossless join if and only if at least one of the
    following dependencies is in F+:
    ●   R1  R2  R1
    ●   R1  R2  R2
●   R = (A, B, C)
    F = {A  B, B  C)
    ●   Can be decomposed in two different ways
●   R1 = (A, B), R2 = (B, C)
    ●   Lossless-join decomposition:
        R1  R2 = {B} and B  BC
    ●   Dependency preserving
●   R1 = (A, B), R2 = (A, C)
    ●   Lossless-join decomposition:
         R1  R2 = {A} and A  AB
    ●   Not dependency preserving
        (cannot check B  C without computing R1 |X| R2)
    Dependency Preservation
●   Let Fi be the set of dependencies F +
            that include only attributes in Ri.
    ●   A decomposition is dependency preserving, if
           (F1  F2  …  Fn )+ = F +
    ●   If it is not, then checking updates for
        violation of functional dependencies
        may require computing joins,
        which is expensive.
            Dependency Preservation
●   To check if a dependency    is preserved
    in a decomposition of R into R1, R2, …, Rn we
    apply the following test (with attribute closure
    done with respect to F)
    ●   result = 
        while (changes to result) do
          for each Ri in the decomposition
             t = (result  Ri)+  Ri
             result = result  t
    ●   If result contains all attributes in , then the
        functional dependency    is preserved.
        Dependency Preservation
●   We apply the test on all dependencies in F
    to check if a decomposition is dependency
    preserving
●   This procedure takes polynomial time,
    instead of the exponential time required to
    compute F+ and (F1  F2  …  Fn)+
            FD Example
●   R = (A, B, C )
    F = {A  B, B  C}
    Key = {A}
●   R is not in BCNF
●   Decomposition R1 = (A, B), R2 = (B,
    C)
    ●   R1 and R2 now in BCNF
    ●   Lossless-join decomposition
    ●   Dependency preserving
A Lossy Decomposition
         Aim of Normalization

• Goal for a relational database design is:
 – BCNF.
 – Lossless join.
 – Dependency preservation.
• If we cannot achieve this, we accept one of
 – Lack of dependency preservation
 – Redundancy due to use of 3NF
Sample Data for a BCNF Conversion




  Table 5.2
Decomposition into BCNF
Perform lossless-join decompositions of each of the following
scheme into BCNF schemes: R(A, B, C, D, E) with dependency set
{AB  CDE, C  D, D  E}


    A B     C D                         A B     C D



C D         A B C E               D E           A B C D



      D E            A B C                C D         A B C
Given the FDs {B  D, AB  C, D  B} and the relation {A,
B, C, D}, give a two distinct lossless join decomposition to
BNCF indicating the keys of each of the resulting relations.


   A B     C D                       A B     C D



B D        A   B    C            B D        A    C   D
          Definition of MVD
• A multivalued dependency (MVD)
  X ->->Y is an assertion that if two tuples of
  a relation agree on all the attributes of X,
  then their components in the set of attributes
  Y may be swapped, and the result will be
  two tuples that are also in the relation.
               Example
• The name-addr-phones-beersLiked example
  illustrated the MVD
      name->->phones
  and the MVD
      name ->-> beersLiked.
Picture of MVD X ->->Y

  X        Y        others

 equal

         exchange
                  MVD Rules
• Every FD is an MVD.
  – If X ->Y, then swapping Y ’s between two tuples that
    agree on X doesn’t change the tuples.
  – Therefore, the “new” tuples are surely in the
    relation, and we know X ->->Y.


• Complementation : If X ->->Y, and Z is all the
  other attributes, then X ->->Z.
        Fourth Normal Form
• The redundancy that comes from MVD’s is
  not removable by putting the database
  schema in BCNF.
• There is a stronger normal form, called
  4NF, that (intuitively) treats MVD’s as FD’s
  when it comes to decomposition, but not
  when determining keys of the relation.
                 4NF Definition
•   A relation R is in 4NF if whenever
    X ->->Y is a nontrivial MVD, then X is a
    superkey.
    –   “Nontrivial means that:
        1. Y is not a subset of X, and
        2. X and Y are not, together, all the attributes.
    –   Note that the definition of “superkey” still
        depends on FD’s only.
         BCNF Versus 4NF
• Remember that every FD X ->Y is also an
  MVD, X ->->Y.
• Thus, if R is in 4NF, it is certainly in
  BCNF.
  – Because any BCNF violation is a 4NF
    violation.
• But R could be in BCNF and not 4NF,
  because MVD’s are “invisible” to BCNF.
              Normalization
 Good Decomposition
  • dependency preserving decomposition
    - it is undesirable to lose functional dependencies
    during decomposition
  • lossless join decomposition
    - join of decomposed relations should be able to
    create the original relation (no spurious tuples)
      Decomposition and 4NF
•    If X ->->Y is a 4NF violation for relation
     R, we can decompose R using the same
     technique as for BCNF.
    1. XY is one of the decomposed relations.
    2. All but Y – X is the other.
                Example
Drinkers(name, addr, phones, beersLiked)

FD:        name -> addr
MVD’s:     name ->-> phones
           name ->-> beersLiked
• Key is {name, phones, beersLiked}.
• All dependencies violate 4NF.
         Example, Continued
• Decompose using name -> addr:
1. Drinkers1(name, addr)
   In 4NF, only dependency is name -> addr.
2. Drinkers2(name, phones, beersLiked)
   Not in 4NF. MVD’s name ->-> phones and
    name ->-> beersLiked apply. No FD’s, so all
    three attributes form the key.
Example: Decompose Drinkers2
• Either MVD name ->-> phones or
  name ->-> beersLiked tells us to
  decompose to:
  – Drinkers3(name, phones)
  – Drinkers4(name, beersLiked)
                  BCNF
• Given a relation schema R, and a set of
  functional dependencies F, if every FD, A
   B, is either:

• 1. Trivial
• 2. A is a superkey of R

• Then, R is in BCNF (Boyce-Codd Normal
  Form)
                  BCNF
• What if the schema is not in BCNF ?

• Decompose (split) the schema into two
  pieces.

• Careful: you want the decomposition to be
  lossless
               Achieving BCNF Schemas
•       For all dependencies A  B in F+, check if A is a superkey
    •      By using attribute closure


•       If not, then
    •      Choose a dependency in F+ that breaks the BCNF rules, say A  B
    •      Create R1 = A B
    •      Create R2 = A (R – B – A)
    •      Note that: R1 ∩ R2 = A and A  AB (= R1), so this is lossless
           decomposition


•       Repeat for R1, and R2
    •      By defining F1+ to be all dependencies in F that contain only attributes in
           R1
    •      Similarly F2+
                   •
                       Example 1
                       R = (A, B, C)
             •     F = {A  B, B  C}
             •     Candidate keys = {A}
         •       BCNF = No. B  C violates.

             BC




     •     R1 = (B, C)               •     R2 = (A, B)
    •     F1 = {B  C}              •     F2 = {A  B}
•      Candidate keys = {B}     •      Candidate keys = {A}
     •     BCNF = true               •     BCNF = true
                              •   Example 2-1
                                  R = (A, B, C, D, E)
                          •   F = {A  B, BC  D}
                          •  Candidate keys = {ACE}
                •       BCNF = Violated by {A  B, BC  D}
                                       etc…             •                 From A  B and BC 
                          AB                                             D by pseudo-transitivity



                    •  R1 = (A, B)             •       R2 = (A, C, D, E)
                •     F1 = {A  B}              •      F2 = {AC  D}
            •      Candidate keys = {A}    •        Candidate keys = {ACE}
                 •     BCNF = true         •        BCNF = false (AC  D)

•   Dependency preservation ???
                                            AC  D
•   We can check:                                                   •        R4 = (A, C, E)
•      A  B (R1), AC  D                                          •        F4 = {} [[ only
    (R3),                          •      R3 = (A, C, D)                        trivial ]]
•      but we lost BC  D         •      F3 = {AC  D}            •         Candidate keys =
•   So this is not a dependency •     Candidate keys = {AC}                      {ACE}
•   -preserving decomposition        •     BCNF = true                •      BCNF = true
                               •   Example 2-2
                                   R = (A, B, C, D, E)
                           • F = {A  B, BC  D}
                         •  Candidate keys = {ACE}
               •       BCNF = Violated by {A  B, BC  D}
                                      etc…
                         BC  D




               •        R1 = (B, C, D)                •       R2 = (B, C, A, E)
              •        F1 = {BC  D}                      •    F2 = {A  B}
          •         Candidate keys = {BC}     •            Candidate keys = {ACE}
                   •     BCNF = true              •         BCNF = false (A  B)
•   Dependency
    preservation ???                              AB
•   We can check:                       •     R3 = (A, B)                  •      R4 = (A, C, E)
•      BC  D (R1), A                 •     F3 = {A  B}                 •      F4 = {} [[ only
    B (R3),                        •      Candidate keys = {A}                       trivial ]]
•   Dependency-preserving               •     BCNF = true                •       Candidate keys =
                                                                                      {ACE}
•   decomposition
                                                                             •    BCNF = true
                                       Example 3
                 •   R = (A, B, C, D, E, H)
                •  F = {A  BC, E  HA}
                 •  Candidate keys = {DE}
       •       BCNF = Violated by {A  BC} etc…
                           A  BC




                 •        R1 = (A, B, C)            •      R2 = (A, D, E, H)
                •        F1 = {A  BC}               •      F2 = {E  HA}
           •           Candidate keys = {A}     •        Candidate keys = {DE}
                     •     BCNF = true         •         BCNF = false (E  HA)

•   Dependency preservation
    ???                                       E  HA
                                                                               •      R4 = (ED)
•   We can check:                                                      •           F4 = {} [[ only
                                       •     R3 = (E, H, A)
•      A  BC (R1), E                                                                 trivial ]]
                                      •     F3 = {E  HA}
    HA (R3),                                                          •            Candidate keys =
                                    •     Candidate keys = {E}
•   Dependency-preserving                                                                {DE}
                                        •    BCNF = true
•   decomposition                                                          •        BCNF = true

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:274
posted:2/16/2012
language:English
pages:67