CSE 594, Spring 2002

 

Assignment #3: due Thursday, May 9

 

  1. Consider the following relational data:

    Products:

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.