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

**sections.**

*blurred***to view the full version.**

*Sign up*
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!