This preview has intentionally blurred sections. Sign up to view the full version.
View Full DocumentThis preview has intentionally blurred sections. Sign up to view the full version.
View Full Document
Unformatted text preview: Schema Refinement and Normal Form Normal Forms Chapter 15 Instructor: Vladimir Zadorozhny [email protected] Information Science Program School of Information Sciences Database Management Systems, R. Ramakrishnan and J. Gehrke INFSCI2710 Instructor: Vladimir Zadorozhny 1 School of Information Sciences, University of Pittsburgh The Evils of Redundancy Redundancy is at the root of several problems associated with relational schemas: – redundant storage, insert/delete/update anomalies Integrity constraints, in particular functional dependencies , can be used to identify schemas with such problems and to suggest refinements. Main refinement technique: decomposition (replacing Database Management Systems, R. Ramakrishnan and J. Gehrke INFSCI2710 Instructor: Vladimir Zadorozhny 2 ABCD with, say, AB and BCD, or ACD and ABD). Decomposition should be used judiciously: – Is there reason to decompose a relation? – What problems (if any) does the decomposition cause? Decomposition of a Relation Scheme Suppose that relation R contains attributes A1 ... An. A decomposition of R consists of replacing R by two or A decomposition of R consists of replacing R by two or more relations such that: – Each new relation scheme contains a subset of the attributes of R (and no attributes that do not appear in R), and – Every attribute of R appears as an attribute of one of the new relations. Intuitively decomposing R means we will store Database Management Systems, R. Ramakrishnan and J. Gehrke INFSCI2710 Instructor: Vladimir Zadorozhny 3 Intuitively, decomposing R means we will store instances of the relation schemes produced by the decomposition, instead of instances of R. E.g., Can decompose SNLRWH into SNLRH and RW . Lossless Join Decompositions Decomposition of R into X and Y is losslessjoin w.r.t. t f FD F if f i t th t ti fi F a set of FDs F if, for every instance r that satisfies F: – ( r ) ( r ) = r It is always true that r ( r ) ( r ) – In general, the other direction does not hold! If it does, the decomposition is losslessjoin. D fi iti t d d t d iti i t 3 X Y X Y Database Management Systems, R. Ramakrishnan and J. Gehrke INFSCI2710 Instructor: Vladimir Zadorozhny 4 Definition extended to decomposition into 3 or more relations in a straightforward way. It is essential that all decompositions used to deal with redundancy be lossless! Functional Dependencies (FDs) A functional dependency X Y holds over relation R if f ll bl i t f R if, for every allowable instance r of R: – t1 r, t2 r, ( t1 ) = ( t2 ) implies ( t1 ) = ( t2 ) – i.e., given two tuples in r , if the X values agree, then the Y values must also agree. (X and Y are sets of attributes.) An FD is a statement about all allowable relations....
View
Full
Document
This note was uploaded on 12/01/2011 for the course INFSCI 2710 taught by Professor Zadorozhnyy,v during the Winter '08 term at Pittsburgh.
 Winter '08
 Zadorozhnyy,V

Click to edit the document details