Functional Dependency in DBMS Explained with Examples

Understanding functional dependency in DBMS is essential for designing efficient, error-free relational databases. In simple terms, a functional dependency describes a clear cause-and-effect relationship between database columns (attributes), where knowing the value in one column allows you to reliably determine the exact value in another.

Whether you refer to it as attribute dependency, column determination, or functional mapping in databases, mastering this core concept helps database architects eliminate data duplication, prevent anomalies, and properly structure tables through normalization.

What is a functional dependency?

In a relational database, a functional dependency is a relationship between two attributes (columns) where the value of one column completely determines the value of another.

Think of it like a lookup rule or a cause-and-effect relationship: if
you know attribute \(A\), you can always figure out the exact value of
attribute \(B\).

A real-world example is a student ID number and a
student’s name, if you look up a specific student ID
(attribute \(A\)), it will always point to just one specific name
(attribute \(B\)), meaning the name functionally depends on the student
ID.

We write this relationship as \(A \rightarrow B\) (read as “\(A\)
determines \(B\)”), and it is the foundational concept used to organize database tables efficiently and avoid repeating duplicate data.

In relational database theory, the formal mathematical definition of a functional dependency is expressed using set theory and tuple mapping.

Mathematical definition

Given a relation schema \(R\) defined over a set of attributes
\(U = \left\{ A_{1},A_{2},\ldots,A_{n} \right\}\), and two subsets of
attributes \(X \subseteq U\) and \(Y \subseteq U\):

A \textbf{functional dependency}, denoted as \(X \rightarrow Y\) (read
as “\(X\) functionally determines \(Y\)” or “\(Y\) is
functionally dependent on \(X\)”), holds on a relation instance
\(r(R)\) if and only if for every pair of tuples \(t_{1},t_{2} \in r\):

\[\text{if }t_{1}\lbrack X\rbrack = t_{2}\lbrack X\rbrack \Longrightarrow t_{1}\lbrack Y\rbrack = t_{2}\lbrack Y\rbrack\]

where,

• \(r(R)\) : A specific relation instance (set of rows/tuples) conforming to the schema .
• \(t_{1},t_{2}\) : Two distinct rows (tuples) within the relation instance .
• \(t\lbrack X\rbrack\) : The projection (tuple of values) of row  corresponding strictly to the attribute set .
• \(X\) (Determinant): The set of attributes on the left side of the arrow.
• \(Y\) (Dependent): The set of attributes on the right side of the arrow.

The mathematical definition states that if two rows agree on all
values in set \(X\), they are strictly required to agree on all
values in set \(Y\).

• If \(t_{1}\lbrack X\rbrack = t_{2}\lbrack X\rbrack\) but
  \(t_{1}\lbrack Y\rbrack \neq t_{2}\lbrack Y\rbrack\), the functional
  dependency \(X \rightarrow Y\) is violated.
• If \(t_{1}\lbrack X\rbrack \neq t_{2}\lbrack X\rbrack\), the
  implication condition holds vacuously, regardless of whether
  \(t_{1}\lbrack Y\rbrack\) and \(t_{2}\lbrack Y\rbrack\) are equal or
  not.

Why is it called a functional dependency?

It is called a functional dependency because the relationship between the attributes behaves exactly like a mathematical function \(y = f(x)\).

In mathematics, a relation is a function if every input \(x\) produces
one and only one output \(y\). If you plug in the same \(x\),
you are guaranteed to get the exact same \(y\).

The concept works identically in databases:

1. Input (\(X\)) and Output (\(Y\)): In the
   dependency \(X \rightarrow Y\), the attribute set \(X\) acts as the
   input, and \(Y\) acts as the output.
2. Single-Value Mapping: Just like a mathematical function
   \(f(x) = y\), mapping \(X\) into the relation always yields a single,
   unique value for \(Y\).
3. Dependency: The value of \(Y\) depends entirely on the
   function of \(X\). If you know \(X\), \(Y\) is completely
   fixed, \(Y\) cannot take on multiple different values for the exact
   same \(X\).

