Functional Dependencies in DBMS
Today I watched a video lecture on DBMS Functional Dependencies. According to this video, as students study DBMS, students make a typical error, as this is skipped and Normalisation is achieved. However, in order to explain Normalisation, the lecturer suggested that Functional Dependencies are very needed.
Definition: The attributes of a table is said to be dependent on each other when an attribute of a table uniquely identifies another attribute of the same table.
Let’s now elaborate on this definition with an example.
If there is an entity set, then to make a valid functional dependency (α → β), there should exist an attribute (α) in which each value has a single value in another attribute(β) and it should be the same for all occurrences. A value in α can occur multiple times but for all the occurrences there should be the same value in β. A value in β can be the same for multiple values for in α.
Note: α is called determinant and β is called dependent.
Functional dependence does not mean that the value of the associated β can be calculated for α. Instead, it means that we should check the related β for a value of α.
Let’s understand the above scenario with an illustration:
This is a valid functional dependency as for every value of α there is a unique value in β.
This is also a valid functional dependency. Although there are multiple occurrences of ‘a’ but for every occurrence of ‘a’ in α, there is the same value in β.
This is also a valid functional dependency. Although both ‘a’ and ‘d’ in α have the same value in the corresponding β but there can be multiple values in α which have the same value in β.
This is not a valid functional dependency. Because ‘a’ in α has two values in β. A value in α should have the same value in β at all occurrences.
Types of Functional Dependency
There are two types of functional dependency:
Trivial Functional Dependency
In this type of functional dependency, the dependent is a subset of determinant, i.e. β ⊆ α. In this functional dependency, we don’t get new information in β when we search for α.
Non-trivial Functional Dependency
When the dependent is not the subset of determinant, then it is said to be that there exists a non-functional dependency, i.e. β ⊄ α. In this functional dependency, we get new information in β when we search for α.
Let’s follow an outline in real life to grasp the whole definition of functional dependency.
Suppose, a school student went to the library and inquired about his dues. The librarian asked that student for some information such as his name, his father’s name, his class, etc.
The librarian found the data after scanning the directory. The student has now supplied the librarian with personal information that is the determinant and the data which the librarian found in the directory (and which he is going to notify the student) is the dependent.
The librarian told the student his name (maybe the librarian was of little unsound mind). In this situation, no new material was provided to the student. This is the case of trivial dependency.
But, if the librarian was of normal mind, he would have told the student about the dues amount. In this case, the student received some new information, i.e. the amount of his dues. This is a non-trivial functional dependency.
Now, it is possible that two different students can have the same dues amount, i.e. same dependency for two different determinants (case 3). But it is not possible for the same student to have two different dues amount, i.e. two different dependencies for the same determinant (case 4).
