Normalization

1. Normalization

2. Lossless Decomposition Theorem A decomposition of R into R1 and R2 is lossless join wrt FDs F, if and only if at least one of the following dependencies is in F+: • R1  R2 R1 • R1  R2 R2 In other words, R1  R2 forms a superkey of either R1 or R2

3. Lossy Decomposition

4. Lossless Decomposition • Observe that S satisfies the FDs: • S#  Status & S#  City • It can not be a coincidence that S is equal to the join of its projections on {S#, Status} & {S#, City} • Heaths’ Theorem: Let R{A,B,C} be a relation, where A, B, & C are sets of attributes. If R satisfies AB & AC, then R is equal to the join of its projections on {A,B} & {A,C} • Observe that in the second decomposition of S the FD, S#  City is lost

5. Lossless Decomposition • The decomposition of R into R1, R2, …Rn is lossless if for any instance r of R r = R1(r ) R2(r ) …… Rn(r ) • We can replace R by R1 & R2, knowing that the instance of R can be recovered from the instances of R1 & R2 • We can use FDs to show that decompositions are lossless

6. Decomposition Goal #2: Dependency preservation Goal: efficient integrity checks of FD’s An example w/ no DP: R = ( bname, bcity, assets, cname, lno, amt) bname  bcity assets lno  amt bname Decomposition: R = R1 U R2 R1 = (bname, assets, cname, lno) R2 = (lno, bcity, amt) Lossless but not DP. Why? Ans: bname bcity assets crosses 2 tables

7. Decomposition Goal #2: Dependency preservation To ensure best possible efficiency of FD checks ensure that only a SINGLE table is needed in order to check each FD i.e. ensure that: A1 A2 ... An  B1 B2 ... Bm Can be checked by examining Ri = ( ..., A1, A2, ..., An, ..., B1, ..., Bm, ...) To test if the decomposition R = R1 U R2 U ... U Rn is DP (1) see which FD’s of R are covered by R1, R2, ..., Rn (2) compare the closure of (1) with the closure of FD’s of R

8. Decomposition Goal #2: Dependency preservation Example: Given F = { AB, AB D, C D} consider R = R1 U R2 s.t. R1 = (A, B, D) , R2 = (C, D) (1) F+ = { ABD, CD}+ (2) G = {ABD, CD, ...} + (3) F+ = G+ note: G+ cannot introduce new FDs not in F+ Decomposition is DP

9. Dependency Preservation • Let Fibe the set of dependencies F + that include only attributes in Ri. • A decomposition is dependency preserving, if (F1 F2  …  Fn )+ = F + • If it is not, then checking updates for violation of functional dependencies may require computing joins, which is expensive.

10. In Layman’s Term • Each Functional Dependency specified in F either appears directly in one of the relations in the decomposition.

11. Continue… • It is not necessary that all dependencies from the relation R appear in some relation Ri. It is sufficient that the union of the dependencies on all the relations Ri be equivalent to the dependencies on R.

12. Property of Dependency-Preservation • If a decomposition is not dependency-preserving, therefore, that dependency is lost in the decomposition. FD4 FD3 FD1 FD2

13. Example of Dependency Preservation • R(A B C D) • FD1: A  B • FD2: B  C • FD3: C  D • Decomposition:R1(A B C) R2(C D)

14. FD1: A  B • FD2: B  C • FD3: C  D R1( A B C ) FD1 FD2

15. FD1: A  B • FD2: B  C • FD3: C  D R2( C D ) FD3

16. FD1: A  B • FD2: B  C • FD3: C  D R1( A B C ) R2( C D ) FD1 FD3 FD2 Has all 3 functional dependencies! Therefore, it’s preserving the dependencies

17. Example of Non-Dependency Preservation • R(A B C D) • FD1: A  B • FD2: B  C • FD3: C  D • Decomposition:R1(A C D) R2(B C)

18. FD1: A  B • FD2: B  C • FD3: C  D R1( A C D ) FD3

19. FD1: A  B • FD2: B  C • FD3: C  D R2( B C ) FD2

