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?
- Types of 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.
| Stu_Id | Stu_Name |
| 011 | Marketing |
| 022 | HR |
| 033 | Finance |
| 044 | Accounting |
| 055 | Sales |
| 066 | Telecom |
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:
- Trivial FD
- Non-Trivial FD
- 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
FAQs
What are functional dependencies in DBMS?
The functional dependency refers to a constraint that would specify the relationship that is between two separate sets of attributes in which one set can determine the actual value of other sets accurately. The attribute set that is present on the left side of the given arrow P is known as Determinant. On the right side, Q is known as the Dependent.
How do you identify functional dependency?
Any functional dependency, say P->Q means that no two values of Q that are different from each other are related to the same P ever. Since the key has to be unique, even when two tuples include the similar value of any attribute(s), the key values have to be different nonetheless.
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,
Comments