4 Normal Form

Document Sample
4 Normal Form Powered By Docstoc
					4 Normal Form


   Nathanael Chow
      CS 157A
      Fall 2006
       Dr. Lee
Overview

●   1 NF
●   2 NF
●   3 NF
●   BCNF
●   4 NF
●   Conclusion
Database Normalization

●   Each data in the database should
    represent once
●   Purpose of normalization: to
    eliminate insert, update, and delete
    anomalies
First Normal Form (1 NF)

●   All values in the columns are atomic (simple,
    indivisible). This is, they contain no repeating
    values.
●   There are no repeating groups: two columns do not
    store similar information in the same table.
●   Basically: 1 NF is to eliminate duplicate columns
  1st Normal Form Example
Un-normalized Students table:
Student# AdvID AdvName AdvRoom Class1 Class2
123        123A James         555 102-8 104-9
124        123B Smith         467 209-0 102-8
Normalized Students table:

Student# AdvID AdvName AdvRoom Class#
123      123A   James   555      102-8
123      123A   James   555      104-9
124      123B   Smith   467      209-0
124      123B   Smith   467      102-8
Second Normal Form (2 NF)

 ●   A relation is in 2 NF if it is in 1 NF and every
     non-key attribute is fully functionally dependant
     on the primary key
  2nd Normal Form Example
Students table
Student#       AdvID   AdvName   AdvRoom
123            123A    James     555
124            123B    Smith     467
Registration table

Student#   Class#
123        102-8
123        104-9
124        209-0
124        102-8
Third Normal Form (3 NF)

●   A relation is in 3 NF if it is 2 NF and no transitive
    dependencies exist.
●   Transitive dependency is a functional dependency
    between non-key attribute
●   Basically: 3 NF is to eliminate column not depend
    upon the primary key.
  transitive dependency
           transitive
          dependency



Cust_ID       Name      Salesperson   Region
Cust_ID        Name        Salesperson       Region




Salesperson       Region




              Cust_ID      Name          Salesperson
Boyce-Codd Normal Form (BCNF)

 ●   A relation is in BCNF if it is in 3 NF and every
     determinant is a candidate key; in other words,
     each determinant can be used as a primary key.
 ●   Determinant: an attribute on which some other
     attribute is fully functionally dependent
        Ex: A --> B (A is called the determinant)
BCNF Example

●   Given: R (A, B, C , D)
●   A --> B, C, D
●   B --> A, C, D
●   C --> A , B, D
●   D --> A, B, C
BCNF Example (Cont...)

 ●   Determinants:
        A, B, C, and D
 ●   Candidate keys:
        A, B, C, and D
 ●   Since all the determinants are candidate keys,
     this is BCNF.
4th Normal Form (4 NF)

 ●   A relation is a 4 NF if it is BCNF and
        There is no multivalued dependency in the relation
         or
        There are multivalued dependency but the attributes,
         which are multivalued dependent on a specific
         attribute, are dependent between themselves
 ●   What is a multivalued dependency (MVD)?
Definition of MVD

 ●   A multivalued dependency 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
MVD Example

R(x, y, z)               X Y Z
                         A B1 C1
                         A B2 C2
X Y Z
A B1 C1       X ->-> Y
A B2 C2
MVD Example

R(x, y, z)               X Y Z
                         A B1 C1
                         A B2 C2
X Y Z                    A   B2 C1
A B1 C1       X ->-> Y   A   B1 C2

A B2 C2
 4 NF Example
Assume the following relation:
Employee (Eid:pk1, Language:pk2, Skill:pk3)



     Eid              Language        Skill
     100              English         Teaching
     100              Kurdish         Politic
     100              French          Cooking
     200              English         Cooking
     200              Arabic          Singing
    4 NF Example (conti...)
    Eid                   Language              Skill
    100                   English               Teaching
    100                   Kurdish               Politic
    100                   French                Cooking
    200                   English               Cooking
    200                   Arabic                Singing

Recall that a relation is in BCNF if all its determinant are candidate
  keys.
Because relation Employee has only one determinant (Eid, Language,
  Skill), which is the composite primary key.
Since the primary is a candidate key, R is in BCNF.

Assume that there is no MVD, then this relation is 4 NF.
   4 NF Example (conti...)

Assume the following relation with multi-value dependency:

Employee (Eid:pk1, Languages:pk2, Skills:pk3)
Eid --->> Languages        Eid --->> Skills

Languages and Skills are dependent.
This says an employee speaks several languages and has several
skills. However for each skill, a specific language is used when that
skill is practiced.
4 NF Example (conti...)
 Thus employee 100 when she teaches, she uses English;
   but when she cooks, she uses French. This relation is
   in fourth normal form.




   Eid                Language           Skill
   100                English            Teaching
   100                Kurdish            Politic
   100                French             Cooking
   200                English            Cooking
   200                Arabic             Singing
     Not 4 NF Example


Assume the following relation with multivalued dependency:

Employee (Eid:pk1, Languages:pk2, Skills:pk3)

Eid --->> Languages             Eid --->> Skills

Languages and Skills are independent.
 Not 4 NF Example (conti...)
Eid              Language          Skill
100              English           Teaching
100              Kurdish           Politic
100              French            Cooking
200              Arabic            Singing


Insertion anomaly: To insert row (200 English
  Cooking) we have to insert two extra rows (200
  Arabic cooking), and (200 English Singing)
  otherwise the database will be inconsistent.
Not 4 NF Example (conti...)
  Here is the table after the insertion:

           Eid         Language    Skill
           100         English     Teaching
           100         Kurdish     Politics
           100         English     Politics
           100         Kurdish     Teaching
           200         Arabic      Singing
           200         English     Cooking
           200         Arabic      Cooking
           200         English     Singing
Conclusion of Steps in Normalization
References

 ●   http://www.cs.sjsu.edu/faculty/lee/cs157/cs157a.
     html
 ●   http://www.cs.sjsu.edu/faculty/lee/cs157/cs157al
     ecturenotes.htm