We will illustrate the SQL SELECT command with the General Hardware
Co. database of Figure 4.1, which is derived from the General Hardware

F I G U R E 4.1
The General Hardware Company relational
database

(a) SALESPERSON table

SPNUM SPNAME COMMPERCT YEARHIRE OFFNUM

137 Baker 10 1995 1284

186 Adams 15 2001 1253

204 Dickens 10 1998 1209

361 Carlyle 20 2001 1227

(b)

CUSTOMER table

CUSTNUM CUSTNAME SPNUM

HQCITY

0121 Main St. Hardware 137 New York

0839 Jane’s Stores 186 Chicago

0933 ABC Home Stores 137 Los Angeles

1047 Acme Hardware Store 137 Los Angeles

1525 Fred’s Tool Stores 361 Atlanta

1700 XYZ Stores 361 Washington

1826 City Hardware 137 New York
2198 Western Hardware 204 New York

2267 Central Stores 186 New York

(c) CUSTOMER EMPLOYEE table

CUSTNUM EMPNUM EMPNAME TITLE

0121 27498 Smith Co-Owner

0121 30441 Garcia Co-Owner

0933 25270 Chen VP Sales

0933 30441 Levy Sales Manager

0933 48285 Morton President

1525 33779 Baker Sales Manager

2198 27470 Smith President

2198 30441 Jones VP Sales

2198 33779 Garcia VP Personnel

2198 35268 Kaplan Senior Accountant

(Continues)

72 C h a p t e r 4 Relational Data Retrieval: SQL

F I G U R E 4.1 (Continued)
The General Hardware Company relational
database

(d) PRODUCT table

PRODNUM PRODNAME UNITPRICE

16386 Wrench 12.95

19440 Hammer 17.50

21765 Drill 32.99

24013 Saw 26.25

26722 Pliers 11.50

(e) SALES table

SPNUM PRODNUM QUANTITY

137 19440 473

137 24013

170

137 26722 688

186 16386 1745

186 19440 2529

186 21765 1962

186 24013 3071

204 21765

809

204 26722 734

361 16386 3729

361 21765

3110

361 26722

2738

(f) OFFICE Table

OFFNUM TELEPHONE SIZE

1253 901-555-4276 1

20

1227 901-555-0364 100

1284 901-555-7335 120

1209 901-555-3108 95

entity-relationship diagram of Figure 2.9. If you have not as yet covered the
database design chapters in this book, just keep in mind that some of the columns
are present to tie together related data from different tables, as discussed in Chapter
3. For example, the SPNUM column in the CUSTOMER table is present to tie
together related salespersons and customers.

As is traditional with SQL, the SQL statements will be shown in all capital
letters, except for data values taken from the tables. Note that the attribute names in
Figure 4.1 have been abbreviated for convenience and set in capital letters to make
them easily recognizable in the SQL statements. Also, spaces in the names have
been removed. Using the General Hardware database, an example of a simple query
that demonstrates the basic SELECT format is:

‘‘Find the commission percentage and year of hire of salesperson number 186.’’

Data Retrieval with the SQL SELECT Command 73

The SQL statement to accomplish this would be:

SELECT COMMPERCT, YEARHIRE

FROM

SALESPERSON

WHERE SPNUM=186;

How is this command constructed? The desired attributes are listed in the SELECT
clause, the required table is listed in the FROM clause, and the restriction or
predicate indicating which row(s) is involved is shown in the WHERE clause in
the form of an equation. Notice that SELECT statements always end with a single
semicolon (;) at the very end of the entire statement.

The result of this statement is:

COMMPERCT YEARHIRE

15 2001

As is evident from this query, an attribute like SPNUM that is used to search
for the required rows, also known as a ‘‘search argument,’’ does not have to appear
in the query result, as long as its absence does not make the result ambiguous,
confusing, or meaningless.

To retrieve the entire record for salesperson 186, the statement would change
to:

SELECT *
FROM SALESPERSON
WHERE SPNUM=186;

resulting in:

SPNUM SPNAME COMMPERCT YEARHIRE OFFNUM
186 Adams 15 2001 1253

The ‘‘*’’ in the SELECT clause indicates that all attributes of the selected
row are to be retrieved. Notice that this retrieval of an entire row of the table is, in
fact, a relational Select operation (see Chapter 5)! A relational Select operation can
retrieve one or more rows of a table, depending, in this simple case, on whether
the search argument is a unique or non-unique attribute. The search argument is
non-unique in the following query:

‘‘List the salesperson numbers and salesperson names of those salespersons
who have a commission percentage of 10.’’

SELECT SPNUM,

SPNAME

FROM SALESPERSON

WHERE COMMPERCT=10;

which

results in:

SPNUM SPNAME

137 Baker

204

Dickens

74 C h a p t e r 4 Relational Data Retrieval: SQL

The SQL SELECT statement can also be used to accomplish a relational
Project operation. This is a vertical slice through a table involving all rows and
some attributes. Since all of the rows are included in the Project operation, there
is no need for a WHERE clause to limit which rows of the table are included. For
example,

‘‘List the salesperson number and salesperson name of all of the salespersons.’’

SELECT SPNUM, SPNAME

FROM SALESPERSON;

results in:
SPNUM SPNAME
137 Baker

186 Adams

204 Dickens

361

Carlyle

To retrieve an entire table, that is to design an SQL SELECT statement that
places no restrictions on either the rows or the attributes, you would issue:

SELECT *
FROM SALESPERSON;

and have as the result:

SPNUM SPNAME COMMPERCT YEARHIRE OFFNUM
137 Baker 10 1995 1284
186 Adams 15 2001 1253
204 Dickens 10 1998 1209
361 Carlyle 20 2001 1227

Comparisons In addition to equal (=), the standard comparison operators, greater
than (>), less than (<), greater than or equal to (>=), less than or equal to (<=), and not equal to (<>) can be used in the WHERE clause.

‘‘List the salesperson numbers, salesperson names, and commission percentages
of the salespersons whose commission percentage is less than 12.’’

SELECT SPNUM, SPNAME, COMMPERCT
FROM SALESPERSON
WHERE COMMPERCT<12;

This results in:

SPNUM SPNAME COMMPERCT

137 Baker 10

204 Dickens 10

Data Retrieval with the SQL SELECT Command 75

As another example:

‘‘List the customer numbers and headquarters cities of the customers that have
a customer number of at least 1700.’’

SELECT CUSTNUM, HQCITY
FROM

CUSTOMER

WHERE CUSTNUM>=1700;

results in:

CUSTNUM HQCITY

1700 Washington

1826 New York

2198 New York

2267 New York

ANDs and ORs Frequently, there is a need to specify more than one limiting
condition on a table’s rows in a query. Sometimes, for a row to be included in
the result it must satisfy more than one condition. This requires the Boolean AND
operator. Sometimes a row can be included if it satisfies one of two or more
conditions. This requires the Boolean OR operator.

AND An example in which two conditions must be satisfied is:
‘‘List the customer numbers, customer names, and headquarters cities of the

customers that are headquartered in New York and that have a customer number
higher than 1500.’’

SELECT CUSTNUM, CUSTNAME, HQCITY
FROM CUSTOMER
WHERE HQCITY=‘New York’
AND CUSTNUM>1500;

resulting in:

CUSTNUM CUSTNAME HQCITY

1826 City Hardware New York
2198 Western Hardware New York
2267 Central Stores New York

Notice that customer number 0121, which is headquartered in New York, was
not included in the results because it failed to satisfy the condition of having a
customer number greater than 1500. With the AND operator, it had to satisfy both
conditions to be included in the result.

OR To look at the OR operator, let’s change the last query to:
‘‘List the customer numbers, customer names, and headquarters cities of the

customers that are headquartered in New York or that have a customer number
higher than 1500.’’

76 C h a p t e r 4 Relational Data Retrieval: SQL

SELECT CUSTNUM, CUSTNAME, HQCITY
FROM CUSTOMER
WHERE HQCITY=‘New York’
OR CUSTNUM>1500;

results in:
CUSTNUM CUSTNAME HQCITY
0121 Main St. Hardware New York
1525 Fred’s Tool Stores Atlanta
1700 XYZ Stores Washington
1826 City Hardware New York
2198 Western Hardware New York
2267 Central Stores New York

Notice that the OR operator really means one or the other or both. Customer
0121 is included because it is headquartered in New York. Customers 1525 and 1700
are included because they have customer numbers higher than 1500. Customers
1826, 2198, and 2267 are included because they satisfy both conditions.

Both AND and OR What if both AND and OR are specified in the same WHERE
clause? AND is said to be ‘‘higher in precedence’’ than OR, and so all ANDs are
considered before any ORs are considered. The following query, which has to be
worded very carefully, illustrates this point:

‘‘List the customer numbers, customer names, and headquarters cities of the
customers that are headquartered in New York or that satisfy the two conditions
of having a customer number higher than 1500 and being headquartered in
Atlanta.’’

SELECT CUSTNUM, CUSTNAME, HQCITY
FROM CUSTOMER
WHERE HQCITY=‘New York’
OR CUSTNUM>1500
AND HQCITY=‘Atlanta’;

The result of this query is:

CUSTNUM CUSTNAME HQCITY
0121 Main St. Hardware New York
1525 Fred’s Tool Stores Atlanta
1826 City Hardware New York
2198 Western Hardware New York
2267 Central Stores New York

Notice that since the AND is considered first, one way for a row to qualify
for the result is if its customer number is greater than 1500 and its headquarters city
is Atlanta. With the AND taken first, it’s that combination or the headquarters city
has to be New York. Considering the OR operator first would change the whole

Data Retrieval with the SQL SELECT Command 77

complexion of the statement. The best way to deal with this, especially if there are
several ANDs and ORs in a WHERE clause, is by using parentheses. The rule is that
anything in parentheses is done first. If the parentheses are nested, then whatever
is in the innermost parentheses is done first and then the system works from there
towards the outermost parentheses. Thus, a ‘‘safer’’ way to write the last SQL
statement would be:

SELECT CUSTNUM, CUSTNAME, HQCITY
FROM CUSTOMER
WHERE HQCITY=‘New York’
OR (CUSTNUM>1500
AND HQCITY=‘Atlanta’);

If you really wanted the OR to be considered first, you could force it by writing
the query as:

SELECT CUSTNUM, CUSTNAME, HQCITY
FROM CUSTOMER
WHERE (HQCITY=‘New York’
OR CUSTNUM>1500)
AND HQCITY=‘Atlanta’;

This would mean that, with the AND outside of the parentheses, both of two
conditions have to be met for a row to qualify for the results. One condition is that
the headquarters city is New York or the customer number is greater than 1500.
The other condition is that the headquarters city is Atlanta. Since for a given row,
the headquarters city can’t be both Atlanta and New York, the situation looks grim.
But, in fact, customer number 1525 qualifies. Its customer number is greater than
1500, which satisfies the OR of the first of the two conditions, and its headquarters
city is Atlanta, which satisfies the second condition. Thus, both conditions are met
for this and only this row.

BETWEEN, IN, and LIKE BETWEEN, IN, and LIKE are three useful operators.
BETWEEN allows you to specify a range of numeric values in a search. IN allows
you to specify a list of character strings to be included in a search. LIKE allows you
to specify partial character strings in a ‘‘wildcard’’ sense.

BETWEEN Suppose that you want to find the customer records for those customers
whose customer numbers are between 1000 and 1700 inclusive (meaning that both
1000 and 1700, as well as all numbers in between them, are included). Using the
AND operator, you could specify this as:

SELECT *
FROM CUSTOMER
WHERE (CUSTNUM>=1000
AND CUSTNUM>=1700);

Or, you could use the BETWEEN operator and specify it as:

SELECT *
FROM CUSTOMER
WHERE CUSTNUM BETWEEN 1000 AND 1700;

78 C h a p t e r 4 Relational Data Retrieval: SQL

With either way of specifying it, the result would be:

CUSTNUM CUSTNAME SPNUM HQCITY
1047 Acme Hardware Store 137 Los Angeles
1525 Fred’s Tool Stores 361 Atlanta
1700 XYZ Stores 361 Washington

IN Suppose that you want to find the customer records for those customers
headquartered in Atlanta, Chicago, or Washington. Using the OR operator, you
could specify this as:

SELECT *
FROM CUSTOMER
WHERE (HQCITY=‘Atlanta’
OR HQCITY=‘Chicago’
OR HQCITY=‘Washington’);

Or, you could use the IN operator and specify it as:

SELECT *
FROM CUSTOMER
WHERE HQCITY IN (‘Atlanta’, ‘Chicago’, ‘Washington’);

With either way of specifying it, the result would be:
CUSTNUM CUSTNAME SPNUM HQCITY
0839 Jane’s Stores 186 Chicago
1525 Fred’s Tool Stores 361 Atlanta
1700 XYZ Stores 361 Washington

LIKE Suppose that you want to find the customer records for those customers whose
names begin with the letter ‘‘A’’. You can accomplish this with the LIKE operator
and the ‘‘%’’ character used as a wildcard to represent any string of characters.
Thus, ‘A%’ means the letter ‘‘A’’ followed by any string of characters, which is the
same thing as saying ‘any word that begins with ‘‘A’’.’

SELECT *
FROM CUSTOMER
WHERE CUSTNAME LIKE ‘A%’;

The result would be:

CUSTNUM CUSTNAME SPNUM HQCITY
0933 ABC Home Stores 137 Los Angeles
1047 Acme Hardware Store 137 Los Angeles

Note that, unlike BETWEEN and IN, there is no easy alternative way in SQL
of accomplishing what LIKE can do.

In a different kind of example, suppose that you want to find the customer
records for those customers whose names have the letter ‘‘a’’ as the second letter of
their names. Could you specify ‘%a%’? No, because the ‘%a’ portion of it would

Data Retrieval with the SQL SELECT Command 79

mean any number of letters followed by ‘‘a’’, which is not what you want. In
order to make sure that there is just one character followed by ‘‘a’’, which is the
same thing as saying that ‘‘a’’ is the second letter, you would specify ‘_a%’. The
‘‘_’’ wildcard character means that there will be exactly one letter (any one letter)
followed by the letter ‘‘a’’. The ‘‘%’’, as we already know, means that any string
of characters can follow afterwards.

SELECT *
FROM CUSTOMER
WHERE CUSTNAME LIKE ‘_a%’;

The result would be:
CUSTNUM CUSTNAME SPNUM HQCITY
0121 Main St. Hardware 137 New York
0839 Jane’s Stores 186 Chicago

Notice that both the words ‘‘Main’’ and ‘‘Jane’s’’ have ‘‘a’’ as their second
letter. Also notice that, for example, customer number 2267 was not included in the
result. Its name, ‘‘Central Stores’’, has an ‘‘a’’ in it but it is not the second letter
of the name. Again, the single ‘‘_’’ character in the operator LIKE ‘_a%’ specifies
that there will be one character followed by ‘‘a’’. If the operator had been LIKE
‘%a%’, then Central Stores would have been included in the result.

Filtering the Results of an SQL Query Two ways to modify the results of an SQL SELECT
command are by the use of DISTINCT and the use of ORDER BY. It is important
to remember that these two devices do not affect what data is retrieved from the
database but rather how the data is presented to the user.

DISTINCT There are circumstances in which the result of an SQL query may contain
duplicate items and this duplication is undesirable. Consider the following query:

‘‘Which cities serve as headquarters cities for General Hardware customers?’’

This could be taken as a simple relational Project that takes the HQCITY column of
the CUSTOMER table as its result. The SQL command would be:

SELECT HQCITY
FROM CUSTOMER;

which results in:

HQCITY New York

Chicago
Los Angeles
Los Angeles
Atlanta
Washington
New York
New York
New York

80 C h a p t e r 4 Relational Data Retrieval: SQL

Technically, this is the correct result, but why is it necessary to list New York
four times or Los Angeles twice? Not only is it unnecessary to list them more than
once, but doing so produces unacceptable clutter. Based on the way the query was
stated, the result should have each city listed once. The DISTINCT operator is used
to eliminate duplicate rows in a query result. Reformulating the SELECT statement
as:

SELECT DISTINCT HQCITY
FROM CUSTOMER;

results in:
HQCITY
New York
Chicago
Los Angeles
Atlanta
Washington

ORDER BY The ORDER BY clause simply takes the results of an SQL query and
orders them by one or more specified attributes. Consider the following query:

‘‘Find the customer numbers, customer names, and headquarters cities of
those customers with customer numbers greater than 1000. List the results in
alphabetic order by headquarters cities.’’

SELECT CUSTNUM, CUSTNAME, HQCITY
FROM CUSTOMER
WHERE CUSTNUM>1000
ORDER BY HQCITY;

This results in:
CUSTNUM CUSTNAME HQCITY
1525 Fred’s Tool Stores Atlanta
1047 Acme Hardware Store Los Angeles
1826 City Hardware New York
2198 Western Hardware New York
2267 Central Stores New York
1700 XYZ Stores Washington

If you wanted to have the customer names within the same city alphabetized,
you would write:

SELECT CUSTNUM, CUSTNAME, HQCITY
FROM CUSTOMER
WHERE CUSTNUM>1000
ORDER BY HQCITY, CUSTNAME;

Data Retrieval with the SQL SELECT Command 81

This results in:
CUSTNUM CUSTNAME HQCITY
1525 Fred’s Tool Stores Atlanta
1047 Acme Hardware Store Los Angeles
2267 Central Stores New York
1826 City Hardware New York
2198 Western Hardware New York
1700 XYZ Stores Washington

The default order for ORDER BY is ascending. The clause can include the
term ASC at the end to make ascending explicit or it can include DESC for
descending order.

Built-In Functions

A number of so-called ‘‘built-in functions’’ give the SQL SELECT command
additional capabilities. They involve the ability to perform calculations based on
attribute values or to count the number of rows that satisfy stated criteria.

AVG and SUM Recall that the SALES table shows the lifetime quantity of particular
products sold by particular salespersons. For example, the first row indicates that
Salesperson 137 has sold 473 units of Product Number 19440 dating back to when
she joined the company or when the product was introduced. Consider the following
query:

‘‘Find the average number of units of the different products that Salesperson
137 has sold (i.e., the average of the quantity values in the first three records of
the SALES table).’’

Using the AVG operator, you would write:

SELECT

AVG(QUANTITY)

FROM

SALES

WHERE SPNUM=137;

and the result would be:

AVG(QUANTITY)

443.67

To find the total number of units of all products that she has sold, you would
use the SUM operator and write:

SELECT

SUM(QUANTITY)

FROM SALES
WHERE SPNUM=137;

and the result would be:
SUM(QUANTITY)

1331

82 C h a p t e r 4 Relational Data Retrieval: SQL

MIN and MAX You can also find the minimum or maximum of a set of attribute
values. Consider the following query:

‘‘What is the largest number of units of Product Number 21765 that any
individual salesperson has sold?’’

Using the MAX operator, you would write:

SELECT

MAX(QUANTITY)

FROM SALES
WHERE PRODNUM=21765;

and the result would be:
MAX(QUANTITY)
3110

To find the smallest number of units you simply replace MAX with MIN:

SELECT

MIN(QUANTITY)

FROM SALES
WHERE PRODNUM=21765;

and get:

MIN(QUANTITY)
809

COUNT COUNT is a very useful operator that counts the number of rows that
satisfy a set of criteria. It is often used in the context of ‘‘how many of something’’
meet some stated conditions. Consider the following query:

‘‘How many salespersons have sold Product Number 21765?’’

Remember that each row of the SALES table describes the history of a particular
salesperson selling a particular product. That is, each combination of

SPNUM

and PRODNUM is unique; there can only be one row that involves a particular
SPNUM/PRODNUM combination. If you can count the number of rows of that
table that involve Product Number 21765, then you know how many salespersons
have a history of selling it. Using the notational device COUNT(*), the SELECT
statement is:

SELECT

COUNT(*)

FROM SALES
WHERE PRODNUM=21765;

and the answer is:

COUNT(*)
3

Data Retrieval with the SQL SELECT Command 83

Don’t get confused by the difference between SUM and COUNT. As we
demonstrated above, SUM adds up a set of attribute values; COUNT counts the
number of rows of a table that satisfy a set of stated criteria.

Grouping Rows

Using the built-in functions, we were able to calculate results based on attribute
values in several rows of a table. In effect, we formed a single ‘‘group’’ of rows and
performed some calculation on their attribute values. There are many situations that
require such calculations to be made on several different groups of rows. This is a
job for the GROUP BY clause.

GROUP BY A little earlier we found the total number of units of all products that
one particular salesperson has sold. It seems reasonable that at some point we might
want to find the total number of units of all products that each salesperson has sold.
That is, we want to group together the rows of the SALES table that belong to each
salesperson and calculate a value—the sum of the Quantity attribute values in this
case—for each such group. Here is how such a query might be stated:

‘‘Find the total number of units of all products sold by each salesperson.’’

The SQL statement, using the GROUP BY clause, would look like this:

SELECT SPNUM, SUM(QUANTITY)
FROM SALES
GROUP BY SPNUM;

and the results would be:

SPNUM SUM(QUANTITY)

137

1331

186

9307

204

1543

361

9577

Notice that GROUP BY SPNUM specifies that the rows of the table are to
be grouped together based on having the same value in their SPNUM attribute.
All the rows for Salesperson Number 137 will form one group, all of the rows for
Salesperson Number 186 will form another group, and so on. The Quantity attribute
values in each group will then be summed—SUM(QUANTITY)—and the results
returned to the user. But it is not enough to provide a list of sums:

1331
9307
1543
9577

These are indeed the sums of the quantities for each salesperson But, without
identifying which salesperson goes with which sum, they are meaningless! That’s

84 C h a p t e r 4 Relational Data Retrieval: SQL

why the SELECT clause includes both the SPNUM and the SUM(QUANTITY).
Including the attribute(s) specified in the GROUP BY clause in the SELECT clause
allows you to properly identify the sums calculated for each group.

An SQL statement with a GROUP BY clause may certainly also include a
WHERE clause. Thus, the query:

‘‘Find the total number of units of all products sold by each salesperson whose
salesperson number is at least 150.’’

would look like:

SELECT SPNUM, SUM(QUANTITY)
FROM SALES
WHERE SPNUM>=150
GROUP BY SPNUM;

and the results would be:
SPNUM SUM(QUANTITY)

186 9307

204 1543

361 9577

HAVING Sometimes there is a need to limit the results of a GROUP BY based on
the values calculated for each group with the built-in functions. For example, take
the last query above,

‘‘Find the total number of units of all products sold by each salesperson whose
salesperson number is at least 150.’’

Now modify it with an additional sentence so that it reads:

‘‘Find the total number of units of all products sold by each salesperson
whose salesperson number is at least 150. Include only salespersons whose total
number of units sold is at least 5000.’’

This would be accomplished by adding a HAVING clause to the end of the SELECT
statement:

SELECT SPNUM, SUM(QUANTITY)
FROM SALES
WHERE SPNUM>=150
GROUP BY SPNUM
HAVING SUM(QUANTITY)>=5000;

and the results would be:
SPNUM SUM(QUANTITY)
186 9307
361 9577

with Salesperson Number 204, with a total of only 1543 units sold, dropping out of
the results.

Data Retrieval with the SQL SELECT Command 85

Notice that in this last SELECT statement, there are two limitations One,
that the Salesperson Number must be at least 150, appears in the WHERE clause
and the other, that the sum of the number of units sold must be at least 5000,
appears in the HAVING clause. It is important to understand why this is so. If the
limitation is based on individual attribute values that appear in the database, then
the condition goes in the WHERE clause. This is the case with the limitation based
on the Salesperson Number value. If the limitation is based on the group calculation
performed with the built-in function, then the condition goes in the HAVING clause.
This is the case with the limitation based on the sum of the number of product units
sold.

The Join

Up to this point, all the SELECT features we have looked at have been shown in
the context of retrieving data from a single table. The time has come to look at how
the SQL SELECT command can integrate data from two or more tables or ‘‘join’’
them. There are two specifications to make in the SELECT statement to make a
join work. One is that the tables to be joined must be listed in the FROM clause.
The other is that the join attributes in the tables being joined must be declared and
matched to each other in the WHERE clause. And there is one more point. Since two
or more tables are involved in a SELECT statement that involves a join, there is the
possibility that the same attribute name can appear in more than one of the tables.
When this happens, these attribute names must be ‘‘qualified’’ with a table name
when used in the SELECT statement. All of this is best illustrated in an example.

Consider the following query, which we discussed earlier in this book:

‘‘Find the name of the salesperson responsible for Customer Number 1525.’’

The SELECT statement to satisfy this query is:

SELECT SPNAME

FROM SALESPERSON, CUSTOMER

WHERE SALESPERSON.SPNUM=CUSTOMER.SPNUM

AND CUSTNUM=1525;

and the result is:

SPNAME
Carlyle

Let’s take a careful look at this last SELECT statement. Notice that the two
tables involved in the join, SALESPERSON and CUSTOMER, are listed in the
FROM clause. Also notice that the first line of the WHERE clause:

SALESPERSON.SPNUM = CUSTOMER.SPNUM
links the two join attributes: the SPNUM attribute of the SALESPERSON table
(SALESPERSON.SPNUM) and the SPNUM attribute of the CUSTOMER table
(CUSTOMER.SPNUM). The notational device of having the table name ‘‘.’’ the
attribute name is known as ‘‘qualifying’’ the attribute name. As we said earlier,
this qualification is necessary when the same attribute name is used in two or more
tables in a SELECT statement. By the way, notice in the SELECT statement that

86 C h a p t e r 4 Relational Data Retrieval: SQL

the attributes SPNAME and CUSTNUM don’t have to be qualified because each
appears in only one of the tables included in the SELECT statement.

Here is an example of a join involving three tables, assuming for the moment
that salesperson names are unique:

‘‘List the names of the products of which salesperson Adams has sold more
than 2000 units.’’

The salesperson name data appears only in the SALESPERSON table and the
product name data appears only in the PRODUCT table. The SALES table shows
the linkage between the two, including the quantities sold. And so the SELECT
statement will be:

SELECT

PRODNAME

FROM SALESPERSON, PRODUCT, SALES
WHERE SALESPERSON.SPNUM=SALES.SPNUM
AND SALES.PRODNUM=PRODUCT.PRODNUM
AND SPNAME=‘Adams’
AND QUANTITY>2000;

which results in:
PRODNAME

Hammer

Saw

Subqueries

A variation on the way that the SELECT statement works is when one SELECT
statement is ‘‘nested’’ within another in a format known as a subquery. This can
go on through several levels of SELECT statements, with each successive SELECT
statement contained in a pair of parentheses. The execution rule is that the innermost
SELECT statement is executed first and its results are then provided as input to the
SELECT statement at the next level up. This procedure can be an alternative to
the join. Furthermore, there are certain circumstances in which this procedure must

Y O U R
T U R N

4.1 QUERIES GALORE!

Having a relational database to
query in any business environment opens up a new world
of information for managers to use to help them run their
portion of the business.

QUESTION:
Think about a business environment that you are familiar

with from your daily life. It might be a university, a

supermarket, a department store, even a sports league.
Write a list of ten questions that you would like to be
able to ask that would enhance your interaction with
that environment. Is it reasonable that a database could
be constructed that would support your ability to ask
the questions you’ve come up with? Do you think that
you would be able to formulate your questions using
SQL? Explain.

