Docstoc

Normalization

Document Sample
Normalization Powered By Docstoc
					Rules of Database
 Normalization




     1              1/24/2013
                     Overview
   What is Normalization?
   What is the objective of Normalization?
   Why is Normalization in a database important?
   What are NORMAL FORMS?
   How do we accomplish Normalization in our database?
   Are there times when Normalization is unnecessary?




                             2                            1/24/2013
       What is Normalization?


 Normalization refers to the process of creating an
  efficient, reliable, flexible, and appropriate
  “relational” structure for storing information in a
  “relational” data structure.
 Normalization usually involves dividing a database
  into two or more tables and defining the relationship
  between the tables.




                           3                              1/24/2013
What is the objective of Normalization?



 The objective is to isolate data so that additions,
  deletions, and modifications of a field can be made in
  just one table and then propagated through the rest
  of the database via the defined relationships.




                            4                              1/24/2013
  Why is Normalization in a database
             important?
 Normalization removes redundant data from
  your tables in order to improve storage
  efficiency.
 Normalization removes redundant data and
  improves data integrity.
 Normalization removes redundant data and
  helps in maintenance problems. Example: If data
  that exists in more than one place must be
  changed, the data must be changed in exactly
  the same way in all locations.
 Finally, Normalization removes redundant data
  and improves the scalability of your database.

                       5                        1/24/2013
   What are NORMAL FORMS?


 A series of logical steps to normalize data tables.
   First Normal Form-1NF
   Second Normal Form-2NF
   Third Normal Form-3NF
   Fourth Normal Form-4NF
   Fifth Normal Form-5NF
  • We will learn the First, Second and Third Normal Forms



                             6                               1/24/2013
What is the First Normal Form?


The definition of the first Normal Form is as follows
 There are no repeating groups.
 All of the key attributes are defined.
 All attributes are dependent on the primary key.
Example on the next slide.




                            7                           1/24/2013
How do we accomplish First Normal Form?
        Look at this example.

Project   Project           Employee   Employee        Rate         Hourly   Employee    Rate       Hourly
Number    Name              Number     Name-1          Category-1   Rate-1   Name 2      Category   Rate



1023,     Madagascar        11         Julius Wilson   A            $60      Monica      B          $50
1056      Travel site and                                                    Jefferson
          Online
          Estate Agency
1023      Madagascar        16         Daniel Moore                                      C          $40
          Travel site

1056      Online Estate     17         Charles Saul                                      D          $30
          Agency




                                                                                              8
                                                                                                          1/24/2013
          Notice the repeating Groups.
Project    Project           Employee   Employee        Rate         Hourly   Employee    Rate           Hourly
Number     Name              Number     Name-1          Category-1   Rate-1   Name 2      Category       Rate




1023,      Madagascar        11         Julius Wilson   A            $60      Monica      B              $50
1056       Travel site and                                                    Jefferson
           Online
           Estate Agency



1023       Madagascar        16         Daniel Moore                                      C              $40
           Travel site



1056                         17         Charles Saul                                      D              $30
           Online Estate
           Agency


                                                                                                     9
                                                                                                                  1/24/2013
   This is our solution to eliminating the
              repeating groups
Project   Project         Employee Employee        Rate       Hourly
Number    Name            Number Name              Category   Rate

1023      Madagascar      11       Julius Wilson   A          $60
          Travel site
1023      Madagascar      12       Monica          B          $50
          Travel site              Jefferson
1023      Madagascar      16       Daniel Moore    C          $40
          Travel site
1056      Online Estate   11       Julius Wilson   A          $60
          Agency
1056      Online Estate   17       Charles Saul    D          $30
          Agency

                                                                    10
                                                                         1/24/2013
   How can we set our Primary Key?
Project   Project         Employee Employee        Rate       Hourly
Number    Name            Number Name              Category   Rate
1023      Madagascar      11       Julius Wilson   A          $60
          Travel site
1023      Madagascar      12       Monica          B          $50
          Travel site              Jefferson
1023      Madagascar      16       Daniel Moore    C          $40
          Travel site
1056      Online Estate   11       Julius Wilson   A          $60
          Agency
1056      Online Estate   17       Charles Saul    D          $30
          Agency


                                                                    11
                                                                         1/24/2013
       Can this be our Primary Key?
