1 / 18

Design Theory

Design Theory. Minimal Sets of Dependancies. A set of dependencies is minimal if: Every right side is a single attribute For no XA in F and proper subset Z of X is F – {XA} U {ZA} equivalent to F. For no X A in F is the set F – {XA} equivalent to F

psyche
Download Presentation

Design Theory

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. Design Theory

  2. Minimal Sets of Dependancies • A set of dependencies is minimal if: • Every right side is a single attribute • For no XA in F and proper subset Z of X is F – {XA} U {ZA} equivalent to F. • For no XA in F is the set F – {XA} equivalent to F If G is equivalent to F and G is minimal, we say that G is a minimal cover of F

  3. Finding Minimal Covers • Replace each FD of the form XA1...An with n FDs:XA1, ..., X An • For each FD A1...AnB and each attribute Ai if F – {A1...AnB} U {A1...Ai-1Ai+1...AnB} is equivalent to F, then delete Ai from the left side. • How do we check this? It is enough to check if F implies A1...Ai-1Ai+1...AnB. Why?

  4. Finding Minimal Covers (cont) • For each FD XA, if F – {XA} is equivalent to F, then delete XA • How do we check this? It is enough to check if F – {XA} implies XA. Why?

  5. Example • Find a minimal cover for:

  6. Normal Form of a Schema • A schema S is in BCNF if for every FD XY that holds in S, • Y is a subset of X or • X is a super-key (i.e., X contains a key) • If this is not the case, our schema may be problematic. • Example: Sup_Info(name, address, item, price) nameaddress name, item  price

  7. Decompositions • A decomposition of a schema R is a set R1...Rn such that • R1 is contained in R • R = R1 U ... U Rn • We may decompose a schema so that it will be a set of schemas all in BCNF. • Not every decomposition is good. • Can you find a trivial decomposition to BCNF?

  8. Lossless Join Decompositions • A decomposition R1...Rn of R is lossless if for all instances r of R that satisfy the FDs of R: r = R1(r)  ... Rn (r) • Checking if a decomposition is lossless: • Create a table with a column for each attribute and a row for each sub-schema • In row i and column k, put a symbol ak if the attribute of column k is in the schema of row i. Otherwise, put bik

  9. Lossless Join Decompositions (2) • While there is a dependency XA in F such that there are 2 rows that have the same values in X and differ in A do: • Choose one of the values. (if one is an a-value, then it must be chosen) • Make all the occurrences of the other value equal to the chosen value • If the resulting relation has a row of all a-s, then the decomposition has a lossless join

  10. Example • R = ABCDE, R1 = AD, R2 = AB, R3 = BE, R4 = CDE, and R5 = AE • Functional dependencies: • AC • BC • CD • DEC • CEA

  11. Example

  12. Decompositions that Preserve Dependencies • Problem: Suppose that we decompose R and then insert rows into the decomposition. Is it possible that the join of these rows will contradict a FD? • Example: R = CSZ (city, street, zip-code) then, CSZ, ZC hold in R. Suppose we decompose into SZ and CZ. This is lossless. However, we can contradict CSZ

  13. Definitions • We define Z (F) to be the set of dependencies XY in F+ such that X and Y are in Z. • We say that a decomposition R1...Rn of R is dependency preserving if for all instances r of R that satisfy the FDs of R: R1(F) U ... U Rn (F) implies F • Note that the other direction of implication clearly holds always.

  14. Testing Dependency Preservation • To check if the decomposition preserves XY: Z:=X while changes to Z occur do for i:=1..n do Z:=Z U ((Z Ri)+ Ri) /*closure w.r.t. F*/ Return true if Y is contained in Z U U

  15. Example • Suppose R=ABCD and we have a decomposition {AB, BC, CD}, and dependencies {AB, BC, CD, DA}. • Does this decomposition preserve DA?

  16. Goal • Given a schema R, we would like to check if R is in BCNF (How?). • If yes, good • If not, try to find a decomposition of R that has a lossless join and is dependency preserving. • Is this always possible? • lossless join: yes • dependency preserving: no

  17. Decomposing to BCNF • Lemma: • Every 2-attribute schema is in BCNF • If R is not in BCNF, then we can find attributes A and B in R such that (R – AB)  A • Proof: • Easy • Suppose that R is not in BCNF. Then there is a FD XA that violates the BCNF condition. Then, A is not in X and there is an attribute B other then A not in X (otherwise, X is a key).

  18. Algorithm for Lossless Join, BCNF Decomposition • Algorithm in handout. • Example: Relation CTHRSG (course, teacher, hour, room, student, grade), FDs: • CT • HRC • HTR • CSG • HSR

More Related