Deser peion Category, Price)) BITS wants to store information about the supervis
ID: 3699286 • Letter: D
Question
Deser peion Category, Price)) BITS wants to store information about the supervisors, including their supervisor number and to con In addition, supervisors specialize in wo convert the following unnormalized relation to fourth normal form: 3. the relationship sultants. Supervisors can work with multiple consultants, but consultants only hav rking with clients in specific task categories. Using this information, Consultant (ConsltNum, LastName, FirstName, Street, city, zipCode, sorNum, SupervisorName) , (Tasks, Description, category, Price, SupervisorNum) )Explanation / Answer
Answer is as follows :
We have the given data in Un- normalized form, So for converting it to 4NF first we convert it to 1NF, So the process is as follows :
Convert UNF to 1NF : Distribute the data among different table to remove the un normalization.
Consultant(ConsltNum, LastName, FirstName, Street, ZipCode, Hours, Rate)
Supervision(ConsltNum, SupervisorNum, SupervisorName, Tasks, Description, Category, Price)
Convert 1NF to 2NF : Now remove all the partial dependencies and make all the attributes fully functional dependent on Key Attribute.So we get this as follows :
Consultant(ConsltNum, LastName, FirstName, Street, ZipCode, Hours, Rate)
ConsultSuper(ConsltNum,SupervisorNum#,Tasks)
Supervision(SupervisorNum, SupervisorName, Description, Category, Price)
Convert 2NF to 3NF : Remove all the transitive dependecies from non prime attributes.
Consultant(ConsltNum, LastName, FirstName, Street, ZipCode, Hours, Rate)
ConsultSuper(ConsltNum,SupervisorNum#,Tasks)
Supervisor(SupervisorNum, SupervisorName)
Supervision(SupervisorNum, Description, Category, Price)
Convert 3NF to BCNF : In this every non prime attribute must dependend on Candidate key i.e. also happens in 3NF. So the relation in 3NF is also in BCNF.
Convert BCNF to 4NF : We will remove all multivalued dependencies.
So in this
ConsultSuper(ConsltNum,SupervisorNum#,Tasks)
Task can be of multiple type but can be same among different Supervisors. So it can be removed as
ConsultSuper(ConsltNum,SupervisorNum#)
SuperTask(SupervisorNum,Tasks)
And the full relation in 4NF is described as :
Consultant(ConsltNum, LastName, FirstName, Street, ZipCode, Hours, Rate)
ConsultSuper(ConsltNum,SupervisorNum#)
SuperTask(SupervisorNum,Tasks)
Supervisor(SupervisorNum, SupervisorName)
Supervision(SupervisorNum, Description, Category, Price)
In all the relation Underlined + Bold attributes are treated as primary key.
In all the relation Underlined + Bold + # attributes are treated as foreign key.
if there is any query please ask in comments....