1 / 66

Chapter 7: Relational Database Design

Chapter 7: Relational Database Design. First Normal Form Functional Dependencies Decomposition Boyce-Codd Normal Form Third Normal Form. First Normal Form. R is in first normal form if the domains of all attributes of R are atomic. Pitfalls in Relational Database Design. Redundancy

patd
Download Presentation

Chapter 7: Relational Database Design

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. Chapter 7: Relational Database Design • First Normal Form • Functional Dependencies • Decomposition • Boyce-Codd Normal Form • Third Normal Form

  2. First Normal Form • R is in first normal form if the domains of all attributes of R are atomic

  3. Pitfalls in Relational Database Design • Redundancy • Inability to represent certain information • E.g. relationships among attributes

  4. Example • Consider the relation schema:Lending-schema = (branch-name, branch-city, assets, customer-name, loan-number, amount) • Redundancy • Null values • Cannot store information about a branch if no loans exist • Can use null values, but they are difficult to handle.

  5. Why Redundancy Is Bad? • Wastes space • Complicates updating, introducing possibility of inconsistency of assets value

  6. Decomposition • Decompose the relation schema Lending-schema into: Branch-schema = (branch-name, branch-city,assets) Loan-info-schema = (customer-name, loan-number, branch-name, amount)

  7. Lossless-join decomposition • All attributes of an original schema (R) must appear in the decomposition (R1, R2): R = R1  R2 • For all possible relations r on schema R r = R1 (r) R2 (r)

  8. Non Lossless-Join Decomposition • Decomposition of R = (A, B) • R1 = (A) R2 = (B) A B A B    1 2 1   1 2 B(r) A(r) r A B A (r) B (r) We do not loss any tuple but we lose the relationship between A and B     1 2 1 2

  9. Relational DB Design Process • Decide whether a particular relation R is in “good” form. • In the case that a relation R is not in “good” form, decompose it into a set of relations {R1, R2, ..., Rn} such that • each relation is in good form • the decomposition is a lossless-join decomposition • Our theory is based on: • functional dependencies • multivalued dependencies

  10. Functional Dependencies • Constraints on the set of legal relations. • Require that the value for a certain set of attributes determines uniquely the value for another set of attributes. • A functional dependency is a generalization of the notion of a key.

  11. Functional Dependencies • Let R be a relation schema   R and   R • The functional dependency  holds onR if and only if for any legal relations r(R), whenever any two tuples t1and t2 of r agree on the attributes , they also agree on the attributes . That is, t1[] = t2 []  t1[ ] = t2 [ ]

  12. Example • Example: Consider r(A,B) with the following instance of r. • On this instance, AB does NOT hold, but BA does hold. • Note: function dependency needs to hold for any possible instance of a relation. • 4 • 1 5 • 3 7

  13. More Example • Consider the schema: Loan-info-schema = (customer-name, loan-number, branch-name, amount). We expect this set of functional dependencies to hold: loan-numberamount loan-number  branch-name but would not expect the following to hold: loan-number customer-name

  14. Keys Defined by Functional Dependencies • K is a superkey for relation schema R if and only if K R • K is a candidate key for R if and only if • K R, and • for no   K,  R

  15. Functional Dependencies • A functional dependency is trivial if it is satisfied by all instances of a relation • E.g. • customer-name, loan-number customer-name • customer-name customer-name • In general,   is trivial if   

  16. 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:ais in F+   a+

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

  18. Example of Attribute Set Closure • R = (A, B, C, G, H, I) • F = {A BA C CG HCG IB 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)

  19. Testing for superkey and candidate key: Testing functional dependencies Computing closure of F Uses of Attribute Closure

  20. Testing for Keys • To test if  is a superkey, we compute +, and check if +contains all attributes of R. • To test if  is a candidate key, first make sure  is a superkey. Then make sure no subset of  is a superkey • In the previous example, 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

  21. Testing Functional Dependencies • To check if a functional dependency    holds (or, in other words, is in F+), just check if   +.

  22. Computing Closure of F • But what is a closure of F? • Given a set F set of functional dependencies, there are certain other functional dependencies that are logically implied by F. • E.g. If A  B and B  C, then we can infer that A  C • The set of all functional dependencies logically implied by F is the closure of F. • We denote the closure of F by F+.

  23. Armstrong’s Axioms • Sound and Complete rules: • if   , then   (reflexivity) • if  , then    (augmentation) • if  , and   , then   (transitivity) • These rules are • sound (generate only functional dependencies that actually hold) and • complete (generate all functional dependencies that hold).

  24. Example • R = (A, B, C, G, H, I)F = { A B, A C, CG H, CG I, B H } • some members of F+ • A H • by transitivity from A B and B H • AG I • by augmenting A C with G, to get AG CG and then transitivity with CG I • CG HI • from CG H and CG I : “union rule” can be inferred from • definition of functional dependencies, or • Augmentation of CG I to infer CG  CGI, augmentation ofCG H to inferCGI HI, and then transitivity

  25. Closure of Functional Dependencies • Derived rules from Armstrong’s axioms • If   holds and  holds, then    holds (union) • If    holds, then   holds and  holds (decomposition) • If   holds and   holds, then   holds (pseudotransitivity)

  26. When we compute the closure of attribute set , we already implicitly used Armstrong’s axioms • Let us just use the closure of attribute set to calculate the closure of functional dependency set

  27. Now Compute Closure of F • For each   R, we find the closure +, and for each S  +, we output a functional dependency   S.

  28. We just talked about the maximal set of functional dependencies that can be derived from a functional dependent set F • Closure of F • What about the minimal set of functional dependencies that is equivalent functional dependent set F • Canonical cover of F

  29. Canonical Cover • 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 • What does “equivalent” mean? • What does “minimal” mean?

  30. Equivalent Means: • Give two functional dependency sets F and F’, they are equivalent if and only if: • F logically implies all functional dependencies in F’ • F’ logically implies all functional dependencies in F • We use to represent logically imply and to represent equivalent • How to test if F F’? • For each  F’, test if it holds in F • You may need to calculate a+ in F

  31. Minimal Means: • No redundant functional dependencies! • Sets of functional dependencies may have redundant dependencies that can be inferred from the others • Eg: A  C is redundant in: {A  B, B  C, A  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}

  32. Extraneous Attributes • For any   in F • Attribute A   is extraneous ifF (F – {})  {( – A) }. • Attribute A  is extraneous if F (F – {})  { (– A)} • Use attribute closures to check equivalence

  33. Examples • Given F = {AC, ABC } • B is extraneous in AB C because {AC, AB C} is equivalent to {AC, AC } = {AC} • Given F = {AC, ABCD} • C is extraneous in ABCD because {AC, ABCD} is equivalent to {AC, ABD}

  34. 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 Fccontains an extraneous attribute, and • Each left side of functional dependency in Fcis unique.

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

  36. Example • 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} • Is B extraneous in ABC • {A BC, B C, ABC} ? {A C, B C, ABC} NO • Is C extraneous in ABC • {A BC, B C, ABC} ? {A B, B C, ABC} Yes • Set is now {A B, B C, ABC} • Is A is extraneous in ABC • {A B, B C, ABC} ? {A B, B C, BC} Yes • Set is now {A B, B C} • The canonical cover is: {A B, B C}

  37. Goals of Normalization • Decide whether a particular relation R is in “good” form. • In the case that a relation R is not in “good” form, decompose it into a set of relations {R1, R2, ..., Rn} such that • the decomposition is a lossless-join decomposition • each relation is in good form

  38. Decomposition • Decompose the relation schema Lending-schema into: Branch-schema = (branch-name, branch-city,assets) Loan-info-schema = (customer-name, loan-number, branch-name, amount) • All attributes of an original schema (R) must appear in the decomposition (R1, R2): R = R1  R2 • Lossless-join decomposition.For all possible relations r on schema R r = R1 (r) R2 (r)

  39. Example of Lossy-Join Decomposition • Lossy-join decompositions result in information loss. • Example: Decomposition of R = (A, B) R1 = (A) R2 = (B) A B A B    1 2 1   1 2 B(r) A(r) r A B A (r) B (r)     1 2 1 2

  40. A decomposition of R into R1 and R2 is lossless join if and only if at least one of the following dependencies is in F+: • R1 R2R1 • R1 R2R2

  41. Relation is in good form • If the relations Ripreferably should be in either Boyce-Codd Normal Form (BCNF) or Third (3NF) Normal Form • BCNF and 3NF eliminate redundancy • What is BCNF? • What is 3NF?

  42. Boyce-Codd Normal Form A relation schema R is in BCNF with respect to a set F of functional dependencies if for all functional dependencies in F+ of the form , where  R and  R,at least one of the following holds: •  is trivial (i.e.,  ) •  is a superkey for R

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

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

  45. However, using only F is incorrect when testing a relation in a decomposition of R • E.g. Consider R (A, B, C, D), with F = { A B, B C} • Decompose R into R1(A,B) and R2(A,C,D) • Neither of the dependencies in F contain only attributes from (A,C,D) so we might be mislead into thinking R2 satisfies BCNF. • In fact, dependency AC in F+ shows R2 is not in BCNF.

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

  47. Testing if Ri is in BCNF • To check if a relation Ri in a decomposition of R is in BCNF • 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

  48. Example of BCNF Decomposition • R = (branch-name, branch-city, assets, customer-name, loan-number, amount) F = {branch-name assets, branch-city loan-number amount, branch-name} Key = {loan-number, customer-name} • Decomposition • R1 = (branch-name, branch-city, assets) • R2 = (branch-name, customer-name, loan-number, amount) • R3 = (branch-name, loan-number, amount) • R4 = (customer-name, loan-number) • Final decomposition R1, R3, R4

  49. Additional Constraint of Decomposition • Dependency preservation: Let Fibe the set of dependencies F+ that include only attributes in Ri. • Preferably the decomposition should be dependency preserving, that is, (F1 F2  …  Fn)+ = F+ • Otherwise, checking updates for violation of functional dependencies may require computing joins, which is expensive.

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

More Related