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

Table 4.10 Agency Information

PK—Agency AgencyPrimaryPhone

AAA Talent [phone]

A Star Is Born Agency [phone]

AAA Talent [phone]

Johnny Jenkins Talent [phone]

BBB Talent [phone]

Now we have a pair of entities that have relevant, unique attributes
that rely on their primary keys. We've also eliminated the confusion about
the phone numbers.
Often, databases that are being normalized with the target of 3NF end
up in 4NF, because this multivalued dependency problem is inherently ob-
vious when you properly identify primary keys. However, the 3NF version
of these entities would have worked, although it isn't necessarily the most
efficient form.
Now that we have a number of 3NF and 4NF entities, we must relate
these entities to one another. The final normal form that we discuss is fifth
normal form (5NF). 5NF specifically deals with relationships among
three or more entities, often referred to as tertiary relationships. In 5NF,
the entities that have specified relationships must be able to stand alone as
individual entities without dependence on the other relationships.
However, because the entities relate to one another, 5NF usually requires
a physical entity that acts as a resolution entity to relate the other entities
to one another. This additional entity has three or more foreign keys (based
on the number of entities in the relationship) that specify how the entities
relate to one another. This is how many-to-many relationships (as defined
in Chapter 2) are actually implemented. Thus, if a many-to-many relation-
ship is properly implemented, the database is in 5NF.
Frequently, you can avoid the complexity of 5NF by properly imple-
menting foreign keys in the entities that relate to one another, so 4NF plus
these keys generally avoids the physical implementation of a 5NF data
model. However, because this alternative is not always realistic, 5NF is de-
fined to help formalize this scenario.