Page 218 -

Q5-6 How Is a Data Model Transformed into a Database Design?

Employee 217
Name HireDate Email DeptNo
Jones Feb 1, 2010 Jones@ourcompany.com 100
Smith Dec 3, 2012 Smith@ourcompany.com 200
Chau March 7, 2012 Chau@ourcompany.com 100
Greene July 17, 2011 Greene@ourcompany.com 100

Department
DeptNo DeptName
100 Accounting
200 Marketing
Figure 5-24 300 Information Systems
Two Normalized Tables

Normalizing for Data Integrity

The data integrity problem can occur only if data are duplicated. Because of this, one easy way to
eliminate the problem is to eliminate the duplicated data. We can do this by transforming the table
design in Figure 5-23a into two tables, as shown in Figure 5-24. Here the name of the department
is stored just once; therefore, no data inconsistencies can occur.
Of course, to produce an employee report that includes the department name, the two tables in
Figure 5-24 will need to be joined back together. Because such joining of tables is common, DBMS
products have been programmed to perform it efficiently, but it still requires work. From this example,
you can see a trade-off in database design: Normalized tables eliminate data duplication, but they can
be slower to process. Dealing with such trade-offs is an important consideration in database design.
The general goal of normalization is to construct tables such that every table has a single topic
or theme. In good writing, every paragraph should have a single theme. This is true of databases
as well; every table should have a single theme. The problem with the table design in Figure 5-23 is
that it has two independent themes: employees and departments. The way to correct the problem is
to split the table into two tables, each with its own theme. In this case, we create an Employee table
and a Department table, as shown in Figure 5-24.
As mentioned, there are dozens of ways that tables can be poorly formed. Database
practitioners classify tables into various normal forms according to the kinds of problems they
have. Transforming a table into a normal form to remove duplicated data and other problems
3
is called normalizing the table. Thus, when you hear a database designer say, “Those tables are
not normalized,” she does not mean that the tables have irregular, not-normal data. Instead, she
means that the tables have a format that could cause data integrity problems.

Summary of Normalization

As a future user of databases, you do not need to know the details of normalization. Instead,
understand the general principle that every normalized (well-formed) table has one and only one
theme. Further, tables that are not normalized are subject to data integrity problems.
Be aware, too, that normalization is just one criterion for evaluating database designs.
Because normalized designs can be slower to process, database designers sometimes choose to
accept non-normalized tables. The best design depends on the users’ processing requirements.

Representing Relationships

Figure 5-25 shows the steps involved in transforming a data model into a relational database
design. First, the database designer creates a table for each entity. The identifier of the entity
becomes the key of the table. Each attribute of the entity becomes a column of the table. Next, the