Functional Dependency in DBMS
“What is your identity now? You can only be identified in your neighborhood or in your relatives when you are told as to your parent’s offspring (not considering any of your remarkable achievements). So, you are dependent on your parents to get identified.” Now, this is an analogy to the topic we are going to learn, Functional dependency.
Functional dependency:
From Table 1, we can deduce that the name Tharun has repeated twice. So, how do we identify the difference when the person is not physically in front of you? Well, with Student_ID it is possible. So, the attribute Student_Name can only be identified with Student_ID, which is our primary key.
Hence, Student_ID → Student_Name read as Student_Name is functionally dependent on Student_ID.
Here, Student_ID is determinant and Student_Name is Dependent.
For the definition, the attributes of a table are said to be functionally dependent on each other when one attribute can uniquely identify another attribute in the same table.
FD: A → B
A- Determinant
B- Dependent
If we look at Table 1, there is an entry with duplicate values (we are not concerned about redundancy here). Let’s say (a,b) be the tuple representing (Student_ID, Student_Name) of the table.
t1= (1, Anirudh) t2= (2, Nikhil) and so on where tn is any tuple.
If t1.a = t2.a then
t1.b = t2.b (must condition). Eqn 1
In our case, the first and last entries are the same. But both the values are the same. Hence we can say that A and B are functionally dependent.
If Eqn 1 doesn’t satisfy, then A and B are not functionally dependent.
Note: Functional dependencies can be between multiple attributes of the same table.
Types of Functional Dependencies:
Trivial:
FD: A → B
A and B are said to have trivial functional dependency if and only if B is a subset or equal to that of A. Trivial functional dependency is always valid independent of the values in the table.
Ex: From Table 2, (Student_ID,Student_Name) →Student_Name. Here the tuple (Student_ID,Student_Name) is a (in (a,b)) as multiple attributes can also be in functional dependency and b is Student_Name. If you observe b is a subset of a i.e. Student_Name is already present in the tuple of a.
Explanation of the above example:
If we call out Student_ID and Student _Name then obviously the Student_Name can be told. This is a trivial functional dependency.
Non-Trivial:
FD: A → B
A and B are said to have non-trivial functional dependency if and only if A intersection B is an empty set. Non-trivial functional dependency may be valid or not with respect to the values in the table. Hence, we cannot assure that the non-trivial functional dependency is always valid.
A ∩ B = Φ.
Ex: Student_ID → Student_Name. It has a non-trivial functional dependency as there is no common attribute between the two. And the functional dependency is valid.
Semi-Trivial:
FD: A→ B
A and B are said to have semi-trivial functional dependency if
A ∩ B ≠ Φ and A ⊄ B.
Ex. (Student_ID, Student_Name) → (Student_Name, Stream). The intersection is not an empty set (contains Student_Name) and tuple b is not a subset of tuple a. Hence it is semi-trivial.
Single valued Functional Dependency:
FD: A → B
The relation is said to be a single-valued functional dependency if and only if there exists only one value of B (dependent) for a particular value of A (determinant).
Ex: From Table 3, Student_ID → Student_Name.
For a particular value of Student_ID, there is only one Student_Name. For Student_ID = 1, Student_Name is Anirudh. For Student_ID = 3, Student_Name is Tharun.
Multivalued Dependency:
FD: A ↠ B
A and B are said to be having multivalued dependency if and only if there exist multiple values of B for a single value of A.
From Table 4,
(1, Anirudh) ↠ Dancing; (1, Anirudh) ↠ Reading which can also be represented as (Student_ID, Student_Name) ↠ Hobbies. For the same value of tuple a, there are multiple values of b. Simply, the same person has multiple hobbies.
Hence (Student_ID, Student_Name) ↠ Hobbies is a multivalued dependency. This is read as (Student_ID, Student_Name) multidetermined Hobbies.
Fully Functional Dependency:
FD: ABC → D.
D is said to be fully functional dependent if and only if any proper subset of ABC (determinant) doesn’t determine D (dependent).
Ex. From Table 5, (Student_ID, Course) → Marks.
Marks is fully functionally dependent on Student_ID and Course. Only Student_ID or Course (a subset of determinant) cannot determine marks of the student.
Partial Functional Dependency:
FD: ABC → D
Contrary to fully functional dependency, if any subset or ABC (determinant) can determine D (dependent) then D is said to be partially functionally dependent on ABC.
From the Table 5, (Student_ID, Course) → Student_Name. Student_Name can also be determined with only Student_ID and Course is not necessary. Hence, Student_Name is said to be partially functionally dependent on (Student_ID, Course).
Transitive Functional dependency:
FD: A → B, B→ C then A → C.
If B is functionally dependent on A and C is functionally dependent on B then C is transitively functionally dependent on A.
From Table 6, Student_ID → Semester; Semester → Hostel. The semester can only be told if we have the Student_ID and the hostel can be determined only if we knew the semester. The final result comes out as we can determine the hostel only if we know the Student_ID.
Hence, the Hostel is transitively functionally dependent on Student_ID.
Non-Transitive Functional Dependency:
Converse to transitive functional dependency, the relation is said to be a non-transitive dependency if there exists no transitive relation between the attributes.
From Table 6, Student_Name is not transitively dependent on Student_ID.
These are all the functional dependencies in Database Management. If we miss out on something or any errors, please free to contact us.
Visit our blog for more articles: Techprodezza
