ITM531CS524 Object Oriented Modeling and Design

1
ITM531/CS524Object Oriented Modeling and Design

• Module 5
• Normal Forms

2
Definition of Key

• A minimal collection of attributes X in relation
  R (A, B), such that A ? A, B.

3
Normalization forms

• First Normalization Form (1NF)
• Second Normalization Form (2NF)
• Third Normalization Form (3NF)
• Boyce Codd Normal Form (BCNF)

4
Transitively dependent

• An attribute Z is transitively dependent on
  attribute set X, if there exists A Y such that X
  ? Y, Y ? Z, and Y !? X with Z distinct from X and
  Y.

5
Third Normal Form (3NF)

• A normalized relation is in 3NF if none of its
  non-key attributes is transitively dependent on
  any key.

6
Boyce Codd Normal Form

• A relation is in BCNF if for every X ? A that
  holds in R, A ? X, it is the case that X includes
  a key of R.

7
3NF example

• Given the following table
• CLASS (STUDENT, COURSE, TA, GRADE)
• Give the following relationships
• F STUDENT, COURSE ? TA GRADE TA ? COURSE
• Given the following assumption
• Each TA will only be assigned to one class
• Given the following keys
• STUDENT, COURSE
• STUDENT, TA
• Therefore
• Class is in 3NF but not BCNF since TA ? COURSE
  holds, but TA does not include a key of Class.

8
Convert 3NF to BCNF

• It is not always possible to obtain a BCNF
  representation which is FD-PRESERVING and
  LOSSLESS. However, a LOSSLESS representation can
  be obtained through XYZ decomposition.

9
Example from 3NF to BCNF

• Given following class
• CLASS (STUDENT, COURSE, TA, GRADE)
• Given following relationship
• F STUDENT, COURSE ? TA GRADE TA ? COURSE
• We decompose the above table to the following two
  tables
• CLASS1 (TA, COURSE)
• F1 TA ? COURSE
• CLASS2 (STUDENT, TA, GRADE)
• F2 STUDENT, TA ? GRADE

10
Example from 3NF to BCNF (cont.)

• In this case, the above two tables are in BCNF.
• During the decomposition, the STUDENT, COURSE ?
  TA relationship is lost, but the decomposition
  is LOSSLESS.