Normalization of database model

1
Normalization of database model
2
Closure an attribute set

• Given a set of attributes a define the closure of
  attribute set a under F (denoted as a) as the
  set of attributes that are functionally depend on
  a under F.
• Example R(A,B,C,G,H,I)
• FA-gtB,A-gtC,CG-gtH,CG-gtI,B-gtH
• (AG) gt AG trivial
• ABCG gt (A-gtB,A-gtC)
• ABCGH gt ( CG-gtH )
• ABCGHI gt (CG-gtI)
• Is AG is a superkey? Does AG-gtR ?
• Is AG a candidate key? A-gtR? Or G-gtR ?
• Compute A and G

3
Why to normalize?

• Teacher(T-Name,T-No,U-Name,U-No)
• Students(S-Name,S-No,U-No)
• Location(U-No,Room,Time)
• If all teacher who teach a particulas unit leavem
  the information about the unit (U-Name) is lost.
• If a teacher teaches many units then information
  on the teacher is unnecessarily replicated.
  Similary information about students attending to
  many units is unnecessarily duplicated.
• To update the U-Name, one may have to update many
  Teacher records.
• Normalization removes such problems!

4
First Normal Form

• A relation is in 1NF if it does not contain
  multivalued field or nested relations, but all
  the fields are atomic.
• Eg
• Teacher(T-Name, T-No,Units(U-No,U-Name))
• Teacher(T-No,T-Name,U-No,U-name)

5
Second Normal Form

• A relation R is in 2NF if it is in 1NF and each
  non-prime attribute of R is fully functionally
  dependent on each candidate key of R.
• Full Functional Dependency
• X,Y--gtZ X-\-gtZ and Y-/-gtZ than X,Y-fully-gtZ
• Eg
• Teacher(T-No,T-Name,U-No,U-Name)
• FT-No-gtT-Name, U-No-gtU-Name
• Is in 2NF?
• U-Name is a non-prime attribute, but it does not
  fully functionally depend on the primary key,
  since U-No-gtU-Name
• Solution
• Teacher(T-No,T-Name)
• Unit(U-No,U-name)
• Teaches(U-No,T-No)

6
Example I

• Consider the following schema
• Source(Supp-No,Part-No,Supp-Details,Supp-Name,Pri
  ce)
• FSupp-No-gtSupp-Details,
• Supp-No,Part-No-gtPrice,
• Supp-No-gtSupp-Name
• Is in 2NF?
• No, since eg. Supp-Details is not prime
  attribute, but is depends only on Supp-No but not
  on Part-No!
• Solution
• Suppliers(Supp-No,Supp-Details,Sup-Name)
• Cost(Supp-No,Part-No,Price)

7
Third Normal Form

• A relation R is in 3NF if it is in 2NF and
  non-prime attribute of R is not transitively
  depend on the primary key.
• Recall transitive dependency
• A-gtB, B-gtC, gt A-gtC
• R(A,B,C) would not be in 3NF
• Eg Employee(E-No,E-Name,Dept-No,Salary,Location)
• FE-No-gtE-Name,E-No-gtDept-No,E-No-gtSalary,
• E-No-gtLocation,Dept-No-gtLocation
• Location transitively depends on E-No, through
  Dept-No.
• Solution Employee(E-No,E-Name,Dept-No,Salary)
• Department(Dept-No,Location)

8
Example II

• Timtetable(S-No,U-No,Time,S-Name,U-Name,Room-No)
• FS-No-gtS-Name,U-No-gtU-Name,
• S-No,Time-gtRoomNo
• Is in 3NF?
• No, it is not even in 2NF!
• Student(S-No,S-Name) Unit(U-No,U-Name)
• Location(S-No,Time,Room-No) Studies(S-No,U-No,Tim
  e)
• It is already in 3NF!

9
Example III

• Stock(Bin-No,Part-No,Bin-Quantity,Re-Order-Level)
  
• FBin-No-gtPart-No,Bin-No-gtBin-Quantity,Part-No-gtR
  e-Order-Level
• Is in 3NF?
• It is in 2NF but not in 3NF because of
  transitive dependency of
• Re-Order-Level!
• Bin-Stock(Bin-No,Part-No,Bin-Quantity)
• Re-Order(Part-No,Re-Order-Level)