SQL Transactions Exercises

Consider the universal relation

WORKER(ID, First, Last, Team, Dept, Salary)

with the following set F of functional dependencies:

ID -->First

ID --> Last

First, Last --> ID

Last --> Team

ID --> Dept

ID --> Salary

Salary --> Dept

Identify candidate keys of WORKER.

Construct a decomposition of WORKER into relations in 3NF that preserves dependencies. How can you be sure that your decomposition is dependency-preserving?

Are all of the relations in decomposition in BCNF? Either explain why they are, or identify one that is not and explain why it is not. (Note that for a relation to be in BCNF, the determinants of all functional dependencies in the relation must be superkeys of that relation not superkeys of the original universal relation.)