Data Retrieval with the SQL SELECT Command 87

be used. These latter circumstances are common enough and important enough to
include in this treatment of the SQL SELECT command.

Subqueries as Alternatives to Joins Let’s reconsider the first join example given
above:

‘‘Find the name of the salesperson responsible for Customer Number 1525.’’

If you methodically weave through the database tables to solve this, as we discussed
earlier in the book, you start at the CUSTOMER table, find the record for Customer
Number 1525 and discover in that record that the salesperson responsible for
this customer is Salesperson Number 361. You then take that information to the
SALESPERSON table where you look up the record for Salesperson Number 361
and discover in it that the salesperson’s name is Carlyle. Using a subquery, this
logic can be built into an SQL statement as:

SELECT SPNAME
FROM SALESPERSON
WHERE SPNUM=

(SELECT SPNUM
FROM CUSTOMER
WHERE CUSTNUM=1525);

and the result will again be:

SPNAME
Carlyle

Follow the way that the description given above of methodically solving
the problem is reconstructed as a SELECT statement with a subquery. Since the
innermost SELECT (the indented one), which constitutes the subquery, is considered
first, the CUSTOMER table is queried first, the record for Customer Number 1525
is found and 361 is returned as the SPNUM result. How do we know that only one
salesperson number will be found as the result of the query? Because CUSTNUM is
a unique attribute, Customer Number 1525 can only appear in one record and that
one record only has room for one salesperson number! Moving along, Salesperson
Number 361 is then fed to the outer SELECT statement. This, in effect, makes the
main query, that is the outer SELECT, look like:

SELECT SPNAME
FROM SALESPERSON
WHERE SPNUM=361;

and this results in:

SPNAME
Carlyle

Notice, by the way, that in the SELECT statement, there is only one semicolon
at the end of the entire statement, including the subquery.

88 C h a p t e r 4 Relational Data Retrieval: SQL

When a Subquery is Required There is a very interesting circumstance in which a
subquery is required. This situation is best explained with an example up front.
Consider the following query:

‘‘Which salespersons with salesperson numbers greater than 200 have the lowest
commission percentage?’’ (We’ll identify salespersons by their salesperson
number.)

This seems like a perfectly reasonable request, and yet it turns out to be deceptively
difficult. The reason is that the query really has two very different parts. First, the
system has to determine what the lowest commission percentage is for salespersons
with salesperson numbers greater than 200. Then, it has to see which of these
salespersons has that lowest percentage. It’s really tempting to try to satisfy this
type of query with an SQL SELECT statement like:

SELECT SPNUM, MIN(COMMPERCT)
FROM SALESPERSON
WHERE SPNUM>200;

or, perhaps:

SELECT SPNUM
FROM SALESPERSON

WHERE SPNUM>200
AND COMMPERCT=MIN(COMMPERCT);

But these will not work! It’s like asking SQL to perform two separate operations
and somehow apply one to the other in the correct sequence. This turns out to be
asking too much. But there is a way to do it and it involves subqueries. In fact, what
we will do is ask the system to determine the minimum commission percentage
first, in a subquery, and then use that information in the main query to determine
which salespersons have it:

SELECT SPNUM
FROM SALESPERSON
WHERE SPNUM>200
AND COMMPERCT=

(SELECT MIN(COMMPERCT)
FROM SALESPERSON)
WHERE SPNUM>200);

which results in:
SPNUM

204

The minimum commission percentage across all of the salespersons with
salesperson numbers greater than 200 is determined first in the subquery and the
result is 10. The main query then, in effect, looks like:

SELECT SPNUM
FROM SALESPERSON

Data Retrieval with the SQL SELECT Command 89

WHERE SPNUM>200
AND COMMPERCT=10;

which yields the result of salesperson number 204, as shown.
Actually, this is a very interesting example of a required subquery. What

makes it really interesting is why the predicate, SPNUM>200, appears in both
the main query and the subquery. Clearly it has to be in the subquery because
you must first find the lowest commission percentage among the salespersons with
salesperson numbers greater than 200. But then why does it have to be in the
main query, too? The answer is that the only thing that the subquery returns to the
main query is a single number, specifically a commission percentage. No memory
is passed on to the main query of how the subquery arrived at that value. If you
remove SPNUM>200 from the main query, so that it now looks like:

SELECT SPNUM
FROM SALESPERSON
WHERE COMMPERCT=

(SELECT MIN(COMMPERCT)
FROM SALESPERSON)
WHERE SPNUM>200);

you would find every salesperson with any salesperson number whose commission
percentage is equal to the lowest commission percentage of the salespersons with
salesperson numbers greater than 20. (Of course, if for some reason you do want
to find all of the salespersons, regardless of salesperson number, who have the
same commission percentage as the salesperson who has the lowest commission
percentage of the salespersons with salesperson numbers greater than 20, then this
last SELECT statement is exactly what you should write!)

A Strategy for Writing SQL SELECT Commands

Before we go on to some more examples, it will be helpful to think about developing
a strategy for writing SQL SELECT statements. The following is an ordered list of
steps.

1. Determine what the result of the query is to be and write the needed attributes
and functions in the SELECT clause. This may seem an obvious instruction, but
it will really pay to think this through carefully before going on. In fact, it is
at this very first step that you must determine whether the query will require a
GROUP BY clause or a subquery. If either of these is required, you should start
outlining the overall SELECT statement by writing the GROUP BY clause or
the nested SELECT for the subquery further down the page (or screen).

2. Determine which tables of the database will be needed for the query and write
their names in the FROM clause. Include only those tables that are really
necessary for the query. Sometime this can be tricky. For example, you might
need an attribute that is the primary key of a table and you might be tempted
immediately to include that table in the FROM clause. However, it could be that
the attribute in question is a foreign key in another table that is already in the
FROM clause for other reasons. It is then unnecessary to include the table in
which it is the primary key unless, of course, other attributes from that table are
needed, too.

90 C h a p t e r 4 Relational Data Retrieval: SQL

3. Begin constructing the WHERE clause by equating the join attributes from the
tables that are in the FROM clause. Once this job is out of the way, you can
begin considering the row limitations that must be stated in the WHERE clause.

4. Continue filling in the details of the WHERE clause, the GROUP BY clause,
and any subqueries.

One final piece of advice: If you are new to writing SQL SELECT commands
but you have a programming background, you may be tempted to avoid setting up
joins and try writing subqueries instead. Resist this temptation, for two reasons!
One is that joins are an essential part of the relational database concept. Embrace
them; don’t be afraid of them. The other is that writing multiple levels of nested
subqueries can be extremely error prone and difficult to debug.

EXAMPLE: GOOD READING BOOK STORES

The best way to gain confidence in understanding SQL SELECT statements is to
write some! And there are some further refinements of the SQL SELECT that we
have yet to present. We will use the same three example databases that appeared
in previous chapters but, as with the General Hardware database, we will shorten
the attribute names. We will state a variety of queries and then give the SELECT
statements that will satisfy them, plus commentary as appropriate. You should try
to write the SELECT statements yourself before looking at our solutions!

Figure 4.2 is the Good Reading Bookstores relational database. Here is a list
of queries for Good Reading Bookstores.

F I G U R E 4.2
Good reading Bookstores Relational
database

SALE table

BOOKNUM CUSTNUM DATE PRICE QUANTITY

WRITING table

BOOKNUM AUTHORNUM

CUSTOMER table

CUSTNUM CUSTNAME STREET CITY STATE COUNTRY

BOOK table

BOOKNUM BOOKNAME PUBYEAR PAGES PUBNAME

AUTHOR table

AUTHORNUM AUTHORNAME YEARBORN YEARDIED

PUBLISHER table

PUBNAME CITY COUNTRY TELEPHONE YRFOUND

Example: Good Reading Book Stores 91

1. ‘‘Find the book number, book name, and number of pages of all the books
published by London Publishing Ltd. List the results in order by book
name.’’

This query obviously requires the PUBNAME attribute but it does not require
the PUBLISHER table. All of the information needed is in the BOOK table,
including the PUBNAME attribute, which is there as a foreign key. The SELECT
statement is:

SELECT BOOKNUM, BOOKNAME, PAGES
FROM

BOOK

WHERE PUBNAME=‘London Publishing Ltd.’
ORDER BY BOOKNAME;

2. ‘‘How many books of at least 400 pages does Good Reading Bookstores
carry that were published by publishers based in Paris, France?’’

This is a straightforward join between the PUBLISHER and BOOK tables that
uses the built-in function COUNT. All of the attribute names are unique between
the two tables, except for PUBNAME, which must be qualified with a table name
every time it is used. Notice that ‘Good Reading Bookstores’ does not appear as
a condition in the SELECT statement, although it was mentioned in the query.
The entire database is about Good Reading Bookstores and no other! There is no
BOOKSTORE CHAIN table in the database and there is no STORENAME or
CHAINNAME attribute in any of the tables.

SELECT COUNT(*)
FROM PUBLISHER, BOOK
WHERE PUBLISHER.PUBNAME=BOOK.PUBNAME
AND CITY=‘Paris’
AND COUNTRY=‘France’
AND PAGES>=400;

3. ‘‘List the publishers in Belgium, Brazil, and Singapore that publish books
written by authors who were born before 1920.’’

Sometimes a relatively simple-sounding query can be fairly involved. This
query actually requires four tables of the database! To begin with, we need the
PUBLISHER table because that’s the only place that a publisher’s country is
stored. But we also need the AUTHOR table because that’s where author birth
years are stored. The only way to tie the PUBLISHER table to the AUTHOR table
is to connect PUBLISHER to BOOK, then to connect BOOK to WRITING, and
finally to connect WRITING to AUTHOR. With simple, one-attribute keys such
as those in these tables, the number of joins will be one fewer than the number
of tables. The FROM clause below shows four tables and the first three lines of
the WHERE clause show the three joins. Also, notice that since a publisher may
have published more than one book with the stated specifications, DISTINCT
is required to prevent the same publisher name from appearing several, perhaps
many, times in the result. Finally, since we want to include publishers in three
specific countries, we list the three countries as Belgium, Brazil, and Singapore.
But, in the SELECT statement, we have to indicate that for a record to be
included in the result, the value of the COUNTRY attribute must be Belgium,
Brazil or Singapore.

92 C h a p t e r 4 Relational Data Retrieval: SQL

SELECT DISTINCT PUBNAME
FROM PUBLISHER, BOOK, WRITING,

AUTHOR

WHERE PUBLISHER.PUBNAME=BOOK.PUBNAME
AND BOOK.BOOKNUM=WRITING.BOOKNUM
AND WRITING.AUTHORNUM=AUTHOR.AUTHORNUM
AND COUNTRY IN (‘Belgium’, ‘Brazil’, ‘Singapore’)
AND YEARBORN < 1920;

4. ‘‘How many books did each publisher in Oslo, Norway; Nairobi, Kenya;
and Auckland, New Zealand, publish in 2001?’’

The keyword here is ‘‘each.’’ This query requires a separate total for each
publisher that satisfies the conditions. This is a job for the GROUP BY clause.
We want to group together the records for each publisher and count the number of
records in each group. Each line of the result must include both a publisher name
and count of the number of records that satisfy the conditions. This SELECT
statement requires both a join and a GROUP BY. Notice the seeming complexity
but really the unambiguous beauty of the ANDs and ORs structure regarding the
cities and countries.

SELECT PUBNAME, CITY, COUNTRY, COUNT(*)
FROM PUBLISHER, BOOK
WHERE PUBLISHER.PUBNAME=BOOK.PUBNAME
AND ((CITY=‘Oslo’ AND COUNTRY=‘Norway’)

OR (CITY=‘Nairobi’ AND COUNTRY=‘Kenya’)
OR (CITY=‘Auckland’ AND COUNTRY=‘New Zealand’))

AND PUBYEAR=2001
GROUP BY PUBNAME;

5. ‘‘Which publisher published the book that has the earliest publication year
among all the books that Good Reading Bookstores carries?’’

All that is called for in this query is the name of the publisher, not the name
of the book. This is a case that requires a subquery. First the system has to
determine the earliest publication year, then it has to see which books have that
earliest publication year. Once you know the books, their records in the BOOK
table give you the publisher names. Since more than one publisher may have
published a book in that earliest year, there could be more than one publisher
name in the result. And, since a particular publisher could have published more
than one book in that earliest year, DISTINCT is required to avoid having that
publisher’s name listed more than once.

SELECT DISTINCT PUBNAME
FROM BOOK
WHERE PUBYEAR=

(SELECT MIN(PUBYEAR)
FROM BOOK);

EXAMPLE: WORLD MUSIC ASSOCIATION

Figure 4.3 is the World Music Association relational database. Here is a list of
queries for the World Music Association.

Example: World Music Association 93

F I G U R E 4.3
World Music Association relational
database

RECORDING table

ORCHNAME YEAR PRICE

COMPOSITIONNAME

COMPOSITION table

COMPOSITIONNAME
COMPOSITIONNAME

YEAR

YEAR

COMPOSERNAME

COMPOSERNAME

COMPOSER table

COMPOSERNAME COUNTRY DATEBIRTH

DEGREE table

MUSNUM DEGREE UNIVERSITY YEAR

MUSICIAN table

MUSNUM MUSNAME INSTRUMENT ANNSALARY ORCHNAME

ORCHESTRA table

ORCHNAME CITY COUNTRY MUSICDIR

1. ‘‘What is the total annual salary cost for all the violinists in the Berlin
Symphony Orchestra?’’

SELECT SUM(ANNSALARY)
FROM

MUSICIAN

WHERE ORCHNAME=‘Berlin Symphony Orchestra’
AND INSTRUMENT=‘Violin’;

2. ‘‘Make a single list, in alphabetic order, of all of the universities attended by
the cellists in India.’’

SELECT DISTINCT UNIVERSITY
FROM ORCHESTRA, MUSICIAN,

DEGREE

WHERE ORCHESTRA.ORCHNAME=MUSICIAN.ORCHNAME
AND MUSICIAN.MUSNUM=DEGREE.MUSNUM
AND INSTRUMENT=‘Cello’
AND COUNTRY=‘India’
ORDER BY UNIVERSITY;

3. ‘‘What is the total annual salary cost for all of the violinists of each orchestra
in Canada? Include in the result only those orchestras whose total annual
salary for its violinists is in excess of $150,000.’’

Since this query requires a separate total for each orchestra, the SELECT
statement must rely on the GROUP BY clause. Since the condition that the

94 C h a p t e r 4 Relational Data Retrieval: SQL

total must be over 150,000 is based on figures calculated by the SUM built-in
function, it must be placed in a HAVING clause rather than in the WHERE
clause.

SELECT ORCHNAME, SUM(ANNSALARY)
FROM ORCHESTRA, MUSICIAN
WHERE ORCHESTRA.ORCHNAME=MUSICIAN.ORCHNAME
AND COUNTRY=‘Canada’
AND INSTRUMENT=‘Violin’
GROUP BY ORCHNAME
HAVING SUM(ANNSALARY)>150,000;

4. ‘‘What is the name of the most highly paid pianist?’’

It should be clear that a subquery is required. First the system has to determine
what the top salary of pianists is and then it has to find out which pianists have
that salary.

SELECT MUSNAME
FROM MUSICIAN
WHERE INSTRUMENT=‘Piano’
AND ANNSALARY=

(SELECT MAX(ANNSALARY)
FROM MUSICIAN
WHERE INSTRUMENT=‘Piano’);

This is another example in which a predicate, INSTRUMENT=‘Piano’ in this
case, appears in both the main query and the subquery. Clearly it has to be in
the subquery because you must first find out how much money the highest-paid
pianist makes. But then why does it have to be in the main query, too? The
answer is that the only thing that the subquery returns to the main query is a single
number, specifically a salary value. No memory is passed on to the main query of
how the subquery arrived at that value. If you remove INSTRUMENT=‘Piano’
from the main query so that it now looks like:

SELECT MUSNAME
FROM MUSICIAN
WHERE ANNSALARY=

(SELECT MAX(ANNSALARY)
FROM MUSICIAN
WHERE INSTRUMENT=‘Piano’);

you would find every musician who plays any instrument whose salary is equal
to the highest- paid pianist. Of course, if for some reason you do want to find
all of the musicians, regardless of the instrument they play, who have the same
salary as the highest-paid pianist, then this last SELECT statement is exactly
what you should write.

5. ‘‘What is the name of the most highly paid pianist in any orchestra in
Australia?’’

This is the same idea as the last query but involves two tables, both of which
must be joined in both the main query and the subquery. The reasoning for this
is the same as in the last query. The salary of the most highly paid pianist in

Example: Lucky Rent-A-Car 95

Australia must be determined first in the subquery. Then that result must be used
in the main query, where it must be compared only to the salaries of Australian
pianists.

SELECT MUSNAME
FROM MUSICIAN,

ORCHESTRA

WHERE MUSICIAN.ORCHNAME=ORCHESTRA.ORCHNAME
AND INSTRUMENT=‘Piano’
AND COUNTRY=‘Australia’
AND ANNSALARY=

(SELECT MAX(ANNSALARY)
FROM MUSICIAN, ORCHESTRA
WHERE MUSICIAN.ORCHNAME=ORCHESTRA.ORCHNAME
AND INSTRUMENT=‘Piano’
AND COUNTRY=‘Australia’);

EXAMPLE: LUCKY RENT-A-

CAR

Figure 4.4 is the Lucky Rent-A-Car relational database. Here is a list of queries for
Lucky Rent-A-Car.

1. ‘‘List the manufacturers whose names begin with the letter ‘‘C’’ or the letter
‘‘D’’ and that are located in Japan.’’

SELECT MANUFNAME
FROM

MANUFACTURER

F I G U R E 4.4
Lucky Rent-A-Car relational database

RENTAL table

CARNUM CUSTNUM RENTALDATE RETURNDATE COST

CUSTOMER table

CUSTNUM CUSTNAME CUSTADDR CUSTPHONE

MAINTENANCE table

REPAIRNUM CARNUM DATE PROCEDURE MILEAGE REPAIRTIME

CAR table

CARNUM MODEL YEAR CLASS MANUFNAME

MANUFACTURER table

MANUFNAME COUNTRY SALESREPNAME SALESREPPHONE

96 C h a p t e r 4 Relational Data Retrieval: SQL

WHERE (MANUFNAME LIKE ‘C%’
OR MANUFNAME LIKE ‘D%’)

AND COUNTRY=‘Japan’;

2. ‘‘What was the average mileage of the cars that had tune-ups in August,
2003?’’

SELECT AVG(MILEAGE)
FROM

MAINTENANCE

WHERE PROCEDURE=‘Tune-Up’
AND DATE BETWEEN ‘AUG-01-2003’ AND ‘AUG-31-2003’;

The exact format for specifying dates may differ among SQL processors and a
given processor may have several options.

3. ‘‘How many different car models are made by manufacturers in Italy?’’

This query will use an interesting combination of COUNT and DISTINCT that
may not work in all SQL processors. In this case it literally counts the different
models among the cars made in Italy. Since many different cars are of the same
model, DISTINCT is needed to make sure that each model is counted just once.

SELECT COUNT(DISTINCT MODEL)
FROM MANUFACTURER, CAR
WHERE MANUFACTURER.MANUFNAME=CAR.MANUFNAME
AND COUNTRY=‘Italy’;

4. ‘‘How many repairs were performed on each car manufactured by Superior
Motors during the month of March, 2004? Include only cars in the result
that had at least three repairs.’’

SELECT CAR.CARNUM, COUNT(*)
FROM CAR, MAINTENANCE
WHERE CAR.CARNUM=MAINTENANCE.CARNUM
AND MANUFNAME=‘Superior Motors’
AND DATE BETWEEN ‘MAR-01-2004’ AND ‘MAR-31-2004’
GROUP BY CAR.CARNUM
HAVING COUNT(*)>=3;

5. ‘‘List the cars of any manufacturer that had an oil change in January, 2004,
and had at least as many miles as the highest-mileage car manufactured by
Superior Motors that had an oil change that same month.’’

SELECT MAINTENANCE.CARNUM
FROM MAINTENANCE
WHERE PROCEDURE=‘Oil Change’
AND DATE BETWEEN ‘JAN-01-2004’ AND ‘JAN-31-2004’
AND MILEAGE>=

(SELECT MAX(MILEAGE)
FROM CAR, MAINTENANCE
WHERE CAR.CARNUM, MAINTENANCE.CARNUM
AND PROCEDURE=‘Oil Change’
AND DATE BETWEEN ‘JAN-01-2004’ AND ‘JAN-31-2004’
AND MANUFNAME=‘Superior Motors’);

Relational Query Optimizer 97

RELATIONAL QUERY OPTIMIZER

Relational DBMS Performance

An ever-present issue in data retrieval is performance: the speed with which
the required data can be retrieved. In a typical relational database application
environment, and as we’ve seen in the examples above, many queries require only
one table. It is certainly reasonable to assume that such single-table queries using
indexes, hashing, and the like, should, more or less, not take any longer in a relational
database system environment than in any other kind of file management system.
But,what about the queries that involve joins? Recall the detailed explanation of how
data integration works earlier in the book that used the Salesperson and Customer
tables as an example. These very small tables did not pose much of a performance
issue, even if the join was carried out in the worst-case way, comparing every row
of one table to every row of the other table, as was previously described. But what
if we attempted to join a 1-million-row table with a 3-million-row table? How long
do you think that would take—even on a large, fast computer? It might well take
much longer than a person waiting for a response at a workstation would be willing
to tolerate. This was actually one of the issues that caused the delay of almost ten
years from the time the first article on relational database was published in 1970
until relational DBMSs were first offered commercially almost ten years later.

The performance issue in relational database management has been approached
in two different ways. One, the tuning of the database structure, which is known
as ‘‘physical database design,’’ will be the subject of an entire chapter of this
book, Chapter 8. It’s that important. The other way that the relational database
performance issue has been approached is through highly specialized software in
the relational DBMS itself. This software, known as a relational query optimizer,
is in effect an ‘‘expert system’’ that evaluates each SQL SELECT statement sent to
the DBMS and determines an efficient way to satisfy it.

Relational Query Optimizer Concepts

All major SQL processors (meaning all major relational DBMSs) include a query
optimizer. Using a query optimizer, SQL attempts to figure out the most efficient
way of answering a query, before actually responding to it. Clearly, a query that
involves only one table should be evaluated to take advantage of aids such as indexes
on pertinent attributes. But, again, the most compelling and interesting reason for
having a query optimizer in a relational database system is the goal of executing
multiple-table data integration or join-type operations without having to go through
the worst-case, very time-consuming, exhaustive row-comparison process. Exactly
how a specific relational DBMS’s query optimizer works is typically a closely
held trade secret. Retrieval performance is one way in which the vendors of these
products compete with one another. Nevertheless, there are some basic ideas that
we can discuss here.

When an SQL query optimizer is presented with a new SELECT statement to
evaluate, it seeks out information about the tables named in the FROM clause. This
information includes:

■ Which attributes of the tables have indexes built over them.
■ Which attributes have unique values.
■ How many rows each table has.

98 C h a p t e r 4 Relational Data Retrieval: SQL

The query optimizer finds this information in a special internal database known
as the ‘‘relational catalog,’’ which will be described further in Chapter 10.

The query optimizer uses the information about the tables, together with the
various components of the SELECT statement itself, to look for an efficient way to
retrieve the data required by the query. For example, in the General Hardware Co.
SELECT statement:

SELECT SPNUM, SPNAME
FROM SALESPERSON
WHERE COMMPERCT=10;

the query optimizer might check on whether the COMMPERCT attribute has an
index built over it. If this attribute does have an index, the query optimizer might
decide to use the index to find the rows with a commission percentage of 10.
However, if the number of rows of the SALESPERSON table is small enough, the
query optimizer might decide to read the entire table into main memory and scan it
for the rows with a commission percentage of 10.

Another important decision that the query optimizer makes is how to satisfy
a join. Consider the following General Hardware Co. example that we looked at
above:

SELECT SPNAME
FROM SALESPERSON, CUSTOMER
WHERE SALESPERSON.SPNUM=CUSTOMER.SPNUM
AND CUSTNUM=1525;

In this case, the query optimizer should be able to recognize that since CUSTNUM
is a unique attribute in the CUSTOMER table and only one customer number is
specified in the SELECT statement, only a single record from the CUSTOMER
table, the one for customer number 1525, will be involved in the join. Once it finds
this CUSTOMER record (hopefully with an index), it can match the SPNUM value
found in it against the SPNUM values in the SALESPERSON records looking for
a match. If it is clever enough to recognize that SPNUM is a unique attribute in
the SALESPERSON table, then all it has to do is find the single SALESPERSON
record (hopefully with an index) that has that salesperson number and pull the
salesperson name (SPNAME) out of it to satisfy the query. Thus, in this type of
case, an exhaustive join can be completely avoided.

When a more extensive join operation can’t be avoided, the query optimizer
can choose from one of several join algorithms. The most basic, known as a
Cartesian product, is accomplished algorithmically with a ‘‘nested-loop join.’’ One
of the two tables is selected for the ‘‘outer loop’’ and the other for the ‘‘inner loop.’’
Each of the records of the outer loop is chosen in succession and, for each, the
inner-loop table is scanned for matches on the join attribute. If the query optimizer
can determine that only a subset of the rows of the outer or inner tables is needed,
then only those rows need be included in the comparisons.

A more efficient join algorithm than the nested-loop join, the ‘‘merge-scan
join,’’ can be used only if certain conditions are met. The principle is that for the
merge-scan join to work, each of the two join attributes either must be in sorted
order or must have an index built over it. An index, by definition, is in sorted order
and so, one way or the other, each join attribute has a sense of order to it. If this

Questions 99

condition is met, then comparing every record of one table to every record of the
other table as in a nested-loop join is unnecessary. The system can simply start at
the top of each table or index, as the case may be, and move downwards, without
ever having to move upwards.

SUMMARY

SQL has become the standard relational database management data definition
and data manipulation language. Data retrieval in SQL is accomplished with the
SELECT command. SELECT commands can be run in a direct query mode or
embedded in higher-level language programs in an embedded mode. The SELECT
command can be used to retrieve one or more rows of a table, one or more columns
of a table, or particular columns of particular rows. There are built-in functions
that can sum and average data, find the minimum and maximum values of a set of
data, and count the number of rows that satisfy a condition. These built-in functions
can also be applied to multiple subsets or groups of rows. The SELECT command
can also integrate data by joining two or more tables. Subqueries can be developed
for certain specific circumstances. There is a strategy for writing SQL commands
successfully.

Performance is an important issue in the retrieval of data from relational
databases. All relational database management systems have a relational query
optimizer, which is software that looks for a good way to solve each relational query
presented to it. While the ways that these query optimizers work are considered
trade secrets, there are several standard concepts and techniques that they generally
incorporate.

KEY TERMS

Access path
AND/OR
Base table
BETWEEN
Built-in functions
Comparisons
Data definition language (DDL)
Data manipulation language (DML)
Declarative

DISTINCT
Embedded mode
Filtering
GROUP BY
HAVING
IN
LIKE
Merge-scan join
ORDER BY

