Database Reference
In-Depth Information
CustomerNum determines all the other columns. In addition, RepNum determines LastName and
FirstName.
When the primary key of a table is a single column, the table is automatically in second normal form. (If
the table were not in second normal form, some columns would be dependent on only a portion of the pri-
mary key, which is impossible when the primary key is just one column.) Thus, the Customer table is in sec-
ond normal form.
The sample Customer table shown in Figure 5-10 illustrates that this table possesses problems similar to
those encountered earlier even though it is in second normal form. In this case, the name of a sales rep can
occur many times in the table; see sales rep 65 (Juan Perez), for example.
166
Customer
CustomerNum CustomerName
Balance
CreditLimit RepNum LastName FirstName
148
Al's Appliance
$6,550.00
$7,500.00
20
Kaiser
Valerie
and Sport
282
Brookings Direct
$431.50
$10,000.00
35
Hull
Richard
356
Ferguson's
$5,785.00
$7,500.00
65
Perez
Juan
408
The Everything
$5,285.25
$5,000.00
35
Hull
Richard
Shop
462
Bargains Galore
$3,412.00
$10,000.00
65
Perez
Juan
524
Kline's
$12,762.00
$15,000.00
20
Kaiser
Valerie
608
Johnson's
$2,106.00
$10,000.00
65
Perez
Juan
Department
Store
687
Lee's Sport
$2,851.00
$5,000.00
35
Hull
Richard
and Appliance
725
Deerield's Four
$248.00
$7,500.00
35
Hull
Richard
Seasons
842
All Season
$8,221.00
$7,500.00
20
Kaiser
Valerie
FIGURE 5-10
Sample Customer table
This redundancy creates the same set of problems that you examined in the first normal form Orders
table. In addition to the problem of wasted space, you have similar update anomalies as follows:
1. Updates. A change to the name of a sales rep requires not one change to the table, but several,
making the update process cumbersome.
2. Inconsistent data. There is nothing about the design that would prohibit a sales rep from having
two different names in the database. In fact, if the same sales rep represents 20 customers (and
thus would be found on 20 different rows), he or she could have 20 different names in the
database.
3. Additions. In order to add sales rep 87 (Mary Daniels) to the database, she must already repre-
sent at least one customer. If she has not yet been assigned any customers, you must add her
record and create a fictitious customer for her to represent. Again, this is not a desirable solu-
tion to the problem.
4. Deletions. If you deleted all the customers of sales rep 35 from the database, you would lose all
information concerning sales rep 35.
These update anomalies are due to the fact that RepNum determines LastName and FirstName, but
RepNum is not the primary key. As a result, the same RepNum and consequently the same LastName and
FirstName can appear on many different rows.
You
ve seen that second normal form is an improvement over first normal form, but to eliminate second
normal form problems, you need an even better strategy for creating tables in the database. Third normal
form provides that strategy. Before looking at third normal form, however, you need to become familiar with
the special name that is given to any column that determines another column (as RepNum does in the
Customer table).
'
Search WWH ::




Custom Search