By now you should know that DBMS stands for database management system. Functional dependency defines or lays out how attributes in a database management system relate to one another. Functional dependencies are so important that they are central to database design. Just to illustrate a simple example to put this into perspective let us consider a student database management system. Such a database might have fields such as Student Number, Student Name, Faculty, Program and so on. Since Student Number will most probably be a unique identifier or primary key you can retrieve all data related to it just by entering it. Thus, a field like Student Name would be considered to be functionally dependent on the Student Number. Let us discuss this further. There are so many things to discuss regarding database management systems and that is why in this article we will just focus on the types of functional dependency.
Table of Contents
N.B when depicting functional dependencies an arrow is used i.e. →. For instance, Student Number → Student Name means that the Student Name is functionally dependent on the Student Number. Essentially what is on the left side of the arrow and the right side of arrow have terms describing them. What is on the left side is called the determinant whereas what is on the right side is called the dependent. Now it must be clear to you what ‘functionally dependent on’ came from a bit earlier. It is important to note that the determinant is always a primary key. The dependent is always a non-key attribute (from the particular table in question).
Let us use an example to easily make it clear what this type of dependency is. We will use the earlier example of a Student database. Let us fields examples such as Student Number, Student Name, Date of Birth and Faculty. Looking at this example you can see that Student Name, Date of Birth and Faculty are independent of each other. However, they are all dependent on the Student Number. Thus when denoting this multi-valued dependency it would look like this:
Student Number → Student Name
Student Number → Date Of Birth
Student Number → Faculty
This type of dependency is there when a relationship between attributes in indirect. Suppose you have 3 different variables as follows, A, B and C. Let us consider that A → B and B → C. A → C would be what is referred to as a transitive dependency. This scenario only occurs if at least 3 non-key attributes are functionally dependent on a primary key. We will use our earlier example again to illustrate this type of dependency.
Let us consider that the database is programmed in such a way that the Student Number stems from the first letter of the Student Name, the abbreviation part of the Faculty (which could be MNS for Mathematics_and_Natural_Sciences_MNS as an example) and date and month of the Date of Birth field. A Student Number example for Clive Masarakufa born 07/05/1988 would be CMNS0705. This shows you that if any of the Student Name, Date of Birth and Faculty fields are edited the changes will reflect on the Student Number. Yet at the same time all 3 attributes are functionally dependent on the Student Number.
Trivial Functional Dependency
We will yet again use the previous example but with some tweaks so as to drive home what we mean. This scenario comes into play when a primary key is derived from two fields (or columns). This will be also coupled with the fact that one of the fields will be functionally dependent on primary key set (which is a combined set).
Let us suppose that the Student Number is derived from a Student Name’s initials and the date and month of the Date of Birth. From the earlier example that would be CM0705. The primary key set would be [Student_Name, Date_Of_Birth]. This effectively means that the Date of Birth is a subset of the primary key set. It is also clear that if you change the name then the primary key set changes also. In this example, the Student Name is considered to have a trivial functional dependency with the primary key set.
Non Trivial Functional Dependency
This type of functional dependency is just the opposite of the trivial functional dependency. Using our previous example we can illustrate how this looks like. Suppose that the primary key is actually the Student Number on its own. This would mean that primary key would not be a primary key set i.e. it would not be derived from more than one field or column. So, looking at, for instance, the Student Name field would be considered to have a non trivial functional dependency with the primary key which in this case would be the Student Number.
Functional dependencies in database management systems cannot be trivialized. They must be clearly considered to come up with well-organized databases. This will get rid of things like data duplication, and data redundancies, just to mention a few. This ultimately leads to smooth and error-free outputs for processes that entail things like queries or reports. Take note that databases work on the GIGO (garbage in, garbage out) principle. If you do not sort out your functional dependencies well you will get conflicts and even inaccurate outputs from the database management system.