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

Table 4.5

Artists: 1NF Is Met, but with Problems

Agent Agent

PK—Artist PK— Signed Primary Secondary

Name Genre Date Agent Phone Phone

The Awkward Stage Rock 9/01/2005 John Doe [phone] NULL

Girth Metal 10/31/1997 Sally Sixpack [phone] [phone]

Wasabi Peanuts Adult 1/01/2005 John Doe [phone] NULL

Contempo-
rary Rock

The Bobby R&B 3/15/1985 Johnny [phone] NULL

Jenkins Band Jenkins

The Bobby Soul 3/15/1985 Johnny [phone] NULL

Jenkins Band Jenkins

Juices of Brazil Latin Jazz 6/01/2001 Jane Doe [phone] [phone]

Juices of Brazil World Beat 6/01/2001 Jane Doe [phone] [phone]

which we have solved the multiple genre problem. But we have added new
attributes, and that presents a new problem.
In this case, we have two attributes in the primary key: Artist Name
and Genre. If the studio decides to sell the Juices of Brazil albums in mul-
tiple genres to increase the band's exposure, we end up with multiple in-
stances of the group in the entity, because one of the primary key attributes
has a different value. Also, we've started storing the name of each band's
agent. The problem here is that the Agent attribute is an attribute of the
artist but not of the genre. So the Agent attribute is only partially depend-
ent on the entity's primary key. If we need to update the Agent attribute
for a band that has multiple entries, we must update multiple records or
else risk having two different agent names listed for the same band. This
practice is inefficient and risky from a data integrity standpoint. It is this
type of problem that 2NF eliminates.
Tables 4.6 and 4.7 show one possible solution to our problem. In this
case, we can break the entity into two different entities. The original entity
still contains only information about our artists; the new entity contains in-
formation about agents and the bands they represent. This technique re-
moves the partial dependency of the Agent attribute from the original
entity, and it lets us store more information that is specific to the agent.