s
Section 5.3 Relations and Databases
379
25. Suppose a join operation over some attribute is to be done on two tables of cardinality p and q,
respectively.
a. The first step is usually to form the Cartesian product of the two relations and then examine the resulting tuples to find those with a common attribute value. How many tuples result from the Cartesian
product that then have to be examined to complete the join operation?
b. Now suppose that the two tables have each been sorted on the common attribute. Explain how the join
operation can be done more cleverly, avoid the Cartesian product, and examine (read) at most only
(p + q) rows.
c. To accomplish a join operation of Author and Writes over Name, how many rows must be examined?
d. To accomplish a join operation of Book and Writes over ISBN, how many rows must be examined?
(See Exercise 26 for why this operation would not be a good idea anyway.)
26. One rule of thumb about good database design is “one fact, one place.” Suppose you try to combine the
Book and Writes tables over ISBN into a single relation as was done with the PetName and Owns relation.
This table would have a heading of the form
iSBN
Title
publisher
Subject
Name
How would the resulting table violate the “one fact, one place” rule? How many tuples have to be updated
if the publisher “Bellman” changes its name to “Bellman-Boyd”?
For Exercises 27 and 28, suppose that an additional attribute called RoyaltyPercent with a domain of integers
between 0 and 100 is added to the Writes relation. The new Writes table appears here. Because the domain of
RoyaltyPercent is numerical, arithmetic comparisons can be done on a given RoyaltyPercent value.
Writes
Name
iSBN
royaltypercent
Chan, Jimmy
0-364-87547-X
20
East, Jane
0-56-000142-8
100
King, Dorothy
0-816-35421-9
100
King, Dorothy
0-816-88506-0
100
Kovalsco, Bert
0-816-53705-4
100
Lau, Won
0-364-87547-X
80
Nkoma, Jon
0-115-01214-1
100
27. a. Write an SQL query to give the author’s name, the title and ISBN of the book, and the royalty percent
for all authors with a royalty percent of less than 100.
b. Write the results of the query.
28. What database integrity errors would be caused by attempting each of the following actions?
a. Adding a tuple in the Writes table: Wilson, Jermain 0-115-01214-1
40
b. Modifying a tuple in the Writes table: Chan, Jimmy 0-364-87547-X Sixty
Section 5.3 Relations and Databases
379
25. Suppose a join operation over some attribute is to be done on two tables of cardinality p and q,
respectively.
a. The first step is usually to form the Cartesian product of the two relations and then examine the resulting tuples to find those with a common attribute value. How many tuples result from the Cartesian
product that then have to be examined to complete the join operation?
b. Now suppose that the two tables have each been sorted on the common attribute. Explain how the join
operation can be done more cleverly, avoid the Cartesian product, and examine (read) at most only
(p + q) rows.
c. To accomplish a join operation of Author and Writes over Name, how many rows must be examined?
d. To accomplish a join operation of Book and Writes over ISBN, how many rows must be examined?
(See Exercise 26 for why this operation would not be a good idea anyway.)
26. One rule of thumb about good database design is “one fact, one place.” Suppose you try to combine the
Book and Writes tables over ISBN into a single relation as was done with the PetName and Owns relation.
This table would have a heading of the form
iSBN
Title
publisher
Subject
Name
How would the resulting table violate the “one fact, one place” rule? How many tuples have to be updated
if the publisher “Bellman” changes its name to “Bellman-Boyd”?
For Exercises 27 and 28, suppose that an additional attribute called RoyaltyPercent with a domain of integers
between 0 and 100 is added to the Writes relation. The new Writes table appears here. Because the domain of
RoyaltyPercent is numerical, arithmetic comparisons can be done on a given RoyaltyPercent value.
Writes
Name
iSBN
royaltypercent
Chan, Jimmy
0-364-87547-X
20
East, Jane
0-56-000142-8
100
King, Dorothy
0-816-35421-9
100
King, Dorothy
0-816-88506-0
100
Kovalsco, Bert
0-816-53705-4
100
Lau, Won
0-364-87547-X
80
Nkoma, Jon
0-115-01214-1
100
27. a. Write an SQL query to give the author’s name, the title and ISBN of the book, and the royalty percent
for all authors with a royalty percent of less than 100.
b. Write the results of the query.
28. What database integrity errors would be caused by attempting each of the following actions?
a. Adding a tuple in the Writes table: Wilson, Jermain 0-115-01214-1
40
b. Modifying a tuple in the Writes table: Chan, Jimmy 0-364-87547-X Sixty