Normalization

1
Normalization

• Chapter 3

2
Terminology

• Primary Key
• Foreign Key
• Secondary Key
• Alternate key
• Candidate Key

3
Terminology

• Functionally Dependent
• Functionally Determinant

4
Terminology

• First Normal Form
• Second Normal Form
• Third Normal Form

5
Coding for Relations

• Relation(Pri Key, Attribute2, Attribute3)
• Dependencies
• Primary Key--gtAttribute2, Attribute3)

6
Normalization Example (1NF)

• Packid Tagnum Instdate Softcost
• AC01 32808 09/13/95 754.95
• DB32 32808 12/03/95 380.00
• DB32 37691 06/15/95 380.00
• DB33 57772 05/27/95 412.77
• WP08 32808 01/12/95 185.00
• WP08 37691 06/15/95 227.50

7
Normalization Example (1NF)

• ID Name Dept
• 125 Burns, Joe FIN
• 145 Collins, Clara FIN
• 157 Dunn, Betty ACCT
• 160 Evers, Don MGMT
• 175 Fuller, Sue FIN
• 187 Goodner, Wade MIS

8
Normalization Example (2NF)

• Packid Packname Tagnum Compid Cost
• Dependencies
• Packid ---gt Packname
• Tagnum ---gt Compid
• Packid, Tagnum --gtPackname, Compid, Cost

9
Normalization Example (3NF)

• ID Name Phone Payclass Payrate
• Dependencies
• ID --gt Name, Phone, Payclass, Payrate
• Payclass --gt Payrate

10
Normalization - Quick View

• 1NF -- The Key
• 2Nf -- The Whole Key
• 3NF -- Nothing but the key

11
Dependency Problem

• Determine dependencies
• STUDENT(STUNUMB, STUNAME, NUMBCRED,
• ADVNUMB, ADVNAME, DEPTNUMB,
• DEPTNAME, CRSENUMB, CRSEDESC,
• CRSETERM, GRADE)
• Convert to 3NF

12
Dependencies (Solution)

• Studnumb,Crsenumb, Crseterm--gtStuname, Numbcred,
  Advnumb, Advname, Deptnumb, Deptname, Crsedesc,
  Grade
• Stunumb---gtStudname, Advnumb, Advname
• Crsenumb--gtNumbcred, Deptnumb, Deptname, Crsedesc
• Advnumb--gtAdvname
• Deptnumb--gtDeptname

13
Conversion to 1NF

• Grade (Studnumb, Crsenumb, Crseterm, Stuname,
  Numbcred, Advnumb, Advnumb, Advname, Deptnumb,
  Deptname, Crsedesc, Grade)

14
Conversion to 2NF

• Grade(Stunumb, Crsenumb, Crseterm,Grade)
• Student(Stunumb,Stuname,Advnumb, Advname)
• Course(Crsenumb, Numbcred,Deptnumb,Deptname,
  Crsedesc)

15
Conversion to 3NF

• Grade(Stunumb, Crsenumb, Crseterm,Grade)
• Student(Stunumb,Stuname,Advnumb)
• Course(Crsenumb, Numbcred,Deptnumb, Crsedesc)
• Advisor (Advnumb, Advname)
• Department (Deptnumb, Deptname)