LOGISTICS

• Turn-in: Please turn in your written work before the beginning of the class due date. 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. All submissions are to be made  electronically via the MOODLE.
• Legibility: Please use a word processor or type your homework assignments. E/R diagrams should be done using a suitable ER diagramming tool or a graphics editor..
• Deadline: The on-time deadline for all students is in class on the due date.
• Late policy: All written work is to be turned in during class on the day that it is due. All assignments turned in late will be penalized at the rate of 10% per day for each unexcused late day.

THE ASSIGNMENT

1. You will be working with the following schema:

   Employee(SSN, name, salary, DNo) 
   Department(DNo, DeptName, MgrSSN) 
   Project(PNo, location, ProjName) 
   HourLog(SSN, PNo, hours) 
   

   The Employee relation provides a list of employees with their SSN, name, salary, and department number (DNo). The SSN is unique for each employee. Each employee belongs to only one department. The Department relation contains a list of the departments for the company. Its schema includes a unique department number called DNo. It also includes the name of the department (DeptName) and the social security number of the department's manager (MgrSSN). Each department has a only one manager. The Project relation includes a unique project number (PNo), location and the project name (ProjName). An employee can be assigned to any number (including zero) projects. Each project has at least one person assigned to it. Finally, the HourLog relation lists for each project the number of hours of work for each employee who is assigned to that project. The key of this relation is SSN and PNo. Write the following queries in SQL.

   a)

   Find the name and the SSN of everyone who works more than 100 hours on one particular project.

   b)

   Find the name and SSN of everyone who works for department number 1 and also work on project number 2.

   c)

   Find the name and the SSN of everyone who works on at least two projects.

   d)

   Find the SSN of everyone who is not working on any project.

2. This problem is based on a book database with four relations:

    BookAuthor(book, author, earnings)
    BookReference(book, referencedBook, times)
    BookReview(book, reviewer, score)
    BookPublish(book, year, publisher, price, num)
   

   In this database, each book may have one or more authors and each author may make a different amount of money from that book. One book may make reference to other books. One book may be reviewed by different reviewers and get different scores. An author could also be a reviewer and a publisher.

   Write the following queries in SQL.

   (a)

   Find all the books published in 1999 and reviewed by both Paul Gray and Daphne Merkin.

   (b)

   Find all the authors who reviewed more than two books written by Charles Dickens. Write in:

   1. SQL with aggregates

   2. SQL without aggregates but with subqueries in the WHERE clause

   3. SQL without aggregates and without subqueries in the WHERE clause

   (c)

   Find all authors who have written exactly one book and reviewed more than one book.

   (d)

   Find all reviewers who have reviewed every book by Stephen King. Write in

   1. SQL with EXCEPT

   2. SQL without EXCEPT

   (e)

   List each author's total earnings in 1995-2000 in descending order.

3. Consider the following relational database schema:

    Student(ID, name, dept, status) // status = "grad" or "undergrad"
    // ID is a key
    RA(ID, advisor, dept) // (ID,advisor) together are a key
    TA(ID, course, dept) // (ID,course) together are a key
   

   Write the following in SQL:

   a)

   Find the names of all graduate students who are neither an RA nor a TA.

   b)

   Find the names of all graduate students who are an RA or a TA in a department other than their own.

4. Consider a relation Flight(from,to,cost,airline) containing nonstop flights from one city to another. Note that the flights from city A to city B are independent of the flights from B to A. For example:

   from	to	cost	airline
   SF	Denver	300	Frontier
   SF	Denver	350	United
   Denver	SF	250	United
   Denver	SF	250	Frontier
   Denver	Chicago	250	American
   Chicago	NY	250	Delta
   Denver	NY	500	American
   Denver	NY	400	TWA
   SF	NY	750	United

   (a) Give a single SQL query that returns the cost of the cheapest nonstop flight between each pair of cities. For example, the result over the above relation instance should be:

   from	to	cost
   SF	Denver	300
   Denver	SF	250
   Denver	Chicago	250
   Chicago	NY	250
   Denver	NY	400
   SF	NY	750

   (b) Give a single SQL query that returns the cheapest cost of flying between each pair of cities assuming we are willing to stop up to two times en-route. For example, by stopping once (in Denver), we can get from SF to NY for 700 instead of 750. With this example data, we could stop twice (in Denver and Chicago), but that would be more expensive ($300+$250+$250 = $800).

   (c) Is it possible to write a single SQL query that returns the cheapest cost of flying between each pair of cities regardless of the number of stops? If so, give the query. If not, briefly explain why.