Page 217 -

Chapter 5 Database Processing
216
weeks in a database management class. In this section, however, we will introduce two important
database design concepts: normalization and the representation of two kinds of relationships.
The first concept is a foundation of database design, and the second will help you understand
important design considerations.

Normalization

Normalization is the process of converting a poorly structured table into two or more well-
structured tables. A table is such a simple construct that you may wonder how one could pos-
sibly be poorly structured. In truth, there are many ways that tables can be malformed—so
many, in fact, that researchers have published hundreds of papers on this topic alone.
Consider the Employee table in Figure 5-23a. It lists employee names, hire dates, email
addresses, and the name and number of the department in which the employee works. This
table seems innocent enough. But consider what happens when the Accounting department
changes its name to Accounting and Finance. Because department names are duplicated in
this table, every row that has a value of “Accounting” must be changed to “Accounting and
Finance.”

Data Integrity Problems

Suppose the Accounting name change is correctly made in two rows, but not in the third. The
result is shown in Figure 5-23b. This table has what is called a data integrity problem: Some
rows indicate that the name of Department 100 is “Accounting and Finance,” and another row
indicates that the name of Department 100 is “Accounting.”
This problem is easy to spot in this small table. But consider a table like the Customer table
in the Amazon.com database or the eBay database. Those databases have millions of rows. Once
a table that large develops serious data integrity problems, months of labor will be required to
remove them.
Data integrity problems are serious. A table that has data integrity problems will produce
incorrect and inconsistent results. Users will lose confidence in the data, and the system will
develop a poor reputation. Information systems with poor reputations become serious burdens to
the organizations that use them.

Employee
Name HireDate Email DeptNo DeptName
Jones Feb 1, 2010 Jones@ourcompany.com 100 Accounting
Smith Dec 3, 2012 Smith@ourcompany.com 200 Marketing
Chau March 7, 2012 Chau@ourcompany.com 100 Accounting
Greene July 17, 2011 Greene@ourcompany.com 100 Accounting
(a) Table Before Update

Employee
Name HireDate Email DeptNo DeptName
Jones Feb 1, 2010 Jones@ourcompany.com 100 Accounting and Finance
Smith Dec 3, 2012 Smith@ourcompany.com 200 Marketing
Chau March 7, 2012 Chau@ourcompany.com 100 Accounting and Finance
Figure 5-23 Greene July 17, 2011 Greene@ourcompany.com 100 Accounting
A Poorly Designed
Employee Table (b) Table with Incomplete Update