Ch19-SRef-95 - Schema Refinement and Normal Normal Forms...

Info icon This preview shows pages 1–4. Sign up to view the full content.

View Full Document Right Arrow Icon
Schema Refinement and 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?
Image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Decomposition of a Relation Scheme Suppose that relation R contains attributes A1 ... An. 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 lossless-join 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 lossless-join. 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!
Image of page 2