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 36

Identifying 2NF Violations

Recommended resources

In our last lesson, we defined Second Normal Form (2NF) and its two core requirements: the table must be in 1NF, and it must contain no partial dependencies. We established that this rule is all about ensuring that every non-key attribute in a table is dependent on the entire primary key.

Today, we shift from definition to application. This lesson is dedicated to the practical skill of spotting 2NF violations. You will learn a systematic process to analyze a table, whether it's presented with sample data or as a set of abstract functional dependencies, and determine with confidence whether it adheres to 2NF. This is a crucial diagnostic skill for any database designer.

1. The Consequences of 2NF Violations

Before we learn how to find violations, let's quickly refresh our memory on why they are a problem. A partial dependency, the defining violation of 2NF, inevitably leads to data redundancy, which in turn causes update, insertion, and deletion anomalies.

The following video provides an excellent example of this.

Learn Database Normalization - 1NF, 2NF, 3NF, 4NF, 5NF

This video from Decomplexify explains how adding a partially dependent attribute (Player_Rating) to a table creates redundancy and leads to data anomalies.

Watch the segment from the start of the 2NF section up to the point where the anomalies are explained. Pay close attention to how the Player_Rating attribute only depends on the Player, not the full {Player, Item_Type} primary key, and how this causes problems.

As the video demonstrates, the Player_Rating only depends on the Player, not the item they possess. This partial dependency means the player's rating is repeated for every item in their inventory, creating three specific problems:

  • Insertion Anomaly: You can't add a new player's rating until they have at least one item.
  • Deletion Anomaly: If you delete a player's last item, their rating information is also lost.
  • Update Anomaly: To change a player's rating, you must update multiple rows, risking data inconsistency if one update fails.

Identifying and eliminating these partial dependencies is the entire point of moving to 2NF.

2. A Systematic Process for Identifying Violations

To reliably find 2NF violations, we need a methodical approach. The key is to analyze the relationship between the non-key attributes and the primary key. This requires us to first distinguish between attributes that are part of the key and those that are not.

  • Prime Attribute: An attribute that is part of any candidate key.
  • Non-Prime Attribute: An attribute that is not part of any candidate key.

A violation of 2NF occurs when a non-prime attribute is functionally dependent on only a part of a candidate key.

This next video explains a clear, three-step process for identifying these partial dependencies.

Partial Dependency in DBMS

This video from the "making IT simple" channel formally defines partial dependency and lays out a clear process for identifying it.

Watch from the start of the explanation. Focus on these three parts: The distinction between prime and non-prime attributes. The definition of partial dependency. The summary of the three-step identification process.

