Third Normal Form (3NF) in SQL
Introduction
Third Normal Form, abbreviated as 3NF, is a major milestone in relational database normalization. It builds on First Normal Form and Second Normal Form by examining functional dependencies that remain after repeating groups and partial dependencies have been removed. In practical schemas, 3NF often separates a shared business fact that is determined by another non-key attribute. Department name, for example, belongs to a department rather than being repeated in every employee row.
The familiar explanation says that non-key attributes should not depend on other non-key attributes. That guideline is useful, but the formal rule is more precise. For every non-trivial functional dependency X -> A, either X must be a superkey or A must be a prime attribute. The prime-attribute exception is what allows some relations to satisfy 3NF while still violating the stricter Boyce-Codd Normal Form.
What Is Third Normal Form?
A relation is in 3NF when it satisfies 2NF and every non-trivial functional dependency has a superkey determinant or a prime dependent attribute. A dependency is non-trivial when the dependent attribute is not already part of the determinant. A prime attribute belongs to at least one candidate key, while a non-prime attribute belongs to none.
For everyday design, 3NF commonly means placing independent entities in their own relations. If employee identifier determines department identifier and department identifier determines department name, storing the department name on each employee creates a dependency through an intermediate fact. The department description should move to a departments table.
An Employee Table That Violates 3NF
| Employee ID | Employee name | Department ID | Department name | Location |
|---|---|---|---|---|
| 101 | Alice | 10 | IT | Chicago |
| 102 | Bob | 20 | HR | Dallas |
| 103 | Carol | 10 | IT | Chicago |
| 104 | David | 30 | Finance | Boston |
Assume employee identifier is the only candidate key and each employee belongs to one department. The dependencies include employee_id -> employee_name, department_id and department_id -> department_name, location. Department identifier is not a superkey of this employee relation, and its dependents are non-prime. Those dependencies violate 3NF.
Why Third Normal Form Matters
Without 3NF, facts about one entity are repeated in rows describing another. A large department may repeat its name and location thousands of times. A move from Chicago to Seattle requires a broad update, and any missed row leaves conflicting locations. A new department cannot be recorded until it has an employee, and deleting the final employee can remove the only description of the department.
Separating departments gives the information one authoritative owner and an independent lifecycle. Employees retain a foreign key that expresses membership. This reduces anomalies, clarifies schema ownership, and makes constraints easier to apply. It does not guarantee faster queries, because combined views require joins and physical performance depends on workload.
The Progression from 1NF to 3NF
| Normal form | Main requirement | Problem addressed |
|---|---|---|
| 1NF | Single-valued attributes and no repeating groups | Embedded or repeating collections |
| 2NF | No partial dependency of a non-prime attribute on a candidate key | Facts owned by part of a composite key |
| 3NF | Every non-trivial FD has a superkey determinant or prime dependent | Additional dependency anomalies |
| BCNF | Every non-trivial FD determinant is a superkey | Remaining determinant anomalies |
Each stage assumes the earlier structural requirements. The progression is not a mechanical exercise in creating more tables; it is an increasingly precise analysis of which keys determine which facts.
First Normal Form Prerequisite
A table containing a comma-separated course list has not yet represented each enrollment as an individual relational fact. 1NF first exposes those values as rows or related records. Only then can dependencies among student, course, and enrollment attributes be analyzed reliably.
Reaching 1NF does not establish 2NF or 3NF. The resulting rows may repeat facts determined by part of a key or by another non-key attribute.
Second Normal Form Prerequisite
2NF removes partial dependencies of non-prime attributes on candidate keys. In a student-course relation, student name moves to students and course name moves to courses, while grade remains with the full enrollment key. Every relation produced by that decomposition must then be examined for 3NF.
A relation with only single-column candidate keys automatically avoids partial dependencies but can still violate 3NF. The employee-department example illustrates this: employee identifier is one column, yet department description depends on department identifier rather than being owned by the employee key.
Functional Dependencies
A functional dependency X -> Y states that any two valid rows agreeing on X must also agree on Y. Dependencies arise from business rules, not temporary patterns in test data. Department identifier determines department name if the organization defines one authoritative department record for each identifier.
Normalization uses these dependencies to assign attributes to appropriate relations. A determinant that is a key can safely own its dependent facts; a determinant that is not a superkey requires closer analysis under the 3NF rule.
Transitive Dependencies
A transitive dependency occurs when a key determines an attribute through an intermediate determinant. In the employee relation, employee_id -> department_id and department_id -> department_name, so the employee identifier determines department name through department identifier.
This pattern commonly reveals a 3NF violation when the intermediate determinant is not a superkey and the dependent attribute is non-prime. Not every transitive dependency violates formal 3NF, however, because the prime-attribute exception can apply in schemas with overlapping candidate keys.
Full, Partial, and Transitive Dependencies
| Dependency | Meaning | Example |
|---|---|---|
| Full dependency | The complete determinant is necessary | Student and course determine grade |
| Partial dependency | Part of a candidate key determines a non-prime fact | Student alone determines student name |
| Transitive dependency | A determinant acts through an intermediate attribute | Employee determines department, which determines location |
2NF focuses on partial dependency, while 3NF applies the formal superkey-or-prime test to all non-trivial dependencies.
Formal Third Normal Form Rule
For every non-trivial functional dependency X -> A in relation R, at least one condition must hold: X is a superkey of R, or A is a prime attribute of R. If neither condition holds, the dependency violates 3NF.
This rule requires a complete list of candidate keys and relevant dependencies. Looking only at the primary key, or repeating the slogan that every non-key attribute must depend directly on it, can produce incorrect conclusions in relations with alternate or overlapping keys.
Candidate Keys in 3NF
A candidate key is a minimal attribute set that uniquely identifies each row. A table may have several candidate keys. If both employee identifier and mandatory unique email identify an employee, attributes from either key are prime, and dependencies with either determinant have a superkey on the left.
Candidate keys must reflect enforceable business rules. A nullable or reusable email address may not qualify. The key inventory determines which attributes receive the prime exception, so incorrect key assumptions lead directly to incorrect 3NF analysis.
Superkeys
A superkey is any attribute set that uniquely identifies a row. Every candidate key is a superkey, but a superkey may contain unnecessary attributes. If employee identifier is a candidate key, then the pair of employee identifier and name is also a superkey, though not a minimal one.
The formal 3NF rule asks whether a determinant is a superkey, not merely whether it is the declared primary key. Alternate candidate keys and their supersets qualify.
Prime and Non-Prime Attributes
A prime attribute belongs to at least one candidate key. A non-prime attribute belongs to no candidate key. In a registration relation with candidate keys (student_id, course_id) and registration_number, all three identifier attributes are prime, while registration date may be non-prime.
The dependent attribute's classification matters because 3NF permits a dependency with a non-superkey determinant when the dependent is prime. BCNF removes that exception.
Converting the Employee Table to 3NF
The employee relation should retain employee identifier, employee name, and department identifier. The departments relation should own department identifier, name, and location. Department identifier becomes a primary key in departments and a foreign key in employees.
The resulting dependencies have appropriate determinants: employee identifier determines employee facts and the department reference; department identifier determines department facts. Each shown determinant is a candidate key of its relation.
SQL Design Before 3NF
CREATE TABLE employees_bad (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(100) NOT NULL,
department_id INT NOT NULL,
department_name VARCHAR(100) NOT NULL,
department_location VARCHAR(100) NOT NULL
);
INSERT INTO employees_bad VALUES
(101, 'Alice', 10, 'IT', 'Chicago'),
(102, 'Bob', 20, 'HR', 'Dallas'),
(103, 'Carol', 10, 'IT', 'Chicago'),
(104, 'David', 30, 'Finance', 'Boston');
If employee identifier is the only candidate key, this table can satisfy 2NF while failing 3NF. Department descriptions are copied into every employee row.
Complete SQL Design After 3NF
CREATE TABLE departments (
department_id INT PRIMARY KEY,
department_name VARCHAR(100) NOT NULL,
department_location VARCHAR(100) NOT NULL
);
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(100) NOT NULL,
department_id INT NOT NULL,
FOREIGN KEY (department_id)
REFERENCES departments(department_id)
);
INSERT INTO departments VALUES
(10, 'IT', 'Chicago'),
(20, 'HR', 'Dallas'),
(30, 'Finance', 'Boston');
INSERT INTO employees VALUES
(101, 'Alice', 10),
(102, 'Bob', 20),
(103, 'Carol', 10),
(104, 'David', 30);
Multirow insert syntax differs across database versions. The relational design remains the same even when an Oracle implementation uses INSERT ALL or separate statements.
Retrieving Data from 3NF Tables
A join reconstructs the employee view without duplicating department facts in storage:
SELECT
e.employee_id,
e.employee_name,
d.department_name,
d.department_location
FROM employees AS e
INNER JOIN departments AS d
ON d.department_id = e.department_id
ORDER BY e.employee_id;
The employee table owns employee-specific data, departments owns department-specific data, and the foreign key connects them.
Preventing Update Anomalies
In the denormalized table, moving the IT department requires updating every IT employee row. A partial update leaves contradictory locations. In 3NF, one statement changes the authoritative department:
UPDATE departments
SET department_location = 'Seattle'
WHERE department_id = 10;
Every employee report sees Seattle through the join, reducing write work and inconsistency risk.
Preventing Insertion Anomalies
A company may create a Legal department before assigning employees. When department details exist only on employee rows, the database must invent a placeholder employee or postpone the department. The departments table supports the independent business event directly.
Preventing Deletion Anomalies
Deleting the final Finance employee should not erase the Finance department. Separate relations give the employee and department distinct lifecycles. Foreign-key actions still require deliberate choices, and an explicit department deletion remains possible only under the configured rules.
Reducing Redundancy
A department with ten thousand employees should not repeat the same name and location ten thousand times. 3NF stores the shared description once and repeats only the foreign key expressing membership. Actual storage savings vary with indexes, compression, and database overhead, but logical ownership becomes clear regardless.
Data Integrity and Constraints
Normalization organizes dependencies; constraints enforce selected rules. A department primary key protects identity, an employee foreign key rejects missing departments, unique constraints protect alternate identifiers, and checks validate domains. Transactions coordinate changes spanning several relations.
A schema can be conceptually normalized yet fail operationally if keys and references are not declared. Conversely, adding foreign keys to a denormalized table does not make it 3NF.
Database Maintenance
Clear ownership simplifies schema evolution. Adding a department contact number changes departments rather than every employee row. Retention, security, auditing, and data stewardship can also follow entity boundaries. Teams know which service or process owns each fact.
SQL Joins and Query Performance
3NF often increases the number of joins required for broad views. Relational engines are designed for joins, but performance depends on indexes, statistics, cardinality estimates, selectivity, result size, caching, and infrastructure. Primary and foreign-key access paths should be reviewed against real queries.
Normalization can make rows narrower and updates more focused, while denormalization can serve specific read shapes. Measure both reads and writes before changing a correct logical model for performance.
Third Normal Form vs Denormalization
| Consideration | 3NF design | Denormalized design |
|---|---|---|
| Shared facts | Stored with an authoritative key | May be copied or precomputed |
| Updates | Usually focused on one entity | Copies require synchronization |
| Reads | May require joins | Can serve a selected shape directly |
| Integrity | Dependencies align with relations | Needs additional controls |
| Common use | Transactional systems | Reporting, caches, and measured read needs |
Denormalization is an engineering trade-off, not automatically a defect. It should begin from a trustworthy source and document ownership, refresh, acceptable staleness, and reconciliation.
Third Normal Form vs BCNF
3NF allows a non-trivial dependency when its determinant is a superkey or its dependent attribute is prime. BCNF is stricter: every non-trivial determinant must be a superkey. The difference appears in schemas with overlapping candidate keys and prime dependents.
Standard 3NF synthesis can produce a lossless and dependency-preserving decomposition. A lossless BCNF decomposition always exists, but preserving every dependency may not be possible. This trade-off can make 3NF a practical target when direct dependency enforcement is important.
A Relation in 3NF but Not BCNF
Consider teaching(student, course, instructor). Assume each instructor teaches exactly one course, and a student takes a course with one instructor. The dependencies are (student, course) -> instructor and instructor -> course. Candidate keys are (student, course) and (student, instructor).
All three attributes are prime. The dependency from instructor to course has a determinant that is not a superkey, but its dependent course is prime, so the relation satisfies 3NF. It violates BCNF because BCNF does not allow the prime-attribute exception.
Lossless Decomposition
A decomposition is lossless when the original valid information can be reconstructed without spurious rows or missing facts. Employees and departments share department identifier, and the dependency from that identifier to department attributes supports their correct reconstruction.
Losslessness should be reasoned about formally for complex designs and tested during migration. Creating smaller tables without a valid join condition is not normalization.
Dependency Preservation
A decomposition preserves dependencies when the original functional rules can be enforced within individual resulting relations rather than by repeatedly joining them. Employee identifier determines employee attributes in employees, and department identifier determines department attributes in departments.
Dependency preservation supports simpler constraints and validation. A standard 3NF synthesis algorithm can achieve both losslessness and dependency preservation, though several valid decompositions may exist.
Surrogate Keys and 3NF
Adding an auto-generated record_id does not remove a dependency such as department_id -> department_name. The new identifier may be a convenient primary key, but business determinants still exist and must satisfy the 3NF rule.
Use surrogate keys when stable references or compact joins justify them, while continuing to enforce natural candidate keys and normalize according to actual dependencies.
Multiple Candidate Keys
A relation can have several candidate keys and satisfy 3NF. If employee identifier and employee email are both candidate keys, dependencies from either to employee name have a superkey determinant. Both identifier and email are prime.
Every candidate key must be identified before classifying attributes or applying the prime exception. Choosing one as primary does not make the others disappear.
Composite Keys
Composite keys are not inherently a 3NF problem. An enrollments relation keyed by student and course can store grade when the full pair determines it. The determinant is a candidate key, so the dependency is permitted. Violations depend on functional rules, not key width.
Historical Data
A product's current price and an order item's purchase-time price are different facts. Current price belongs to the product, while the amount charged belongs to the transaction. Storing both does not violate 3NF simply because their values sometimes match.
Historical meaning must be stated explicitly. Moving every transaction value to a current entity table can destroy invoices, auditability, and contractual history.
E-Commerce Customer Orders
An orders table that repeats customer name and email has order_id -> customer_id and customer_id -> customer details. Separating customers from orders lets one customer update apply to future operational use without rewriting every order. Historical billing or shipping snapshots may remain on the order when they describe the completed transaction.
Employee Department Management
Employee name and department reference belong to employees; department name, location, and contact data belong to departments. This familiar decomposition supports department changes and allows an empty department to exist independently.
School Management
If student identifier determines class identifier and class identifier determines class teacher, repeating the teacher on each student creates a 3NF issue. Classes should own the teacher assignment. If a class can have several teachers or assignments change over time, a separate dated class-teacher relation is required.
Banking Applications
An account row should not repeat customer name merely because it references a customer. Customers and accounts have separate identities. Joint accounts require an ownership junction table because one customer can own several accounts and one account can have several customers.
Inventory Management
Products may reference categories, while category name belongs to categories. Inventory quantity belongs to a product-warehouse relationship, not directly to category or product alone. A correct 3NF model follows each dependency to its authoritative determinant.
Software Testing Management
A test case can reference a project, but project name should not be copied into every test case. Projects own project descriptions, test cases own reusable test definitions, and execution results belong to a specific test, build, environment, and run. These boundaries keep reports and updates consistent.
Healthcare Management
A simplified patient row may reference an assigned doctor while doctor name and specialization belong to doctors. Real clinical systems often require appointments, care teams, specialties, assignments with effective dates, privacy rules, and audit history. Normalization must match those richer lifecycles rather than freeze a temporary relationship into one row.
Course Content Website
Articles may reference topics, and topics may reference courses. Article title belongs to articles, topic name to topics, and course name to courses. This creates a clear content hierarchy without repeating course and topic labels on every article.
SELECT
c.course_name,
t.topic_name,
a.article_title
FROM courses AS c
INNER JOIN topics AS t
ON t.course_id = c.course_id
INNER JOIN articles AS a
ON a.topic_id = t.topic_id
ORDER BY c.course_name, t.topic_name, a.article_title;
Third Normal Form in OLTP Systems
Transactional systems perform frequent inserts, updates, and deletes under concurrency. Banking, commerce, reservations, inventory, and employee systems benefit from authoritative entity records and focused transactions. 3NF is therefore a common OLTP baseline, although some domains require higher normal forms or deliberate exceptions.
Third Normal Form in OLAP Systems
Analytical systems prioritize large scans, aggregation, historical comparison, and convenient reporting. Star schemas often place denormalized dimensions around fact tables. This does not invalidate 3NF; it reflects a different workload. Normalized operational sources can feed dimensional warehouses through controlled transformation pipelines.
Migrating a Production Schema to 3NF
Profile the source and locate contradictions before creating target tables. If one department identifier appears with several names or locations, the migration needs an authoritative resolution rule. Build entity tables first, populate distinct validated keys, and then replace copied descriptions with foreign-key references.
Reconcile source and target using row counts, distinct keys, orphan counts, joined reports, and business totals. A compatibility view can temporarily expose the old combined shape. During cutover, control writes, apply final deltas, verify constraints, and retain a tested rollback plan. Remove obsolete copied columns after consumers move so they cannot remain competing sources of truth.
Testing a Third Normal Form Design
A diagram alone cannot prove that a decomposition works operationally. Test independent entity lifecycles first. Create a department with no employees, add several employees to it, change its location, transfer one employee, and remove the final employee. The department should remain authoritative throughout unless an explicit business rule says it must be closed. Negative tests should reject an employee referencing a missing department and duplicate values for every enforced candidate key.
Reconstruction tests should compare the original business view with joins over the new relations. Validate row counts, distinct identifiers, null behavior, and control totals, and watch for accidental row multiplication caused by incomplete join predicates. Update tests should confirm that changing a shared fact affects one authoritative row while all consuming reports display the new value. Historical tests should verify that changes to current entity data do not rewrite transaction snapshots.
Security and concurrency also matter. Users allowed to maintain employees may not be allowed to rename departments, so permissions should follow the new ownership boundaries. Concurrent updates need suitable transactions, isolation, optimistic version checks, or locking. Normalization reduces competing copies, but it does not replace application authorization or transaction design.
Business Rules and Dependencies Can Evolve
Functional dependencies are statements about the business, and those statements can change. A company may initially assign one location to each department, making department_id -> location valid. If departments later operate in several offices, location becomes a separate entity or a department-location relationship rather than one attribute. Keeping the old dependency would overwrite valid facts or force another embedded list.
Time can also change the determinant. A class identifier may determine one teacher in a simple model, but teacher assignments may vary by term. The correct determinant could become (class_id, term_id), with effective dates or assignment history stored separately. Similar changes occur when products have localized names, accounts gain joint owners, or projects receive several managers.
Document each important dependency with its assumptions and revisit it when requirements change. Schema migrations should update candidate keys, unique constraints, foreign keys, and downstream queries together. A table was not permanently certified as 3NF merely because it satisfied yesterday's rules; normalization remains valid only while its declared dependencies match the domain.
Common 3NF Mistakes
- Assuming a table in 2NF must also be in 3NF.
- Claiming that every transitive dependency violates formal 3NF.
- Ignoring alternate candidate keys and prime attributes.
- Believing a declared primary or surrogate key guarantees 3NF.
- Splitting every descriptive column without dependency evidence.
- Confusing foreign-key enforcement with normalization.
- Ignoring lossless decomposition or dependency preservation.
- Inferring dependencies from a small sample rather than business rules.
- Removing legitimate historical transaction values.
- Treating 3NF and BCNF as identical.
- Assuming 3NF automatically improves every query.
- Denormalizing without ownership and reconciliation controls.
Third Normal Form Best Practices
Verify 1NF and 2NF first, then inventory all candidate keys and classify prime attributes. Document business dependencies and test each non-trivial determinant for superkey status. If it is not a superkey, determine whether every dependent attribute is prime before declaring a violation.
Decompose around meaningful entities, define primary and alternate keys, add foreign keys, and validate losslessness. Preserve historical semantics and enforce important dependencies where practical. Test insert, update, delete, and join behavior with representative data.
After 3NF, consider BCNF where remaining determinant anomalies matter, and evaluate performance separately. Revisit the model whenever business keys, cardinalities, time rules, or ownership change.
Interview Questions and Answers
What is Third Normal Form?
3NF requires 2NF and, for every non-trivial dependency, either a superkey determinant or a prime dependent attribute.
What is a transitive dependency?
It is a determination relationship that operates through an intermediate attribute, such as employee determining department and department determining location.
Does every transitive dependency violate 3NF?
No. Formal 3NF permits certain dependencies when the dependent attribute is prime.
How does 3NF differ from BCNF?
BCNF requires every non-trivial determinant to be a superkey, while 3NF allows the prime-attribute exception.
Does a surrogate key guarantee 3NF?
No. Existing business dependencies remain and must still satisfy the formal rule.
Does 3NF guarantee faster queries?
No. It improves logical consistency, while performance depends on joins, indexes, data distribution, statistics, and workload.
Practical Exercises
- Write the dependencies in an employee-department table.
- Identify the determinant that violates 3NF.
- Decompose employees and departments and add foreign keys.
- Normalize customer details repeated in orders.
- Separate product categories from product rows.
- Identify all candidate keys in the teaching-assignment example.
- Explain why that relation satisfies 3NF but violates BCNF.
- Preserve purchase-time price while normalizing current product data.
- Validate a decomposition for lossless reconstruction.
- Review a 3NF schema for OLTP performance and indexing needs.
Quick Reference
| Concept | Meaning |
|---|---|
| 3NF | 2NF plus the superkey-or-prime condition for every non-trivial FD |
| Candidate key | Minimal attribute set uniquely identifying a row |
| Superkey | Any attribute set uniquely identifying a row |
| Prime attribute | An attribute belonging to at least one candidate key |
| Transitive dependency | Determination through an intermediate attribute |
| Lossless decomposition | Preserves the original valid information without spurious rows |
| Dependency preservation | Keeps dependencies enforceable in resulting relations |
| BCNF | A stricter form requiring every determinant to be a superkey |
Complete 3NF Workflow
- Understand the business rules and define each table's grain.
- Verify First and Second Normal Form.
- Identify every candidate key and prime attribute.
- Document all relevant functional dependencies.
- Inspect every non-trivial dependency.
- Check whether its determinant is a superkey.
- If not, check whether the dependent attribute is prime.
- Decompose actual violations around authoritative entities.
- Define primary, unique, and foreign-key constraints.
- Verify losslessness and dependency preservation.
- Migrate and reconcile production data.
- Test lifecycle operations, joins, and performance.
- Consider BCNF and controlled denormalization where justified.
Key Takeaway
Third Normal Form assigns business facts to relations according to their functional determinants. A relation must first satisfy 2NF, and every non-trivial dependency must then have a superkey determinant or a prime dependent attribute. In common schemas, this removes department, category, customer, project, and similar descriptions that are repeated through non-key references.
Reliable 3NF design requires a full candidate-key inventory, accurate business dependencies, lossless decomposition, preserved historical meaning, and enforceable relationships. It is a strong transactional baseline rather than a universal performance prescription. After correctness is established, teams can consider BCNF, indexes, analytical models, or controlled denormalization based on measured requirements.