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 44

Database Normalization Analysis

Recommended resources

Hello! In our last lesson, we focused on how the normalization process creates a network of linked tables, with a special emphasis on identifying the primary and foreign keys that form the "glue" holding your database together.

Today, we'll put that knowledge to work in a new way. Instead of creating normalized tables from scratch, you'll learn how to act as a database analyst, examining an existing set of tables to determine its level of normalization. This is a crucial skill for evaluating and improving real-world databases you might encounter. We will follow a systematic process to check for First, Second, and then Third Normal Form, justifying our assessment at each step.

1. The Systematic Approach to Analysis

To determine a table's normalization level, you can't just look at it and guess. You must follow a sequential process, checking the requirements for each normal form in order.

  1. Check for 1NF: Are all attribute values atomic (single-valued)? Are there any repeating groups?
  2. If it is in 1NF, check for 2NF: Does the table have a composite primary key? If so, are there any partial dependencies (where a non-key attribute depends on only part of the primary key)?
  3. If it is in 2NF, check for 3NF: Are there any transitive dependencies (where a non-key attribute depends on another non-key attribute)?

A table is considered to be at the highest normal form for which it satisfies all the rules. For example, a table that is in 2NF but has a transitive dependency is simply "in 2NF".

The diagram below provides a great high-level overview of this hierarchy, showing which problem each normal form is designed to solve.

Types of Normal Forms in DBMS
This chart illustrates the progression of normal forms. To reach 2NF, you must first be in 1NF and eliminate partial dependencies. To reach 3NF, you must be in 2NF and eliminate transitive dependencies.

To perform this analysis accurately, you must first be able to identify the different types of dependencies. The following reading will refresh your memory on the precise definitions of partial and transitive dependencies, which are the key things to look for when analyzing for 2NF and 3NF.

Normalization – Relational Databases

This text provides clear, formal definitions of the dependencies you need to identify.

Please read the following sections: Section 10.1.4 Partial Functional Dependencies: Focus on the definition and the diagram showing how an attribute C is partially dependent on a composite key {A, B}. Section 10.1.6 Transitive Functional Dependencies: Understand the definition and the diagram illustrating how attribute A transitively determines C via B. Section 10.4 Summary: Read the concise definitions provided for 2NF and 3NF. This ties the dependency types directly to the normal form rules.

2. Identifying Violations: A Step-by-Step Guide

With the rules and definitions clear, let's walk through the analysis process.

Step 1: Is the table in 1NF?

A table is in First Normal Form (1NF) if all its columns contain atomic values, meaning no lists, arrays, or repeating groups in any single cell. In most scenarios you'll encounter in your course, tables are presented in 1NF to begin with, but it's always the first check to make. If a table has a column with multiple values (e.g., a skills column with "Java, Python, SQL"), it is not in 1NF.

Step 2: If 1NF, is it in 2NF?

A table is in Second Normal Form (2NF) if it is in 1NF and contains no partial dependencies. This check is only relevant for tables with a composite primary key. If a table's primary key is a single attribute, it cannot have partial dependencies and is automatically in 2NF (assuming it's in 1NF).

To check for a 2NF violation:

  1. Identify the composite primary key.
  2. Examine all non-key attributes.
  3. For each non-key attribute, determine if it is functionally dependent on the entire primary key or just a part of it. If any attribute depends on only part of the key, you have a partial dependency, and the table is not in 2NF.

The image below shows a perfect example of a table with a partial dependency.

Functional Dependencies and Partial Dependency Example
In this sales table, the primary key is `{Item Code, Receipt No}`. The `Qty` depends on both the item and the receipt, so it is fully dependent. However, `Cust Name` depends only on the `Receipt No`, not the specific item. This is a partial dependency, meaning this table is not in 2NF.

Step 3: If 2NF, is it in 3NF?

A table is in Third Normal Form (3NF) if it is in 2NF and contains no transitive dependencies. A transitive dependency exists when a non-key attribute determines another non-key attribute. The dependency chain looks like this: Primary Key → Non-key Attribute A → Non-key Attribute B.

