CMSC424: Database DesignRelational Database DesignDecompositionExamples of Join DecompositionsGoal — Devise a Theory for the FollowingNormal FormsFirst Normal FormRest of the normal forms…Functional DependenciesFunctional Dependencies (Cont.)Slide 11Slide 12Closure of a Set of Functional DependenciesExampleProcedure for Computing F+Closure of Functional Dependencies (Cont.)Closure of Attribute SetsExample of Attribute Set ClosureUses of Attribute ClosureCanonical CoverExtraneous AttributesTesting if an Attribute is ExtraneousSlide 23Example of Computing a Canonical CoverNormalization Using Functional DependenciesSlide 26CMSC424, Spring 2005 1CMSC424: Database DesignLecture 9CMSC424, Spring 2005 2Relational Database Design•Find a “good” collection of relation schemas; otherwise–Repetition of Information.•Leads to anomalies–Inability to represent certain information.•Use integrity constraints to refine the schema•Main refinement technique: Decomposition–E.g. break ABCD into AB and BCD•Must be careful with decompositionCMSC424, Spring 2005 3DecompositionAll attributes of an original schema (R) must appear in the decomposition (R1, R2):R = R1 R2Lossless-join decomposition.For all possible legal relations r on schema R r = R1 (r) R2 (r) How do you define legal ?CMSC424, Spring 2005 4Examples of Join DecompositionsCMSC424, Spring 2005 5Goal — Devise a Theory for the FollowingDecide 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 decompositionOur theory is based on:functional dependenciesmultivalued dependenciesCMSC424, Spring 2005 6Normal Forms1st, 2nd, 3rd, 4th Normal FormsBoyce-Codd Normal Form (BCNF)CMSC424, Spring 2005 7First Normal FormAtomic DomainsSometimes a function of how domain is used, rather than intrinsic properties of the domainA set of values as an attribute not acceptableNon-atomic values complicate storage and encourage redundant (repeated) storage of dataWe will assume 1st normal form complianceCMSC424, Spring 2005 8Rest of the normal forms…Need:functional dependenciesmulti-valued dependenciesWhat are they ?Constraints on the set of legal relationsCMSC424, Spring 2005 9Functional DependenciesLet R be a relation schema R and RThe functional dependency holds on R if and only if for any legal relations r (R): t1[] = t2 [] t1[ ] = t2 [ ] Example: Consider r(A,B) with the following instance of r.On this instance, A B does NOT hold, but B A does hold. Beware:Difference between holding in one instance, and holding in all legal relations1 41 53 7CMSC424, Spring 2005 10Functional Dependencies (Cont.)Generalization of the notion of keysK is a superkey for relation schema R if and only if K RK is a candidate key for R if and only if K R, andfor no K, RFunctional dependencies allow us to express constraints that cannot be expressed using superkeys.CMSC424, Spring 2005 11Functional DependenciesIf a relation r is legal under a set F of functional dependencies, we say that r satisfies F.We say that F holds on R if all legal relations on R satisfy the set of functional dependencies F.What’s the difference between r and R above ?CMSC424, Spring 2005 12Functional Dependencies (Cont.)A functional dependency is trivial if it is satisfied by all instances of a relationE.g.• customer-name, loan-number customer-name• customer-name customer-nameIn general, is trivial if CMSC424, Spring 2005 13Closure of a Set of Functional DependenciesGiven 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 CF+ (closure of F)Set of all functional dependencies logically implied by FArmstrong’s Axioms:if , then (reflexivity)if , then (augmentation)if , and , then (transitivity)These rules are sound completeCMSC424, Spring 2005 14ExampleR = (A, B, C, G, H, I)F = { A B A C CG H CG I B H}some members of F+A H AG I CG HI if , then (reflexivity)if , then (augmentation)if , and , then (transitivity)CMSC424, Spring 2005 15Procedure for Computing F+To compute the closure of a set of functional dependencies F: F+ = Frepeatfor each functional dependency f in F+ apply reflexivity and augmentation rules on f add 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 furtherCMSC424, Spring 2005 16Closure of Functional Dependencies (Cont.)We can further simplify manual computation of F+ by using the following additional rules.If holds and holds, then holds (union)If holds, then holds and holds (decomposition)If holds and holds, then holds (pseudotransitivity)The above rules can be inferred from Armstrong’s axioms.CMSC424, Spring 2005 17Closure of Attribute SetsFor an attriute set , + = Closure of under F is the set of attributes that are functionally determined by under F: is in F+ +Algorithm to compute + :result := ;while (changes to result) dofor each in F dobeginif result then result := result endCMSC424, Spring 2005 18Example of Attribute Set ClosureR = (A, B, C, G, H, I)F = {A BA C CG HCG IB H}(AG)+1. result = AG2. 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)+ RIs any subset of AG a superkey?Does A R? == Is (A)+ RDoes G R? == Is (G)+ RCMSC424, Spring 2005 19Uses of Attribute ClosureThere are several uses of the attribute closure algorithm:Testing
View Full Document
Unlocking...