Functional Dependency in DBMS

What is Functional Dependency in DBMS?

Relational database is a collection of data stored in rows and columns. Columns represent the characteristic of data while each row in a table represents a set of related data, and every row in the table has the same structure. The row is sometimes referred to as a tuple in DBMS.

Have a look at the Employee table below. It contains attributes as column values, namely

1. Employee_Id
2. Employee_Name
3. Employee_Department
4. Salary

Employee Table

Now that we are clear with the jargon related to functional dependency, let's discuss what functional dependency is.

• Functional Dependency in DBMS, as the name suggests it is the relationship between attributes(characteristics) of a table related to each other.
• A relation consisting of functional dependencies always follows a set of rules called RAT rules. They are proposed by William Armstrong in 1974.
• It helps in maintaining the quality of data in the database, and the core concepts behind database normalization are based on functional dependencies.

How to Denote a Functional Dependency in DBMS?

A functional dependency is denoted by an arrow “→”. The functional dependency of A on B is represented by A → B.

Consider a relation with four attributes A, B, C and D,

R (ABCD)

1. A → BCD
2. B → CD

• For the first functional dependency A → BCD, attributes B, C and D are functionally dependent on attribute A.
• Function dependency B → CD has two attributes C and D functionally depending upon attribute B.

Types of Functional Dependencies in DBMS

1. Trivial functional dependency
2. Non-Trivial functional dependency
3. Multivalued functional dependency
4. Transitive functional dependency

Trivial Functional Dependency in DBMS

• In Trivial functional dependency, a dependent is always a subset of the determinant. In other words, a functional dependency is called trivial if the attributes on the right side are the subset of the attributes on the left side of the functional dependency.
• X → Y is called a trivial functional dependency if Y is the subset of X.
• For example, consider the Employee table below.

• Here, { Employee_Id, Name } → { Name } is a Trivial functional dependency, since the dependent Name is the subset of determinant { Employee_Id, Name }.
• { Employee_Id } → { Employee_Id }, { Name } → { Name } and { Age } → { Age } are also Trivial.

Non-Trivial Functional Dependency in DBMS

• It is the opposite of Trivial functional dependency. Formally speaking, in Non-Trivial functional dependency, dependent if not a subset of the determinant.
• X → Y is called a Non-trivial functional dependency if Y is not a subset of X. So, a functional dependency X → Y where X is a set of attributes and Y is also a set of the attribute but not a subset of X, then it is called Non-trivial functional dependency.
• For example, consider the Employee table below.

• Here, { Employee_Id } → { Name } is a non-trivial functional dependency because Name(dependent) is not a subset of Employee_Id(determinant).

• Similarly, { Employee_Id, Name } → { Age } is also a non-trivial functional dependency.

Multivalued Functional Dependency in DBMS

• In Multivalued functional dependency, attributes in the dependent set are not dependent on each other.
• For example, X → { Y, Z }, if there exists is no functional dependency between Y and Z, then it is called as Multivalued functional dependency.
• For example, consider the Employee table below.

• Here, { Employee_Id } → { Name, Age } is a Multivalued functional dependency, since the dependent attributes Name, Age are not functionally dependent(i.e. Name → Age or Age → Name doesn’t exist !).

Transitive Functional Dependency in DBMS

• Consider two functional dependencies A → B and B → C then according to the transitivity axiom A → C must also exist. This is called a transitive functional dependency.
• In other words, dependent is indirectly dependent on determinant in Transitive functional dependency.
• For example, consider the Employee table below.

• Here, { Employee_Id → Department } and { Department → Street Number } holds true. Hence, according to the axiom of transitivity, { Employee_Id → Street Number } is a valid functional dependency.

Armstrong’s Axioms/Properties of Functional Dependency in DBMS

William Armstrong in 1974 suggested a few rules related to functional dependency. They are called RAT rules.

1. Reflexivity: If A is a set of attributes and B is a subset of A, then the functional dependency A → B holds true.

   • For example, { Employee_Id, Name } → Name is valid.

2. Augmentation: If a functional dependency A → B holds true, then appending any number of the attribute to both sides of dependency doesn't affect the dependency. It remains true.

   • For example, X → Y holds true then, ZX → ZY also holds true.
   • For example, if { Employee_Id, Name } → { Name } holds true then, { Employee_Id, Name, Age } → { Name, Age }

3. Transitivity: If two functional dependencies X → Y and Y → Z hold true, then X → Z also holds true by the rule of Transitivity.

   • For example, if { Employee_Id } → { Name } holds true and { Name } → { Department } holds true, then { Employee_Id } → { Department } also holds true.

Advantages of Functional Dependency in DBMS

Let's discuss some of the advantages of Functional dependency,

1. It is used to maintain the quality of data in the database.
2. It expresses the facts about the database design.
3. It helps in clearly defining the meanings and constraints of databases.
4. It helps to identify bad designs.
5. Functional Dependency removes data redundancy where the same values should not be repeated at multiple locations in the same database table.
6. The process of Normalization starts with identifying the candidate keys in the relation. Without functional dependency, it's impossible to find candidate keys and normalize the database.

Read More

• Denormalization in DBMS.
• Data Models in DBMS.
• DBMS Join Dependency