Nested-loop join
Procedural
Query
Relational query optimizer
Search argument
SELECT
Structured Query Language (SQL)
Subquery

QUESTIONS

1. What are the four basic operations that can be
performed on stored data?

2. What is Structured Query Language (SQL)?
3. Name several of the fundamental SQL commands

and discuss the purpose of each.
4. What is the purpose of the SQL SELECT command?

5. How does the SQL SELECT command relate to the
relational Select, Project, and Join concepts?

6. Explain the difference between running SQL in
query mode and in embedded mode.

7. Describe the basic format of the SQL SELECT
command.

100 C h a p t e r 4 Relational Data Retrieval: SQL

8. In a general way, describe how to write an SQL
SELECT command to accomplish a relational Select
operation.

9. In a general way, describe how to write an SQL
SELECT command to accomplish a relational
Project operation.

10. In a general way, describe how to write an SQL
SELECT command to accomplish a combination of
a relational Select operation and a relational Project
operation.

11. What is the purpose of the WHERE clause in SQL
SELECT commands?

12. List and describe some of the common operators
that can be used in the WHERE clause.

13. Describe the purpose of each of the following
operators in the WHERE clause:
a. AND
b. OR
c. BETWEEN
d. IN
e. LIKE

14. What is the purpose of the DISTINCT operator?
15. What is the purpose of the ORDER BY clause?

16. Name the five SQL built-in functions and describe
the purpose of each.

17. Explain the difference between the SUM and
COUNT built-in functions.

18. Describe the purpose of the GROUP BY clause.
Why must the attribute in the GROUP BY clause
also appear in the SELECT clause?

19. Describe the purpose of the HAVING clause. How
do you decide whether to place a row-limiting
predicate in the WHERE clause or in the HAVING
clause?

20. How do you construct a Join operation in an SQL
SELECT statement?

21. What is a subquery in an SQL SELECT statement?
22. Describe the circumstances in which a subquery

must be used.
23. What is a relational query optimizer? Why are they

important?
24. How do relational query optimizers work?
25. What information does a relational query optimizer

use in making its decisions?
26. What are some of the ways that relational query

optimizers can handle joins?

EXERCISES

1. Consider the following relational database that
Best Airlines uses to keep track of its mechanics,
their skills, and their airport locations. Mechanic
number (MECHNUM), airport name (AIRNAME),
and skill number are all unique fields. SIZE is
an airport’s size in acres. SKILLCAT is a skill
category, such as an engine skill, wing skill, tire
skill, etc. YEARQUAL is the year that a mechanic
first qualified in a particular skill; PROFRATE is the
mechanic’s proficiency rating in a particular skill.

MECHANIC Table

MECHNUM MECHNAME AGE SALARY −−−−−−AIRNAME

AIR

PORT Table

AIRNAME CITY STATE SIZE YEAROPENED

SKILL Table

SKILLNUM SKILLNAME SKILLCAT

QUALIFICATION Table

−−−−−−−MECHNUM −−−−−−−SKILLNUM YEARQUAL PROFRATE

Write SQL SELECT commands to answer the
following queries.
a. List the names and ages of all the mechanics.
b. List the airports in California that are at least

20 acres in size and have been open since 1935.
Order the results from smallest to largest airport.

c. List the airports in California that are at least 20
acres in size or have been open since 1935.

d. Find the average size of the airports in California
that have been open since 1935.

Exercises 101

e. How many airports have been open in California
since 1935?

f. How many airports have been open in each state
since 1935?

g. How many airports have been open in each state
since 1935? Include in your answer only those
states that have at least five such airports.

h. List the names of the mechanics who work in
California.

i. Fan blade replacement is the name of a skill.
List the names of the mechanics who have a
proficiency rating of 4 in fan blade replacement.

j. Fan blade replacement is the name of a skill.
List the names of the mechanics who work in
California who have a proficiency rating of 4 in
fan blade replacement.

k. List the total, combined salaries of all of the
mechanics who work in each city in California.

l. Find the largest of all of the airports.
m. Find the largest airport in California.

2. Consider the following relational database for the
Quality Appliance Manufacturing Co. The database
is designed to track the major appliances (refrig-
erators, washing machines, dishwashers, etc.) that
Quality manufactures. It also records information
about Quality’s suppliers, the parts they supply, the
buyers of the finished appliances, and the finished
goods inspectors. Note the following facts about this
environment:

• Suppliers are the companies that supply Quality
with its major components, such as electric
motors, for the appliances. Supplier number is
a unique identifier.

• Parts are the major components that the suppliers
supply to Quality. Each part comes with a part
number but that part number is only unique within
a supplier. Thus, from Quality’s point of view, the
unique identifier of a part is the combination of
part number and supplier number.

• Each appliance that Quality manufactures is given
an appliance number that is unique across all of
the types of appliances that Quality makes.

• Buyers are major department stores, home
improvement chains, and wholesalers. Buyer
numbers are unique.

• An appliance may be inspected by several
inspectors. There is clearly a many-to-many
relationship among appliances and inspectors, as
indicated by the INSPECTION table.

• There are one-to-many relationships between
suppliers and parts (Supplier Number is a foreign
key in the PART table), parts and appliances
(Appliance Number is a foreign key in the PART
table), and appliances and buyers (Buyer Number
is a foreign key in the APPLIANCE table).

SUPPLIER Table

SUPPLIERNUM SUPPLIERNAME CITY COUNTRY PRESIDENT

PART Table

PARTNUM −−−−−−−−−SUPPLIERNUM PARTTYPE COST −−−−−−−−−−APPLIANCENUM

APPLIANCE Table

APPLIANCENUM APPLIANCETYPE DATEMANUF −−−−−−−BUYERNUM PRICE

BUYER Table

BUYERNUM BUYERNAME CITY COUNTRY CREDITRATING

INSPECTOR Table

INSPECTORNUM INSPECTORNAME SALARY DATEHIRE

INSPECTION Table

−−−−−−−−−−APPLIANCENUM −−−−−−−−−−INSPECTORNUM DATEINSPECTION SCORE

Write SQL SELECT commands to answer the
following queries.
a. List the names, in alphabetic order, of the

suppliers located in London, Liverpool, and
Manchester, UK.

b. List the names of the suppliers that supply motors
(see PARTTYPE) costing between $50 and $100.

102 C h a p t e r 4 Relational Data Retrieval: SQL

c. Find the average cost of the motors (see
PARTTYPE) supplied by supplier number 3728.

d. List the names of the inspectors who were
inspecting refrigerators (see APPLIANCE-
TYPE) on April 17, 2011.

e. What was the highest inspection score achieved
by a refrigerator on November 3, 2011?

f. Find the total amount of money spent on Quality
Appliance products by each buyer from Mexico,
Venezuela, and Argentina.

g. Find the total cost of the parts used in each
dishwasher manufactured on February 28, 2010.
Only include in the results those dishwashers that
used at least $200 in parts.

h. List the highest0paid inspectors.
i. List the highest0paid inspectors who were hired

in 2009.
j. Among all of the inspectors, list those who earn

more than the highest-paid inspector who was
hired in 2009.

MINICASES

1. Consider the following relational database for Happy
Cruise Lines. It keeps track of ships, cruises, ports, and
passengers. A ‘‘cruise’’ is a particular sailing of a ship
on a particular date. For example, the seven-day journey

of the ship Pride of Tampa that leaves on June 13,
2011, is a cruise. Note the following facts about this
environment.

• Both ship number and ship name are unique in the
SHIP Table.

• A ship goes on many cruises over time. A cruise is
associated with a single ship.

• A port is identified by the combination of port name
and country.

• As indicated by the VISIT Table, a cruise includes
visits to several ports and a port is typically included
in several cruises.

• Both Passenger Number and Social Security Number
are unique in the PASSENGER Table. A particular
person has a single Passenger Number that is used for
all the cruises she takes.

• The VOYAGE Table indicates that a person can
take many cruises and a cruise, of course, has many
passengers.

SHIP Table

SHIPNUM SHIPNAME BUILDER LAUNCHDATE WEIGHT

CRUISE Table

CRUISENUM STARTDATE ENDDATE DIRECTOR −−−−−−SHIPNUM

PORT Table

PORTNAME COUNTRY NUMDOCKS MANAGER

VISIT Table

CRUISENUM −−−−−−−PORTNAME −−−−−−COUNTRY ARRDATE DEPDATE

PASSENGER Table

PASSENGERNUM PASSENGERNAME SOCSECNUM STATE COUNTRY

VOYAGE Table

−−−−−−−−−−−PASSENGERNUM −−−−−−−−CRUISENUM ROOMNUM FARE

Write SQL SELECT commands to answer the
following queries.
a. Find the start and end dates of cruise number 35218.
b. List the names and ship numbers of the ships built

by the Ace Shipbuilding Corp. that weigh more than
60,000 tons.

c. List the companies that have built ships for Happy
Cruise Lines.

d. Find the total number of docks in all the ports in
Canada.

e. Find the average weight of the ships built by the Ace
Shipbuilding Corp. that have been launched since
2000.

f. How many ports in Venezuela have at least three
docks?

Minicases 103

g. Find the total number of docks in each country. List
the results in order from most to least.

h. Find the total number of ports in each country.
i. Find the total number of docks in each country but

include only those countries that have at least twelve
docks in your answer.

j. Find the name of the ship that operated on (was used
on) cruise number 35218.

k. List the names, states and countries of the passengers
who sailed on The Spirit of Nashville on cruises that
began during July, 2011.

l. Find the names of the company’s heaviest ships.
m. Find the names of the company’s heaviest ships that

began a cruise between July 15, 2011 and July 31,
2011.

2. Consider the following relational database for the Super
Baseball League. It keeps track of teams in the league,
coaches and players on the teams, work experience of
the coaches, bats belonging to each team, and which
players have played on which teams. Note the following
facts about this environment:

• The database keeps track of the history of all the teams
that each player has played on and all the players who
have played on each team.

• The database only keeps track of the current team that
a coach works for.

• Team number, team name, and player number are
each unique attributes across the league.

• Coach name is unique only within a team (and we
assume that a team cannot have two coaches of the
same name).

• Serial number (for bats) is unique only within a team.
• In the Affiliation table, the Years attribute indicates

the number of years that a player played on a team;
the batting average is for the years that a player played
on a team.

TEAM Table

TEAMNUM TEAMNAME CITY MANAGER

COACH Table

−−−−−−−TEAMNUM COACHNAME ADDRESS

WORK EXPERIENCE Table

−−−−−−−TEAMNUM −−−−−−−−COACHNAME EXPERIENCETYPE YEARSEXPERIENCE

BATS Table

−−−−−−−TEAMNUM SERIALNUM MANUFACTURER

PLAYER Table

PLAYERNUM PLAYERNAME AGE

AFFILIATION Table

−−−−−−−−PLAYERNUM −−−−−−−TEAMNUM YEARS BATTINGAVG

Write SQL SELECT commands to answer the
following queries.
a. Find the names and cities of all of the teams

with team numbers greater than 15. List the results
alphabetically by team name.

b. List all of the coaches whose last names begin with
‘‘D’’ and who have between 5 and 10 years of experi-
ence as college coaches (see YEARSEXPERIENCE
and EXPERIENCETYPE).

c. Find the total number of years of experience of Coach
Taylor on team number 23.

d. Find the number of different types of experience that
Coach Taylor on team number 23 has.

e. Find the total number of years of experience of each
coach on team number 23.

f. How many different manufacturers make bats for the
league’s teams?

g. Assume that team names are unique. Find the names
of the players who have played for the Dodgers for
at least five years (see YEARS in the AFFILIATION
Table.)

h. Assume that team names are unique. Find the total
number of years of work experience of each coach
on the Dodgers, but include in the result only
those coaches who have more than eight years of
experience.

i. Find the names of the league’s youngest players.
j. Find the names of the league’s youngest players

whose last names begin with the letter ‘‘B’’.

C H A P T E R 5

THE RELATIONAL
DATABASE MODEL:

INTRODUCTION

I n 1970, Dr. Edgar F. (Ted) Codd of IBM published in Communications of the
ACM a paper entitled ‘‘A Relational Model of Data for Large Shared Data Banks.’’

This paper marked the beginning of the field of relational databases. During the 1970s,
the relational approach to databases progressed from being a technical curiosity to a
subject of serious interest in the information systems community. But it was not until the
early 1980s that commercially viable relational database management systems became
available. There were two basic reasons for this. One was that, while the relational
database was very tempting in concept, its application in a real-world environment
was elusive for performance-related reasons. The second reason was that at exactly the
time that Codd’s paper was published, the earlier hierarchical and network database
management systems were just coming onto the commercial scene and were the focus of
intense marketing efforts by the software and hardware vendors of the day. Eventually,
both of these obstacles were overcome and the relational model became and remains the
database model of choice.

OBJECTIVES

■ Explain why the relational database model became practical in about 1980.
■ Define such basic relational database terms as relation and tuple.
■ Describe the major types of keys including primary, candidate, and foreign.
■ Describe how one-to-one, one-to-many, and many-to-many binary relationships

are implemented in a relational database.
■ Describe how relational data retrieval is accomplished using the relational algebra

select, project, and join operators.
■ Understand how the join operator facilitates data integration in relational database.

CHAPTER OUTLINE

Introduction
The Relational Database Concept

Relational Terminology

Primary and Candidate Keys

Foreign Keys and Binary
Relationships

Data Retrieval from a Relational Database
Extracting Data from a Relation

106 C h a p t e r 5 The Relational Database Model: Introduction

The Relational Select Operator

The Relational Project Operator

Combination of the Relational

Select and Project Operators
Extracting Data Across Multiple

Relations: Data Integration

Example: Good Reading Book Stores
Example: World Music Association
Example: Lucky Rent-A-Car
Summary

INTRODUCTION

Several factors converged in the early 1980s to begin turning the tide toward
relational database. One was that the performance issues that held back its adoption
in the 1970s began to be resolved. Another was that, after a decade of use
of hierarchical and network database management systems, information systems
professionals were interested in an alternative that would move toward simplifying
the database design process and produce database structures that were easier to use
and understand at all levels. Also, at this time there was increasing interest in a
DBMS environment that would allow easier, more intuitive access to the data by an
increasingly broad range of personnel. Finally, the early 1980s saw the advent of
the personal computer. As software developers began trying to create all manner
of applications and supporting software utilities for the PC, it quickly became clear
that the existing hierarchical and network database approaches would not work in
the PC environment, for two reasons. One was that these DBMSs were simply too
large to store and use on the early PCs. The other was that they were too complex to
be used by the very broad array of non-information-systems professionals at whom
the PCs were targeted.

Today, the relational approach to database management is by far the primary
database management approach used in all levels of information systems and for
most application purposes, from accounting to banking to manufacturing to sales on
the World Wide Web. Relational database management is represented today by such
products as Microsoft Access and SQL Server, Oracle, Sybase, and IBM’s DB2 and
Informix. While these and other relational database systems differ in their features
and implementations, they all share a common data structure philosophy and a
common data access tool: Structured Query Language (SQL) (often pronounced
‘‘sequel’’). This chapter will focus on the basic concepts of how data is stored
and retrieved in a relational database by a relational DBMS. Chapter 6 will discuss
some additional relational database concepts. Then, Chapter 7 will describe logical
database design and Chapter 8 will go into physical database design.

THE RELATIONAL DATABASE CONCEPT

Relational Terminology

In spite of the apparent conflict between non-redundant, linear file data storage
and data integration demonstrated in Chapter 3, the relative simplicity of simple,
linear files or structures that resemble them in a true database environment is very
desirable. After all, the linear file arrangement is the most basic and commonly used
data structure there is. This is precisely one of the advantages of relational database
management.

The Relational Database Concept 107

C O N C E P T S
I N A C T I O N

5-A BLACK & DECKER

Black & Decker is one of the world’s
largest producers of electric power tools and power tool
accessories; it is among the largest-selling residential lock
manufacturers in the U.S., and is a major manufacturer
of faucets sold in the U.S. It is also the world’s
largest producer of certain types of technology-based
industrial fastening systems. The company’s brand names
include Black & Decker and DeWalt power tools, Emhart
Teknologies, Kwikset locks and other home security
products, and Price Pfister plumbing fixtures. Based in
Towson, MD, Black & Decker has manufacturing plants
in ten countries and markets its products in over 100
countries around the globe.

One of the major factors in Black & Decker’s Power
Tools Division’s leadership position is its highly advanced,
database-focused information system that assures a steady
and accurate supply of raw materials to the manufacturing
floor. Using Manugistics’ Demand and Supply Planning
software, the system forecasts demand for Black &
Decker’s power tools and then generates a raw material

Printed by permission of Black & Decker

supply plan based on the forecast and on the company’s
manufacturing capacity. These results are fed into SAP’s
Plant Planning System that takes into account suppliers’
capabilities and lead-time constraints to set up orders for
the raw materials.

Both the Manugistics and SAP software use Ora-
cle databases to keep track of all the data involved in
these processes. Black & Decker runs the system, which
became fully integrated in 1998, on clustered Compaq
Alphas. The databases are also shared by the company’s
purchasing, receiving, finance, and accounting depart-
ments, assuring very high degrees of accuracy and speed
throughout the company’s operations and procedures.
Included among the major database tables that support
this information system are a material master table, a
vendor master table, a bill-of-materials table (indicating
which parts go into making which other parts), a rout-
ing table (indicating the work stations that the part will
move through during manufacturing), planning, purchase
order, customer, and other tables.

108 C h a p t e r 5 The Relational Database Model: Introduction

F I G U R E 5.1
Relational database terminology

Relation (or Table or File)

Student Student
Number Name Class Major

03657 Robert Shaw Senior Biology

05114 Gloria Stuart Freshman English

05950 Fred Simpson Junior Mathematics

12746 W. Shin Junior English

15887 Pedro Marcos Senior History

19462 H. Yamato Sophomore French

21682 Mary Jones Freshman Chemistry

24276 Steven Baker Sophomore History

Attribute
(or Column or Field)

Tuple
(or Row or Record)

To begin with, consider the data structure used in relational databases. In a
relational database, the data appears to be stored in what we have been referring
to as simple, linear files. Following the conventions of the area of mathematics on
which relational database is based, we will begin calling those simple linear files
relations, although in common practice they are also referred to as ‘‘tables.’’ In the
terminology of files, each row is called a ‘‘record,’’ while in a relation, each row
is called a tuple. In files, each column is called a ‘‘field,’’ while in a relation each
column is called an attribute. In practice, in speaking about relational database,
people commonly use the terms relation, table, and file synonymously. Similarly,
tuple, row, and record are often used synonymously, as are attribute, column, and
field, Figure 5.1. We will use an appropriate term in each particular situation during
our discussion. In particular, we will use the term ‘‘relation’’ in this chapter and
the next, in which we are talking about relational database concepts. Following
common usage, we will generally use the word ‘‘table’’ in the more applied parts
of the book, such as in the corporate database stories in each chapter and in the
discussion of SQL in Chapter 4.

It is important to note that there are technical differences between the concept
of a file and the concept of a relation (which is why we say that in a relational
database the data only appears to be stored in structures that look like files).The
differences include:

■ The columns of a relation can be arranged in any order without affecting the
meaning of the data. This is not true of a file.

■ Similarly, the rows of a relation can be arranged in any order, which is not true
of a file.

■ Every row/column position, sometimes referred to as a ‘‘cell,’’ can have only a
single value, which is not necessarily true in a file.

■ No two rows of a relation are identical, which is not necessarily true in a file.

A relational database is simply a collection of relations that, as a group,
contain the data describing a particular business environment.

The Relational Database Concept 109

Primary and Candidate Keys

Primary Keys Figure 5.2 contains two relations, the

SALESPERSON relation

and the CUSTOMER relation, from General Hardware Company’s relational
database. The SALESPERSON relation has four rows, each representing one
salesperson. Also, the SALESPERSON relation has four columns, each representing
a characteristic of salespersons. Similarly, the CUSTOMER relation has nine rows,
each representing a customer, and four columns.

A relation always has a unique primary key. A primary key (sometimes
shortened in practice to just ‘‘the key’’) is an attribute or group of attributes whose
values are unique throughout all rows of the relation. In fact, the primary key
represents the characteristic of a collection of entities that uniquely identifies each
one. For example, in the situation described by the relations in Figure 5.2, each
salesperson has been assigned a unique salesperson number and each customer
has been assigned a unique customer number. Therefore the Salesperson Number
attribute is the primary key of the SALESPERSON relation and the Customer
Number attribute is the primary key of the CUSTOMER relation. As in Figure 5.2,
we will start marking the primary key attribute(s) with a single, solid underline.

The number of attributes involved in the primary key is always the minimum
number of attributes that provide the uniqueness quality. For example, in the
SALESPERSON relation, it would make no sense to have the combination of
Salesperson Number and Salesperson Name as the primary key because Salesperson
Number is unique by itself. However, consider the situation of a SALESPERSON

F I G U R E 5.2
General Hardware Company
relational database

(a) SALESPERSON relation

Salesperson Salesperson Commission Year
Number Name Percentage of Hire
137 Baker 10 1995
186 Adams 15 2001
204 Dickens 10 1998
361 Carlyle 20 2001

(b)

CUSTOMER relation

Customer Customer Salesperson
Number Name Number HQ City
0121 Main St. Hardware 137 New York
0839 Jane’s Stores 186 Chicago
0933 ABC Home Stores 137 Los Angeles
1047 Acme Hardware Store 137 Los Angeles
1525 Fred’s Tool Stores 361 Atlanta
1700 XYZ Stores 361 Washington
1826 City Hardware 137 New York
2198 Western Hardware 204 New York
2267 Central Stores 186 New York

110 C h a p t e r 5 The Relational Database Model: Introduction

relation that does not include a Salesperson Number attribute, but instead has a First
Name attribute, a Middle Name attribute, and a Last Name attribute. The primary
key might then be the combination of the First, Middle, and Last Name attributes
(assuming this would always produce a unique combination of values. If it did not,
then a fourth attribute could be added to the relation and to the primary key as a
sequence field to produce, for example, John Alan Smith #1, John Alan Smith #2,
and so forth). Some attribute or combination of attributes of a relation has to be
unique and this can serve as the unique primary key, since, by definition, no two
rows can be identical. In the worst case, all of the relation’s attributes combined
could serve as the primary key if necessary (but this situation is uncommon in
practice).

Candidate Keys If a relation has more than one attribute or minimum group of
attributes that represents a way of uniquely identifying the entities, then they are
each called a candidate key. (Actually, if there is only one unique attribute or
minimum group of attributes it can also be called a candidate key.) For example, in
a personnel relation, an employee number attribute and a Social Security Number
attribute (each of which is obviously unique) would each be a candidate key of that
relation. When there is more than one candidate key, one of them must be chosen to
be the primary key of the relation. That is where the term ‘‘candidate key’’ comes
from, since each one is a candidate for selection as the primary key. The decision of
which candidate key to pick to be the primary key is typically based on which one
will be the best for the purposes of the applications that will use the relation and the
database. Sometimes the term alternate key is used to describe a candidate key that
was not chosen to be the primary key of the relation, Figure 5.3.

F I G U R E 5.3
Candidate keys become either primary or
alternate keys

CandidateKey 1

CandidateKey 3

CandidateKey 2

CandidateKey 1
CandidateKey 3
CandidateKey 2

Alternate
Key

Alternate
Key

The Winner and
Primary Key

The Relational Database Concept 111

Foreign Keys and Binary Relationships

Foreign Keys If, in a collection of relations that make up a relational database, an
attribute or group of attributes serves as the primary key of one relation and also
appears in another relation, then it is called a foreign key in that other relation. Thus
Salesperson Number, which is the primary key of the SALESPERSON relation,
is considered a foreign key in the CUSTOMER relation, Figure 5.4. As shown
in Figure 5.4, we will start marking the foreign key attribute(s) with a dashed
underline. The concept of the foreign key is crucial in relational databases, as
the foreign key is the mechanism that ties relations together to represent unary,
binary, and ternary relationships. We begin the discussion by considering how
binary relationships are stored in relational databases. These are both the most
common and the easiest to deal with. The unary and ternary relationships will come
later. Recall from the discussion of the entity-relationship model that the three
kinds of binary relationships among the entities in the business environment are
the one-to-one, one-to-many, and many-to-many relationships. The first case is the
one-to-many relationship, which is typically the most common of the three.

One-to-Many Binary Relationship Consider the SALESPERSON and CUSTOMER
relations of Figure 5.2, repeated in Figure 5.4. As one would expect in most
sales-oriented companies, notice that each salesperson is responsible for several
customers while each customer has a single salesperson as their point of contact with
General Hardware. This one-to-many binary relationship can be represented as:

Salesperson Customer

137
186
204
361
Baker
Adams
Dickens
Carlyle
10

15

10
20
1995
2001
1998
2001

Salesperson
Number

Salesperson

Name

Year
of Hire

Commission
Percentage

0121
0839
0933
1047
1525
1700
1826
2198
2267
Main St. Hardware

Jane’s Hardware

ABC Home Stores
Acme Hardware Store
Fred’s Tool Stores
XYZ Stores
City Hardware

Western Hardware

Central Stores
137
186
137
137
361
361
137
204
186
New York
Chicago
Los Angeles
Los Angeles
Atlanta
Washington
New York
New York
New York

Customer
Number

Customer
Name HQ City

Salesperson
Number

Primary
Key

Foreign
Key

(a) SALESPERSON relation

(b) CUSTOMER relation

United States Europe

F I G U R E 5.4
A foreign key

112 C h a p t e r 5 The Relational Database Model: Introduction

F I G U R E 5.5
A salesperson and his four customers

Customer 1826

Customer 1047

Customer 0933

Customer 0121

Salesperson 137

Mr. Baker

For example, the Salesperson Number attribute of the CUSTOMER relation
shows that salesperson 137 is responsible for customers 0121, 0933, 1047, and 1826.
Looking at it from the point of view of the customer, the same relation shows that
the only salesperson associated with customer 0121 is salesperson 137, Figure 5.5.
This last point has to be true. After all, there is only one record for each customer in
the CUSTOMER relation (the Customer Number attribute is unique since it is the
relation’s primary key) and there is only one place to put a salesperson number in
it. The bottom line is that the Salesperson Number foreign key in the CUSTOMER
relation effectively establishes the one-to-many relationship between salespersons
and customers.

By the way, notice that, in this case, the primary key of the SALESPERSON
relation and the corresponding foreign key in the CUSTOMER relation both have
the same attribute name, Salesperson Number. This will often be the case but it
does not have to be. What is necessary is that both attributes have the same domain
of values; that is, they must both have values of the same type, such as (in this case)
three-digit whole numbers that are the identifiers for salespersons.

It is the presence of a salesperson number in a customer record that indicates
which salesperson the customer is associated with. Fundamentally, that is why
the Salesperson Number attribute is in the CUSTOMER relation and that is the
essence of its being a foreign key in that relation. In Chapter 7, we will discuss
database design issues in detail. But, for now, note that when building a one-to-many
relationship into a relational database, it will always be the case that the unique
identifier of the entity on the ‘‘one side’’ of the relationship (Salesperson Number,
in this example) will be placed as a foreign key in the relation representing the entity
on the ‘‘many side’’ of the relationship (the CUSTOMER relation, in this example).

