Types of Functional Dependency : Trivial, Non-trivial, Multi-valued and Transitive Dependency

There are Four Types of Functional Dependency in Relational database :

  • Trivial functional dependency
  • Non-trivial functional dependency
  • Multi-valued dependency
  • Transitive dependency

1. Trivial functional dependency : 

A trivial functional dependency is a functional dependency of an attribute on a super-set of itself. In other words, the dependency of an attribute on a set of attributes is known as trivial functional dependency if the set of attributes includes that attribute. 

If R is a relation with attributes A and B, then  A -> B is trivial functional dependency if B is a subset of A. The following dependencies are also trivial: A -> A & B -> B

For example consider a table 'student' with two columns student_id and student_name. 

                {student_id, student_name} -> student_id 

is a trivial functional dependency as student_id is a subset of {student_id, student_name}. That makes sense because if we know the values of student_id and student_name then the value of student_id can be uniquely determined. And also, 

                 student_id -> student_id & student_name -> student_name 

are trivial dependencies too.

2. Non-trivial functional dependency : 

If a functional dependency X->Y holds true where Y is not a subset of X then this dependency is called non trivial Functional dependency. For example an employee table with three attributes: employee_id, employee_name, employee_address. Then the following functional dependencies are non-trivial dependencies : 

employee_id -> employee_name  (because employee_name is not a subset of employee_id) 
employee_id -> employee_address (because employee_address is not a subset of employee_id)

On the other hand, the following dependencies are trivial:

{ employee_id, employee_name } -> employee_name  

At above employee_name is a subset of {employee_id,employee_name}. 

Completely non trivial FD : If a FD X->Y holds true where X intersection Y is null then this dependency is said to be completely non trivial function dependency.

3. Multi-valued dependency : 

A Multi-Value Dependency occurs when two or more independent multi valued facts about the same attribute occur within the same table.  It means that if in a relation R having A, B and C as attributes, B and C are multi-value facts about A, which is represented as A ->> B and A ->> C. Multi value dependency exist only if B and C are independent on each other. For example consider a bike manufacture company, which produces two colors Black and White, in each model every year.

Here columns MFG_year and Color are independent of each other and dependent on Bike_model. In this case these two columns are said to be multi-valued dependent on Bike_model. These dependencies can be represented as

           Bike_model ->> MFG_year
           Bike_model ->> Color 

4. Transitive dependency

A functional dependency is said to be transitive if it is indirectly formed by two functional dependencies. For example X -> Z is a transitive dependency if the following three functional dependencies hold true:
  • X -> Y
  • Y does not -> X
  • Y -> Z
A transitive dependency can only occur in a relation of three of more attributes. Now let’s take an example to understand it better, consider the below table 'books' :

  • {Book} -> {Author} (if we know the book, we knows the author name)
  • {Author} does not -> {Book}
  • {Author} -> {Author_age}
 Therefore as per the rule of transitive dependency: {Book} -> {Author_age} should hold, that makes sense because if we know the book name we can know the author’s age.

Next Topic :  Join Dependency in Relational Database

No comments:

Post a Comment