Docstoc

NORMALIZATION

Document Sample
NORMALIZATION Powered By Docstoc
					          Normalization


   A technique that organizes data
attributes (or fields) such that they are
 grouped to form stable, flexible and
           adaptive entities.


                                            5-1
              Normal Forms
 First Normal Form (1NF)
  There are no attributes (fields) that have more
   than one value for a single instance (record) of
   the entity.
  There are no repeating fields
 Second Normal Form (2NF)
  1NF
  The value of all non-primary key fields are
   dependent on the full primary key - not just part
   of it

                                                  5-2
              Normal Forms

 Third Normal Form (3NF)
  2NF
  The values of its non-primary key fields are not
   dependent on any other non-primary key field




                                                  5-3
                         Keys
 Primary
   A field whose values identify one and only one record
    in a table
   Every ACCESS table must have EXACTLY one primary
    key
 Foreign
   A primary key of one table that is included in another
    table
   Links the records in one database table to another
    table

                                                       5-4
             Steps Of Normalization

1. Remove repeating groups of data fields.
2.   Remove partial dependencies:

     If any data fields are dependent upon only
     part of the primary key defined for your core
     business transaction, split those fields out into a
     separate table, using the field(s) upon which they
     are dependent as the primary key.




                                                           5-5
              Steps Of Normalization

3. Remove transitive dependencies:
     If any fields are dependent upon a non-key
     field, split those fields out into a separate table,
     using the non-key field as the primary key.

4.   Remove any field which can be easily derived from
     other field(s): If any fields can be derived from
     others, remove from database.

5.   Check by drawing/comparing to E-R diagram: is
     this model correct/logical?

                                                            5-6
            Non-normalized table



 In this example, we want to design an order
  database.
    Identify all fields needed in this database
    Which attribute above uniquely identifies each order?
      • Order Number is the starting primary key




                                                       5-7
             Non-normalized table

                Original Primary Key: Order Number

 An order can have many parts. This is a repeating field. What
  fields above are more closely related to a part than an order?
  Part Number, Number of Parts [quantity ordered], Part
  Description, Unit Cost, Supplier Number [for the part], supplier
  name, supplier address.
 Remove these fields and put them into a separate table. Which
  one of these fields uniquely identifies the part information?
  [Part Number]
     The Primary Key to the new table will be the original primary
      key combined with the new unique identifier (Part Number)
     When a primary key has two or more parts, it is called a
      composite primary key.

                                                               5-8
                First Normal Form


Primary Key: Order Number




Composite Primary Key: Order Number plus Part Number




                                                       5-9
         Checking Second Normal Form

 Primary Key: Order Number




Composite Primary Key: Order Number plus Part Number

 Look at the second table
    If you know a part of the primary key, could you derive
     any of the non-keyed fields?
        • For example: If you know the part number [part of the
          primary key], could you derive the part description, the unit
          cost and the supplier information for that part?
        • Violation of second normal form

                                                                    5-10
         Checking Second Normal Form

Primary Key: Order Number




Composite Primary Key: Order Number plus Part Number

 Remove the non-keyed fields from the second table that are
  dependent on only part of the primary key and put them in a
  new table [Part Description. Unit Cost, Supplier Information]
 Copy the part of the primary key from which these fields can
  be determined into the new table. This field will become the
  primary key of the new table.


                                                            5-11
                  Second Normal Form


   Primary Key: Order Number




  Composite Primary Key: Order Number plus Part Number




  Primary Key: Part Number

Notice: I moved the PART table name to the table that had the most
information about the part. The ordered part table name came from the
rules for the ERD. One order can have many parts on it and one part
[product line] can be on many orders.

                                                                    5-12
       Checking Third Normal Form

  Primary Key: Order Number




  Composite Primary Key: Order Number plus Part Number




  Primary Key: Part Number

 Look at the third table: If you know the Supplier
  Number [a non-keyed field], you can determine the
  supplier name and address [non-keyed fields]
    Violation of third normal form

                                                         5-13
        Checking Third Normal Form

  Primary Key: Order Number



 Composite Primary Key: Order Number plus Part Number




 Primary Key: Part Number
 Remove the fields that can be determined from the other field from
  this table. Put them [supplier name and address] in a new table.
 Copy the field that determines them from the original table into the
  new table [supplier number].
    This field will be the primary key of the new table.

                                                                   5-14
        Part 1 of Third Normal Form

Primary Key: Order Number



Composite Primary Key: Order Number plus Part Number




Primary Key: Part Number




Primary Key: Supplier Number

                                                       5-15
         Checking Third Normal Form

Primary Key: Order Number                  Primary Key: Supplier Number




Composite Primary Key: Order Number plus Part Number



Primary Key: Part Number

    The top table has the same violation as before but with the
    customer information.
      Customer name and address can be determined from the customer
       number
      Violation of 3rd Normal Form

                                                                       5-16
        CheckingThird Normal Form

Primary Key: Order Number                  Primary Key: Supplier Number



Composite Primary Key: Order Number plus Part Number



Primary Key: Part Number

 Remove the fields that can be determined from the other field from
  this table. Put them [cust name, address] in a new table.
 Copy the field that determines them from the original table into the
  new table [customer number].
     This field will be the primary key of the new table.

                                                                   5-17
                Third Normal Form

                                      Primary Key: Customer Number
Primary Key: Order Number



                                      Primary Key: Supplier Number

Composite Primary Key: Order Number plus Part Number




Primary Key: Part Number


                                                               5-18
                    Draw the ERD for Validation
                                                        Orders
Customers                                          M    Order No
Cust No             1                    Place          Order Date
Cust Name                                               Delivery Date
Cust Address                                            Order Total
                                                        Customer No
                                Supplier
                                Supplier No                     1
                            1   Supplier Name
                                Supplier Address           Contain
                Locate
          M
                                                                 M
Parts [Inventory]
Part Number                                            Ordered Part
Part Description        1                          M   Order No
Unit Cost                               Locate         Part Number
Supplier No                                            Number of Parts


                                                                        5-19
Normalized Relation




                      5-20

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:10
posted:11/25/2011
language:English
pages:20