# Final exam -- open book section

The entire exam is to be turned in by 3:45 PM. You must turn in the closed book section of the final before you consult your textbook or notes.

## Question 1. (8 points)

Suppose the following SQL SELECT statement will list the names, department, and salaries of all company employees

```SELECT Staff.Name, Staff.Dept, Staff.Salary
FROM EmployeeTable ;
```

Now write two SQL statements to

• list each company department along with the highest salary in that department, but only if the department has more than two employees
• list all employees who work for more than one department

## Problem 2 (2 points)

Give an example of a functional dependency that is not a full function dependency.

## Problem 3 (3 points)

Which would you expect to generate the most tuples: joining two relations or unioning two relations.

## Question 4. (6 points)

Give examples of the following

• one-to-one relation
• many-to-one relation
• many-to-many relation

## Question 5. (1 point)

What CASE tool was used in the ER-diagram homework?

## Question 6. (3 points)

Briefly explan what the following two C calls accomplish

• fseek( ... )

## Question 7. (12 points)

Suppose you have a relation with six attributes, A, B, C, D, E, and F, and the following dependencies:

• A, B -> C, D, E, F
• A -> D, E, F
• D -> E
• E -> D

Transform this relation into a set of relations that are in Boyce-Codd Normal Form (BCNF).