Banner

 

5 - The Many-to-Many Relationship

Answers to exercises

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.
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.
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
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
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.

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
7c.
How many people made donations in 1999?
SELECT COUNT(donorno) FROM gift 
WHERE year = 1999;
expr1000
10
7e.
What was the total amount donated in 2000?
SELECT SUM (amount) FROM gift 
WHERE year = 2000;
expr1000
$6,768.00
7g.
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
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

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

Date revised: 19-Oct-2016