How to Achieve Third Normal Form (3NF)

To achieve third normal form, identify all candidate keys and functional dependencies, decompose relations that violate the 3NF rule, and verify the resulting relations and joins. Do not stop after the first split: a newly created table may still contain a dependency that needs attention.

The previous lesson showed how to separate category facts from item facts. This lesson applies the same reasoning to a student registration example containing both partial and transitive dependencies.

1. State the Business Rules Before Choosing Tables

Assume the following simplified registration system:

• A student can register for many course offerings, and an offering can have many students. Each student-offering pair appears once.
• CourseID identifies a particular offering, not a catalog course repeated across terms.
• Each offering has exactly one instructor. An instructor can teach many offerings.
• Each instructor has one current office. Names and offices need not be unique.
• The system stores current assignments; teaching teams and assignment history are outside this example.

Start with one relation whose attributes contain single values, with no repeating groups:

Registration(StudentID, StudentName, CourseID, CourseTitle,
             InstructorID, InstructorName, InstructorOffice)

Under these rules, the only candidate key of Registration is (StudentID, CourseID). Neither column alone identifies a registration. The relevant dependencies are:

StudentID → StudentName
CourseID → CourseTitle, InstructorID
InstructorID → InstructorName, InstructorOffice

These are rules for all valid data, not conclusions drawn from a few sample rows. If the business rules change, reassess the keys and dependencies.

2. Remove Partial Dependencies to Reach 2NF

Second normal form requires that every non-prime attribute depend on the whole of every candidate key. A non-prime attribute belongs to no candidate key. In Registration, StudentName depends on StudentID alone, while course and instructor facts are determined by CourseID alone. These are dependencies on only part of the composite key.

Separate the student facts, the course facts, and the registration relationship:

Student(StudentID PK, StudentName)
Registration(StudentID PK/FK, CourseID PK/FK)
Course(CourseID PK, CourseTitle, InstructorID,
       InstructorName, InstructorOffice)

This is schema notation, not executable SQL. Registration has one composite primary key, (StudentID, CourseID); its two columns are also foreign keys. The Course relation still needs another check.

3. Check the New Course Table for a 3NF Violation

The dependency chain is now easier to see:

CourseID → InstructorID → InstructorName, InstructorOffice

InstructorID is not a superkey of Course because an instructor can teach several offerings. InstructorName and InstructorOffice are non-prime attributes. Storing those facts in Course would repeat them for each offering taught by that instructor.

The formal 3NF rule: for every nontrivial functional dependency X → A, either X is a superkey or A is a prime attribute. A superkey uniquely identifies a row; a prime attribute belongs to at least one candidate key. A dependency is nontrivial when A is not already part of X.

The instructor dependencies fail both alternatives in Course. Move instructor facts into their own relation and retain InstructorID as the reference.

4. Define the Final Relations and References

Under the stated dependencies, each descriptive fact is determined by a key in its own relation. No additional dependencies are assumed among the descriptive attributes. All four relations therefore satisfy 3NF.

Use primary-key and foreign-key constraints to enforce the identifiers and references. Course.InstructorID must be NOT NULL for the rule that every offering has an instructor. Both Registration columns are non-null as components of its primary key. Do not declare names or offices unique unless the business rules require it.

5. Verify Reconstruction and Dependency Preservation

The following illustrative query reconstructs the information for registered student-offering pairs:

SELECT s.StudentID,
       s.StudentName,
       c.CourseID,
       c.CourseTitle,
       i.InstructorID,
       i.InstructorName,
       i.InstructorOffice
FROM Registration AS r
JOIN Student AS s ON s.StudentID = r.StudentID
JOIN Course AS c ON c.CourseID = r.CourseID
JOIN Instructor AS i ON i.InstructorID = c.InstructorID;

The decomposition is lossless under these dependencies: each separation shares an identifier that determines the facts moved into the corresponding parent relation. Each registration joins to one student, one course, and one instructor without generating extra registration combinations. Testing a sample join is useful, but the dependencies explain why reconstruction works for all valid instances.

The design also preserves the stated functional dependencies: student facts can be checked in Student, course facts in Course, and instructor facts in Instructor. The composite key in Registration enforces one row per student-offering pair.

A student or offering with no registrations can now exist independently. Such rows do not appear in this inner-join registration report; that is the report's intended scope, not evidence that their records have been lost.

6. Test the Design Against Real Operations

• Changing an instructor's office updates one Instructor row, regardless of how many offerings the instructor teaches.
• Adding a new student does not require inventing a course registration.
• Deleting a registration does not delete its student, course, or instructor.
• An invalid student or course reference is rejected by a foreign-key constraint.
• A duplicate student-offering pair is rejected by Registration's composite key.

These checks address the dependencies in this example. Additional requirements, such as multiple instructors per offering or historical office assignments, need additional modeling.

Normalization and Performance Are Separate Checks

3NF is a useful design baseline, but it does not guarantee the fastest queries or an optimal design for every workload. Measure representative queries, inspect execution plans, and consider appropriate indexes before introducing deliberate redundancy. If denormalization is justified, document which facts are duplicated and how updates keep them consistent.

Final 3NF Checklist

1. Confirm the intended attribute domains and remove repeating groups.
2. Identify every candidate key and the functional dependencies justified by the business rules.
3. Remove partial dependencies, then test every resulting relation against the formal 3NF rule.
4. Verify lossless decomposition and dependency preservation.
5. Define constraints and test the operations that previously caused anomalies.

The next lesson discusses Codd's relational criteria. These concern the capabilities of a relational database system; they are not additional normalization stages for individual tables.

Achieve Third Normal Form - Exercise