Databases Reference
In-Depth Information
This is bad, since it may produce redundancy. For instance, consider Table 4-3. Note that
the price attribute is redundant. After all, we could fill in the Price value for the third row
if it were blank, because we know that PubID 2 charges $34.95 for 500-page books.
T
a
ble 4-3. Redunda
n
t data in a table
Title
PubID
PageCount
Price
Moby-Dick
1
500
29.95
G
iant
2
5
00
3
4.95
Moby-Dick
2
500
34.95
We can summarize the problem with the dependency:
{PubID,PageCount} {Price}
by saying that the attribute Price depends upon a set of attributes:
{PubID,PageCount}
that is not a key, not a superkey, and not a proper subset of a key. It is a mix containing
one attribute from the key {Title,PubID} and one attribute that is not in any key.
With this example in mind, we can now define third normal form. A table scheme is in
third normal form if it is not possible to have a dependency of the form:
{A
1
,...,A
k
} {B}
where B does not belong to any key (is strictly informational) and {A
1
,...,A
k
} is not a
superkey. In other words, third normal form does not permit any strictly informational
attribute to depend upon anything other than a superkey. Of course, superkeys determine
all attributes, including strictly informational attributes, and so all attributes depend on
any superkey. The point is that, with third normal form, strictly informational attributes
depend only on superkeys.
4.7 Boyce-Codd Normal Form
It is possible to find table schemes that are in third normal form, but still have
redundancy. Here is an example.
Consider the table scheme {City,StreetName,ZipCode}, with dependencies:
{City,StreetName} {ZipCode}
and:
{ZipCode} {City}
