Page 253 -
P. 253
252 Part Two Information Technology Infrastructure
FIGURE 6.9 AN UNNORMALIZED RELATION FOR ORDER
An unnormalized relation contains repeating groups. For example, there can be many parts and suppliers for each order. There is
only a one-to-one correspondence between Order_Number and Order_Date.
Normalization and Entity-Relationship Diagrams
The conceptual database design describes how the data elements in the
database are to be grouped. The design process identifies relationships among
data elements and the most efficient way of grouping data elements together
to meet business information requirements. The process also identifies
redundant data elements and the groupings of data elements required for
specific application programs. Groups of data are organized, refined, and
streamlined until an overall logical view of the relationships among all the
data in the database emerges.
To use a relational database model effectively, complex groupings of data
must be streamlined to minimize redundant data elements and awkward
many-to-many relationships. The process of creating small, stable, yet
flexible and adaptive data structures from complex groups of data is called
normalization. Figures 6.9 and 6.10 illustrate this process.
In the particular business modeled here, an order can have more than one
part but each part is provided by only one supplier. If we build a relation called
ORDER with all the fields included here, we would have to repeat the name
and address of the supplier for every part on the order, even though the order
is for parts from a single supplier. This relationship contains what are called
repeating data groups because there can be many parts on a single order to
a given supplier. A more efficient way to arrange the data is to break down
ORDER into smaller relations, each of which describes a single entity. If we
go step by step and normalize the relation ORDER, we emerge with the rela-
tions illustrated in Figure 6.10. You can find out more about normalization,
FIGURE 6.10 NORMALIZED TABLES CREATED FROM ORDER
After normalization, the original relation ORDER has been broken down into four smaller relations. The relation ORDER is left with only two
attributes and the relation LINE_ITEM has a combined, or concatenated, key consisting of Order_Number and Part_Number.
MIS_13_Ch_06 Global.indd 252 1/17/2013 2:27:43 PM