20. FD1: A  B • FD2: B  C • FD3: C  D R1( A C D ) R2( B C ) FD2 FD3 Does not support FD1: A => B Therefore, it does not preserve the dependencies

21. More Example • R(A B C D E) • FD1: A  B • FD2: BC  D • Decomposition:R1(A C E) R2(B C D) R3(A B)

22. FD1: A  B • FD2: BC  D R1( A C E ) No Dependencies

23. FD1: A  B • FD2: BC  D R2( B C D ) FD2

24. FD1: A  B • FD2: BC  D R3( A B ) FD1

25. FD1: A  B • FD2: BC  D R1( A C E ) R2( B C D ) FD2 R3( A B ) FD1 Has all 2 functional dependencies! Therefore, it’s preserving the dependencies

26. R(A, B, C, D, E, F ) • FD1: DA, B • FD2: C  E, F • Decomposition:R1( A, C, D )R2( A, D, B ) R3( D, E, F )R4( C, E, F )

27. FD1: DA, B FD2: C  E, F R1( A C D )R2( A D B )R3( D E F ) R4( C E F ) FD1 FD2

28. Answer • Yes! This is a dependency-preservation

29. To check if a dependency    is preserved in a decomposition of R into R1, R2, …, Rn we apply the following test (with attribute closure done with respect to F) result = while (changes to result) dofor eachRiin the decompositiont = (result  Ri)+  Riresult = result  t If result contains all attributes in , then the functional dependency    is preserved. We apply the test on all dependencies in F to check if a decomposition is dependency preserving This procedure takes polynomial time, instead of the exponential time required to compute F+and(F1 F2  …  Fn)+ Testing for Dependency Preservation

30. Example • R = (A, B, C)F = {A B, B C) • Can be decomposed in two different ways • R1 = (A, B), R2 = (B, C) • Lossless-join decomposition: R1  R2 = {B}and B BC • Dependency preserving • R1 = (A, B), R2 = (A, C) • Lossless-join decomposition: R1  R2 = {A}and A  AB • Not dependency preserving (cannot check B C without computing R1 R2)

31. Decomposition Goal #3: Redudancy Avoidance Example: Redundancy for B=x , y and z (1) An FD that exists in the above relation is: B  C (2) A superkey in the above relation is A, (or any set containing A) When do you have redundancy? Ans: when there is some FD, XY covered by a relation and X is not a superkey

32. Problems with Decompositions There are three potential problems to consider: • Some queries become more expensive • e.g., What is the price of prop# 1? • Given instances of the decomposed relations, we may not be able to reconstruct the corresponding instance of the original relation! • Fortunately, not in the PLOTS example • Checking some dependencies may require joining the instances of the decomposed relations. • Fortunately, not in the PLOTS example Tradeoff:Must consider these issues vs. redundancy

33. Example • R = (A, B, C )F = {AB B  C}Key = {A} • R is not in BCNF (B  C but B is not superkey) • Decomposition R1 = (A, B), R2 = (B, C) • R1and R2 in BCNF • Lossless-join decomposition • Dependency preserving

34. Testing for BCNF • To check if a non-trivial dependency causes a violation of BCNF 1. compute + (the attribute closure of ), and 2. verify that it includes all attributes of R, that is, it is a superkey of R. • Simplified test: To check if a relation schema R is in BCNF, it suffices to check only the dependencies in the given set F for violation of BCNF, rather than checking all dependencies in F+. • If none of the dependencies in F causes a violation of BCNF, then none of the dependencies in F+ will cause a violation of BCNF either. • However, simplified test using only F isincorrectwhen testing a relation in a decomposition of R • Consider R = (A, B, C, D, E), with F = { A  B, BC  D} • Decompose R into R1 =(A,B) and R2 =(A,C,D, E) • Neither of the dependencies in F contain only attributes from (A,C,D,E) so we might be mislead into thinking R2 satisfies BCNF. • In fact, dependency ACD in F+ shows R2 is not in BCNF.

