1 / 29

Non Trivial FD

Non Trivial FD. Candidate Key. FD’s that Hold on S. 3NF. Consider R = {A, B, C, D, E, F, G, H} with a set of FDs F = {CD→A, EC→H, GHB→AB, C→D, EG→A, H→B, BE→CD, EC→B} The candidate keys are: {BEFG, CEFG, EFGH}. F = {CD→A, EC→H, GHB→AB, C→D, EG→A, H→B, BE→CD, EC→B}.

twyla
Télécharger la présentation

Non Trivial FD

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Non Trivial FD

  2. Candidate Key

  3. FD’s that Hold on S

  4. 3NF Consider R = {A, B, C, D, E, F, G, H} with a set of FDs F = {CD→A, EC→H, GHB→AB, C→D, EG→A, H→B, BE→CD, EC→B} The candidate keys are: {BEFG, CEFG, EFGH}

  5. F = {CD→A, EC→H, GHB→AB, C→D, EG→A, H→B, BE→CD, EC→B} No, R w.r.t. F is NOT in 3NF, because CD→A violates the 3NF requirements. i.e. • CD→A is not trivial FD • CD is not a superkey • CD is not a key, but A is not part of any key of R either

  6. Binary Decomposition Approach Considering R: Keys ={BEFG, CEFG, EFGH} F = {CD→A, EC→H, GHB→AB, C→D, EG→A, H→B, BE→CD, EC→B} Decomposition #1: CD→A is a violating FD R is decomposed into R1 and R2: R1 (A,C,D): We need to project FDs F onto relation R1: A+ = A C+ = CDA (C →DA) D+ = D AC+ = ACD (AC→D) AD+ = AD CD+ = CDA (CD → A) So, F1 = {C→DA, AC→D, CD→A}

  7. R2 ( B,C,D,E,F,G,H): In general, we should project F onto R2. However, if we look carefully, we can easily see that the only difference between R and R2 is attribute A. Attribute A has never appeared on LHS of any FD. So, removing it won’t make any change in F2. So, F2={EC→H, GHB→B, C→D, H→B, BE→CD, EC→B} FDs that are lost in Decomposition #1 are: Lost = {GHB→A, EG→A} Do we need further decomposition?

  8. Consider R1(A,C,D): F1 = {C→DA, AC→D, CD→A} Since C+=ACD, C is a key. C (in C→DA), CD (in CD→A), and AC (in AC→D) are key/super keys. Therefore, we have no violating FD. (So, we are done with this branch.) Consider R2: F2={EC→H, C→D, H→B, BE→CD, EC→B} Keys of R2 = Keys of R = {BEFG, CEFG, EFGH} EC→H is not a violating FD, since H is part of a key. C→D is a violating FD, since C is not a super key and D is not part of any key. So, further decomposition is needed.

  9. Decomposition #2: C→D is a violating FD R2 is decomposed into R21 and R22: R21 (C, D): We need to project F2 onto relation R21: C+=CD D+=D So, F21={C→D} R22 (B,C,E,F,G,H): In general, we should project F2 onto R22. However, if we look carefully, we can easily see that the only difference between R2 and R22 is attribute D. Attribute D has never appeared on LHS of any FD. So, removing it won’t make any change in F22. So, F22 = {EC→H, H→B, BE→C, EC→B}

  10. FDs that are lost in Decomposition #2 is: Lost = {BE→D} So, overall, we’ve lost the following FDs: Lost = {GHB→A, EG→A, BE→D} Do we need further decomposition? Consider R21(C,D): F21={C→D} Since C+=CD, C is a key. Therefore, we have no violating FD. (So, we are done with this branch.) Consider R22(B,C,E,F,G,H): Keys of R22 = Keys of R2 = {BEFG, CEFG, EFGH}

  11. F22 = {EC→H, H→B, BE→C, EC→B} EC→H is not a violating FD since H is part of a key. H→B is not a violating FD since B is part of a key. BE→C is not a violating FD since C is part of a key. EC→B is not a violating FD since B is part of a key. So, we are done with this branch. Overall, we have: R1 (A, C, D) F1 = {C→DA, AC→D, CD→A} R21 (C, D) F21 = {C→D} R22 (B, C, E, F, G, H) F22 = {EC→H, H→B, BE→C, EC→B}

  12. Since R1 includes R21 we might want to remove R21. This is a loss-less join decomposition, but it is not dependency preserving. To make the decomposition dependency preserving, we need to add the lost FDs as new relations. The lost FDs are: Lost = {GHB→AB, EG→A, BE→D} So, we add three relations: L1(A, B, G, H) FL1 = {GHB→AB} L2(A, E, G) FL2 = {EG→A} L3(B, D, E) FL3 = {BE→D}

  13. Synthesis Approach R = {A, B, C, D, E, F, G, H} with a set of FDs F = {CD→A, EC→H, GHB→AB, C→D, EG→A, H→B, BE→CD, EC→B} The candidate keys are {BEFG, CEFG, EFGH} Canonical cover for F is: FC = {C→AD, EC→H, GH→A, EG→A, H→B, BE→C}

  14. FC = {C→AD, EC→H, GH→A, EG→A, H→B, BE→C} Now, we create the relations: R1 = {A, C, D} F1 = {C→AD} R2 = {E, C, H} F2 = {EC→H} R3 = {A, G, H} F3 = {GH→A} R4 = {A, E, G} F4 = {EG→A} R5 = {B, H} F5 = {H→B} R6 = {B, C, E} F6 = {BE→C} Now, we need to check if at least one of the keys exists in the above relations. The candidate keys are {BEFG, CEFG, EFGH}

  15. Since none of these keys is in the relations, this decomposition is not lossless. So, we need to add an extra relation containing those attributes that form any key of R: R7 = {B, E, F, G} F7 = { }

  16. Check lossless join Check if the decomposition of R(A, B, C, D, E, F, G) with the set of FDs F={C→AD, E→G, FG→A, EF→A, G→B, BE→C} into the following relations is lossless join. R1 = {A, C, D} R2 = {E, C, G} R3 = {A, F, G} R4 = {A, E, F} R5 = {B, G} R6 = {B, C, E}

  17. Step 1- Table initialization

  18. Round 1

  19. Round 2

More Related