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

Table 4.8 Agents: More Agent Information

PK— PK— PK—Artist Agent Agent

Agent Name Agency Name PrimaryPhone SecondaryPhone

John Doe AAA Talent The Awkward [phone] NULL

Stage

Sally Sixpack A Star Is Born Girth [phone] [phone]

Agency

John Doe AAA Talent Wasabi Peanuts [phone] NULL

Johnny Jenkins Johnny The Bobby [phone] NULL

Jenkins Talent Jenkins Band

Jane Doe BBB Talent Juices of Brazil [phone] [phone]

Specifically, this entity stores information that creates redundancy, be-
cause there is a multivalued dependency within the primary key. A multi-
valued dependency is a relationship in which a primary key attribute,
because of its relationship to another primary key attribute, creates multi-
ple tuples within an entity. In this case, John Doe represents multiple
artists. The primary key requires that the Agent Name, Agency, and Artist
Name uniquely define an agent; if you don't know which agency an agent
works for and if an agent quits or moves to another agency, updating this
table will require multiple updates to the primary key attributes.
There's a secondary problem as well: we have no way of knowing
whether the phone numbers are tied to the agent or tied to the agency. As
with 2NF and 3NF, the solution here is to break Agency out into its own
entity. 4NF specifies that there be no multivalued dependencies in an en-
tity. Consider Tables 4.9 and 4.10, which show a 4NF of these entities.

T ABLE 4.9 Agent-Only Information

PK— Agent Agent

Agent Name PrimaryPhone SecondaryPhone Artist Name

John Doe [phone] NULL The Awkward Stage

Sally Sixpack [phone] [phone] Girth

John Doe [phone] NULL Wasabi Peanuts

Johnny Jenkins [phone] NULL The Bobby Jenkins Band

Jane Doe [phone] [phone] Juices of Brazil