Docstoc

Normalization

Document Sample
Normalization Powered By Docstoc
					Database
Design:Normalization



10/2/2013
Functional Dependence
lAn attribute (column) B, is
 functionally dependent on another
 attribute A if a value for A
 determines a single value for B at
 any one time.

lA -----> B


10/2/2013
Primary Key
lAttribute A (or a collection of
  attributes) is the primary key for a
  relation (table), R, if
1. All attributes in R are functionally
  dependent on A.
2. No subcollection of attributes in A
  also has a property 1.


10/2/2013
Primary Key
lCandidate key: Collection of
 attributes that has the same
 properties as in the definition of
 primary key.

lAlternate keys: Candidate keys that
 are not chosen to be the primary
 key.

10/2/2013
First Normal Form
lUnnormalized relation: A relation
 (table) that contains a repeating
 group

l1NF: Relations without repeating
 groups




10/2/2013
Relations
Unnormalized
lORDERS(ORDER_NUMBER,
 ORDER_DATE, (PART_NUMBER,
 NUMBER_ORDERED))
1NF
lORDERS(ORDER_NUMBER,
 ORDER_DATE, PART_NUMBER,
 NUMBER_ORDERED)

10/2/2013
Unnormalized Table
lCustomer (Order Number)
     Order       Order    Part     Number
     Number      Date     Number   Ordered

     12489    9/02/98    AX12      11
     12491    9/02/98    BT04      1
                         BZ66      1
     12494    9/04/98    CB03      4
     12495    9/04/98    CX11      2
     12498    9/05/98    AZ52      2
                         BA74      4
     12500    9/05/98    BT04      1
     12504    9/05/98    CZ81      2



10/2/2013
Result of Normalization (1NF)
lCustomer (Order Number, Part      Number)

     Order       Order    Part           Number
     Number      Date     Number         Ordered

     12489    9/02/98    AX12            11
     12491    9/02/98    BT04            1
     12491    9/02/98    BZ66            1
     12494    9/04/98    CB03            4
     12495    9/04/98    CX11            2
     12498    9/05/98    AZ52            2
     12498    9/05/98    BA74            4
     12500    9/05/98    BT04            1
     12504    9/05/98    CZ81            2



10/2/2013
Conversion to 1NF
lNo repeating group
lPrimary key will expand in
 converting a non-1NF table to 1NF




10/2/2013
Second Normal form
l ORDERS(ORDER_NUMBER, ORDER_DATE,
  PART_NUMBER, PART_DESRIPTION,
  NUMBER_ORDERED, Quoted_PRICE)
l Functional dependencies

     ORDER_NUMBER    ORDER_DATE

     PART_NUMBER    PART_DESRIPTION

     ORDER_NUMBER, PART_NUMBER
     NUMBER_ORDERED, Quoted_PRICE

10/2/2013
Sample Orders table
lOrders
   Order     Order     Part     Part          Number    Quoted
   Number    Date      Number   Description   Ordered   price

     12489   9/02/98    AX12    Iron          11        $21.95
     12491   9/02/98    BT04    Gas Grill     1         $149.99
     12491   9/02/98    BZ66    Washer        1         $399.99
     12494   9/04/98    CB03    Bike          4         $279.99
     12495   9/04/98    CX11    Blender       2         $22.95
     12498   9/05/98    AZ52    Dartboard     2         $12.95
     12498   9/05/98    BA74    Basketball    4         $24.95
     12500   9/05/98    BT04    Gas Grill     1         $149.99
     12504   9/05/98    CZ81    Treadmill     2         $325.99



10/2/2013
Update Anomalies
lUpdate: A single change may need
 to be updated in several rows
lInconsistent data: Values that are
 intended to be the same may appear
 differently in different rows




10/2/2013
Update Anomalies
lAdditions: Values may be missing,
 forcing users to add dummy data as
 a placeholder until real data is
 available
lDeletions: Deleting one row may
 delete information in the row
 unintentionally



10/2/2013
Dependencies in Order table