To summarize and formalize the process shown in the video, here are the steps to check if a table has 2NF violations:

  1. Identify all candidate keys of the table. If all candidate keys are single attributes, the table is in 2NF (as long as it's in 1NF). If there is at least one composite candidate key, proceed to the next step.
  2. Differentiate prime and non-prime attributes.
  3. Analyze the functional dependencies: For each non-prime attribute, check if it is functionally dependent on a part of a composite candidate key (i.e., a proper subset of the key). If you find even one such dependency, the table violates 2NF.

3. Example: Finding Violations in a Table

Let's apply this process to a practical example. Consider the following table for temporary staff allocations, which tracks the hours per week a staff member works at different branches.

TempStaffAllocation Table

The TempStaffAllocation table shows staff members assigned to branches and the hours they work.

Let's analyze this table for 2NF violations. The following reading walks through exactly this scenario.

NORMALIZATION NOTE & EXERCISES

This resource provides a clear, worked example of identifying 2NF violations.

Read Question 9 (a) and its accompanying "Answer" section, which uses a diagram to explain why the TempStaffAllocation table is not in 2NF. Focus on the red text annotations that identify the dependencies.

Let's break down the analysis from the document using our systematic process:

  1. Identify Candidate Key: To uniquely identify a row, we need to know which staff member and which branch they are assigned to. Therefore, the primary key is a composite key: {staffNo, branchNo}.

  2. Identify Attributes:

    • Prime Attributes: staffNo, branchNo.
    • Non-Prime Attributes: branchAddress, name, position, hoursPerWeek.

  3. Analyze Dependencies:

    • Does hoursPerWeek depend on the full key? Yes, the hours are specific to a particular staff member at a particular branch. So, {staffNo, branchNo} -> hoursPerWeek. This is a full dependency. No violation here.
    • Does name or position depend on the full key? No, a staff member's name and position are determined by their staffNo alone. The dependency is staffNo -> name, position. Since name and position are non-prime attributes dependent on only part of the primary key (staffNo), this is a partial dependency and a 2NF violation.
    • Does branchAddress depend on the full key? No, the branch address is determined by the branchNo alone. The dependency is branchNo -> branchAddress. This is another partial dependency and a 2NF violation.

Because we found two sets of partial dependencies, this table is not in 2NF.

4. Example: Finding Violations from Functional Dependencies

Sometimes you won't have a table with data, but rather a schema and a set of functional dependencies (FDs). The process remains the same.

Let's work through an example from a practice sheet.

Database Normalization – Exercise Practices on 2NF

This document provides abstract examples using letters to represent attributes, which helps in focusing purely on the normalization rules.

Read the solution for Que 1. Follow the steps of finding the candidate key, identifying prime/non-prime attributes, and then spotting the partial dependency.

Let's review the logic for Que 1:

  • Relation: R(A, B, C, D, E)
  • FDs: A -> B, B -> E, C -> D
  • Step 1: Find Candidate Key. By calculating the closure, we find (AC)+ = {A, C, B, D, E}. It contains all attributes, so the candidate key is {A, C}.
  • Step 2: Identify Attributes.
    • Prime Attributes: A, C.
    • Non-Prime Attributes: B, D, E.
  • Step 3: Analyze Dependencies.
    • The FD C -> D shows that a non-prime attribute (D) is determined by a part of the candidate key (C). This is a partial dependency. VIOLATION.
    • The FD A -> B shows that a non-prime attribute (B) is determined by a part of the candidate key (A). This is also a partial dependency. VIOLATION.
    • (The dependency B -> E is not a partial dependency, as its determinant B is not part of the candidate key. It creates a transitive dependency {A} -> {B} -> {E}, which is a 3NF issue we'll address later.)

Since the table contains partial dependencies, it is not in 2NF.

5. Practice Exercise

Now it's your turn. Use the systematic process to identify any 2NF violations in the following scenario.

Scenario:
You are given a relational schema R(A, B, C, D, E) and the following set of functional dependencies:

  • {A, B} -> C
  • A -> D
  • B -> E

Your Task:

  1. Identify the candidate key for the relation.
  2. List the prime and non-prime attributes.
  3. Identify which, if any, of the functional dependencies represent a partial dependency and therefore violate 2NF.
Click here for the solution

Solution:

  1. Candidate Key: The closure of {A, B} is (AB)+ = {A, B, C, D, E}, which includes all attributes of the relation. Therefore, the candidate key is {A, B}.

  2. Attributes:

    • Prime Attributes: A, B.
    • Non-Prime Attributes: C, D, E.

  3. 2NF Violations (Partial Dependencies):

    • A -> D: The non-prime attribute D is determined by A, which is only a part of the candidate key {A, B}. This is a partial dependency and a 2NF violation.
    • B -> E: The non-prime attribute E is determined by B, which is only a part of the candidate key {A, B}. This is also a partial dependency and a 2NF violation.
    • {A, B} -> C: The non-prime attribute C is determined by the entire candidate key {A, B}. This is a full functional dependency and does not violate 2NF.

Conclusion: The relation is not in 2NF because it contains two partial dependencies.

Conclusion

In this lesson, you have learned and practiced a systematic method for identifying 2NF violations. This process of finding the keys, classifying attributes as prime or non-prime, and then scrutinizing the dependencies is a fundamental skill in database normalization.

Key Takeaways:

  • A 2NF violation is the presence of a partial dependency.
  • A partial dependency exists when a non-prime attribute is functionally dependent on a proper subset (a part) of a composite candidate key.
  • To find violations, you must first determine the candidate key(s) of the relation.
  • Tables with only single-attribute candidate keys cannot have partial dependencies and are therefore already in 2NF (if they are in 1NF).

Now that you can confidently diagnose a 2NF violation, our next lesson will focus on the cure: you will learn how to convert a 1NF table into a set of 2NF tables by systematically removing partial dependencies.