CSE 444 Homework 3

Objectives:
To be able to manipulate XML: query it with XQuery and export it from relational databases.
Reading Assignments:
Lecture notes on XML and XQuery, SQL Server documentation
Number of points:
100
Due date:
November 10
Assignment Tools:
SQL Server, XQuery (Galax)
  1. [50 points] Consider the XML data instance Mondial, avaialable here (at the bottom of the page). Write XQueries to answer the following questions. In formulating your questions, you need to understand how various elements are nested: e.g. what is under a country, under which element is a city etc. For that it helps if you inspect the DTD (ignore the warning that the data is not valid), or inspect the data directly. For each question below turn in the XQuery, and the result of running the query on the XML data (which should be an XML document).

    1. Retrieve all the names of all cities located in Peru, sorted alphabetically.
    2. Find all countries with more than 20 provinces.
    3. Find all ethnic groups that live in more than more than 10 countries.
    4. Find the countries adjacent to the 'Pacific Ocean' sea.
    5. Find the names of all countries that have at least 3 mountains over 2000m high, and list the names and heights of all mountains in these countries (regardless of their height). Note: the height attribute is in meters, so you don' have to do any conversions.
    6. One user is interested in long rivers. Produce the following view of the data, containing only rivers longer than 2000 (all units are in km), in the format described below:
      • The root element is user and contains several river elements
      • Each rivercontains a name element with the river's name, and several country elements, one for each country through which it flows. (Note: some rivers may not have any country, due to noise in the data. It is OK to include these rivers, even if they look as they flow through no country at all.)
      • Each country element contains only the name of the country (a string).

    For this question you need to run galax. Here is a brief introduction to getting started with galax.

  2. [25 points] Create a database on SQL server with the following schema:

    Country(id, name, population)
    City(countryId, name, population)
    Border(countryId1, countryId2, length)

    Then populate the database with the Mondial XML data. You only need to use that information in Mondial that corresponds to the relational schema (e.g. country, city, etc), and ignore the rest of the data (e.g. regions, ethnic groups, etc). Turn in all queries/programs you used to populate your database, as well as the name of your database.

    Hint: there are two wasy to populate the relational database. The first is to use Galax to produce three comma-separated text files with the information you need for each of the three tables. (If needed, generate a single begin- and end-tag, which you can remove with a text editor). Then use the 'bulk insert' feature in SQL server to import these files into the database. The second is to use the OPENXML statement in SQL Server: please read the documentation if you choose this method.
    Click here to turn in problems 1 and 2.

  3. [25points] Consider the movies database we used in Homework 1. Generate an XML file with the following structure:

    Your XML file should include all the relevant data in the relational database, i.e. all actors, and for each actor all their movies. In this problem you can use a combination of the FOR XML feature of SQL Server and Galax queries to generate this XML file. Turn in all programs that you used to generate the XML file, as well as the resulting XML document. Click here to turn in the XML file.

    4.