The following question is based on Employees relation: Employees(Name, Project,
ID: 3572087 • Letter: T
Question
The following question is based on Employees relation:
Employees(Name, Project, Task, Office)
Name -> Office
An employee can work on different tasks and different projects. NOTE that Tasks are independent of Projects.
Office
400
400
400
400
442
442
442
588
What non-trivial multi-value dependencies hold on this relation? Is this relation in BCNF? Is it in 4NF?
Name Project TaskOffice
Bill 100X T1400
Bill 100X T2400
Bill 200Y T1400
Bill 200Y T2400
Sue 100X T33442
Sue 200Y T33442
Sue 300Z T33442
Ed 100X T2588
Explanation / Answer
The table is in BCNF only, not 4NF. There are multilple dependies that can be seen in this case.For ex, the Office is always determined by Name. So essentially the presence of Project and Task provide no useful information to constrain the possible values of Office.
So for 4NF we have the condition that for every one of its non-trivial multivalued dependencies X -> Y, X should be a superkey( X is either a candidate key or a superset).
Or Simply saying a table is in 4NF if:-
So inorder to make it 4NF we should do something like:-
Table1 Name Project Task Bill 100x T1 Bill 100x T2 Bill 200y T1 Bill 200y T2 Sue 100x T33 Sue 200y T33 Sue 300z T33 Ed 100x T2Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.