Managing & Using MySQL, 2nd Edition

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.

Figure 7-7. The data model with the new Artist entity

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.

Figure 7-8. The Artist relationships in the ...