Functional dependency

Functional dependency is the concept which is given by E. F. Codd.  It is a relationship that exists when one attribute uniquely determines another attribute or between multiple attributes of a relation

This concept is given by E. F. Codd which is used to define various normal forms.

For example: Suppose we have an Employee table with attributes:-

Employee _Id, Employee _Name, Employee _Age. Here Employee _Id attribute uniquely identifies the Employee _Name attribute of Employee table because if we know the Employee id we can tell the Employee name associated with it. This is known as functional dependency and can be written as Employee _Id-> Employee _Name or in words we can say Employee _Name is functionally dependent on Employee _Id.

Advantages of Functional Dependency

• Functional Dependency avoid where same data should not be repeated at multiple locations in same database.
• It is used to maintain the quality of data in database.
• It let allows clearly defined meanings and constraints of databases.
• It helps to identify bad designs.
• It expresses the facts about the database design.

Types of Functional Dependencies

• Trivial functional dependency
• non-trivial functional dependent
• Multivalued dependency
• Transitive dependency

Trivial functional dependency-: The dependency of an attribute on a set of attributes is called as trivial functional dependency if the set of attributes includes that attribute.

Symbolically: X->Y is trivial functional dependency if Y is a subset of X.

The following dependencies are also trivial: X->X & Y->Y

For example: Consider a table with two columns Employee _id and Employee _Name.

{Employee_Id, Employee_Name} -> Employee_Id is a trivial functional dependency as Employee_Id is a subset of {Employee_Id, Employee_Name}.  That makes sense because if we know the values of Employee_Id and Employee_Name then the value of Employee_Id can be uniquely determined.

Also, Employee_Id -> Employee_Id & Employee_Name -> Employee_Name are trivial dependencies too.

Non trivial Functional dependency -: If a functional dependency A->B holds true where B is not a subset of A then this dependency is called non trivial Functional dependency.

For example:
An employee table with three attributes: stu_id, stu_name, stu_address.
The following functional dependencies are non-trivial:
stu_id -> stu_name (stu_name is not a subset of stu_id)
stu_id -> stu_address (stu_address is not a subset of stu_id)

Multivalued dependency -: Multivalued dependency occurs when there are more than one independent multivalued attributes in a table.

For example: Consider a Car manufacture company, which produces two colors (Black and white) in each model every year.

Here columns manuf_year and color are independent of each other and dependent on car_model. In this case these two columns are said to be multivalued dependent on car_model. These dependencies can be represented like this:

car_model ->> manuf_year

car_model ->> color

Transitive dependency -: A functional dependency is known to be transitive if it is indirectly formed by two functional dependencies.

For e.g.

 A -> B is a transitive dependency if the following three functional dependencies hold true:

• A->B
• B does not ->A
• B->A

Note: This dependency helps us normalizing the database in 3NF (3^rd Normal Form).

Example: Let’s take an example to understand it better:

{Book} ->{Author} (if we know the book, we knows the author name)

{Author} does not ->{Book}

{Author} -> {Author_age}

Therefore as per the rule of transitive dependency: {Book} -> {Author_age} should hold, that makes sense because if we know the book name we can know the author’s age.