Chapter 5

Answers to exercises

1.
Draw data models for the following entities. In each case, think about the names you give each entity:
1a.
Farmers can own cows or share cows with other farmers.
1c.
A patient can have many physicians, and a physician can have many patients.
1e.
The Marathoner, a monthly magazine, regularly reports the performance of professional marathon runners. It has asked you to design a database to record the details of all major marathons (e.g., Boston, London, and Paris). Professional marathon runners compete in several races each year. A race may have thousands of competitors, but only about 200 or so are professional runners, the ones The Marathoner tracks. For each race, the magazine reports a runner's time and finishing position and some personal details such as name, gender, and age.
2.
The data model shown was designed by a golf statistician. Write SQL statements to create the corresponding relational database.
player
playerno pfname plname pdob
competition
compno location comptitle datebegin
field
playerno compno
round
roundno date compno
hole
holeno length par roundno compno
score
strokes holeno roundno compno playerno 
CREATE TABLE player ( 
playerno INTEGER, pfname VARCHAR(15), plname VARCHAR(20),
pdob DATE, PRIMARY KEY (playerno)); 
CREATE TABLE competition ( 
compno INTEGER,
location VARCHAR(20), 
comptitle VARCHAR(20), 
datebegin DATE, 
PRIMARY KEY(compno)); 
CREATE TABLE field ( 
playerno INTEGER, compno INTEGER, 
PRIMARY KEY(playerno, compno) 
FOREIGN KEY fkplayer(playerno) REFERENCES player, 
FOREIGN KEY fkcompetition(compno) REFERENCES competition); CREATE TABLE round ( 
roundno INTEGER, date DATE, 
compno INTEGER, PRIMARY KEY(roundno, compno), 
FOREIGN KEY fkcompetition(compno)REFERENCES competition); CREATE TABLE hole ( 
holeno INTEGER, length INTEGER, par CHAR(2), 
roundno INTEGER, compno INTEGER, 
PRIMARY KEY (holeno, roundno, compno), 
FOREIGN KEY fkround(roundno, compno) REFERENCES round); 
CREATE TABLE score ( 
strokes INTEGER, holeno INTEGER NOT NULL, 
roundno INTEGER NOT NULL, 
compno INTEGER NOT NULL, 
playerno INTEGER NOT NULL, 
PRIMARY KEY(holeno, roundno, compno, playerno) 
FOREIGN KEY fkhole(holeno, roundno,compno) 
REFERENCES hole, 
FOREIGN KEY fkplayer(playerno) REFERENCES player);
3.
Write the following SQL queries for the database described in this chapter:
3a.
List the names of items for which the quantity sold is greater than one for any sale.
SELECT itemname FROM item, lineitem WHERE item.itemno = lineitem.itemno AND lineqty > 1;
itemname
lineqty
Safari chair 50
Hammock 50
Tent - 8 person 8
3c.
Report all items of type “F” that have been sold. 
SELECT DISTINCT(itemname) FROM item, lineitem
WHERE item.itemno = lineitem.itemno AND itemtype = 'F';
itemname
Hammock
Safari chair
Tent - 2 person
Tent - 8 person
3e.
Compute the total value of each sale. 
SELECT sale.saleno, SUM (lineqty * lineprice) 
FROM sale, lineitem 
WHERE sale.saleno = lineitem.saleno 
GROUP BY sale.saleno;
saleno sum
1
4.50
2
72.25
3
502.25
4
712.25
5
5109.00
5.
Why do you have to create a third entity when you have an m:m relationship?

One has to create a third entity to store facts about the m:m relationship.

7.
How does EXIST differ from other clauses in an SQL statement?

The main difference between EXIST and other clauses in an SQL statement is that EXIST returns the value true or false instead of a table. As long as at least one row can be found that satisfies the EXIST condition, true is returned. Otherwise false is returned.

9.
Answer the following queries based on the described relational database.
9a.
List the phone numbers of donor number 106 and 125. 
SELECT dphone FROM donor 
WHERE donorno = 106 OR donorno = 125;
dphone
2124
9c.
How many people made donations in 1999? 
SELECT COUNT(donorno) FROM gift 
WHERE year = 1999;
expr1000
10
9e.
What was the total amount donated in 2000? 
SELECT SUM (amount) FROM gift 
WHERE year = 2000;
expr1000
$6,768.00
9g.
List the donors who give twice the average. 
SELECT dlname, dfname, AVG(amount) 
FROM donor, gift 
WHERE donor.donorno = gift.donorno 
GROUP BY dlname, dfname 
HAVING AVG(amount) > (SELECT 2*AVG(amount) FROM gift);
dfname
dlname
expr1002
Gulsen Beckman $2,405.00
9i.
Report the total donations in 2001 by state. 
SELECT DSTATE, SUM(amount) 
FROM donor, gift 
WHERE donor.donorno = gift.donorno
AND year = 2001 GROUP BY dstate;
dstate
expr1001
AK $332.00
AZ $155.00
GA $671.00
MA $499.00
MN $835.00
NC $966.00
ND $345.00
WA $5,208.00
10.
The following table records data found on the side of a breakfast cereal carton. Use these data as a guide to develop a data model to record nutrition facts for a meal. In this case, a meal is a cup of cereal and 1/2 cup of skim milk.
Nutrition facts
Serving size 1 cup (30g)
Servings per container about 17
Amount per serving Cereal with 1/2 cup

of skim milk
Calories 110 150
Calories from Fat 10 10
Total Fat 1g 1% 2%
Saturated Fat 0g 0% 0%
Polyunsaturated Fat 0g
Monounsaturated Fat 0g
Cholesterol 0mg 0% 1%
Sodium 220mg 9% 12%
Potassium 105 mg 3% 9%
Total Carbohydrate 24g 8% 10%
Dietary Fiber 3g 13% 13%
Sugars 4g
Other Carbohydrate 17g
Protein 3g
Vitamin A 10% 15%
Vitamin C 10% 10%
Calcium 2% 15%
Iron 45% 45%
Vitamin D 10% 25%
Thiamin 50% 50%
Riboflavin 50% 50%
Niacin 50% 50%
Vitamin B12 50% 60%
Phosphorus 10% 20%
Magnesium 8% 10%
Zinc 50% 50%
Copper 4% 4%

This page is part of the promotional and support material for Data Management (fifth edition) by Richard T. Watson
For questions and comments please contact the author

Revised: Thu, Nov 17, 2005