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:

 ``` 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 R2221 = (A L M) Key(R2221) = AL R2222 = (A I L) Key(R2222) = AIL R2221 ∩ R2222 -> R2221 ```

• 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 !

 ``` 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)