To check for a 3NF violation:

  1. Confirm the table is in 2NF.
  2. Examine the functional dependencies among the non-key attributes.
  3. If you find any non-key attribute that is a determinant for another non-key attribute, you have a transitive dependency, and the table is not in 3NF.

The following video demonstrates this entire analytical process with a clear example. The presenter is given a single table and systematically checks it against the rules for 1NF, 2NF, and 3NF, explaining the violations along the way.

Normalization Practice Exercise | Third Normal Form| Denormalization

This video provides a concise practice exercise on normalization. Pay close attention to how the presenter justifies the normalization level at each stage.

Watch the following segments to see the analysis in action: 1NF Analysis (watch this part): The presenter quickly confirms the table is in 1NF because it has no multivalued attributes. 2NF Analysis (watch this part): The presenter identifies the composite PK (Student ID, Course ID) and then points out that Student Name depends only on Student ID, violating 2NF. 3NF Analysis (watch this part): After breaking the tables into 2NF, the presenter examines the new Course table. They find a transitive dependency (Course ID → Teacher Name, and Teacher Name seems to determine other teacher attributes, although not shown, this is the implication of the structure). Correction in video: The video says Teacher Name is dependent on Course ID, and Course Name is dependent on Course ID. This is a bit simplified. A better example would be CourseID → TeacherID → TeacherName. The video's decomposition to solve this is correct, but the initial analysis could be clearer. The underlying principle is that one non-key attribute shouldn't determine another.

3. Practice Analysis Exercise

Now it's your turn to apply these steps. Consider the following table, which stores information about employees assigned to projects.

Table: PROJECT_ASSIGNMENT

ProjID EmpID ProjName EmpName EmpDept HoursWorked
P101 E55 Data Migration J. Smith IT 40
P101 E62 Data Migration A. Wong Finance 25
P205 E55 UI Redesign J. Smith IT 50
P205 E70 UI Redesign P. Jones IT 35

Assume the following:

  • A project can have many employees, and an employee can be on many projects.
  • Therefore, the primary key is the composite key: {ProjID, EmpID}.
  • ProjName is functionally dependent on ProjID.
  • EmpName and EmpDept are functionally dependent on EmpID.
  • HoursWorked is functionally dependent on the combination of ProjID and EmpID.

Your Task:
Analyze the PROJECT_ASSIGNMENT table and determine its level of normalization (1NF, 2NF, or 3NF). Provide a clear justification for your answer.

Click to see the solution and justification

Analysis:

  1. Check for 1NF: All columns in the table (ProjID, EmpID, ProjName, etc.) contain single, atomic values. There are no repeating groups. Therefore, the table is in 1NF.

  2. Check for 2NF: The table has a composite primary key: {ProjID, EmpID}. We must check for partial dependencies.

    • ProjName depends only on ProjID, which is a part of the primary key. This is a partial dependency.
    • EmpName and EmpDept depend only on EmpID, which is also a part of the primary key. This is another partial dependency.
    • Since partial dependencies exist, the table violates the rules of 2NF.

Conclusion:

The PROJECT_ASSIGNMENT table is in 1NF.

Justification: The table is in 1NF because all attributes are atomic. However, it is not in 2NF because it contains partial dependencies. Specifically, ProjName is dependent only on ProjID, and EmpName and EmpDept are dependent only on EmpID, not the full composite primary key {ProjID, EmpID}.

Conclusion

In this lesson, you've learned how to systematically analyze a table to determine its normalization level. This "diagnostic" approach is the flip side of the "synthesis" you learned previously and is essential for working with existing databases.

Key Takeaways:

  • Analysis is a sequential process: Check for 1NF, then 2NF, then 3NF. A table's level is the highest one it fully satisfies.
  • Justification is key: Stating the normalization level is not enough. You must justify your assessment by identifying the specific violation (or lack thereof), such as "no repeating groups," "a partial dependency exists," or "no transitive dependencies were found."
  • Dependencies are the clues: The entire analysis hinges on your ability to identify the primary key and all functional dependencies within the table.

You have now mastered both creating 3NF schemas and analyzing existing tables. In our final lesson on normalization, we will bring everything together by learning how to construct a complete Entity-Relationship Diagram (ERD) that accurately represents a fully normalized 3NF database design.