Understanding closure of functional dependencies

Understanding functional dependencies and closure gives you great power in designing or analyzing database schema. If you are new to functional dependencies you can read them up from here.

What is closure?

The closure of a set F of functional dependencies is the set of all functional dependencies logically implied by F.

What can we do by knowing closure?

We can find out several things about the relational database by knowing closure properties. I will list a few of these:

• Finding a key for the relational schema: If we go through the closures of all elements in the functional dependency, the element for which the closure encompasses all the elements of the relation is the database key
• Finding if a relation having composite candidate key is in 2NF (we only consider the composite case because  when a 1NF table has no composite candidate keys (candidate keys consisting of more than one attribute), the table is automatically in 2NF): If we take closure of a part of the key then there must not be any element other than itself in it. This ensures that all elements are dependent on the complete key itself

How to find closure?

I have written a tool for this which you can pick up from github . Also you can read about it here and also go through the example below.

To understand closure let us take an example of a relational database that stores a employee information. Let the fields in this relation be EID, NAME, ROLE, DIVISION,SALARY

For this we can logically see the following functional dependencies:
EID->NAME #every employee has a unique id
ROLE,DIVISION->SALARY #An employee's salary is determined by where he works and his role
EID,NAME->ROLE #Employee can have only one role
EID,NAME->DIVISION #Employee can work for only one division

1. Let us validate using closure of the field EID whether EID is actually the key. Since we start with EID, our closure is initialized with {EID}.
2.  Going through all the functional dependencies we see that attribute NAME depends on EID which is already in our closure set. We can add this and update our closure set to {EID,NAME}
3. Now going through all functional dependencies again we see that we have ROLE dependent on EID and NAME (both already in closure set) and add ROLE to closure set.
4. Traversing again we see SALARY dependent on ROLE and DIVISION but we skip it as DIVISION is not in our set yet.
5. We see that DIVISION depends on EID and NAME and add it to our closure set to make it {EID,NAME,ROLE,DIVISION}
6. Traversing again we see that we can add SALARY now as both ROLE and DIVISION are already in the closure making our closure {EID,NAME,ROLE,DIVISION,SALARY}
7. We end our traversal when we have no more additions to make
8. Thus looking at our closure we see indeed that EID is fit to be the primary key.