Functional Dependency in DBMS

In this article Functional Dependency in DBMS we give the information about a functional dependency (FD) is a relationship between two sets of attributes in a relation (or table).

Functional Dependency in DBMS

In Database Management Systems (DBMS), a functional dependency (FD) is a relationship between two sets of attributes in a relation (or table). It expresses a constraint that if two tuples (rows) agree on the attributes on the left-hand side, they must also agree on the attributes on the right-hand side.

Definition:

Let R be a relation, and X and Y be subsets of the attributes of R.

We say:

X → Y (X functionally determines Y)

This means:

If two rows of R have the same values for attributes X, then they must have the same values for attributes Y.

Key Terms:

• X = Determinant
• Y = Dependent
• The FD X → Y means Y is functionally dependent on X.

Example:

Suppose we have a relation Student(RollNo, Name, Department).

• RollNo → Name
  Meaning: If two students have the same RollNo, they must have the same Name.
• RollNo → Department
  Meaning: If two students have the same RollNo, they must be in the same Department.

Thus, RollNo functionally determines both Name and Department.

Types of Functional Dependencies:

1. Trivial Functional Dependency

A functional dependency X → Y is trivial if Y ⊆ X.

Example:

1. • {RollNo, Name} → Name
2. Non-Trivial Functional Dependency

A dependency X → Y is non-trivial if Y is not a subset of X.

Example:

• • RollNo → Name

Fully Functional Dependency

• • A functional dependency X → Y is fully functional if Y is dependent on all of X, and not just a part of X.

For Example:

• • {RollNo, CourseCode} → Grade
    But RollNo → Grade is not true
    → So this is a fully functional dependency.

Partial Dependency

• • When an attribute is functionally dependent on a part of a composite primary key.

Example:

• • If {RollNo, Subject} → Marks
    But RollNo → StudentName
    → StudentName is partially dependent on the composite key.

Transitive Dependency

• • If X → Y and Y → Z, then X → Z is a transitive dependency.

For Example:

• • RollNo → DepartmentID
    DepartmentID → DepartmentName
    ⇒ RollNo → DepartmentName (Transitive)

Multivalued Dependency (MVD)

• • If X →→ Y, then Y is multivalued dependent on X.
  • Occurs when for a single X, there are multiple independent Y values.

Uses of Functional Dependencies in DBMS:

• Normalization: To remove redundancy and anomalies in relational schema (1NF, 2NF, 3NF, BCNF).
• Integrity Constraints: Ensures data consistency and accuracy.
• Identifying Keys: Candidate keys are found using functional dependencies.

Additional (Derived) Rules:

• Union: If X → Y and X → Z, then X → YZ
• Decomposition: If X → YZ, then X → Y and X → Z
• Pseudo-transitivity: If X → Y and YZ → W, then XZ → W

Real-Life Example:

In a University Database:

From this:

• RollNo → Name, Department, HOD
• Department → HOD (transitive: RollNo → Department → HOD)

Functional Dependency vs Key Constraint:

Conclusion:

Functional dependencies are the foundation for:

• Normalization
• Schema refinement
• Data integrity

POP- Introduction to Programming Using ‘C’

DS – Data structure Using C

OOP – Object Oriented Programming 

Java Programming

DBMS – Database Management System

RDBMS – Relational Database Management System

Join Now: Data Warehousing and Data Mining