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

This table is not in BCNF because ZIP is a determinant that is not a candidate key. We can
normalize this table as follows:
CUSTOMER_2 ( CustomerID , LastName, FirstName, Street, ZIP )
ZIP_CODE ( ZIP , City, State)
with referential integrity constraint:
CUSTOMER_2.ZIP must exist in ZIP_CODE.ZIP
The tables CUSTOMER_2 and ZIP_CODE are in BCNF, but consider these tables in light
of the advantages and disadvantages of normalization listed in Figure 4-2. Normalization elim-
inates modification anomalies, but how often does ZIP code data change? How often does the
post office change the city and state assigned to a ZIP code value? Almost never. The conse-
quences on every business and person would be too severe. So, even though the design allows
anomalies to occur, in practice, they will not occur because the data never change. Consider
the second advantage: Normalization reduces data duplication and hence improves data
integrity. In fact, data integrity problems can happen in the single-table example if someone
enters the wrong value for City, State, or ZIP. In that case, the database will have inconsistent
ZIP values. But normal business processes will cause ZIP code errors to be noticed, and they
will be corrected without problem.
Now consider the disadvantages of normalization. Two separate tables require applica-
tion programs to write more complex SQL. They also require the DBMS to process two tables,
which may make the applications slow. Weighing the advantages and disadvantages, most
practitioners would say that the normalized data are just too pure. ZIP code data would there-
fore be left in the original table.
In summary, when you design an updatable database from existing tables, examine every
table to determine if it is in BCNF. If it is not, then the table is susceptible to modification
anomalies and inconsistent data. In almost all cases, transform the table into tables that are
in BCNF. However, if the data are never modified and if data inconsistencies will be easily
corrected via the normal operation of business activity, then you may choose not to place the
table into BCNF.
Multivalued Dependencies
Unlike functional dependencies, the anomalies from multivalued dependencies are so serious
that multivalued dependencies should always be eliminated. Unlike BCNF, there is no gray
area. Just place the columns of a multivalued dependency in tables of their own.
As shown in the last section, normalization is not difficult. It does mean that application
programmers will have to write subqueries and joins to re-create the original data. Writing
subqueries and joins, however, is nothing compared with the complexity of code that must be
written to handle the anomalies due to multivalued dependencies.
Some experts might object to such a hard and fast rule, but it is justifiable. Although
there may be a few rare, obscure, and weird cases in which multivalued dependencies are not
problematic, such cases are not worth remembering. Until you have years of database design
experience, always eliminate multivalued dependencies from any updatable table.
Designing Read-Only Databases
In the course of your career, you will likely be given tables of data and asked to create a read-
only database. In fact, this task is commonly assigned to beginning database administrators.
Read-only databases are used in business intelligence (BI) systems for querying, reporting,
and data mining applications, as you will learn in Chapter 12. Because such databases are up-
dated by carefully controlled and timed procedures, the design guidelines and design priorities
are different than those for operational databases that are frequently updated.