5 - The Many-to-Many Relationship

Answers to exercises

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));

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;

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';

3e. Compute the total value of each sale.

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

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.

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.

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';;

7c.How many people made donations in 1999?

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

7e. What was the total amount donated in 2000?

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

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);

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;