Data Management
Normalization
Week 7
Definition
• Normalization is a process for evaluating and correcting table structures to
minimize data redundancies, thereby reducing the likelihood of data
anomalies (usually by breaking up their attributes into smaller relations)
• Normalization works through a series of stages called NORMAL FORMS: 1 NF, 2 NF
and 3 NF.
• From structural point of view, higher NF is better. In practice, the highest level of
normalization is not always the most desirable.
• Denormalization produces a lower normal form to meet performance
requirements (reduce time to join operations)
Base Relations EMP_DEPT and EMP_PROJ formed after a Natural Join :
with redundant information
Two relation schemas suffering from update anomalies
EXAMPLE OF AN INSERT ANOMALY
• Consider the relation:
• EMP_PROJ(Emp#, Proj#, Ename, Pname, No_hours)
• Insert Anomaly:
• Cannot insert a project unless an employee is assigned to it.
• Conversely
• Cannot insert an employee unless a he/she is assigned to a project.
EXAMPLE OF AN DELETE ANOMALY
• Consider the relation:
• EMP_PROJ (Emp#, Proj#, Ename, Pname, No_hours)
• Delete Anomaly:
• When a project is deleted, it will result in deleting all the employees who work
on that project.
• Alternately, if an employee is the sole employee on a project, deleting that
employee would result in deleting the corresponding project.
EXAMPLE OF A MODIFICATION ANOMALY
• Consider the relation:
• EMP_PROJ(Emp#, Proj#, Ename, Pname, No_hours)
• Update Anomaly:
• Changing the name of project number P1 from “Billing” to “Customer-
Accounting” may cause this update to be made for all 100 employees
working on project P1.
Normalization into 1NF
Normalization of nested relations into 1NF
Second Normal Form
• Examples:
• {SSN, PNUMBER} -> HOURS is a full FD since neither SSN -> HOURS nor
PNUMBER -> HOURS hold
• {SSN, PNUMBER} -> ENAME is not a full FD (it is called a partial dependency )
since SSN -> ENAME also holds
Third Normal Form
• Examples:
• SSN -> DMGRSSN is a transitive FD
• Since SSN -> DNUMBER and DNUMBER -> DMGRSSN hold
• Assume that DNUMBER is not a candidate key
• SSN -> ENAME is non-transitive
• Since there is no set of attributes X where SSN -> X and X -> ENAME
Normalizing into 2NF and 3NF
Normalization of LOTS into 2NF and 3NF
SUMMARY OF NORMAL FORMS based
on Primary Keys
Exercise 1
ISBN BookTitle Author_Num LastName Publisher Royalty Edition
• Define all FUNCTIONAL DEPENDENCIES
• Transform that table into 2NF and 3 NF
Exercise 2
INV_NUM PROD_NUM SALE_DATE PROD_LABEL VEND_CODE VEND_NAME QUANT_SOLD PROD_PRICE
211347 AA-E3422QW 15-JAN-2012 Rotary Sander 211 NeverFail, Inc 1 $49.95
211347 QD-300932X 15-JAN-2012 0.25-in. drill bit 211 NeverFail, Inc 8 $3.45
211347 RU-995748G 15-JAN-2012 Band saw 309 BeGood, Inc 1 $39.99
211348 AA-E3422QW 15-JAN-2012 Rotary sander 211 NeverFail, inc 2 $49.95
211349 GH-778345P 16-JAN-2012 Power drill 157 ToughGo, Inc 1 $87.75
• Identify all FUNCTIONAL DEPENDENCIES (assume that any given product is supplied
by a single vendor, but a vendor can supply many products)
• Transform that table into 2 NF and 3 NF