For example, if we treat Student_ID as \(x\) and Student_Name as \(y\), Student_Name is a function of Student_ID because \(f\left( \text{1001} \right)\) will always evaluate to “John Doe”.

Use of functional dependency

The main use of functional dependencies is to help design clean, efficient databases by preventing redundant or duplicated information. By knowing which columns determine the values of other columns, database designers can split large, messy tables into smaller, well-organized ones, a process called database normalization.

This structure ensures that if a piece of information changes, like a student’s address, you only need to update it in one single spot rather than across hundreds of duplicate rows. Ultimately, relying on functional dependencies prevents accidental data errors, saves storage space, and keeps the entire database accurate and easy to maintain.

How to find functional dependencies?

Finding functional dependencies involves analyzing the relationships between columns (attributes) in a database table to see if one column’s value always uniquely determines another. Here is how to find them in three simple steps:

1. Analyze the Real-World Business Rules: Look at what the data actually represents rather than just a few rows of sample data. Ask yourself: “If I know the value of Column A, does that guaranteed give me exactly one possible value for Column B?” For example, in a company database, an Employee_ID will always determine a specific Employee_Name and Social_Security_Number.
2. Test for Single-Value Consistency: Examine table data to ensure there are no contradictions. For any dependency \(A \rightarrow B\) to be valid, every time attribute \(A\) repeats the value “X”, attribute \(B\) must repeat the same value “Y”. If two rows have the same Zip_Code (\(A\)), they must both have the same City (\(B\)). If you ever find two rows with the exact same Zip_Code but two different City names, then Zip_Code does not functionally determine City.
3. Check for Minimal (Canonical) Sets: Eliminate redundant columns on the left side. If (Employee_ID, Department) determines Employee_Name, but Employee_ID alone is already enough to determine Employee_Name, then Department is unnecessary. You want to keep the smallest set of columns on the left side that reliably determines the right side.

Example for finding functional dependencies

To demonstrate finding functional dependencies, we use our university database schema (which is already normalized).

To identify functional dependencies (FDs) in a database, we look for attributes (columns) where knowing the value of one attribute uniquely determines the value of another.

Because the provided schema is already normalized into separate tables, we first combine (flatten) related tables: Enrollment, Student, Course, Professor, and Department into a single unnormalized table called Course_Registration to demonstrate how FDs are discovered. The table is given below:

Step-by-Step Method to Find Functional Dependencies

Step 1: Check Single Column Determinants

Examine individual columns to see if their values consistently point to exactly one value in another column:

• Student_ID \(\rightarrow\) Student_Name: Every time Student_ID 1001 appears, Student_Name is always “John Doe”. Student_ID uniquely determines Student_Name.
• Course_ID \(\rightarrow\) Course_Name, Prof_ID: Every time Course_ID 201 appears, the course is always “Database Systems” and taught by Prof_ID 1.
• Prof_ID \(\rightarrow\) Prof_Name: Every time Prof_ID 1 appears, Prof_Name is always “Dr. Alice Smith”.

Step 2: Test for Contradictions (Invalid FDs)

Check if an attribute produces multiple conflicting values for another attribute:

• Does Dept_Name determine Prof_Name? No. For “Computer Science”, we see two different professors (“Dr. Alice Smith” and “Dr. Bob Jones”). Therefore, \(Dept\_ Name \rightarrow Prof\_ Name\) is not valid.
• Does Student_ID determine Course_ID? No. Student 1001 is enrolled in both course 201 and course 202. Therefore, \(Student\_ ID \rightarrow Course\_ ID\) is not valid.

Step 3: Identify Composite Determinants (Primary Keys)

When a single column cannot uniquely identify a row, combine attributes to form a composite determinant:

• The combination of (Student_ID, Course_ID) uniquely identifies a specific course enrollment record for a student. Thus, \((Student\_ ID,Course\_ ID)\) determines all other attributes in the flattened row.

Step 4: Identify Transitive Dependencies