Here’s something else about foreign keys. There are situations in which a
relation doesn’t have a single, unique attribute to serve as its primary key. Then, it

The Relational Database Concept 113

requires a combination of two or more attributes to reach uniqueness and serve as
its primary key. Sometimes one or more of the attributes in that combination can be
a foreign key! Yes, when this happens, a foreign key is actually part of the relation’s
primary key! This was not the case in the CUSTOMER relation of Figure 5.2b.
In this relation, the primary key only consists of one attribute, Customer Number,
which is unique all by itself. The foreign key, Salesperson Number, is clearly not a
part of the primary key.

Here is an example of a situation in which a foreign key is part of a relation’s
primary key. Figure 5.6 adds the CUSTOMER EMPLOYEE relation, Figure 5.6c,
to the General Hardware database. Remember that General Hardware’s customers
are the hardware stores, home improvement stores, or chains of such stores that it
supplies. Figure 5.6c, the CUSTOMER EMPLOYEE relation, lists the employees of
each of General Hardware’s customers. In fact, there is a one-to-many relationship
between customers and customer employees. A customer (like a hardware store)
has many employees but an employee, a person, works in only one store:

Customer Customer Employee

For example, Figure 5.6c shows that customer 2198 has four employees,
Smith, Jones, Garcia, and Kaplan. Each of those people is assumed to work for
only one customer company, customer 2198. Following the rule we developed for
setting up a one-to-many relationship with a foreign key, the Customer attribute
must appear in the CUSTOMER EMPLOYEE relation as a foreign key, and indeed
it does.

Now, what about finding a legitimate primary key for the CUSTOMER
EMPLOYEE relation? The assumption here is that employee numbers are unique
only within a company; they are not unique across all of the customer companies.
Thus, as shown in the CUSTOMER EMPLOYEE relation in Figure 5.6c, there can
be an employee of customer number 0121 who is employee number 30441 in that
company’s employee numbering system, an employee of customer number 0933
who is employee number 30441 in that company’s system, and also an employee of
customer number 2198 who is also employee number 30441. That being the case, the
Employee Number is not a unique attribute in this relation. Neither it nor any other
single attribute of the CUSTOMER EMPLOYEE relation is unique and can serve,
alone, as the relation’s primary key. But the combination of Customer Number and
Employee Number is unique. After all, we know that customer numbers are unique
and within each customer company, employee numbers are unique. That means
that, as shown in Figure 5.6c, the combination of Customer Number and Employee
Number can be and is the relation’s primary key. Further, that means that Customer
Number is both a foreign key in the CUSTOMER EMPLOYEE relation and a part
of its primary key. As shown in Figure 5.6c, we will start marking attributes that
are both a foreign key and a part of the primary key with an underline consisting of
a dashed line over a solid line.

Many-to-Many Binary Relationship

Storing the Many-to-Many Binary Relationship Figure 5.7 expands the General Hardware
database by adding two more relations, the PRODUCT relation, Figure 5.7d, and the
SALES relation, Figure 5.7e. The PRODUCT relation simply lists the products that
General Hardware sells, one row per product, with Product Number as the unique
identifier and thus the primary key of the relation. Each of General Hardware’s

114 C h a p t e r 5 The Relational Database Model: Introduction

F I G U R E 5.6
General Hardware Company relational
database including the CUSTOMER
EMPLOYEE relation

(a) SALESPERSON relation
Salesperson Salesperson Commission Year
Number Name Percentage of Hire
137 Baker 10 1995
186 Adams 15 2001
204 Dickens 10 1998
361 Carlyle 20 2001
(b) CUSTOMER relation
Customer Customer Salesperson
Number Name Number HQ City
0121 Main St. Hardware 137 New York
0839 Jane’s Stores 186 Chicago
0933 ABC Home Stores 137 Los Angeles
1047 Acme Hardware Store 137 Los Angeles
1525 Fred’s Tool Stores 361 Atlanta
1700 XYZ Stores 361 Washington
1826 City Hardware 137 New York
2198 Western Hardware 204 New York
2267 Central Stores 186 New York

(c) CUSTOMER EMPLOYEE relation

Customer Employee Employee
Number Number Name Title

0121 27498 Smith Co-Owner
0121 30441 Garcia Co-Owner
0933 25270 Chen VP Sales
0933 30441 Levy Sales Manager
0933 48285 Morton President
1525 33779 Baker Sales Manager
2198 27470 Smith President
2198 30441 Jones VP Sales
2198 33779 Garcia VP Personnel
2198 35268 Kaplan Senior Accountant

salespersons can sell any or all of the company’s products and each product can be
sold by any or all of its salespersons. Therefore the relationship between salespersons
and products is a many-to-many relationship.

Salesperson Product

The Relational Database Concept 115

So, the database will somehow have to keep track of this many-to-many
relationship between salespersons and products. The way that a many-to-many
relationship is represented in a relational database is by the creation of an additional
relation, in this example, the SALES relation in Figure 5.7e. The SALES relation
of Figure 5.7e is intended to record the lifetime sales of a particular product
by a particular salesperson. Thus, there will be a single row in the relation for
each applicable combination of salesperson and product (i.e., when a particular
salesperson has actually sold some of the particular product). For example, the first
row of the SALES relation indicates that salesperson 137 has sold product 19440.

F I G U R E 5.7
General Hardware Company relational
database including the PRODUCT and
SALES relation

(a) SALESPERSON relation
Salesperson Salesperson Commission Year
Number Name Percentage of Hire
137 Baker 10 1995
186 Adams 15 2001
204 Dickens 10 1998
361 Carlyle 20 2001
(b) CUSTOMER relation
Customer Customer Salesperson
Number Name Number HQ City
0121 Main St. Hardware 137 New York
0839 Jane’s Stores 186 Chicago
0933 ABC Home Stores 137 Los Angeles
1047 Acme Hardware Store 137 Los Angeles
1525 Fred’s Tool Stores 361 Atlanta
1700 XYZ Stores 361 Washington
1826 City Hardware 137 New York
2198 Western Hardware 204 New York
2267 Central Stores 186 New York
(c) CUSTOMER EMPLOYEE relation
Customer Employee Employee
Number Number Name Title
0121 27498 Smith Co-Owner
0121 30441 Garcia Co-Owner
0933 25270 Chen VP Sales
0933 30441 Levy Sales Manager
0933 48285 Morton President
1525 33779 Baker Sales Manager
2198 27470 Smith President
2198 30441 Jones VP Sales
2198 33779 Garcia VP Personnel
2198 35268 Kaplan Senior Accountant

(Continues)

116 C h a p t e r 5 The Relational Database Model: Introduction

F I G U R E 5.7 (Continued)
General Hardware Company relational
database including the PRODUCT and
SALES relation

(d)

PRODUCT relation

Product Product Unit
Number Name Price

16386 Wrench 12.95
19440 Hammer 17.50
21765 Drill 32.99
24013 Saw 26.25
26722 Pliers 11.50

(e) SALES relation

Salesperson Product
Number Number

Quantity

137 19440 473
137 24013 170
137 26722 688

186 16386 1,745

186 19440 2,529

186 21765 1,962

186 24013 3,071

204 21765 809

204 26722 734

361 16386 3,729

361 21765 3,110

361 26722 2,738

Since it is sufficient to record that fact once, the combination of the Salesperson
Number and Product Number attributes always produces unique values. So, in
general, the new relation created to record the many-to-many relationship will
have as its primary key the combined unique identifiers of the two entities in the
many-to-many relationship. That’s why, in this example, the Salesperson Number
and Product Number attributes both appear in the SALES relation. Each of the two
is a foreign key in the SALES relation since each is the primary key of another
relation in the database. The combination of these two attributes is unique, and
combined they comprise the primary key of the newly created SALES relation.

The new SALES relation of Figure 5.7e effectively records the many-to-
many relationship between salespersons and products. This is illustrated from the
‘‘salesperson side’’ of the many-to-many relationship by looking at the first three
rows of the SALES relation and seeing that salesperson 137 sells products 19440,
24013, and 26722. It is illustrated from the ‘‘product side’’ of the many-to-many
relationship by scanning down the Product Number column of the SALES relation,
looking for the value 19440, and seeing that product 19440 is sold by salespersons
137 and 186, Figure 5.8.

Intersection Data What about the Quantity attribute in the SALES relation? In addition
to keeping track of which salespersons have sold which products, General Hardware

The Relational Database Concept 117

F I G U R E 5.8
Many-to-many relationship between
salespersons and products as shown in
the SALES relation

Salesperson 137
Mr. Baker

Product 19440

Hammer

Product 24013

Saw

Product 26722
Pliers

Salesperson 186

Ms. Adams

wants to record how many of each particular product each salesperson has sold
since the product was introduced or since the salesperson joined the company. So,
it sounds like there has to be a ‘‘Quantity’’ attribute. And, an attribute describes
an entity, right? Then, which entity does the Quantity attribute describe? Does
it describe salespersons the way the Year of Hire does in the SALESPERSON
relation? Does it describe products the way Unit Price does in the

PRODUCT

relation? Each salesperson has exactly one date of hire. Each product has exactly
one unit price. But a salesperson doesn’t have just one ‘‘quantity’’ associated with
her because she sells many products and similarly, a product doesn’t have just one
‘‘quantity’’ associated with it because it is sold by many salespersons.

While year of hire is clearly a characteristic of salespersons and unit price is
clearly a characteristic of products, ‘‘quantity’’ is a characteristic of the relationship
between salesperson and product. For example, the fact that salesperson 137 appears
in the first row of the SALES relation of Figure 5.7e along with product 19440
indicates that he has a history of selling this product. But do we know more about
his history of selling it? Yes! That first row of Figure 5.7e indicates that salesperson
137 has sold 473 units of product 19440. Quantity describes the many-to-many
relationship between salespersons and products. In a sense it falls at the intersection
between the two entities and is thus called ‘‘intersection data,’’ Figure 5.9.

Since the many-to-many relationship has its own relation in the database and
since it can have attributes, does that mean that we should think of it as a kind of
entity? Yes! Many people do just that and refer to it as an ‘‘associative entity,’’ a
concept we first described when discussing data modeling in Chapter 2!

Additional Many-to-Many Concepts Before leaving the subject of many-to-many relation-
ships, there are a few more important points to make. First, will the combination
of the two primary keys representing the two entities in the many-to-many
relationship always serve as a unique identifier or primary key in the additional
relation representing the many-to-many relationship? The answer is that this
depends on the precise nature of the many-to-many relationship. For example,

118 C h a p t e r 5 The Relational Database Model: Introduction

F I G U R E 5.9
Intersection data that indicates that
salesperson 137 has sold 473 units of
product 19440

Salesperson 137
Product 19440

473
Units

Intersection
Data

in the situation of the SALES relation in Figure 5.7e, the combination of the two
entity identifier attributes works perfectly as the primary key, as described above.
But, what if General Hardware decides it wants to keep track of each salesperson’s
annual sales of each product instead of their lifetime sales? Fairly obviously, a
new attribute, Year, would have to be added to the SALES relation, as shown in
Figure 5.10. Moreover, as demonstrated by a few sample rows of that relation, the
combination of Salesperson Number and Product Number is no longer unique. For
example, salesperson 137 sold many units of product 19440 in each of 1999, 2000,
and 2001. The first three records of the relation all have the salesperson number,
product number combination of 137, 19440. Clearly, the way to solve the problem
in this instance is to add the Year attribute to the Salesperson Number and Product
Number attributes to form a three-attribute unique primary key. It is quite common
in practice to have to add such a ‘‘timestamp’’ to a relation storing a many-to-many
relationship in order to attain uniqueness and have a legitimate primary key.
Sometimes, as in the example in Figure 5.10, this is accomplished with a Year
attribute. A Date attribute is required if the data may be stored two or more times in a
year. A Time attribute is required if the data may be stored more than once in a day.

Next is the question of why an additional relation is necessary to represent
a many-to-many relationship. For example, could the many-to-many relationship
between salespersons and products be represented in either the SALESPERSON or
PRODUCT relations? The answer is no! If, for instance, you tried to represent the
many-to-many relationship in the SALESPERSON relation, you would have to list
all of the products (by Product Number) that a particular salesperson has sold in that
salesperson’s record. Furthermore, you would have to carry the Quantity intersection
data along with it in some way. For example, in the SALESPERSON relation, the
row for salesperson 137 would have to be extended to include products 19440,

The Relational Database Concept 119

F I G U R E 5.10
Modified SALES relation of the General
Hardware Company relational database,
including a Year attribute

SALES relation (modified)

Salesperson Product
Number Number Year Quantity

137 19440 1999 132

137 19440 2000 168

137 19440 2001 173

137 24013 2000 52

137 24013 2001 118

137 26722 1999 140

137 26722 2000 203

137 26722 2001 345

186 16386 1998 250

186 16386 1999 245

186 16386 2000 581

186 16386 2001 669

24013, and 26722, plus the associated intersection data, Figure 5.11a. Alternatively,
one could envision a single additional attribute in the SALESPERSON relation into
which all the related product number and intersection data for each salesperson
would somehow be stuffed, Figure 5.11b (although, aside from other problems,
this would violate the rule that every cell in a relation must have only a single
value). In either case, it would be unworkable. Because, in general, each salesperson
has been involved in selling different numbers of product types, each record of
the SALESPERSON relation would be a different length. Furthermore, additions,

(a) Additional Product and Quantity columns

Salesperson Salesperson Commission Year
Number Name Percentage of Hire Product Qty Product Qty Product Qty Product Qty

137 Baker 10 1995 19440 473 24013 170 26722 688

186 Adams 15 2001 16386 1745 19440 2529 21765 1962 24013 3071

204 Dickens 10 1998 21765 809 26722 734

361 Carlyle 20 2001 16386 3729 21765 3110 26722 2738

(b) One additional column for Product and Quantity Pairs

Salesperson Salesperson Commission Year
Number Name Percentage of Hire Product and Quantity Pairs

137 Baker 10 1995 (19440, 473) (24013, 170) (26722, 688)

186 Adams 15 2001 (16386, 1745) (19440, 2529) (21765, 1962) (24013, 3071)

204 Dickens 10 1998 (21765, 809) (26722, 734)

361 Carlyle 20 2001 (16386, 3729) (21765, 3110) (26722, 2738)

F I G U R E 5.11
Unacceptable ways of storing a binary many-to-many relationship

120 C h a p t e r 5 The Relational Database Model: Introduction

deletions, and updates of product/quantity pairs would be a nightmare. Also, trying
to access the related data from the ‘‘product side,’’ for example looking for all
of the salespersons who have sold a particular product, would be very difficult.
And, incidentally, trying to make this work by putting the salesperson data into the
PRODUCT relation, instead of putting the product data into the SALESPERSON
relation as in Figure 5.11, would generate an identical set of problems. No,
the only way that’s workable is to create an additional relation to represent the
many-to-many relationship. Each combination of a related salesperson and product
has its own record, making the insertion, deletion, and update of related items
feasible, providing a clear location for intersection data, and avoiding the issue of
variable-length records.

Finally, there is the question of whether an additional relation is required to
represent a many-to-many relationship if there is no intersection data. For example,
suppose that General Hardware wants to track which salespersons have sold which
products, but has no interest in how many units of each product they have sold.
The SALES relation of Figure 5.7e would then have only the Salesperson Number
and Product Number attributes, Figure 5.12. Could this information be stored in
some way other than with the additional SALES relation? The answer is that the
additional relation is still required. Note that in the explanation above of why an
additional relation is necessary in general to represent a many-to-many relationship,
the intersection data played only a small role. The issues would still be there, even
without intersection data.

One-to-One Binary Relationship After considering one-to-many and many-to-many
binary relationships in relational databases, the remaining binary relationship is the
one-to-one relationship. Each of General Hardware’s salespersons has exactly one
office and each office is occupied by exactly one salesperson, Figure 5.13.

Salesperson Office

F I G U R E 5.12
The many-to-many SALES relation without
intersection data

SALES relation
(without intersection data)

Salesperson Product
Number Number

137 19440

137 24013

137 26722

186 16386

186 19440

186 21765

186 24013

204 21765

204 26722

361 16386

361 21765

361 26722

The Relational Database Concept 121

F I G U R E 5.13
A one-to-one binary relationship

Salesperson 186

Salesperson 204

Salesperson 361

Salesperson 137 Office 1253

Office 1227

Office 1284

Office 1209

Figure 5.14f shows the addition of the OFFICE relation to the General
Hardware relational database. The SALESPERSON relation has the Office Number
attribute as a foreign key so that the company can look up the record for a salesperson
and see to which office she is assigned. Because this is a one-to-one relationship and
each salesperson has only one office, the company can also scan down the Office
Number column of the SALESPERSON relation, find a particular office number
(which can only appear once, since it’s a one-to-one relationship), and see which
salesperson is assigned to that office. In general, this is the way that one-to-one
binary relationships are built into relational databases. The unique identifier, the
primary key, of one of the two entities in the one-to-one relationship is inserted into
the other entity’s relation as a foreign key. The question of which of the two entities
is chosen as the ‘‘donor’’ of its primary key and which as the ‘‘recipient’’ will be
discussed further when we talk about logical design in Chapter 7.

But there is another interesting question about this arrangement. Could the
SALESPERSON and OFFICE relations of Figure 5.14 be combined into one
relation? After all, a salesperson has only one office and an office has only
one salesperson assigned to it. So, if an office and its unique identifier, Office
Number, ‘‘belongs’’ to one particular salesperson, so does that office’s Telephone

122 C h a p t e r 5 The Relational Database Model: Introduction

F I G U R E 5.14
General Hardware Company relational
database including the OFFICE relation

(a) SALESPERSON relation

Salesperson Salesperson Commission Year Office
Number Name Percentage of Hire Number

137 Baker 10 1995 1284
186 Adams 15 2001 1253
204 Dickens 10 1998 1209
361 Carlyle 20 2001 1227
(b) CUSTOMER relation
Customer Customer Salesperson
Number Name Number HQ City
0121 Main St. Hardware 137 New York
0839 Jane’s Stores 186 Chicago
0933 ABC Home Stores 137 Los Angeles
1047 Acme Hardware Store 137 Los Angeles
1525 Fred’s Tool Stores 361 Atlanta
1700 XYZ Stores 361 Washington
1826 City Hardware 137 New York
2198 Western Hardware 204 New York
2267 Central Stores 186 New York
(c) CUSTOMER EMPLOYEE relation
Customer Employee Employee
Number Number Name Title
0121 27498 Smith Co-Owner
0121 30441 Garcia Co-Owner
0933 25270 Chen VP Sales
0933 30441 Levy Sales Manager
0933 48285 Morton President
1525 33779 Baker Sales Manager
2198 27470 Smith President
2198 30441 Jones VP Sales
2198 33779 Garcia VP Personnel
2198 35268 Kaplan Senior Accountant
(Continues)

Number and Size. Indeed, when we want to contact a salesperson, we ask for her
phone number, not for ‘‘her office’s phone number!’’ So, could we combine the
SALESPERSON and OFFICE relations of Figure 5.14 into the single relation of
Figure 5.15? The answer is, it’s possible in some cases, but you have to be very
careful about making such a decision. In the General Hardware case, how would
you store an unoccupied office in the database? The relation of Figure 5.15 allows
data about an office to be stored only if the office is occupied. After all, the primary
key of Figure 5.15’s relation is Salesperson Number! You can’t have a record with
office data in it and no salesperson data. A case where it might work is a database
of U.S. states and their governors. Every state always has exactly one governor and

The Relational Database Concept 123

F I G U R E 5.14 (Continued)
General Hardware Company relational
database including the OFFICE relation

(d) PRODUCT relation
Product Product Unit
Number Name Price
16386 Wrench 12.95
19440 Hammer 17.50
21765 Drill 32.99
24013 Saw 26.25
26722 Pliers 11.50
(e) SALES relation

Salesperson Product
Number Number Quantity

137 19440 473
137 24013 170
137 26722 688
186 16386 1,745
186 19440 2,529
186 21765 1,962
186 24013 3,071
204 21765 809
204 26722 734
361 16386 3,729
361 21765 3,110
361 26722 2,738

(f) OFFICE relation

Office
Number Telephone Size (sq. ft.)

1253 901-555-4276 120

1227 901-555-0364 100
1284 901-555-7335 120
1209 901-555-3108 95

F I G U R E 5.15
Combining the SALESPERSON and

OFFICE

relations into a single relation

Combined SALESPERSON/OFFICE relation

Salesperson Salesperson Commission Year Office
Number Name Percentage of Hire Number Telephone Size (sq. ft.)

137 Baker 10 1995 1284 901-555-7335 120

186 Adams 15 2001 1253 901-555-4276 120

204 Dickens 10 1998 1209 901-555-3108 95

361 Carlyle 20 2001 1227 901-555-0364 100

124 C h a p t e r 5 The Relational Database Model: Introduction

Y O U R
T U R N

5.1 ENTITIES, KEYS, AND RELATIONSHIPS

Think about a retail store that sells
TVs, computers, cameras, DVDs, etc. What entities must
it keep track of? What are some of the attributes of
those entities? What about relationships among those
entities? What do you think would be appropriate
primary, candidate, and foreign keys in the relational
tables designed to store data about the entities?

QUESTION:
Specifically consider a chain of retail electronics stores.

List several of the main entities that the chain must keep
track of. What are the relationships between those
entities? What would appropriate primary, candidate,
and foreign keys be in the relational tables that would
store the data about these entities?

anyone who is a governor must be associated with one state. There can’t be a state
without a governor or a governor without a state.

At any rate, in practice, there are a variety of reasons for keeping the two
relations involved in the one-to-one relationship separate. It may be that because
each of the two entities involved is considered sufficiently important in its own
right, this separation simply adds clarity to the database. It may be because most
users at any one time seek data about only one of the two entities. It may have to
do with splitting the data between different geographic sites. It can even be done
for system performance in the case where the records would be unacceptably long
if the data was all contained in one relation. These issues will be discussed later in
this book but it is important to have at least a basic idea of the intricacies of the
one-to-one relationship, at this point.

DATA RETRIEVAL FROM A RELATIONAL DATABASE

Extracting Data from a Relation

Thus far, the discussion has concentrated on how a relational database is structured.
But building relations and loading them with data is only half of the story. The other
half is the effort to retrieve the data in a way that is helpful and beneficial to the
business organization that built the database. If the database management system did
not provide any particular help in this effort, then the problem would revert to simply
writing a program in some programming language to retrieve data from the relations,
treating them as if they were simple, linear files. But the crucial point is that a
major, defining feature of a relational DBMS is the ability to accept high-level data
retrieval commands, process them against the database’s relations, and return the
desired data. The data retrieval mechanism is a built-in part of the DBMS and does
not have to be written from scratch by every program that uses the database. As we
shall soon see, this is true even to the extent of matching related records in different
relations (integrating data), as in the earlier example of finding the name of the sales-
person on a particular customer account. We shall address what relational retrieval
might look like, first in terms of single relations and then across multiple relations.

Since a relation can be viewed as a tabular or rectangular arrangement of data
values, it would seem to make sense to want to approach data retrieval horizontally,
vertically, or in a combination of the two. Taking a horizontal slice of a relation
implies retrieving one or more rows of the relation. In effect, that’s an expression

Data Retrieval from a Relational Database 125

for retrieving one or more records or retrieving the data about one or more entities.
Taking a vertical slice of a relation means retrieving one or more entire columns of
the relation (down through all of its rows). Taken in combination, we can retrieve
one or more columns of one or more rows, the minimum of which is a single column
of a single row, or a single attribute value of a single record. That’s as fine a sense
of retrieval as we would ever want.

Using terminology from a database formalism called relational algebra and
an informal, hypothetical command style for now, there are two commands called
Select and Project that are capable of the kinds of horizontal and vertical manipula-
tions just suggested. (Note: the use of the word ‘‘Select’’ here is not the same as its
use in the SQL data retrieval language discussed in Chapter 4.)

The Relational Select Operator

Consider the database of Figure 5.14 and its SALESPERSON relation, Figure 5.14a.
To begin with, suppose that we want to find the row or record for salesperson number
204. In a very straightforward way, the informal command might be:

Select rows from the SALESPERSON relation in which Salesperson Number
= 204.

The result would be:

Salesperson Salesperson Commission Year
Number Name Percentage Of Hire

204 Dickens 10 1998

Notice that the result of the Select operation is itself a relation, in this case
consisting of only one row. The result of a relational operation will always be a
relation, whether it consists of many rows with many columns or one row with one
column (i.e., a single attribute value).

In order to retrieve all of the records with a common value in a particular
(nonunique) attribute, for example all salespersons with a commission percentage
of 10, the command looks the same as when dealing with a unique attribute:

Select rows from the SALESPERSON relation in which Commission
Percentage = 10.

But the result of the operation may include several rows:

Salesperson Salesperson Commission Year
Number Name Percentage Of Hire
137 Baker 10 1995
204 Dickens 10 1998

If the requirement is to retrieve the entire relation, the command would be:
Select all rows from the SALESPERSON relation.

The Relational Project Operator

To retrieve what we referred to earlier as a vertical slice of the relation requires the
Project operator. For example, the command to retrieve the number and name of
each salesperson in the file might look like:

Project the Salesperson Number and Salesperson Name over the SALESPER-
SON relation.

126 C h a p t e r 5 The Relational Database Model: Introduction

The result will be a long narrow relation:

Salesperson Salesperson
Number Name

137 Baker
186 Adams
204 Dickens

361 Carlyle

If we project a nonunique attribute, then a decision must be made on whether
or not we want duplicates in the result (although, since the result is itself a relation,
technically there should not be any duplicate rows). For example, whether:

Project the Year of Hire over the SALESPERSON relation.
produces

Year Of Hire

1995
2001
1998
2001

or (eliminating the duplicates in the identical rows) produces

Year Of Hire
1995
2001
1998

would depend on exactly how this hypothetical informal command language was
implemented.

Combination of the Relational Select and Project Operators

More powerful still is the combination of the Select and Project operators. Suppose
we apply them serially, with the relation that results from one operation being used
as the input to the next operation. For example, to retrieve the numbers and names
of the salespersons working on a 10 % commission, we would issue:

Select rows from the SALESPERSON relation in which Commission
Percentage = 10.

Project the Salesperson Number and Salesperson Name over that result.
The first command ‘‘selects out’’ the rows for salespersons 137 and 204. Then

the second command ‘‘projects’’ the salesperson numbers and names from those
two rows, resulting in:

Salesperson Salesperson
Number Name
137 Baker
204 Dickens

Data Retrieval from a Relational Database 127

The following combination illustrates the ability to retrieve a single attribute
value. Suppose that there is a need to find the year of hire of salesperson number
204. Since Salesperson Number is a unique attribute, only one row of the relation
can possibly be involved. Since the goal is to find one attribute value in that row,
the result must be just that: a single attribute value. The command is:

Select rows from the SALESPERSON relation in which Salesperson Number
= 204.

Project the Year of Hire over that result.
The result is the single value:

Year of Hire

1998

Extracting Data Across Multiple Relations: Data Integration

