165
Chapter 7: Relational Database Design and Creating the Physical Database Schema
7
Don’t plan to design an entity and move it through a fi rst normal form to a second normal form,
and so on. Each normal form is simply a different type of data integrity fault to be avoided.First Normal Form (1NF)
The fi rst normalized form means the data is in an entity format, such that the following
three conditions are met:■ (^) Every unit of data is represented within scalar attributes. A scalar value
is a value “capable of being represented by a point on a scale,” according to
Merriam-Webster.
Every attribute must contain one unit of data, and each unit of data must fi ll one
attribute. Designs that embed multiple pieces of information within an attribute
violate the fi rst normal form. Likewise, if multiple attributes must be combined in
some way to determine a single unit of data, the attribute design is incomplete.
■ All data must be represented in unique attributes. Each attribute must have a
unique name and a unique purpose. An entity should have no repeating attributes.
If the attributes repeat, or the entity is wide, the object is too broadly designed.
A design that repeats attributes, such as an order entity that includes item1, item2,
and item3 attributes to hold multiple line items, violates the fi rst normal form.
■ (^) All data must be represented within unique tuples. If the entity design requires
or permits duplicate tuples, that design violates the fi rst normal form.
If the design requires multiple tuples to represent a single item, or multiple items
are represented by a single tuple, the table violates fi rst normal form.
For an example of the fi rst normal form in action, assume that you have a listing of cus-
tomers and each customer can have multiple phone numbers. Table 7-3 shows customer
data in a model that violates the fi rst normal form. The repeating phone number attribute
is not unique.
TABLE 7-3 Violating the First Normal Form
Customer PhoneNumber1 PhoneNumber2 PhoneNumber2
J o h n D o e 111 111 1111 2 2 2 2 2 2 2222
Jane Smith 333 333 3333 444 444 4444
Luke Phillips 555 555 5555
To redesign the data model so that it complies with the fi rst normal form, resolve the
repeating group of phone number attributes into a single unique attribute, as shown in
Table 7-4, and then move any multiple values to a unique tuple. The Customer entity con-
tains a unique tuple for each customer, and the PhoneNumber entity’s CustomerID refers
to the primary key in the Customer entity.
c07.indd 165c07.indd 165 7/30/2012 4:18:17 PM7/30/2012 4:18:17 PM
http://www.it-ebooks.info