Databases Reference
In-Depth Information
Definition:
Let
SK
be a subset of the heading of relvar
R
. Then
SK
is a
superkey
for
R
if and only if it
possesses the following property:
1.
Uniqueness:
No valid value for
R
contains two distinct tuples with the same value for
SK
.
More succinctly, a superkey for
R
is a subset of the heading of
R
that's unique but not necessarily irreducible.
In other words, we might say, loosely, that a superkey is a superset of a key (“loosely,” because of course the
superset in question must still be a subset of the pertinent heading). Observe, therefore, that all keys are
superkeys, but “most” superkeys aren't keys.
Note:
A superkey that isn't a key is sometimes said to be a
proper
superkey.
It's convenient to define the notion of a
subkey
also:
Definition:
Let
SK
be a subset of the heading of relvar
R
. Then
SK
is a
subkey
for
R
if and only if it's a
subset of at least one key of
R
.
Note:
A subkey that isn't a key is sometimes said to be a
proper
subkey.
By way of example, consider relvar SP, which has just one key, {SNO,PNO}. That relvar has:
a.
Two superkeys:
{ SNO , PNO }
{ SNO , PNO , QTY }
Note that the heading is always a superkey for any relvar
R
.
b.
Four subkeys:
{ SNO , PNO }
{ SNO }
{ PNO }
{ }
Note that the empty set of attributes is always a subkey for any relvar
R
.
To close this section, note that if
H
and
SK
are the heading and a superkey, respectively, for relvar
R
, then the
FD
SK
→
H
holds in
R
, necessarily; equivalently, the FD
SK
→
Y
holds in
R
for all subsets
Y
of
H
. The reason is
that if two tuples of
R
have the same value for
SK
, then they must in fact be the very same tuple, in which case they
obviously must have the same value for
Y
. Of course, all of these remarks apply in the important special case in
which
SK
is not just a superkey but a key; as I put it earlier (very loosely, of course), there are always arrows out of
keys. In fact, we can now make a more general statement: There are always arrows out of superkeys.
SECOND NORMAL FORM
I need to introduce one more concept,
FD irreducibility
(a second kind of irreducibility, observe), before I can get
on to the definitions of 2NF, 3NF, and BCNF:
Definition:
The FD
X
→
Y
is
irreducible with respect to relvar
R
(or just
irreducible
, if
R
is understood) if
and only if it holds in
R
and
X
-
→
Y
doesn't hold in
R
for any proper subset
X
-
of
X
.