Functional Dependency and Its Importance in Relational Database Design

Functional dependency was one of those database concepts that seemed abstract during my loksewa preparation until I understood its practical importance in database design. Once I grasped how it prevents data anomalies, everything about normalization made sense. This is how I finally understood it. I almost got this wrong in my exam.

Introduction to Functional Dependency

Functional Dependency (FD) is a constraint that describes the relationship between attributes in a relation. It specifies that the value of one set of attributes determines the value of another set of attributes.

I've seen this come up in multiple loksewa exams.

Here's how I understand it: Think of functional dependency like a rule that says "if you know A, you can always determine B." For example, if you know someone's student ID, you can always determine their name - the ID functionally determines the name.

Definition and Notation

Formal Definition

A functional dependency X → Y exists in relation R if, for any two tuples t1 and t2 in R: If t1[X] = t2[X], then t1[Y] = t2[Y].

Notation

X → Y: "X functionally determines Y" or "Y is functionally dependent on X".

X: Determinant (left side)
Y: Dependent (right side)

Example

In a Student relation: StudentID → StudentName (Student ID determines Student Name).

StudentID → DateOfBirth (Student ID determines Date of Birth) CourseCode → CourseName (Course Code determines Course Name).

Types of Functional Dependencies

1. Trivial Functional Dependency

Definition: A functional dependency X → Y is trivial if Y is anyway, a subset of X.

Examples:

{StudentID, StudentName} → {StudentID} (trivial)
{StudentID, StudentName} → {StudentName} (trivial)
{A, B, C} → {A, B} (trivial)

Characteristics:

Always satisfied Doesn't provide useful information. Y ⊆ X.

2. Non-trivial Functional Dependency

Definition: A functional dependency X → Y is non-trivial if Y isn't a subset of X.

I got this wrong so many times in practice.

Examples:

StudentID → StudentName (non-trivial)
{CourseCode, StudentID} → Grade (non-trivial)
EmployeeID → Salary (non-trivial)

Characteristics: Provides meaningful constraints. Used in database design.

Y ⊄ X

3. Completely Non-trivial Functional Dependency

Definition: X → Y is completely non-trivial if X ∩ Y = ∅ (no common attributes).

Example:

StudentID → StudentName (completely non-trivial) EmployeeID → Department (completely non-trivial).

4. Partial Functional Dependency

Definition: A non-prime attribute is functionally dependent on a proper subset of a candidate key. I remember struggling with this part.

I finally understood this during revision week.

Example:

Relation: StudentCourse(StudentID, CourseCode, StudentName, Grade)
Candidate Key: `{StudentID, CourseCode}`

StudentID → StudentName (partial dependency)

Problem: Causes 2NF violations

5. Full Functional Dependency

Definition: A functional dependency where the dependent attribute depends on the entire candidate key, not just a part of it.

Example:

`{StudentID, CourseCode} → Grade` (full dependency)

6. Transitive Functional Dependency

