Partial Dependency in DBMS

A partial dependency occurs in a relation when a non-key attribute depends on only a part of a composite key, rather than on the complete key. It is a type of functional dependency that can lead to data redundancy and update anomalies. Partial dependencies are removed during Second Normal Form (2NF) normalisation.

• It can occur only when the key is composite, meaning the key contains two or more attributes.
• A non-key attribute is partially dependent when it can be determined by only one part of the composite key.

Example

The composite primary key is {Employee_ID, Project_ID}.

Here, Project_Name depends only on Project_ID:

Project_ID → Project_Name

Since Project_ID is only part of the composite key, Project_Name has a partial dependency. Hours_Worked, on the other hand, depends on the complete combination of Employee_ID and Project_ID.

Decomposition

To remove the partial dependency, separate the project information from the employee-project relationship.

1. Student_Course:

2. Course:

When Does Partial Dependency Occur?

Partial dependency occurs only when certain conditions are satisfied. These conditions help distinguish a partial dependency from a normal functional dependency.

• The key must be composite: The primary or candidate key must contain two or more attributes, such as {Student_ID, Course_ID}.
• A non-key attribute depends on part of the key: The non-key attribute must depend on a proper subset of the composite key rather than the entire key.
• The dependency must be meaningful: The part of the key must be sufficient to determine the non-key attribute. For example, if Course_ID determines Course_Name, then Course_Name does not require Student_ID to be determined.

How to Minimize Partial Dependency?

Partial dependency is minimized through normalization, particularly when converting a relation into Second Normal Form (2NF). The main goal is to ensure that every non-key attribute depends on the entire composite key, rather than only a part of it.

To achieve this:

• Identify partial dependencies: Find non-key attributes that depend on only a part of the composite key.
• Decompose the relation: Move those attributes into a separate table where the determining attribute becomes the key.
• Keep the remaining relationship: Retain the attributes that depend on the complete composite key in the original relation.

Example: Removing Partial Dependency

Consider an Employee_Project table where an employee can work on multiple projects:

The composite primary key is {Employee_ID, Project_ID}.

Here, Project_Name depends only on Project_ID:

Project_ID → Project_Name

Since Project_ID is only part of the composite key, Project_Name has a partial dependency. Hours_Worked, on the other hand, depends on the complete combination of Employee_ID and Project_ID.

Decomposition

To remove the partial dependency, separate the project information from the employee-project relationship.

1. Employee_Project

2. Project

After decomposition:

• Project_ID → Project_Name is stored in the Project table.
• Hours_Worked remains in Employee_Project because it depends on the complete {Employee_ID, Project_ID} relationship.
• The partial dependency is therefore removed.

Connection to 2NF

Removing partial dependencies is an important step in converting a relation with a composite key into Second Normal Form (2NF). A relation in 2NF must have every non-key attribute fully dependent on the entire candidate key.