Introduction

The following is a list of functional and multi-valued dependencies for a company:

Employee_ID -> Supervisor_ID
Supervisor_ID ↠ Employee_ID
Employee_ID ↠ Child
Employee_ID ↠ {Salary, Year}
Employee_ID -> Spouse
{Employee_ID, Year} -> Salary
Year ↠ Salary
Employee_ID -> {Name, Address}

How can these dependencies help us design a schema?

First, extract the functional dependencies based upon a single attribute and see what they tell us:

Employee_ID -> Supervisor_ID
Employee_ID -> Spouse
Employee_ID -> {Name, Address}

These three dependencies imply that Employee_ID could be the primary key in a table that also contains Spouse, Name, Address, and Supervisor_ID, since these attributes depend only upon a single Employee_ID. The {Name, Address} group can be split into two separate attributes since neither appear anywhere else in the dependencies:

Now, extract the multi-valued dependencies based upon a single attribute:

Supervisor_ID ↠ Employee_ID
Employee_ID ↠ Child
Employee_ID ↠ {Salary, Year}
Year ↠ Salary

The dependency Employee_ID ↠ Child implies that one employee may have many children. However, there is no dependency from Child to any other attribute. This leaves us with a table that relates only Employee_ID and Child. Since an Employee_ID may repeat, the tables primary key cannot consist only of the Employee_ID. Since there is only one other attribute in the table, i.e. Child, the primary key must consist of Employee_ID and Child:

The dependencies Employee_ID ↠ {Salary, Year} and Year ↠ Salary implies a

Here is one possible normalization of these dependencies:

Here is some simple sample data that shows these dependencies:

Syntax

Examples