Database Reference
In-Depth Information
which shows sample data extracted from a corporate database. This simple
table has three columns: the buyer's name, the SKU of the products that
the buyer purchases, and the names of the buyer's college major(s). Buyers
manage more than one SKU, and they can have multiple college majors.
To understand why this is an odd table, suppose that Nancy Meyers is
assigned a new SKU, say 101300. What addition should we make to this
table? Clearly, we need to add a row for the new SKU, but if we add just
one row, say the row ('Nancy Meyers', 101300, 'Art'), it will appear that she
manages product 101300 as an Art major, but not as an Info Systems major.
To avoid such an illogical state, we need to add two rows: ('Nancy Meyers',
101300, 'Art') and ('Nancy Meyers', 101300, 'Info Systems').
This is a strange requirement. Why should we have to add two rows
of data simply to record the fact that a new SKU has been assigned to a
buyer? Further, if we assign the product to Pete Hansen instead, we would
only have to add one row, but if we assigned the product to a buyer who
had four majors, we would have to add four new rows.
The more one thinks about the table in Figure 3-2, the more strange
it becomes. What changes should we make if SKU 101100 is assigned to
Pete Hansen? What changes should we make if SKU 100100 is assigned
to Nancy Meyers? What should we do if all the SKU values in Figure 3-2
are deleted? Later in this chapter, you will learn that these problems arise
because this table has a problem called a
multivalued dependency.
Even
better, you will learn how to remove that problem.
Tables can have many different patterns; some patterns are susceptible
to serious problems and other patterns are not. Before we can address this
question, however, you need to learn some basic terms.
Figure 3-3 lists the most important terms used by the relational model. By the time you
finish Chapters 3 and 4, you should be able to define each of these terms and explain how
each pertains to the design of relational databases. Use this list of terms as a check on your
comprehension.
Relations
So far, we have used the terms
table
and
relation
interchangeably. In fact, a relation is a special
case of a table. This means that all relations are tables, but not all tables are relations. Codd
defined the characteristics of a relation in his 1970 paper that laid the foundation for the rela-
tional model.
1
Those characteristics are summarized in Figure 3-4.
1
E. F. Codd, “A Relational Model of Data for Large Shared Databanks,”
Communications of the ACM
, June 1970,
pp. 377-387. A downloadable copy of this paper in PDF format is available at
http://dl.acm.org/citation.cfm?id=362685.
Search WWH ::
Custom Search