Search code examples
dependenciesfunctional-dependencies

Find all multivalued dependencies of a relation

Could anyone explain how to find all the multivalued dependencies of the following relation?

enter image description here

enter image description here

I understand that any FD is also an MVD so by that definition the given FD is an MVD. However, I'm lost at how to find all the other MVDs.

Solution

  • First, remove "office" from "employees" by projection. This eliminates a partial key dependency in "employees".

    • employees (name, project, task)
    • emp_offices (name, office)

    Now "employees" is in BCNF, and emp_offices is in at least 5NF.

    Next, "name"->>"task" is a nontrivial multivalued dependency, and "name" is not a superkey in "employees". Remove "task" from "employees" by projection.

    • employees (name, project)
    • emp_offices (name, office)
    • emp_tasks (name, task)

    All three of those are in at least 5NF.

    I'd change the name of the "employees" relation, because it no longer has to do with employees.

    • emp_projects (name, project)
    • emp_offices (name, office)
    • emp_tasks (name, task)

    If you need an algorithm, the search term is "4nf algorithm". What you see here is essentially an application of the 4NF algorithm here.