Functional Dependency in DBMS

• Functional Dependency is also known as “FDs” or a relationship.
• A relationship:- An attribute can determine other attributes functionally.
• The first attribute does not use for computing or calculating the value of the second attribute but it searches the value of the tuple corresponding to the first attribute and fetches records.
• Functional Dependency is denoted by an arrow between two or more attributes such as :

FD: A--à  B

A & B are the attributes present in any relation.   

“Aà  B” means, “B” is functionally dependent upon “A” or “A” functionally determines “B”.

·         Functional dependency acts as a constraint between sets of attributes data, present in any database.

·         functional dependency means the association between any two attributes. 

·         It is a relationship between multiple attributes of a relation.

·         This concept is given by E. F. Codd.

·         Functional dependency represents a formalism of the infrastructure of relation.

·         It is used to define various normal forms.

 Examples:-  a table student_details containing details.

Roll_No attribute is able to determine the Name of student and marks uniquely.

In other words, Name and Marks are functionally dependent on Roll_No but not vice versa.

FD1 : Roll_No-à   Name

FD2 : Roll_No  --à Marks

 

·          Non-key attribute is functionally dependent on the primary key attribute.

Example:  In Employee Table

EmpName (employee name) is functionally dependent on EmpId (employee id) because the EmpId is unique for individual names.

EmpName cannot distinguish EmpId because more than one employee could have the same name.

 

·        The functional dependency between attributes eliminates the repetition of information.

·        A candidate key, which uniquely identifies a tuple and determines the value of all other attributes in the relation.

 

Advantages of Functional Dependency

·        Avoids data redundancy (not repeating the same data at multiple locations in the same database).

·        Maintains the quality of data in the database.

·        Clearly defined meanings and constraints of databases.

·        Identifying bad designs.

·        Expresses the facts about the database design.

 Types of Functional Dependency

Classified into the four types:

1. Multivalued Functional Dependency in DBMS

·         It is shown when more than one independent attribute with multiple values in the same table.

·         It is a complete limitation between two sets of attributes in the relationship.

·         This can be represented as,

X → Y
X → Z
X → A,

Here X, Y, Z, A are attributes of the same table,

X is the primary key.

Y, Z, A are non-key attributes and functionally dependent on X, and not dependent on each other.

 Example:-

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 all are dependent on Student_ID.

Student_ID is the determinant, Student_Name, Dept, DOB are the dependents.

Student_ID is the primary key and Student_Name, Dept, and DOB is non-key columns.

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 as trivial if the non-key-dependent attribute is a subset of the determinant attribute, which makes jointly a primary key attribute.

·         This Trivial Functional Dependency occurs when the primary key is formed by two columns, one of which is functionally dependent on the combined set.

 

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, the primary key is a combination of the columns Student_ID and Student_Name.

in Trivial Functional Dependency, the Student_Name column is used with the primary key set [Student_ID, Student_Name].

If Any changes in the Student_Name column then it will effects 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].

 

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,

In a non-trivial functional dependency, Y is not a subset of X.

 

Example:- ,

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, the primary key is the Student_ID, and the Student_Name column is not a subset of Student_ID, it is in a non Trivial Functional Dependency relationship with the primary key Student_ID.

 

4. Transitive Functional Dependency in DBMS

·         In this functional dependency, the non-key attribute is indirectly designed by its functional dependencies on the primary key attributes.

·         This Functional Dependency is occur in three relations or more non-key attributes that are functionally dependent on the primary key attribute.

 

Example:-

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, the last code from the Dept column, and the date & month from the DOB column.

If any change is done in any of these columns then will reflect changes in the primary key column 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.

=============================================================