158.247 Database Design

Assignment 3

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-
s521 Econo-Wiz 555-

Sells
pid Markup sid
323 10% s
233 25% s
233 15% s

Q1. We want to export this data into an XML file. Write a DTD describing the following XML structure:

• there is one root element calledproducts
• theproductselement contains a sequence ofproductsub elements, one for each product in the database
• eachproductelement contains onenamesubelement, onepricesubelement, onedescriptionsubelement, and a sequence ofstoresubelements, one for each store that sells that product
• eachstoreelement contains onename, onephone, and onemarkupelement

Create an XML documentproducts.xmlobtained by exporting the database above. Include the DTD in your
XML document, and make sure to validate it. [6 marks]

Q2. Assuming that you have an XML document with the structure given in Q1, write an XQuery that returns the names and prices of all products that are sold at one or more stores with a markup of exactly 25%. Save the query asquery.txt. [4 marks]

Q3. Assume the same database is represented in an XML document whose structure follows the relational tables:

<db>
<products>
<row>
<pid>323</pid>
<name>gizmo</name>
<price>22.99</price>
<description>great</description>
</row>
...
</products>
<stores>
...
</stores>
<sells>
...
</sells>
</db>

Write an XQuery that, when given an input with the structure described above, constructs an XML document
with the structure described in Q1. Save this query astransform.txt [10 marks]

Submitproducts.xml,query.txtandtransform.txt.

1