Databases Reference
In-Depth Information
(Although in real life, a zip code may be shared by two different cities, we will assume
otherwise for the purposes of illustration.) This table scheme is in third normal form. To
see this, observe that the keys are {City,StreetName} and {ZipCode,StreetName}. Hence,
no attribute is strictly informational, and there is nothing to violate third normal form.
On the other hand, consider Table 4-4. We can fill in the blank city name because
{ZipCode}
{City}.
Ta
b
le 4-4. A table with dependencies
City
StreetName
ZipCode
L
os Angeles
Hollywood Blvd
95000
Vine St
95000
The problem here is with the dependency:
{ZipCode} {City}
which does not violate third normal form because, as we have mentioned, {City} is not
strictly informational.
The previous example gives us the idea to strengthen the condition in the definition of
third normal form by dropping the requirement that B be strictly informational. Thus, we
can define our last, and strongest, normal form. A table scheme is in Boyce-Codd normal
form if it is not possible to have a dependency of the form:
{A
1
,...,A
k
} {B}
where {A
1
,...,A
k
} is not a superkey. In other words, BCNF form does not permit any
attribute to depend upon anything other than a superkey.
As mentioned earlier, all attributes must depend on any superkey by the very definition of
superkey. Thus, BCNF is the strongest possible restriction of this type—it says that an
attribute is not allowed to depend on anything other than a superkey.
4.8 Normalization
As mentioned earlier, the process of changing a database design to produce table schemes
in normal form is called
normalization
.
As a very simple example, the table scheme:
{ISBN,Title,Authors}
is not even in first normal form, because the Authors attribute might contain more than
one author and is therefore not atomic. By trading in this table scheme for the two
schemes:
