Microsoft® SQL Server® 2012 Bible

168

Part II: Building Databases and Working with Data

The second normal form is violated when an attribute depends on part of the key. The third

normal form is violated when the attribute does depend on the key but also depends on

another non-key attribute.

The key phrase when describing the third normal form is that every attribute “must provide

a fact about the key, the whole key, and nothing but the key.”

Just as with the second normal form, the third normal form is resolved by moving the non-

dependent attribute to a new entity.

Continuing with the Regional Offi ce Store example, assume that a manager is assigned to

each region. The Regional Manager attribute belongs in the Regional Office entity;

but it is a violation of the third normal form if other information describing the manager is

stored in the regional offi ce, as shown in Table 7-7.

TABLE 7-7 Violating the Third Normal Form

Regional Offi ce Entity

RegionalOffi cePK RegionalOffi cePhoneNumber LManager DateofHire

Southeast 1-828-555-1212 Jeff Davis 5/1/99

North 1-828-555-1213 Ken Frank 4/15/97

Northwest 1-828-555-1214 Dab Smith 7/7/2001

The DateofHire describes the manager not the region, so the hire-date attribute is not

directly dependent on the RegionalOffice entity’s primary key. The DateOfHire’s

dependency is transitive — it describes the key and a non-key attribute — in that it goes

through the Manager attribute.

Creating a Manager entity and moving its attributes to the new entity resolves the viola-

tion of the third normal form and cleans up the logical design, as demonstrated in

Table 7-8.

TABLE 7-8 Conforming to the Third Normal Form

Regional Offi ce Entity Manager Entity

RegionalOffi cePK Manager ManagerPK DateofHire

Southeast Jeff Davis Jeff Davis 5/1/99

North Ken Frank Ken Frank 4/15/97

Northwest Dab Smith Dab Smith 7/7/2001

c07.indd 168c07.indd 168 7/30/2012 4:18:17 PM7/30/2012 4:18:17 PM

http://www.it-ebooks.info