Docstoc

webcast_Normalization

Document Sample
webcast_Normalization Powered By Docstoc
					                  Lecture 4
• Normalization

• An Normalization
  example




                     INSS 651   1
            Normalization
A process for evaluating and correcting table
  structure

Minimize data redundancy

Eliminate Anomalies


                    INSS 651                2
  Is Normalization Necessary?
NO

But it is helpful to maintain data integrity and
 consistency




                     INSS 651                  3
           Denormalization
• Reversing normalization
• i.e from 3rd NF to 2nd NF
• Or 2nd to 1st NF




                    INSS 651   4
              Anomalies
• Update—requires update in multiple
  locations
• Deletion—A deletion may lose important
  information
• Insertion—Requires complete definitions,
  ie does see page 187 (an employee can
  not be entered unless he is assigned a
  project
                   INSS 651                  5
      Normalization Process
• 1st NF
• 2nd NF
• 3rd NF

• Almost for 90-98% application 3rd NF is
  sufficient


                    INSS 651                6
             Dependency
When an attribute value depends on
  attribute B then B is dependent on A
A---B
or values of B can be determined by value of
  A, reverse may or may not be true
Ex:
ssn--Name
SSN, CID--Grade
                   INSS 651                7
Un-normalized relation

                            Remove REPEATING groups
   1st NF

                            Remove PARTIAL dependency

   2nd NF

                            Remove TRANSIENT dependency

   3rd NF
                         Every determinant is a candidate key


   Boyce-CODD NF

   If we can convert a relation into 3NF almost 90-98% of anomalies are removed


                                      INSS 651                                    8
              Un-normalized form
A relation is in un-normalized form, if it contains repeating group
Typically shown in parentheses

Ex: PART NO DESC.          VENDOR-NAME ADDRESS UNIT-COST
   1234         LOGIC       INTEL              SAN JOSE 150.00
                chip        LSI LOGIC          SAN JOSE 120.00
   5678         MEMORY INTEL                   SAN JOSE       50.00
                chip

SUPPLIER
(Part_no, Part_DESC, (Vendor_name, Vendor_address, Unit_cost))




                                  INSS 651                            9
                        Another Way

(Part_NO, V_NAME)-> Unit_cost
Part_NO->P_Desc (Partial dependency)
V-Name->V_DESC (Partial Dependency)




     Part_NO   P-DESC   V_NAME   V_ADDRESS   UNIT_COST




                                  INSS 651               10
                           1st NF
A relation is in 1st NF if it does NOT contain any repeating groups
(Part_no, Part_DESC, (Vendor_name, Vendor_address, Unit_cost))

1st NF..remove repeating groups
Break it into TWO relations

One without repeating group and
ONE with repeating group AND PK of other relation

S1 (Part_no, Part_DESC)
S2 (Vendor_name, Part_no, Vendor_address, Unit_cost




                                  INSS 651                        11
                          2nd NF
A relation is in 2nf NF if it is in 1stNF and it does not
   contain any partial dependency

Partial dependency: A partial dependency exists if an
  attribute is dependent ONLY on PART of the PK and the
  WHOLE PK

We must examine each relation for partial dependency

NOTE: A partial dependency can only exist if there are
  more than ONE attribute as PK

                             INSS 651                       12
S1 (Part_no, Part_DESC)
S2 (Vendor_name, Part_no, Vendor_address,
  Unit_cost
Note S1 is already in 2nd NF since there is only
  attribute as PK
In S2:
Question is Vendor_address dependent on BOTH
  vendor_name AND Part_NO?

Question is Unit_price dependent on BOTH
 vendor_name AND Part_NO?
                      INSS 651                 13
Question is Vendor_address dependent on BOTH
 vendor_name AND Part_NO?

Answer: NO
Give me vendor_no and I can find
  vendor_address, we do NOT need Part_No to
  know vendor_address, ie Vendor_address
  depends ONLY Vendor_name, hence the partial
  dependency

                    INSS 651                14
Question is Unit_price dependent on BOTH
 vendor_name AND Part_NO?
YES if you examine the table, price changes
 with vendor and part_no, ie price depnds
 on both Part_no AND which vendor
 supplies it



                   INSS 651               15
  Remove Partial Dependency
VENDOR _ADDRESS

           VENDOR_name
                                    UNIT_PRICE
           PART#



Create TWO tables:
One with Partial dependency and other without it
S21 (Vendor_name, vendor_address)
S22(Vendor_name, Part_no, Unit_price)

                         INSS 651                  16
                   3rd NF
A relation is in 3rd NF if it is in 2nd NF and it
  does not contain any transitive
  dependency
Transitive dependency: A transitive
  dependency exists when some of the non-
  key attributes are dependent on other non-
  key attributes


                      INSS 651                  17
So far we have three relations that are in
 at least 2nd NF
S1 (Part_no, Part_DESC)
S21 (Vendor_name, vendor_address)
S22(Vendor_name, Part_no, Unit_price)

S1, S21 & S22 are also in 3rd NF since there
 is ONLY ONE non_key attribute and
 transitive dependency can NOT exist
                    INSS 651                 18
               ERD


PART   PART-SUPPLIED        VENDOR




                 INSS 651            19
                     Q6/p 175
A Using notation from the book
a. (C1, C3)- C2,C4,C5
(i.e., C2, C4, C5) are functionally dependent on C1 and C3
Above relation is in at least 1stNF, since there are No
      repeating groups

C1C2 there is PARTIAL dependency since C2 depends
   on PART of the PK and the whole PK

C4--C5 (transitive dependency since C5 ( a non-PK
    attribute) depends on another non-PK attribute (C4)

                          INSS 651                        20
                    Part b


                    Table 1

                    Primary key: C1
C1   C2             Foreign key: None
                    Normal form: 3NF



                    Table 2

C1   C3   C4   C5   Primary key: C1 + C3
                    Foreign key: C1 (to Table 1)
                    Normal form: 2NF, because the
                       table exhibits the transitive
                       dependencies C4          C5




                         INSS 651                      21
                 Part c

                 Table 1
                 Primary key: C1
      C1    C2   Foreign key: None
                 Normal form: 3NF



                 Table 2
                 Primary key: C1 + C3
C1    C3    C4   Foreign key: C1 (to Table 1)
                              C4 (to Table 3)
                 Normal form: 3NF


                 Table 3
                 Primary key: C4
     C4    C5    Foreign key: None
                 Normal form: 3NF




                      INSS 651                  22
                                             Q8/P177

Table P5.8 Sample ITEM Records
                 Sample Value        Sample Value        Sample Value
Attribute Name
ITEM_ID          231134-678          342245-225          254668-449
ITEM_LABEL       HP DeskJet 895Cse   HP Toner            DT Scanner
ROOM_NUMBER      325                 325                 123
BLDG_CODE        NTC                 NTC                 CSF
BLDG_NAME        Nottooclear         Nottoclear          Canseefar
BLDG_MANAGER     I. B. Rightonit     I. B. Rightonit     May B. Next




                                                       INSS 651         23
                            Problem 8 Solution


ITEM_ID ITEM_DESCRIPTION BLDG_ROOM BLDG_CODE BLDG_NAME BLDG_MANAGER




                              INSS 651                          24
ITEM_ID ITEM_DESCRIPTION BLDG_ROOM BLDG_CODE BLDG_NAME BLDG_MANAGER




                 Problem 9 Solution: All tables in 3NF


           ITEM_ID ITEM_DESCRIPTION BLDG_ROOM BLDG_CODE




          BLDG_CODE BLDG_NAME       EMP_CODE




          EMP_CODE   EMP_LNAME EMP_FNAME           EMP_INITIAL
                             INSS 651                            25
              1
                  EMPLOYEE

                       (0,N)


                   manages



                       (1,1)
              M                1                     M
                  BUILDING             contains              ITEM
                               (0,N)               (1,1)



EMPLOYEE                       BUILDING                    ITEM
EMP_CODE      1                BLDG_CODE    1              ITEM_ID
EMP_LNAME                      BLDG_NAME                   ITEM_DESCRIPTION
EMP_FNAME              M       EMP_CODE                    ITEM_ROOM
EMP_INITIAL                                         M      BLDG_CODE




                                                  INSS 651                    26
INSS 651   27

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:13
posted:8/16/2012
language:
pages:27