CS457 Syllabus & Progress

Example in Decomposition into BCNF

Relation:

A B C D E F G H I J K L M Functional dependencies: A -> B C D E E -> F G H I -> J A I -> K A L -> M

First: Find all keys

Necessary attributes for key: A I L (they are not on the RHS) AIL⁺ = A I L B C D E F G H J K M = R !!!
Therefore: key = AIL (only 1 key)

Check R for BCNF condition:

R = (A B C D E F G H I J K L M) Key(R) = AIL A -> B C D E E -> F G H I -> J A I -> K A L -> M A violating FD =============== A -> B C D E Determinant A is not a super key Prepare to decompose, compute: A⁺ = A B C D E F G H

Decompose:

R = (A B C D E F G H I J K L M) Key(R) = AIL R1 = (A B C D E F G H) Key(R1) = A R2 = (A I J K L M) R1 ∩ R2 -> R1 Key(R2) = AIL

Check R1 for BCNF condition:

R1 = (A B C D E F G H) Key(R1) = A A -> B C D E E -> F G H I -> J A I -> K A L -> M A violating FD =============== E -> F G H Determinant E is not a super key Prepare to decompose, compute: E⁺ = E F G H

Decompose:

R = (A B C D E F G H I J K L M) Key(R) = AIL R1 = (A B C D E F G H) Key(R1) = A R11 = (A B C D E) Key(R11) = A R12 = (E F G H) R11 ∩ R12 -> R12 Key(R12) = E R2 = (A I J K L M) Key(R2) = AIL

Check R11 for BCNF condition:

R11 = (A B C D E) Key(R11) = A A -> B C D E E -> F G H I -> J A I -> K A L -> M OK !

Check R12 for BCNF condition:

R11 = (E F G H) Key(R12) = E A -> B C D E E -> F G H I -> J A I -> K A L -> M OK !

Check R2 for BCNF condition:

R2 = (A I J K L M) Key(R2) = AIL A -> B C D E E -> F G H I -> J A I -> K A L -> M A violating FD =============== I -> J Determinant I is not a super key Prepare to decompose, compute: I⁺ = IJ

Decompose:

R = (A B C D E F G H I J K L M) Key(R) = AIL R1 = (A B C D E F G H) Key(R1) = A R11 = (A B C D E) Key(R11) = A R12 = (E F G H) Key(R12) = E R2 = (A I J K L M) Key(R2) = AIL R21 = (I J) Key(R22) = I R22 = (A I K L M) R21 ∩ R22 -> R21 Key(R22) = AIL

Check R21 for BCNF condition:

R21 = (I J) Key(R21) = I A -> B C D E E -> F G H I -> J A I -> K A L -> M OK !

Check R22 for BCNF condition:

R22 = (A I K L M) Key(R22) = AIL A -> B C D E E -> F G H I -> J A I -> K A L -> M A violating FD =============== A I -> K Determinant (A,I) is not a super key Prepare to decompose, compute: (AI)⁺ = AIK

Decompose R22:

R = (A B C D E F G H I J K L M) Key(R) = AIL R1 = (A B C D E F G H) Key(R1) = A R11 = (A B C D E) Key(R11) = A R12 = (E F G H) Key(R12) = E R2 = (A I J K L M) Key(R2) = AIL R21 = (I J) Key(R22) = I R22 = (A I K L M) Key(R22) = AIL R221 = (A I K) Key(R221) = AI R222 = (A I L M) Key(R222) = AIL R221 ∩ R222 -> R221

Check R221 for BCNF condition:

R221 = (A I K) Key(R221) = A I A -> B C D E E -> F G H I -> J A I -> K A L -> M OK !

Check R222 for BCNF condition:

R222 = (A I L M) Key(R22) = AIL A -> B C D E E -> F G H I -> J A I -> K A L -> M A violating FD =============== A L -> M Determinant (A,L) is not a super key Prepare to decompose, compute: (AL)⁺ = ALM

Decompose R22:

Check R2221 for BCNF condition:

R2221 = (A L M) Key(R221) = A L A -> B C D E E -> F G H I -> J A I -> K A L -> M OK !

Check R2222 for BCNF condition:

R221 = (A I L) Key(R221) = A I L A -> B C D E E -> F G H I -> J A I -> K A L -> M OK !

DONE !

Answer:

R11 = (A B C D E) Key(R11) = A R12 = (E F G H) Key(R12) = E R21 = (I J) Key(R22) = I R221 = (A I K) Key(R221) = AI R2221 = (A L M) Key(R2221) = AL R2222 = (A I L) Key(R2222) = AIL

Take a good look at what we have done:

Suppose we were told that we the DB must store the following data:

SSN FName LName SupSSN DNum DName MgrSSN MgrStartDate A B C D E F G H Pnum PName Hours DependName Relationship I J K L M
And the data has the following dependencies:


   SSN -> FName LName SupSSN DNum
   DNum -> DName MgrSSN MgrStartDate
   PNum -> PName
   SSN PNum -> Hours
   SSN DependName -> Relationship

   A -> B C D E
   E -> F G H
   I -> J
   A I -> K
   A L -> M

Decomposition algorithm produces the following relations:

R11 = (SSN FName LName SupSSN DNum) Key(R11) = SSN R12 = (DNum DName MgrSSN MgrStartDate) Key(R12) = DNum R21 = (PNum PName) Key(R22) = PNum R221 = (SSN PNum Hours) Key(R221) = SSN PNum R2221 = (SSN DependName Relationship) Key(R2221) = SSN DependName R2222 = (SSN PNum DependName) Key(R2222) = SSN PNum DependName

It is exactly the same as an (abreviated) company database, except for (SSN PNum DependName)

A relation where every attribute is part of the primary key is called a "trivial" relation.
Trivial relations are often the "by-product" of decompositions.
Most trivial relations are not meaningful and can be discarded. (There are some meaningful trivial relations)