Homework Information: Some of the problems are probably too long to attempt the night before the due date, so plan accordingly. Late homework will penalized by a factor of 70.71% for each late class period. Feel free to work with others, but the work you hand in should be your own.
Show your work for all parts of all the questions, You may not get credit if you only give the result without explaining how you get the result.
Question 1
For each of the following relations, tell which normal form it is (none, 1NF, 2NF, 3NF, or BCNF) and why. If it is less than 3NF, give an equivalent 3NF schema
A) Users [username, password, first_name, last_name, birth_date]
[username] is the primary key. username → password.
B) Users [username, password, first_name, last_name, birth_date]
[username] is the primary key. username → password, first_name → username
You are told that the following three queries are extremely important:
Finding the total number of projects that employees who worked a given project specified by name.
Listing the project names which has lowest employee per hour rate rate per hour
Computing the cost charged for each employee (Σh=hours worked on projecth.rate_per_hour*h.hours_worked)
A) Describe the indices and file layout that you would use for each relation. Note the queries addressed by each of your index and clustering choices.
B) Suppose that the performance of query 3 was still unsatisfactory. What minimal index would provide an index-only plan for resolving query 3?
C) Using the indices and the file organization from part A, provide a sketch of a query evaluation plan for query 1.
Question 4
For each of the following relation and functional dependencies, find the BCNF decomposition. For each part, show your work by filling the following table.