35. BCNF and Dependency Preservation It is not always possible to get a BCNF decomposition that is dependency preserving • R = (J, K, L )F = {JK L L K }Two candidate keys = JK and JL • R is not in BCNF • Any decomposition of R will fail to preserve JK L This implies that testing for JK L requires a join

36. Third Normal Form: Motivation • There are some situations where • BCNF is not dependency preserving, and • efficient checking for FD violation on updates is important • Solution: define a weaker normal form, called Third Normal Form (3NF) • Allows some redundancy (with resultant problems; we will see examples later) • But functional dependencies can be checked on individual relations without computing a join. • There is always a lossless-join, dependency-preserving decomposition into 3NF.

37. Redundancy in 3NF • There is some redundancy in this schema • Example of problems due to redundancy in 3NF • R = (J, K, L)F = {JK L, L K } J L K j1 j2 j3 null l1 l1 l1 l2 k1 k1 k1 k2 • repetition of information (e.g., the relationship l1, k1) • (i_ID, dept_name) • need to use null values (e.g., to represent the relationshipl2, k2 where there is no corresponding value for J). • (i_ID, dept_nameI) if there is no separate relation mapping instructors to departments

38. Testing for 3NF • Optimization: Need to check only FDs in F, need not check all FDs in F+. • Use attribute closure to check for each dependency   , if  is a superkey. • If  is not a superkey, we have to verify if each attribute in  is contained in a candidate key of R • this test is rather more expensive, since it involve finding candidate keys • testing for 3NF has been shown to be NP-hard • Interestingly, decomposition into third normal form (described shortly) can be done in polynomial time

39. 3NF Decomposition Algorithm Let Fcbe a canonical cover for F;i := 0;for each functional dependency in Fcdo if none of the schemas Rj, 1  j  i contains then begini := i + 1;Ri := endif none of the schemas Rj, 1  j  i contains a candidate key for Rthen begini := i + 1;Ri := any candidate key for R;end /* Optionally, remove redundant relations */ repeatif any schema Rj is contained in another schema Rkthen /* delete Rj */Rj = R;; i=i-1;return (R1, R2, ..., Ri)

40. Testing Decomposition for BCNF • To check if a relation Ri in a decomposition of R is in BCNF, • Either test Ri for BCNF with respect to the restriction of F to Ri (that is, all FDs in F+ that contain only attributes from Ri) • or use the original set of dependencies F that hold on R, but with the following test: • for every set of attributes   Ri, check that + (the attribute closure of ) either includes no attribute of Ri- , or includes all attributes of Ri. • If the condition is violated by some   in F, the dependency (+ - )  Rican be shown to hold on Ri, and Ri violates BCNF. • We use above dependency to decompose Ri

41. BCNF Decomposition Algorithm result := {R };done := false;compute F +;while (not done) do if (there is a schema Riin result that is not in BCNF)then beginlet   be a nontrivial functional dependency that holds on Risuch that  Riis not in F +, and   = ;result := (result – Ri )  (Ri – )  (,  );end else done := true; Note: each Riis in BCNF, and decomposition is lossless-join.

42. Example of BCNF Decomposition • class (course_id, title, dept_name, credits, sec_id, semester, year, building, room_number, capacity, time_slot_id) • Functional dependencies: • course_id→ title, dept_name, credits • building, room_number→capacity • course_id, sec_id, semester, year→building, room_number, time_slot_id • A candidate key {course_id, sec_id, semester, year}. • BCNF Decomposition: • course_id→ title, dept_name, credits holds • but course_id is not a superkey. • We replace class by: • course(course_id, title, dept_name, credits) • class-1 (course_id, sec_id, semester, year, building, room_number, capacity, time_slot_id)

43. BCNF Decomposition (Cont.) • course is in BCNF • How do we know this? • building, room_number→capacity holds on class-1 • but {building, room_number} is not a superkey for class-1. • We replace class-1 by: • classroom (building, room_number, capacity) • section (course_id, sec_id, semester, year, building, room_number, time_slot_id) • classroom and section are in BCNF.