1 / 28

Logical Database Design (3 of 3)

Logical Database Design (3 of 3). John Ortiz. Normalization. If a relation is not in BCNF or 3NF, we refine it by decomposing it into two or more smaller relation schemas that are in the normal form. Decomposition has to be used carefully, since there are potential problems.

graiden-scott
Download Presentation

Logical Database Design (3 of 3)

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. Logical Database Design (3 of 3) John Ortiz

  2. Normalization • If a relation is not in BCNF or 3NF, we refine it by decomposing it into two or more smaller relation schemas that are in the normal form. • Decomposition has to be used carefully, since there are potential problems. • What are desirable properties of a decomposition, and how to test them? • How to obtain a decomposition with some desirable properties? Logical Database Design (2)

  3. Decomposition of a Relation • Let R be a relation schema. A decomposition of R, demoted by D = {R1, R2, ..., Rn}, is a set of relation schemas such that R = R1  ...  Rn. • If {R1, R2, ..., Rn} is a decomposition of R and r is an instance of R, then r  R1(r) R2(r) . . . Rn(r) • Information may be lost (i.e. wrong tuples may be added by the natural join) due to a decomposition. Logical Database Design (2)

  4. SC SR SG SR SG An Example of Information Loss • Before • After Logical Database Design (2)

  5. Lossless Join Decomposition • Let R be a relation schema, and D = {R1, R2, ..., Rn} be a decomposition of R. D is a lossless (non-additive) join decomposition of R if for every legal instance r of R, we have r = R1(r) R2(r) . . . Rn(r) • Theorem: Let F be a set of FDs over R, and D = {R1, R2} be a decomposition of R. D is a lossless-join decomposition if and only if • R1  R2  R1 - R2 is in F+; or • R1  R2  R2 - R1 is in F+. Logical Database Design (2)

  6. Lossless Join: An Example Consider F = {B  AH, L  CAt} over Bank-Loans(Bank, Assets, Headquarter, Loan#, Customer, Amount). Let D = {Banks(B,A,H), Loans(B,L,C,At)}. Since Banks  Loans = B  AH = Banks - Loans is in F+ (since it is already in F), D is a lossless-join decomposition. • What if the decomposition contains more than two relations. Logical Database Design (2)

  7. Test for Lossless Join * AlgorithmTestLJ (Chase) Input: A relation schema R(A1, …, Am), a set of FDs F, and a decomposition D = {R1, …, Rn}. Output: Yes, if D is a Lossless join; no, otherwise. Method: • Create an n  m table T (labeled by Ai and Rj). • If Ri contains Aj, place aj at Ti,j. Otherwise, place bij at Ti,j. Logical Database Design (2)

  8. TestLJ (cont.) * • Repeat for each FD X  Y in F do For all rows with identical symbols on X do make the symbols on Y identical. (choose aj over bij whenever possible) Until no more change can be made. • Return yes if there is a row of aj’s. Otherwise, return no. Logical Database Design (2)

  9. B A H L C At BAH a1 a2 a3 b14 b15 b16 BLCAt a1 b22 b23 a4 a5 a6 B A H L C At BAH a1 a2 a3 b14 b15 b16 BLCAt a1a2 a3 a4 a5 a6 TestLJ: An Example Continue with the previous example. • Set up the table T. • Enforce B  AH. • Need to repeat until no more changes. Logical Database Design (2)

  10. Dependency-Preserving Decomposition • Let F be a set of FDs over R, and D = {R1, R2, ..., Rn} be a decomposition of R. D is a dependency-preserving decomposition if F+ = (R1(F)  R2(F)  . . . Rn(F))+ where for i = 1, …, n Ri(F) = { X  Y | X  Y  F and XY  Ri}. • Restrict FDs to local relations. If all “global” FDs can be derived from “local” FDs, all dependencies are preserved. Logical Database Design (2)

  11. Dependency Preservation: An Example Consider F = {CS  Z, Z  C} over R(City, Street, Zipcode), and D ={R1(S, Z), R2(C, Z)}. Then R1(F) = {} and R2(F) = {Z  C} (consider non-trivial FDs only) Since CS  Z  F+ but CS  Z  (R1(F)  R2(F))+, D is not dependency-preserving. Logical Database Design (2)

  12. Test for Dependency Preservation Algorithm TestDP Input: A relation schema R, A set of FDs F over R, a decomposition D= {R1, R2, ..., Rn} of R. Output: Yes, if D is dependency-preserving; no, otherwise. Method: for every X  Y  F if  Ri such that XY  Ri then X  Y is preserved; Logical Database Design (2)

  13. TestDP (cont.) else W := X; repeat for i from 1 to n do W := W  ((W  Ri)+ Ri); until there is no change to W; if Y  W then X  Y is preserved; if every X  Y is preserved then return yes; else return no. • Derive global FDs using only local FDs. Logical Database Design (2)

  14. TestDP: An example Consider F = {A  B, B  C, C  D, D  A } over R(A, B, C, D), & D = {R1(A,B), R2(B,C), R3(C,D)}. Is D a dependency-preserving decomposition? Since AB  R1, A  B is preserved. Since BC  R2, B  C is preserved. Since CD  R3, C  D is preserved. Since DA is not in any one of the three relations, we need to compute W. Logical Database Design (2)

  15. TestDP: An example (cont.) * Initialization: W = D; first iteration: W = D  ((D  AB)+ AB) = D; W = D  ((D  BC)+ BC) = D; W = D  ((D  CD)+ CD) = D  (D+ CD) = D  (ABCD  CD) = CD; • W changed from D to CD. Logical Database Design (2)

  16. TestDP: An example (cont.) * second iteration: W = CD  ((CD  AB)+ AB) = CD; W = CD  ((CD  BC)+ BC) = CD  (C+ BC) = BCD; W = BCD  ((BCD  CD)+ CD) = BCD; • W changed from CD to BCD. Logical Database Design (2)

  17. Normalization • It is good to have BCNF relation schemas. • If a relation schema is not in BCNF, then decompose it into a set of relation schemas: • every new schema is in BCNF; • it is lossless-join (can guarantee); • it is dependency-preserving (no guarantee). • If not possible to have all nice properties, be happy with a lossless join, dependency preserving 3NF decomposition (can guarantee) Logical Database Design (2)

  18. Normalization to BCNF Algorithm LLJD-BCNF Input: R: A relation schema F: A set of FDs satisfied by R. Output: A lossless-join decomposition D = {R1, …, Rn}, such that each Ri is in BCNF. Logical Database Design (2)

  19. Normalization to BCNF (cont.) Method: D := {R}; while  RiDthat is not in BCNF do begin Find an FD X Y such that (1) Ri is not BCNF because of X Y, and (2) XY  Ri; D:= D - Ri  {Ri - Y, XY} end; Logical Database Design (2)

  20. Normalization to BCNF (cont.) * Theorem: Algorithm LLJD-BCNF is correct. Proof (sketch): • Every schema in D is in BCNF because the algorithm will not stop otherwise. • D is a lossless-join decomposition because in each iteration, Ri is decomposed into 2 smaller schemas (Ri - Y) and XY and they satisfy the condition: (Ri - Y)  XY = X  Y = (XY - (Ri - Y)). Logical Database Design (2)

  21. Normalization to BCNF: An Example Consider F = {B  AH, L  CAt} over Bank-Loans(Bank, Assets, Headquarter, Loan#, Customer, Amount), and a set of FDs, Candidate keys: LB Initialization: D = {BAHLCAt } Logical Database Design (2)

  22. Normalize to BCNF: An Example * 1st iteration: • Ri = BAHLCAt is not in BCNF because B  AH is not a trivial FD and B is not a superkey. • Replace BAHLCAt by BAH and BLCAt. Hence: D = {BAH, BLCAt}. BAH is in BCNF because in BAH, B is a candidate key. Logical Database Design (2)

  23. Normalize to BCNF: An Example * 2nd iteration: • Ri = BLCAt is not in BCNF because L  CAt is not a trivial FD and L is not a superkey in BLCAt. • Replace BLCAt by CLAt and BL. Hence, D= {BAH, CLAt, BL}. CLAt is in BCNF because in CLAt, L is a candidate key. BL is in BCNF (see theorem on next page). Final result: D = {BAH, CLAt, BL}. • Dhappens to be dependency-preserving. • Any relation schema with exactly two attributes is in BCNF. Logical Database Design (2)

  24. Normalize to BCNF: Another Ex. * Consider R(City, Street, Zipcode), and F = {CS  Z, Z  C }. Candidate keys: CS, ZS. Initialization: D = {CSZ}; 1st iteration: • R = CSZ is not in BCNF because Z  C is not a trivial FD and Z is not a superkey. • D = {ZC, ZS}. D is not dependency-preserving because CS  Z is not preserved. Logical Database Design (2)

  25. Equivalence of FD Sets Let F and G be two sets of FDs satisfied by R. F and G are equivalent, denoted by F  G, if F+ = G+. Example: F = {B  CD, AD  E, B  A} and G = {B  CDE, B  ABC, AD  E} are equivalent. • Check to see that every FD in F is also in G+ and that every FD in G is also in F+ Logical Database Design (2)

  26. Extraneous Attributes Let F be a set of FDs. F contains an extraneous attribute A if there is an FD X  Y in F, such that • either A  X, and [F - {X  Y}  {X - {A}  Y}]  F; • or A  Y, and [F - {X  Y}  {X  Y - {A}}]  F. • This is a “useless” attribute either at the left side or at the right side of an FD. Logical Database Design (2)

  27. Summary • A good schema should have three properties: • BCNF (or 3NF if BCNF can not be obtained) • Lossless join • Dependency preserving • Lossless join BCNF decomposition is guaranteed, need to check for dependency preservation • Lossless join, dependency preserving 3NF decomposition is guaranteed (need to find the minimal cover) Logical Database Design (2)

  28. Look Ahead • Next topic: SQL Overview & DDL • Read textbook: • Chapter 8, 10.1-10.6 Logical Database Design (2)

More Related