Microsoft® SQL Server® 2012 Bible

167

Chapter 7: Relational Database Design and Creating the Physical Database Schema

7

TABLE 7-5 Violating the Second Normal Form

PK-RegionalOffi ce PK-Store RegionalOffi ce PhoneNumber

Southeast Store One 828-555-1212

Southeast Store Two 828-555-1212

North Store Three 828-555-1213

North Store Four 828-555-1213

Northwest Store Five 828-555-1214

Northwest Store Six 828-555-1215

The problem with this design is that the phone number is an attribute of the regional offi ce

but not the store, so the PhoneNumber attribute is only partially dependent on the entity’s

primary key.

An obvious practical problem with this design is that updating the phone number requires

either updating multiple tuples or risking having two phone numbers for the same phone.

The solution is to remove the partially dependent attribute from the entity with the com-

posite keys, and create an entity with a unique primary key for the store as shown in

Table 7-6. This new entity is then an appropriate location for the dependent attribute.

TABLE 7- 6 Conforming to the Second Normal Form

Store Entity Regional Offi ce Entity

PK-Regional Offi ce PK-Store PK-Regional Offi ce PhoneNumber

Southeast Store One Southeast 828-555-1212

Southeast Store Two North 828-555-1213

North Store Three Northwest 828-555-1214

North Store Four

Northwest Store Five

Northwest Store Six

The PhoneNumber attribute is now fully dependent on the entity’s primary key. Each

phone number is stored in only one location, and no partial dependencies exist.

Third Normal Form (3NF)

The third normal form checks for transitive dependencies. A transitive dependency is similar

to a partial dependency in that they both refer to attributes that are not fully depen-

dent on a primary key. A dependency is transient when attribute1 is dependent on

attribute2, which is dependent on the primary key.

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

http://www.it-ebooks.info