Access Database Design and Programming, Second Edition

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}

(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}.

Table 4-4. A Table with Dependencies

City	StreetName	ZipCode
Los 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, or BCNF, if it is not possible to have a dependency of the form:

{A1,. . .,Ak} → {B}

where {A₁,...,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.