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. Consider a relation R(A,B,C,D) with multivalued dependency AC->->D and functional dependency B->A.

   (a)

   Find all 4NF violations. For any that you find, explain why each is a violation, or explain why none are violations.

   (b)

   Decompose the relations into a collection of relation schemas in 4NF.

   (c)

   Consider the original relation R(A,B,C,D) with multivalued dependency AC ->->D and functional dependency B->A. Which of the following hold? For each, give reasons why it holds or at least one counterexample

   i.

   B ->-> CD

   ii.

   A ->-> D

   iii.

   AC -> D

2. This problem is based on the relations:

    Customers(custID, name, email, shipAddr)
    Orders(orderID, custID, itemID, date, status)
    Items(itemID, description)
   

   Write in relational algebra the following queries. You may write a sequence of steps with named temporary relations if you like.

   (a)

   Find the email of the customer(s) with name "Laura Lee."

   (b)

   Find the names of the customers whose orders were placed on Jan. 1, 2000, and whose order status is "lost."

   (c)

   Find the descriptions of the items ordered by "Laura Lee."

   (d)

   Find the names of the customers who have two or more orders with status "pending."

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 relational algebra:

   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. 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 Relational Algebra. You may use assignment of intermediate results for long queries.

   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.

   e)

   (Extra Credit) Find the name and the SSN of everyone who works on every project.

   f)

   (Extra Credit) Find employees and their managers who work the fewest hours for each project. A tuple of (EmployeeSSN, EmployeeName, ManagerName, PNo) should be listed for every project.