Microsoft® SQL Server® 2012 Bible

166

Part II: Building Databases and Working with Data

TABLE 7- 4 Conforming to the First Normal Form

PhoneNumber Entity CustomerEntity

CustomerID(FK) PhoneNumber CustomerID (PK) Name

1 111 111 1111 1 J o h n D o e

1 222 222 2222 2 Jane Smith

2 333 333 3333Excess Inventory 3 Luke Phillips

2 444 444 4444

3 555 555 5555

Another example of a data structure that desperately needs to adhere to the fi rst normal

form is a corporate product code that embeds the department, model, color, size, and so

forth within the code. I’ve even seen product codes that were so complex they included dig-

its to signify the syntax for the following digits.

In a theoretical sense, this type of design is wrong because the attribute isn’t a scalar

value. In practical terms, it has the following problems:

■ (^) Using a digit or two for each data element means that the database will soon run
out of possible data values.
■ (^) Databases don’t index based on the internal values of a string, so searches require
scanning the entire table and parsing each value.
■ (^) Business rules are diffi cult to code and enforce.
Entities with nonscalar attributes need to be completely redesigned so that each individual
data attribute has its own attribute. Smart keys may be useful for humans, but it is best if
it is generated by combining data from the tables.
Second Normal Form (2NF)
The second normal form ensures that each attribute describes the entity. It’s a dependency
issue. Does the attribute depend on, or describe, the item identifi ed by the primary key?
If the entity’s primary key is a single value, this isn’t too diffi cult. Composite primary keys can
sometimes get into trouble with the second normal form if the attributes aren’t dependent on
every attribute in the primary key. If an attribute depends on one of the primary key attri-
butes but not the other that is a partial dependency, which violates the second normal form.
For example, assume a database contains the table RegionalOffi ceStore, whose primary key
was a composite key including RegionalOffi ceID and StoreID. Adding the regional phone
number to the data model would violate the second normal form, as shown in Table 7-5.
Because the primary key (PK) is a composite of both RegionalOffi ce and Store, and that the
phone number is a permanent phone number for the regional offi ce, a phone number isn’t
assigned for each store.
c07.indd 166c07.indd 166 7/30/2012 4:18:17 PM7/30/2012 4:18:17 PM
http://www.it-ebooks.info