Assignment 4: XML Processing
Due Date: June 5
Total number of points: 100

(Solution)

1. [55 points] XML Publishing. 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

2.
Stores:

sid	Name	Phone
s282	Wiz	555-1234
s521	Econo-Wiz	555-6543

3.
Sells:

pid	Markup	sid
323	10%	s521
233	25%	s282
233	15%	s521

4.  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 subelement.

5. b. [5 points] Write the XML document obtained by exporting the database above; you have to turn in an XML document.

6. c. [10 points] Assuming that you have XML documents with the structure given in a., write an XQuery expression that returns the names and prices of all products that are sold at least at one store with a markup of 25%.

7. d. [10 points] Write the same query in SQL over the original relational database schema.

8. d. [20 points] Assume the same database is represented in an XML document whose structure follows the relational tables:

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

10.                                <row> ... <row>

11.                                <row> ... <row>

12.             <products>

13.             <stores> <row> ... <row> ... </stores>

14.             <sells>   <row> ... </row> ... </sells>

15.    </db>

16. Write an XQuery expression that, when given an input with this structure, constructs an XML document with the structure described in a. 

17. [45 points] XML Storage. Consider the DTD below:

<!ELEMENT Parties (Party*)>
<!ELEMENT Party   (Event, Organizer?, ((Time, Place) | Recurring), Participant*)>
<!ELEMENT Participant (Name, Phone*,Email*)>

Elements that are not defined are #PCDATA. The order of the participants is important in Party. The order of their phone numbers or email addresses is irrelevant. Consider a data instance X with 10 Party elements, each with one Organizer, a Time, a Place, and 20 Participants, each with exactly one Phone and one Email. Also consider the query Q below:

         for $x in /Parties/Party[Place="Tom's house"],
              $y in $x/Participant[Phone/text() = $x/Organizer/text()]
         return $y/Email/text()

1. [5 points] Design a relational schema S to store any XML data of this DTD. The schema must be reasonable: use freely (not blindly) the techniques described in [Shanmugansundaram'99].
2. [5 points] For each table in S indicate how many tuples it contains when we populate the database with X.
3. [5 points] Translate query Q into a SQL query over the schema S.
4. [5 points] Show the schema S' for the edge-relation representation of this XML data.
5. [5 points] Indicate the number of tuples in each table of S' when we populate it with the database X.
6. [5 points] Translate the query Q into a SQL query over the schema S'.
7. [5 points] Show the schema S'' for the path-encoding of this data.
8. [5 points] For each table in S'' indicate the number of tuples it contains when we populate the database with X.
9. [5 points] Translate the query Q into a SQL query over the schema S''.