Database Reference
In-Depth Information
What about relationships? At Premiere Products, there is a one-to-many relationship between sales reps
and customers. (Each sales rep is related to the many customers he or she represents, and each customer is
related to the one sales rep who represents it.) How is this relationship implemented in a relational database?
The answer is through common columns in two or more tables. Consider Figure 2-1 again. The RepNum col-
umns in the Rep and Customer tables implement the relationship between sales reps and customers. For any
sales rep, you can use these columns to determine all the customers the sales rep represents; for any cus-
tomer, you can use these columns to find the sales rep who represents the customer. If the Customer table
did not include the sales rep number, you would not be able to identify the sales rep for a given customer
and the customers for a given sales rep.
More formally, a relation is essentially just a two-dimensional table. If you consider the tables shown in
Figure 2-1, however, you might see certain restrictions that you can place on relations. Each column in a
table should have a unique name, and all entries in each column should be consistent with this column
name. For example, in the CreditLimit column, all entries should, in fact, be credit limits. In addition, each
row should be unique. After all, when two rows in a table contain identical data, the second row doesn
33
'
t pro-
vide any information that you don
t already have. In addition, for maximum flexibility, the order in which
columns and rows appear in a table should be immaterial. Finally, a table
'
s design is less complex when you
restrict each location in the table to a single value; that is, you should not permit multiple entries (often
called repeating groups) in the table. These ideas lead to the following definitions.
'
Definition:
A relation is a two-dimensional table in which:
1. The entries in the table are single-valued; that is, each location in the table contains a single
value.
2. Each column has a distinct name (technically called the attribute name).
3. All values in a column are values of the same attribute (that is, all entries must match the
column name).
4. The order of columns is immaterial.
5. Each row is distinct.
6. The order of rows is immaterial.
Definition:
A relational database is a collection of relations.
Later in this text, you will encounter situations in which a structure satisfies all the properties of a
relation except for the first item; that is, some of the entries contain repeating groups and, thus, are not
single-valued. Such a structure is called an unnormalized relation. This jargon is a little strange in that an
unnormalized relation is really not a relation at all. This term is used for such a structure, however. The
table shown in Figure 2-2 is an example of an unnormalized relation.
Orders
OrderNum OrderDate CustomerNum PartNum NumOrdered QuotedPrice
21608
10/20/2013
148
AT94
11
$21.95
21610
10/20/2013
356
DR93
1
$495.00
DW11
1
$399.99
21613
10/21/2013
408
KL62
4
$329.95
21614
10/21/2013
282
KT03
2
$595.00
21617
10/23/2013
608
BV06
2
$794.95
CD52
4
$150.00
21619
10/23/2013
148
DR93
1
$495.00
21623
10/23/2013
608
KV29
2
$1,290.00
FIGURE 2-2
Sample structure of an unnormalized relation