In Chapter 3, the issue of data integration was broached and the concept was defined.
First, the data in the Salesperson and Customer files of Figure 3.7 was shown to be
non-redundant. Then it was shown that integrating data would require extracting
data from one file and using that extracted data as a search argument to find
the sought-after data in the other file. For example, recall that finding the name of
the salesperson who was responsible for customer number 1525 required finding the
salesperson number in customer 1525’s record in the Customer file (i.e. salesperson
number 361) and then using that salesperson number as a search argument in the
Salesperson file to discover that the sought-after name was Carlyle. The alternative
was the combined file of Figure 3.8 that introduced data redundancy.

A fundamental premise of the database approach is that a DBMS must be
able to store data non-redundantly while also providing a data integration facility.
But it seems that we may have a problem here. Since relations appear to be largely
similar in structure to simple, linear files, do the lessons learned from the files
of Figure 3.7 and Figure 3.8 lead to the conclusion that it is impossible to have
simultaneously non-redundant data storage and data integration with relations in a
relational database? In fact, one of the elegant features of relational DBMSs is that
they automate the cross-relation data extraction process in such a way that it appears
that the data in the relations is integrated while also remaining non-redundant. The
data integration takes place at the time that a relational query is processed by the
relational DBMS for solution. This is a unique feature of relational databases and
is substantially different from the functional equivalents in the older navigational
database systems and in some of the newer object-oriented database systems, in both
of which the data integration is much more tightly built into the data structure itself.
In relational algebra terms, the integration function is known as the Join command.

Now, focus on the SALESPERSON and CUSTOMER relations of Figure 5.14,
which outwardly look just like the SALESPERSON and CUSTOMER files of
Figure 3.7. Adding the Join operator to our hypothetical, informal command style,
consider the following commands designed to find the name of the salesperson
responsible for customer number 1525. Again, this was the query that seemed to be
so problematic in Chapter 3.

Join the SALESPERSON relation and the CUSTOMER relation, using the
Salesperson Number of each as the join fields.

Select rows from that result in which Customer Number = 1525.
Project the Salesperson Name over that last result.

128 C h a p t e r 5 The Relational Database Model: Introduction

Obviously, the first sentence represents the use of the join command. The
join operation will take advantage of the common Salesperson Number attribute,
which for this purpose is called the join field, in both relations. The Salesperson
Number attribute is, of course, the primary key of the SALESPERSON relation
and is a foreign key in the CUSTOMER relation. Remember that the point of
the foreign key is to represent a one-to-many (in this case) relationship between
salespersons and customers. Some rows of the SALESPERSON relation are related
to some rows of the CUSTOMER relation by virtue of having the same salesperson
number. The Salesperson Number attribute serves to identify each salesperson in the
SALESPERSON relation, while the Salesperson Number attribute indicates which
salesperson is responsible for a particular customer in the CUSTOMER relation.
Thus, the rows of the two relations that have identical Salesperson Number values
are related. It is these related rows that the join operation will bring together in
order to satisfy the query that was posed.

The join operation tries to find matches between the join field values of the
rows in the two relations. For example, it finds a match between the Salesperson
Number value of 137 in the first row of the SALESPERSON relation and the
Salesperson Number value of 137 in the first, third, fourth, and seventh rows of the
CUSTOMER relation. When it finds such a pair of rows, it takes all the attribute
values from both rows and creates a single new row out of them in the resultant
relation. In its most basic form, as shown here, the join is truly an exhaustive
operation, comparing every row of one relation to every row of the other relation,
looking for a match in the join fields. (Comparing every possible combination of
two sets, in this case rows from the two relations, is known as taking the ‘‘Cartesian
product.’’) So the result of the join command, the first of the three commands in the
example command sequence we’re executing, is:

SalesPerson SalesPerson Commission Year of Customer Customer SalesPerson
Number Name Percentage Hire Number Name Number HQ City

137 Baker 10 1995 0121 Main St. Hardware 137 New York

137 Baker 10 1995 0933 ABC Home Stores 137 Los Angeles

137 Baker 10 1995 1047 Acme Hardware Store 137 Los Angeles

137 Baker 10 1995 1826 City Hardware 137 New York

186 Adams 15 2001 0839 Jane’s Stores 186 Chicago

186 Adams 15 2001 2267 Central Stores 186 New York

204 Dickens 10 1998 2198 Western Hardware 204 New York

361 Carlyle 20 2001 1525 Fred’s Tool Stores 361 Atlanta

361 Carlyle 20 2001 1700 XYZ Stores 361 Washington

Notice that the first and seventh columns are identical in all of their
values, row by row. They represent the Salesperson Number attributes from
the SALESPERSON and CUSTOMER relations respectively. Remember that two
rows from the SALESPERSON and CUSTOMER relations would not be combined
together to form a row in the resultant relation unless their two join field values
were identical in the first place. This leads to identical values of the two Salesperson
Number attributes within each of the rows of the resultant relation. This type of
join is called an ‘‘equijoin.’’ If, as seems reasonable, one of the two identical join
columns is eliminated in the process, the result is called a ‘‘natural join.’’

Example: Good Reading Book Stores 129

Continuing with the command sequence to eventually find the name of the
salesperson responsible for customer number 1525, the next part of the command
issued is:

Select rows from that result (the relation that resulted from the join) in which
Customer Number = 1525.

This produces:

SalesPerson SalesPerson Commission Year of Customer Customer SalesPerson
Number Name Percentage Hire Number Name Number HQ City
361 Carlyle 20 2001 1525 Fred’s Tool Stores 361 Atlanta

Finally, we issue the third command
Project the Salesperson Name over that last result.
and get:

SalesPerson Name

Carlyle

Notice that the process could have been streamlined considerably if the relational
DBMS had more ‘‘intelligence’’ built into it. The query dealt with only a single
customer, customer 1525, and there is only one row for each customer in the
CUSTOMER relation, since Customer Number is the unique key attribute. Therefore,
the query needed to look at only one row in the CUSTOMER relation, the one for
customer 1525. Since this row references only one salesperson, salesperson 361,
it follows that, in turn, it needed to look at only one row in the SALESPERSON
relation, the one for salesperson 1525. This type of performance issue in relational
query processing will be covered later in this book in Chapter 8.

EXAMPLE: GOOD READING BOOK STORES

Figure 5.16 shows the relational database for the Good Reading Book Stores
example described earlier. Since publishers are in a one-to-many relationship to
books, the primary key of the PUBLISHER Relation, Publisher Name, is inserted
into the BOOK relation as a foreign key. There are two many-to-many relationships.
One, between books and authors, keeps track of which authors wrote which books.
Recall that a book can have multiple authors and a particular author may have written
or partly written many books. The other many-to-many relationship, between books
and customers, records which customers bought which books.

The WRITING relation handles the many-to-many relationship between
books and authors. The primary key is the combination of Book Number and
Author Number. There is no intersection data! Could there be a reason for having
intersection data in this relation? If, for example, this database belonged to a
publisher instead of a bookstore chain, an intersection data attribute might be
Royalty Percentage, i.e. the percentage of the royalties to which a particular author
is entitled for a particular book. The SALE relation takes care of the many-to-many
relationship between books and customers. Certainly Book Number and Customer
Number are part of the primary key of the SALE relation, but is the combination
of the two the entire primary key? The answer is that this depends on whether the
assumption is made that a given customer can or cannot buy copies of a given

130 C h a p t e r 5 The Relational Database Model: Introduction

F I G U R E 5.16
Good Reading Bookstores relational
database

SALE relation

Book Customer
Number Number Date Price Quantity

WRITING relation

Book Author
Number Number

CUSTOMER relation

Customer Customer
Number Name Street City State Country

BOOK relation

Book Book Publication Publisher
Number Name Year Pages Name

AUTHOR relation

Author Author Year Year
Number Name Born Died

PUBLISHER relation

Publisher Year
Name City Country Telephone Founded

book on different days. If the assumption is that a customer can only buy copies
of a particular book on one single day, then the combination of Book Number and
Customer Number is fine as the primary key. If the assumption is that a customer
may indeed buy copies of a given book on different days, then the Date attribute
must be part of the primary key to achieve uniqueness.

EXAMPLE: WORLD MUSIC ASSOCIATION

Figure 5.17 shows the relational database for the World Music Association example
described earlier. There is a one-to-many relationship from orchestras to musicians
and, in turn, a one-to-many relationship from musicians to degrees. Thus, the primary
key of the ORCHESTRA relation, Orchestra Name, appears in the MUSICIAN
relation as a foreign key. In turn, the primary key of the MUSICIAN relation,
Musician Number, appears in the DEGREE relation as a foreign key. In fact, since
the DEGREE attribute is unique only within a musician, the Musician Number
attribute and the Degree attribute together serve as the compound primary key of the
DEGREE relation. A similar situation exists between composers and compositions.
The one-to-many relationship from composers to compositions requires that the

Example: World Music Association 131

F I G U R E 5.17
World Music Association relational
database

RECORDING relation

Orchestra Composition
Name Name Year Price

COMPOSITION relation

Composition
Name Year

COMPOSER relation

Composer Date of
Country Birth

DEGREE relation

Musician
Number Degree University Year

MUSICIAN relation

Musician Musician Annual Orchestra
Number Name Instrument Salary Name

ORCHESTRA relation

Orchestra Music
Name City Country Director

Name

Composer
Name

Composer
Name

primary key of the COMPOSER relation, Composer Name, appear as a foreign key
in the COMPOSITION relation. Since composition names are unique only within
composers, the combination of Composition Name and Composer Name serves as
the compound primary key of the COMPOSITION relation.

The many-to-many relationship between orchestras and compositions indicates
which orchestras have recorded which compositions and which compositions have
been recorded by which orchestras. As a many-to-many relationship, it requires
that an additional relation be created. The primary key of this new

RECORDING

relation has three attributes: Orchestra Name, Composition Name, and Composer
Name. Orchestra Name is the unique identifier of orchestras. The combination of
Composition Name and Composer Name is the unique identifier of compositions.
The combination of Orchestra Name, Composition Name, and Composer Name is
the unique identifier of the RECORDING relation. The Year and Price attributes are
intersection data in the RECORDING relation. If a particular orchestra could have
recorded a particular composition multiple times in different years (although we
assume that this is limited to once per year), Year must also be part of the primary
key of the RECORDING relation to provide uniqueness.

132 C h a p t e r 5 The Relational Database Model: Introduction

F I G U R E 5.18
Lucky Rent-A-Car relational database

RENTAL relation

Car Serial Customer Rental Return Total
Number Number Date Date Cost

CUSTOMER relation

Customer Customer Customer Customer
Number Name Address Telephone

MAINTENANCE relation

Repair Car Serial Repair
Number Number Date Procedure Mileage Time

CAR Relation

Car Serial Manufacturer
Number Model Year Class Name

MANUFACTURER relation

Manufacturer Manufacturer Sales Rep Sales Rep
Name Country Name Telephone

EXAMPLE: LUCKY RENT-A-CAR

Figure 5.18 shows the relational database for the Lucky Rent-A-Car example
described earlier. There is a one-to-many relationship from manufacturers to cars
and another one-to-many relationship from cars to maintenance events. The former
requires the manufacturer primary key, Manufactuer Name, to be placed in the CAR
relation as a foreign key. The latter requires the car primary key, Car Serial Number,
to be placed in the MAINTENANCE relation as a foreign key. The many-to-many
relationship among cars and customers requires the creation of a new relation, the
RENTAL relation. Each record of the RENTAL relation records the rental of a
particular car by a particular customer. Note that the combination of the Car Serial
Number and Customer Number attributes is not sufficient as the primary key of the
RENTAL relation. A given customer might have rented a given car more than once.
Adding Rental Date to the primary key achieves the needed uniqueness.

SUMMARY

The relational approach to database management is by far the primary database
management approach used in all levels of information systems applications today.
The basic structural component of a relational database is the relation, which
appears to be a simple linear file but has some technical differences.

Questions 133

Every relation has a unique primary key consisting of one or more attributes that
have unique values in that relation. Multiple such unique attributes or combinations
of attributes that have the uniqueness property are called candidate keys. The
candidate keys that are not chosen to be the one primary key are called alternate
keys. If the primary key of one relation in the database also appears in another
relation of the database, it called a foreign key in that second relation. Foreign keys
tie relations together in the sense that they implement relationships between the
entities represented by the relations. A one-to-many relationship is implemented
by adding the primary key on the ‘‘one side’’ of the relationship to the relation
representing the ‘‘many side’’ of the relationship. Many-to-many relationships are
implemented by constructing an additional relation that includes the primary keys of
the two entities in the many-to-many relationship. Additional attributes that describe
the many-to-many relationship are called intersection data.

Three basic relational algebra commands permit data retrieval from a relational
database. The Select command retrieves one or more rows of a relation. The
Project command retrieves one or more columns of a relation. The Join command
accomplishes data integration by tying together relations that have a common
primary key/foreign key pair. These three commands can be used in combination to
retrieve the specific data required in a particular query.

KEY TERMS

Alternate key
Attribute
Candidate key
Cell
Column
Data retrieval
Domain of values
Entity identifier
Entity type

Equijoin
Foreign key
Integrating data
Join operator
Natural join
Non-redundant data
Personal computer (PC)
Primary key
Project operator

Redundant data
Relation
Relational algebra
Relational database
Relational model
Row
Select operator
Tuple
Unique attribute

QUESTIONS

1. Why was the commercial introduction of relational
database delayed during the 1970s? What factors
encouraged its introduction in the early 1980s?

2. How does a relation differ from an ordinary file?
3. Define the terms ‘‘tuple’’ and ‘‘attribute.’’
4. What is a relational database?
5. What are the characteristics of a candidate key?
6. What is a primary key? What is an alternate key?
7. Define the term ‘‘foreign key.’’
8. In your own words, describe how foreign keys are

used to set up one-to-many binary relationships in
relational databases.

9. Describe why an additional relation is needed to
represent a many-to-many relationship in a relational
database.

10. Describe what intersection data is, what it describes,
and why it does not describe a single entity.

11. What is a one-to-one binary relationship?
12. Describe the purpose and capabilities of:

a. The relational Select operator.
b. The relational Project operator.
c. The relational Join operator.

13. Describe how the join operator works.

134 C h a p t e r 5 The Relational Database Model: Introduction

EXERCISES

1. The main relation of a motor vehicle registration
bureau’s relational database includes the following
attributes:

Vehicle License Owner
Identification Plate Serial
Number Number Number Manufacturer Model Year Color

The Vehicle Identification Number is a unique num-
ber assigned to the car when it is manufactured. The
License Plate Number is, in effect, a unique number
assigned to the car by the government when it is
registered. The Owner Serial Number is a unique
identifier of each owner. Each owner can own more
than one vehicle. The other attributes are not unique.
What is/are the candidate key(s) of this relation? If
there is more than one candidate key, choose one
as the primary key and indicate which is/are the
alternate key(s).

2. A relation consists of attributes A, B, C, D, E, F, G,
and H.

No single attribute has unique values.
The combination of attributes A and E is unique.
The combination of attributes B and D is unique.
The combination of attributes B and G is unique.
Select a primary key for this relation and indicate

and alternate keys.
3. In the General Hardware Corp. relational database

of Figure 5.14:
a. How many foreign keys are there in each of the

six relations?

b. List the foreign keys in each of the six relations.
4. Identify the relations that support many-to-many

relationships, the primary keys of those relations,
and any intersection data in the General Hardware
Corp. database.

5. Consider the General Hardware Corp. relational
database. Using the informal relational command
language described in this chapter, write commands
to:

a. List the product name and unit price of all of the
products.

b. List the employee names and titles of all the
employees of customer 2198.

c. Retrieve the record for office number 1284.
d. Retrieve the records for customers headquartered

in Los Angeles.
e. Find the size of office number 1209.
f. Find the name of the salesperson assigned to

office number 1209.
g. List the product name and quantity sold of each

product sold by salesperson 361.
6. Consider the General Hardware Corp. relational

database and the data stored in it, as shown
in Figure 5.14. Find the answer to each of the
following queries (written in the informal relational
command language described in this chapter).
a. Select rows from the

CUSTOMER EMPLOYEE

relation in which Customer Number = 2198.
b. Select rows from the CUSTOMER EMPLOYEE

relation in which Customer Number = 2198.
Project Employee Number and Employee Name
over that result.

c. Select rows from the PRODUCT relation in
which Product Number = 21765.

d. Select rows from the PRODUCT relation in
which Product Number = 21765. Project Unit
Price over that result.

e. Join the SALESPERSON and CUSTOMER
relations using the Salesperson Number attribute
of each as the join fields. Select rows from that
result in which Salesperson Name = Baker.
Project Customer Name over that result.

f. Join the PRODUCT relation and the SALES
relation using the Product Number attribute of
each as the join fields. Select rows in which
Product Name = Pliers. Project Salesperson
Number and Quantity over that result.

7. For each of Exercise 6, describe in words what the
query is trying to accomplish.

MINICASES
1. Consider the following relational database for Happy
Cruise Lines. It keeps track of ships, cruises, ports, and
passengers. A ‘‘cruise’’ is a particular sailing of a ship
on a particular date. For example, the seven-day journey

of the ship Pride of Tampa that leaves on June 13,
2009, is a cruise. Note the following facts about this
environment.

Minicases 135

• Both ship number and ship name are unique in the
SHIP Relation.

• A ship goes on many cruises over time. A cruise is
associated with a single ship.
• A port is identified by the combination of port name
and country.

• As indicated by the VISIT Relation, a cruise includes
visits to several ports, and a port is typically included
in several cruises.

• Both Passenger Number and Social Security Number
are unique in the PASSENGER Relation. A particular
person has a single Passenger Number that is used for
all of the cruises that she takes.

• The VOYAGE Relation indicates that a person can
take many cruises and a cruise, of course, has many
passengers.

SHIP Relation

Ship Number Ship Ship Launch Gross
Number Name Builder Date Weight

CRUISE Relation

Cruise Start End Cruise Ship
Number Date Date Director Number

PORT Relation

Port Number Port
Name Country of Docks Manager

VISIT Relation

Cruise Port Arrival Departure
Number Name Country Date Date

PASSENGER Relation

Passenger Passenger Social Security Home Telephone
Number Name Number Address Number

VOYAGE Relation

Passenger Cruise Stateroom
Number Number Number Fare

a. Identify the candidate keys of each relation.
b. Identify the primary key and any alternate keys of

each relation.
c. How many foreign keys does each relation have?
d. Identify the foreign keys of each relation.
e. Indicate any instances in which a foreign key serves

as part of the primary key of the relation in which
it is a foreign key. Why does each of those relations
require a multi-attribute primary key?

f. Identify the relations that support many-to-many
relationships, the primary keys of those relations,
and any intersection data.

g. Using the informal relational command language
described in this chapter, write commands to:
i. Retrieve the record for passenger number 473942.

ii. Retrieve the record for the port of Nassau in the
Bahamas.

iii. List all of the ships built by General Shipbuilding,
Inc.

iv. List the port name and number of docks of every
port in Mexico.

v. List the name and number of every ship.
vi. Who was the cruise director on cruise number

38232?
vii. What was the gross weight of the ship used for

cruise number 39482?
viii. List the home address of every passenger on cruise

number 17543.
2. Super Baseball League Consider the following relational

database for the Super Baseball League. It keeps track
of teams in the league, coaches and players on the teams,
work experience of the coaches, bats belonging to each
team, and which players have played on which teams.
Note the following facts about this environment:

• The database keeps track of the history of all of the
teams that each player has played on and all of the
players who have played on each team.

• The database keeps track of only the current team that
a coach works for.

• Team Number, Team Name, and Player Number are
each unique attributes across the league.

• Coach Name is unique only within a team (and we
assume that a team cannot have two coaches of the
same name).

136 C h a p t e r 5 The Relational Database Model: Introduction

• Serial Number (for bats) is unique only within a team.
• In the AFFILIATION relation, the Years attribute

indicates that number of years that a player played
on a team; the Batting Average is for the years that a
player played on a team.

TEAM Relation

Team Team
Number Name City Manager

COACH Relation

Team Coach Coach
Number Name Telephone

WORK EXPERIENCE Relation

Team Coach Experience Years Of
Number Name Type Experience

BATS Relation

Team Serial
Number Number Manufacturer

PLAYER Relation

Number Name
Player Player Age

AFFILIATION Relation

Player Team Batting
Number Number Years Average

a. Identify the candidate keys of each relation.
b. Identify the primary key and any alternate keys of
each relation.
c. How many foreign keys does each relation have?
d. Identify the foreign keys of each relation.
e. Indicate any instances in which a foreign key serves
as part of the primary key of the relation in which
it is a foreign key. Why does each of those relations
require a multi-attribute primary key?
f. Identify the relations that support many-to-many
relationships, the primary keys of those relations,
and any intersection data.

g. Assume that we add the following STADIUM
relation to the Super Baseball League relational
database. Each team has one home stadium, which
is what is represented in this relation. Assume that a
stadium can serve as the home stadium for only one
team. Stadium Name is unique across the league.

STADIUM Relation

Stadium Year Team
Name Built Size Team Number

What kind of binary relationship exists between the
STADIUM relation and the TEAM relation? Could
the data from the two relations be combined into one
without introducing data redundancy? If so, how?

h. Using the informal relational command language
described in this chapter, write commands to:

i. Retrieve the record for team number 12.
ii. Retrieve the record for coach Adams on team

number 12.
iii. List the player number and age of every player.
iv. List the work experience of every coach.
v. List the work experience of every coach on team

number 25.
vi. Find the age of player number 42459.

vii. List the serial numbers and manufacturers of all
of the Vultures’ (the name of a team) bats.

viii. Find the number of years of college coaching
experience that coach Taylor of the Vultures has.

C H A P T E R 6

THE RELATIONAL DATABASE
MODEL: ADDITIONAL

CONCEPTS

C hapter 5 defined the basic terminology of relational database and then
demonstrated some fundamental ideas about constructing relations in relational

databases and manipulating data in them. The discussion focused on relationships between
two different entity types, i.e. binary relationships. This chapter will go beyond binary
relationships into unary and ternary relationships. It will then address the important issue
of referential integrity.

OBJECTIVES

■ Describe how unary and ternary relationships are implemented in a relational
database.

■ Explain the concept of referential integrity.
■ Describe how the referential integrity restrict, cascade, and set-to-null delete rules

operate in a relational database.

CHAPTER OUTLINE

Introduction
Relational Structures for Unary and

Ternary Relationships

Unary One-to-Many Relationships

Unary Many-to-Many Relationships

Ternary Relationships
Referential Integrity

The Referential Integrity Concept

Three Delete Rules

Summary

138 C h a p t e r 6 The Relational Database Model: Additional Concepts

INTRODUCTION

The previous chapter talked about how binary relationships, i.e. those involving
two entity types, can be constructed in relational databases so that the data can be
integrated while data redundancy is avoided. Unary relationships, with one entity
type, and ternary relationships, with three entity types, while perhaps not quite as
common as binary relationships, are also facts of life in the real world and so must
also be handled properly in relational databases.

Referential integrity addresses a particular issue that can arise between two
tables in a relational database. The issue has to do with a foreign key value in one
table being able to find a matching primary key value in another table during a join
operation. Interestingly, in the older hierarchical and network database management
systems, the equivalents of primary and foreign keys were linked by physical
address pointers and so were always tied together. But, in relational databases, the
tables are basically independent of each other. So, if there are no controls in place,
the proper foreign key-primary key matches can be lost when data is updated or
records are deleted.

This chapter will address the issues of unary relationships, ternary relation-
ships, and referential integrity, all of which will move us much closer to modeling
real-world business environments properly in relational databases.

C O N C E P T S
I N A C T I O N

6-A CITY OF MEMPHIS, TN—VEHICLE SERVICE CENTER

The city of Memphis, TN, is the
18th largest city in the United States in both population
(650,000) and land area (280 square miles). Memphis
was founded in 1819 by General/President Andrew
Jackson and others and was incorporated as a city in
1826. Because of its position on the Mississippi River in
the midst of the country’s largest cotton-farming region,
Memphis has traditionally been the center of the U.S.
cotton industry. It is still the world’s largest spot-cotton
market and also the world’s largest hardwood market.
The concept of the grocery supermarket was invented in
Memphis in the early 1900s and the concept of the motel
chain was invented in Memphis in the 1950s. Today,
because of its central location in the country and because
of its position as a major transportation hub, Memphis is
known as the ‘‘Distribution Center’’ of the United States.

The Vehicle Service Center of the Memphis city
government’s General Services Division is responsible
for all of the city’s municipal vehicles except for Fire
Department vehicles. The approximately 4,000 vehicles

include everything from police cruisers and sanitation
trucks to street cleaners and even lawn-mowing tractors.
Since 1991, the city has kept track of all these vehicles
with a database application that manages them through
their complete lifecycle. New vehicles are entered into
the database when they are purchased and they’re
assigned to a city department. The application then keeps
each vehicle’s maintenance history, generates automatic
reports on maintenance due dates, tracks mileage and
gasoline use, and produces monthly reports for the
departments listing all of this activity for each of their
vehicles. Finally, the system tracks the reassignment of
older vehicles and the auctioning of vehicles being
disposed of.

Memphis’ vehicle tracking system uses an Oracle
database running on Dell servers. For vehicle main-
tenance, the system’s major tables include a Vehicle
Downtime Detail table with 1.6 million records, a Work
Order Job Notes table with 3.3 million records, and a
Parts Journal table with 950,000 records.

Relational Structures for Unary and Ternary Relationships 139

‘‘Photo by Permission of City of Memphis’’

RELATIONAL STRUCTURES FOR UNARY AND TERNARY RELATIONSHIPS

Unary One-to-Many Relationships

Let’s continue with the General Hardware Co. example of Figure 5.14, reprinted
here for convenience as Figure 6.1. Suppose that General Hardware’s salespersons
are organized in such a way that some of the salespersons, in addition to having
their customer responsibilities, serve as the sales managers of other salespersons,
Figure 6.2. A salesperson reports to exactly one sales manager, but each salesperson
who serves as a sales manager typically has several salespersons reporting to
him. Thus, there is a one-to-many relationship within the set or entity type of
salespersons.

Salesperson who is also a sales manager Salesperson

This is known as a unary one-to-many relationship. It is unary because there is
only one entity type involved. It is one-to-many because among the individual
entity occurrences, i.e. among the salespersons, a particular salesperson reports

140 C h a p t e r 6 The Relational Database Model: Additional Concepts

F I G U R E 6.1
General Hardware Company relational
database

(a) SALESPERSON relation
Salesperson Salesperson Commission Year Office
Number Name Percentage of Hire Number
137 Baker 10 1995 1284
186 Adams 15 2001 1253
204 Dickens 10 1998 1209
361 Carlyle 20 2001 1227
(b) CUSTOMER relation
Customer Customer Salesperson
Number Name Number HQ City
0121 Main St. Hardware 137 New York
0839 Jane’s Stores 186 Chicago
0933 ABC Home Stores 137 Los Angeles
1047 Acme Hardware Store 137 Los Angeles
1525 Fred’s Tool Stores 361 Atlanta
1700 XYZ Stores 361 Washington
1826 City Hardware 137 New York
2198 Western Hardware 204 New York
2267 Central Stores 186 New York
(c) CUSTOMER EMPLOYEE relation
Customer Employee Employee
Number Number Name Title
0121 27498 Smith Co-Owner
0121 30441 Garcia Co-Owner
0933 25270 Chen VP Sales
0933 30441 Levy Sales Manager
0933 48285 Morton President
1525 33779 Baker Sales Manager
2198 27470 Smith President
2198 30441 Jones VP Sales
2198 33779 Garcia VP Personnel
2198 35268 Kaplan Senior Accountant
(Continues)