Order       Order   Part     Part          Number     Quoted
Number      Date    Number   Description   ordered    Price




                •Nonkey attributes depend on only a
                portion of the primary key



10/2/2013
2NF conversion
Convert into 3 Tables
(ORDER NUMBER, ORDER_DATE)
(PART_NUMBER, PART_DESCRIPTION)
(ORDER_NUMBER, PART_NUMBER,
  NUMBER_ORDERED, QUOTED_PRICE)




10/2/2013
Sample Orders table
lOrders         (Order Number, Part Number)


   Order     Order         Part          Part          Number    Quoted
   Number    Date          Number        Description   Ordered   price

     12489   9/02/98         AX12        Iron          11        $21.95
     12491   9/02/98         BT04        Gas Grill     1         $149.99
     12491   9/02/98         BZ66        Washer        1         $399.99
     12494   9/04/98         CB03        Bike          4         $279.99
     12495   9/04/98         CX11        Blender       2         $22.95
     12498   9/05/98         AZ52        Dartboard     2         $12.95
     12498   9/05/98         BA74        Basketball    4         $24.95
     12500   9/05/98         BT04        Gas Grill     1         $149.99
     12504   9/05/98         CZ81        Treadmill     2         $325.99



10/2/2013
   Conversion to 2NF
    Orders                          Part
    Order          Order            Part      Part
    Number         Date             Number    Description
    12489          9/02/98          AX12      Iron
    12491          9/02/98          BT04      Gas Grill
    12491          9/02/98          BZ66      Washer
    12494          9/04/98          CB03      Bike
    12495          9/04/98          CX11      Blender
    12498          9/05/98          AZ52      Dartboard
    12498          9/05/98          BA74      Basketball
    12500          9/05/98          CZ81      Treadmill
    12504          9/05/98
Order line
         Order             Part     Number      Quoted
         Number            Number   Ordered     Price
           12489           AX12        11       $21.95
           12491           BT04        1        $149.99
           12491           BZ66        1        $399.99
           12494           CB03        4        $279.99
           12495           CX11        2        $22.95
           12498           AZ52        2        $12.95
           12498           BA74        4        $24.95
           12500           BT04        1        $149.99
  10/2/2013
           12504           CZ81        2        $325.99
2NF
lis in 1NF
lNo nonkey attribute is dependent on
  only a portion of the primary key.
Elimination of Update anomalies
lEasy Update
lNo inconsistent data
lAdditions- No need for dummy data
lDeletions- No fact is lost
10/2/2013
Third Normal Form
lAny attribute or collection of
 attributes that determine another
 attribute is called a determinant.
lA relation (table) is in 3NF if it is
 second NF and if the only
 determinant it contains are
 candidate keys.



10/2/2013
Sample Customer table
  Customer (Customer Number)
   Customer   Customer   Customer              Credit   Slsrep   Slsrep   Slsrep
                                    Balance
   Number     Last       First                 Limit    Number   Last     First


    124       Adams      Sally      $818.75    $1000     03      Jones      Mary
    256       Samuels    Ann        $21.50     $1500     06      Smith      William
    311       Charles    Don        $825.75    $1000     12      Diaz       Miquel
    315       Daniels    Tom        $770.75    $750      06      Smith      William
    405       Williams   Al         $402.75    $1500     12      Diaz       Miguel
    412       Adams      Sally      $1817.50   $2000     03      Jones      Mary
    522       Nelson     Mary       $98.75     $1500     12      Diaz       Miguel
    567       Dinh       Tran       $402.40    $750      06      Smith      William
    587       Galvez     Mara       $114.60    $1000     06      Smith      William
    622       Martin     Dan        $1045.75   $1000     03      Jones      Mary




10/2/2013
Dependencies in Customer table

Customer CUST   Cust    Balance   Credit   SLSREP SLSREP   SLSREP
Number   Name   First             Limit    Number LAST     FIRST




10/2/2013
Update Anomalies
lUpdate
lInconsistent data
lAdditions
lDeletions