Project   Project         Employee Employee        Rate       Hourly
Number    Name            Number Name              Category   Rate
1023      Madagascar      11       Julius Wilson   A          $60
          Travel site
1023      Madagascar      12       Monica          B          $50
          Travel site              Jefferson
1023      Madagascar      16       Daniel Moore    C          $40
          Travel site
1056      Online Estate   11       Julius Wilson   A          $60
          Agency
1056      Online Estate   17       Charles Saul    D          $30
          Agency


                                                                    12
                                                                         1/24/2013
       Or, can this be our Primary Key?
Project   Project         Employee Employee        Rate       Hourly
Number    Name            Number Name              Category   Rate
1023      Madagascar      11       Julius Wilson   A          $60
          Travel site
1023      Madagascar      12       Monica          B          $50
          Travel site              Jefferson
1023      Madagascar      16       Daniel Moore    C          $40
          Travel site
1056      Online Estate   11       Julius Wilson   A          $60
          Agency
1056      Online Estate   17       Charles Saul    D          $30
          Agency


                                                                    13
                                                                         1/24/2013
    Solution-We need both as our Primary
                   Keys.
Project       Project         Employee Employee             Rate       Hourly
Number        Name            Number Name                   Category   Rate
Primary Key                   Primary Key
1023          Madagascar      11            Julius Wilson   A          $60
              Travel site
1023          Madagascar      12            Monica          B          $50
              Travel site                   Jefferson
1023          Madagascar      16            Daniel Moore    C          $40
              Travel site
1056          Online Estate   11            Julius Wilson   A          $60
              Agency
1056          Online Estate   17            Charles Saul    D          $30
              Agency

                                                                             14
                                                                                  1/24/2013
       Is our table in First Normal Form?
Project       Project         Employee Employee             Rate       Hourly
Number        Name            Number Name                   Category   Rate
Primary Key                   Primary Key
1023          Madagascar      11            Julius Wilson   A          $60
              Travel site
1023          Madagascar      12            Monica          B          $50
              Travel site                   Jefferson
1023          Madagascar      16            Daniel Moore    C          $40
              Travel site
1056          Online Estate   11            Julius Wilson   A          $60
              Agency
1056          Online Estate   17            Charles Saul    D          $30
              Agency

                                                                             15
                                                                                  1/24/2013
    What is Second Normal Form


     A table is in 2nd Normal Form if:
     1)   It is in 1st normal form.
     2)   It includes no partial dependencies (where an
          attribute is dependent on only a part of a primary
          key).




                                16                             1/24/2013
 Does it include partial dependencies? Do you
             notice anything else?
Project       Project         Employee Employee             Rate       Hourly
Number        Name            Number Name                   Category   Rate
Primary Key                   Primary Key
1023          Madagascar      11            Julius Wilson   A          $60
              Travel site
1023          Madagascar      12            Monica          B          $50
              Travel site                   Jefferson
1023          Madagascat      16            Daniel Moore    C          $40
              Travel site
1056          Online Estate   11            Julius Wilson   A          $50
              Agency
1056          Online Estate   17            Charles Saul    D          $30
              Agency

                                                                             17
                                                                                  1/24/2013
  Look at “Project Name” Row 3. Also look at
  Hourly Rate? What about data integrity?
Project       Project         Employee Employee             Rate       Hourly
Number        Name            Number Name                   Category   Rate
Primary Key                   Primary Key
1023          Madagascar      11            Julius Wilson   A          $60
              Travel site
1023          Madagascar      12            Monica          B          $50
              Travel site                   Jefferson
1023          Madagascat      16            Daniel Moore    C          $40
              Travel site
1056          Online Estate   11            Julius Wilson   A          $50
              Agency
1056          Online Estate   17            Charles Saul    D          $30
              Agency

                                                                             18
                                                                                  1/24/2013
                         Step One:
Employee-Project Table

Project   Employee
Name
1023
          Number
          11
                     Project Name is only
                     Dependent on Project
1023      12
                     Number; so, we
1023      16         create a
1056      11
                     “Employee Project
                     Table.”
1056      17


                                            19
                                                 1/24/2013
                                Step Two:
   Project Table

Project Project
Number Name                   Notice that “Project Name”
Primary Key

