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.