Please login

Prime

Prepinsta Prime

Video courses for company/skill based Preparation

(Check all courses)
Get Prime Video
Prime

Prepinsta Prime

Purchase mock tests for company/skill building

(Check all mocks)
Get Prime mock

Functional Dependencies

Functional Dependencies

 

In this article, we will learn about Functional Dependencies in DBMS.

  • If one attribute is determined by another attribute in a DBMS system then it is a functional dependency
  • Functional Dependency plays an important role to find the difference between good and bad database design.
  • A functional dependency is denoted by an arrow →
  • The functional dependency of A on B is represented by A →B

 

Learn more about DBMS here on this page.

Functional Dependencies

Example

  • In the below table, if we know the value of Stu_ID, we can obtain Stu_Name, Marks, City, etc
  • From this we can say that Stu_Name, Marks and City are functionally dependent on Stu_ID.

Stu_ID

Stu_NameMarksCity

3012

Amitha98Hyderabad
2198Divya95

Delhi

2589Kushi85

Mumbai

 

Types of functional dependencies are

  • Multivalued dependency
  • Trivial functional dependency
  • Non-trivial functional dependency
  • Transitive dependency

 

Rules of Functional Dependencies

  • Reflexive rule :. If A is a set of attributes and B is subset of A, then A holds a value of B.
  • Augmentation rule : When A -> B holds, and C is attribute set, then AC->BC also holds. That is adding attributes which do not change the basic dependencies.
  • Transitivity rule : This rule is very much similar to the transitive rule in algebra if A->B holds and B->C holds, then A->C also holds. A->B is called as functionally that determines B.

 

Multivalued Dependency

  • Multivalued dependency occurs in the situation where there are multiple independent multivalued attributes in a single table.
  • A multivalued dependency is a complete constraint between two sets of attributes in a relation.

Example:

Car_modelManufacture_yearColor
H0012017Metallic
H0012017Green
H0052018Metallic
H0052018Blue
H0102015Metallic
H0332012Gray

 

  • In this example, Manufacture_year and colour are independent of each other but dependent on Car_model. In this example, these two columns are said to be multivalue dependent on Car_model.
  • This dependence can be represented like this:
    • Car_model -> Manufacture_year
    • Car_model-> colour

 

Trivial Functional dependency

  • The Trivial dependency is a set of attributes which are called a trivial if the set of attributes are included in that attribute.
  • A->B is a trivial functional dependency if B is a subset of A.

Example:

Stu_IDStu_Name
3015Priya
1402Rajesh
2109Ganga

 

In the above table,{Stu_ID, Stu_Name} -> Stu_ID is a trivial functional dependency as Stu_ID is a subset of {Stu_ID,Stu_Name}.

 

Non trivial functional dependency

  • In a relationship, if attribute B is not a subset of attribute A, then it is considered as a non-trivial dependency.
  • A nontrivial dependency occurs when A->B holds true where B is not a subset of A.

Example:

Stu_IDStu_NameMarks
3015Priya97
1402Rajesh83
2109 Ganga89

 

  • {Stu_ID} -> {Stu_Name} (if we know the Stu_ID, we knows the Stu_Name)
  • But Stu_Name is not a subset of Stu_ID, and hence it’s non-trivial functional dependency.

 

Transitive dependency

A transitive is a type of functional dependency which happens when it is indirectly formed by two functional dependencies.

Example:

Stu_IDStu_NameMarks
3015Priya97
1402Rajesh83
2109 Ganga89

 

  • {Stu_ID} -> {Stu_Name} (if we know the Stu_ID, we know its Stu_Name)
  • {Stu_Name} -> {Marks} If we know the Stu_Name, we know the Marks
  • Therefore according to the rule transitive dependency: {Stu_ID} -> {Marks} should hold, that makes sense because if we know the Stu_ID, we can know his/her marks.

 

Advantages of Functional Dependency

  • Functional Dependency avoids data redundancy. Therefore same data do not repeat at multiple locations in that database
  • It helps you to maintain the quality of data in the database
  • It helps you to define meanings and constraints of databases
  • It helps you to identify bad designs
  • It helps you to find the facts regarding the database design