Databases Reference
In-Depth Information
For example, the FD {SNO,PNO} → {QTY} is irreducible with respect to relvar SP.
Note:
This kind of
irreducibility is sometimes referred to more explicitly as
left
irreducibility (since it's really the left side of the FD
that we're talking about), but I've chosen to elide that “left” here for simplicity.
Now—at last, you might be forgiven for thinking—I can define 2NF:
Definition:
Relvar
R
is in
second normal form
(2NF) if and only if, for every key
K
of
R
and every nonkey
attribute
A
of
R
, the FD
K
→ {
A
} (which holds in
R
, necessarily) is irreducible.
Note:
The following definition is logically equivalent to the one just given (see Exercise 4.5 at the end of the
chapter) but can sometimes be more useful:
Definition:
Relvar
R
is in
second normal form
(2NF) if and only if, for every nontrivial FD
X
→
Y
that
holds in
R
, at least one of the following is true: (a)
X
is a superkey; (b)
Y
is a subkey; (c)
X
is not a subkey.
Points arising:
It would be extremely unusual to regard 2NF as the ultimate goal of the design process. In fact, both 2NF
and 3NF are mainly of historical interest; they're both regarded at best as stepping stones on the way to
BCNF, which is of much more pragmatic (as well as theoretical) interest.
Definitions of 2NF in the literature often take the form “
R
is in 2NF if and only if it's in 1NF and ... .”
However, such definitions are usually based on a mistaken understanding of what 1NF is. As we've seen,
all
relvars are in 1NF, and the words “it's in 1NF and” therefore add nothing.
Let's look at an example. Actually, it's usually more instructive with the normal forms to look at a
counterexample rather than an example per se. Consider, therefore, a revised version of relvar SP—let's call it
SCP—that has an additional attribute CITY, representing the city of the applicable supplier. Here are some sample
tuples:
┌─────┬────────┬─────┬─────┐
SCP │ SNO │ CITY │ PNO │ QTY │
├═════┼────────┼═════┼─────┤
│ S1 │ London │ P1 │ 300 │
│ S1 │ London │ P2 │ 200 │
│ S1 │ London │ P3 │ 400 │
│ .. │ ...... │ .. │ ... │
│ S2 │ Paris │ P1 │ 300 │
│ S2 │ Paris │ P2 │ 400 │
│ .. │ ...... │ .. │ ... │
This relvar clearly suffers from redundancy: Every tuple for supplier S1 tells us S1 is in London, every tuple
for supplier S2 tells us S2 is in Paris, and so on. And the relvar isn't in 2NF—its sole key is {SNO,PNO}, and the
FD {SNO,PNO} → {CITY} therefore certainly holds, but that FD isn't irreducible: We can drop PNO from the
determinant and what remains, {SNO} → {CITY}, is still an FD that holds in the relvar. Equivalently, we can say
the FD {SNO} → {CITY} holds and is nontrivial; moreover, (a) {SNO} isn't a superkey, (b) {CITY} isn't a
subkey, and (c) {SNO}
is
a subkey, and so—appealing now to the second of the definitions given above—the relvar
isn't in 2NF.