Definition: If X → Y and Y → Z, then X → Z (where Y isn't a candidate key).

Example:

StudentID → DepartmentCode
DepartmentCode → DepartmentName
that's why: StudentID → DepartmentName (transitive)

Problem: Causes 3NF violations

Armstrong's Axioms (Inference Rules)

Primary Rules

1. Now, reflexivity (trivial rule)

if y ⊆ x, then x → y

example: {a, b} → a

2. Augmentation

If X → Y, then XZ → YZ for any set Z

Example: If A → B, then AC → BC

3. Transitivity

If X → Y and Y → Z, then X → Z

Example: If A → B and B → C, then A → C

Secondary Rules (Derived)

4. Union

If X → Y and X → Z, then X → YZ

Example: If A → B and A → C, then A → BC

5. Decomposition

If X → YZ, then X → Y and X → Z

Example: If A → BC, then A → B and A → C

6. Pseudo-transitivity

If X → Y and WY → Z, then WX → Z

Closure of Attributes

Definition

The closure of a set of attributes X (denoted X+) is the set of all attributes that can be functionally determined by X.

Algorithm to Find Closure

Input: Set of attributes X, Set of FDs F
Output: X+ (closure of X)

1. Initialize X+ = X
2. Repeat until no change in X+:
   For each FD Y → Z in F:
     If Y ⊆ X+, then X+ = X+ ∪ Z
3. Return X+

Example

Relation: R(A, B, C, D, E)
FDs: `{A → BC, B → E, CD → A}`

Find (A)+:
Step 1: (A)+ = `{A}`
Step 2: A → BC, so (A)+ = `{A, B, C}`
Step 3: B → E, so (A)+ = `{A, B, C, E}`
Step 4: No more changes
Result: (A)+ = `{A, B, C, E}`

Candidate Keys and Functional Dependencies

Finding Candidate Keys

A set of attributes X is a candidate key if:

X+ = R (X determines all attributes)
No proper subset of X has this property

Algorithm

1. Find attributes that don't appear on right side of any FD
2. So, these attributes must be in every candidate key
3. Check combinations starting with these attributes
4. Find minimal sets whose basically, closure equals R

Importance in Database Design

1. Data Integrity

Consistency: Ensures data remains consistent Accuracy: Prevents contradictory information.
Reliability: Maintains data quality

2. Normalization

Functional dependencies are the foundation of normalization:

First Normal Form (1NF)

Eliminate repeating groups.

Atomic values only

Second Normal Form (2NF)

Must be in 1NF Eliminate partial dependencies.

Third Normal Form (3NF)

Must be in 2NF .

Eliminate transitive dependencies

Boyce-Codd Normal Form (BCNF)

Must be in 3NF
For every non-trivial FD X → Y, X must be a superkey

3. Anomaly Prevention

Update Anomaly

Problem: Same information stored in multiple places Solution: Proper functional dependencies ensure single source of truth

Insert Anomaly

Problem: Cannot insert certain data without other unrelated data Solution: Decomposition based on functional dependencies

Delete Anomaly

Problem: Deleting a tuple may lose other important information Solution: Proper normalization preserves all information

Practical Examples

Example 1: Student Database

Original Relation: Student(ID, Name, DeptCode, DeptName, Advisor)

My teacher explained this three times before I got it.

Functional Dependencies:
ID → Name, DeptCode, Advisor
DeptCode → DeptName

Problems:
Transitive dependency: ID → DeptCode → DeptName.
Update anomaly: Department name stored multiple times. My friend helped me understand this.

Solution:
Student(ID, Name, DeptCode, Advisor)
Department(DeptCode, DeptName)

Example 2: Employee Project

Original: EmpProject(EmpID, EmpName, ProjID, ProjName, Hours) This was my weak point during prep.

Functional Dependencies:
EmpID → EmpName
ProjID → ProjName
`{EmpID, ProjID} → Hours`

Problems:
- Partial dependencies: EmpID → EmpName, ProjID → ProjName

Solution:
Employee(EmpID, EmpName)
Project(ProjID, ProjName)
Assignment(EmpID, ProjID, Hours)

Tools for Analysis

Dependency Diagram

Visual representation showing functional dependencies

Closure Computation

Algorithm to find all implied dependencies

Minimal Cover

Smallest set of FDs equivalent to original set

I got this wrong so many times in practice.

My Study Notes

Key concept: X → Y means "X determines Y" Armstrong's axioms: Reflexivity, Augmentation, Transitivity.
Closure: All attributes determined by a set Normalization: FDs guide the decomposition process.
Anomalies: FDs help prevent update, insert, delete anomalies This is easier than it looks.

Questions from My Experience

During my exam prep, I noticed these questions keep showing up:

I bombed this topic in my first practice test.

"What does the functional dependency A → B mean?"
- Answer: Attribute A functionally determines attribute B; knowing A's value uniquely determines B's value
- Tip: This appeared in my practice tests multiple times
- Tip: Emphasize the determination relationship I remember struggling with this part.
"Find the closure of {a} given FDs: a → bc, b → d"
- Answer: {a}+ = {a, b, c, d}
- Tip: This appeared in my practice tests multiple times
- Tip: Apply FDs step by step until no new attributes added
"What is a partial functional dependency?"
- Answer: When a non-prime attribute depends on a proper subset of a candidate key
- Tip: This violates 2NF
Look, "state armstrong's three primary axioms"
- answer: reflexivity, augmentation, transitivity
- tip: these are fundamental - memorize them
"How do functional dependencies help in normalization?"
- Answer: They identify dependencies that cause anomalies and guide decomposition to eliminate them
- Tip: Connect FDs to specific normal forms

Pro tip from my experience: When working with functional dependencies, always think about the real-world meaning. If StudentID → StudentName makes sense sort of in reality, it should be a functional dependency in your database. This helps you identify the correct you know, dependencies and understand why they're important for maintaining data integrity.