Database Design Using Normalization - Database Processing: Fundamentals, Design, and Implementation - page 163

• Advantages
Eliminate modification anomalies
Reduce duplicated data
• Eliminate data integrity problems
• Save file space
• Disadvantages

Figure 4-2
Advantages and
Disadvantages of
Normalization

More complicated SQL required for multitable

subqueries and joins

Extra work for DBMS can mean slower applications

Functional Dependencies
As we discussed in Chapter 3, we can eliminate anomalies due to functional dependencies
by placing all tables in BCNF. Most of the time, the problems of modification anomalies are
so great that you should put your tables into BCNF. There are exceptions, however, as you
will see.

Normalizing with SQL
As we explained in Chapter 3, a table is in BCNF if all determinants are candidate keys. If any
determinant is not a candidate key, we must break the table into two or more tables. Consider
an example. Suppose you are given the EQUIPMENT_REPAIR table in Figure 4-3 (the same
table shown in Figure 3-10). In Chapter 3, we found that ItemNumber is a determinant, but
not a candidate key. Consequently, we created the EQUIPMENT_ITEM and REPAIR tables
shown in Figure 4-4. In these tables, ItemNumber is a determinant and a candidate key of
EQUIPMENT_ITEM, and RepairNumber is a determinant and primary key of REPAIR; thus
both tables are in BCNF.
Now, as a practical matter, how do we transform the data in the format in Figure 4-3 to
that in Figure 4-4? To answer that question, we need to use the SQL INSERT statement . You
will learn the particulars of the INSERT statement in Chapter 7. For now, we will use one ver-
sion of it to illustrate the practical side of normalization.
First, we need to create the structure for the two new tables in Figure 4-4. If you are
using Microsoft Access, you can follow the procedure in Appendix A to create the tables.
Later, in Chapter 7, you will learn how to create tables using SQL, a process that works for
all DBMS products.
Once the tables are created, you can fill them using the SQL INSERT command. To fill the
ITEM table, we use:

/* *** SQL-INSERT-CH04-01 *** */
INSERT INTO EQUIPMENT_ITEM
SELECT DISTINCT ItemNumber, EquipmentType, AcquisitionCost

FROM EQUIPMENT_REPAIR;

EQUIPMENT_REPAIR

Figure 4-3
The EQUIPMENT_REPAIR
Table