Functional Dependency

Functional Dependency (FD) is a constraint that determines the relation of one attribute to another attribute in a Database Management System (DBMS). This article explains the concept of functional dependency, provides examples, and discusses the types of functional dependencies such as trivial, non-trivial, multivalued, and transitive dependencies. It also covers inference rules for deriving functional dependencies.

Functional Dependenc
5/12/2022 PREPARED BY: SADIA NAZIM 1

Functional Dependency (FD)
❑ Functional Dependency (FD) is a constraint that determines the relation of one attribute to
another attribute in a Database Management System (DBMS).
❑ If column A of a table uniquely identifies the column B of same table then it can represented
as A->B (Attribute B is functionally dependent on attribute A)
❑ The functional dependency is a relationship that exists between two attributes. It typically
exists between the primary key and non-key attribute within a table.
❑ A functional dependency is denoted by an arrow “→”.
❑ The functional dependency of X on Y is represented by X → Y.
❑ The left side of FD is known as a determinant, the right side of the production is known as a
dependent.
5/12/2022 PREPARED BY: SADIA NAZIM 2

Example
❑ For example: Suppose we have a student table with attributes: Stu_Id, Stu_Name, Stu_Age.
Here Stu_Id attribute uniquely identifies the Stu_Name attribute of student table because if
we know the student id we can tell the student name associated with it. This is known as
functional dependency and can be written as Stu_Id->Stu_Name or in words we can say
Stu_Name is functionally dependent on Stu_Id.
Stu_Id Stu_Name Stu_Age
1 Ali 12
2 Sara 15
5/12/2022 PREPARED BY: SADIA NAZIM 3

Types of Functional Dependencies
❑ Trivial functional dependency
❑ non-trivial functional dependency
❑ Multivalued dependency
❑ Transitive dependency
5/12/2022 PREPARED BY: SADIA NAZIM 4

Trivial functional dependency
❑The dependency of an attribute on a set of attributes is known as trivial functional if the set of
attributes includes that attribute.
❑A dependent is always a subset of the determinant.
❑Symbolically: A ->B is trivial functional dependency if B is a subset of A.
❑The following dependencies are also trivial: A->A & B->B
❑Consider this table of with two columns Emp_id and Emp_name.
❑{Emp_id, Emp_name} -> Emp_id is a trivial functional dependency as Emp_id is a subset of
{Emp_id,Emp_name}.
5/12/2022 PREPARED BY: SADIA NAZIM 5

Non Trivial Functional Dependency
❑A → B has a non-trivial functional dependency if B is not a subset of A.
❑The dependent is strictly not a subset of the determinant.
❑ When A intersection B is NULL, then A → B is called as complete non-trivial.
❑An employee table with three attributes: emp_id, emp_name, emp_address.
❑The following functional dependencies are non-trivial:
❑emp_id -> emp_name (emp_name is not a subset of emp_id)
❑emp_id -> emp_address (emp_address is not a subset of emp_id)
5/12/2022 PREPARED BY: SADIA NAZIM 6

Non Trivial Functional Dependency
Here, roll_no → name is a non-trivial functional dependency, since the dependent name is not a subset of
determinant roll_no
Similarly, {roll_no, name} → age is also a non-trivial functional dependency, since age is not a subset of
{roll_no, name}
5/12/2022 PREPARED BY: SADIA NAZIM 7

Multivalued dependency
❑Multivalued dependency occurs when there are more than one independent multivalued
attributes in a table.
❑i.e. If a → {b, c} and there exists no functional dependency between b and c, then it is called a
multivalued functional dependency.
❑For example: Consider a bike manufacture company, which produces two colors (Black and
white) in each model every year.
5/12/2022 PREPARED BY: SADIA NAZIM 8

Multivalued dependency
❑Here columns manuf_year and color are independent of each other and dependent on
bike_model. In this case these two columns are said to be multivalued dependent on
bike_model. These dependencies can be represented like this:
❑bike_model ->> manuf_year
❑bike_model ->> color
5/12/2022 PREPARED BY: SADIA NAZIM 9

Transitive dependency
❑A functional dependency is said to be transitive if it is indirectly formed by two functional
dependencies. For e.g.
❑If a → b & b → c, then according to axiom of transitivity, a → c. This is a transitive functional
dependency
❑Note: A transitive dependency can only occur in a relation of three of more attributes. This
dependency helps us normalizing the database in 3NF (3rd Normal Form).
❑Example: Let’s take an example to understand it better:
5/12/2022 PREPARED BY: SADIA NAZIM 10

Transitive dependency
{Book} ->{Author} (if we know the book, we knows the author name)
{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.
5/12/2022 PREPARED BY: SADIA NAZIM 11

Inference Rule (IR):
❑ The Armstrong's axioms are the basic inference rule.
❑ Armstrong's axioms are used to conclude functional dependencies on a relational database.
❑ The inference rule is a type of assertion. It can apply to a set of FD(functional dependency) to
derive other FD.
❑ Using the inference rule, we can derive additional functional dependency from the initial set.
❑ The Functional dependency has 6 types of inference rule:
❑ 1. Reflexive Rule (IR1)
❑ In the reflexive rule, if Y is a subset of X, then X determines Y.
❑ If X ⊇ Y then X → Y
❑ Example:
❑ For example, {roll_no, name} → name is valid.
5/12/2022 PREPARED BY: SADIA NAZIM 12

❑ 2. Augmentation Rule (IR2)
❑ The augmentation is also called as a partial dependency. In augmentation, if X determines Y, then XZ determines YZ for
any Z.
❑ If X → Y then XZ → YZ
❑ Example:
❑ For example, If {roll_no, name} → dept_building is valid, hence {roll_no, name, dept_name} → {dept_building,
dept_name} is also valid.→
❑ 3. Transitive Rule (IR3)
❑ In the transitive rule, if X determines Y and Y determine Z, then X must also determine Z.
❑ If X → Y and Y → Z then X → Z
❑ For example, roll_no → dept_name & dept_name → dept_building, then roll_no → dept_building is also valid.
❑ 4. Union Rule (IR4)
❑ Union rule says, if X determines Y and X determines Z, then X must also determine Y and Z.
❑ If X → Y and X → Z then X → YZ
5/12/2022 PREPARED BY: SADIA NAZIM 13

❑ 5. Decomposition Rule (IR5)
❑ Decomposition rule is also known as project rule. It is the reverse of union rule.
❑ This Rule says, if X determines Y and Z, then X determines Y and X determines Z separately.
❑ If X → YZ then X → Y and X → Z
5/12/2022 PREPARED BY: SADIA NAZIM 14