Functional Dependencies

DBMS NOTES, IOE ,BSC CSIT,BCA

FUNCTIONAL DEPENDENCIES IN DBMS 

In a relational database management system (DBMS), a functional dependency (FD) is a constraint that defines a relationship between attributes (columns) in a table. It essentially describes how the value in one or more attributes (determinant) determines the value in another attribute (dependent) within the same table.

It is denoted as X → Y, where the attribute set on the left side of the arrow, X is called Determinant, and Y is called the Dependent. 

RULE FOR FINDING FUNCTIONAL DEPENDENCY

A functional dependency X->Y in a relation holds if two different values of Y are not related to same X.

Example: 

Imagine a table storing information about Employees, with columns for EID, Name, and Department and Salary.

 

In this scenario, the EID uniquely identifies each Employee. This relationship can be expressed as a functional dependency:

EID → Name

Here two different value of name will never have same EID. Hence functional dependency exist.

Some valid functional dependencies:

• eid→ { name, department, salary },
• eid → department , Since, eid can determine the whole set above, it can determine its subset dept_name also.

Some invalid functional dependencies:

•  name → department   employees with the same name can have different departments, hence this is not a valid functional dependency.
• name → salary    

 

BENEFITS OF FUNCTIONAL DEPENDECY

Data integrity: They help ensure the consistency and accuracy of data by preventing the creation of duplicate or inconsistent records.

Data redundancy reduction: By identifying dependencies, we can avoid storing the same information in multiple places, minimizing data redundancy and storage requirements.

Normalization: They are fundamental to the process of normalization, which involves restructuring a database to eliminate anomalies (data inconsistencies) and improve efficiency.

 

ARMSTRONG'S AXIOMS/ PROPERTIES OF FUNCTIONAL DEPENDENCIES:

1. Reflexivity: If Y is a subset of X, then X→Y holds by reflexivity rule
   Example, {eid, name} → name is valid.
2. Augmentation: If X → Y is a valid dependency, then XZ → YZ is also valid by the augmentation rule.
   Example, {eid, name} → salary is valid, hence {eid, name, department} → {salary, department} is also valid.
3. Transitivity: If X → Y and Y → Z are both valid dependencies, then X→Z is also valid by the Transitivity rule.
   Example, eid → department & department → salary, then eid → salary is also valid.

 

TYPES OF FUNCTIONAL DEPENDENCIES

1. Trivial Functional Dependency

In Trivial Functional Dependency, a dependent is always a subset of the determinant. i.e. If X → Y and Y is the subset of X.

 

 

Here, {eid, name} → name is a trivial functional dependency, since the dependent name is a subset of the determinant set {eid, name}. 

2. Non-trivial Functional Dependency

In Non-trivial functional dependency, the dependent is strictly not a subset of the determinant. i.e. If X → Y and Y is not a subset of X.

 

 

Here, {eid, name} → salary is also a non-trivial functional dependency, since salary is not a subset of {eid, name} 

3. Transitive Functional Dependency

In transitive functional dependency, dependent is indirectly dependent on determinant. i.e. If a → b & b → c, then according to the axiom of transitivity, a → c. This is a transitive functional dependency.

For example,

 

Here, eid → name and name → salary. Hence, according to the axiom of transitivity, eid → salary is a valid functional dependency. This is an indirect functional dependency, hence called Transitive functional dependency.

Transitive Dependency are generally not considered good as it may cause various insertion, deletion and update anamolies .

Consider a table with columns Course, Instructor, and InstructorPhone. If Course ->Instructor (e.g., each course has one instructor) and Instructor ->InstructorPhone (e.g., each instructor has one phone number), then a transitive dependency exists: Course -> InstructorPhone. 

If you change the instructor's phone number, you must update all courses taught by that instructor.

4. Fully Functional Dependency

In full functional dependency an attribute or a set of attributes uniquely determines another attribute or set of attributes. If a relation R has attributes X, Y, Z with the dependencies X->Y and X->Z which states that those dependencies are fully functional.

Example: Supplier Table

 

supplier_id	item_id	price
1	1	540
2	1	545
1	2	200
2	2	201
1	1	540
2	2	201
3	1	542

{ supplier_id , item_id } -> price

Here above dependecy if fully functional dependency as neither supplier_id  nor item_id can uniquely determine the price but both supplier_id and item_id together can do so.

5. Partial Functional Dependency

In partial functional dependency a non key attribute depends on a part of the composite key, rather than the whole key. If a relation R has attributes X, Y, Z where X and Y are the composite key and Z is non key attribute. Then X->Z is a partial functional dependency.

Example: Student Table

name	roll_no	course
Ravi	2	DBMS
Tim	3	OS
John	5	Java

{ name , roll_no } -> course

Here course can be determined by only one key roll_no from the composite key { name , roll_no }

Note: Please study topic closure properties in dbms after this content