CSE 594 – HW #3 Sample Solution
1. a. <?xml version="1.0"?>
<!DOCTYPE products [
<!ELEMENT products
(product)*>
<!ELEMENT product
(name,price,description,store*)>
<!ELEMENT store
(name,phone,markup)>
<!ELEMENT name
(#PCDATA)>
<!ELEMENT price
(#PCDATA)>
<!ELEMENT description (#PCDATA)>
<!ELEMENT phone
(#PCDATA)>
<!ELEMENT markup
(#PCDATA)>
]>
b. <products>
<product>
<name>gizmo</name>
<price>22.99</price>
<description>great</description>
<store>
<name>Econo-Wiz</name>
<phone>555-6543</phone>
<markup>10</markup>
</store>
</product>
<product>
<name>gizmo plus</name>
<price>99.99</price>
<description>more features</description>
<store>
<name>Wiz</name>
<phone>555-1234</phone>
<markup>25</markup>
</store>
<store>
<name>Econo-Wiz</name>
<phone>555-6543</phone>
<markup>15</markup>
</store>
</product>
<product>
<name>gadget</name>
<price>59.99</price>
<description>good value</description>
</product>
</products>
c. <products>
{
FOR $x in document(“data.xml”)/products/product
WHERE $x/store/markup = “25%”
RETURN <product>{$x/name, $x/price}</product>
}
</products>
d. select distinct name, price
from Products, Sells
where Products.pid = Sells.pid and Sells.Markup=’25%’
2. a. wnyc( wnycID: integer )
piece( pieceID: integer, parentID: integer, time: string, title: string, composer: string, conductor: string, orchestra: string, publisher: string )
soloist( soloistID: integer, parentID: integer, name: string)
b.
wnyc
wnycID |
0 |
piece
pieceID |
parentID |
time |
title |
composer |
conductor |
orchestra |
publisher |
1 |
0 |
12:26 PM |
Mad Rush |
Philip Glass |
null |
null |
Romeo 7204 |
2 |
0 |
12:49 PM |
Concerto No. 12 in E, Op. 3, RV 265 |
Antonio Vivaldi |
Fabio Biondi |
Europa Galante |
Virgin Classics 45315 |
3 |
0 |
1:01 PM |
Symphony No. 1 in e, Op. 39 |
Jean Sibelius |
Leonard Bernstein |
Vienna Philharmonic Orchestra |
Deutsche Grammophon 435351 |
4 |
0 |
1:47 PM |
Andante for Piano ("Andante favori") |
Ludwig van Beethoven |
null |
null |
Philips 438472 |
5 |
0 |
1:57 PM |
Upon Enchanted Ground |
Alan Hovhaness |
null |
null |
Telarc 80530 |
soloist
soloistID |
parentID |
name |
13 |
1 |
Aleck Karis, piano |
44 |
4 |
Alfred Brendel, piano |
55 |
5 |
Yolanda Kondonassis, harp |
56 |
5 |
Frank Hendrickx, alto flute |
57 |
5 |
Herwig Coryn, cello |
58 |
5 |
Patrick De Smet, tam-tam |
c. FOR $x in distinct( document( “wnyc.xml” ) /wnyc/piece
WHERE $x/title = “Mad Rush”
AND $x/composer = “Philip Glass”
RETURN <result> $x/conductor, $x/time </result>