Normalization

1. Normalization

2. To compute the closure of a set of functional dependencies F: F + = Frepeat for each functional dependency f in F+ apply reflexivity and augmentation rules on fadd the resulting functional dependencies to F +for each pair of functional dependencies f1and f2 in F + if f1 and f2 can be combined using transitivity then add the resulting functional dependency to F +until F + does not change any further NOTE: We shall see an alternative procedure for this task later Procedure for Computing F+

3. Example on Computing F+ • •  F = {A → B, B → C, C D → E } • •  Step 1: For each f in F, apply reflexivity rule • –  We get: CD → C; CD → D • –  Add them to F: • •  F = {A → B, B → C, C D → E; CD → C; CD → D } • •  Step 2: For each f in F, apply augmentation rule • –  From A → B we get: A → AB; AB → B; AC → BC; AD • → BD; ABC →BC; ABD → BD; ACD →BCD • –  From B → C we get: AB → AC; BC → C; BD → CD; • ABC → AC; ABD → ACD, etc etc. • •  Step 3: Apply transitivity on pairs of f’s • •  Keep repeating… You get the idea

4. Reasoning About FDs • Example: • Contracts(contract_id, supplier, project, dept, part, qty, value) • and: • –  C is the key: C → CSJDPQV • –  Project purchases each part using single contract: JP → C • –  Dept purchases at most one part from a supplier: SD → P • •  JP → C, C → CSJDPQV imply JP → CSJDPQV • •  SD → P implies SDJ → JP • •  SDJ → JP, JP → CSJDPQV imply SDJ → CSJDPQV

