EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials DBMS Tutorial Functional Dependency in DBMS
Secondary Sidebar
DBMS Tutorial
  • DBMS
    • What is DBMS?
    • Introduction To DBMS
    • DBMS ER Diagram
    • DBMS_RANDOM
    • DBMS_XPLAN
    • DBMS join
    • DBMS Functions
    • Data Administrator in DBMS
    • Checkpoint in DBMS
    • DBMS Table
    • Mapping Constraints in DBMS
    • DBMS Canonical Cover
    • DBMS Log-Based Recovery
    • DBMS Multivalued Dependency
    • View Serializability in DBMS
    • DBMS Concepts
    • DBMS Constraints
    • DBMS_Scheduler
    • B+ Tree in DBMS
    • DBMS_LOB
    • dbms entity
    • DBMS Foreign Key
    • DBMS Users
    • DBMS_Metadata.get_ddl
    • Relational Algebra in DBMS
    • DBMS Components
    • DBMS Features
    • DBMS Models
    • DBMS Relational Model
    • Hashing in DBMS
    • DBMS network model
    • Relationship in DBMS
    • ER Model in DBMS
    • Data Models in DBMS
    • Static Hashing in DBMS
    • Advantages of DBMS
    • dbms_output.put_line
    • DBMS Data Dictionary
    • dbms_xplan.display_cursor
    • Normal Forms in DBMS
    • DBMS helps achieve
    • DBMS 3 tier Architecture
    • Relational Calculus in DBMS
    • Serializability in DBMS
    • File Organization in DBMS
    • DBMS Transaction Processing
    • States of Transaction in DBMS
    • Functional Dependency in DBMS
    • Generalization in DBMS
    • Data Independence in DBMS
    • Lock Based Protocols in DBMS
    • Deadlock in DBMS
    • Integrity Constraints in DBMS
    • Concurrency Control in DBMS
    • Validation Based Protocol in DBMS
    • DBMS Locks
    • Normalization in DBMS
    • Transaction Property in DBMS
    • Specialization in DBMS
    • Aggregation in DBMS
    • Types of DBMS

Functional Dependency in DBMS

By Priya PedamkarPriya Pedamkar

Functional Dependency in DBMS Main

Introduction to Functional Dependency in DBMS

The term functional dependency means the association between any two attributes. Typically, this relationship is demonstrated between the primary key and non-key attributes within the table of a Database Management System, where the non-key attribute is functionally dependent on the primary key attribute to operate. A Functional Dependency case in a table is termed as ‘Minimal’ if the non- key attribute has dependencies on the primary key attribute with the functional characteristics such as there is only one non-key attribute in the table, any change made in the primary key attribute brings in changes to the non-key attribute as well, and if any alteration made on the functional dependencies will affect the table contents of the primary key.

Syntax:

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Functional Dependency on any given table can be explained as,

X → Y

Here, the left side of the arrow is identified as a Determinant, while the right side of the arrow is identified as a Dependent. X will always be the primary key attribute and Y will be any dependent non- key attribute from the same table as the primary key. This shows X primary key attribute is functionally dependent on the Y non-key attribute. In other words, If column X attribute of the table uniquely identifies the column Y attribute of the same table, then the functional dependency of the column Y on the column X is symbolized as X → Y.

Types of Functional Dependency

The Scenario for Functional Dependencies can be classified into the below four types:

1. Multivalued Functional Dependency in DBMS

Multivalued Functional Dependency takes place in the conditions when there is more than one independent attribute with multiple values in the same table. The Multivalued Dependency case is a complete limitation between two sets of attributes in the relationship of Functional Dependency. It requires that certain row values can be present as a functional dependency connection. This can be represented as,

X → Y
X → Z
X → A,

Where X, Y, Z, A are attributes of the same table, X being the primary key and Y, Z, A is non- key attributes. Here Y, Z, A are functionally dependent on X, and not dependent on each other.

For better understanding, let us consider the below table,

Student_ID

Student_Name Dept DOB
S_001 Sname01 Computer

Jan-01

S_002

Sname02 Maths Mar-07
S_003 Sname03 English

Sept-11