10/2/2013
Conversion to 3NF
Convet into 2 Tables
lCUSTOMER(CUSTOMER_NUMBER,
 CUST_LAST, CUST_FIRST,
 BALANCE, CREDIT_LIMIT,
 SLSREP_NUMBER)
lSALES_REP(SLSREP_NUMBER,
 SLSREP_LAST, SLSREP_FIRST)


10/2/2013
 Conversion to 3NF
  Customer
   Customer        Customer   Customer               Credit   Slsrep
                                          Balance
   Number          Last       First                  Limit    Number

     124       Adams          Sally       $818.75    $1000    03
     256       Samuels        Ann         $21.50     $1500    06
     311       Charles        Don         $825.75    $1000    12
     315       Daniels        Tom         $770.75    $750     06
     405       Williams       Al          $402.75    $1500    12
     412       Adams          Sally       $1817.50   $2000    03
     522       Nelson         Mary        $98.75     $1500    12
     567       Dinh           Tran        $402.40    $750     06
     587       Galvez         Mara        $114.60    $1000    06
     622       Martin         Dan         $1045.75   $1000    03
        Sales Rep
        Slsrep            Slsrep         Slsrep
        Number            Last           First

              03          Jones          Mary
              06          Smith          William
              12          Diaz           Miquel

10/2/2013
Normal Forms
l1 NF: No repeating groups
l2 NF: 1 NF and no nonkey attribute
 depedent on only a portion of the
 primary key.
 Automatically 2NF if the primary key
 contains only a single attribute
l3NF: 2NF and the only determinants
 are candidate keys.
 Boyce-Codd normal form (BCNF)
10/2/2013
 Incorrect decomposition
  Customer
   Customer      Customer     Customer              Credit   Slsrep
                                         Balance
   Number        Last         First                 Limit    Number

     124        Adams        Sally       $818.75    $1000    03
     256        Samuels      Ann         $21.50     $1500    06
     311        Charles      Don         $825.75    $1000    12
     315        Daniels      Tom         $770.75    $750     06
     405        Williams     Al          $402.75    $1500    12
     412        Adams        Sally       $1817.50   $2000    03
     522        Nelson       Mary        $98.75     $1500    12
     567        Dinh         Tran        $402.40    $750     06
     587        Galvez       Mara        $114.60    $1000    06
     622        Martin       Dan         $1045.75   $1000    03
       Sales Rep
            Customer    Slsrep           Slsrep
            Number      Last             First
              124           Jones        Mary
              256           Smith        William
              311           Diaz         Miquel
              315           Smith        William
              405           Diaz         Miguel
              412           Jones        Mary
              522           Diaz         Miguel
              567           Smith        William
              587           Smith        William
10/2/2013
              622           Jones        Mary
Another Decomposition
  Customer
  Customer        Customer   Customer               Credit   Slsrep     Slsrep
                                         Balance
  Number          Last       First                  Limit    Last       First

     124      Adams          Sally       $818.75    $1000    Jones    Mary
     256      Samuels        Ann         $21.50     $1500    Smith    William
     311      Charles        Don         $825.75    $1000    Diaz     Miquel
     315      Daniels        Tom         $770.75    $750     Smith    William
     405      Williams       Al          $402.75    $1500    Diaz     Miguel
     412      Adams          Sally       $1817.50   $2000    Jones    Mary
     522      Nelson         Mary        $98.75     $1500    Diaz     Miguel
     567      Dinh           Tran        $402.40    $750     Smith    William
     587      Galvez         Mara        $114.60    $1000    Smith    William
     622      Martin         Dan         $1045.75   $1000    Jones    Mary


        Sales Rep
        Slsrep           Slsrep         Slsrep
        Number           Last           First

             03          Jones          Mary
             06          Smith          William
             12          Diaz           Miquel

10/2/2013
            Questions and Comments




10/2/2013

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:0
posted:9/30/2013
language:English
pages:28
xiaocuisanmin xiaocuisanmin
About