Skip to main content
Back to path

Welcome to a Grasp lesson

Create a free account to keep studying and create your own study path.

Lesson 34

Understanding Partial Dependencies

Recommended resources

Hello! In our last lesson, you learned how to convert unnormalized data into First Normal Form (1NF) by ensuring all data is atomic and there are no repeating groups. This is the first essential step in creating a well-structured database.

Now, we'll build on that foundation. This lesson introduces the concept of partial dependency, a more subtle type of data redundancy that can exist even in 1NF tables. Understanding partial dependency is the key to reaching the next level of database normalization: Second Normal Form (2NF). We will define what a partial dependency is and practice identifying it, specifically in tables that use a composite primary key.

1. The Pre-requisites: Composite Keys and Attribute Types

Partial dependency can only occur in a table that has a composite primary key—that is, a primary key made up of two or more attributes. Before we can find a partial dependency, we need to distinguish between two types of attributes in such a table.

A short video from the channel making IT simple explains this very clearly using a simple, abstract example.

Partial Dependency in DBMS

This video introduces the necessary terminology using a simple relation with four attributes.

Please watch the section that defines prime and non-prime attributes. Focus on how it uses the candidate key to classify the attributes.

To summarize the video's key points:

  • Prime Attributes: These are attributes that are part of any candidate key. In a table with a composite primary key {Attr1, Attr2}, both Attr1 and Attr2 are prime attributes.
  • Non-Prime Attributes: These are all other attributes in the table that are not part of any candidate key.

These definitions are crucial. The relationship between non-prime and prime attributes is exactly what defines a partial dependency.

2. What is a Partial Dependency?

Now for the main concept.

A partial dependency exists when a non-prime attribute is functionally dependent on only a part of the composite primary key, rather than the entire key.

Let's watch the next part of the same video, which provides a formal definition.

Partial Dependency in DBMS

This segment builds on the previous one to define and explain partial dependency.

Watch the section that explains how to spot a partial dependency in the example. Then, listen to the formal definition and steps for identification.

The key idea is that a non-prime attribute should need the whole primary key to be uniquely identified. If you can determine its value using just one piece of the composite key, you have a partial dependency, and with it, data redundancy.

Let's look at a more practical example.

Second Normal Form (2NF) | Database Normalization | DBMS

This video from Studytonight uses a common university scenario to illustrate partial dependency.

Watch the segment explaining the score table, which has a composite primary key. Pay close attention to the column that creates a partial dependency.

In the video's score table, the primary key is {student_id, subject_id}.

  • The marks attribute depends on the full key. You need to know both the student and the subject to find the marks. This is a full functional dependency.
  • The teacher_name attribute depends only on the subject_id. The teacher for 'Java' is the same regardless of which student is taking the course. This is a partial dependency.

This redundancy is a problem. If the Java teacher changes, you'd have to update every single row for every student enrolled in Java, risking errors and inconsistencies.

Functional Dependencies: Partial Dependency
This image illustrates a partial dependency in a CUSTOMER table. The `Name` attribute depends only on `Cust ID`, which is just one part of the composite primary key `{Cust ID, Order ID}`.

3. A Method for Identifying Partial Dependencies

You can systematically check for partial dependencies by following these steps:

  1. Identify the Primary Key: Determine the primary key of the table. If it's a single attribute, the table cannot have partial dependencies, and (if it's in 1NF) it is automatically in 2NF.
  2. Identify Attribute Types: If the primary key is composite, list your prime and non-prime attributes.
  3. Analyze Functional Dependencies: For each non-prime attribute, ask the question: "Do I need the entire composite primary key to determine the value of this attribute?"
    • If the answer is yes, the attribute is fully functionally dependent on the primary key.
    • If the answer is no, because the attribute depends on only a part of the primary key, then you have identified a partial dependency.

Let's solidify this with a reading that walks through another clear example.

Partial Dependency in DBMS with Example

This article from Hero Vired provides another excellent, practical example and reinforces the steps for identification.

First, read the "Detailed Explanation of Partial Dependency in DBMS" section. Pay attention to the Student_Grades table and how it explains that CourseName depends only on CourseID. Next, read the "Methods for Identifying Partial Dependency in Database Tables" section, which formalizes the identification process.

4. Practice Exercise

Now, let's test your ability to identify partial dependencies in a more abstract format, which is common in exams and technical documentation.

Consider a relational schema R(A, B, C, D, E) with the following functional dependencies (FDs):

  • A -> B
  • B -> E
  • C -> D

The candidate key for this relation is {A, C}.

Your task:

  1. List the prime and non-prime attributes.
  2. Examine the functional dependencies and identify which ones are partial dependencies. Explain your reasoning.
Click here for the solution

1. Prime and Non-Prime Attributes:

  • Prime Attributes: A, C (because they are part of the candidate key {A, C}).
  • Non-Prime Attributes: B, D, E.

2. Identifying Partial Dependencies:
We check each functional dependency involving a non-prime attribute as the dependent.

  • A -> B: Here, the non-prime attribute B is determined by A. Since A is only a part of the candidate key {A, C}, this is a partial dependency.

  • C -> D: Here, the non-prime attribute D is determined by C. Since C is also only a part of the candidate key {A, C}, this is also a partial dependency.

  • B -> E: This dependency shows a non-prime attribute E depending on another non-prime attribute B. This is not a partial dependency. It is a different type called a transitive dependency, which we will cover in a later lesson when we discuss Third Normal Form (3NF). For now, just know that it doesn't violate the rule we are focused on today.

So, the partial dependencies in this relation are A -> B and C -> D.

Conclusion

In this lesson, you've taken a significant step beyond 1NF. You now have the tools to analyze tables with composite keys and spot the hidden redundancies caused by partial dependencies.

Key Takeaways:

  • A partial dependency occurs when a non-prime attribute depends on only a portion of a composite primary key.
  • Tables with a single-attribute primary key cannot have partial dependencies.
  • Partial dependencies lead to data redundancy and can cause update, insertion, and delete anomalies.
  • You can identify them by separating attributes into prime and non-prime categories and then checking if any non-prime attribute can be determined by a part of the key.

Now that you can define and identify partial dependencies, you're ready for the next logical step. In our next lesson, we will formally define Second Normal Form (2NF) and learn how to resolve partial dependencies by decomposing the table into smaller, well-structured tables.