CMSC 424 Database design Lecture 10 Normalization Mihai Pop Midterm Graded A B disappointing Outline of what I expected for A B Normalization functional dependencies Large schemas redundant information Small schemas inefficient Normalization a way to pick a reasonable compromise Combine Schemas Suppose we combine borrower and loan to get bor loan customer id loan number amount Result is possible repetition of information L 100 in example below A Lossy Decomposition Functional dependencies How can we formally reason about when a decomposition is correct Functional dependencies how the attributes relate to each other basic idea super key if I know the values of the attributes in a super key I know the entire tuple super key all attributes functional dependency super key implies all attributes note implies is my term more generally any set of attributes can imply any other set of attributes iff tuples t 1 t 2 t 1 t 2 t 1 t 2 or t 1 t 2 t 1 t 2 FDs continued trivial dependencies if closure need all FDs some logically implied by others e g if A B B C then A C is implied given F set of FDs find F the closure of all logically implied by F Why See http www schneier com blog archives 2007 12 anonymity and t 2 html Given a user s public IMDb ratings which the user posted voluntarily to selectively reveal some of his or her but we ll use the male pronoun without loss of generality movie likes and dislikes we discover all the ratings that he entered privately into the Netflix system presumably expecting that they will remain private FD formalism Amstrong s axioms reflexivity if then trivial FD augmentation if then transitivity if then More rules can be inferred from Amstrong s axioms union rule if then decomposition rule if then and pseudotransitivity rule if then Computing the closure of a set of FDs Example R A B C G H I F A B A C CG H CG I B H F A H CG HI AG I AG H A B H transitivity CG H CG I union rule A C augmentation AG CG I CG H there is a non trivial exponential algorithm for computing F Closure of Attribute Sets useful to find if a set of attributes is a superkey the closure of a set of attributes under F is the set of all attributes that are functionally determined by there is an algorithm that computes the closure Example Algorithm to Compute AG R A B C G H I F Aresult AG B A C CG H CG I B H start with A B expands result AGB A C expands result AGBC CG H result AGBCH CG I result AGBCHI B H no more expansion Note that since G is not on any right hand side no subset of the attributes can be a superkey unless it contains G for there is no FD to generate it 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 Lossless Decompositions 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 X R2 r 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 anyone of these two FDs guarantees uniqueness in the mapping Dependency Preservation In a decomposition dependencies are preserved if we do not need to join in order to enforce FDs all FDs remain intra relational and do not become inter relational constraints to check if a decomposition is dependency preserving we need to examine all FDs in F there is an algorithm for testing dependency preservation requires the computation of F The Normal Forms 1NF every attribute has an atomic value not a set value 2NF we will not be concerned in this course 3NF if for each FD X Y either it is trivial or X is a superkey Y X is a proper subset of a candidate key BCNF if for each FD X Y either it is trivial or X is a superkey 1NF 2NF 3NF BCNF 4NF we are not concerned in this course 4NF
View Full Document
Unlocking...