1023          Madagascar
                              is dependent on only
              Travel site
                              “Project Number.”
1056
                              We create our “Project Table”
              Online Estate
              Agency

                              With Project Number as our
                              Primary Key. Look at the space
                              we have saved. What else have
                              we done?
                                                      20
                                                           1/24/2013
                                  Step Three:
              Employee Table

Employee Employee             Rate     Hourly   Notice that Employee
Number Name                   Category Rate     Name, Rate Category,
Primary Key

11            Julius Wilson   A        $60
                                                and Hourly Rate are
                                                dependent on
12            Monica          B        $50      Employee Number.
              Jefferson
                                                We create our
16            Daniel Moore C           $40
                                                “Employee Table”
17            Charles Saul    D        $30      with
                                                Employee Number
                                                as our Primary Key.

                                                             21
                                                                  1/24/2013
   Are we now in Second Normal Form?
Employee Project           Project Table                              Employee Table
    Table
  Project   Employee      Project       Project         Employee        Employee           Rate       Hourly
                          Number        Name            Number          Name               Category   Rate
  Name      Number        Primary Key                   Primary Key
  Primary   Primary Key
  Key
                          1023          Madagascar
  1023      11                                          11              Julius Wilson      A          $60
                                        Travel site
  1023      12            1056          Online Estate   12              Monica Jefferson   B          $50
                                        Agency

  1023      16                                          16              Daniel Moore       C          $40


                                                        17              Charles Saul       D          $30
  1056      11

  1056      17


 Is it in First Normal Form and does it includes no partial
 Dependencies?
                                                                                                      22
                                                                                                               1/24/2013
Now, What is Third Normal Form?


 It is in SECOND Normal Form.
 It contains no transitive dependencies (where a non-
  key attribute is dependent on another non-key
  attribute).
 Let’s see where we begin.




                           23                            1/24/2013
   Let’s take another look at what we have.?
Employee Project           Project Table                              Employee Table
    Table
  Project   Employee      Project       Project         Employee        Employee           Rate       Hourly
                          Number        Name            Number          Name               Category   Rate
  Name      Number        Primary Key                   Primary Key
  Primary   Primary Key
  Key
                          1023          Madagascar
  1023      11                                          11              Julius Wilson      A          $60
                                        Travel site
  1023      12            1056                          12              Monica Jefferson   B          $50
                                        Online Estate
                                        Agency
  1023      16                                          16              Daniel Moore       C          $40


                                                        17              Charles Saul       D          $30
  1056      11

  1056      17




                                                                                                      24
                                                                                                               1/24/2013
    Let’s look at the Employee Project
                      Table
 Can we do anything else with this table?
   Employee Project
       Table
     Project   Employee
     Name      Number
     Primary   Primary Key
     Key

     1023      11
     1023      12

     1023      16

     1056      11

     1056      17

                             25              1/24/2013
    Let’s Look at the Project Table.
Can we do anything else with this table?

  Project Table
 Project       Project
 Number        Name
 Primary Key


 1023          Madagascar
               Travel site


 1056
               Online Estate
               Agency




                                       26
                                            1/24/2013
      Let’s look at the Employee table

      Can we further normalize this table?
              Employee Table

Employee        Employee           Rate       Hourly
Number          Name               Category   Rate
Primary Key



11              Julius Wilson      A          $60


12              Monica Jefferson   B          $50


16              Daniel Moore       C          $40


17              Charles Saul       D          $30




                                                       27   1/24/2013
     See what we can do with the employee
                    table.
 Let’s make an “Rate”
  Table
              Employee Table                  Rate Table
Employee        Employee           Rate       Rate       Hourly   We have now reduced
Number          Name               Category   Category   Rate
Primary Key                                   Primary             the possibility of
                                              Key
                                                                  Employees being paid
11              Julius Wilson      A          A          $60      Incorrectly. Our database
                                                                  Will have more integrity.
12              Monica Jefferson   B          B          $50

16              Daniel Moore       C
                                              C          $40

17              Charles Saul       D
                                              D          $30



                                                                                 28
                                                                                        1/24/2013
                    That’s It!
 Our data is now in 3NF..
 Ready to be used in a DBMS…




                                 29
                                      1/24/2013

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:0
posted:1/25/2013
language:Unknown
pages:29