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. Write the query twice, once using IN and once using UNION.
SELECT * FROM item WHERE itemname LIKE '%Hat%' OR itemname LIKE '%Helmet%' OR itemname LIKE '%Stetson%'
SELECT * FROM item WHERE itemname LIKE '%Hat%' UNION SELECT * FROM item WHERE itemname LIKE '%Helmet%' UNION SELECT * FROM item WHERE itemname LIKE '%Stetson%';
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, track, recording, composition WHERE cd.cdid = track.cdid AND recording.rcdid = track.rcdid AND composition.compid = recording.compid AND cdtitle = 'Swing';
3b.Who composed the music for Spiral?
SELECT psnfname, psnlname, psncomprole, psncomporder
FROM person, person_composition, composition
WHERE person.psnid = person_composition.psnid
AND composition.compid = person_composition.compid
AND comptitle = 'Spiral';3c. Who played which instruments for the May 4, 1959, recording of "Giant Steps"?
SELECT psnfname, psnlname, psnrcdrole FROM person, person_recording, recording, composition WHERE person.psnid = person_recording.psnid AND recording.rcdid = person_recording.rcdid AND recording.compid = composition.compid AND 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, person_composition, person_recording
WHERE person.psnid = person_recording.psnid
AND person.psnid = person_composition.psnid
AND 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 (fifth edition) by Richard T. Watson |