ITM531CS524 Object Oriented Modeling and Design

1
ITM531/CS524Object Oriented Modeling and Design

• Module 6
• Normalization Algorithm

2
Key Calculation Algorithm Illustration

• Given R ( A B C D E F)
• F AB ? C D DE ?A B E ? D DA ? F FB ?
  E
• Remove A (BCDEF) ? DE ? A ok to remove A
• Remove B (CDEF) ? DE ? B ok to remove B
• Remove C (DEF) ? De ? AB ? C ok to remove C
• Remove D (EF) ? E ? D ok to remove D
• Remove E (F) !? E do not remove E
• Remove F (E) ? DE ? DA ? F ok to remove F
• Therefore E is a key of R

3
Minimal Cover

• Definition
• A set of dependencies G is A COVER for F if G
  F
• Therefore,
• G is A minimal cover if for every H ? G H ? G

4
Minimal Cover (cont.)

• Definition
• F is set of FDs
• For each X ? Y ? F
• H ? F X ? Y
• If H F
• Then ELIM X ? Y from F
• End
• Quit (Since F is minimal cover)

5
Introducing 3NF decomposition algorithm

• The 3NF decomposition algorithm (Bernstein Tods
  1976) produces a lossless 3NF decomposition which
  preserves Functional Dependencies (BISKUP, DAYAL,
  BERNSTTEIN SIGMOD 1979)

6
Elementary FDs

• F is a set of elementary FDs if
• The right side of every FD in F is a single
  attribute
• The left side of every FD in F has no redundant
  attributes.
• For example Z is redundant in ZX ? A if X ? A

7
3NF decomposition algorithm

• Let R be a relation and H an elementary set of
  FDs describing R.
• Fins a minimal cover G of H
• Partition G into groups such that all FDs with
  the same left side are in one group
• Merge groups with equivalent KEYS
• J ? ?
• For each pair of groups Gi Gj, with left sides
  X Y, resp,
• Do
• J ? J ? X? Y, Y ? X
• Gi ? Gj X? A A ?Y
• Gj ? Gj Y ? B B ? X
• Merge Gi and Gj
• End do

8
3NF decomposition algorithm (cont.)

• Find a cover G of G such that (G ? J) return
  each FD in J to the appropriate group of G
  according to its left side.
• If no group of G contains the key set Kr of R,
  then augment G with a group consisting of Kr.
• For each group Gi construct a relation Ri defined
  over all attributes of Gi.

9
Example for 3NF decomposition

• Given the following relationships
• R ( CL, ST, HR, RM, IN, GR)
• CL ? IN
• CL, ST ? GR
• HR, RM ? CL
• HR, ST ? RM
• HR, IN ? RM
• HR, ST ? CL
• HR, IN ? CL
• HR, CL ? RM

10
Example for 3NF decomposition (cont.)

• CL ? IN
• CL, ST ? GR
• HR, RM ? CL
• HR, ST ? RM Eliminate with (6) (8)
• HR, LN ? RM
• HR, ST ? CL
• HR, LN ? CL Eliminate with (3) (5)
• HR, CL ? RM Eliminate with (1) (5)

11
Example for 3NF decomposition (cont.)

• After perform step 1 and 2, we have
• (1) CL ? LN
• (2) CL, ST ? GR
• (3) HR, RM ? CL
• (5) HR, LN ? RM
• (6) HR, ST ? CL

12
Example for 3NF decomposition (cont.)

• Applying step 3 Merging
• HR, RM ? CL
• HR, LN ? RM
• J HR, RM ? HR, LN HR, LN ? HR, RM
• G3 HR, RM ? CL
• ?G35 HR, RM ? CL
• G5 ?

13
Example for 3NF decomposition (cont.)

• Therefore
• G1 CL ? LN
• G2 CL, ST ? GR
• G35 HR, RM ? CL (HR, LN)
• G6 HR, ST ? CL

14
Example for 3NF decomposition (cont.)

• Results
• R1(CL, IN) Key CL
• R2(CL, ST, GR) Key CL, ST
• R35 (HR, RM, IN, CL) Key HR, RM
• HR, IN
• R6 (HR, ST, CL) Key HR, ST