CMSC424 Database Design Lecture 9 CMSC424 Spring 2005 1 Relational 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 decomposition CMSC424 Spring 2005 2 Decomposition All attributes of an original schema R must appear in the decomposition R1 R2 R R1 R2 Lossless join decomposition For all possible legal relations r on schema R r R1 r R2 r How do you define legal CMSC424 Spring 2005 3 Examples of Join Decompositions CMSC424 Spring 2005 4 Goal Devise a Theory for the Following 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 CMSC424 Spring 2005 5 Normal Forms 1st 2nd 3rd 4th Normal Forms Boyce Codd Normal Form BCNF CMSC424 Spring 2005 6 First Normal Form Atomic Domains Sometimes a function of how domain is used rather than intrinsic properties of the domain A set of values as an attribute not acceptable Non atomic values complicate storage and encourage redundant repeated storage of data We will assume 1st normal form compliance CMSC424 Spring 2005 7 Rest of the normal forms Need functional dependencies multi valued dependencies What are they Constraints on the set of legal relations CMSC424 Spring 2005 8 Functional Dependencies Let R be a relation schema R and R The 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 1 4 1 5 3 NOT 7 hold but B A does hold On this instance A B does Beware Difference between holding in one instance and holding in all legal relations CMSC424 Spring 2005 9 Functional Dependencies Cont Generalization of the notion of keys 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 Functional dependencies allow us to express constraints that cannot be expressed using superkeys CMSC424 Spring 2005 10 Functional Dependencies If 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 11 Functional Dependencies Cont 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 CMSC424 Spring 2005 12 Closure of a Set of Functional Dependencies 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 F closure of F Set of all functional dependencies logically implied by F Armstrong s Axioms if then reflexivity if then augmentation if and then transitivity These rules are sound complete CMSC424 Spring 2005 13 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 AG I CG HI if then reflexivity if then augmentation if and then transitivity CMSC424 Spring 2005 14 Procedure for Computing F To compute the closure of a set of functional dependencies F F F repeat for 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 further CMSC424 Spring 2005 15 Closure 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 16 Closure of Attribute Sets For 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 do for each in F do begin if result then result result end CMSC424 Spring 2005 17 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 2 3 4 result AG result ABCG result ABCGH result ABCGHI A C and A B CG H and CG AGBC 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 CMSC424 Spring 2005 18 Uses of Attribute Closure 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 CMSC424 Spring 2005 19 Canonical Cover 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 CMSC424 Spring 2005 20 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 CMSC424 Spring 2005 21 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 extraneous in compute A using the dependencies in F check that A contains A if it does A is extraneous To test if attribute A is …
View Full Document
Unlocking...