Given the following relational schema where each attribute is atomic:
EMPLOYEE (id, name, age, classid, gender, manager, salary)
and a set of functional dependencies:
SetOfFDs = {(classid, id, gender) -> (salary, manager),
name -> (age, id),
id -> name,
manager -> (gender, age, classid, id)}
a) Find ALL candidate keys for the schema EMPLOYEE; show your work.
b) For each of the normal forms (1NF, 2NF, 3NF, BCNF), explain in detail why EMPLOYEE satisfies/does not satisfy with respect to the set of functional dependencies SetOfFDs.
c) Use the decomposition algorithm to obtain the lowest normal form that the schema EMPLOYEE does not satisfy with respect to SetOfFDs (for example, if your answer for question (b) is that EMPLOYEE satisfies 1NF and 2NF, but does not satisfy 3NF and BCNF, then use the decomposition algorithm to decompose EMPLOYEE into the schemas each of which satisfies 3NF); show your work. For each resulting schema, give its complete set of functional dependencies and candidate keys. You must explain why each of your resulting schemas is in the desired normal form.
Individual Question:
Each group member will be assigned one of the following questions randomly after the group portion of the homework is due (see the submission instructions at the end of the document). You must be ready to answer any of these questions on your own to submit the individual portion of the homework:
d) Explain in detail why your decomposition obtained in part (c) is/is not loss-less join.
e) Explain in detail why your decomposition obtained in part (c) is/is not dependency-preserving.