CSE 444 Homework 3

Objectives:

To master the relational algebra, and to understand and be able to handle XML data.

Reading Assignments:

For the relational algebra: Chapters 5.2, 5.3, 5.4
For XML: Chapter: 4.6, 4.7 and lecture notes.

Number of points:

100

Due date:

November 1st, 2002

Questions:

  1. [10 points] Relational Algebra.  Consider the following schema:

            Player(pname, team, salary)
            Team(tname, city)

    The team in Player is a foreign key in Team. Write relational algebra expressions to answer the questions below.  You should turn in either a relational algebra tree, or an expression in the linear notation as in  Chapter 5.2.11 in the book.
    1. [5 points] Retrieve the names of the players that play for a team in 'Seattle'.
    2. [5 points] Retrieve the names of the teams for which all players have a salary > $1000000.
  2. [25 points] XML Querying. Consider XML data given by the following DTD:

       <!ELEMENT seattle ((theater | concert | opera)*)>
       <!ELEMENT theater (title, address, date, price*)>
       <!ELEMENT concert (title, type, date, price*)>
       <!ELEMENT opera   (title, date, price*)>

    All elements that were not indicated have #PCDATA as content. For each of the questions below write an XPath or an XQuery query.
    1. [5 points] Return all titles in the XML document
    2. [5 points] Find the addresses of all theaters that have some tickets under $20 on 11/2/2002 and the titles of their show on that night.
    3. [5 points] Retrieve all concert titles whose type is 'chamber orchestra' where the average ticket price is > $50.
    4. [10 points] Write a query that constructs a new XML document with the following structure:

      <!ELEMENT groupedByDate (day*)>
      <!ELEMENT day (date, show*)>
      <!ELEMENT show (title, price*)>
  3. [30 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


    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

    1. [5 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.

    2. [10 points] Assume the relational database is accessible through an XML interface that exports it as:

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

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

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

         </db>

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

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

    4. [5 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%.
    5. [5 points] Write the same query in SQL over the original relational database schema.

  4. [30 points] XML Storage. Consider the DTD below:

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

    Elements that are not defined are #PCDATA. The order of all elements in the document  is irrelevant. Consider a data instance X with 10 Party elements, each with one OrganizerPhone, 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/text()="Tom's house"],
                  $y in $x/Participant[Phone/text() = $x/OrganizerPhone/text()]
             return $y/Email/text()

    1. [5 points] Design a relational schema S to store such an XML data.  Choose a good database design, given the DTD.
    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'.
  5. [5 points] Please answer the following questions below. Please answer them online, at  http://iinetsrv.cs.washington.edu/CSE444Feedback/Start.aspx
    1. How long did it take you to complete this assignment?
    2. What did you like the best about this assignment?
    3. What did you like the least about this assignment?
    4. What helped you learn the best in this assignment?
    5. What distracted from your learning in this assignment?