Chapter 5

1.

Draw data models for the following situations. In each case, think about the names you give each entity:

1a.

Farmers can own cows or share cows with other farmers.

1b.

A track and field meet can have many competitors, and a competitor can participate in more than one event.

1c.

A patient can have many physicians, and a physician can have many patients.

1d.

A student can attend more than one class, and the same class can have many students.

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.

CREATE TABLE player (
   playerno SMALLINT,
   pfname    VARCHAR(15),
   plname    VARCHAR(20),
   pdob      DATE,
     PRIMARY KEY (playerno));
CREATE TABLE competition (
   compno    SMALLINT,
   location  VARCHAR(20),
   comptitle VARCHAR(20),  
   datebegin DATE,  
      PRIMARY KEY (compno));
CREATE TABLE field (  
   playerno  SMALLINT,  
   compno    SMALLINT,  
     PRIMARY KEY (playerno, compno),  
     CONSTRAINT fkplayer1 FOREIGN KEY(playerno) REFERENCES player(playerno),  
     CONSTRAINT fkcomp1 FOREIGN KEY(compno) REFERENCES competition(compno)); 
CREATE TABLE golfround(  
   roundno   SMALLINT,  
   rounddate DATE,  
   compno    SMALLINT,  
     PRIMARY KEY (roundno, compno),  
     CONSTRAINT fkcomp2 FOREIGN KEY(compno) REFERENCES competition(compno));
CREATE TABLE hole ( 
   holeno    SMALLINT,  
   length    SMALLINT,  
   par       SMALLINT,  
   roundno   SMALLINT,  
   compno    SMALLINT,  
     PRIMARY KEY (holeno, roundno, compno),  
     CONSTRAINT fkround FOREIGN KEY(roundno, compno) REFERENCES golfround(roundno, compno));
CREATE TABLE score ( 
   strokes   SMALLINT,  
   holeno    SMALLINT,  
   roundno   SMALLINT,  
   compno    SMALLINT,  
   playerno  SMALLINT,  
     PRIMARY KEY (holeno, roundno, compno, playerno),  
     CONSTRAINT fkhole FOREIGN KEY(holeno, roundno, compno) REFERENCES hole(holeno, roundno, compno), 
     CONSTRAINT fkplayer2 FOREIGN KEY(playerno) REFERENCES player(playerno));

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 JOIN lineitem
ON item.itemno = lineitem.itemno WHERE lineqty > 1;

itemname	lineqty
Safari chair	50
Hammock	50
Tent - 8 person	8

3b.

Compute the total value of sales for each item by date.

SELECT itemname, saledate, SUM(lineqty*lineprice) 
FROM item JOIN lineitem
ON item.itemno = lineitem.itemno 
JOIN sale
ON sale.saleno = lineitem.salemno
GROUP BY itemname, saledate;

itemname	expr101
Geo positioning system	2
Hammock	50
Pocket knife - Avon	5
Safari chair	50
Tent - 2 person	2
Tent - 8 person	8

3c.

Report all items of type “F” that have been sold.

SELECT itemname FROM item
  WHERE itemtype = 'F'
  AND EXISTS 
   (SELECT * FROM lineitem
	 WHERE lineitem.itemno = item.itemno);

SELECT DISTINCT(itemname) FROM item JOIN lineitem
  ON item.itemno = lineitem.itemno
  WHERE itemtype = 'F';

itemname
Hammock
Safari chair
Tent - 2 person
Tent - 8 person

3d.

List all items of type “F” that have not been sold.

SELECT DISTINCT(itemname) FROM item 
 WHERE itemtype = 'F' 
 AND NOT EXISTS
  (SELECT * FROM lineitem 
    WHERE item.itemno = lineitem.itemno);

SELECT DISTINCT(itemname) FROM item 
 WHERE itemtype = 'F' 
 AND itemname NOT IN
  (SELECT itemname FROM item JOIN lineitem 
    ON item.itemno = lineitem.itemno WHERE itemtype = 'F');

3e.

Compute the total value of each sale.

SELECT saleno, SUM(lineqty * lineprice) 
FROM lineitem 
GROUP BY saleno;

saleno	sum
1	4.50
2	72.25
3	502.25
4	712.25
5	5109.00

4.

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.

5.

What does a plus sign near a relationship arc mean?

The plus sign near a relationship arc signifies that the identifier of the one-end of the relationship is part of the identifier for the entity at the many-end.

6.

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.

7.

Answer the following queries based on the described relational database.

7a.

List the phone numbers of donors with last names Hays or Jefts.

SELECT dphone FROM donor 
WHERE dlname = 'Hays' OR dlname = 'Jefts';;

dphone
1352
8103


7b.

How many donors are there in the donor table?

SELECT COUNT (*) FROM donor;


7c.

How many people made donations in 1999?

SELECT COUNT(donorno) FROM gift 
WHERE year = 1999;

expr1000
10


7d.

What is the name of the person who made the largest donation in 1999?

SELECT dlname, dfname FROM donor JOIN gift 
ON donor.donorno = gift.donorno 
WHERE year = 1999 
AND amount = (SELECT MAX(amount) FROM gift WHERE year = 1999);


7e.

What was the total amount donated in 2000?

SELECT SUM (amount) FROM gift 
WHERE year = 2000;

expr1000
$6,768.00


7f.

List the donors who have made a donation every year.

SELECT dlname, dfname FROM donor 
WHERE NOT EXISTS (SELECT * FROM year 
WHERE NOT EXISTS (SELECT * FROM gift 
WHERE donor.donorno = gift.donorno 
AND year.year = gift.year));


7g.

a. List the donors whose average donation is more than twice the average donation of all donors.

SELECT dlname, dfname, AVG(amount) 
FROM donor JOIN gift 
ON 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


7h.

List the total amount given by each person across all years; sort the report by the donor

SELECT dlname, dfname, SUM(amount) 
FROM donor JOIN gift 
ON donor.donorno = gift.donorno 
GROUP BY dlname, dfname 
ORDER BY dlname, dfname;


7i.

Report the total donations in 2001 by state.

SELECT DSTATE, SUM(amount) 
FROM donor JOIN gift 
ON donor.donorno = gift.donorno
WHERE 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


7j.

In which years did the total donated exceed the goal for the year?

SELECT year.year, yeargoal AS Goal, SUM(amount) AS Total
FROM year JOIN gift 
ON year.year = gift.year 
WHERE yeargoal < (SELECT SUM(amount) 
FROM gift 
WHERE year.year = gift.year) 
GROUP BY year.year

8.

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
		% Daily Value
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%

5 - The Many-to-Many Relationship

Answers to exercises

Date revised: 10-Dec-2021