Classroom Exercise Normalization u Consider the relation R A

Classroom Exercise: Normalization u Consider the relation R(A, B, C, D) with these given FDs: w w w u u u u AB -> C C -> D D -> A Compute all nontrivial FDs that follow from these. Compute the key(s) for R. What are all the superkeys? What are all the BCNF violations? Decompose R into BCNF. What are all the 3 NF violations (before decomposing into BCNF)? Decompose R into 3 NF. 1

u u Consider the relation R(A, B, C, D) with these given FDs: w AB -> C C -> D D -> A Compute all nontrivial FDs that follow from these. 1. For each set of attributes X, compute X+: A+ = A; B+ = B; C+ = CDA; D+ = DA; AB+ = ABCD; AC+ = ACD; AD+ = AD; BC+ = BCDA; BD+ = BDAC; CD+ = CDA; ABC+ = ABCD; ABD+ = ABCD; ACD+ = ACD; BCD+ = BCDA 2. New FDs are of form X -> A for all A in X+; drop trivial ones A -> A; B -> B; C -> CDA; D -> DA; AB -> ABCD; AC -> ACD; AD -> AD; BC -> BCDA; BD -> BDA; CD -> CDA; ABC -> ABCD; ABD -> ABCD; ACD -> ACD; BCD -> BCDA 3. Drop redundant ones (if we have X -> A, don't need XY -> A): C -> D; C -> A; D -> A, AB -> C; AB -> D; AC -> D; BC -> A; BD -> A; CD -> A; ABC -> D; ABD -> C; BCD -> A 2

u u So the relation R(A, B, C, D) has these FDs: 1. C -> D (given originally) 2. C -> A 3. D -> A (given originally) 4. AB -> C (given originally) 5. AB -> D Compute the key(s) for R. w three keys: AB, BC, and BD What are all the superkeys? w every superset of a key: ABC, ABD, BCD, ABCD What are all the BCNF violations? w any FD whose LHS does not contain a key: C -> D; C -> A; D -> A 3

u u Decompose R into BCNF. 1. 2. 3. 4. 5. Start with violating FD X -> A. Compute X+. New relations are R 1 with attributes X+ and R 2 with attributes R - X+ U X. Project R's FD's onto R 1 and R 2. Check if need to decompose some more. Let's start with C -> D. w w w Compute C+ = CDA. New relation R 1(C, D, A): • • • FD's for R 1: C -> D, C -> A, D -> A key for R 1: C D -> A violates BCNF, need to decompose New relation R 2(B, C): • • • FD's for R 2: none key for R 2: BC in BCNF 4

u. Decompose R 1(C, D, A) with FD's C -> D; C -> A; D -> A and key C u. Start with D -> A: w Compute D+ = DA w new relation R 3(D, A) with FD D -> A and key D. Is in BCNF. w new relation R 4(C, D) with FD C -> D and key C. Is in BCNF. u. Final set of BCNF relation schemas: w R 2(B, C), R 3(D, A), and R 4(C, D) 5

u What about decomposing into 3 NF? u Original relation is R(A, B, C, D) u We already discovered the FD's: u u 1. 2. 3. 4. 5. C -> D (given originally) C -> A D -> A (given originally) AB -> C (given originally) AB -> D We already discovered the keys: AB, BC, BD What are the 3 NF violations (LHS does not contain a key AND RHS is not part of a key)? w None! Every attribute is part of a key, so no FD violates FD. Thus no decomposition is necessary. 6