Ch19-SRef-95

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

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

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?

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

View Full Document
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!