Functional Dependency in DBMS

Just like the name suggests, a Functional dependency in DBMS refers to a relationship that is present between attributes of any table that are dependent on each other. E. F. Codd introduced it, and it helps in avoiding data redundancy and getting to know more about bad designs.

In this article, we will take a look at the Functional Dependency in DBMS according to the GATE Syllabus for CSE (Computer Science Engineering). Read ahead to learn more.

Table of Contents

• What is Functional Dependency in DBMS?
  • Example
• Types of Functional Dependency
  • Trivial Functional Dependency
  • Non–Trivial Functional Dependency
  • Completely Non-Trivial Functional Dependency
• Functional Dependency’s Armstrong’s Axioms Property
• Practice Questions on Functional Dependency in DBMS
• FAQs

What is Functional Dependency in DBMS?

Let us try to understand the concept thoroughly. If X is a relation that has attributes P and Q, then their functional dependency would be represented by -> (arrow sign)

Thus, here, the following would represent the functional dependency between the attributes using an arrow sign:

P -> Q

In this case, the left side of this arrow is a Determinant. The right side of this arrow is a Dependent. P will be the primary key attribute, while Q will be a dependent non-key attribute from a similar table as the primary key. It shows that the primary key attribute P is functionally dependent on the non-key attribute Q. In simpler words, If the column P attribute of a table identifies the column Q attribute of the very same table uniquely, then the functional dependency of column Q on column P is symbolised as P → Q.

Example

Let us look at an example that makes it easier to comprehend functional dependency.

Suppose we have a <Student> table with two separate attributes − Stu_Id and Stu_Name.

Stu_Id = Student ID

Stu_Name = Student Name

The StuId is our primary key. And StuId here identifies the StuName attribute uniquely. It is because if someone wants to know the student’s name, then you need to have the StuId at first.

The functional dependency given above between Stu_Id and Stu_Name can be specified as Stu_Id is functionally dependent on Stu_Name.

Stu_Id -> Stu_Name

Types of Functional Dependency

The Functional Dependencies are of four major types:

1. Trivial FD
2. Non-Trivial FD
3. Completely Non-Trivial FD

1. Trivial Functional Dependency

A trivial functional dependency occurs when Q is a subset of P in −

P ->Q

For example,

Let us now consider the same <Section> table with two separate attributes to comprehend the trivial dependency’s concept. Here is a trivial functional dependency because SecId is a subset of SecId and SecName.

{ Sec_Id, Sec_Name } -> Sec Id

2. Non –Trivial Functional Dependency

A non-trivial functional dependency occurs when Q is not a subset of P in −

P -> Q

For example,

Sec_Id -> Sec_Name

The FD given above is a functional dependency that is of a non-trivial type since SecName is not a subset of SecId.

3. Completely Non-Trivial Functional Dependency

A completely non-trivial functional dependency occurs when P intersection Q is null in −

P -> Q

Functional Dependency’s Armstrong’s Axioms Property

William Armstrong developed Armstrong’s Axioms property in 1974 to reason about FDs. This property suggests those rules that hold true only when the following ones are satisfied:

Augmentation

This last rule suggests that PR -> QR, if P -> Q

Reflexivity

P -> Q, if Q is a subset of P.

Transitivity

If P -> Q and Q -> R, then P -> R i.e. a transitive relation.

Practice Questions on Functional Dependency in DBMS

1. When an FD is reflexive, Y is a subset of X and X refers to the set of attributes, then:

A. Y -> X holds

B. X -> Y holds

C. XY -> Z holds

D. None of the above

Answer: B. X -> Y holds

When an FD is reflexive, Y is a subset of X, and X is the set of attributes, then X -> Y would always hold. It is known as the Armstrong axioms reflexivity rule.

2. If P -> Q, P -> R, then which of these would be true?

A. P -> QR

B. P -> Q

C. P -> R

D. All of the above

Answer: D. All of the above

3. If A(X, Y, Z) refers to a relation, then which of these doesn’t have a lossless join type of dependency that preserves BCNF decomposition?

A. P -> Q, Q -> RS

B. P -> Q, Q -> R, R -> S

C. PQ -> R, R -> PS

D. P -> QRS

Answer: A. P -> Q, Q -> RS

Keep learning and stay tuned to get the latest updates on GATE Exam along with GATE Eligibility Criteria, GATE 2023, GATE Admit Card, GATE Syllabus for CSE (Computer Science Engineering), GATE CSE Notes, GATE CSE Question Paper, and more.

Also Explore,

• Introduction to DBMS
• Join Dependency in DBMS
• Lossless Decomposition in DBMS
• Lossy Decomposition in DBMS
• Multivalued Dependency in DBMS
• Decomposition in DBMS
• First Normal Form in DBMS
• Partial Dependenc in DBMS
• Transitive Dependency in DBMS