to one salesperson who is his sales manager while a salesperson who is a sales
manager may have several salespersons reporting to her. Note that, in general, this
arrangement can involve as few as two levels of entity occurrences or can involve
many levels in a hierarchical arrangement. In general, in a company, an employee
can report to a manager who in turn reports to a higher-level manager, and so on up
to the CEO.

Relational Structures for Unary and Ternary Relationships 141

F I G U R E 6.1 (Continued)
General Hardware Company relational
database

(d) PRODUCT relation

Product Number Unit
Number Name Price

16386 Wrench 12.95
19440 Hammer 17.50
21765 Drill 32.99
24013 Saw 26.25
26722 Pliers 11.50
(e) SALES relation
Salesperson Product
Number Number Quantity
137 19440 473
137 24013 170
137 26722 688
186 16386 1745
186 19440 2529
186 21765 1962
186 24013 3071
204 21765 809
204 26722 734
361 16386 3729

361 21765 3110

361 26722 2738

(f) OFFICE relation
Office
Number Telephone Size (sq. ft.)
1253 901-555-4276 120
1227 901-555-0364 100
1284 901-555-7335 120
1209 901-555-3108 95

Assume that the General Hardware Co. has two levels of sales managers,
resulting in a three-level hierarchy. That is, each salesperson reports to a sales
manager (who is himself a salesperson) and each sales manager reports to one of
several chief sales managers (who is herself a salesperson). Figure 6.3 shows two
levels of sales managers plus the salespersons who report to them. For example,
salespersons 142, 323, and 411 all report to salesperson (and sales manager) 137.
Salespersons 137 and 439, both of whom are sales managers, report to salesperson
186 who is a chief sales manager. As you go upward in the hierarchy, each
salesperson is associated with exactly one other salesperson. As you go downward
in the hierarchy from any salesperson/sales manager, each salesperson/sales manager

142 C h a p t e r 6 The Relational Database Model: Additional Concepts

F I G U R E 6.2
Salespersons 142, 323, and 411
reporting to salesperson 137 who is their
sales manager

Salesperson 142 Salesperson 323 Salesperson 411

Sales Manager
Salesperson 137

Reports to

F I G U R E 6.3
General Hardware Company salesperson
reporting hierarchy

137

412
323
411

186

439

170
267

198
204

285

483

361
388
446

is associated with many salespersons below, except for the bottom-level salespersons
who are not sales managers and thus have no one reporting to them.

Figure 6.4, which is an expansion of the General Hardware Co. SALESPER-
SON relation in Figure 6.1a, demonstrates how this type of relationship is reflected
in a relational database. A one-to-many unary relationship requires the addition of
one column to the relation that represents the single entity involved in the unary
relationship. In Figure 6.4, the Sales Manager Number attribute is the new attribute
that has been added to the SALESPERSON relation. The domain of values of the
new column is the same as the domain of values of the relation’s primary key. Thus,
the values in the new Sales Manager Number column will be three-digit whole
numbers representing the unique identifiers for salespersons, just like the values
in the Salesperson Number column. The value in the new column for a particular
row represents the value of the next entity ‘‘upward’’ in the unary one-to-many
hierarchy. For example, in the row for salesperson number 323, the sales manager

Relational Structures for Unary and Ternary Relationships 143

F I G U R E 6.4
General Hardware Company
SALESPERSON relation including Sales
Manager Number attribute

SALESPERSON relation

Salesperson Salesperson Commission Year Sales Manager
Number Name Percentage of Hire Number

137 Baker 10 1995 186

142 Smith 15 2001 137

170 Taylor 18 1992 439

186 Adams 15 2001

198 Wang 20 1990 267

204 Dickens 10 1998 267

267 Perez 22 2000 285

285 Costello 10 1996

323 McNamara 15 1995 137

361 Carlyle 20 2001 483

388 Goldberg 20 1997 483

411 Davidson 18 1992 137

439 Warren 10 1996 186

446 Albert 10 2001 483

483 Jones 15 1995 285

value is 137 because salesperson 323’s sales manager is salesperson/sales manager
137, as shown in Figure 6.3. Similarly, the row for salesperson 137, who happens
also to be a sales manager, shows salesperson number 186 in its sales manager
number column. Salesperson/sales manager 137 reports to chief sales manager 186,
also as shown in Figure 6.3. The sales manager column value for salesperson/chief
sales manager 186 is blank because the reporting structure happens to end with each
chief sales manager; i.e., there is nothing ‘‘above’’ salesperson 186 in Figure 6.3.

Note that a unary one-to-one relationship, for example one salesperson
backing-up another (see Figure 2.7a) is handled in a manner similar to Figure 6.4.
The difference is that the Sales Manager Number column would be replaced by
a Back-Up Number column and a particular salesperson number would appear at
most once in that column.

Unary Many-to-Many Relationships

The unary many-to-many relationship is a special case that has come to be known
as the ‘‘bill of materials’’ problem. Among the entity occurrences of a single entity
type, which is what makes this ‘‘unary,’’ each particular entity occurrence can be
related to many other occurrences and each of those latter occurrences can, in turn,
be related to many other occurrences. Put another way, every entity occurrence
can be related to many other occurrences, which, if you think about it, makes this
a many-to-many relationship because only one entity type is involved. (Yes, that
sounds a little strange, but keep reading.) The general idea is that in a complex
item, say an automobile engine, small parts are assembled together to make a small
component or assembly. Then some of those small components or assemblies (and
maybe some small parts) are assembled together to make medium-sized components
or assemblies, and so on until the final, top-level ‘‘component’’ is the automobile
engine. The key concept here is that an assembly at any level is considered to be

144 C h a p t e r 6 The Relational Database Model: Additional Concepts

F I G U R E 6.5
General Hardware Company product bill of
materials

