Question

Assume that you have been given the following attributes:

Team_ID, Team_Name, Team_Motto, Emp_ID, Emp_Name, Emp_Address, Emp_Skills, Task_ID, Task_Begin_Date, Task_Completion_Date, Emp_Performance

Assume there are no repeating groups. An employee can be in only one team at a time, but a team can have multiple employees. Emp_ID determines the team in which an employee works. Emp_skills is a multi-valued attribute. An employee can be assigned multiple tasks. Emp_Performance depends on the employee and the task to which they are assigned. Identify all dependencies and covert to 3 NF. First convert the above to 1NF then 2NF and lastly, 3NF. Show all work.

Answer 1

According to the question :-

1. An employee can be in only one team at a time, but a team can have multiple employees
2. Emp_skills is a multi-valued attribute
3. An employee can be assigned multiple tasks
4. Emp_Performance depends on the employee and the task to which they are assigned

The functional dependencies possible are as follows:-

1. Team_ID -> Team_Name, Team_Motto
2. Emp_ID, Team_ID -> Emp_Name, Emp_Address, Emp_Skills
3. Task_ID -> Task_Begin_Date, Task_Completion_Date
4. Emp_ID, Task_ID -> Emp_Performance

From the above functional dependencies the candidate keys are Team_ID, Emp_ID, Task_ID.

For 1NF there should not be any composite and multivalued attributes, clearly the relation is not in 1 NF as Emp_Skills is multivalued attribute therefore the given relation will normalized as follows:-

R (Team_ID, Team_Name, Team_Motto, Emp_ID, Emp_Name, Emp_Address, Task_ID, Task_Begin_Date, Task_Completion_Date, Emp_Performance)

Skills (Emp_ID -> Emp_Skills)

For 2NF there should not be any partial dependencies, clearly there is partial dependencies present in the relation therefore the relation will break into sub relation as follows:-

1. Team (Team_ID, Team_Name, Team_Motto)
2. Employee (Emp_ID, Team_ID, Emp_Name, Emp_Address)
3. Skills (Emp_ID -> Emp_Skills)
4. Task (Task_ID, Task_Begin_Date, Task_Completion_Date)
5. Perform (Emp_ID, Task_ID, Emp_Performance)

For 3 NF there should not be any transitive dependency, clearly the above sub relation is in 3 NF, therefore after normalization the relation schema is derived as follows:-

1. Team (Team_ID, Team_Name, Team_Motto)
2. Employee (Emp_ID, Team_ID, Emp_Name, Emp_Address)
3. Skills (Emp_ID -> Emp_Skills)
4. Task (Task_ID, Task_Begin_Date, Task_Completion_Date)
5. Perform (Emp_ID, Task_ID, Emp_Performance)

In above relation schema primary keys are represented by underline and foreign keys by bold