LOGISTICS

• Turn-in: Please turn in your written work at the beginning of class. Please put your name and preferred email address on every page that you turn in - you'd be surprised how many times assignments mysteriously come apart. Please remember that written work will not be accepted electronically.
• Legibility: Please use a word processor or type your homework assignments. E/R diagrams should be drawn with a suitable er diagramming tool or graphics editor.
• Deadline: The on-time deadline for all students is in class on the due date.

THE ASSIGNMENT

1. Suppose we have the following relations or predicates:

    Employee(id, name, salary)
    Manages(emp_id, mgr_id)
    Department(id, deptNo)
   

   The interpretations should be obvious. Employees have ID's (key), name and salary. Manages has a listing of the immediate manager for each employee (emp_id is the key). Department has a listing of id (key) and the corresponding deptNo. Each employee has one direct manager. In addition, all the mangers of an employee's manager also serve as a manger for that employee. Write the following queries in Datalog. You should use only safe rules, but you may wish to use several IDB predicates corresponding to subexpressions of complicated expressions.

   a)

   Find the ID's of all employees who work for department 10.

   b)

   Find the names and the salaries of all employees who make more money than their immediate managers.

   c)

   Find the name and the ID of the CEO of the company (the only person who doesn't have a manager).

   d)

   Write a Datalog program to produce triples consisting of an employee ID, the name of one of the managers of that employee, and the manager's ID. Remember that managers of managers are managers, and so on.

   e)

   Write the query of (d) as a recursive SQL query.

2. The following problems are based on the following "auction" database:

    Bids(auctionID, bidder, price, quantity)
    Auctions(auctionID, seller, item, quantity, expires)
    Ratings(seller, stars)
   

   where you may assume that in Bids, a bidder may place several bids for one auction, but all bids by one bidder will have different prices.

   Write the following queries in Datalog. You may wish to define several "helper" IDB predicates.

   a)

   Find all the bidders on auctions for item "Beanie Baby."

   b)

   Find all the sellers who have both 1-star and 5-star ratings.

   c)

   Find the highest price bid for any auction where the item is "Beanie Baby."