ITM531CS524 Object Oriented Modeling and Design

1
ITM531/CS524Object Oriented Modeling and Design

• Module 4
• Decomposition Theory

2
Decomposition Theory

• Lossless decomposition
• FD-Preserving decomposition

3
Lossless Decomposition

• R1 (A1) PROJECTION (R, A1)
• R2 (A2) PROJECTION (R, A2)
• Let R(A) be decomposed into R1 (A1) R2 (A2).
• The decomposition is LOSSLESS if for all
  extensions of R
• R JOIN (R1, R2)
• Otherwise it is LOSSY.

4
XYZ Ddecomposition

• Let R(X, Y, Z) be a relation where F X? Y, .
  The decomposition of R into R(X Y), R2(X Z) is
  LOSSLESS.
• Example R( A B C D)
• F B ? C
• ?
• R1 ( B C) R2 ( B A D)

5
Example Joins

• R ( EMP, PROJ, DEPT, ACCT)
• F EMP, PROJ ? DEPT ACCT
• EMP ? DEPT
• DEPT, PROJ ? ACCT
• LOSSLESS
• R1 (EMP, DEPT)
• R2 (EMP, PROJ, ACCT)
• LOSSY
• R1 (EMP, DEPT)
• R2 (DEPT, PROJ, ACCT)

6
Multiple Lossless Decomposition

• If R1, R2 is a LOSSLESS decomposition of R
  and R11, R12 is a LOSSLESS decomposition of R1,
  then R11, R12, R2 is a LOSSLESS decomposition of
  R.
• Example
• R (A B C D E)
• F A ? B C ? D
• ? ?
• R1(A C D E) R2(A B)
• ? ?
• R11 ( C D) R12 (C A E)

7
FD Preserving decomposition

• Let T be a decomposition applied to relation R
  yielding relations R1 R2.
• Let F, F1, F2 be the set of FDs in R, R1 R2
  respectively
• T is FD- Preserving
• If F (F1 ? F2)

8
Example FD Preserving

• R (EMP, PROJ, DEPT, SUPV, ACCT)
• F EMP, PROJ ? DEPT SUPV ACCT
• EMP ? SUPV DEPT
• SUPV ? DEPT
• ?
• R1 ( EMP, SUPV, DEPT)
• EMP ? SUPV DEPT
• SUPV ? DEPT
• R2 (EMP, PROJ, ACCT)
• EMP, PROJ ? ACCT
• EMP,PROJ ? DEPT SUPV are derivable