Second Normal Form (2NF) in SQL
Introduction
Second Normal Form, abbreviated as 2NF, is the next stage of relational database normalization after First Normal Form. It addresses a specific dependency problem: a non-prime attribute must not depend on only part of a candidate key. When a table uses a composite key but also stores facts owned by one component of that key, those facts are repeated for every relationship row. The repetition causes unnecessary updates and can make independent entities impossible to insert or preserve.
Consider a student enrollment table identified by (student_id, course_id). A grade can belong to the complete student-course relationship, but the student name belongs only to the student and the course name belongs only to the course. Separating students, courses, and enrollments gives each fact the key that determines it. This tutorial develops that reasoning carefully, including candidate keys, prime attributes, surrogate keys, historical data, lossless decomposition, and implementation trade-offs.
What Is Second Normal Form?
A relation is in 2NF when it already satisfies 1NF and every non-prime attribute is fully functionally dependent on every candidate key. A non-prime attribute is an attribute that is not part of any candidate key. A full dependency means the complete determinant is required; no proper subset of a candidate key can determine that non-prime attribute.
The rule matters most when candidate keys contain multiple attributes. If (student_id, course_id) identifies an enrollment, then student_name depending on student_id is a partial dependency. The student name should move to a students table. A grade that depends on the complete pair remains in enrollments.
A Table That Violates 2NF
| Student ID | Course ID | Student name | Course name | Grade |
|---|---|---|---|---|
| 101 | 201 | Alice | Java | A |
| 101 | 202 | Alice | SQL | B |
| 102 | 201 | Bob | Java | A |
| 103 | 202 | Carol | SQL | C |
Assume a student can enroll in each course once, making (student_id, course_id) a candidate key. The business rules imply student_id -> student_name, course_id -> course_name, and (student_id, course_id) -> grade. The first two dependencies use proper subsets of the composite key and determine non-prime attributes, so the relation violates 2NF.
Why Second Normal Form Matters
Partial dependencies repeat entity information inside relationship rows. A student enrolled in fifty courses repeats the same name fifty times. Renaming that student requires fifty changes, and a missed row creates conflicting data. A course cannot be recorded before its first enrollment if its only description is stored in enrollment rows. Deleting the final enrollment can accidentally remove the only known course description.
2NF separates those lifecycles. Student details are maintained once, course details are maintained once, and enrollment facts remain on the association. This improves consistency and maintainability, but it does not promise that every read becomes faster. Reconstructing a combined view requires joins, and physical performance must be measured independently.
Prerequisite: First Normal Form
A table must satisfy 1NF before 2NF analysis is meaningful. Its ordinary attributes should hold single values from defined domains, and repeating groups should be represented as rows or related tables. A student row containing Java, SQL in one courses field has not yet exposed individual enrollments to relational dependency analysis.
Converting the list to one student-course row reaches the 1NF structure, but it may still repeat the student name. This demonstrates an important progression: 1NF fixes how collections and attribute values are represented, while 2NF asks whether every non-prime fact depends on the entire candidate key.
Candidate Keys
A candidate key is a minimal set of attributes that uniquely identifies every row under the business rules. Minimal means no attribute can be removed while preserving uniqueness. In enrollments, neither student identifier nor course identifier is unique by itself, but their pair can be unique if one enrollment per student-course combination is allowed.
A table can have more than one candidate key. One is selected as the primary key, while alternate keys should normally be enforced with unique constraints. 2NF must be evaluated against every candidate key, not merely the one named in the PRIMARY KEY clause.
Composite Keys
A composite key contains two or more attributes. Composite keys naturally appear in junction tables, detail lines, and contextual identifiers. Their existence does not imply poor design and does not automatically cause a 2NF violation. The deciding question is what the non-prime attributes depend on.
CREATE TABLE enrollments (
student_id INT NOT NULL,
course_id INT NOT NULL,
grade CHAR(1),
PRIMARY KEY (student_id, course_id)
);
If grade is determined by the complete pair and neither key component alone determines it, the relation can satisfy 2NF.
Functional Dependencies
A functional dependency, written X -> Y, means that whenever two valid rows agree on X, they must also agree on Y. Dependencies come from enduring business rules, not coincidences in a small sample. Seeing one name per student in test data does not prove the rule unless the domain says a student identifier represents exactly one student identity.
Dependencies may have one attribute or a set of attributes on either side. They provide the reasoning behind normalization: facts should be stored in relations whose keys determine them according to the business model.
Full Functional Dependency
An attribute is fully functionally dependent on a composite determinant when the entire determinant is necessary. For an enrollment, (student_id, course_id) -> grade is full if neither student alone nor course alone determines the grade. The value describes the student's performance in that specific course.
Full dependency is not merely a statement that a key can identify the value. The requirement is that no proper subset of that key is sufficient under the stated rules. Relationship-specific facts such as quantity, role, hours worked, or supply price often have this form.
Partial Dependency
A partial dependency occurs when a non-prime attribute is determined by a proper subset of a candidate key. In the enrollment example, student_id -> student_name and course_id -> course_name are partial dependencies relative to the candidate key pair.
The formal emphasis on non-prime attributes matters. A dependency involving a prime attribute does not violate 2NF under the standard definition. Identifying which attributes belong to at least one candidate key is therefore part of correct analysis.
Full Dependency vs Partial Dependency
| Characteristic | Full dependency | Partial dependency |
|---|---|---|
| Determinant needed | The complete attribute set | A proper subset of a candidate key |
| Enrollment example | Student and course determine grade | Student alone determines student name |
| Compatible with 2NF | Yes | No, for a non-prime dependent |
| Typical action | Keep the fact with the relationship | Move the fact to the entity it describes |
Rules of Second Normal Form
The practical test has two requirements. First, the relation must already be in 1NF. Second, no non-prime attribute may depend on a proper subset of any candidate key. The word any prevents a surrogate or alternate key from hiding a dependency that still exists against another candidate key.
2NF does not require every non-prime attribute to depend directly on a key without intermediate dependencies. A non-prime attribute can depend on another non-key attribute while the table remains in 2NF; that situation may violate 3NF instead.
Order Items Example
Consider an order-items table keyed by (order_id, product_id) that also stores product name and quantity. Product name depends only on product identifier, while quantity belongs to the particular order-product line. The table repeats product names and violates 2NF.
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(100) NOT NULL
);
CREATE TABLE order_items (
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL,
PRIMARY KEY (order_id, product_id),
FOREIGN KEY (product_id)
REFERENCES products(product_id)
);
A complete application should also reference an orders table. Product details can now exist without an order, while quantity remains attached to the full line key.
Step-by-Step Conversion to 2NF
Start by stating the grain and verifying 1NF. Then identify every candidate key and classify attributes as prime or non-prime. Write the functional dependencies from business rules, inspect proper subsets of each composite candidate key, and mark any subset that determines a non-prime attribute.
For student enrollment, move student facts into students and course facts into courses. Retain grade in enrollments because it depends on the complete key. Add primary, alternate, and foreign-key constraints. Finally, verify that all original information can be reconstructed and that valid inserts, updates, and deletes behave as intended.
Complete SQL Implementation
CREATE TABLE students (
student_id INT PRIMARY KEY,
student_name VARCHAR(100) NOT NULL
);
CREATE TABLE courses (
course_id INT PRIMARY KEY,
course_name VARCHAR(100) NOT NULL
);
CREATE TABLE enrollments (
student_id INT NOT NULL,
course_id INT NOT NULL,
grade CHAR(1),
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id)
REFERENCES students(student_id),
FOREIGN KEY (course_id)
REFERENCES courses(course_id)
);
INSERT INTO students VALUES
(101, 'Alice'), (102, 'Bob'), (103, 'Carol');
INSERT INTO courses VALUES
(201, 'Java'), (202, 'SQL');
INSERT INTO enrollments VALUES
(101, 201, 'A'), (101, 202, 'B'),
(102, 201, 'A'), (103, 202, 'C');
Multirow insert syntax varies by database version. MySQL, PostgreSQL, and SQL Server support the shown form, while traditional Oracle workflows may use INSERT ALL or separate inserts.
Retrieving the Combined Data
Normalization changes storage ownership, not the application's ability to display combined information. Joins reconstruct the useful view from authoritative relations:
SELECT
s.student_id,
s.student_name,
c.course_id,
c.course_name,
e.grade
FROM enrollments AS e
INNER JOIN students AS s
ON s.student_id = e.student_id
INNER JOIN courses AS c
ON c.course_id = e.course_id
ORDER BY s.student_id, c.course_id;
The query returns the same business facts without storing student and course descriptions in every enrollment row.
Reducing Data Redundancy
When a student has many enrollments, repeating the name adds copies that have no independent meaning. Moving it to students creates one authoritative value. Relationship rows become narrower and contain only identifiers and facts about the relationship.
2NF does not remove every possible redundancy. Transitive dependencies, derived values, and other design problems can remain. Its scope is specifically partial dependency of non-prime attributes on candidate keys.
Preventing Update Anomalies
Before decomposition, changing Alice's name requires updating every enrollment. Missing one row leaves conflicting identities. In the 2NF design, the operation targets one student row:
UPDATE students
SET student_name = 'Alice Johnson'
WHERE student_id = 101;
All enrollment queries see the revised name through the relationship, reducing write work and synchronization logic.
Preventing Insertion Anomalies
An enrollment table that owns course descriptions cannot represent a new course until a student enrolls. Separating courses allows the business to create a course independently, then add enrollments later. The same principle applies to products before orders, projects before assignments, and warehouses before inventory arrives.
Preventing Deletion Anomalies
Deleting the last enrollment in a course should not necessarily delete the course definition. Independent tables give the enrollment and course separate lifecycles. Foreign-key actions still require deliberate choices: restrict, cascade, or set-null behavior must match the domain. Normalization prevents accidental loss through shared rows, not an explicit deletion issued against the entity table.
Single-Column Candidate Keys
If all candidate keys contain one attribute and the relation satisfies 1NF, partial dependency is impossible because a single-attribute key has no nonempty proper subset. Such a relation is therefore in 2NF. It can still have serious redundancy caused by transitive dependencies.
An employees table keyed only by employee identifier may store department identifier and department name. It can satisfy 2NF while department identifier determines department name, producing a 3NF concern. Normal forms solve different classes of dependency problems.
Composite Keys Can Already Satisfy 2NF
A composite key is not itself a violation. An enrollments table containing only student identifier, course identifier, and grade can be in 2NF if grade requires the complete pair. Likewise, an inventory quantity may require both product and warehouse, and an ownership role may require both customer and account.
Decompose according to actual dependencies, not merely because a key has several columns. Splitting a valid relationship fact away from its determinant can make the model less clear.
Multiple Candidate Keys
Suppose registrations have a unique registration number and also a unique (student_id, course_id) pair. Both are candidate keys. If student identifier determines student name, then the name depends on part of the composite candidate key even when registration number is selected as the SQL primary key.
This is why a key inventory must be completed before dependency analysis. Looking only at the primary key can incorrectly classify the relation as 2NF.
Surrogate Keys Do Not Automatically Solve 2NF
Adding an auto-generated enrollment_id can simplify references, but it does not erase the business candidate key (student_id, course_id). If the pair remains unique, it is still a candidate key and partial dependencies must be evaluated against it.
CREATE TABLE enrollments_bad (
enrollment_id INT PRIMARY KEY,
student_id INT NOT NULL,
course_id INT NOT NULL,
student_name VARCHAR(100),
course_name VARCHAR(100),
grade CHAR(1),
UNIQUE (student_id, course_id)
);
The surrogate identifier is useful operationally, but student and course names still belong in their entity tables.
Second Normal Form vs First Normal Form
| Feature | 1NF | 2NF |
|---|---|---|
| Single-valued attributes | Required | Required |
| Repeating groups removed | Required | Required |
| Candidate-key analysis | Useful | Essential |
| Partial dependencies removed | Not required | Required for non-prime attributes |
| Main focus | Relational value structure | Complete dependency on candidate keys |
Second Normal Form vs Third Normal Form
2NF removes partial dependencies, while 3NF applies broader restrictions to dependencies that determine non-prime attributes. An employees table with a single candidate key can be automatically in 2NF yet violate 3NF if department identifier determines department name.
Every 3NF relation satisfies 2NF, but a 2NF relation may still contain transitive dependency problems. Reaching 2NF should therefore be followed by another dependency review rather than treated as completion.
Transitive Dependencies
If employee_id -> department_id and department_id -> department_location, the location is transitively associated with the employee. This can exist in a 2NF relation when employee identifier is the only candidate key. Separating departments is a 3NF-oriented step.
Keeping the scopes clear avoids the common claim that 2NF removes every dependency among non-key attributes. Its exact purpose is narrower and depends on proper subsets of candidate keys.
Lossless Decomposition
A decomposition is lossless when joining the resulting relations reconstructs exactly the valid information represented by the original relation, without missing facts or creating spurious combinations. Students, courses, and enrollments preserve the original enrollment data when keys and foreign keys reflect the assumed dependencies.
For complex schemas, losslessness should be reasoned about formally and tested with representative data. Simply splitting columns into several tables does not prove that the decomposition is correct.
Dependency Preservation
A decomposition is dependency-preserving when important dependencies can be enforced within the resulting relations without joining them first. After decomposition, student_id -> student_name belongs in students, course_id -> course_name belongs in courses, and the enrollment key determines grade within enrollments.
Keys, unique constraints, checks, and foreign keys can then protect much of the model directly. Some complex business dependencies may still require application logic, triggers, or deferred validation.
SQL Joins and Performance
2NF commonly introduces joins because entity and relationship facts no longer share one row. The trade-off is deliberate: writes target authoritative facts, while reads compose the required view. Appropriate indexes on primary and foreign keys help the optimizer access those relationships efficiently.
Normalization may produce smaller relationship rows and more focused indexes, but additional table access can cost work. Measure representative queries and updates. Do not denormalize prematurely, and do not promise that 2NF alone improves every workload.
Employee Project Assignments
An assignment table keyed by employee and project may store employee name, project name, and hours worked. Employee name depends only on employee identifier, project name only on project identifier, and hours worked on the pair. The 2NF design separates employees, projects, and employee-project assignments.
Inventory Management
Inventory often uses (product_id, warehouse_id) as a key. Product name belongs to products, warehouse name belongs to warehouses, and stock quantity belongs to the combination. Separating these facts lets a warehouse or product exist before stock is recorded and prevents their descriptions from being repeated for every location.
School Management
A student-subject result table can place student and subject descriptions in entity relations while marks remain on the association. If students can take the same subject in several semesters or attempts, the candidate key must include the relevant term or attempt identifier. Correct key selection must reflect the actual business lifecycle.
Software Testing Management
A compatibility table may be keyed by test case and browser. Test-case name depends on the test case, browser name depends on the browser, and compatibility status belongs to their combination. Separate test cases, browsers, and supported combinations make each definition independently maintainable.
Banking Account Ownership
Joint accounts create a many-to-many relationship between customers and accounts. Customer name belongs to customers, account type belongs to accounts, and ownership role belongs to the customer-account pair. The association can also store start date, authority limits, and closure date without duplicating entity details.
Course Content Management
If lesson identifiers are unique only within a course, (course_id, lesson_id) can identify a lesson. Course name depends only on course identifier and should move to courses. Lesson title and display order may remain with lessons because they describe the full locally identified lesson.
Supplier Product Pricing
A supplier-product relation illustrates full dependency clearly. Supplier name depends only on supplier identifier, product name only on product identifier, but supply price can depend on the exact pair because suppliers quote different prices for the same product. Entity facts move out while the negotiated price remains.
Historical Transaction Facts
Normalization must preserve time meaning. A product's current price may depend on product identifier, but unit_price_at_purchase belongs to an order item because it records the contractual price for that transaction. Moving it to products would rewrite history whenever the catalog price changes.
Two values that look alike can have different dependencies because they describe different moments. State each attribute's meaning before moving it solely on the basis of its name.
Many-to-Many Relationships
Many-to-many relationships commonly use a composite candidate key. 2NF provides a clean ownership test: attributes of the left entity belong in its table, attributes of the right entity belong in its table, and facts about their association remain in the junction table. This pattern applies to enrollments, assignments, ownership, inventory, compatibility, and supplier pricing.
Indexing 2NF Tables
A primary key on (student_id, course_id) usually creates an index beginning with student identifier. Queries that primarily filter by course may benefit from another index beginning with course_id:
CREATE INDEX idx_enrollments_course
ON enrollments (course_id);
The exact requirement depends on engine behavior, selectivity, workload, and execution plans. Foreign-key columns should be reviewed explicitly because database products differ in automatic index creation.
Common 2NF Mistakes
- Assuming atomic attributes in 1NF automatically satisfy 2NF.
- Treating every composite key as evidence of a violation.
- Checking only the selected primary key and ignoring alternate candidate keys.
- Believing that an auto-generated surrogate key removes business dependencies.
- Confusing partial dependencies with transitive dependencies.
- Moving every attribute into a separate table without dependency evidence.
- Inferring functional dependencies from sample rows instead of business rules.
- Removing valid historical or transaction-specific facts.
- Failing to verify lossless decomposition and preserved relationships.
- Choosing a key that ignores semesters, attempts, versions, or effective dates.
- Assuming 2NF guarantees better query performance.
- Stopping before reviewing the design for 3NF.
Second Normal Form Best Practices
Begin with a documented grain and a complete candidate-key inventory. Classify prime and non-prime attributes, write relevant dependencies, and examine every proper subset of every composite candidate key. Decompose only where a real partial dependency exists.
Use constraints to reflect the model: primary keys for selected identities, unique constraints for alternate keys, foreign keys for references, and checks for valid domains. Preserve historical facts according to their transaction context. Validate source-to-target counts and joined results during migration.
Test entity creation before relationships exist, updates to shared descriptions, removal of the final association, and duplicate relationship attempts. After 2NF, analyze transitive dependencies and performance separately.
Migrating a Production Table to 2NF
A production decomposition needs more than new CREATE TABLE statements. Profile the source first and identify conflicting copies of partially dependent facts. If the same student identifier appears with two names, the migration cannot safely choose an authoritative value without a business rule. Resolve or quarantine contradictions, define key mappings, and record how invalid or orphaned relationship rows will be handled.
Populate independent entity tables before relationship tables so foreign keys can be enabled during or immediately after the load. Use distinct business keys carefully: a blind SELECT DISTINCT can hide conflicting descriptions instead of resolving them. Reconciliation should compare original and reconstructed row counts, candidate-key uniqueness, null distributions, control totals, and representative reports. Joining the new tables should reproduce every valid source relationship exactly once.
Application cutover may use a maintenance window, a staged dual-write period, or change-data-capture replication. Dual writes introduce their own consistency risk and need monitoring, retries, and reconciliation. A compatibility view can temporarily expose the old combined shape to read-only consumers. After all writers and readers move to the normalized model, remove the obsolete descriptive columns so they cannot continue as competing sources of truth.
Candidate Keys Can Change with Business Rules
A candidate key is valid only under the current domain rules. The pair (student_id, course_id) identifies an enrollment if a student can take a course once. If retakes, semesters, campuses, or delivery modes are introduced, the grain changes. A term or attempt identifier may need to join the candidate key, and facts such as grade, instructor, and status must be reevaluated against that expanded context.
This evolution can change the classification of dependencies. Course title may still depend on course identifier, while section schedule may depend on course and term, and a final grade may depend on student, section, and attempt. Treating an old uniqueness assumption as permanent can reject valid data or overwrite history. Schema reviews should therefore connect key definitions to explicit business statements and examples.
Surrogate identifiers help keep foreign references stable when natural keys evolve, but they do not remove the need to enforce current business uniqueness. Add or revise unique constraints as rules change, migrate existing references carefully, and rerun 2NF and 3NF analysis. Normalization is a continuing modeling discipline, not a one-time certification applied to a static table.
Interview Questions and Answers
What is Second Normal Form?
2NF requires 1NF and prohibits a non-prime attribute from depending on only part of any candidate key.
What is a partial dependency?
It is a functional dependency in which a proper subset of a candidate key determines a non-prime attribute.
Does every composite key violate 2NF?
No. A composite key is valid when non-prime attributes that remain in the relation require the complete key.
Why must all candidate keys be considered?
A partial dependency can involve an alternate composite key even when a single-column surrogate key was selected as primary.
Can a 2NF table violate 3NF?
Yes. It may contain transitive or other functional dependencies prohibited by 3NF.
Does 2NF guarantee faster SQL?
No. It improves logical ownership and write consistency, while read performance depends on indexes, plans, data volume, and workload.
Practical Exercises
- Identify candidate keys in a student enrollment relation.
- Classify each enrollment attribute as prime or non-prime.
- Write the dependencies for student name, course name, and grade.
- Decompose an order-item table that repeats product names.
- Explain why quantity remains with the full order-product key.
- Analyze a surrogate-key table with an alternate composite key.
- Normalize employee-project hours and project descriptions.
- Preserve purchase-time price while separating current product price.
- Write a join that reconstructs a decomposed enrollment report.
- Review the resulting tables for transitive dependencies.
Quick Reference
| Concept | Meaning |
|---|---|
| 2NF | 1NF plus no partial dependency of a non-prime attribute on a candidate key |
| Candidate key | Minimal attribute set uniquely identifying a row |
| Prime attribute | An attribute belonging to at least one candidate key |
| Non-prime attribute | An attribute belonging to no candidate key |
| Full dependency | The complete determinant is required |
| Partial dependency | A proper subset of a candidate key determines a non-prime attribute |
| Lossless decomposition | The original valid information can be reconstructed exactly |
| 3NF | The next stage addressing broader functional dependency problems |
Complete 2NF Workflow
- Understand the business rules and state the table grain.
- Verify that the relation satisfies 1NF.
- Identify every candidate key.
- Classify prime and non-prime attributes.
- Document relevant functional dependencies.
- Inspect proper subsets of composite candidate keys.
- Move partially dependent entity facts to their owning relations.
- Keep facts requiring the full key in the relationship table.
- Define primary, unique, and foreign-key constraints.
- Verify lossless decomposition and dependency preservation.
- Migrate and reconcile production data.
- Test lifecycle operations, joins, and query plans.
- Review remaining dependencies for 3NF.
Key Takeaway
Second Normal Form separates facts that belong to individual key components from facts that belong to the complete relationship. A table must first satisfy 1NF, then every non-prime attribute must avoid partial dependency on every candidate key. The result reduces duplicated entity descriptions and prevents common update, insertion, and deletion anomalies.
Correct 2NF analysis requires more than looking at the declared primary key. Teams must identify all candidate keys, understand business dependencies, distinguish current entity data from historical transaction facts, and validate lossless decomposition. Once those conditions are met, the schema has a stronger foundation for 3NF and for measured physical optimization.