Functional Dependencies

Document Sample
Functional Dependencies Powered By Docstoc
					Functional Dependencies and
Normalization
        Normalization
   Normalization is a formalized procedure to
    eliminating redundancy from data by the
    progressive use of ‘non-lose decomposition’,
    which involves splitting records without losing
    information.

   In reducing the data model to the state where each
    bit of information is only held in one place, the
    update process is much simpler, more efficient and
    inconsistencies in the database are impossible.
         Normalization (cont.)
                                   5NF

                             4NF


                       3NF

                 2NF

          1NF

Redundancy
  Redundancy
    Redundancy
        Normalization (cont.)
   Normalization is based on the idea that an
    attribute may depend on another attribute
    in some way.
   There are 2 different kinds of dependencies
    involved up to 5 NF
       Functional dependency
       Multivalued dependence
  Functional Dependence
        S#       CITY          P#            QTY

        S1   Khon Kaen         P1            100
        S1   Khon Kaen         P2            100
        S2   Saraburii         P1            200
        S2   Saraburii         P2            200
        S3   Saraburii         P2            300
        S4   Bangkok           P2            400
        S4   Bangkok           P4            400
        S4   Bangkok           P5            400



S#,P#             QTY    QTY is functionally dependent on S#andP#

                         S# and P# are the determinant of QTY
        Functional Dependence
   In a relation including attribute A and B, B is
    functional dependent on A if, for every valid
    occurrence, the value A determines the value B
   An occurrence can not be used to show that a
    dependency is true, only that it is false
       A and B can be composite
       If B is ‘Functional Dependent on’ A, then A ‘is the
        determinant of B’
       All fields are functionally dependent on the primary key
        – or indeed any candidate key – be definition.
    First Normal Form

   A relation is in First Normal form if,
    and only if, it contains no multi-
    value or no repeating groups.
       First NF (cont.)
NO     Name      Province    PayDate1     Amount1 PayDate2     Amount2

E001   Somchai   Khon Kaen   15/04/2004    5,000.00 30/04/2004 5,000.00
E002   Sompong   Sarakham    15/04/2004    4,500.00 30/04/2004 4,500.00
E003   Somchay   Ubon        15/04/2004    5,200.00 30/04/2004 5,200.00




                                                 Repeat
          Problem                                    Multi-value
       Staff
ENO       Name       Dno          DeptName   ProjNo      ProjName

E001      Somchai    D01          Physic     P01, P02 NMR, Laser

E002      Sompong    D01          Physic       P03       Medical Image
                                                         processing
E003      Somchay    D02          Computer   P04, P05    Voice ordering, Speech
                                  Science                Coding
E004      SomSiri    D02          Computer   P04, P06    Voice ordering, Speech
                                  Science                Synthesis

Problem
1. Difficult to manipulate data
2. Redundancy
    • UPDATE ANOMALIES
       Insert                                   P06       Speech Corpus

   Staff

ENO     Name         Dno       DeptName         ProjNo      ProjName

E001    Somchai      D01       Physic           P01, P02 NMR, Laser

E002    Sompong      D01       Physic             P03       Medical Image
                                                            processing
E003    Somchay      D02       Computer        P04, P05     Voice ordering, Speech
                               Science                      Coding
E004    SomSiri      D02       Computer        P04, P06     Voice ordering, Speech
                               Science                      Synthesis


   We can not insert new project if the project has not assigned to
   any employee yet.
       UPDATE ANOMALIES
       Staff

ENO       Name       Dno      DeptName      ProjNo     ProjName

E001      Somchai    D01      Physic         P01, P02 NMR, Laser

E002      Sompong    D01      Physic          P03      Medical Image
                                                       processing
E003      Somchay    D02      Computer      P04, P05   Voice ordering, Speech
                              Science                  Coding
E004      SomSiri    D02      Computer      P04, P06   Voice ordering, Speech
                              Science                  Synthesis


       Change ProjName from Voice Ordering to Speech Ordering
       need to change all in Database
       DELETE Problem
   Staff

