CSc 8711 Databases and the Web - Spring 2013

Csc 8711, Databases and the Web - Project 2

Due: Sunday, February 24th
Individual Assignment.

XPath and XQuery

movies.xml (from Web DM textbook Chapter 5) Solve XPath problems from section 4.1 and XQuery problems from section 4.2.
mondial.xml: Consider the XML data instance mondial.xml. Write XQuery expressions to answer the following questions. The output of queries should conform to the DTD that follows the query.
1. Retrieve all the names of all cities located in Peru, sorted alphabetically.
```
    <!ELEMENT result (country)>
    <!ELEMENT country (name, city+)>
    <!ELEMENT city (name)>
    <!ELEMENT name (#PCDATA)>
```
2. For each province of China, return its capital. Order the result by province name.
```
    <!ELEMENT result (country)>
    <!ELEMENT country (name, province+)>
    <!ELEMENT province (name, capital)>
    <!ELEMENT capital (name)>
    <!ELEMENT name (#PCDATA)>
```
3. Find all countries with more than 20 provinces. Order by the number of provinces.
```
    <!ELEMENT result (country*)>
    <!ELEMENT country (name)>
    <!ATTLIST country num_provinces CDATA #REQUIRED>
    <!ELEMENT name (#PCDATA)>
```
4. For each province(state) in the United States, compute the ratio of its population to area, and return each province's name, its computed ratio, and order them by ratio.
```
    <!ELEMENT result (country)>
    <!ELEMENT country (name, state+)>
    <!ELEMENT state (name, population_density)>
    <!ELEMENT name (#PCDATA)>
    <!ELEMENT population_density (#PCDATA)>
```
5. Find all ethnic groups that live in more than 10 countries.
```
    <!ELEMENT result (ethnicgroups+)>
    <!ELEMENT ethnicgroups (name)>
    <!ATTLIST ethnicgroups num_countries CDATA #REQUIRED>
    <!ELEMENT name (#PCDATA)>
```
6. Find the countries adjacent to the 'Pacific Ocean' (sea).
```
    <!ELEMENT result (waterbody)>
    <!ELEMENT waterbody (name, adjacent_countries+)>
    <!ELEMENT adjacent_countries (country+)>
    <!ELEMENT country (name)>
    <!ELEMENT name (#PCDATA)>
```
  Note: Use the country attribute for the tag to find the respective countries.
7. Find all the provinces(states) of the United States with population more than 11,000,000. Compute the ratio of each qualified state's population to the whole population of the country. Return each state's name and the ratio. Order by the ratio in descending order.
```
    <!ELEMENT result (country)>
    <!ELEMENT country (name, state+)>
    <!ELEMENT state (name, population_ratio)>
    <!ELEMENT name (#PCDATA)>
    <!ELEMENT population_ratio (#PCDATA)>
```
8. For each river which crosses at least 2 countries, return its name, and the names of the countries it crosses. Order by the numbers of countries crossed.
```
    <!ELEMENT html (head, body)>
    <!ELEMENT head (title)>
    <!ELEMENT title (#PCDATA)>
    <!ELEMENT body (h1, ul)>
    <!ELEMENT h1 (#PCDATA)>
    <!ELEMENT ul (li+)>
    <!ELEMENT li (#PCDATA | font | ol)*>
    <!ELEMENT ol (li+)>
    <!ELEMENT font (#PCDATA)>
```
  The idea with the <li> containing a font and ol tag is such that the output looks roughly like:
```
    ...
    <ul>
      <li>
        <font>River name<font>
        <ol>
          <li>Country crossed #1<li>
          <li>Country crossed #2<li>
          ...
        <ol>
      <li>
      ...
    <ul>
```
  Note: Use the country attribute for the tag <river> to find the respective countries.
9. 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't have to do any conversions.
```
    <!ELEMENT result (country+)>
    <!ELEMENT country (name, mountains+)>
    <!ELEMENT mountains (name, height)>
    <!ELEMENT height (#PCDATA)>
    <!ELEMENT name (#PCDATA)>
```
10. 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:
```
    <!ELEMENT user (river+)>
    <!ELEMENT river (name, country*)>
    <!ELEMENT country (name)>
    <!ELEMENT name (#PCDATA)>
```
  Note 1: 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.)
  Note 2: Use the country attribute for the tag <river> to find the respective countries.

XML Schema

Consider the XML document bank.xml describing banking data. Write a XML Schema specification for the document that satisfies the following requirements:
- There are two account types: checking and savings accounts
- The account id is unique in 'accounts'
- The customer id is unique in 'customers'
- 'c_id' refers to customers and 'ac_id' refers to accounts
- The account balance must be greater than -5000
- Use inheritance for checking and savings accounts by deriving from a common account type
Solve problem 8.7 (page 427) from Oracle 10g Programming textbook. mo.xml

XSLT

Write 3 different XSL transforms that allow for browsing of the data available in mondial.xml. You may choose to display HTML pages for some of the data (such as countries and their capital cities or countries and populations).
Problem 9.7 (page 476) from Oracle 10g Programming textbook: Consider the XML document described in Problem 8.7 related to the mail order database. Write an XSLT program that takes as input an order number and produces a well formatted invoice as a Web page for the given order. The invoice should include customer details, employee information, as well as order details for the order. Java servlets may be used to invoke the XSLT program.