In this example, Student_Name, Dept & DOB are not dependent on each other but are dependent on Student_ID. In terms of Functional Dependency, Student_ID is the determinant, Student_Name, Dept, DOB are the dependents. Student_ID is the primary key here, while Student_Name, Dept, and DOB are non-key columns. Hence the dependence can be symbolized as,

Student_ID → Student_Name Student_ID → Dept Student_ID → DOB

2. Trivial Functional Dependency in DBMS

The Trivial Functional Dependency is a set of attributes or columns that are known a trivial if the on- key-dependent attribute is a subset of the determinant attribute, which is a primary key attribute. This Trivial Functional Dependency scenario occurs when the primary key is formed by two columns, and one of which is functionally dependent on the combined set.

X → Y, where is a trivial functional dependency, if Y is a subset of X. Let us consider the below table,

Student_ID

Student_Name Dept DOB
S_001 Sname01 Computer

Jan-01

S_002

Sname02 Maths Mar-07
S_003 Sname03 English

Sept-11

Here, if the primary key is a combination of the columns Student_ID and Student_Name, then the Student_Name column is in Trivial Functional Dependency relationship with the primary key set [Student_ID, Student_Name]. Any changes made in the Student_Name column will have its effects on the primary key set [Student_ID, Student_Name], as the Student_Name column is a subset of the primary key attribute set. For a Student ID, S_001, the primary key combination will be [S_001, Sname01]. If a change to the name is made as Sname001, then the primary key combination will change as [S_001, Sname001], as the Student_Name column is a subset of the primary key.

3. Non-Trivial Functional Dependency in DBMS

A Non Trivial Functional Dependency is a normal functional dependency, where the non-key attribute is functionally dependent on a primary key attribute, without the occurrence of trivial functional dependency.

X → Y, where is a non-trivial functional dependency, if and only if Y is not a subset of X. Let us consider the below table,

Student_ID

Student_Name Dept DOB
S_001 Sname01 Computer

Jan-01

S_002

Sname02 Maths Mar-07
S_003 Sname03 English

Sept-11

Here, if the primary key is the column Student_ID, and the Student_Name column is not a subset of Student_ID, then the Student_Name column is in a non Trivial Functional Dependency relationship with the primary key Student_ID.

4. Transitive Functional Dependency in DBMS

A Transitive Functional Dependency is a type of functional dependency which happens when the non- key attribute is indirectly formed by its functional dependencies on the primary key attributes. Either the value or the known factors can be the reason for this type of Functional Dependency occurrence. The Transitive Functional Dependency can only occur in a relation of three or more non-key attributes that are functionally dependent on the primary key attribute.

Let us consider the below table to understand this,

Student_ID

Student_Name

Dept

DOB

S_0101_C

Sname01 Computer_C 01-01-1999
T_0307_M

Tname02

Maths_M

03-07-1998

U_0711_E

Uname03 English_E

07-11-1997

In this table, the Student_ID column is the primary key. The values in the Student_ID column are formed by the combination of the first letter from the Student_Name column, last code from the Dept column and date & month from the DOB column. If any change is made in any of these columns will reflect changes in the primary key column, that is, the Student_ID column. Any new record inserted in this table will also have a Student_ID value formed from the combination of the other three non-key columns.

Conclusion

Defining Functional Dependency between the columns is a crucial part of designing a table, as bad DBMS designs have plenty of disadvantages while querying, as well as makes it impossible to implement any potential upgrades. Functional Dependency provides more clarity on the values, helps to maintain the quality of the data, and keep the database clean without junk or repeating data.

Recommended Articles

This is a guide to the Functional Dependency in DBMS. Here we discuss the introduction, Syntax and various types of Functional Dependency in DBMS. You may also look at the following articles to learn more –

  1. Different types of Data Models in DBMS
  2. Types of DBMS Architecture
  3. Key Difference between DBMS vs RDBMS
  4. Guide to Specialization in DBMS
Popular Course in this category
All in One Data Science Bundle (360+ Courses, 50+ projects)
  360+ Online Courses |  1500+ Hours |  Verifiable Certificates |  Lifetime Access
4.7
Price

View Course
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more