CMSC 424 – Database designLecture 10NormalizationMihai PopMidterm...•Graded A & B – disappointing•Outline of what I expected for A & BNormalization, functional dependencies....•Large schemas: redundant information•Small schemas: inefficient•Normalization – a way to pick a “reasonable” compromiseCombine 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 DecompositionFunctional 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 tuplesuper-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 t1,t2t1[]=t2[]⇒ t1[]=t2[] ort1[]≠t2[]⇒t1[]≠t2[]FDs continued•trivial dependencies: •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. if ⊆FD formalismAmstrong’s axioms •reflexivity: •augmentation: •transitivity: More rules (can be inferred from Amstrong's axioms)•union rule: •decomposition rule: •pseudotransitivity rule: if ⊆ then (trivial FD)if then if ∧ then if ∧ then if then and if ∧ then Computing the closure of a set of FDsExample: R(A,B,C,G,H,I) F = { A → B A → C CG → H CG → I B → H }F+ = { A → H /* A → B → H transitivity CG → HI /* CG → H, CG → I union rule AG → I /* A → C augmentation AG → CG → I AG → H /* 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 closureExample: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. R(A,B,C,G,H,I) F={ A → B, A → C, CG → H, CG → I, B → H } Algorithm to Compute (AG)+start with result=(AG)A → B expands result=(AGB)A → C expands result=(AGBC)CG → H “-” result=(AGBCH) CG → I “-” result=(AGBCHI)B → H no more expansionUses of Attribute ClosureThere 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 usefulLossless 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 Rr = ∏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 mappingDependency PreservationIn 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•4NF,…: we are not concerned in this
View Full Document
Unlocking...