Look for chains of dependencies where \(A \rightarrow B\) and
\(B \rightarrow C\):

• Because Course_ID \(\rightarrow\) Prof_ID and Prof_ID \(\rightarrow\) Prof_Name, Course_ID indirectly determines Prof_Name. This is written as \(Course\_ ID \rightarrow Prof\_ Name\).

So, the discovered functional dependencies are:

Types of functional dependencies with examples

Functional dependencies are categorized into five main types based on how attributes relate to one another within a database schema. Using the flattened Course_Registration table from the University Database, here is a breakdown of each type:

1. Trivial Functional Dependency

A functional dependency \(X \rightarrow Y\) is trivial if \(Y\) is a subset of \(X\). It is called “trivial” because it is always automatically true if you already know a set of columns, you naturally know any individual column within that set.

• Rule: \(Y \subseteq X\)
• Example: {Student_ID, Student_Name} \(\rightarrow\) Student_Name
• Explanation: If you know both Student_ID and Student_Name, you obviously know Student_Name.

2. Non-Trivial Functional Dependency

A functional dependency \(X \rightarrow Y\) is non-trivial if \(Y\) is not a subset of \(X\). Knowing  gives you genuinely new information about . If \(X\) and \(Y\) share no attributes at all (X ∩ Y = ø)), it is called a completely non-trivial dependency.

• Rule: \(Y\) ⊄ \(X\)
• Example: Student_ID \(\rightarrow\) Student_Name
• Explanation: Student_Name is not part of Student_ID, so looking up Student_ID 1001 gives you the new information “John Doe”.

3. Full Functional Dependency

A functional dependency \(X \rightarrow Y\) is fully functionally dependent if \(Y\) depends on the entire composite set \(X\), and removing any single attribute from \(X\) breaks the dependency.

• Rule: \(Y\) cannot be determined by any sub-part of \(X\).
• Example: (Student_ID, Course_ID) \(\rightarrow\) Project_Assignment
• Explanation: To find out a student’s specific project assignment, you need both the Student_ID and the Course_ID. Neither Student_ID alone nor Course_ID alone is enough.

4. Partial Functional Dependency

A functional dependency is partial when an attribute on the right side depends on only part of a composite primary key, rather than the entire key.

• Rule: In a table with key \((A,B)\), \(A \rightarrow Y\) is a partial dependency.
• Example: (Student_ID, Course_ID) \(\rightarrow\) Course_Name
• Explanation: The primary key of the flattened table is (Student_ID, Course_ID). However, Course_ID alone is enough to determine Course_Name (e.g., 201 \(\rightarrow\) “Database Systems”). Because Course_Name relies on only half the key, it is a partial dependency.

5. Transitive Functional Dependency

A functional dependency is transitive when \(A\) determines \(B\), and \(B\) determines \(C\), causing \(A\) to indirectly determine \(C\) through a non-key attribute.

• Rule: If \(A \rightarrow B\) and \(B \rightarrow C\), then \(A \rightarrow C\).
• Example: Course_ID \(\rightarrow\) Prof_Name
• Explanation:
  1. Course_ID determines Prof_ID (Course 201 is taught by Professor 1).
  2. Prof_ID determines Prof_Name (Professor 1 is “Dr. Alice Smith”).
  3. Therefore, Course_ID indirectly determines Prof_Name through Prof_ID.

Suryateja Pericherla

Mr. P.S.Suryateja, also known as Suryateja Pericherla, is at present a Research Scholar (full-time Ph.D.) in the Dept. of Computer Science & Systems Engineering at Andhra University, Visakhapatnam. Previously worked as an Associate Professor in the Dept. of CSE at Vishnu Institute of Technology, India.

He has 14+ years of teaching experience and is an individual researcher whose research interests are Cloud Computing, Internet of Things, Computer Security, Network Security and Blockchain.

He is a member of professional societies like IEEE, ACM, CSI and ISCA. He published several research papers which are indexed by SCIE, WoS, Scopus, Springer and others.