Wrench Model A (#11)

Wrench Model B (#14)
Deluxe Wrench Set (#43)

Wrench Model C (#17) Supreme Tool Set (#53)

Wrench Model D (#19)
Master Wrench Set (#44)

Hammer Model A (#22)

Hammer Model B (#24) Deluxe Hammer Set (#48)
Grand ToolSet (#56)

Hammer Model C (#28)

Drill Model A (#31)

Drill Model B (#35)

both a part made up of smaller units and a unit that can be a component of a larger
part. Parts and assemblies at all levels are all considered occurrences of the same
entity type and they all have a unique identifier in a single domain of values.

Certainly, this requires an example! Figure 6.5 illustrates this concept using
an expansion of General Hardware Co.’s product set.

Product Product

The numbers in parentheses are product numbers. Assume, as is quite reasonable,
that General Hardware not only sells individual tools but also sells sets of tools. Both
individual tools and sets of tools are considered to be ‘‘products,’’ which also makes
sense. As shown in Figure 6.5, General Hardware carries several types (or perhaps
sizes) of wrenches, hammers, and drills. Various combinations of wrenches and
hammers are sold as wrench and hammer sets. Various combinations of these sets
and other tools such as drills are sold as even larger sets. Very importantly, notice
the many-to-many nature of this arrangement. For example, the Master Wrench Set
(product number 44), looking to its left, is comprised of three different wrenches,
including Wrench Model A (#11). Conversely, Wrench Model A, looking to its
right, is a component of two different wrench sets, both the Deluxe Wrench Set
(#43) and the Master Wrench Set (#44). This demonstrates the many-to-many nature
of products. Similarly, both the Supreme Tool Set (#53) and the Grand Tool Set
(#56) are, obviously, comprised of several smaller sets and tools, while the Deluxe
Hammer Set (#48) is a component of both the Supreme Tool Set (#53) and the
Grand Tool Set (#56).

How can this unary many-to-many relationship be represented in a relational
database? First of all, note that Figure 6.6 is a modification and expansion of the
PRODUCT relation in the General Hardware Co. relational database of Figure 6.1d.
Note that the product numbers matching the product numbers in Figure 6.5 have
been reduced to two digits for simplicity in the explanation. Every individual unit
item and every set in Figure 6.5 has its own row in the relation in Figure 6.6 because
every item and set in Figure 6.5 is a product that General Hardware has for sale.

Now, here is the main point. Just as a binary many-to-many relationship
requires the creation of an additional relation in a relational database, so does
a unary many-to-many relationship. The new additional relation is shown in
Figure 6.7. It consists of two attributes. The domain of values of each column is
that of the Product Number column in the PRODUCT relation of Figure 6.6. The
relation of Figure 6.7 represents, in a tabular format, the way that the assemblies
of Figure 6.5 are constructed. The first two rows of Figure 6.7 literally say that
product (assembly) number 43 (the Deluxe Wrench Set) is comprised of products

Relational Structures for Unary and Ternary Relationships 145

F I G U R E 6.6
General Hardware Company modified
PRODUCT relation

PRODUCT relation
Product Product Unit
Number Name Price

11 Wrench Model A 12.50

14 Wrench Model B 13.75

17 Wrench Model C 11.62

19 Wrench Model D 15.80

22 Hammer Model A 17.50

24 Hammer Model B 18.00

28 Hammer Model C 19.95

31 Drill Model A 31.25

35 Drill Model B 38.50

43 Deluxe Wrench Set 23.95

44 Master Wrench Set 35.00

48 Deluxe Hammer Set 51.00

53 Supreme Tool Set 100.00

56 Grand Tool Set 109.95

F I G U R E 6.7
General Hardware Company unary
many-to-many relation

Assembly Part

43 11

43 14

44 11

44 17

44 19

48 22

48 24

48 28

53 43

53 48

53 31

56 44

56 48

56 35

11 and 14, as indicated in Figure 6.5. Next, product (assembly) 44 is comprised
of products 11, 17, and 19. Moving to the last three rows of the relation, product
(assembly) 56 is comprised of products 44 and 48, both of which happen to be
assemblies, and product 35. Again, notice the many-to-many relationship as it is
represented in the relation of Figure 6.7. The first two rows indicate that assembly
43 is comprised of two parts. Conversely, the first and third rows indicate that part
11 is a component of two different assemblies.

146 C h a p t e r 6 The Relational Database Model: Additional Concepts

Ternary Relationships

A ternary relationship is a relationship that involves three different entity types. If
the entity types are A, B, and C, then we might illustrate this as:

B

C

A

To demonstrate this concept in the broadest way using the General Hardware
Co. database, let’s slightly modify part of the General Hardware premise. The
assumption has always been that there is a one-to-many relationship between
salespersons and customers. A salesperson is responsible for several customers,
while a customer is in contact with (is sold to by) exactly one of General Hardware’s
salespersons. For the purposes of describing a general ternary relationship,
we change that premise temporarily to a many-to-many relationship between
salespersons and customers. That is, we now assume that any salesperson can make
a sale to any customer and any customer can buy from any salesperson.

With that change, consider the ternary relationship among salespersons,
customers, and products. Such a relationship allows us to keep track of which
salesperson sold which product to which customer. This is very significant. In this
environment, a salesperson can sell many products and a salesperson can sell to
many customers. A product can be sold by many salespersons and can be sold
to many customers. A customer can buy many products and can buy from many
salespersons. All of this leads to a lot of different possibilities for any given sale. So,
it is very important to be able to tie down a particular sale by noting and recording
which salesperson sold which product to which customer. For example, we might
store the fact that salesperson 137 sold some of product number 24013 to customer
0839, Figure 6.8.

Relations a, b, and c of Figure 6.9 show the SALESPERSON, CUSTOMER,
and PRODUCT relations, respectively, from the General Hardware relational
database of Figure 6.1, except for one change. Since there is no longer a one-to-
many relationship between salespersons and customers, the Salesperson Number
foreign key in the CUSTOMER relation has been removed! The three relations are
now all quite independent with no foreign keys in any of them.

Figure 6.9d, the SALES relation, shows how this ternary relationship is
represented in a relational database. Similarly to how we created an additional
relation to accommodate a binary many-to-many relationship, an additional relation
has to be created to accommodate a ternary relationship, and that relation is
Figure 6.9d. Clearly, as in the binary many-to-many case, the primary key of the
additional relation will be (at least) the combination of the primary keys of the
entities involved in the relationship. Thus, in Figure 6.9d, the Salesperson Number,
Customer Number, and Product Number attributes all appear as foreign keys and
the combination of the three serve as part of the primary key. Why just ‘‘part of’’

Relational Structures for Unary and Ternary Relationships 147

F I G U R E 6.8
A ternary relationship

Customer 0839

Salesperson 137

Salesperson 137 sold
Product 24013 to
Customer 0839

Product 24013

F I G U R E 6.9
A portion of General Hardware Company
relational database modified to
demonstrate a ternary relationship

(a) SALESPERSON relation
Salesperson Salesperson Commission Year
Number Name Percentage of Hire
137 Baker 10 1995
186 Adams 15 2001
204 Dickens 10 1998
361 Carlyle 20 2001
(b) CUSTOMER relation
Customer Customer
Number Name HQ City
0121 Main St. Hardware New York
0839 Jane’s Stores Chicago
0933 ABC Home Stores Los Angeles
1047 Acme Hardware Store Los Angeles
1525 Fred’s Tool Stores Atlanta
1700 XYZ Stores Washington
1826 City Hardware New York
2198 Western Hardware New York
2267 Central Stores New York
(Continues)

148 C h a p t e r 6 The Relational Database Model: Additional Concepts

F I G U R E 6.9 (Continued)
A portion of General Hardware Company
relational database modified to
demonstrate a ternary relationship

(c) PRODUCT relation

Product Product Unit
Number Name Price
16386 Wrench 12.95
19440 Hammer 17.50
21765 Drill 32.99
24013 Saw 26.25
26722 Pliers 11.50

(d) SALES relation

Salesperson Customer Product
Number Number Number Date Quantity

137 0839 24013 2/21/2002 25

361 1700 16386 2/27/2002 70

137 2267 19440 3/1/2002 40

204 1047 19440 3/1/2002 15

186 0839 26722 3/12/2002 35

137 1700 16386 3/17/2002 65

361 0121 21765 3/21/2002 40

204 2267 19440 4/03/2002 30

204 0839 19440 4/17/2002 20

the primary key? Because in this example, a particular salesperson may have sold
a particular product to a particular customer more than once on different dates.
Thus the Date attribute must also be part of the primary key. (We assume that this
combination of the three could not have happened more than once on the same
date. If it could, then there would also need to be a ‘‘time’’ attribute in the key.)
Recall that this need for an additional attribute in the primary key also came up
when we discussed binary many-to-many relationships in the last chapter. Finally,
the Quantity attribute in Figure 6.9d is intersection data, just as it would be in a
binary many-to-many relationship. The quantity of the product that the salesperson
sold to the customer is clearly an attribute of the ternary relationship, not of any one
of the entities.

There is one more important point to make about ternary relationships. In the
process of describing the ternary relationship, you may have noticed that, taken two
at a time, every pair of the three entities, salespersons, customers, and products, are
in a binary many-to-many relationship. In general, this would be shown as:

A B

B C

A C

The question is: are these three many-to-many relationships the equivalent of
the ternary relationship? Do they provide the same information that the ternary
relationship does? The answer is, no!

Relational Structures for Unary and Ternary Relationships 149

F I G U R E 6.10
Ternary relationship counter-example

(a) Salespersons and customers

Salesperson 137 Customer 0839

Salesperson 204 Customer 1826

(b) Customers and products

Customer 0839 Product 19440

Customer 1826 Product 24013

(c) Salespersons and products

Salesperson 137 Product 19440

Salesperson 204 Product 24013

Again, consider salespersons, customers, and products. You might know that
a particular salesperson has made sales to a particular customer. You might also
know that a particular salesperson has sold certain products at one time or another.
And,you might know that a particular customer has bought certain products. But all
of that is not the same thing as knowing that a particular salesperson sold a particular
product to a particular customer. Still skeptical? Look at Figure 6.10. Parts a, b, and
c of the figure clearly illustrate three many-to-many relationships. They are between
(a) salespersons and customers, (b) customers and products, and (c) salespersons
and products. Part a shows, among other things, that salesperson 137 sold something
to customer 0839. Part b shows that customer 0839 bought product 19440. Does that
mean that we can infer that salesperson 137 sold product 19440 to customer 0839?
No! That’s a possibility and, indeed, part c of the figure shows that salesperson 137
did sell product 19440. But part c of the figure also shows that salesperson 204
sold product 19440. Is it possible that salesperson 204 sold it to customer 0839?
According to part a, salesperson 204 sold something to customer 0839, but it doesn’t
indicate what. You can go around and around Figure 6.10 and never conclude with
certainty that salesperson 137 sold product 19440 to customer 0839. That would

Y O U R
T U R N

6.1 TERNARY RELATIONSHIPS

Ternary relationships are all around
us. Think about an automobile dealership. Certainly the
dealership management wants to keep track of which
car was sold to which customer by which salesperson.
Certainly this is important for billing, accounting, and
commission purposes. But also, in that kind of high-
priced product environment, it’s simply good business
to keep track of such information for future marketing and
customer relationship reasons.

QUESTION:

Consider a hospital environment involving patients,
doctors, nurses, procedures, medicines, hospital rooms,
etc. Make a list of five ternary relationships in this
environment. Remember that each one has to make
sense from a business point of view.

150 C h a p t e r 6 The Relational Database Model: Additional Concepts

require a ternary relationship and a relation like the one in Figure 6.9d. Notice that
the last row of Figure 6.9d shows, without a doubt, that it was salesperson 204 who
sold product 19440 to customer 0839.

REFERENTIAL INTEGRITY

The Referential Integrity Concept

Thus far in this chapter and the previous one, we have been concerned with how
relations are constructed and how data can be retrieved from them. Data retrieval is
the operation that clearly provides the ultimate benefit from maintaining a database,
but it is not the only operation needed. Certainly, we should expect that, as with any
data storage scheme, in addition to retrieving data we must be prepared to perform
such data maintenance operations as inserting new records (or rows of a relation),
deleting existing records, and updating existing records. All database management
systems provide the facilities and commands to accomplish these data maintenance
operations. But there are some potential pitfalls in these operations that must be
dealt with.

The problem is that the logically related (by foreign keys) but physically
independent nature of the relations in a relational database exposes the database
to the possibility of a particular type of data integrity problem. This problem has
come to be known as a referential integrity problem because it revolves around
the circumstance of trying to refer to data in one relation in the database, based
on values in another relation. (Actually, referential integrity is an issue in all of
the DBMS approaches, not just the relational approach. We discuss this issue here
because we are focusing on relational databases and the concept is much easier
to explain in the context of an example, again the General Hardware database.)
Also, while referential integrity problems can surface in any of the three operations
that result in changes to the database—insert, delete, and update records—we will
generally use the case of delete to explain the concept while mentioning insert and
update where appropriate.

First, consider the situation of record deletion in the two relations of
Figure 6.11, which is a repeat of Figure 5.2. Suppose that salesperson 361, Carlyle,
left the company and his record was deleted from the SALESPERSON relation. The
problem is that there are still two records in the CUSTOMER relation (the records
for customers 1525 and 1700) that refer to salesperson 361, i.e. that have the value
361 in the Salesperson Number foreign key attribute. It is as if Carlyle left the
company and his customers have not as yet been reassigned to other salespersons.
If a relational join command was issued to join the two relations in order to (say)
find the name of the salesperson responsible for customer 1525, there would be a
problem. The relational DBMS would pick up the salesperson number value 361 in
the record for customer 1525 in the CUSTOMER relation, but would not be able to
match 361 to a record in the SALESPERSON relation because there no longer is a
record for salesperson 361 in the SALESPERSON relation—it was deleted! Notice
that the problem arose because the deleted record, a salesperson record, was on
the ‘‘one side’’ of a one-to-many relationship. What about the customer records on
the ‘‘many side’’ of the one-to-many relationship? Suppose customer 1047, Acme
Hardware Store, is no longer one of General Hardware’s customers. Deleting the
record for customer 1047 in the CUSTOMER relation has no referential integrity
exposure. Nothing else in these two relations refers to customer 1047.

Referential Integrity 151

F I G U R E 6.11
General Hardware Company
SALESPERSON and CUSTOMER relations

(a) SALESPERSON relation
Salesperson Salesperson Commission Year
Number Name Percentage of Hire
137 Baker 10 1995
186 Adams 15 2001
204 Dickens 10 1998
361 Carlyle 20 2001
(b) CUSTOMER relation
Customer Customer Salesperson
Number Name Number HQ City
0121 Main St. Hardware 137 New York
0839 Jane’s Stores 186 Chicago
0933 ABC Home Stores 137 Los Angeles
1047 Acme Hardware Store 137 Los Angeles
1525 Fred’s Tool Stores 361 Atlanta
1700 XYZ Stores 361 Washington
1826 City Hardware 137 New York
2198 Western Hardware 204 New York
2267 Central Stores 186 New York

Similar referential integrity arguments can be made for the record insertion
and update operations, but the issue of whether the exposure is on the ‘‘one side’’
or the ‘‘many side’’ of the one-to-many relationship changes! Again, in the case
of deletion, the problem occurred when a record was deleted on the ‘‘one side’’
of the one-to-many relationship. But, for insertion, if a new salesperson record is
inserted into the Salesperson relation, i.e. a new record is inserted into the ‘‘one
side’’ of the one-to-many relationship, there is no problem. All it means is that a
new salesperson has joined the company but, as yet, has no customer responsibility.
On the other hand, if a new customer record is inserted into the CUSTOMER
relation, i.e. a new record is inserted into the ‘‘many side’’ of the one-to-many
relationship, and it happens to include a salesperson number that does not have a
match in the SALESPERSON relation, that would cause the same kind of problem
as the deletion example above. Similarly, the update issue would concern updating
a foreign key value, i.e. a salesperson number in the CUSTOMER relation with a
new salesperson number that has no match in the SALESPERSON relation.

The early relational DBMSs did not provide any control mechanisms for
referential integrity. Programmers and users were on their own to keep track of
it and this upset many people. This was particularly the case because referential
integrity issues in the older hierarchical and network DBMSs were more naturally
controlled by the nature of the hierarchical and network data structures on which they
were based, at the expense of some flexibility in database design. Modern relational
DBMS’s provide sophisticated control mechanisms for referential integrity with
so-called ‘‘delete rules,’’ ‘‘insert rules,’’ and ‘‘update rules.’’ These rules are
specified between pairs of relations. We will take a look at the three most common
delete rules, ‘‘restrict,’’ ‘‘cascade,’’ and ‘‘set-to-null,’’ to illustrate the problem.

152 C h a p t e r 6 The Relational Database Model: Additional Concepts

F I G U R E 6.12
Delete rule: Restrict

Customer 1700

Customer 1525

Salesperson 361
Mr. Carlyle

Delete Rule: Restrict

Three Delete Rules

Delete Rule: Restrict Again, consider the two relations in Figure 6.11. If the delete
rule between the two relations is restrict and an attempt is made to delete a record on
the ‘‘one side’’ of the one-to-many relationship, the system will forbid the delete to
take place if there are any matching foreign key values in the relation on the ‘‘many
side.’’ For example, if an attempt is made to delete the record for salesperson 361 in
the SALESPERSON relation, the system will not permit the deletion to take place
because the CUSTOMER relation records for customers 1525 and 1700 include
salesperson number 361 as a foreign key value, Figure 6.12. This is as if to say,
‘‘You can’t delete a salesperson record as long as there are customers for whom
that salesperson is responsible.’’ Clearly, this is a reasonable and necessary course
of action in many business situations.

Delete Rule: Cascade If the delete rule between the two relations is cascade and
an attempt is made to delete a record on the ‘‘one side’’ of the relationship, not
only will that record be deleted but all of the records on the ‘‘many side’’ of the
relationship that have a matching foreign key value will also be deleted. That is,
the deletion will cascade from one relation to the other. For example, if an attempt
is made to delete the record for salesperson 361 in the SALESPERSON relation
and the delete rule is cascade, that salesperson record will be deleted and so too,
automatically, will the records for customers 1525 and 1700 in the CUSTOMER
relation because they have 361 as a foreign key value, Figure 6.13. It is as if the
assumption is that when a salesperson leaves the company she always takes all of
her customers along with her. While that might be a bit of a stretch in this case,
there are many other business situations where it is not a stretch at all. For example,
think about a company that has a main employee relation with name, home address,
telephone number, etc., plus a second relation that lists and describes the several
skills of each employee. Certainly, when an employee leaves the company you
would expect to delete both his record in the main employee relation and all his
records in the skills relation.

Delete Rule: Set-to-Null If the delete rule between the two relations is set-to-null
and an attempt is made to delete a record on the ‘‘one side’’ of the one-to-many
relationship, that record will be deleted and the matching foreign key values in

Summary 153

Customer 1700
Customer 1525
Salesperson 361
Mr. Carlyle

Delete Rule: Cascade

F I G U R E 6.13
Delete rule: Cascade

F I G U R E 6.14
Delete rule: Set-to-Null

Salesperson 361
Mr. Carlyle
Customer 1700
Customer 1525

Delete Rule: Set-to-Null

Temporarily
Without a
Saleperson
Assigned

the records on the ‘‘many side’’ of the relationship will be changed to null. For
example, if an attempt is made to delete the record for salesperson 361 in the
SALESPERSON relation, that record will be deleted, and the Salesperson Number
attribute values in the records for customers 1525 and 1700 in the CUSTOMER
relation will be changed from 361 to null, Figure 6.14. This is as if to say, ‘‘You
can delete a salesperson record and, we will indicate that, temporarily at least, their
former customers are without a salesperson.’’ Obviously this is the appropriate
response in many business situations.

SUMMARY

Relational databases must be capable of handling unary and ternary relationships,
as well as binary relationships. All of these have to promote data integration while
avoiding data redundancy. As this chapter demonstrated, the relational database
concept is up to this task.

154 C h a p t e r 6 The Relational Database Model: Additional Concepts

Referential integrity is an important issue in relational databases. Relational
database management systems must be able to allow users to specify referential
integrity controls between related tables. Otherwise, changes to one table that are
not coordinated with a related table may cause serious data integrity problems.

KEY TERMS

Cascade delete rule
Delete rules
Entity occurrence

Insert rules
Record deletion
Referential integrity

Restrict delete rule
Set-to-null delete rule
Update rules

QUESTIONS

1. Describe the concept of the unary one-to-many
relationship.

2. How is a unary one-to-many relationship con-
structed in a relational database?

3. Describe the concept of the unary many-to-many
relationship.

4. How is a unary many-to-many relationship con-
structed in a relational database?

5. Describe the concept of the ternary relationship.

6. How is a ternary relationship constructed in a
relational database?

7. Is a ternary relationship the equivalent of the
three possible binary relationships among the three
entities involved? Explain.

8. Describe the problem of referential integrity.
9. Compare and contrast the three delete rules: restrict,

cascade, and set-to-null.

EXERCISES

1. Leslie’s Auto Sales has a relational database with
which it maintains data on its salespersons, its
customers, and the automobiles it sells. Each of these
three entity types has a unique attribute identifier.
The attributes that it stores are as follows:

• Salesperson Number (unique), Salesperson Name,
Salesperson Telephone, Years with Company

• Customer Number (unique), Customer Name,
Customer Address, Value of Last Purchase From
Us

• Vehicle Identification Number (unique), Manu-
facturer, Model, Year, Sticker Price Leslie’s also
wants to keep track of which salesperson sold
which car to which customer, including the date
of the sale and the negotiated price. Construct a
relational database for Leslie’s Auto Sales.

2. The State of New York certifies firefighters through-
out the state and must keep track of all of them,
as well as of the state’s fire departments. Each
fire department has a unique department number, a
name that also identifies its locale (city, county, etc.),
the year it was established, and its main telephone

number. Each certified firefighter has a unique fire-
fighter number, a name, year of certification, home
telephone number, and a rank (firefighter, fire lieu-
tenant, fire captain, etc.) The state wants to record the
fire department for which each firefighter currently
works and each firefighter’s supervisor. Supervi-
sors are always higher-ranking certified firefighters.
Construct a relational database for New York’s fire
departments and firefighters.

3. The ABC Consulting Corp. contracts for projects
that, depending on their size and skill requirements,
can be assigned to an individual consultant or to
a team of consultants. A consultant or a team can
work on several projects simultaneously. Several
employees can be organized into a team. Larger
teams can consist of a combination of smaller teams,
sometimes with additional individual consultants
added. This pattern can continue to larger and larger
teams. ABC wants to keep track of its consultants,
teams, and projects, including which consultant or
team is responsible for each project. Each consultant
has a unique employee number, plus a name, home
address, and telephone number. Each project has a

Minicases 155

unique project number, plus a name, budgeted cost,
and due date. Construct a relational database for
ABC Consulting. Hint: You may want to develop
an attribute called ‘‘responsible party’’ that can
be either a team or an individual consultant. Each
project has one responsible party that is responsible
for its completion. Or you may want to think of an
individual consultant as a potential ‘‘team of one’’
and have the responsibility for each project assigned
to a ‘‘team’’ that could then be an individual
consultant or a genuine team.

4. Consider the General Hardware Corp. database
of Figure 6.1. Describe the problem of referen-
tial integrity in terms of the CUSTOMER and
CUSTOMER EMPLOYEE relations if the record
for customer 2198 in the CUSTOMER relation is
deleted. (Assume that no delete rules exist.)

5. In the General Hardware Corp. database of
Figure 6.1, what would happen if:
a. The delete rule between the CUSTOMER and

CUSTOMER EMPLOYEE relations is restrict
and an attempt is made to delete the record for
customer 2198 in the CUSTOMER relation?

b. The delete rule between the CUSTOMER and
CUSTOMER EMPLOYEE relations is cascade
and an attempt is made to delete the record for
customer 2198 in the CUSTOMER relation?

c. The delete rule between the CUSTOMER and
CUSTOMER EMPLOYEE relations is set-to-
null and an attempt is made to delete the record
for customer 2198 in the CUSTOMER relation?

d. The delete rule between the CUSTOMER and
CUSTOMER EMPLOYEE relations is restrict
and an attempt is made to delete the record
for employee 33779 of customer 2198 in the
CUSTOMER EMPLOYEE relation?

e. The delete rule between the CUSTOMER and
CUSTOMER EMPLOYEE relations is cascade
and an attempt is made to delete the record
for employee 33779 of customer 2198 in the
CUSTOMER EMPLOYEE relation?

f. The delete rule between the CUSTOMER and
CUSTOMER EMPLOYEE relations is set-to-
null and an attempt is made to delete the record
for employee 33779 of customer 2198 in the
CUSTOMER EMPLOYEE relation?

MINICASES

1. Happy Cruise Lines
a. Look at the Happy Cruise Lines database of Chapter

5, Minicase 1 but, for this question, consider only
the SHIP, PORT, and PASSENGER relations. The
company wants to keep track of which passengers
visited which ports on which ships on which dates.
Reconstruct these three relations as necessary and/or
add additional relation(s) as necessary to store this
information.

b. Consider the following data from the SHIP and
CRUISE relations of the Happy Cruise Lines database
of Chapter 5, Minicase 1:

SHIP Relation

Ship Ship Ship Launch Gross
Number Name Builder Date Weight

005 Sea Joy Jones 1999 80,000

009 Ocean IV Ajax 2003 75,000

012 Prince Al Ajax 2004 90,000

020 Queen Shirley Master 1999 80,000

CRUISE Relation
Cruise Start End Cruise Ship
Number Date Date Director Number

21644 7/5/2002 7/12/2002 Smith 009

23007 8/14/2002 8/24/2002 Chen 020

24288 3/28/2003 4/4/2003 Smith 009

26964 7/1/2003 7/11/2003 Gomez 020

27045 7/15/2003 7/22/2003 Adams 012

28532 8/17/2003 8/24/2003 Adams 012

29191 12/20/2003 12/27/2003 Jones 009

29890 1/15/2004 1/22/2004 Levin 020

What would happen if:
i. The delete rule between the SHIP and CRUISE

relations is restrict and an attempt is made to
delete the record for ship number 012 in the
SHIP relation?

ii. The delete rule between the SHIP and CRUISE
relations is restrict and an attempt is made to

156 C h a p t e r 6 The Relational Database Model: Additional Concepts

delete the record for ship number 005 in the
SHIP relation?

iii. The delete rule between the SHIP and CRUISE
relations is cascade and an attempt is made to
delete the record for ship number 012 in the
SHIP relation?

iv. The delete rule between the SHIP and CRUISE
relations is cascade and an attempt is made to
delete the record for ship number 005 in the
SHIP relation?

v. The delete rule between the SHIP and CRUISE
relations is set-to-null and an attempt is made to
delete the record for ship number 012 in the SHIP
relation?

vi. The delete rule between the SHIP and CRUISE
relations is set-to-null and an attempt is made to
delete the record for ship number 005 in the SHIP
relation?

vii. The delete rule between the SHIP and CRUISE
relations is restrict and an attempt is made to
delete the record for cruise number 26964 in the
CRUISE relation?

viii. The delete rule between the SHIP and CRUISE
relations is cascade and an attempt is made to
delete the record for cruise number 26964 in the
CRUISE relation?

ix. The delete rule between the SHIP and CRUISE
relations is set-to-null and an attempt is made to
delete the record for cruise number 26964 in the
CRUISE relation?

2. Super Baseball League
a. In the Super Baseball League database of Chapter

5, Minicase 2, assume that instead of having
coaches who are different from players, now some

of the players serve as coaches to other players.
A player/coach can have several players whom
he coaches. Each player is coached by only one
player/coach. Reconstruct the database structure to
reflect this change.

b. In the Super Baseball League database of Chapter
5, Minicase 2, assume that the TEAM relation has
a record for team number 17 and that the COACH
relation has records for three coaches on that team.
What would happen if:
i. The delete rule between the TEAM and COACH

relations is restrict and an attempt is made to
delete the record for team 17 in the TEAM
relation?

ii. The delete rule between the TEAM and COACH
relations is cascade and an attempt is made to
delete the record for team 17 in the TEAM
relation?

iii. The delete rule between the TEAM and COACH
relations is set-to-null and an attempt is made
to delete the record for team 17 in the TEAM
relation?

iv. The delete rule between the TEAM and COACH
relations is restrict and an attempt is made to
delete the record for one of team 17’s coaches in
the COACH relation?

v. The delete rule between the TEAM and COACH
relations is cascade and an attempt is made to
delete the record for one of team 17’s coaches in
the COACH relation?

vi. The delete rule between the TEAM and COACH
relations is set-to-null and an attempt is made to
delete the record for one of team 17’s coaches in
the COACH relation?

C H A P T E R 7

LOGICAL DATABASE
DESIGN

L ogical database design is the process of deciding how to arrange the attributes
of the entities in a given business environment into database structures, such as

the tables of a relational database. The goal of logical database design is to create well
structured tables that properly reflect the company’s business environment. The tables will
be able to store data about the company’s entities in a non-redundant manner and foreign
keys will be placed in the tables so that all the relationships among the entities will be
supported. Physical database design, which will be treated in the next chapter, is the
process of modifying the logical database design to improve performance.

OBJECTIVES

■ Describe the concept of logical database design.
■ Design relational databases by converting entity-relationship diagrams into

relational tables.
■ Describe the data normalization process.
■ Perform the data normalization process.
■ Test tables for irregularities using the data normalization process.
■ Learn basic SQL commands to build data structures.
■ Learn basic SQL commands to manipulate data.

CHAPTER OUTLINE

Introduction
Converting E-R Diagrams into Relational

Tables
Introduction

Converting a Simple Entity

Converting Entities in Binary

Relationships
Converting Entities in Unary

Relationships

Converting Entities in Ternary
Relationships

Designing the General
Hardware Co. Database

Designing the Good Reading
Bookstores Database

Designing the World Music
Association Database

Designing the Lucky Rent-A-Car
Database

158 C h a p t e r 7 Logical Database Design

The Data Normalization Process
Introduction to the Data

Normalization Technique
Steps in the Data Normalization

Process
Example: General Hardware Co.
Example: Good Reading Bookstores
Example: World Music Association

Example: Lucky Rent-A-Car
Testing Tables Converted from E-R

Diagrams
with Data Normalization

Building the Data Structure with SQL
Manipulating the Data with SQL
Summary

INTRODUCTION

Historically, a number of techniques have been used for logical database design. In
the 1970s, when the hierarchical and network approaches to database management
were the only ones available, a technique known as data normalization was
developed. While data normalization has some very useful features, it was difficult
to apply in that environment. Data normalization can also be used to design
relational databases and, actually, is a better fit for relational databases than it
was for the hierarchical and network databases. But, as the relational approach
to database management and the entity-relationship approach to data modeling
both blossomed in the 1980s, a very natural and pleasing approach to logical
database design evolved in which rules were developed to convert E-R diagrams
into relational tables. Optionally, the result of this process can then be tested with the
data normalization technique. Thus, this chapter on the logical design of relational
databases will proceed in three parts: first, the conversion of E-R diagrams into
relational tables, then the data normalization technique, and finally the use of the
data normalization technique to test the tables resulting from the E-R diagram
conversions.

CONVERTING E-R DIAGRAMS INTO RELATIONAL TABLES

Introduction

Converting entity-relationship diagrams to relational tables is surprisingly straight-
forward, with just a few simple rules to follow. Basically, each entity will convert
to a table, plus each many-to-many relationship or associative entity will convert
to a table. The only other issue is that during the conversion, certain rules must be
followed to ensure that foreign keys appear in their proper places in the tables. We
will demonstrate these techniques by methodically converting the E-R diagrams of
Chapter 2 into relational tables.

Converting a Simple Entity

Figure 7.1 repeats the simple entity box in Figure 2.1. Figure 7.2 shows a relational
table that can store the data represented in the entity box. The table simply contains
the attributes that were specified in the entity box. Notice that Salesperson Number
is underlined to indicate that it is the unique identifier of the entity, and the primary
key of the table. Clearly, the more interesting issues and rules come about when, as
almost always happens, entities are involved in relationships with other entities.

Converting E-R Diagrams into Relational Tables 159

C O N C E P T S
I N A C T I O N

7-A ECOLAB

Ecolab is a $3-billion-plus developer
and marketer of cleaning, sanitizing, pest elimination,
and industrial maintenance and repair products and
services that was founded in 1923. Its customers include
restaurants, hotels, hospitals, food and beverage plants,
laundries, schools, and other retail and commercial
facilities. Headquartered in St. Paul, MN, Ecolab is truly
a global company, operating directly in 70 countries and
through distributors, licensees, and export operations in
an additional 100 countries. Its domestic and worldwide
operations are supported by 20,000 employees and
over 50 manufacturing and distribution facilities. A large
percentage of the employees are sales and service
individuals who work in a mobile, remote environment.

One of Ecolab’s applications with a significant
database component is called ‘‘EcoNet.’’ EcoNet gives
the large sales and service work force access to infor-
mation distributed across many databases. EcoNet pro-
vides Ecolab’s North American sales and service people
with a portal into pertinent information needed when

‘‘Photo Courtesy of Ecolab’’ Printed by permission of Ecolab, Inc. (c) 2002 Ecolab
Inc. All rights reserved. Ecolab Inc., 370 Wabasha Street North, St. Paul, Minnesota
55102, U.S.A.

interacting with customers for sales and service purposes.
EcoNet also enables the standardization of processes
across the sales and service organizations within the
seven various North American business units. This is
achieved by having one application get data from
different databases.

The system is also used as a sales planning tool.
Using EcoNet, a salesperson can access such customer
information as past and outstanding invoices, service
reports, and order status. The salesperson can also use
the system to place new orders. Being Web-based, Econet
can be accessed from a home or office PC, from a laptop
at the customer location, and even through handheld
devices. In addition, customers can view their own data
through ‘‘My Ecolab.com.’’

Implemented in 2002, EcoNet uses an interesting
mix of databases.

1. The transactional data, including the last six month’s
orders, is held in a Computer Associates IDMS

160 C h a p t e r 7 Logical Database Design

network-type database. EcoNet accesses this ‘‘up-
to-the-minute’’ information using screen scrapping
technology against the IBM mainframe computer
rather than migrating the data in real time to a
relational DBMS.

2. Completed transaction data is bridged nightly to a
data warehouse holding seven years of sales data in
IBM DB2 Unix.

3. Summarized Sales tables and Key Performance
Indicators are also bridged to Microsoft SQL Server
relational databases.

Ecolab is continually looking for additional informa-
tion to add to the EcoNet application in order to provide
their sales and service people with valuable information
when interacting with customers.

F I G U R E 7.1
The entity box from Figure 2.1

SALESPERSON

PK

Salesperson
Number

Salesperson
Name
Commission
Percentage
Year of Hire

F I G U R E 7.2
Conversion of an E-R diagram entity
box to a relational table

SALESPERSON
Salesperson Salesperson Commission Year
Number Name Percentage of Hire

Converting Entities in Binary Relationships

One-to-One Binary Relationship Figure 7.3 repeats the one-to-one binary relation-
ship of Figure 2.4a. There are three options for designing tables to represent this
data, as shown in Figure 7.4. In Figure 7.4a, the two entities are combined into one
relational table. On the one hand, this is possible because the one-to-one relationship
means that for one salesperson, there can only be one associated office and con-
versely, for one office there can be only one salesperson. So a particular salesperson
and office combination can fit together in one record, as shown in Figure 7.4a. On
the other hand, this design is not a good choice for two reasons. One reason is that
the very fact that salesperson and office were drawn in two different entity boxes
in the E-R diagram of Figure 7.3 means that they are thought of separately in this
business environment and thus should be kept separate in the database. The other
reason is the modality of zero at the salesperson in Figure 7.3. Reading that diagram
from right to left, it says that an office might have no one assigned to it. Thus, in
the table in Figure 7.4a, there could be a few or possibly many record occurrences
that have values for the office number, telephone, and size attributes but have the
four attributes pertaining to salespersons empty or null! This could result in a lot of
wasted storage space, but it is worse than that. If Salesperson Number is declared

Converting E-R Diagrams into Relational Tables 161

F I G U R E 7.3
The one-to-one (1-1) binary
relationship from Figure 2.4a

OFFICE

PK Office
Number

Telephone
Size

SALESPERSON

PK Salesperson
Number

Salesperson
Name
Commission
Percentage
Year of Hire

Works in

Occupied by

to be the primary key of the table, this scenario would mean that there would be
records with no primary key values, a situation which is clearly not allowed.

Figure 7.4b is a better choice. There are separate tables for the salesperson
and office entities. In order to record the relationship, i.e. which salesperson is
assigned to which office, the Office Number attribute is placed as a foreign key in
the SALESPERSON table. This connects the salespersons with the offices to which

F I G U R E 7.4
Conversion of an E-R diagram with two
entities in a one-to-one binary relationship
into one or two relational tables

a. One-to-one binary relationship converted to a single relational table.

b. One-to-one binary relationship converted to two relational tables, with the for-
eign key in the SALESPERSON table.

c. One-to-one binary relationship converted to two relational tables, with the for-
eign key in the OFFICE table.

OFFICE

Office Salesperson
Number Telephone Number Size

SALESPERSON

Salesperson Salesperson Commission Year of
Number Name Percentage Hire

OFFICE

Office
Number Telephone Size

SALESPERSON

Salesperson Salesperson Commission Year of Office
Number Name Percentage Hire Number

SALESPERSON/OFFICE

Salesperson Salesperson Commission Year of Office
Number Name Percentage Hire Number Telephone Size

162 C h a p t e r 7 Logical Database Design

they are assigned. Again, look at the modalities in the E-R diagram in Figure 7.3.
Reading from left to right, each salesperson is assigned to exactly one office
(indicated by the two ‘‘ones’’ adjacent to the office entity). That translates directly
into each record in the SALESPERSON table of Figure 7.4b having a value (and a
single value, at that) for its Office Number foreign key attribute. That’s good! But
what about the problem of unassigned offices mentioned in the previous paragraph?
In Figure 7.4b, unassigned offices will each have a record in the OFFICE table, with
Office Number as the primary key, which is fine. Their office numbers will simply
not appear as foreign key values in the SALESPERSON table.

Finally, instead of placing Office Number as a foreign key in the
SALESPERSON table, could you instead place Salesperson Number as a foreign key
in the OFFICE table, Figure 7.4c? Recall that, reading the E-R diagram of Figure 7.3
from right to left, the modality of zero adjacent to the salesperson entity says that
an office might be empty, i.e. it might not be assigned to any salesperson. But then,
some or perhaps many records of the OFFICE table of Figure 7.4c would have no
value or a null in their Salesperson Number foreign key attribute positions. Why
bother having to deal with this situation when the design in Figure 7.4b avoids it?

Certainly, it follows that if the modalities were reversed, meaning that the zero
modality was adjacent to the office entity box and the one modality was adjacent to
the salesperson entity box, then the design in Figure 7.4c would be the preferable
one. This would mean that every office must have a salesperson assigned to it but a
salesperson may or may not be assigned to an office. Perhaps lots of the salespersons
travel most of the time and don’t need offices. By the way, while we’re in ‘‘what if’’
mode, what if the modality was zero on both sides? Then there would be a judgment
call to make between the designs of Figure 7.4b and Figure 7.4c. If the goal is to
minimize the number of null values in the foreign key, then you have to decide
whether it is more likely that a salesperson is not assigned to an office (Figure 7.4c
is preferable) or that an office is empty (Figure 7.4b is preferable).

One-to-Many Binary Relationship Figure 7.5 (copied from Figure 2.4b) shows an
E-R diagram for a one-to-many binary relationship. Figure 7.6 shows the conversion
of this E-R diagram into two relational tables. This is, perhaps, the simplest case
of all. The rule is that the unique identifier of the entity on the ‘‘one side’’ of the
one-to-many relationship is placed as a foreign key in the table representing the
entity on the ‘‘many side.’’ In this case, the Salesperson Number attribute is placed
in the CUSTOMER table as a foreign key. Each salesperson has one record in
the SALESPERSON table, as does each customer in the CUSTOMER table. The
Salesperson Number attribute in the CUSTOMER table links the two and, since

F I G U R E 7.5
The one-to-many (1-M) binary
relationship from Figure 2.4b

CUSTOMER

PK

Customer
Number

Customer
Name
HQ City

SALESPERSON
PK Salesperson
Number
Salesperson
Name
Commission
Percentage
Year of Hire

Sells to

Buys from

Converting E-R Diagrams into Relational Tables 163

F I G U R E 7.6
Conversion of an E-R diagram with two
entities in a one-to-many binary
relationship into two relational tables

CUSTOMER

Customer Customer Salesperson
Number Name HQ City Number

SALESPERSON
Salesperson Salesperson Commission Year
Number Name Percentage of Hire

the E-R diagram tells us that every customer must have a salesperson, there are no
empty attributes in the CUSTOMER table records.

Many-to-Many Binary Relationship Figure 7.7 shows the E-R diagram with the
many-to-many binary relationship from Figure 2.5. The equivalent diagram from
Figure 2.6, using an associative entity, is shown in Figure 7.8. An E-R diagram with
two entities in a many-to-many relationship converts to three relational tables, as
shown in Figure 7.9. Each of the two entities converts to a table with its own attributes

F I G U R E 7.7
The many-to-many binary relationship
from Figure 2.5

Sells

Sold by

PRODUCT

PK

Product
Number

Product
Name
Unit Price

SALESPERSON
PK Salesperson
Number
Salesperson
Name
Commission
Percentage
Year of Hire
Quantity

Sold

Sold by
Sold

Sold
Product

PRODUCT

PK Product
Number

Product
Name
Unit Price

SALESSALESPERSON

PK Salesperson
Number
Salesperson
Name
Commission
Percentage
Year of Hire

PK

Quantity
PK Product
Number
Salesperson
Number

F I G U R E 7.8
The associative entity from Figure 2.6

164 C h a p t e r 7 Logical Database Design

F I G U R E 7.9
Conversion of an E-R diagram in Figure
7.7 (and Figure 7.8) with two entities in
a many-to-many binary relationship into
three relational tables

SALE

Salesperson Product
Number Number Quantity
PRODUCT

Product Product
Number Name Unit Price

SALESPERSON
Salesperson Salesperson Commission Year
Number Name Percentage of Hire

but with no foreign keys (regarding this relationship). The SALESPERSON table
and the PRODUCT table in Figure 7.9 each contain only the attributes shown in the
salesperson and product entity boxes of Figure 7.7 and Figure 7.8.

In addition, there must be a third ‘‘many-to-many’’ table for the many-to-many
relationship, the reasons for which were explained in Chapter 5. The primary key of
this additional table is the combination of the unique identifiers of the two entities in
the many-to-many relationship. Additional attributes consist of the intersection data,
Quantity in this example. Also as explained in Chapter 5, there are circumstances
in which additional attributes, such as date and timestamp attributes, must be added
to the primary key of the many-to-many table to achieve uniqueness.

Converting Entities in Unary Relationships

One-to-One Unary Relationship Figure 7.10 repeats the E-R diagram with a one-
to-one unary relationship from Figure 2.7a. In this case, with only one entity type
involved and with a one-to-one relationship, the conversion requires only one table,
as shown in Figure 7.11. For a particular salesperson, the Backup Number attribute
represents the salesperson number of his backup person, i.e. the person who handles
his accounts when he is away for any reason.

F I G U R E 7.10
The one-to-one (1-1) unary relationship
from Figure 2.7a

SALESPERSON
PK Salesperson
Number
Salesperson
Name
Commission
Percentage
Year of Hire

Backs-up

Backed-up by

Converting E-R Diagrams into Relational Tables 165

F I G U R E 7.11
Conversion of the E-R diagram in Figure
7.10 with a one-to-one unary relationship
into a relational table

SALESPERSON

Salesperson Salesperson Commission Year Backup
Number Name Percentage of Hire Number

F I G U R E 7.12
The one-to-many (1-M) unary relationship
from Figure 2.7b

SALESPERSON
PK Salesperson
Number
Salesperson
Name
Commission
Percentage
Year of Hire

Manages

Reports to

One-to-Many Unary Relationship The one-to-many unary relationship situation is
very similar to the one-to-one unary case. Figure 7.12 repeats the E-R diagram
from Figure 2.7b. Figure 7.13 shows the conversion of this diagram into a relational
database. Some employees manage other employees. An employee’s manager
is recorded in the Manager Number attribute in the table in Figure 7.13. The
manager numbers are actually salesperson numbers since some salespersons are
sales managers who manage other salespersons. This arrangement works because
each employee has only one manager. For any particular SALESPERSON record,
there can only be one value for the Manager Number attribute. However, if you
scan down the Manager Number column, you will see that a particular value may
appear several times because a person can manage several other salespersons.

Many-to-Many Unary Relationship Figure 7.14 shows the E-R diagram for the
many-to-many unary relationship of Figure 2.7c. As Figure 7.15 indicates, this
relationship requires two tables in the conversion. The PRODUCT table has no
foreign keys. The COMPONENT table indicates which items go into making up
which other items, as was described in the bill-of-materials discussion in Chapter 6.
This table also contains any intersection data that may exist in the many-to-many
relationship. In this example, the Quantity attribute indicates how many of a
particular item go into making up another item.

The fact that we wind up with two tables in this conversion is really not
surprising. The general rule is that in the conversion of a many-to-many relationship
of any degree (unary, binary, or ternary), the number of tables will be equal to the
number of entity types (one, two, or three, respectively) plus one more table for
the many-to-many relationship. Thus, the conversion of the many-to-many unary
relationship required two tables, the many-to-many binary relationship three tables,
and, as will be shown next, the many-to-many ternary relationship four tables.

F I G U R E 7.13
Conversion of the E-R diagram in Figure
7.12 with a one-to-many unary
relationship into a relational table

SALESPERSON

Salesperson Salesperson Commission Year
Number Name Percentage of Hire Manager

166 C h a p t e r 7 Logical Database Design

F I G U R E 7.14
The many-to-many unary relationship
from Figure 2.7c

COMPONENT

PK
Quantity

PK Subassembly
Number

Product
Number
PRODUCT
PK Product
Number
Product
Name
Unit Price

Part of
Includes

Part of
Includes

F I G U R E 7.15
Conversion of the E-R diagram in Figure
7.14 with a many-to-many unary
relationship into two relational tables

COMPONENT

Product Subassembly
Number Number Quantity

PRODUCT
Product Product
Number Name Unit Price

Converting Entities in Ternary Relationships

Finally, Figure 7.16 repeats the E-R diagram with the ternary relationship from
Figure 2.8. Figure 7.17 shows the four tables necessary for the conversion to
relational tables. Notice that the primary key of the SALE table, which is the
table added for the many-to-many relationship, is the combination of the unique
identifiers of the three entities involved, plus the Date attribute. In this case, with
the premise being that a particular salesperson can have sold a particular product to
a particular customer on different days, the Date attribute is needed in the primary
key to achieve uniqueness.

Designing the General Hardware Co. Database

Having explored the specific E-R diagram-to-relational database conversion rules,
let’s look at a few examples, beginning with the General Hardware Co. Figure 7.18
is the General Hardware E-R diagram. It is convenient to begin the database
design process with an important, central E-R diagram entity, such as salesperson,
that has relationships with several other entities. Thus, the relational database in

Converting E-R Diagrams into Relational Tables 167

CUSTOMER

PK Customer
Number

Customer
Name
HQ City
SALE
PK Salesperson
Number
PK Product
Number
PK Customer
Number

Date
Quantity

SALESPERSON
PK Salesperson
Number
Salesperson
Name
Commission
Percentage
Year of Hire
PRODUCT
PK Product
Number
Product
Name
Unit Price

Purchased
Sold to

Sold
Sold
Product
Sold
Sold by

F I G U R E 7.16
The ternary relationship from Figure 2.8

F I G U R E 7.17
Conversion of the E-R diagram in Figure
7.16 with three entities in a ternary
relationship into four relational tables

SALE
Salesperson Customer Product
Number Number Number Date Quantity
PRODUCT
Product Product
Number Name Unit Price
CUSTOMER
Customer Customer
Number Name HQ City
SALESPERSON
Salesperson Salesperson Commission Year
Number Name Percentage of Hire

Figure 7.19 includes a SALESPERSON table with the four salesperson attributes
shown in Figure 7.18’s salesperson entity box (plus the Office Number attribute, to

168 C h a p t e r 7 Logical Database Design

F I G U R E 7.18
The General Hardware
Company E-R diagram

PK Employee
Number

Customer
Number
PK

CUSTOMER
EMPLOYEE

Employee
Name
Title

Office
Number

OFFICE
PK
Telephone
Size
Salesperson
Number
SALESPERSON
PK
Salesperson
Name
Commission
Percentage
Year of Hire
Customer
Number
CUSTOMER
PK
Customer
Name
HQ City
Product
Number
PRODUCT
PK
Product
Name
Unit Price
PK Product
Number
Salesperson
Number
SALES
PK
Quantity

Occupied by
Works in

Sells to

Buys from

Sold
Sold by

Sold
Sold
Product

Employs

Employed by

Converting E-R Diagrams into Relational Tables 169

F I G U R E 7.19
The General Hardware Company
relational database

OFFICE
Office
Number Telephone Size
SALES
Salesperson Product
Number Number Quantity
PRODUCT
Product Product
Number Name Unit Price
CUSTOMER EMPLOYEE
Customer Employee Employee
Number Number Name Title
CUSTOMER
Customer Customer Salesperson
Number Name Number HQ City
SALESPERSON
Salesperson Salesperson Commission Year Office
Number Name Percentage of Hire Number

which we will return shortly). To the right of the salesperson entity box in the E-R
diagram, there is a one-to-many relationship (‘‘Sells To’’) between salespersons and
customers. The database then includes a CUSTOMER table with the Salesperson
Number attribute as a foreign key, because salesperson is on the ‘‘one side’’ of the
one-to-many relationship and customer is on the ‘‘many side’’ of the one-to-many
relationship.

Customer employee is a dependent entity of customer and there is a one-to-
many relationship between them. Because of this relationship, the CUSTOMER
EMPLOYEE table in the database includes the Customer Number attribute as a
foreign key. Furthermore, the Customer Number attribute is part of the primary key
of the CUSTOMER EMPLOYEE table because customer employee is a dependent
entity and we’re told that employee numbers are unique only within a customer.

The PRODUCT table contains the three attributes of the product entity.
The many-to-many relationship between the salesperson and product entities is
represented by the SALES table in the database. Notice that the combination
of the unique identifiers (Salesperson Number and Product Number) of the two
entities in the many-to-many relationship is the primary key of the SALES table.
Finally, the office entity has its table in the database with its three attributes, which
brings us to the presence of the Office Number attribute as a foreign key in the

170 C h a p t e r 7 Logical Database Design

SALESPERSON table. This is needed to maintain the one-to-one binary relationship
between salesperson and office. A fair question is, since the relationship is ‘‘one’’
on both sides, why did we decide to put the foreign key in the SALESPERSON
table rather than in the OFFICE table? The answer lies in the fact that the modality
adjacent to SALESPERSON is zero while the modality adjacent to OFFICE is one.
An office may or may not have a salesperson assigned to it but a salesperson must
be assigned to an office. The result is that every salesperson must have an associated
office number; the Office Number attribute in the SALESPERSON table can’t be
null. If we reversed it and put the Salesperson Number attribute in the OFFICE
table, many of the Salesperson Number attribute values could be null since the zero
modality going from office to salesperson tells us that an office can be empty.

One last thought: Why did the PRODUCT table end-up without any foreign
keys? Because it is not the ‘‘target’’ (it is not on the ‘‘many side’’) of any one-to-
many binary relationship. It is also not involved in a one-to-one binary relationship
that would require the presence of a foreign key. Finally, it is not involved in a
unary relationship that would require repeating the primary key in the table.

Designing the Good Reading Bookstores Database

The Good Reading Bookstores’ E-R diagram is repeated in Figure 7.20. Beginning
with the central book entity and looking to its left, we see that there is a one-to-many

Publisher
Name

PUBLISHER

PK

City
Country
President
Year Founded

Customer
Number

Author
Number

Book
Number

BOOK
PK

Book Name
Publication
Year
Pages

PK Author
Number

Book
Number

WROTE

PK
PK Customer
Number
Book
Number
CUSTOMER
PK

Customer
Name
Street
City
State
Country

AUTHOR
PK

Author Name
Year Born
Year Died

SALE
PK

Date
Price
Quantity

Published

Published by

Wrote

Written by

Wrote
Written by

Bought

Bought by

Sold
In sale

F I G U R E 7.20
Good Reading Bookstores entity-relationship diagram

Converting E-R Diagrams into Relational Tables 171

F I G U R E 7.21
The Good Reading Bookstores
relational database

SALE
Book Customer
Number Number Date Price Quantity

WRITING

Book Author
Number Number
CUSTOMER
Customer Customer
Number Name Street City State Country
BOOK
Book Book Publication Publisher
Number Name Year Pages Name
AUTHOR
Author Author Year Year
Number Name Born Died
PUBLISHER
Publisher Year
Name City Country Telephone Founded

relationship between books and publishers. A publisher publishes many books but
a book is published by just one publisher. The Good Reading Bookstores relational
database of Figure 7.21 shows the BOOK and PUBLISHER tables. Publisher Name
is a foreign key in the BOOK table because publisher is on the ‘‘one side’’ of the one-
to-many relationship and book is on the ‘‘many side.’’ Next is the AUTHOR table,
which is straightforward. The many-to-many binary relationship between books and
authors is reflected in the WRITING table, which has no intersection data. Finally,
there is the customer entity and the many-to-many relationship between books and
customers. Correspondingly, the relational database includes a CUSTOMER table
and a SALE table to handle the many-to-many relationship. Notice the Date, Price,
and Quantity attributes appearing in the SALE table as intersection. Also notice that
since a customer can buy the same book on more than one day, the Date attribute
must be part of the primary key to achieve uniqueness.

Designing the World Music Association Database

Looking at the World Music Association E-R diagram in Figure 7.22, it appears that
the orchestra entity would be a good central starting point for the database design

172 C h a p t e r 7 Logical Database Design

Orchestra
Name

ORCHESTRA
PK

City
Country
Music
Director

Orchestra
Name

Composer
Name

Musician
Number

MUSICIAN
PK

Musician
Name
Instrument
Annual
Salary

PK Degree

Musician
Number
DEGREE
PK

University
Year

PK Composer
Name

Composition
Name

RECORDING
PK
Composition
Name
PK
Composer
Name
PK

Year
Price

COMPOSER

PK

Country
Date of Birth

COMPOSITION

PK
Year
Employs

Employed by Earned by

Earned

Recorded

Contains

Wrote
Written by

Recorded
Recorded by

F I G U R E 7.22
World Music Association entity-relationship diagram

process. Thus, the relational database in Figure 7.23 begins with the ORCHESTRA
table. The Orchestra Name foreign key in the MUSICIAN table reflects the one-to-
many relationship from orchestra to musician. Since degree is a dependent entity
of musician in a one-to-many relationship and degrees (e.g. B.A.) are unique only
within a musician, not only does Musician Number appear as a foreign key in the
DEGREE table but also it must be part of that table’s primary key. A similar situation
exists between the composer and composition entities, as shown in the COMPOSER
and COMPOSITION tables in the database. Finally, the many-to-many relationship
between orchestra and composition is converted into the RECORDING table.

Y O U R
T U R N

7.1 THE E-R DIAGRAM CONVERSION LOGICAL DESIGN TECHNIQUE

In Your Turn in Chapter 2, you
created an entity-relationship diagram for your university
environment.

QUESTION:
Using the logical design techniques just described, convert

your university E-R diagram into a logical database
design.

Converting E-R Diagrams into Relational Tables 173

F I G U R E 7.23
The World Music Association
relational database

RECORDING
Orchestra Composition
Name Name Year Price
COMPOSITION

Composition Composer
Name Name

Composer
Name
Year
COMPOSER

Composer Date of
Name Country Birth

DEGREE
Musician
Number Degree University Year
MUSICIAN
Musician Musician Annual Orchestra
Number Name Instrument Salary Name
ORCHESTRA
Orchestra Music
Name City Country Director

Notice that the primary key of the RECORDING table begins with the Orchestra
Name attribute and then continues with both the Composition Name and Composer
Name attributes. This is because the primary key of one of the two entities in the
many-to-many relationship, composition, is the combination of those two latter
attributes.

Designing the Lucky Rent-A-Car Database

Figure 7.24 shows the Lucky Rent-A-Car E-R diagram. The conversion to a
relational database structure begins with the car entity and its four attributes, as
shown in the CAR table of the database in Figure 7.25. Because car is on the ‘‘many
side’’ of a one-to-many relationship with the manufacturer entity, the CAR table
also has the Manufacturer Name attribute as a foreign key. The straightforward one-
to-many relationship from car to maintenance event produces a MAINTENANCE
EVENT table with Car Serial Number as a foreign key. The customer entity converts
to the CUSTOMER table with its four attributes. The many-to-many relationship
between car and customer converts to the RENTAL table. Car Serial Number, the
unique identifier of the car entity, and Customer Number, the unique identifier
of the customer entity, plus the Rental Date intersection data attribute form the
three-attribute primary key of the RENTAL table, with Return Date and Total
Cost as additional intersection data attributes. Rental Date has to be part of the

174 C h a p t e r 7 Logical Database Design

F I G U R E 7.24
Lucky Rent-A-Car entity-
relationship diagram

PK Customer
Number

Car Serial
Number

RENTAL

PK

Rental Date
Return Date
Total Cost

Manufacturer
Name

MANUFACTURER
PK

Manufacturer
Country
Sales Rep
Name
Sales Rep
Number

Car Serial
Number
CAR
PK

Model
Year
Class

Customer
Number
CUSTOMER
PK

Customer
Name
Customer
Address
Customer
Credit Rating

MAINTENANCE
EVENT

Manufactured
Manufactured by

Rented

Car rented

Repaired
Car Repaired

Rented
Rented by

Repair
Number

PK

Date
Procedure
Mileage
Repair Time

primary key to achieve uniqueness because a particular customer may have rented
a particular car on several different dates.

THE DATA NORMALIZATION PROCESS

Data normalization was the earliest formalized database design technique and
at one time was the starting point for logical database design. Today, with the
popularity of the Entity-Relationship model and other such diagramming tools and
the ability to convert its diagrams to database structures, data normalization is used
more as a check on database structures produced from E-R diagrams than as a

The Data Normalization Process 175

F I G U R E 7.25
The Lucky Rent-A-Car relational database

RENTAL
Car Serial Customer Rental Return Total
Number Number Date Date Cost
CUSTOMER
Customer Customer Customer Customer
Number Name Address Telephone
MAINTENANCE
Repair Car Serial Repair
Number Number Date Procedure Mileage Time
CAR
Car Serial Manufacturer
Number Model Year Class Name
MANUFACTURER
Manufacturer Manufacturer Sales Rep Sales Rep
Name Country Name Telephone

full-scale database design technique. That’s one of the reasons for learning about
data normalization. Another reason is that the data normalization process is another
way of demonstrating and learning about such important topics as data redundancy,
foreign keys, and other ideas that are so central to a solid understanding of database
management.

Data normalization is a methodology for organizing attributes into tables so
that redundancy among the non-key attributes is eliminated. Each of the resultant
tables deals with a single data focus, which is just another way of saying that
each resultant table will describe a single entity type or a single many-to-many
relationship. Furthermore, foreign keys will appear exactly where they are needed.
In other words, the output of the data normalization process is a properly structured
relational database.

Introduction to the Data Normalization Technique

The input required by the data normalization process has two parts. One is a list of all
the attributes that must be incorporated into the database: that is, all of the attributes
in all of the entities involved in the business environment under discussion plus all
of the intersection data attributes in all of the many-to-many relationships between
these entities. The other input, informally, is a list of all of the defining associations
among the attributes. Formally, these defining associations are known as functional
dependencies. And what are defining associations or functional dependencies? They
are a means of expressing that the value of one particular attribute is associated with

176 C h a p t e r 7 Logical Database Design

a specific single value of another attribute. If we know that one of these attributes
has a particular value, then the other attribute must have some other value. For
example, for a particular Salesperson Number, 137, there is exactly one Salesperson
Name, Baker, associated with it. Why is this true? In this example, a Salesperson
Number uniquely identifies a salesperson and, after all, a person can have only one
name! And this is true for every person! Informally, we might say that Salesperson
Number defines Salesperson Name. If I give you a Salesperson Number, you
can give me back the one and only name that goes with it. (It’s a little like the
concept of independent and dependent variables in mathematics. Take a value of the
independent variable, plug it into the formula and you get back the specific value of
the dependent variable associated with that independent variable.) These defining
associations are commonly written with a right-pointing arrow like this:

Salesperson Number Salesperson Name

In the more formal terms of functional dependencies, Salesperson Number, in
general the attribute on the left side, is referred to as the determinant. Why? Because
its value determines the value of the attribute on the right side. Conversely, we also
say that the attribute on the right is functionally dependent on the attribute on the left.

Data normalization is best explained with an example and this is a good place
to start one. In order to demonstrate the main points of the data normalization
process, we will modify part of the General Hardware Co. business environment
and focus on the salesperson and product entities. Let’s assume that salespersons are
organized into departments and each department has a manager who is not herself
a salesperson. Then the list of attributes we will consider is shown in Figure 7.26.
The list of defining associations or functional dependencies is shown in Figure 7.27.

Notice a couple of fine points about the list of defining associations in
Figure 7.27. The last association:

Salesperson Number, Product Number Quantity

shows that the combination of two or more attributes may possibly define another
attribute. That is, the combination of a particular Salesperson Number and a
particular Product Number defines or specifies a particular Quantity. Put another
way, in this business context, we know how many units of a particular product
a particular salesperson has sold. Another point, which will be important in

F I G U R E 7.26
List of attributes for salespersons
and products

Salesperson Number
Salesperson Name
Commission

Percentage
Year of Hire
Department

Number
Manager Name
Product Number
Product Name
Unit Price
Quantity

The Data Normalization Process 177

F I G U R E 7.27
List of defining associations (functional
dependencies) for the attributes of
salespersons and products

Salesperson Number Salesperson Name
Salesperson Number Commission Percentage
Salesperson Number Year of Hire

Salesperson Number Department Number

Salesperson Number Manager Name

Product Number Product Name
Product Number Unit Price

Department Number Manager Name

Salesperson Number, Product Number Quantity

demonstrating one step of the data normalization process, is that Manager Name
is defined, independently, by two different attributes: Salesperson Number and
Department Number:

Salesperson Number Manager Name
Department Number Manager Name

Both these defining associations are true! If I identify a salesperson by his
Salesperson Number, you can tell me who his manager is. Also, if I state a
department number, you can tell me who the manager of the department is. How
did we wind up with two different ways to define the same attribute? Very easily!
It simply means that during the systems analysis process, both these equally true
defining associations were discovered and noted. By the way, the fact that I know
the department that a salesperson works in:

Salesperson Number Department Number

(and that each of these two attributes independently define Manager Name) will
also be an issue in the data normalization process. More about this later.

Steps in the Data Normalization Process

The data normalization process is known as a ‘‘decomposition process.’’ Basically,
we are going to line up all the attributes that will be included in the relational
database and start subdividing them into groups that will eventually form the
database’s tables. Thus, we are going to ‘‘decompose’’ the original list of all of
the attributes into subgroups. To do this, we are going to step through a number
of normal forms. First, we will demonstrate what unnormalized data looks like.
After all, if data can exist in several different normal forms, then there should be
the possibility that data is in none of the normal forms, too! Then we will basically
work through the three main normal forms in order:

First Normal Form
Second Normal Form
Third Normal Form

There arc certain ‘‘exception conditions’’ that have also been described as normal
forms. These include the Boyce-Codd Normal Form, Fourth Normal Form, and
Fifth Normal Form. They are less common in practice and will not be covered here.

178 C h a p t e r 7 Logical Database Design

Here are three additional points to remember:

1. Once the attributes are arranged in third normal form (and if none of the
exception conditions are present), the group of tables that they comprise is, in
fact, a well-structured relational database with no data redundancy.

2. A group of tables is said to be in a particular normal form if every table in the
group is in that normal form.

3. The data normalization process is progressive. If a group of tables is in second
normal form it is also in first normal form. If they are in third normal form they
are also in second normal form.

Unnormalized Data Figure 7.28 shows the salesperson and product-related attributes
listed in Figure 7.26 arranged in a table with sample data. The salesperson and
product data is taken from the General Hardware Co. relational database of
Figure 5.14, with the addition of Department Number and Manager Name data.
Note that salespersons 137, 204, and 361 are all in department number 73 and their
manager is Scott. Salesperson 186 is in department number 59 and his manager is
Lopez.

The table in Figure 7.28 is unnormalized. The table has four records, one for
each salesperson. But, since each salesperson has sold several products and there is
only one record for each salesperson, several attributes of each record must have
multiple values. For example, the record for salesperson 137 has three product
numbers, 19440, 24013, and 26722, in its Product Number attribute, because
salesperson 137 has sold all three of those products. Having such multivalued
attributes is not permitted in first normal form, and so this table is unnormalized.

First Normal Form The table in Figure 7.29 is the first normal form representation
of the data. The attributes under consideration have been listed out in one table and

SALESPERSON/PRODUCT table

Salesperson Product Salesperson Commission Year of Department Manager Product Unit
Number Number Name Percentage Hire Number Name Name Price Quantity

137 19440 Baker 10 1995 73 Scott Hammer 17.50 473

24013 Saw 26.25 170
26722 Pliers 11.50 688

186 16386 Adams 15 2001 59 Lopez Wrench 12.95 1745
19440 Hammer 17.50 2529
21765 Drill 32.99 1962
24013 Saw 26.25 3071

204 21765 Dickens 10 1998 73 Scott Drill 32.99 809
26722 Pliers 11.50 734

361 16386 Carlyle 20 2001 73 Scott Wrench 12.95 3729
21765 Drill 32.99 3110
26722 Pliers 11.50 2738

F I G U R E 7.28
The salesperson and product attributes, unnormalized with sample data

The Data Normalization Process 179

SALESPERSON/PRODUCT table
Salesperson Product Salesperson Commission Year of Department Manager Product Unit
Number Number Name Percentage Hire Number Name Name Price Quantity

F I G U R E 7.29
The salesperson and product attributes in first normal form

a primary key has been established. As the sample data of Figure 7.30 shows, the
number of records has been increased (over the unnormalized representation) so
that every attribute of every record has just one value. The multivalued attributes
of Figure 7.28 have been eliminated. Indeed, the definition of first normal form is a
table in which every attribute value is atomic, that is, no attribute is multivalued.

The combination of the Salesperson Number and Product Number attributes
constitutes the primary key of this table. What makes this combination of attributes a
legitimate primary key? First of all, the business context tells us that the combination
of the two provides unique identifiers for the records of the table and that there is no
single attribute that will do the job. That, of course, is how we have been approaching
primary keys all along. Secondly, in terms of data normalization, according to the list
of defining associations or functional dependencies of Figure 7.27, every attribute
in the table is either part of the primary key or is defined by one or both attributes
of the primary key. Salesperson Name, Commission Percentage, Year of Hire,
Department Number, and Manager Name are each defined by Salesperson Number.
Product Name and Unit Price are each defined by Product Number. Quantity is
defined by the combination of Salesperson Number and Product Number.

Are these two different ways of approaching the primary key selection
equivalent? Yes! If the combination of a particular Salesperson Number and a
particular Product Number is unique, then it identifies exactly one record of the
table. And, if it identifies exactly one record of the table, then that record shows
the single value of each of the non-key attributes that is associated with the unique
combination of the key attributes.

SALESPERSON/PRODUCT table
Salesperson Product Salesperson Commission Year of Department Manager Product Unit
Number Number Name Percentage Hire Number Name Name Price Quantity

137 19440 Baker 10 1995 73 Scott Hammer 17.50 473
137 24013 Baker 10 1995 73 Scott Saw 26.25 170
137 26722 Baker 10 1995 73 Scott Pliers 11.50 688
186 16386 Adams 15 2001 59 Lopez Wrench 12.95 1475
186 19440 Adams 15 2001 59 Lopez Hammer 17.50 2529
186 21765 Adams 15 2001 59 Lopez Drill 32.99 1962
186 24013 Adams 15 2001 59 Lopez Saw 26.25 3071
204 21765 Dickens 10 1998 73 Scott Drill 32.99 809
204 26722 Dickens 10 1998 73 Scott Pliers 11.50 734
361 16386 Carlyle 20 2001 73 Scott Wrench 12.95 3729
361 21765 Carlyle 20 2001 73 Scott Drill 32.99 3110
361 26722 Carlyle 20 2001 73 Scott Pliers 11.50 2738

F I G U R E 7.30
The salesperson and product attributes in first normal form with sample data

180 C h a p t e r 7 Logical Database Design

But that is the same thing as saying that each of the non-key attributes is
defined by or is functionally dependent on the primary key! For example, consider
the first record of the table in Figure 7.30.

Sales-person Product Sales-person Commission Year of Department Manager Product Unit
Number Number Name Percentage Hire Number Name Name Price Quantity

137 19440 Baker 10 1995 73 Scott Hammer 17.50 473

The combination of Salesperson Number 137 and Product Number 19440 is
unique. There is only one record in the table that can have that combination of
Salesperson Number and Product Number values. Therefore, if someone specifies
those values, the only Salesperson Name that can be associated with them is Baker,
the only Commission Percentage is 10, and so forth. But that has the same effect
as the concept of functional dependency. Since Salesperson Name is functionally
dependent on Salesperson Number, given a particular Salesperson Number, say
137, there can be only one Salesperson Name associated with it, Baker. Since
Commission Percentage is functionally dependent on Salesperson Number, given
a particular Salesperson Number, say 137, there can be only one Commission
Percentage associated with it, 10. And so forth.

First normal form is merely a starting point in the normalization process. As
can immediately be seen from Figure 7.30, there is a great deal of data redundancy
in first normal form. There are three records involving salesperson 137 (the first
three records) and so there are three places in which his name is listed as Baker, his
commission percentage is listed as 10, and so on. Similarly, there are two records
involving product 19440 (the first and fifth records) and this product’s name is listed
twice as Hammer and its unit price is listed twice as 17.50. Intuitively, the reason
for this is that attributes of two different kinds of entities, salespersons and products,
have been mixed together in one table.

Second Normal Form Since data normalization is a decomposition process, the
next step will be to decompose the table of Figure 7.29 into smaller tables to
eliminate some of its data redundancy. And, since we have established that at least
some of the redundancy is due to mixing together attributes about salespersons
and attributes about products, it seems reasonable to want to separate them out at
this stage. Informally, what we are going to do is to look at each of the non-key
attributes of the table in Figure 7.29 and, on the basis of the defining associations
of Figure 7.27, decide which attributes of the key are really needed to define it. For
example, Salesperson Name really only needs Salesperson Number to define it; it
does not need Product Number. Product Name needs only Product Number to define
it; it does not need Salesperson Number. Quantity indeed needs both attributes,
according to the last defining association of Figure 7.27.

More formally, second normal form, which is what we are heading for, does
not allow partial functional dependencies. That is, in a table in second normal form,
every non-key attribute must be fully functionally dependent on the entire key of
that table. In plain language, a non-key attribute cannot depend on only part of
the key, in the way that Salesperson Name, Product Name, and most of the other
non-key attributes of Figure 7.29 do.

Figure 7.31 shows the salesperson and product attributes arranged in second
normal form. There is a SALESPERSON Table in which Salesperson Number is

What Will You Get?

We provide professional writing services to help you score straight A’s by submitting custom written assignments that mirror your guidelines.

Premium Quality

Get result-oriented writing and never worry about grades anymore. We follow the highest quality standards to make sure that you get perfect assignments.

Experienced Writers

Our writers have experience in dealing with papers of every educational level. You can surely rely on the expertise of our qualified professionals.

On-Time Delivery

Your deadline is our threshold for success and we take it very seriously. We make sure you receive your papers before your predefined time.

24/7 Customer Support

Someone from our customer support team is always here to respond to your questions. So, hit us up if you have got any ambiguity or concern.

Complete Confidentiality

Sit back and relax while we help you out with writing your papers. We have an ultimate policy for keeping your personal and order-related details a secret.

Authentic Sources

We assure you that your document will be thoroughly checked for plagiarism and grammatical errors as we use highly authentic and licit sources.

Moneyback Guarantee

Still reluctant about placing an order? Our 100% Moneyback Guarantee backs you up on rare occasions where you aren’t satisfied with the writing.

Order Tracking

You don’t have to wait for an update for hours; you can track the progress of your order any time you want. We share the status after each step.

Order Now Talk to Us

Trusted Partner of 9650+ Students for Writing

From brainstorming your paper's outline to perfecting its grammar, we perform every step carefully to make your paper worthy of A grade.

Preferred Writer

Hire your preferred writer anytime. Simply specify if you want your preferred expert to write your paper and we’ll make that happen.

Grammar Check Report

Get an elaborate and authentic grammar check report with your work to have the grammar goodness sealed in your document.

One Page Summary

You can purchase this feature if you want our writers to sum up your paper in the form of a concise and well-articulated summary.

Plagiarism Report

You don’t have to worry about plagiarism anymore. Get a plagiarism report to certify the uniqueness of your work.

Free Features $66FREE

• Most Qualified Writer $10FREE
• Plagiarism Scan Report $10FREE
• Unlimited Revisions $08FREE
• Paper Formatting $05FREE
• Cover Page $05FREE
• Referencing & Bibliography $10FREE
• Dedicated User Area $08FREE
• 24/7 Order Tracking $05FREE
• Periodic Email Alerts $05FREE

Our Services

Join us for the best experience while seeking writing assistance in your college life. A good grade is all you need to boost up your academic excellence and we are all about it.

• On-time Delivery
• 24/7 Order Tracking
• Access to Authentic Sources

Academic Writing

We create perfect papers according to the guidelines.

Professional Editing

We seamlessly edit out errors from your papers.

Thorough Proofreading

We thoroughly read your final draft to identify errors.

Thorough Proofreading

We thoroughly read your final draft to identify errors.

It May Not Be Much, but It’s Honest Work!

Here is what we have achieved so far. These numbers are evidence that we go the extra mile to make your college journey successful.

0+

Happy Clients

0+

Words Written This Week

0+

Ongoing Orders

0%

Customer Satisfaction Rate

See How We Helped 9000+ Students Achieve Success

We Analyze Your Problem and Offer Customized Writing

We understand your guidelines first before delivering any writing service. You can discuss your writing needs and we will have them evaluated by our dedicated team.

• Clear elicitation of your requirements.
• Customized writing as per your needs.

We Mirror Your Guidelines to Deliver Quality Services

We write your papers in a standardized way. We complete your work in such a way that it turns out to be a perfect description of your guidelines.

• Proactive analysis of your writing.
• Active communication to understand requirements.

We Handle Your Writing Tasks to Ensure Excellent Grades

We promise you excellent grades and academic excellence that you always longed for. Our writers stay in touch with you via email.

• Thorough research and analysis for every order.
• Deliverance of reliable writing service to improve your grades.

Place an Order Start Chat Now