ENO       Name         Dno       DeptName        ProjNo        ProjName

E001      Somchai      D01       Physic          P01, P02 NMR, Laser

E002      Sompong      D01       Physic            P03         Medical Image
                                                               processing
E003      Somchay      D02       Computer        P04, P05      Voice ordering, Speech
                                 Science                       Coding
E004      SomSiri      D02       Computer        P04, P06      Voice ordering, Speech
                                 Science                       Synthesis


       Delete Employee E003 Somchay
       Project P03 Medical Image Processing was deleted also
Solution
   Remove the repeating group
   In case of multi-valued
       Create new relation
       Columns = Key + multi-valued
   Take its determinant with it
       Repeating group
Employee
ENO    Name       Province    PayDate1     Amount1 PayDate2     Amount2

E001   Somchai    Khon Kaen   15/04/2004    5,000.00 30/04/2004 5,000.00
E002   Sompong    Sarakham    15/04/2004    4,500.00 30/04/2004 4,500.00
E003   Somchay    Ubon        15/04/2004    5,200.00 30/04/2004 5,200.00


                                     PayCheck
Employee
                                    ENO       PayDate       Amount
 ENO       Name    Province
                                   E001    15/04/2004           5,000.00
E001    Somchai   Khon Kaen        E001    30/04/2004           5,000.00

E002    Sompong   Sarakham         E002    15/04/2004           4,500.00

E003    Somchay   Ubon             E002    30/04/2004           4,500.00

                                   E003    15/04/2004           5,200.00

                                   E003    30/04/2004           5,200.00
         Multi-Valued

 Staff
ENO      Name      Dno    DeptName           ProjNo     ProjName

E001     Somchai    D01   Physic             P01, P02   NMR, Laser
E002     Sompong    D01   Physic               P03      Medical Image processing
E003     Somchay    D02   Computer Science   P04, P05   Voice ordering, Speech Coding

E004     SomSiri    D02   Computer Science   P04, P06   Voice ordering, Speech Synthesis
       Multi-Valued

ENO    Name      Dno    DeptName           ProjNo   ProjName
E001   Somchai    D01   Physic              P01     NMR
E001   Somchai    D01   Physic              P02     Laser
E002   Sompong    D01   Physic              P03     Medical Image processing
E003   Somchay    D02   Computer Science    P05     Voice ordering

E003   Somchay    D02   Computer Science    P04     Speech Coding

E004   SomSiri    D02   Computer Science    P04     Voice ordering

E004   SomSiri    D02   Computer Science    P06     Speech Synthesis




                                             Insert Project still has problem
Second Normal Form
(2NF)
   A relation is in first normal form if and
    only if
       It is in 1NF
       Every non-key attribute is dependent on
        all parts of the primary key.
         2NF ?
Staff
ENO       Name      Dno     DeptName           ProjNo   ProjName
E001      Somchai     D01   Physic              P01     NMR
E001      Somchai     D01   Physic              P02     Laser
E002      Sompong     D01   Physic              P03     Medical Image processing
E003      Somchay     D02   Computer Science    P05     Voice ordering

E003      Somchay     D02   Computer Science    P04     Speech Coding

E004      SomSiri     D02   Computer Science    P04     Voice ordering

E004      SomSiri     D02   Computer Science    P06     Speech Synthesis



                                               Answer is No. Because ProjNo is
       KEY = ENO + ProjNo
                                                dependent on ProjNo. (not all
                                                         part of Key)
       Problem

ENO    Name      Dno    DeptName           ProjNo     ProjName
E001   Somchai    D01   Physic               P01      NMR
E001   Somchai    D01   Physic               P02      Laser
E002   Sompong    D01   Physic               P03      Medical Image processing
E003   Somchay    D02   Computer Science     P05      Voice ordering

E003   Somchay    D02   Computer Science     P04      Speech Coding

E004   SomSiri    D02   Computer Science     P04      Voice ordering

E004   SomSiri    D02   Computer Science     P06      Speech Synthesis




                                    We can not insert Project if have not yet
                                    assigned project to any employee
