NORMALIZING A DATA MODEL - A Developer's Guide to Data Modeling for SQL Server - page 86

Table 4.6 Artists: 2NF Version of This Entity

PK—Artist Name PK—Genre SignedDate

The Awkward Stage Rock 9/01/2005

Girth Metal 10/31/1997

Wasabi Peanuts Adult Contemporary Rock 1/01/2005

The Bobby Jenkins Band R&B 3/15/1985

The Bobby Jenkins Band Soul 3/15/1985

Juices of Brazil Latin Jazz 6/01/2001

Juices of Brazil World Beat 6/01/2001

Table 4.7 Agents: An Additional Entity to Solve the Problem

Agent Agent

PK—Agent Name Artist Name PrimaryPhone SecondaryPhone

John Doe The Awkward Stage 555-1234 NULL

Sally Sixpack Girth [phone] [phone]

Johnny Jenkins The Bobby Jenkins Band [phone] NULL

Jane Doe Juices of Brazil 555-4321 555-9999

Third Normal Form (3NF)
Third normal form is the form that most well-designed databases meet.
3NF extends 2NF to include the elimination of transitive dependencies.
Transitive dependencies are dependencies that arise from a non-key
attribute relying on another non-key attribute that relies on the primary
key. In other words, if there is an attribute that doesn't rely on the primary
key but does rely on another attribute, then the first attribute has a transi-
tive dependency. As with 2NF, to resolve this issue we might simply move
the offending attribute to a new entity. Coincidentally, in solving the 2NF
problem in Table 4.7, we also created a 3NF entity. In this particular case,
AgentPrimaryPhone and AgentSecondaryPhone are not actually attributes
of an artist; they are attributes of an agent. Storing them in the Artists en-
tity created a transitive dependency, violating 3NF.
The differences between 2NF and 3NF are very subtle. 2NF deals
with partial dependency, and 3NF with transitive dependency. Basically, a