Database Management System (DBMS) is a system for storing, managing and organizing data efficiently. Reducing redundancy and preserving data consistency is essential for relational databases to function at their best. A key idea that helps achieve these objectives is functional dependency (FD). This idea is the foundation of database normalization and practical design.
In a relational database, a functional dependency is a link between attributes that establishes how one property uniquely identifies another. An employee’s ID, for instance, can be used to uniquely determine their name and other information in an employee database.
Building reliable and effective databases requires a thorough understanding of and adherence to functional dependencies.
What is functional dependency?
A constraint between two sets of attributes in a relation is known as functional dependency. It is denoted as X→Y, where:
- X is the determinant, and
- Y is the dependent attribute.
According to this notation, two rows in a table must have the same value for X if they also have the same value for Y. In simpler words, the value of X determines the value of Y.
Example: Consider a table with table name Students:
| StudentID | Name | Department |
| 101 | Alice | Computer |
| 102 | Bob | Electrical |
| 103 | Alice | Computer |
Here, StudentID uniquely determines the Name and Department. This can be represented as:
- StudentID → Name
- StudentID → Department
Functional dependency is commonly used in database architecture and normalisation and is vital for ensuring data consistency.
Also Read: ACID Properties in DBMS
Types of Functional Dependency
1. Full Functional Dependency:
When every attribute in the determinant is required to determine the dependent attribute, the functional dependency is said to be complete. The reliance would be broken if the determinant’s attributes were removed.
Example: Consider a table with the table name Course:
| CourseID | Instructor | Duration |
| C101 | John | 3 months |
| C102 | Sarah | 6 months |
Here, CourseID → Instructor and CourseID → Duration are full functional dependencies, as CourseID alone determines both Instructor and Duration.
2. Partial Functional Dependency:
If an attribute is determined by only a part of a composite key, the dependency is said to be partial.
Example: Consider a table Subject:
| CourseID | SubjectID | SubjectName |
| C101 | S1 | Mathematics |
| C102 | S2 | Physics |
{CourseID, SubjectID} → SubjectName is a composite key dependency, but SubjectID → SubjectName is a partial dependency as SubjectID alone determines SubjectName.
Normalisation is necessary to eliminate data anomalies and redundancies that are frequently caused by partial dependencies.
3. Transitive Dependency:
When an attribute indirectly depends on the determinant through another attribute, this is known as a transitive dependency.
Example:
| EmployeeID | DepartmentID | DepartmentName |
| E01 | D1 | HR |
| E02 | D2 | IT |
Here, EmployeeID → DepartmentID and DepartmentID → DepartmentName, implying EmployeeID → DepartmentName is a transitive dependency.
Higher stages of normalisation deal with transitive dependencies, which frequently make data linkages more difficult.
4. Multivalued Dependency
When a particular attribute, independent of other attributes, determines a set of values for another, this is known as a multivalued dependency.
Example: Consider a table Projects:
| EmployeeID | Project |
| E01 | Project A |
| E01 | Project B |
EmployeeID → Project is a multivalued dependency.
In situations where there are many-to-many links between attributes, multivalued dependencies usually arise. The Fourth Normal Form (4NF) addresses these dependencies.
5. Trivial and Non-Trivial Dependency
- Trivial Dependency: Takes place when the dependent attribute’s subset of the determinant (e.g., A → A).
- Non-Trivial Dependency: When the dependent attribute is not a subset of the determinant, this is known as non-trivial dependency (e.g., A → B).
Designing databases that are devoid of redundancies and inconsistencies requires an understanding of these differences.
Role of Functional Dependency in Normalization
The process of organising data to improve data integrity and minimise redundancy is called normalisation. By directing the conversion of relations into normal forms, functional dependencies are essential to this process.
1. First Normal Form (1NF):
Verifies that there are no repeated groups and that all attributes have atomic values.
Example:
| EmployeeID | Skills |
| E01 | Java, Python |
| E02 | C++, Python |
This table violates 1NF after Normalization:
| EmployeeID | Skill |
| E01 | Java |
| E01 | Python |
| E02 | C++ |
| E02 | Python |
2. Second Normal Form (2NF):
In 2NF, partial dependency is eliminated and all non-key attributes must depend on the entire primary key.
Example:
| CourseID | SubjectID | Instructor |
| C101 | S1 | John |
Normalization removes partial dependency:
- CourseID → Instructor
- {CourseID, SubjectID} → Subject
2NF ensures that every non-key attribute is fully functionally dependent on the primary key.
3. Third Normal Form (3NF):
The transitive dependency is eliminated in the Third Normal Form (3NF). Attributes must depend only on the primary key.
Example:
| EmployeeID | DepartmentID | DepartmentName |
Normalization splits into:
- EmployeeID → DepartmentID
- DepartmentID → DepartmentName
3NF removes needless middlemen and guarantees direct data dependency.
4. Boyce-Codd Normal Form (BCNF):
This normal form ensures each determinant is a candidate key in order to handle anomalies not covered in 3NF.
Example:
| StudentID | CourseID | Instructor |
| S1 | C1 | John |
| S2 | C1 | John |
The consistency of `CourseID → Instructor` across all records is guaranteed via BCNF.
5. Higher Normal Forms (4NF and 5NF):
- 4NF: Handles multivalued dependencies, ensuring no attribute is independently associated with multiple others unless necessary.
- 5NF: Focuses on join dependencies, ensuring the data can’t be decomposed further without losing information.
These forms are generally applied in highly specialized cases, making them less common in everyday database design.
To understand normalization deeply, read Normalization in DBMS with Examples.
Applications and Benefits of Functional Dependency
1. Effective Database Design:
- Assists with identifying the relationships among attributes.
- Directs the development of strong database schemas.
2. Redundancy Reduction:
- Reduces redundant information.
- Enhances query performance and conserves storage.
3. Data Integrity:
- Preserves legitimate associations to guarantee consistency.
4. Anomaly Resolution:
- It deals with insertion, update, and deletion anomalies.
Example- Avoiding anomalies in a table:
| OrderID | ProductName | CustomerName |
| O1 | Laptop | Alice |
| O2 | Laptop | Alice |
5. Maintenance and Scalability:
- Databases that have been properly normalised are simpler to scale and manage.
- The pattern for expanding database schemas is provided by functional dependencies.
6. Query Optimisation:
- Functional dependencies simplify queries and improve performance by effectively organising data.
Suggested Read: DBMS Interview Questions
Challenges and Limitations of Functional Dependency:
1. Complexity in Large Databases:
- As data and attributes increase, it becomes more difficult to identify dependencies.
2. Design Errors Owing to Misinterpretation:
- Ineffective database design may result from incorrectly identifying FDs.
Understanding constraints in SQL is important to avoiding design errors. Learn more here- SQL Constraints – Everything You Need to Know
3. Managing Relationships with Multivalued Dependencies:
- Maintaining relationships with multivalued dependencies can be challenging.
4. Scalability Issues:
- It can be difficult to ensure functional interdependence across distributed databases due to scalability issues.
5. Dynamic real-world data:
- The dynamic nature of real-world data makes it challenging to predefine all functional dependencies since real-world scenarios frequently entail data relationships that change over time.
6. Performance Trade-offs:
- Performance problems might occasionally arise from over-normalization based on functional dependencies, particularly for databases that have a high read volume.
Conclusion
One of the fundamental ideas in relational database theory is functional dependency. It helps with effective database architecture, minimises redundancy, and guarantees data integrity. Its types, normalisation function, and real-world applications can all be understood in order to build databases that are optimised for performance and scalability.
Although there are difficulties, they can be lessened with careful planning and study. You can enrol in our Free DBMS Course if you are a beginner and want to gain expertise in DBMS.
Suggested Read: Types of SQL Commands
