CSE 305 Midterm Examination

Monday, October 27, 2003, 5:20 - 6:40 pm (80 minutes)

Total Points: 100

• Open books and open class notes. No other printed material allowed.

• Violations of honor code will be dealt with strictly.

1. (0 points) Identity. Please print your name and student ID.
   1. Name: _______________________________.
   2. ID: _______________________________.


































2. (10 points) E/R Diagrams. We assume that you are all familiar with courses, students, and semesters. We give two E/R diagrams intended to describe a database telling which students take which courses in which semesters. We ask you to discuss the differences between the diagrams in practical terms, e.g., "a student can only take one course at a time," rather than in abstract terms such as "there is a many-one relationship from students to courses."

   

   1. Give one limitation of diagram II which is not present in diagram I.
   
   
   
   
   
   
   
   
   
   
   
   2. Suppose we were to modify diagram I by adding an arrow on the line to Semesters. State the constraint (in practical terms) implied by the added arrow.
   
   
   
   
   
   
   
   
   
   
   
   
   
3. (30 points) Functional Dependencies. Consider a relation R(A,B,C,D,E) with the following functional dependencies: A->B, B->C, BC->A, A->D, E->A, D->E.
1. Find all keys (i.e., minimal superkeys).







2. Which FDs are BCNF violations?







3. Which FDs are 3NF violations?







4. Using the first (in the order given in the question) FD that violates BCNF, decompose the relation R into two relations. You do not need to decompose the relations beyond the first step of decomposition.













4. (15 points) Relational Algebra. Show how to implement the full outerjoin operator with the help of left-outerjoin and union operators. In other words, write an equivalent expression for (R FULL OUTERJOIN S) using only the left-outerjoin and union operators. For full credit, you may not use any other operator. To get partial credit, you may use right-outerjoin.














5. (20 points) Relational Algebra. Consider a relation Emps(empID, ssNo, name, mgrID) giving for a set of employees their employee ID (assumed unique), their social security number (also unique), the name of the employee (not necessarily unique), and the employee ID of the manager of the employee. Assume that the president is his/her own manager, so each employee has a unique manager. You may assume that there are no duplicate tuples in the relation. Write relational expressions for the following.
   1. Find the ID's of the employees that are managed by people who are managed by the employee with ID 123.
   
   
   
   
   
   
   
   
   
   2. Find the ID's of all employees who do not manage any employee named "Sally."
   
   
   
   
   
   
   
   
   
6. (25 points) SQL Queries Consider the following relational schema:
   • Teams (name, country, fanLevel)
   • Players (name, country, teamName, salary)
   • Goals (playerName, numGoals, date)
     

   Write the following queries in SQL.

   1. Find all foreign players (one whose own country differs from the country of his/her team).
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   2. Find all players who have a salary that's at least 20% greater than the salary of any other player playing for the same team.
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   3. Find all players who have scored more than 30% percent of the total number of goals scored by all players of his team.