CS 33 (Spring 2011): Homework 4: Relational Calculus, QBE, XML

Deadline: 10:00pm sharp on April 26, 2011

Please read through this page entirely and carefully before beginning your work.

A Database and Some Queries

Consider the following simple database of information about certain employees and companies for which they work.

employee(ename, street, city) — records residential addresses
company(name, city) — records business location
works(ename, name, salary) — employment & salary info; name = company name
manages(ename, mname) — org chart info; mname = manager name

With respect to this database, consider the following queries.

  1. Find the names of all employees who work for "Adobe".

  2. Find the names and cities of residence of all employees who work for "Adobe".

  3. Find the names, street addresses, and cities of residence of all employees who work for "Adobe" and earn more than $70000.

  4. Find the names of all employees who live in the same city as that in which the company for which they work is located.

  5. Find the names of all employees who live in the same city and on the same street as their managers.

  6. Find the names and employers (i.e., company names) of all employees in the database who do not work for "FedEx".

  7. Find the names and salaries of all employees who earn more than every employee of "FedEx".

  8. Assume that the companies may be located in several cities (this means city will have to become part of the primary key of the relation company). Find all companies located in every city in which "Adobe" is located.

Part 1: Relational Calculus

  1. Give domain relational calculus expressions to answer each of the queries listed above.

    [8 * 3 = 24 points]

    Note that some printings of the textbook have severe typos in the discussion of relational calculus. Please consult the errata on the textbook's website as you read that particular chapter.

Part 2: Query By Example

  1. Give a QBE solution for each of the queries listed above.

    [8 * 3 = 24 points]

    If you will be using LaTeX to produce your PDF for this part and you need help with it, please study the example in ~cs33/data/hw1_example.tex to see how it's done. As always, you don't have to use LaTeX, and you always have the option of scanning a handwritten solution.

Part 3: XML and XQuery

  1. The file ~cs33/data/emp.xml has one particular XML representation of the data in the same database. Produce a suitable DTD for this XML document.
    [4 points]

  2. Give XQuery expressions for queries (b) through (h) above. In each case, your expression should answer the query by generating HTML output that is nicely formatted to make a pleasing display in a modern web browser. Thus, you may want to use <ul>, <li>, <table>, etc.

    [7 * 4 = 28 points]

    3. To help you get started, the file ~cs33/data/hw4_prob4a.xq contains a solution for item (a) above. To run the query, use the following commands at the Unix prompt. 

          cd ~cs33/data

      PATH=$PATH:~cs33/bin       (change this appropriately if using csh)

      qizx -i emp.xml hw4_prob4a.xq

What to turn in