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

EQUIPMENT_ITEM

REPAIR

Figure 4-4
The Normalized
EQUIPMENT_ITEM and
REPAIR Relations

Notice that we must use the DISTINCT keyword because the combination (ItemNumber,
EquipmentType, AcquisitionCost) is not unique in the EQUIPMENT_REPAIR table. Once we
have created the rows in EQUIPMENT_ITEM, we can then use the following INSERT com-
mand to fill the rows of REPAIR:

/* *** SQL-INSERT-CH04-02 *** */
INSERT INTO REPAIR
SELECT RepairNumber, ItemNumber, RepairDate, RepairCost

FROM EQUIPMENT_REPAIR;

As you can see, the SQL statements for normalizing tables are relatively simple. After this
transformation, we should probably remove the EQUIPMENT_REPAIR table. For now, you can
do this using the graphical tools in Microsoft Access, SQL Server, Oracle Database, or MySQL.
In Chapter 7, you will learn how to remove tables using the SQL DROP TABLE statement .
You will also learn how to use SQL to create the referential integrity constraint:

REPAIR.ItemNumber must exist in ITEM.ItemNumber

If you want to try out this example, download the Microsoft Access 2013 database
Equipment-Repair-Database.accdb from the text's Web site at www.pearsonhighered.com/
kroenke . This database has the EQUIPMENT_REPAIR table with data. Create the new tables (see
Appendix A) and then do the normalization by executing the SQL INSERT statements illustrated.
This process can be extended to any number of tables. We will consider richer examples
of it in Chapter 7. For now, however, you should have the gist of the process.

Choosing Not to Use BCNF
Although in most cases the tables in an updatable database should be placed in BCNF, in some
situations BCNF is just too pure. The classic example of unneeded normalization involves
United States ZIP codes and similar postal codes in other countries (although, if fact, ZIP
codes do not always determine city and state). Consider the following table for customers in
the United States:

CUSTOMER (CustomerID, LastName, FirstName, Street, City, State, ZIP)

The functional dependencies of this table are:

CustomerID → (LastName, FirstName, Street, City, State, ZIP)
ZIP → (City, State)