# Normalization

Document Sample

```					Normalization

Topics
   Why normalization is needed
   What causes anomalies
   What the 4 normal forms are
   How to normalize a relation to 3NF
   Beyond 3NF - BCNF

Why Normalization
Student_ID Student_Name Major Course_ID Course_Title Instructor_Name Office Grade
111111111 Ford          SE    SE405      Java Prog      Liu                 W402         A
FIN101     Intro to Fin   T hompson           W405         C
222222222 Jackson       MKT   MKT 312    MKT Strategy   Sawacki             W403         B
MIS101     Computers      Blaine              W404         A
FIN101     Intro to FIN   T hompson           W405         B

This is a Non-Normal form since it is NOT a Relation:
Multi-valued attributes or “repeating groups”

Why Normalization
   Anomalies
 InsertionAnomaly
 Deletion Anomaly
 Update Anomaly

Student_ID Student_Name Major Course_ID Course_Title Instructor_Name Office Grade
111111111   Ford          SE     SE405     Java Prog      Liu                W402      A
111111111   Ford          SE     FIN101    Intro to Fin   T hompson          W405      C
222222222   Jackson       MKT    MKT 312   MKT Strategy   Sawacki            W403      B
222222222   Jackson       MKT    MIS101    Computers      Blaine             W404      A
222222222   Jackson       MKT    FIN101    Intro to FIN   T hompson          W405      B
To analyze design                Normalization allows us to start with a table and
To identify problems             produce a new collection of tables that represent
To correct anomalies             the same information but is free of problems.

What Causes Anomalies
    Existence of functional
dependencies
 Key: a set of one or more attributes in a relation
that identifies uniquely ALL the other attributes in
the relation
 Functional Dependency: a set of one or
more attributes in a relation that identifies uniquely
SOME of the other attributes in the relation
 Transitive Dependency: a special kind of
functional dependency. A --> B --> C

Student_ID Student_Name Major Course_ID Course_Title Instructor_Name Office Grade
111111111   Ford          SE    SE405      Java Prog      Liu               W402      A
111111111   Ford          SE    FIN101     Intro to Fin   Thompson          W405      C
222222222   Jackson       MKT   MKT312     MKT Strategy   Sawacki           W403      B
222222222   Jackson       MKT   MIS101     Computers      Blaine            W404      A
222222222   Jackson       MKT   FIN101     Intro to Fin   Thompson          W405      B
The 4 Normal Forms
   1NF (First Normal Form)
   No repeating columns

   2NF (Second Normal Form)
   1NF + No sub-key => non-key

   3NF (Third Normal Form)
   2NF + No non-key => non-key

   BCNF (Forth Normal Form)
   3NF + No non-key => sub-key

Student_ID Student_Name Major Course_ID Course_Title Instructor_Name Office Grade
111111111   Ford        SE        SE405    Java Prog      Liu               W402      A
111111111   Ford        SE        FIN101   Intro to Fin   Thompson          W405      C
222222222   Jackson     MKT       MKT312   MKT Strategy   Sawacki           W403      B
222222222   Jackson     MKT       MIS101   Computers      Blaine            W404      A
222222222   Jackson     MKT       FIN101   Intro to FIN   Thompson          W405      B
Converting a Relation to
3NF - An Algorithm
   Determine the key to relation T
   Determine the FDs in relation T
   For every FD whose LHS is not a key
in relation T, obtain 2 relations T1 and
T2 so that T1 contains all attributes of
the FD, T2 contains all the attributes
of T except the RHS attributes of the
FD
   If the FD is transitive (A-->B-->C),
always decompose B-->C first

Converting To 3NF - An
Example
Student_ID Student_Name Major Course_ID Course_Title Instructor_Name Office Grade
111111111   Ford          SE     SE405    Java Prog      Liu               W402      A
111111111   Ford          SE     FIN101   Intro to Fin   Thompson          W405      C
222222222   Jackson       MKT    MKT312   MKT Strategy   Sawacki           W403      B
222222222   Jackson       MKT    MIS101   Computers      Blaine            W404      A
222222222   Jackson       MKT    FIN101   Intro to FIN   Thompson          W405      B
   Key to the relation
     Student_ID, Course_ID
   FDs in the relation
     STUDENT_ID ---> STUDENT_NAME, MAJOR
     COURSE_ID ---> COURSE_TITLE,
INSTRUCTOR_NAME -->OFFICE
     STUDENT_ID + COURSE_ID ---> GRADE

Converting To 3NF - An
Example
Student_ID Student_Name Major Course_ID Course_Title Instructor_Name Office Grade
111111111   Ford        SE      SE405     Java Prog      Liu               W402      A
111111111   Ford        SE      FIN101    Intro to Fin   Thompson          W405      C
222222222   Jackson     MKT     MKT312    MKT Strategy   Sawacki           W403      B
222222222   Jackson     MKT     MIS101    Computers      Blaine            W404      A
222222222   Jackson     MKT     FIN101    Intro to FIN   Thompson          W405      B

Student_ID Student_Name Major    Course_ID Course_Title Instructor_Name Office
111111111 Ford          SE       SE405      Java Prog       Liu                    W402
222222222 Jackson       MKT      FIN101     Intro to Fin    Thompson               W405
MKT312     MKT Strategy    Sawacki                W403
MIS101     Computers       Blaine                 W404

Converting To 3NF - An
Example
Student_ID       Course_ID Grade       Student_ID Student_Name Major
111111111 Ford            SE
111111111 SE405       A
222222222 Jackson         MKT
111111111 FIN101      C
222222222 MKT312      B
222222222 MIS101      A

Course_ID Course_Title Instructor_Name Office         Instructor_Name Office
SE405        Java Prog      Liu            W402       Liu              W402
FIN101       Intro to Fin   Thompson       W405       Thompson         W405
Sawacki          W403
MKT312       MKT Strategy   Sawacki        W403
Blaine           W404
MIS101       Computers      Blaine         W404

Converting To 3NF - An
Example
Student_ID         Course_ID Grade       Student_ID Student_Name Major
111111111 Ford          SE
111111111 SE405          A
222222222 Jackson       MKT
111111111 FIN101         C
222222222 MKT312         B
222222222 MIS101         A

Course_ID Course_Title Instructor_Name          Instructor_Name Office
SE405        Java Prog      Liu                 Liu             W402
FIN101       Intro to Fin   Thompson            Thompson        W405
MKT312       MKT Strategy   Sawacki             Sawacki         W403
MIS101       Computers      Blaine              Blaine          W404

Boyce-Codd BCNF
   Every determinant is a key (3NF +
no non-key -->subkey)
   A student can have 1 or more majors
   A major can have 1 or more advisors    111111111 SE    Benjamin
students in the major

111111111 Benjamin     Benjamin           SE
111111111 Floyd        Floyd              MIS
222222222 Carr         Carr               MKT