DATABASE MANAGEMENT SYSTEM

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   

Functional dependencies play a crucial role in database design for several reasons:

• 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 {roll_no, 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.

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.

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.

•  

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