CSc 8711 Databases and the Web - Spring 2015

Csc 8711, Databases and the Web - Project 2

Due: Sunday, February 22nd
Individual Assignment.

XML Schema

Consider the following XML document containing the transcript for a student:
```
  <Transcript>
    <Sid>111111111</Sid> 
    <Name>John Doe</Name>
    <Courses>
      <Course>
        <Code>CS308</Code> 
        <Sem>F1997</Sem> 
        <Grade>B</Grade>
      </Course>
      …
    </Courses>
  </Transcript>
```
The <Sid> element contains 9 digit social security numbers, the <Code> element contains 2 upper-case letters followed by 3 digits, the <Sem> element contains a letter (F, S, or U) followed by a 4-digit year, and the <Grade> element contains one of the grades: A, B, C, D, F, IP, S, or U.
Write an XML Schema specification for the transcript document and validate several instance documents against the schema.

Consider the following XML document that describes data related to the postings in a typical bulletin board:

  <postings>
    <posting postId="1" postDate="2003-01-11">
      <postedBy>a@abc.com</postedBy>
      <postSubject>Welcome</postSubject>
      <content>Welcome to the bulletin board</content>
      <followUp postId="2" postDate="2003-01-12">
        <postedBy>b@abc.com</postedBy>
        <postSubject>Posting 2</postSubject>
        <content>This is posting 2</content>
        <followUp postId="7" postDate="2003-01-17">
          <postedBy>c@abc.com</postedBy>
          <postSubject>Posting 7</postSubject>
          <content>This is posting 7</content>
        </followUp>
        <followUp postId="8" postDate="2003-01-18">
          <postedBy>d@abc.com</postedBy>
          <postSubject>Posting 8</postSubject>
          <content>This is posting 8</content>
          <followUp postId="9" postDate="2003-01-19">
            <postedBy>a@abc.com</postedBy>
            <postSubject>Posting 9</postSubject>
            <content>This is posting 9</content>
          </followUp>
        </followUp>
      </followUp>
      <followUp postId="3" postDate="2003-01-13">
        <postedBy>c@abc.com</postedBy>
        <postSubject>Posting 3</postSubject>
        <content>This is posting 3</content>
      </followUp>
    </posting>
    …
  </postings>

The root element is the <postings> element and it contains a repeating group of zero or more <posting> sub-elements. The <posting> element has two attributes: postId, a unique positive integer and postDate of type date. The <posting> element also contains the following sub-elements: <postedBy> of type string, <postSubject> of type string, <content> of type string, and zero or more <followUp> elements. The structure of <followUp> elements is identical to that of <posting>.

Write an XML Schema specification for the postings document and validate several instance documents against the schema.

XQuery

Consider the XML document mo.xml related to the mail order database. Write XQuery expressions to answer the following queries:
1. Get the names of parts that cost less than 20.00.
2. Get the names and cities of employees who have taken orders for parts costing more than 20.00.
3. Get the names of customers who have ordered parts from employees living in Wichita.
4. Get the names of customers who have ordered parts only from employees living in Wichita.
5. Get the names of customers who have ordered all parts costing less than 20.00.
6. Get the names of employees who have never made a sale to a customer living in their own zipcode.
7. Get order numbers of orders that took longer than 2 days to ship.
8. Get the total price of products in order 1022.
9. Get order number and total price for each order.
10. Get employee numbers and total sales for each employee.
Consider the XML document movies.xml related to a movies database. Write XQuery expressions to answer the following queries:
1. Get the title and years of movies in the Crime genre.
2. Get names of persons who have acted in a movie and have directed it as well.
3. Get titles and years of movies in which James Caan has acted.
4. Get the names of performers and the number of movies in which they have acted.
5. Get the names of performers who have acted in at least 10 movies and have directed at least 2 movies.
6. Get the name(s) of the youngest performer(s).
7. Get the names of performers who have directed some actor who is older than them.

XSLT

Consider the XML document movies.xml. Write XSLT programs to display a Web page to browse all movies based on the first letter of the movie title. The top of the Web page should have the letters of the alphabet A through Z hyperlinked and by default all movies that start with the letter A should be displayed below. On clicking the hyperlink for a letter the lower part of the screen should display movies whose titles begin with that letter. Each of the movies in the listing should display the full title with the year in parentheses. They should also be sorted by year in descending order. The title of the movie should be hyperlinked and upon clicking the hyperlink should take the browser to a separate page with all details of the individual movie well formatted.
Consider the XML document described earlier containing bulletin board data (bb.xml). Write an XSLT program to display the contents of the bulletin board along with form elements to post new messages as shown below in the form of a Web page. The “Post Message” hyperlink should point to the start of the post a message block. The messages themselves are hyperlinked to a message detail URL which need not be programmed.