UNIT II DATABASE DESIGN

2.5 Functional Dependency 

A functional dependency occurs when the value of one attribute (or a set of attributes) uniquely determines the value of another attribute. This relationship is denoted as:

X → Y

Here, X is the determinant, and Y is the dependent attribute. This means that for each unique value of X, there is precisely one corresponding value of Y.

Consider a table named Students with the following attributes:

StudentID	StudentName	StudentAge
101	Rahul	23
102	Ankit	22
103	Aditya	22
104	Sahil	24
105	Ankit	23

The above table has the following functional dependencies

StudentID -> StudentName
StudentID -> StudentAge

Note that the functional dependencies StudentName -> StudentAge or StudentAge -> StudentName Do not hold.

Types of Functional dependencies

A functional dependency occurs when one attribute uniquely determines another attribute within a relation. There are following types of functional dependencies.

1. Trivial Functional Dependency

In Trivial Functional Dependency, a dependent is always a subset of the determinant. i.e. If X → Y and Y is the subset of X, then it is called trivial functional dependency.

Symbolically: A→B is trivial functional dependency if B is a subset of A.

The following dependencies are also trivial: A→A & B→B

Example 1 :

• ABC -> AB
• ABC -> A
• ABC -> ABC

Example 2:

roll_no	name	age
42	abc	17
43	pqr	18
44	xyz	18

Here, {roll_no, name} → name is a trivial functional dependency, since the dependent name is a subset of determinant set {roll_no, name}. Similarly, roll_no → roll_no is also an example of trivial functional dependency. 

2. Non-trivial Functional Dependency

In Non-trivial functional dependency, the dependent is strictly not a subset of the determinant. i.e. If X → Y and Y is not a subset of X, then it is called Non-trivial functional dependency.

Example 1 :

• Id -> Name
• Name -> DOB

Example 2:

roll_no	name	age
42	abc	17
43	pqr	18
44	xyz	18

Here, roll_no → name is a non-trivial functional dependency, since the dependent name is not a subset of determinant roll_no. Similarly, {roll_no, name} → age is also a non-trivial functional dependency, since age is not a subset of {roll_no, name} 

3. Semi Non Trivial Functional Dependencies 

A semi non-trivial functional dependency occurs when part of the dependent attribute (right-hand side) is included in the determinant (left-hand side), but not all of it. This is a middle ground between trivial and non-trivial functional dependencies. X -> Y is called semi non-trivial when X intersect Y is not NULL. 

Example:

Consider the following table:

Student_ID	Course_ID	Course_Name
101	CSE101	Computer Science
102	CSE102	Data Structures
103	CSE101	Computer Science

Functional Dependency:

{StudentID,CourseID}→CourseID

This is semi non-trivial because:

• Part of the dependent attribute (Course_ID) is already included in the determinant ({Student_ID, Course_ID}).
• However, the dependency is not completely trivial because {StudentID}→CourseID is not implied directly.

4. Multivalued Functional Dependency

In Multivalued functional dependency, entities of the dependent set are not dependent on each other. i.e. If a → {b, c} and there exists no functional dependency between b and c, then it is called a multivalued functional dependency.

Example:

bike_model	manuf_year	color
tu1001	2007	Black
tu1001	2007	Red
tu2012	2008	Black
tu2012	2008	Red
tu2222	2009	Black
tu2222	2009	Red

In this table:

• X: bike_model
• Y: color
• Z: manuf_year

For each bike model (bike_model):

1. There is a group of colors (color) and a group of manufacturing years (manuf_year).
2. The colors do not depend on the manufacturing year, and the manufacturing year does not depend on the colors. They are independent.
3. The sets of color and manuf_year are linked only to bike_model.

That’s what makes it a multivalued dependency.

In this case these two columns are said to be multivalued dependent on bike_model. These dependencies can be represented like this:

Read more about Multivalued Dependency in DBMS.

5. Transitive Functional Dependency

In transitive functional dependency, dependent is indirectly dependent on determinant. i.e. If a → b & b → c, then according to axiom of transitivity, a → c. This is a transitive functional dependency.

Example:

enrol_no	name	dept	building_no
42	abc	CO	4
43	pqr	EC	2
44	xyz	IT	1
45	abc	EC	2

Here, enrol_no → dept and dept → building_no. Hence, according to the axiom of transitivity, enrol_no → building_no is a valid functional dependency. This is an indirect functional dependency, hence called Transitive functional dependency.

6. Fully Functional Dependency

In full functional dependency an attribute or a set of attributes uniquely determines another attribute or set of attributes. If a relation R has attributes X, Y, Z with the dependencies X->Y and X->Z which states that those dependencies are fully functional.

Read more about Fully Functional Dependency.

7. Partial Functional Dependency

In partial functional dependency a non key attribute depends on a part of the composite key, rather than the whole key. If a relation R has attributes X, Y, Z where X and Y are the composite key and Z is non key attribute. Then X->Z is a partial functional dependency in RDBMS.