Achieving Third Normal Form (3NF)

In earlier lessons, we explored the limitations of Second Normal Form (2NF) and learned how to eliminate partial dependencies. This lesson takes the process one step further—showing how to translate relations into Third Normal Form (3NF) to ensure data consistency, reduce redundancy, and eliminate transitive dependencies within your database schema.

Step-by-Step Process to Achieve 3NF

Normalization is a systematic process that progresses through successive stages, each addressing specific types of redundancy or dependency. To transform a relation into Third Normal Form, follow these general steps:

1. Identify Repeating Groups: Remove repeating groups and ensure that each field contains atomic (indivisible) values. After this step, the relation is in First Normal Form (1NF).
2. Identify Functional Dependencies: Determine how attributes depend on the primary key. If an attribute is only partially dependent on a composite key, separate it into a new relation. After this step, the relation is in Second Normal Form (2NF).
3. Identify and Remove Transitive Dependencies: If non-key attributes depend on other non-key attributes (instead of directly on the primary key), move those attributes to separate relations where they depend solely on the primary key. After this adjustment, the relation is in Third Normal Form (3NF).

At each normalization stage, ensure that relationships between tables are maintained through primary keys and foreign keys, which preserve referential integrity. The process often involves decomposing larger, complex tables into smaller, more focused relations that represent distinct entities.

Understanding Transitive Dependencies

A transitive dependency occurs when a non-key attribute depends on another non-key attribute rather than directly on the primary key. For example:

Student (StudentID, CourseID, InstructorName, InstructorOffice)

In the above relation, InstructorName and InstructorOffice are dependent on CourseID rather than directly on StudentID. This is a transitive dependency because one non-key attribute (CourseID) determines another non-key attribute (InstructorOffice).

To eliminate this dependency, you would separate the relation into two tables:

Student (StudentID, CourseID)
Course  (CourseID, InstructorName, InstructorOffice)

By restructuring the schema in this way, each non-key attribute now depends directly on the primary key of its respective table—achieving Third Normal Form (3NF).

Balancing Theory and Performance

While achieving 3NF enhances data integrity and prevents anomalies, over-normalization can sometimes reduce query performance by increasing the number of joins required. For operational databases (OLTP systems), 3NF provides an optimal balance between structural clarity and efficiency. However, for analytical workloads (OLAP systems), selective denormalization may be more appropriate.

Key Criteria for 3NF

A relation is in Third Normal Form if it meets both of the following conditions:

1. The relation is already in Second Normal Form (2NF).
2. Every non-key attribute depends only on the primary key and not on any other non-key attribute.

When transforming a 2NF relation into 3NF, ensure that any moved attributes retain referential connections to their original relations. This is achieved by preserving the foreign key, which allows the original user view to be reconstructed through SQL joins.

Example Join Reconstruction

SELECT s.StudentID, s.CourseID, c.InstructorName, c.InstructorOffice
FROM Student s
JOIN Course c ON s.CourseID = c.CourseID;

This query reconstructs the original user view using a join, confirming that normalization does not result in loss of information.

Conclusion

Third Normal Form (3NF) removes transitive dependencies and ensures that all attributes depend solely on the primary key. By applying these principles, database designers create structures that are logically sound, maintainable, and resistant to data anomalies. In the next lesson, we will review Codd’s 12 rules for a fully relational database system.

Achieve Third Normal Form - Exercise