5. Reasoning About FDs • •  Computing the closure of a set of FDs can be expensive. • (Size of closure is exponential in # of attrs!) • •  Typically, we just want to check if a given FD X →Y is in • the closure of a set of FDs F. An efficient check: • –  Compute attribute closure of X (denoted X+) wrt F: • •  Set of all attributes Z such that X → Z is in F+ • •  There is a linear time algorithm to compute this. • –  Check if Y is in X+ • •  Does F = {A → B, B → C, C D → E } imply A → E? • –  i.e, is A → E in the closure F+? Equivalently, is E in A+?

6. Closure of Attribute Sets • Closure of a set of attributes X with respect to F is the set X+ of all attributes that are functionally determined by X • X+ can be calculated by repeatedly applying IR1, IR2, IR3 using the FDs in F

7. Closure of Attribute Sets • Given a set of attributes a, define the closureof aunderF (denoted by a+) as the set of attributes that are functionally determined by a under F • Algorithm to compute a+, the closure of a under F result := a;while (changes to result) do for each in F do begin if  result then result := result end

8. Example of Attribute Set Closure • R = (A, B, C, G, H, I) • F = {A B, A C, CG H, CG I, B H} • (AG)+ 1. result = AG 2. result = ABCG (A C and A  B) 3. result = ABCGH (CG H and CG  AGBC) 4. result = ABCGHI (CG I and CG  AGBCH) • Is AG a candidate key? • Is AG a super key? • Does AG R? == Is (AG)+  R • Is any subset of AG a superkey? • Does AR? == Is (A)+  R • Does GR? == Is (G)+  R

9. There are several uses of the attribute closure algorithm: Testing for superkey: To test if  is a superkey, we compute +, and check if + contains all attributes of R. Testing functional dependencies To check if a functional dependency    holds (or, in other words, is in F+), just check if   +. That is, we compute + by using attribute closure, and then check if it contains . Is a simple and cheap test, and very useful Computing closure of F For each   R, we find the closure +, and for each S  +, we output a functional dependency   S. Uses of Attribute Closure

11. Computing F+ •  Given F={ A → B, B → C}. Compute F+ (with attributes A, B, C). We’ll do an example on A+. Step 1: Result = A Step 2: Consider A → B, Result = A ∪ B = AB Consider B → C, Result = AB ∪ C = ABC Step 3: A+ = {ABC}

15. Equivalence of Sets of FDs • Two sets of FDs F and G are equivalent if: - every FD in F can be inferred from G, & - every FD in G can be inferred from F • Hence, F and G are equivalent if F+=G+ Definition: F covers G if every FD in G can be inferred from F (i.e., if G+F+) • F and G are equivalent if F covers G and G covers F • There is an algorithm for checking equivalence of sets of FDs

16. Canonical Cover • Sets of functional dependencies may have redundant dependencies that can be inferred from the others • For example: A  C is redundant in: {AB, BC} • Parts of a functional dependency may be redundant • E.g.: on RHS: {AB, BC, ACD} can be simplified to {A B, BC, AD} • E.g.: on LHS: {A B, BC, ACD} can be simplified to {A B, BC, AD} • Intuitively, a canonical cover of F is a “minimal” set of functional dependencies equivalent to F, having no redundant dependencies or redundant parts of dependencies

17. Extraneous Attributes • Consider a set F of functional dependencies and the functional dependency   in F. • Attribute A is extraneous in  if A   and F logically implies (F – {})  {( – A) }. • Attribute A is extraneous in  if A  and the set of functional dependencies (F – {})  {(– A)} logically implies F. • Note: implication in the opposite direction is trivial in each of the cases above, since a “stronger” functional dependency always implies a weaker one • Example: Given F = {AC, ABC } • B is extraneous in AB C because {AC, AB C} logically implies AC (I.e. the result of dropping B from AB C). • Example: Given F = {AC, ABCD} • C is extraneous in ABCD since AB C can be inferred even after deleting C

18. Testing if an Attribute is Extraneous • Consider a set F of functional dependencies and the functional dependency   in F. • To test if attribute A   is extraneousin • compute ({} – A)+ using the dependencies in F • check that ({} – A)+ contains ; if it does, A is extraneous • To test if attribute A  is extraneous in  • compute + using only the dependencies in F’ = (F – {})  {(– A)}, • check that + contains A; if it does, A is extraneous

19. Canonical Cover • A canonical coverfor F is a set of dependencies Fc such that • F logically implies all dependencies in Fc, and • Fclogically implies all dependencies in F, and • No functional dependency in Fc contains an extraneous attribute, and • Each left side of functional dependency in Fcis unique. • To compute a canonical cover for F:repeatUse the union rule to replace any dependencies in F11 and 12 with 112 Find a functional dependency  with an extraneous attribute either in  or in  If an extraneous attribute is found, delete it from until F does not change • Note: Union rule may become applicable after some extraneous attributes have been deleted, so it has to be re-applied

20. Computing Canonical Cover • R = (A, B, C)F = {A BC, B C, A B, ABC} • Combine A BC and A B into A BC • Set is now {A BC, B C, ABC} • A is extraneous in ABC • Check if the result of deleting A from ABC is implied by the other dependencies • Yes: in fact, BC is already present! • Set is now {A BC, B C} • C is extraneous in ABC • Check if A C is logically implied by A B and the other dependencies • Yes: using transitivity on A B and B  C. • Can use attribute closure of A in more complex cases • The canonical cover is: A B, B C

21. Decomposition 1. Decomposing the schema R = ( bname, bcity, assets, cname, lno, amt) R = R1 U R2 R1 = (cname, lno, amt) R1 = (bname, bcity, assets, cname) 2. Decomposing the instance

22. Goals of Decomposition 1. Lossless Joins Want to be able to reconstruct big (e.g. universal) relation by joining smaller ones (using natural joins) (i.e. R1 R2 = R) 2. Dependency preservation Want to minimize the cost of global integrity constraints based on FD’s ( i.e. avoid big joins in assertions) 3. Redundancy Avoidance Avoid unnecessary data duplication (the motivation for decomposition) Why important? LJ : information loss DP: efficiency (time) RA: efficiency (space), update anomalies

23. Lossy Decomposition Spurious Tuples JOIN

24. Dependency Goal #1: lossless joins A bad decomposition: = Problem: join adds meaningless tuples “lossy join”: by adding noise, have lost meaningful information as a result of the decomposition

25. Dependency Goal #1: lossless joins Is the following decomposition lossless or lossy? Ans: Lossless: R = R1 R2, it has 4 tuples

26. Ensuring Lossless Joins A decomposition of R : R = R1 U R2 Is lossless iff R1  R2  R1, or R1  R2  R2 (i.e., intersecting attributes must for a superkey for one of the resulting smaller relations)

27. 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

28. Lossy Decomposition

29. 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

30. 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

31. 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

32. 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

33. 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

34. 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.

35. 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

36. 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)

37. 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

38. 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

39. 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

40. 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.

41. 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

42. 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.

43. 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

44. 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

45. 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)

46. 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

47. 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.

48. 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)

49. 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.