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

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

View Full Document Right Arrow Icon

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

View Full DocumentRight Arrow Icon

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

View Full DocumentRight Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: Schema Refinement and Normal Form Normal Forms Chapter 15 Instructor: Vladimir Zadorozhny vladimir@sis.pitt.edu 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 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! 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

Page1 / 11

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

This preview shows document pages 1 - 4. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online