The Denormalization Decision in Relational Database Systems Design - High-Performance Web Databases: Design, Development, and Deployment - page 673

Exhibit 49-5. An example of low FD usage.
WH#. fd1 in Exhibit 4 includes fd2 as a transitive functional dependency.
After being normalized to BCNF, WAREHOUSE_INFO is decomposed into
two relations, as shown in Exhibit 2 (i.e., WAREHOUSE and EMPLOYEE).
According to the previously explained method, the U-Ratio of the functional
dependencies is calculated as .92. This high U-Ratio implies that fd2 is used
quite frequently independently of fd1. Thus, WAREHOUSE_INFO should be
normalized into two relations (i.e., WAREHOUSE and EMPLOYEE).
Exhibit 5 demonstrates a different case. Even though attributes of state
and zip code in the relation PERSON_INFO are transitively functionally
dependent on SS#, the low U-Ratio of.21 shows that fd2 alone is not very
often used independently of fd1and thus that fd2 is not frequently used in
application programs. Consequently, there will be minimal update anoma-
lies even though the PERSON_INFO relation is not normalized into two rela-
tions (i.e., PERSON and CODE as shown in Exhibit 2b). Thus, PERSON_INFO
may not be decomposed into the relations, PERSON and CODE.
RECOMMENDED COURSE OF ACTION
Although normalization is based on functional dependency theory,
denormalization is often conducted without any consistent theory. Nor-
malization theory is based on functional dependencies in a relation. A
properly normalized model removes the potential update anomalies. How-
ever, an existing functional dependency may rarely be used in application
programs. Thus, the use of functional dependency should be addressed
when the denormalization decision is made. This chapter proposes a
method for making this denormalization decision, based on the idea of
functional dependencies.