Access Database Design and Programming, Second Edition

Normalization

As we 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:

{ISBN,Title,AuID} and {AuID,AuName}

we have normalized the database into first normal form.

Here is another example involving the higher normal forms.

Recall from an earlier example that the table scheme {City,StreetName,ZipCode}, with dependencies:

{City,StreetName} → {ZipCode}

and:

{ZipCode} → {City}

is in third normal form. However, Table 4.5 shows that there is still some redundancy in the table scheme. The table scheme is not in BCNF. In fact, this was the example we used to motivate our definition of BCNF. (The example violates BCNF.)

Table 4-5. A Table with Redundant Data

City	StreetName	ZipCode
Los Angeles	Hollywood Blvd	95000
	Vine St	95000

However, we can split this table scheme into two schemes:

{ZipCode,City}

and:

{ZipCode,StreetName}

In this case, Table 4.5 gets split into two tables, Table 4.6 and Table 4.7, and the redundancy is gone!

Table 4-6. First Table Derived from Table 4.5 to Eliminate Redundancy

ZipCode	City
95000	Los Angeles

Table 4-7. Second Table Derived from Table 4.5 to Eliminate Redundancy

ZipCode	StreetName
95000	Hollywood Blvd
95000	Vine St

Generally speaking, ...