Banner

 

5 - The Many-to-Many Relationship
Skill Builders

A hamburger shop makes several types of hamburgers, and the same type of ingredient can be used with several types of hamburgers. This does not literally mean the same piece of lettuce is used many times, but lettuce is used with several types of hamburgers. Draw the data model for this situation. What is a good name for the associative entity?


Report all red items that have been sold. Write the query twice, once using EXISTS and once without EXISTS.

SELECT itemname, itemcolor FROM item
WHERE itemcolor = 'Red'
AND EXISTS 
   (SELECT * FROMlineitem
   WHERE lineitem.itemno = item.itemno);
   
SELECT itemname, itemcolor FROM item
WHERE itemcolor = 'Red' AND itemno IN 
(SELECT itemno FROM lineitem);

Report all red items that have not been sold. Write the query twice, once using EXISTS and once without EXISTS.

SELECT itemname, itemcolor FROM item
WHERE itemcolor = 'Red'
AND NOT EXISTS 
   (SELECT * FROMlineitem
   WHERE lineitem.itemno = item.itemno);
   
SELECT itemname, itemcolor FROM item
WHERE itemcolor = 'Red' AND itemno NOT IN 
(SELECT itemno FROM lineitem);

Find the brown items that have appeared in all sales.

SELECT itemname FROM item
WHERE itemcolor = 'Brown'
AND NOT EXISTS 
   (SELECT *
   FROM sale WHERE NOT EXISTS
      (SELECT * FROM lineitem
      WHERE lineitem.itemno = item.itemno
      AND lineitem.saleno = sale.saleno));

List the items that contain the words "Hat", "Helmet", or "Stetson" in their name.

SELECT itemname FROM item
WHERE itemname REGEXP 'Hat|hat|Helmet|helmet|Stetson|stetson';

1. What data will you enter in PERSON-CD to relate John Coltrane to the Giant Steps CD?

The entry into the table person-cd for John Coltrane's Giant Steps CD will look like this:

psnid cdid psncdorder
1 1 1

Person ID (psnid) 1 stands for John Coltrane in the Person table, cdid 1 stands for the CD Giant Steps in the cd table and person-cd-order (psncdorder) 1 stands for the fact that John Coltrane is the first (and only) artist mentioned on the CD cover.


2. Enter appropriate values in PERSON-COMPOSITION to relate John Coltrane to compositions with compid 1 through 7. You should indicate he wrote the music by entering a value of "music " for the person ' s role in the composition.

Since John Coltrane wrote the music on compositions 1-7, the entries will be as follows:

psnid compid psncomprole psncomporder
1 1 music 1
1 2 music 1
1 3 music 1
1 4 music 1
1 5 music 1
1 6 music 1
1 7 music 1

Person ID (psnid) 1 stands for John Coltrane, composition ID (compid) 1-7 stands for the 7 compositions, person-composition-role (psncomprole) stands for the role the person played in composing this piece. Person- composition-order (psncomporder) stands for the order the person is mentioned in this part of the composition. Since John Coltrane composed the music for these 7 pieces alone, he is mentioned first.


3a. List the tracks on Swing.

SELECT trknum, comptitle FROM cd JOIN track
   JOIN recording ON recording.rcdid = track.rcdid
   JOIN composition ON composition.compid = recording.compid
   WHERE cdtitle = 'Swing'; 

3b.Who composed the music for Spiral?

SELECT psnfname, psnlname, psncomprole, psncomporder
   FROM person JOIN person_composition ON person.psnid = person_composition.psnid
      JOIN composition ON composition.compid = person_composition.compid
      WHERE comptitle = 'Spiral';

3c. Who played which instruments for the May 4, 1959, recording of "Giant Steps"?

SELECT psnfname, psnlname, psnrcdrole
   FROM person JOIN person_recording ON person.psnid = person_recording.psnid
   JOIN recording ON recording.rcdid = person_recording.rcdid
   JOIN composition ON recording.compid = composition.compid
   WHERE  comptitle = 'Giant Steps'
   AND RCDDATE = '1959-05-04';

3d. List the composers who write music and play the tenor sax?

SELECT psnfname, psnlname
   FROM person JOIN  person_recording ON person.psnid = person_recording.psnid
      JOIN person_composition ON  person.psnid = person_composition.psnid
      WHERE psnrcdrole = 'tenor sax'
      AND psncomprole = 'music';

4. What is the data model missing?

The data model is missing information on the name of bands or other groups featured on the CDs. So far there is only information on individuals.

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