Answer:
Check the explanation
Explanation:
1. The atomic attributes can't be a primary key because the values in the respective attributes should be unique.
So, the size of the primary key should be more than one.
In order to find the candidate key, let the functional dependencies be obtained.
The functional dependencies are :
Emp_ID -> Name, DeptID, Marketing, Salary
Name -> Emp_ID
DeptID -> Emp_ID
Marketing -> Emp_ID
Course_ID -> Course Name
Course_Name -> Course_ID
Date_Completed -> Course_Name
Closure of attribute { Emp_ID, Date_Completed } is { Emp_ID, Date_Completed , Name, DeptID, Marketing, Salary, Course_Name, Course_ID}
Closure of attribute { Name , Date_Completed } is { Name, Date_Completed , Emp_ID , DeptID, Marketing, Salary, Course_Name, Course_ID}
Closure of attribute { DeptID, Date_Completed } is { DeptID, Date_Completed , Emp_ID,, Name, , Marketing, Salary, Course_Name, Course_ID}
Closure of attribute { Marketing, Date_Completed } is { Marketing, Date_Completed , Emp_ID,, Name, DeptID , Salary, Course_Name, Course_ID}.
So, the candidate keys are :
{ Emp_ID, Date_Completed }
{ Name , Date_Completed }
{ DeptID, Date_Completed }
{ Marketing, Date_Completed }
Only one candidate key can be a primary key.
So, the primary key chosen be { Emp_ID, Date_Completed }..
2.
The functional dependencies are :
Emp_ID -> Name, DeptID, Marketing, Salary
Name -> Emp_ID
DeptID -> Emp_ID
Marketing -> Emp_ID
Course_ID -> Course Name
Course_Name -> Course_ID
Date_Completed -> Course_Name
3.
For a relation to be in 2NF, there should be no partial dependencies in the set of functional dependencies.
The first F.D. is
Emp_ID -> Name, DeptID, Marketing, Salary
Here, Emp_ID -> Salary ( decomposition rule ). So, a prime key determining a non-prime key is a partial dependency.
So, a separate table should be made for Emp_ID -> Salary.
The tables are R1(Emp_ID, Name, DeptID, Marketing, Course_ID, Course_Name, Date_Completed)
and R2( Emp_ID , Salary)
The following dependencies violate partial dependency as a prime attribute -> prime attribute :
Name -> Emp_ID
DeptID -> Emp_ID
Marketing -> Emp_ID
The following dependencies violate partial dependency as a non-prime attribute -> non-prime attribute :
Course_ID -> Course Name
Course_Name -> Course_ID
So, no separate tables should be made.
The functional dependency Date_Completed -> Course_Name has a partial dependency as a prime attribute determines a non-prime attribute.
So, a separate table is made.
The final relational schemas that follows 2NF are :
R1(Emp_ID, Name, DeptID, Marketing, Course_ID, Course_Name, Date_Completed)
R2( Emp_ID , Salary)
R3 (Date_Completed, Course_Name, Course_ID)
For a relation to be in 3NF, the functional dependencies should not have any transitive dependencies.
The functional dependencies in R1(Emp_ID, Name, DeptID, Marketing, Date_Completed) is :
Emp_ID -> Name, DeptID, Marketing
This violates the transitive property. So, no table is created.
The functional dependencies in R2 ( Emp_ID , Salary) is :
Emp_ID -> Salary
The functional dependencies in R3 (Date_Completed, Course_Name, Course_ID) are :
Date_Completed -> Course_Name
Course_Name -> Course_ID
Here there is a transitive dependency as a non- prime attribute ( Course_Name ) is determining a non-attribute ( Course_ID ).
So, a separate table is made with the concerned attributes.
The relational schemas which support 3NF re :
R1(Emp_ID, Name, DeptID, Course_ID, Marketing, Date_Completed) with candidate key as Emp_ID.
R2 ( Emp_ID , Salary) with candidate key Emp_ID.
R3 (Date_Completed, Course_Name ) with candidate key Date_Completed.
R4 ( Course_Name, Course_ID ). with candidate keys Course_Name and Course_ID.
