Second Normal Form
An
entity is said to be in the second
normal form (2NF) if it is already in 1NF and all non-identifying
attributes are dependent on the entity’s entire
unique identifier. If any attribute is not dependent entirely on the
entity’s unique identifier, that attribute has been
misplaced and must be removed. For example, “Herbie
Hancock” is the band name for two different CDs, and
therefore Band
Name
is not
entirely dependent on CD
ID
. To
normalize a misplaced attribute, either find the entity where the
attribute belongs or create an additional entity for the attribute.
In our example, we have a sign that Band Name
should be part of a new entity with some relationship to
CD
. As before, we resolve this problem by asking
the question: “What does a band name
describe”? It describes a band, or more generally,
an artist. Artist is yet another thing we are capturing data about
and is therefore probably an entity. We will add it to our diagram
with Band Name
as an attribute. Since not all
artists are bands, we will rename the attribute Artist
Name
. Figure 7-7 shows the new state of
the model.
Of course, the relationships for the new Artist
table are missing. We know that each Artist
has
one or many CD
rows. Each CD
could have one or many Artist
rows. We model this
in Figure 7-8.
Get Managing & Using MySQL, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.