Docstoc

Functional dependencies and normalization _1-3NF + BCNF_

Document Sample
Functional dependencies and normalization _1-3NF + BCNF_ Powered By Docstoc
					  Normalization
Functional dependencies
  1.-3. NF + BCNF




     Functional dependencies and   1
            normalization
               Terminology
• Functional dependency
  – Dependency between attributes in a single
    relation
• Normal form
  – Rule, which a relation must obey. Higher
    normal form means more “quality” in the
    relation.
• Normalization
  – Process that leads the relation to a certain
    normal form.
                   Functional dependencies and     2
                          normalization
     2 types of database-design
Bottom-up                          Top-down
• Starting point:                  • Starting point:
   – A set of attributes                 – Attributes “grouped”
   – Dependencies among                    into relations from ER-
     the attributes.                       diagram (analysis).
• Hard to survey all the
  attributes.


                    Functional dependencies and                  3
                           normalization
               Normalization
• Can be used in 2 ways:
  – Quality check of relations (from ER)
     • Top down
     • Normal procedure
  – Process, starting with unordered data
     • Bottom up
     • Not recommended
     • Used in many book (in the normalization chapters)

                   Functional dependencies and             4
                          normalization
        Goal of normalization
• Reduce / abolish redundancy
  – Redundant data: Data that occurs more than
    once (or can be inferred from other data)
     • Price_incl_postage = price + postage
• Reduce the number of NULLs
  – NULLs fill the database without giving any
    information.


                   Functional dependencies and   5
                          normalization
                                     Keys
• Key
   – Minimal set of attributes that uniquely defines any row.
   – Primary key
        • If the set of attributes consists of ONE attribute
   – Composite primary key
        • If the set of attributes consists of more that one attribute.
   – Prime attribute
        • Attribute in some candidate key
• Secondary key
   – If we have more than one key. One of the candidates is chosen as the
     primary key.
• Surrogate key
   – Invented (not in the real world) attribute.
   – Advice: Always use surrogate keys.Never use real world data in a key
        • Real world data might change in the future.
                                Functional dependencies and               6
                                       normalization
          Functional dependency
• CPR → first name
   – 1 CPR leads to exactly 1 first name
• CPR → phone number
   – Probably not true: Many people have more than 1 phone number
• Must be true in every relational state
   – in the past and in the future
• A relational state may
   – Exclude certain functional dependencies
   – Never show any functional dependencies




                          Functional dependencies and               7
                                 normalization
         Dependency diagram
• A dependency of a relation
  – All attribute of the relation
  – The key (bold)
  – The dependencies (arrows)




                   Functional dependencies and   8
                          normalization
                   1. normal form
• A relation is on 1NF, if
  – Primary key defined
  – All attributes are atomic
• Forbids
  – Multi valued attributes
     • Create more tuples in the relation.
     • Create a new relation with foreign keys to this relation
  – Composite attributes
     • Create an attribute for each sub-attribute.

                          Functional dependencies and             9
                                 normalization
             2. normal form
• 1NF + no partial dependency
• Partial dependency
  – An attribute is dependent of a PART of the key.
  – Only interesting if the key is composite.
• What to do? Divide the relation
  – Partial dependent attributes must have their
    own relation.


                  Functional dependencies and      10
                         normalization
               3. normal form
• 2NF + no transitive dependency
• Transitive dependency
  – Person: CPR  post and code  city
• What to do? Divide the relation
  – Transitive dependencies must go into another relation




                    Functional dependencies and             11
                           normalization
       The Relational oath
"I promise to use the key, the whole key,
and nothing but the key, so help me Codd."

Codd defined the relational model - and
invented the normal forms [1970].



              Functional dependencies and    12
                     normalization
                    BCNF
• Boyce-Codd Normal form
  – Named after its inventors: Boyce and Codd
  – Stronger than 3NF, but less strong than 4NF
• A relation is in BCNF, if
  – For every functional dependency A → B
  – A must be a super key in the relation


                 Functional dependencies and      13
                        normalization
         BCNF “cut-through”
• Find all the functional dependencies
• Check that the left hand sides of the
  functional dependencies are (super)keys
  – If they are not, then split the relation into 2 sub-
    relations
• No need to go through 1NF, 2NF, and 3NF


                   Functional dependencies and        14
                          normalization
Strengths of the normal forms



                          BCNF




          Functional dependencies and   15
                 normalization
            Denormalization
• Normalization produces many (good)
  tables.
  – Many joins: Takes extra time at “select”
  – Sometimes we prefer fewer tables with
    controlled redundancy.




                  Functional dependencies and   16
                         normalization

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:17
posted:3/14/2012
language:English
pages:16