CSE 594, Spring 2002
pid |
Name |
Price |
Description |
323 |
gizmo |
22.99 |
great |
233 |
gizmo plus |
99.99 |
more features |
312 |
gadget |
59.99 |
good value |
Stores:
sid |
Name |
Phone |
s282 |
Wiz |
555-1234 |
s521 |
Econo-Wiz |
555-6543 |
Sells:
pid |
Markup |
sid |
323 |
10% |
s521 |
233 |
25% |
s282 |
233 |
15% |
s521 |
a. [10
points] We want to export this data into an XML file. Write a DTD describing
the following structure for the XML file:
- there is one root element called products
- the products element contains a sequence of product sub
elements, one for each product in the database
- each product element contains one name, one price,
and one description subelement, and a sequence of store
subelements, one for each store that sells that product:
- each store element contains one name, one
phone, and one markup.
b. [10
points] Write the XML document obtained by exporting the database above.
c. [10
points] Assuming that you have XML documents with the structure given in a.,
write an XQuery query that returns the names and prices of all products that
are sold at least at one store with a markup of 25%.
d. [10
points] Write the same query in SQL over the original relational database
schema.
2. Consider the DTD below:
<!ELEMENT wnyc (piece*)>
<!ELEMENT piece (time, title, composer, conductor?, orchestra?, soloist*,
publisher>
<!ELEMENT time (#PCDATA)>
. . . [the content model of all leaf elements is #PCDATA] . . .
<!ELEMENT publisher (#PCDATA)>
<!ATTLIST wnyc id ID REQUIRED>
. . . [all elements have an id attribute] . . .
<!ATTLIST publisher id ID REQUIRED>
Note: In order to answer the following 2 subparts you will need to read one of two papers:
Relational Databases for Querying XML Documents: Limitations and Opportunities. Shanmugasundaram, Tufte, Zhang, He, DeWitt, Naughton, VLDB 1999.
A Performance Evaluation of Alternative Mapping Schemes for Storing XML Data in a Relational Database. Florescu, Kossmann, INRIA Technical Report.
The papers can be found at: http://www.cs.washington.edu/education/courses/590db/00wi/ under the topic Storing XML as Relations.
a. [10 points] Design a relational schema that can store one XML document of this DTD
b. [10 points] Show the relational database instance that
corresponds to the following XML document:
<wnyc id="0">
<piece id="1"> <time id="10">
12:26 PM </time>
<title id="11"> Mad Rush </title>
<composer id="12"> Philip Glass </composer>
<soloist id="13"> Aleck Karis, piano </soloist>
<publisher id="14"> Romeo 7204 </publisher>
</piece>
<piece id="2"> <time id="21">
12:49 PM </time>
<title id="22"> Concerto No. 12 in E, Op. 3, RV 265
</title>
<composer id="23"> Antonio Vivaldi </composer>
<conductor id="24"> Fabio Biondi </conductor>
<orchestra id="25"> Europa Galante </orchestra>
<publisher id="26"> Virgin Classics 45315 </publisher>
</piece>
<piece id="3"> <time
id="31"> 1:01 PM </time>
<title id="32"> Symphony No. 1 in e, Op. 39 </title>
<composer id="33"> Jean Sibelius </composer>
<conductor id="34"> Leonard Bernstein </conductor>
<orchestra id="35"> Vienna Philharmonic Orchestra
</orchestra>
<publisher id="36"> Deutsche Grammophon 435351
</publisher>
</piece>
<piece id="4"> <time
id="41"> 1:47 PM </time>
<title id="42"> Andante for Piano ("Andante favori")
</title>
<composer id="43"> Ludwig van Beethoven </composer>
<soloist id="44"> Alfred Brendel, piano </soloist>
<publisher id="45"> Philips 438472 </publisher>
</piece>
<piece id="5"> <time
id="51"> 1:57 PM </time>
<title id="52"> Upon Enchanted Ground </title>
<composer id="53"> Alan Hovhaness </composer>
<soloist id="55"> Yolanda Kondonassis, harp </soloist>
<soloist id="56"> Frank Hendrickx, alto flute </soloist>
<soloist id="57"> Herwig Coryn, cello </soloist>
<soloist id="58"> Patrick De Smet, tam-tam </soloist>
<publisher id="59"> Telarc 80530 </publisher>
</piece>
</wnyc>
c. [10 points] Write an XQuery query to retrieve all conductors that conduct "Mad Rush" by "Philip Glass", and the times they are broadcasting.