Partial, Full, and Transitive Dependencies

Functional Dependency is a key feature of a Database management System. Functional Dependency is used to maintain the relationship between various attributes in a given database.

What is Functional Dependency?

Functional dependency states the relationship between two sets of attributes where a value of a set of attributes is dependent on the other set of attributes.

It is a relationship that typically exists between two attributes such that with the help of one attribute we can get the values of another attribute. The attribute that is used for finding the values of other attributes is called the primary key attribute.

Why is Functional Dependency Used?

• To maintain Data Integrity
• Easy to maintain
• Efficient Data Storage
• Improved Data Redundancy

Example

We have a table with student details such as roll number, name, and city.

roll_no	name	city
1	Yash	Delhi
2	Kartik	Mumbai
3	Aditya	Delhi
4	Kartik	Pune

• Here roll_no is only unique attribute. So the primary key for the given table will be roll_no. Other attributes such as name and city are dependent on the roll_no i.e. on the basis of roll_no we can get student’s name and its city.But we can not get roll_no of student based on it’s name or city as it will create ambiguity.
• For example, if we take name as kartik there will be 2 records with the name kartik which will result into ambiguity. Also if we take city as Delhi there will be 2 records i.e. roll_no 1 and roll_no 3.
• So here we can say that name and city are functionally dependent on roll_no.

Types of Dependencies

• Partial Dependency
• Full Dependency
• Transitive Dependency

Partial Dependency

• If the value of a non-primary attribute can be defined using part of the primary key then it is called a partial dependency. Partial dependency occurs when primary key is formed using more than one attribute.This type of key also called as composite key.
• In below given example, the primary key is formed using roll_no + sub_id which can also be called as composite key.When composite key is present and one of the non-primary attribute can is dependent on part of the primary key instead of whole primary key then it is called as Partial Dependency.

Example

Let’s take an example, we have a table where we have columns of student roll number, subject ID, sub name, and marks obtained.

Table

roll_no	sub_id	sub_name	sub_mark
1	121	Science	80
1	131	Math	65
2	131	Math	95
2	141	English	75

• Here primary key will be roll_no+ sub_id because multiple roll_no can have the same sub_id and the same roll_no can have multiple sub_id.In the given example, roll_no 1 has two sub_id i.e. 121 and 131 where as sub_id 131 has two roll_no 1 and 2.So here primary key will be roll_no + sub_id.

• But we do have another column of sub_name and the value of sub_name can be easily obtained by only sub_id which is part of the primary key.For example, sub_id = 131 will have the sub_name = ‘math’ here we required only partial primary key i.e. sub_id.

• This type of functional Dependency is known as Partial Dependency.

Full Dependency

• If all attributes of the primary key are required for the identifying value of a non-primary attribute then it is known as Full Dependency.
• When all non-primary attribute are dependent on whole primary key and they cannot be get defined using only partial part of primary key then it is called as Full Dependency.
• If the dependency is non-partial dependency then it can be called as Full Deppendency also.

Example

Let’s take an example, we have a table where we have columns of student roll number, subject ID, and marks obtained.

Table

roll_no	sub_id	marks
1	121	80
1	131	65
2	131	95
2	141	75

• Here the primary key is roll_no+ sub_id. If we want a mark of any student, we require both roll_no and sub_id. We cannot obtain marks based on one attribute from the primary key.

• If we want to know the marks of sub_id=131 there will be two records and ambiguity will be created. If we take roll_id=1 there will be two records with the same roll number and ambiguity will be created here.This ambiguity will be solved using full attributes of the primary key i.e. roll_no + sub_id. So we required the full attributes of the primary key.

• This type of functional Dependency is known as Full Dependency.

Transitive Dependency

• If the value of a non-primary attribute can be defined using another non-primary attribute then it is called a transitive dependency.
• When any attribute does not require primary key and can easily get value using another non-primary attribute then it is called as Transitive Dependency.

Example

Let’s take an example, we have a table where we have columns of student roll number, name, city where student live, and zip-code of city .

Table

roll_no	name	city	zip-code
1	abc	pune	411044
2	jkl	mumbai	400001
3	uvw	pune	411044
4	xyz	delhi	110001

• Here the primary key is roll_no but we can identify the city using zip-code where city and zip-code both are the primary key

• So here roll_no → city and city→zip-code eventually resulting into roll_no →zip-code. so we can find a non-primary attribute using another non-primary attribute.For example, roll-no = 1 has city=pune and city=pune will have zip-code=411044.So wherever city is pune , zip-code will be 411044

• This type of functional Dependency is known as Transitive Dependency.

Partial, Full, and Transitive Dependency – FAQs

Which normal form is based on partial dependency?

2NF(second normal form ) is used to eliminate the partial dependencies present in the database.

Which normal form is based on transitive dependency?

3NF(third normal form ) is used to eiminate the transitives dependencies present in database.

Why are partial and transitive dependencies are bad?

Because they create problems in database management. Due to this type of dependency, they create redundancy issue , inconsistency in data and many more issues.So we need to avoid this type of dependencies .

How to avoid transitive dependency?

To avoid transitive dependency, a new table should be created using the non prime attributes which have relation with each other .New table should have its primary key and a refernce to the previous table via foreign key.

How to avoid transitive dependency?

The partial primary key attribute with its dependent attribute is removed from the table and kept in a new table with new relation where non prime attribute are now fully dependent on primary key atrributes.