Solution
   Remove the attribute involved
   Take its determinant with it
       Normalize

ENO    Name      Dno    DeptName           ProjNo   ProjName
E001   Somchai    D01   Physic              P01     NMR
E001   Somchai    D01   Physic              P02     Laser
E002   Sompong    D01   Physic              P03     Medical Image processing
E003   Somchay    D02   Computer Science    P05     Voice ordering

E003   Somchay    D02   Computer Science    P04     Speech Coding

E004   SomSiri    D02   Computer Science    P04     Voice ordering

E004   SomSiri    D02   Computer Science    P06     Speech Synthesis
       Result
                                   Project                  PERSON_Proj
PERSON
                                   Proj ProjName            ENO    Proj
ENO    Name      Dno    DeptNa      No                              No
                        me         P01   NMR                E001   P01
E001   Somchai    D01   Physic     P02   Laser              E001   P02
E003   Somchay    D02   Computer
                        Science
                                   P03   Medical Image      E002   P03
                                         processing
E004   SomSiri    D02   Computer                            E003   P04
                        Science    P04   Speech Coding
                                                            E004   P05
                                   P05   Voice ordering
                                                            E004   P06
                                   P06   Speech Synthesis


PERSON(ENO,NAME,Dno,DeptName)
PROJECT(ProjNo,ProjName)
PERSON_PROJ(ENO,ProjNo)
Third Normal Form
   A relation is in 3NF if, and only if:
       It is in 2NF
       Every non-key attribute is functionally
        dependent upon the key. (No non-key
        attribute is functional dependent on
        another non-key attribute)
       Or non-key attribute no transitive
        dependent on key
     Transitive dependent
   R(A,B,C,D) ; A is Key, others are non- key

   If A → B and B → C
     can say
    A → B → C (C transitive dependent on A)
       3NF?
                                          Project                  PERSON_Proj
PERSON
                                          Proj ProjName            ENO    Proj
ENO    Name          Dno     DeptNa        No                              No
                             me           P01   NMR                E001   P01
E001   Somchai        D01    Physic       P02   Laser              E001   P02
E003   Somchay        D02    Computer
                             Science
                                          P03   Medical Image      E002   P03
                                                processing
E004   SomSiri        D02    Computer                              E003   P04
                             Science      P04   Speech Coding
                                                                   E004   P05
                                          P05   Voice ordering
                                                                   E004   P06
                                          P06   Speech Synthesis


                Answer is No
       Because DeptName is dependent
       on Dno (has transitive dependent
                   on key)
Solution
   Remove the offending attributes
   Take the determinant along
       Result
PERSON
ENO      Name            Dno    Project                  PERSON_Proj
E001     Somchai          D01   Proj ProjName            ENO    Proj
E003     Somchay          D02    No                              No
E004     SomSiri          D02   P01   NMR                E001   P01
                                P02   Laser              E001   P02
       Department               P03   Medical Image      E002   P03
       Dno    DeptNa                  processing
                                                         E003   P04
              me                P04   Speech Coding
                                                         E004   P05
        D01   Physic            P05   Voice ordering
                                                         E004   P06
        D02   Computer          P06   Speech Synthesis
              Science
        D02   Computer
              Science
       Note
   The third normal form is often reached in practice by
    inspection, in a single step. Its meaning seems intuitively
    clear; it represents a formalization of designer’s
    common sense.

   This level of normalization is widely accepted as the
    initial target for a design which eliminates redundancy.

   However, there are higher normal forms which, although
    less frequently invoked, highlight further redundancy
    problems which may affect the designer
Boyce-Codd Normal Form
(BCNF)
   A relation is in BCNF if, and only if,
    every determinant is a candidate key.
   BCNF is a refinement to third normal
    form, and tightens its duration.
     Multivalued Dependence
   In a relation including attribute A, B and C,
    B is multivalued dependent on A if the
    set of B values matching a given A+C value
    pair, depends only on the A value.

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:2
posted:2/5/2012
language:English
pages:30