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 |
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 !!! |
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 |
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 |
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 |
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 |
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 ! |
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 ! |
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 |
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 |
R21 = (I J) Key(R21) = I A -> B C D E E -> F G H I -> J A I -> K A L -> M OK ! |
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 |
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 |
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 ! |
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 |
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 |
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 ! |
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 ! |
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 |
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 MAnd 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)
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)