DATABASE DESIGN 1: NORMALIZATION - Concepts of Database Management - page 162

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

162

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 Deerfield's $248.00 $7,500.00 35 Hull Richard

Four Seasons

842 All Season $8,221.00 $7,500.00 20 Kaiser Valerie

Customer Rep

RepNum LastName FirstName

CustomerNum CustomerName Balance CreditLimit RepNum

20 Kaiser Valerie

148 Al's Appliance $6,550.00 $7,500.00 20

and Sport

35 Hull Richard

282 Brookings Direct $431.50 $10,000.00 35

65 Perez Juan

356 Ferguson's $5,785.00 $7,500.00 65

408 The Everything $5,285.25 $5,000.00 35

Shop

462 Bargains Galore $3,412.00 $10,000.00 65

524 Kline's $12,762.00 $15,000.00 20

608 Johnson's $2,106.00 $10,000.00 65

Department Store

687 Lee's Sport $2,851.00 $5,000.00 35

and Appliance

725 Deerfield's Four $248.00 $7,500.00 35

Seasons

842 All Season $8,221.00 $7,500.00 20

FIGURE 5-12 Conversion to third normal form

Have you now corrected all previously identified problems? A sales rep's name appears only once, thus
avoiding redundancy and simplifying the process of changing a sales rep's name. With this design, it is not pos-
sible for the same sales rep to have different names in the database. To add a new sales rep to the database,
you can add a row in the Rep table without requiring the rep to have at least one assigned customer. Finally,
deleting all the customers of a given sales rep will not remove the sales rep's record from the Rep table, retain-
ing the sales rep's name. In addition, you can reconstruct all the data in the original table from the data in the
new collection of tables. All previously mentioned problems have indeed been solved.