1 / 6

Classroom Exercise: Normalization

Classroom Exercise: Normalization. Consider the relation R(A,B,C,D) with these given FDs: 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.

xaviere
Download Presentation

Classroom Exercise: Normalization

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. Classroom Exercise: Normalization • Consider the relation R(A,B,C,D) with these given FDs: • 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 3NF violations (before decomposing into BCNF)? • Decompose R into 3NF.

  2. Consider the relation R(A,B,C,D) with these given FDs: • 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 -> D; BC -> A; BD -> A; CD -> A; ABC -> D; ABD -> C; BCD -> A

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

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

  5. Decompose R1(C,D,A) with FD's C -> D; C -> A; D -> A and key C • Start with D -> A: • Compute D+ = DA • new relation R3(D,A) with FD D -> A and key D. Is in BCNF. • new relation R4(C,D) with FD C -> D and key C. Is in BCNF. • Final set of BCNF relation schemas: • R2(B,C), R3(D,A), and R4(C,D)

  6. What about decomposing into 3NF? • Original relation is R(A,B,C,D) • We already discovered the FD's: • 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 3NF violations (LHS does not contain a key AND RHS is not part of a key)? • None! Every attribute is part of a key, so no FD violates FD. Thus no decomposition is necessary.

More Related