Database Management SystemsSecond Normal Form (2NF)

Second Normal Form: Eliminating Partial Dependencies

LevelIntermediate

Duration55 mins

TopicSecond Normal Form (2NF)

5 / 5

Second Normal Form: Comprehensive Examples

Theory Meets Practice

You've mastered the theory of Second Normal Form: partial dependencies, formal definitions, detection algorithms, and decomposition techniques. Now it's time to cement that knowledge through comprehensive examples drawn from real-world domains.

Each example in this page follows the complete normalization workflow: analyzing the original schema, identifying violations, performing decomposition, and verifying the result. These examples represent common patterns you'll encounter in production databases.

What You Will Master

By working through these examples, you will reinforce your 2NF skills across diverse domains, recognize common patterns that lead to 2NF violations, gain confidence in applying the normalization process end-to-end, and develop intuition for spotting violations quickly.

Example 1: E-Commerce Order System

Scenario: An e-commerce company stores order line items in a single denormalized table.

Original Schema:

OrderLineItem(
    OrderID,
    ProductID,
    OrderDate,
    CustomerID,
    CustomerName,
    CustomerEmail,
    ShippingAddress,
    ProductName,
    ProductCategory,
    UnitPrice,
    Quantity,
    LineTotal
)

Primary Key: {OrderID, ProductID}

Step 1: Identify Functional Dependencies

Analyzing the business rules:

Functional Dependencies Analysis
FD	Dependency	Type	Status
FD1	{OrderID, ProductID} → Quantity, LineTotal	Full	✓ OK
FD2	{OrderID} → OrderDate, CustomerID, CustomerName, CustomerEmail, ShippingAddress	Partial	✗ Violation
FD3	{ProductID} → ProductName, ProductCategory, UnitPrice	Partial	✗ Violation
FD4	{CustomerID} → CustomerName, CustomerEmail	Transitive	3NF issue

Step 2: Identify 2NF Violations

Partial Dependencies Found:

{OrderID} → OrderDate, CustomerID, CustomerName, CustomerEmail, ShippingAddress
{ProductID} → ProductName, ProductCategory, UnitPrice

Note: CustomerID → CustomerName, CustomerEmail is transitive (we'll address this during 3NF, but we can proactively separate now).

Step 3: Decomposition

E-Commerce 2NF Decomposition
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
-- Original violating table had 12 columns and massive redundancy
 
-- Extracted Relation 1: Order (from partial dep on OrderID)
CREATE TABLE Orders (
    OrderID         INT PRIMARY KEY,
    OrderDate       DATE NOT NULL,
    CustomerID      INT NOT NULL,
    ShippingAddress VARCHAR(500) NOT NULL,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
 
-- Extracted Relation 2: Customer (proactive for 3NF)
CREATE TABLE Customers (
    CustomerID    INT PRIMARY KEY,
    CustomerName  VARCHAR(100) NOT NULL,
    CustomerEmail VARCHAR(100) NOT NULL UNIQUE
);
 
-- Extracted Relation 3: Product (from partial dep on ProductID)
CREATE TABLE Products (
    ProductID       INT PRIMARY KEY,
    ProductName     VARCHAR(200) NOT NULL,
    ProductCategory VARCHAR(50) NOT NULL,
    UnitPrice       DECIMAL(10,2) NOT NULL
);
 
-- Remaining: OrderLineItem (only full dependencies)
CREATE TABLE OrderLineItems (
    OrderID   INT,
    ProductID INT,
    Quantity  INT NOT NULL CHECK (Quantity > 0),
    LineTotal DECIMAL(12,2) NOT NULL,
    PRIMARY KEY (OrderID, ProductID),
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

Results of Decomposition

Before: 1 table with 12 columns, massive data repetition After: 4 tables, each in 2NF (and actually 3NF)

Storage savings: If 1000 orders average 5 products each, customer info was stored 5000 times. Now it's stored 1000 times (in Orders) or once per customer (in Customers).

Example 2: University Course Registration

Scenario: A university tracks course sections and enrollments in a combined table.

Original Schema:

CourseEnrollment(
    CourseCode,
    SectionNumber,
    Semester,
    StudentID,
    CourseName,
    Credits,
    DepartmentName,
    InstructorID,
    InstructorName,
    RoomNumber,
    StudentName,
    Major,
    Grade
)

Primary Key: {CourseCode, SectionNumber, Semester, StudentID}

Business Rules:

A course has one name, credit value, and department
Each section (course + section + semester) has one instructor and room
Each student has one name and major
Grade depends on the specific enrollment

Step 1: Analyze Functional Dependencies

•FD1: {CourseCode} → CourseName, Credits, DepartmentName — PARTIAL DEP (subset of key)
•FD2: {CourseCode, SectionNumber, Semester} → InstructorID, InstructorName, RoomNumber — PARTIAL DEP (proper subset of 4-attribute key)
•FD3: {InstructorID} → InstructorName — TRANSITIVE (3NF issue)
•FD4: {StudentID} → StudentName, Major — PARTIAL DEP (subset of key)
•FD5: {CourseCode, SectionNumber, Semester, StudentID} → Grade — FULL DEP (entire key)

Step 2: Group Partial Dependencies by Determinant

Grouping for Decomposition
Determinant	Dependent Attributes	New Relation
{CourseCode}	CourseName, Credits, DepartmentName	Course
{CourseCode, SectionNumber, Semester}	InstructorID, RoomNumber (InstructorName via transitive)	Section
{StudentID}	StudentName, Major	Student
{InstructorID}	InstructorName	Instructor (for 3NF)

Step 3: Create Normalized Schema

University 2NF Decomposition
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
-- Course: Base course information
CREATE TABLE Course (
    CourseCode     VARCHAR(10) PRIMARY KEY,
    CourseName     VARCHAR(100) NOT NULL,
    Credits        INT NOT NULL CHECK (Credits BETWEEN 1 AND 6),
    DepartmentName VARCHAR(50) NOT NULL
);
 
-- Instructor: Instructor details (proactive 3NF extraction)
CREATE TABLE Instructor (
    InstructorID   INT PRIMARY KEY,
    InstructorName VARCHAR(100) NOT NULL
);
 
-- Section: Specific offering of a course
CREATE TABLE Section (
    CourseCode    VARCHAR(10),
    SectionNumber INT,
    Semester      VARCHAR(20),  -- e.g., "Fall 2024"
    InstructorID  INT NOT NULL,
    RoomNumber    VARCHAR(20) NOT NULL,
    PRIMARY KEY (CourseCode, SectionNumber, Semester),
    FOREIGN KEY (CourseCode) REFERENCES Course(CourseCode),
    FOREIGN KEY (InstructorID) REFERENCES Instructor(InstructorID)
);
 
-- Student: Student information
CREATE TABLE Student (
    StudentID   INT PRIMARY KEY,
    StudentName VARCHAR(100) NOT NULL,
    Major       VARCHAR(50)
);
 
-- Enrollment: The relationship with full dependency
CREATE TABLE Enrollment (
    CourseCode    VARCHAR(10),
    SectionNumber INT,
    Semester      VARCHAR(20),
    StudentID     INT,
    Grade         CHAR(2),
    PRIMARY KEY (CourseCode, SectionNumber, Semester, StudentID),
    FOREIGN KEY (CourseCode, SectionNumber, Semester) 
        REFERENCES Section(CourseCode, SectionNumber, Semester),
    FOREIGN KEY (StudentID) REFERENCES Student(StudentID)
);

Hierarchical Decomposition

Notice the hierarchy: Course → Section → Enrollment. The Section foreign key to Course ensures we can't create a section for a non-existent course. The Enrollment foreign key to Section ensures valid section references. This is a common pattern when the original composite key has hierarchical meaning.

Example 3: Manufacturing Work Orders

Scenario: A manufacturing plant tracks work orders with machine and operator details.

Original Schema:

WorkOrderDetail(
    WorkOrderID,
    OperationSeq,
    MachineID,
    MachineName,
    MachineType,
    MaintenanceSchedule,
    OperatorID,
    OperatorName,
    OperatorCertifications,
    StartTime,
    EndTime,
    UnitsProduced,
    DefectCount
)

Primary Key: {WorkOrderID, OperationSeq}

Business Rules:

Each machine has a fixed name, type, and maintenance schedule
Each operator has a name and certifications
Each operation uses one machine and one operator at specific times

Analysis:

The key is {WorkOrderID, OperationSeq}. Let's check each non-key attribute:

Attribute	Determined By	Type
MachineID	{WorkOrderID, OperationSeq}	Full
MachineName	{MachineID}	Transitive (not partial!)
MachineType	{MachineID}	Transitive
MaintenanceSchedule	{MachineID}	Transitive
OperatorID	{WorkOrderID, OperationSeq}	Full
OperatorName	{OperatorID}	Transitive
OperatorCertifications	{OperatorID}	Transitive
StartTime	{WorkOrderID, OperationSeq}	Full
EndTime	{WorkOrderID, OperationSeq}	Full
UnitsProduced	{WorkOrderID, OperationSeq}	Full
DefectCount	{WorkOrderID, OperationSeq}	Full

Key Insight: This relation has NO partial dependencies!

MachineID and OperatorID depend on the full key (which machine/operator is assigned depends on both the work order AND the operation sequence). The attributes like MachineName depend on MachineID, but MachineID is NOT part of the key—so these are transitive dependencies, not partial dependencies.

Important Distinction

This example is already in 2NF! The dependencies on MachineID and OperatorID are TRANSITIVE (3NF violations), not PARTIAL (2NF violations). Partial dependencies require the determinant to be a proper subset of the KEY. Here, MachineID and OperatorID are not parts of the key.

Since it's in 2NF, should we still decompose?

Yes, but for 3NF reasons. The transitive dependencies still cause redundancy:

-- For 3NF (not 2NF, but good practice):
CREATE TABLE Machine (
    MachineID           INT PRIMARY KEY,
    MachineName         VARCHAR(100) NOT NULL,
    MachineType         VARCHAR(50) NOT NULL,
    MaintenanceSchedule VARCHAR(200)
);

CREATE TABLE Operator (
    OperatorID            INT PRIMARY KEY,
    OperatorName          VARCHAR(100) NOT NULL,
    OperatorCertifications TEXT
);

CREATE TABLE WorkOrderDetail (
    WorkOrderID   INT,
    OperationSeq  INT,
    MachineID     INT NOT NULL,
    OperatorID    INT NOT NULL,
    StartTime     TIMESTAMP NOT NULL,
    EndTime       TIMESTAMP,
    UnitsProduced INT DEFAULT 0,
    DefectCount   INT DEFAULT 0,
    PRIMARY KEY (WorkOrderID, OperationSeq),
    FOREIGN KEY (MachineID) REFERENCES Machine(MachineID),
    FOREIGN KEY (OperatorID) REFERENCES Operator(OperatorID)
);

Lesson: Not every denormalized table has 2NF violations. Always analyze the dependencies carefully before declaring a violation.

Example 4: Flight Booking System

Scenario: An airline stores booking information with flight and passenger details.

Original Schema:

Booking(
    FlightNumber,
    FlightDate,
    SeatNumber,
    DepartureAirport,
    DepartureCity,
    ArrivalAirport,
    ArrivalCity,
    DepartureTime,
    ArrivalTime,
    AircraftType,
    PassengerID,
    PassengerName,
    PassengerPassport,
    BookingClass,
    MealPreference,
    TicketPrice
)

Primary Key: {FlightNumber, FlightDate, SeatNumber}

Functional Dependency Analysis:

•{FlightNumber} → DepartureAirport, ArrivalAirport, DepartureTime, ArrivalTime, AircraftType — PARTIAL
•{DepartureAirport} → DepartureCity — Transitive
•{ArrivalAirport} → ArrivalCity — Transitive
•{FlightNumber, FlightDate} → (specific crew, actual aircraft, etc. if we tracked them) — PARTIAL
•{PassengerID} → PassengerName, PassengerPassport — But wait, is PassengerID part of the key?
•{FlightNumber, FlightDate, SeatNumber} → PassengerID, BookingClass, MealPreference, TicketPrice — FULL

Key Insight: PassengerID is NOT part of the primary key. It's a non-prime attribute that happens to also be a determinant (for PassengerName, PassengerPassport). This makes it a transitive dependency, not a partial dependency.

The actual partial dependency is:

{FlightNumber} → DepartureAirport, ArrivalAirport, DepartureTime, ArrivalTime, AircraftType

FlightNumber alone (a proper subset of the 3-attribute key) determines these attributes.

Step 2: Decomposition Plan

Decomposition Strategy
Relation	Determinant	Attributes	Reason
Flight	{FlightNumber}	DepartureAirport, ArrivalAirport, DepartureTime, ArrivalTime, AircraftType	Partial dep on FlightNumber
Airport	{AirportCode}	City	Transitive (3NF)
Passenger	{PassengerID}	PassengerName, PassengerPassport	Transitive (3NF)
Booking	{FlightNumber, FlightDate, SeatNumber}	PassengerID, BookingClass, MealPreference, TicketPrice	Full dependencies

Flight Booking Normalized Schema
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
-- Airport: Extracted for 3NF (city depends on airport code)
CREATE TABLE Airport (
    AirportCode CHAR(3) PRIMARY KEY,  -- IATA code
    City        VARCHAR(100) NOT NULL,
    Country     VARCHAR(100) NOT NULL
);
 
-- Flight: Schedule information (2NF extraction)
CREATE TABLE Flight (
    FlightNumber     VARCHAR(10) PRIMARY KEY,
    DepartureAirport CHAR(3) NOT NULL,
    ArrivalAirport   CHAR(3) NOT NULL,
    DepartureTime    TIME NOT NULL,
    ArrivalTime      TIME NOT NULL,
    AircraftType     VARCHAR(50) NOT NULL,
    FOREIGN KEY (DepartureAirport) REFERENCES Airport(AirportCode),
    FOREIGN KEY (ArrivalAirport) REFERENCES Airport(AirportCode)
);
 
-- Passenger: Traveler information (3NF extraction)
CREATE TABLE Passenger (
    PassengerID       INT PRIMARY KEY,
    PassengerName     VARCHAR(100) NOT NULL,
    PassengerPassport VARCHAR(20) NOT NULL UNIQUE
);
 
-- Booking: The actual reservation
CREATE TABLE Booking (
    FlightNumber   VARCHAR(10),
    FlightDate     DATE,
    SeatNumber     VARCHAR(5),
    PassengerID    INT NOT NULL,
    BookingClass   CHAR(1) NOT NULL,  -- F, B, E for First, Business, Economy
    MealPreference VARCHAR(20),
    TicketPrice    DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (FlightNumber, FlightDate, SeatNumber),
    FOREIGN KEY (FlightNumber) REFERENCES Flight(FlightNumber),
    FOREIGN KEY (PassengerID) REFERENCES Passenger(PassengerID)
);
 
-- Index for common queries
CREATE INDEX idx_booking_passenger ON Booking(PassengerID);
CREATE INDEX idx_booking_date ON Booking(FlightDate);

Normalization Complete

The original 16-column table is now 4 tables. Flight schedule info is stored once per flight (not repeated for every seat on every date). Airport cities are stored once. Passenger details are stored once per passenger. Only the actual booking facts are in the Booking table.

Example 5: Library Book Loans

Scenario: A library tracks book loans with a single denormalized table.

Original Schema:

BookLoan(
    BookID,
    CopyNumber,
    MemberID,
    LoanDate,
    ISBN,
    Title,
    Author,
    Publisher,
    PublicationYear,
    BranchID,
    BranchName,
    BranchAddress,
    MemberName,
    MembershipType,
    DueDate,
    ReturnDate,
    FineAmount
)

Primary Key: {BookID, CopyNumber, MemberID, LoanDate}

Analysis: This is a complex 4-attribute composite key!

Proper Subsets to Check:

With a 4-attribute key, proper subsets include:

Single attributes: {BookID}, {CopyNumber}, {MemberID}, {LoanDate}
Pairs: {BookID, CopyNumber}, {BookID, MemberID}, etc.
Triples: {BookID, CopyNumber, MemberID}, etc.

Dependencies Found:

Dependency Analysis for Library System
Determinant	Dependent Attributes	Subset of Key?	Type
{BookID}	ISBN, Title, Author, Publisher, PublicationYear	Yes (1 of 4)	PARTIAL
{BookID, CopyNumber}	BranchID	Yes (2 of 4)	PARTIAL
{BranchID}	BranchName, BranchAddress	No	Transitive
{MemberID}	MemberName, MembershipType	Yes (1 of 4)	PARTIAL
{BookID, CopyNumber, MemberID, LoanDate}	DueDate, ReturnDate, FineAmount	Full key	FULL ✓

Three Partial Dependencies Identified:

{BookID} → ISBN, Title, Author, Publisher, PublicationYear
{BookID, CopyNumber} → BranchID
{MemberID} → MemberName, MembershipType

Decomposition:

Library System Normalized Schema
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
-- Book: The bibliographic record
CREATE TABLE Book (
    BookID          INT PRIMARY KEY,
    ISBN            VARCHAR(17) UNIQUE,
    Title           VARCHAR(300) NOT NULL,
    Author          VARCHAR(200) NOT NULL,
    Publisher       VARCHAR(100),
    PublicationYear INT
);
 
-- Branch: Library locations
CREATE TABLE Branch (
    BranchID      INT PRIMARY KEY,
    BranchName    VARCHAR(100) NOT NULL,
    BranchAddress VARCHAR(300) NOT NULL
);
 
-- BookCopy: Specific physical copies
CREATE TABLE BookCopy (
    BookID     INT,
    CopyNumber INT,
    BranchID   INT NOT NULL,
    Condition  VARCHAR(20) DEFAULT 'Good',
    PRIMARY KEY (BookID, CopyNumber),
    FOREIGN KEY (BookID) REFERENCES Book(BookID),
    FOREIGN KEY (BranchID) REFERENCES Branch(BranchID)
);
 
-- Member: Library members
CREATE TABLE Member (
    MemberID       INT PRIMARY KEY,
    MemberName     VARCHAR(100) NOT NULL,
    MembershipType VARCHAR(20) NOT NULL  -- 'Standard', 'Premium', 'Student'
);
 
-- Loan: The actual loan transaction
CREATE TABLE Loan (
    BookID     INT,
    CopyNumber INT,
    MemberID   INT,
    LoanDate   DATE,
    DueDate    DATE NOT NULL,
    ReturnDate DATE,
    FineAmount DECIMAL(8,2) DEFAULT 0,
    PRIMARY KEY (BookID, CopyNumber, MemberID, LoanDate),
    FOREIGN KEY (BookID, CopyNumber) REFERENCES BookCopy(BookID, CopyNumber),
    FOREIGN KEY (MemberID) REFERENCES Member(MemberID)
);

Hierarchical Key Structure

Notice how the decomposition reveals a natural hierarchy: Book → BookCopy → Loan. BookCopy has a composite key {BookID, CopyNumber} where BookID references Book. Loan references the composite key of BookCopy. This pattern is common when composite keys have hierarchical meaning.

Example 6: Recognizing When 2NF Is Already Satisfied

Not every table with a composite key violates 2NF. Let's examine tables that are already in 2NF.

Example 6A: Pure Junction Table

StudentClub(
    StudentID,
    ClubID,
    JoinDate,
    Role
)

Primary Key: {StudentID, ClubID}

Analysis:

{StudentID} → ? Neither JoinDate nor Role depends on StudentID alone (a student can have different join dates and roles for different clubs)
{ClubID} → ? Neither JoinDate nor Role depends on ClubID alone (a club has many members with different join dates and roles)
{StudentID, ClubID} → JoinDate, Role ✓ Both depend on the full key

Conclusion: Already in 2NF! This is a well-designed junction table.

Example 6B: All Attributes Are Prime

RoomReservation(
    RoomID,
    TimeSlot,
    ReservationCode
)

Candidate Key 1: {RoomID, TimeSlot}
Candidate Key 2: {ReservationCode}

Analysis:

All three attributes are part of a candidate key (all are prime)
There are NO non-prime attributes
2NF is automatically satisfied

Example 6C: Single-Attribute Key

Employee(
    EmployeeID,
    Name,
    Department,
    Salary,
    HireDate
)

Primary Key: {EmployeeID}

Analysis:

Single-attribute key cannot have partial dependencies
2NF is automatically satisfied (check 3NF for Department → related info)

Quick 2NF Check Rules

Automatically in 2NF if: • All candidate keys are single-attribute, OR • All attributes are prime (part of some candidate key), OR • It's a pure junction table with only the composite key attributes

Might violate 2NF if: • Composite key AND non-prime attributes that describe only one entity referenced by part of the key

Verification: Confirming Your Decomposition

After decomposition, verify your work with these checks:

Check 1: Lossless Join Test

Reconstruct the original data using joins:

SQL: Verify Lossless Join
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- For the E-Commerce example
-- Original data should equal the join of normalized tables
 
WITH Reconstructed AS (
    SELECT 
        oli.OrderID, oli.ProductID,
        o.OrderDate, o.CustomerID,
        c.CustomerName, c.CustomerEmail,
        o.ShippingAddress,
        p.ProductName, p.ProductCategory, p.UnitPrice,
        oli.Quantity, oli.LineTotal
    FROM OrderLineItems oli
    JOIN Orders o ON oli.OrderID = o.OrderID
    JOIN Customers c ON o.CustomerID = c.CustomerID
    JOIN Products p ON oli.ProductID = p.ProductID
)
SELECT * FROM Reconstructed
EXCEPT
SELECT * FROM OriginalOrderLineItem;
 
-- Should return 0 rows if decomposition is lossless
 
-- Also check the reverse:
SELECT * FROM OriginalOrderLineItem
EXCEPT
SELECT * FROM Reconstructed;
 
-- Should also return 0 rows

Check 2: 2NF Compliance Test

For each resulting table, verify no partial dependencies exist:

SQL: Verify 2NF Compliance
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- For a table with composite key, check if non-key values 
-- are consistent for each key-part value
 
-- Example: Verify OrderLineItems (key: OrderID, ProductID)
-- Check if any non-key attribute depends on just OrderID
 
SELECT OrderID, COUNT(DISTINCT Quantity) AS DistinctQuantities
FROM OrderLineItems
GROUP BY OrderID
HAVING COUNT(DISTINCT ProductID) > 1  -- Multiple products per order
   AND COUNT(DISTINCT Quantity) = 1;  -- But only one quantity value
 
-- If this returns rows, Quantity might depend on just OrderID
-- (would be a 2NF violation if true)
 
-- For OrderLineItems, this should return 0 rows because
-- different products in an order have different quantities

Check 3: Dependency Preservation Test

Verify all original constraints can be enforced:

•Primary keys: Each decomposed table should have an appropriate primary key
•Foreign keys: Relationships should be enforceable via FK constraints
•Uniqueness: Any unique constraints from the original should still be enforceable
•Data integrity: Business rules should be expressible as constraints on single tables

Summary: Mastering 2NF Through Practice

Through these comprehensive examples, you've seen 2NF normalization in action across diverse domains. Let's consolidate the patterns and principles:

Key Takeaways

•Entity Mixing Pattern: Tables combining multiple entity types (Order+Product+Customer) almost always have 2NF violations. Decompose by entity.
•Transitive vs Partial: Not every dependency is partial. If the determinant isn't part of the key, it's transitive (3NF), not partial (2NF).
•Hierarchical Keys: Composite keys often represent hierarchies (Course → Section → Enrollment). Decomposition reveals and enforces this hierarchy.
•Junction Tables: Well-designed junction tables with only relationship attributes are naturally 2NF-compliant.
•Verification is Essential: Always test lossless join and dependency preservation after decomposition.
•Proactive 3NF: While addressing 2NF, it's often efficient to also extract 3NF violations (transitive dependencies).

Module Complete!

You have now mastered Second Normal Form. You understand partial dependencies, can formally define 2NF, systematically detect violations, decompose relations correctly, and have practiced with real-world examples. This knowledge is foundational for database design and prepares you for Third Normal Form and beyond.

Second Normal Form Mastered

Congratulations! You've completed the 2NF module with a thorough understanding of partial dependencies and their elimination. You can now analyze any relation for 2NF compliance and perform correct decomposition. The next step in your normalization journey is Third Normal Form, which addresses the transitive dependencies we've identified but set aside in these examples.

5 / 5

Loading learning content...

Database Management SystemsSecond Normal Form (2NF)

Second Normal Form: Eliminating Partial Dependencies

LevelIntermediate

Duration55 mins

TopicSecond Normal Form (2NF)

5 / 5

Second Normal Form: Comprehensive Examples

Theory Meets Practice

What You Will Master

Example 1: E-Commerce Order System

Scenario: An e-commerce company stores order line items in a single denormalized table.

Original Schema:

OrderLineItem(
    OrderID,
    ProductID,
    OrderDate,
    CustomerID,
    CustomerName,
    CustomerEmail,
    ShippingAddress,
    ProductName,
    ProductCategory,
    UnitPrice,
    Quantity,
    LineTotal
)

Primary Key: {OrderID, ProductID}

Step 1: Identify Functional Dependencies

Analyzing the business rules:

Functional Dependencies Analysis
FD	Dependency	Type	Status
FD1	{OrderID, ProductID} → Quantity, LineTotal	Full	✓ OK
FD2	{OrderID} → OrderDate, CustomerID, CustomerName, CustomerEmail, ShippingAddress	Partial	✗ Violation
FD3	{ProductID} → ProductName, ProductCategory, UnitPrice	Partial	✗ Violation
FD4	{CustomerID} → CustomerName, CustomerEmail	Transitive	3NF issue

Step 2: Identify 2NF Violations

Partial Dependencies Found:

{OrderID} → OrderDate, CustomerID, CustomerName, CustomerEmail, ShippingAddress
{ProductID} → ProductName, ProductCategory, UnitPrice

Note: CustomerID → CustomerName, CustomerEmail is transitive (we'll address this during 3NF, but we can proactively separate now).

Step 3: Decomposition

E-Commerce 2NF Decomposition
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
-- Original violating table had 12 columns and massive redundancy
 
-- Extracted Relation 1: Order (from partial dep on OrderID)
CREATE TABLE Orders (
    OrderID         INT PRIMARY KEY,
    OrderDate       DATE NOT NULL,
    CustomerID      INT NOT NULL,
    ShippingAddress VARCHAR(500) NOT NULL,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
 
-- Extracted Relation 2: Customer (proactive for 3NF)
CREATE TABLE Customers (
    CustomerID    INT PRIMARY KEY,
    CustomerName  VARCHAR(100) NOT NULL,
    CustomerEmail VARCHAR(100) NOT NULL UNIQUE
);
 
-- Extracted Relation 3: Product (from partial dep on ProductID)
CREATE TABLE Products (
    ProductID       INT PRIMARY KEY,
    ProductName     VARCHAR(200) NOT NULL,
    ProductCategory VARCHAR(50) NOT NULL,
    UnitPrice       DECIMAL(10,2) NOT NULL
);
 
-- Remaining: OrderLineItem (only full dependencies)
CREATE TABLE OrderLineItems (
    OrderID   INT,
    ProductID INT,
    Quantity  INT NOT NULL CHECK (Quantity > 0),
    LineTotal DECIMAL(12,2) NOT NULL,
    PRIMARY KEY (OrderID, ProductID),
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

Results of Decomposition

Before: 1 table with 12 columns, massive data repetition After: 4 tables, each in 2NF (and actually 3NF)

Storage savings: If 1000 orders average 5 products each, customer info was stored 5000 times. Now it's stored 1000 times (in Orders) or once per customer (in Customers).

Example 2: University Course Registration

Scenario: A university tracks course sections and enrollments in a combined table.

Original Schema:

CourseEnrollment(
    CourseCode,
    SectionNumber,
    Semester,
    StudentID,
    CourseName,
    Credits,
    DepartmentName,
    InstructorID,
    InstructorName,
    RoomNumber,
    StudentName,
    Major,
    Grade
)

Primary Key: {CourseCode, SectionNumber, Semester, StudentID}

Business Rules:

A course has one name, credit value, and department
Each section (course + section + semester) has one instructor and room
Each student has one name and major
Grade depends on the specific enrollment

Step 1: Analyze Functional Dependencies

•FD1: {CourseCode} → CourseName, Credits, DepartmentName — PARTIAL DEP (subset of key)
•FD2: {CourseCode, SectionNumber, Semester} → InstructorID, InstructorName, RoomNumber — PARTIAL DEP (proper subset of 4-attribute key)
•FD3: {InstructorID} → InstructorName — TRANSITIVE (3NF issue)
•FD4: {StudentID} → StudentName, Major — PARTIAL DEP (subset of key)
•FD5: {CourseCode, SectionNumber, Semester, StudentID} → Grade — FULL DEP (entire key)

Step 2: Group Partial Dependencies by Determinant

Grouping for Decomposition
Determinant	Dependent Attributes	New Relation
{CourseCode}	CourseName, Credits, DepartmentName	Course
{CourseCode, SectionNumber, Semester}	InstructorID, RoomNumber (InstructorName via transitive)	Section
{StudentID}	StudentName, Major	Student
{InstructorID}	InstructorName	Instructor (for 3NF)

Step 3: Create Normalized Schema

University 2NF Decomposition
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
-- Course: Base course information
CREATE TABLE Course (
    CourseCode     VARCHAR(10) PRIMARY KEY,
    CourseName     VARCHAR(100) NOT NULL,
    Credits        INT NOT NULL CHECK (Credits BETWEEN 1 AND 6),
    DepartmentName VARCHAR(50) NOT NULL
);
 
-- Instructor: Instructor details (proactive 3NF extraction)
CREATE TABLE Instructor (
    InstructorID   INT PRIMARY KEY,
    InstructorName VARCHAR(100) NOT NULL
);
 
-- Section: Specific offering of a course
CREATE TABLE Section (
    CourseCode    VARCHAR(10),
    SectionNumber INT,
    Semester      VARCHAR(20),  -- e.g., "Fall 2024"
    InstructorID  INT NOT NULL,
    RoomNumber    VARCHAR(20) NOT NULL,
    PRIMARY KEY (CourseCode, SectionNumber, Semester),
    FOREIGN KEY (CourseCode) REFERENCES Course(CourseCode),
    FOREIGN KEY (InstructorID) REFERENCES Instructor(InstructorID)
);
 
-- Student: Student information
CREATE TABLE Student (
    StudentID   INT PRIMARY KEY,
    StudentName VARCHAR(100) NOT NULL,
    Major       VARCHAR(50)
);
 
-- Enrollment: The relationship with full dependency
CREATE TABLE Enrollment (
    CourseCode    VARCHAR(10),
    SectionNumber INT,
    Semester      VARCHAR(20),
    StudentID     INT,
    Grade         CHAR(2),
    PRIMARY KEY (CourseCode, SectionNumber, Semester, StudentID),
    FOREIGN KEY (CourseCode, SectionNumber, Semester) 
        REFERENCES Section(CourseCode, SectionNumber, Semester),
    FOREIGN KEY (StudentID) REFERENCES Student(StudentID)
);

Hierarchical Decomposition

Example 3: Manufacturing Work Orders

Scenario: A manufacturing plant tracks work orders with machine and operator details.

Original Schema:

WorkOrderDetail(
    WorkOrderID,
    OperationSeq,
    MachineID,
    MachineName,
    MachineType,
    MaintenanceSchedule,
    OperatorID,
    OperatorName,
    OperatorCertifications,
    StartTime,
    EndTime,
    UnitsProduced,
    DefectCount
)

Primary Key: {WorkOrderID, OperationSeq}

Business Rules:

Each machine has a fixed name, type, and maintenance schedule
Each operator has a name and certifications
Each operation uses one machine and one operator at specific times

Analysis:

The key is {WorkOrderID, OperationSeq}. Let's check each non-key attribute:

Attribute	Determined By	Type
MachineID	{WorkOrderID, OperationSeq}	Full
MachineName	{MachineID}	Transitive (not partial!)
MachineType	{MachineID}	Transitive
MaintenanceSchedule	{MachineID}	Transitive
OperatorID	{WorkOrderID, OperationSeq}	Full
OperatorName	{OperatorID}	Transitive
OperatorCertifications	{OperatorID}	Transitive
StartTime	{WorkOrderID, OperationSeq}	Full
EndTime	{WorkOrderID, OperationSeq}	Full
UnitsProduced	{WorkOrderID, OperationSeq}	Full
DefectCount	{WorkOrderID, OperationSeq}	Full

Key Insight: This relation has NO partial dependencies!

Important Distinction

Since it's in 2NF, should we still decompose?

Yes, but for 3NF reasons. The transitive dependencies still cause redundancy:

-- For 3NF (not 2NF, but good practice):
CREATE TABLE Machine (
    MachineID           INT PRIMARY KEY,
    MachineName         VARCHAR(100) NOT NULL,
    MachineType         VARCHAR(50) NOT NULL,
    MaintenanceSchedule VARCHAR(200)
);

CREATE TABLE Operator (
    OperatorID            INT PRIMARY KEY,
    OperatorName          VARCHAR(100) NOT NULL,
    OperatorCertifications TEXT
);

CREATE TABLE WorkOrderDetail (
    WorkOrderID   INT,
    OperationSeq  INT,
    MachineID     INT NOT NULL,
    OperatorID    INT NOT NULL,
    StartTime     TIMESTAMP NOT NULL,
    EndTime       TIMESTAMP,
    UnitsProduced INT DEFAULT 0,
    DefectCount   INT DEFAULT 0,
    PRIMARY KEY (WorkOrderID, OperationSeq),
    FOREIGN KEY (MachineID) REFERENCES Machine(MachineID),
    FOREIGN KEY (OperatorID) REFERENCES Operator(OperatorID)
);

Lesson: Not every denormalized table has 2NF violations. Always analyze the dependencies carefully before declaring a violation.

Example 4: Flight Booking System

Scenario: An airline stores booking information with flight and passenger details.

Original Schema:

Booking(
    FlightNumber,
    FlightDate,
    SeatNumber,
    DepartureAirport,
    DepartureCity,
    ArrivalAirport,
    ArrivalCity,
    DepartureTime,
    ArrivalTime,
    AircraftType,
    PassengerID,
    PassengerName,
    PassengerPassport,
    BookingClass,
    MealPreference,
    TicketPrice
)

Primary Key: {FlightNumber, FlightDate, SeatNumber}

Functional Dependency Analysis:

•{FlightNumber} → DepartureAirport, ArrivalAirport, DepartureTime, ArrivalTime, AircraftType — PARTIAL
•{DepartureAirport} → DepartureCity — Transitive
•{ArrivalAirport} → ArrivalCity — Transitive
•{FlightNumber, FlightDate} → (specific crew, actual aircraft, etc. if we tracked them) — PARTIAL
•{PassengerID} → PassengerName, PassengerPassport — But wait, is PassengerID part of the key?
•{FlightNumber, FlightDate, SeatNumber} → PassengerID, BookingClass, MealPreference, TicketPrice — FULL

The actual partial dependency is:

{FlightNumber} → DepartureAirport, ArrivalAirport, DepartureTime, ArrivalTime, AircraftType

FlightNumber alone (a proper subset of the 3-attribute key) determines these attributes.

Step 2: Decomposition Plan

Decomposition Strategy
Relation	Determinant	Attributes	Reason
Flight	{FlightNumber}	DepartureAirport, ArrivalAirport, DepartureTime, ArrivalTime, AircraftType	Partial dep on FlightNumber
Airport	{AirportCode}	City	Transitive (3NF)
Passenger	{PassengerID}	PassengerName, PassengerPassport	Transitive (3NF)
Booking	{FlightNumber, FlightDate, SeatNumber}	PassengerID, BookingClass, MealPreference, TicketPrice	Full dependencies

Flight Booking Normalized Schema
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
-- Airport: Extracted for 3NF (city depends on airport code)
CREATE TABLE Airport (
    AirportCode CHAR(3) PRIMARY KEY,  -- IATA code
    City        VARCHAR(100) NOT NULL,
    Country     VARCHAR(100) NOT NULL
);
 
-- Flight: Schedule information (2NF extraction)
CREATE TABLE Flight (
    FlightNumber     VARCHAR(10) PRIMARY KEY,
    DepartureAirport CHAR(3) NOT NULL,
    ArrivalAirport   CHAR(3) NOT NULL,
    DepartureTime    TIME NOT NULL,
    ArrivalTime      TIME NOT NULL,
    AircraftType     VARCHAR(50) NOT NULL,
    FOREIGN KEY (DepartureAirport) REFERENCES Airport(AirportCode),
    FOREIGN KEY (ArrivalAirport) REFERENCES Airport(AirportCode)
);
 
-- Passenger: Traveler information (3NF extraction)
CREATE TABLE Passenger (
    PassengerID       INT PRIMARY KEY,
    PassengerName     VARCHAR(100) NOT NULL,
    PassengerPassport VARCHAR(20) NOT NULL UNIQUE
);
 
-- Booking: The actual reservation
CREATE TABLE Booking (
    FlightNumber   VARCHAR(10),
    FlightDate     DATE,
    SeatNumber     VARCHAR(5),
    PassengerID    INT NOT NULL,
    BookingClass   CHAR(1) NOT NULL,  -- F, B, E for First, Business, Economy
    MealPreference VARCHAR(20),
    TicketPrice    DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (FlightNumber, FlightDate, SeatNumber),
    FOREIGN KEY (FlightNumber) REFERENCES Flight(FlightNumber),
    FOREIGN KEY (PassengerID) REFERENCES Passenger(PassengerID)
);
 
-- Index for common queries
CREATE INDEX idx_booking_passenger ON Booking(PassengerID);
CREATE INDEX idx_booking_date ON Booking(FlightDate);

Normalization Complete

Example 5: Library Book Loans

Scenario: A library tracks book loans with a single denormalized table.

Original Schema:

BookLoan(
    BookID,
    CopyNumber,
    MemberID,
    LoanDate,
    ISBN,
    Title,
    Author,
    Publisher,
    PublicationYear,
    BranchID,
    BranchName,
    BranchAddress,
    MemberName,
    MembershipType,
    DueDate,
    ReturnDate,
    FineAmount
)

Primary Key: {BookID, CopyNumber, MemberID, LoanDate}

Analysis: This is a complex 4-attribute composite key!

Proper Subsets to Check:

With a 4-attribute key, proper subsets include:

Single attributes: {BookID}, {CopyNumber}, {MemberID}, {LoanDate}
Pairs: {BookID, CopyNumber}, {BookID, MemberID}, etc.
Triples: {BookID, CopyNumber, MemberID}, etc.

Dependencies Found:

Dependency Analysis for Library System
Determinant	Dependent Attributes	Subset of Key?	Type
{BookID}	ISBN, Title, Author, Publisher, PublicationYear	Yes (1 of 4)	PARTIAL
{BookID, CopyNumber}	BranchID	Yes (2 of 4)	PARTIAL
{BranchID}	BranchName, BranchAddress	No	Transitive
{MemberID}	MemberName, MembershipType	Yes (1 of 4)	PARTIAL
{BookID, CopyNumber, MemberID, LoanDate}	DueDate, ReturnDate, FineAmount	Full key	FULL ✓

Three Partial Dependencies Identified:

{BookID} → ISBN, Title, Author, Publisher, PublicationYear
{BookID, CopyNumber} → BranchID
{MemberID} → MemberName, MembershipType

Decomposition:

Library System Normalized Schema
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
-- Book: The bibliographic record
CREATE TABLE Book (
    BookID          INT PRIMARY KEY,
    ISBN            VARCHAR(17) UNIQUE,
    Title           VARCHAR(300) NOT NULL,
    Author          VARCHAR(200) NOT NULL,
    Publisher       VARCHAR(100),
    PublicationYear INT
);
 
-- Branch: Library locations
CREATE TABLE Branch (
    BranchID      INT PRIMARY KEY,
    BranchName    VARCHAR(100) NOT NULL,
    BranchAddress VARCHAR(300) NOT NULL
);
 
-- BookCopy: Specific physical copies
CREATE TABLE BookCopy (
    BookID     INT,
    CopyNumber INT,
    BranchID   INT NOT NULL,
    Condition  VARCHAR(20) DEFAULT 'Good',
    PRIMARY KEY (BookID, CopyNumber),
    FOREIGN KEY (BookID) REFERENCES Book(BookID),
    FOREIGN KEY (BranchID) REFERENCES Branch(BranchID)
);
 
-- Member: Library members
CREATE TABLE Member (
    MemberID       INT PRIMARY KEY,
    MemberName     VARCHAR(100) NOT NULL,
    MembershipType VARCHAR(20) NOT NULL  -- 'Standard', 'Premium', 'Student'
);
 
-- Loan: The actual loan transaction
CREATE TABLE Loan (
    BookID     INT,
    CopyNumber INT,
    MemberID   INT,
    LoanDate   DATE,
    DueDate    DATE NOT NULL,
    ReturnDate DATE,
    FineAmount DECIMAL(8,2) DEFAULT 0,
    PRIMARY KEY (BookID, CopyNumber, MemberID, LoanDate),
    FOREIGN KEY (BookID, CopyNumber) REFERENCES BookCopy(BookID, CopyNumber),
    FOREIGN KEY (MemberID) REFERENCES Member(MemberID)
);

Hierarchical Key Structure

Example 6: Recognizing When 2NF Is Already Satisfied

Not every table with a composite key violates 2NF. Let's examine tables that are already in 2NF.

Example 6A: Pure Junction Table

StudentClub(
    StudentID,
    ClubID,
    JoinDate,
    Role
)

Primary Key: {StudentID, ClubID}

Analysis:

{StudentID} → ? Neither JoinDate nor Role depends on StudentID alone (a student can have different join dates and roles for different clubs)
{ClubID} → ? Neither JoinDate nor Role depends on ClubID alone (a club has many members with different join dates and roles)
{StudentID, ClubID} → JoinDate, Role ✓ Both depend on the full key

Conclusion: Already in 2NF! This is a well-designed junction table.

Example 6B: All Attributes Are Prime

RoomReservation(
    RoomID,
    TimeSlot,
    ReservationCode
)

Candidate Key 1: {RoomID, TimeSlot}
Candidate Key 2: {ReservationCode}

Analysis:

All three attributes are part of a candidate key (all are prime)
There are NO non-prime attributes
2NF is automatically satisfied

Example 6C: Single-Attribute Key

Employee(
    EmployeeID,
    Name,
    Department,
    Salary,
    HireDate
)

Primary Key: {EmployeeID}

Analysis:

Single-attribute key cannot have partial dependencies
2NF is automatically satisfied (check 3NF for Department → related info)

Quick 2NF Check Rules

Might violate 2NF if: • Composite key AND non-prime attributes that describe only one entity referenced by part of the key

Verification: Confirming Your Decomposition

After decomposition, verify your work with these checks:

Check 1: Lossless Join Test

Reconstruct the original data using joins:

SQL: Verify Lossless Join
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- For the E-Commerce example
-- Original data should equal the join of normalized tables
 
WITH Reconstructed AS (
    SELECT 
        oli.OrderID, oli.ProductID,
        o.OrderDate, o.CustomerID,
        c.CustomerName, c.CustomerEmail,
        o.ShippingAddress,
        p.ProductName, p.ProductCategory, p.UnitPrice,
        oli.Quantity, oli.LineTotal
    FROM OrderLineItems oli
    JOIN Orders o ON oli.OrderID = o.OrderID
    JOIN Customers c ON o.CustomerID = c.CustomerID
    JOIN Products p ON oli.ProductID = p.ProductID
)
SELECT * FROM Reconstructed
EXCEPT
SELECT * FROM OriginalOrderLineItem;
 
-- Should return 0 rows if decomposition is lossless
 
-- Also check the reverse:
SELECT * FROM OriginalOrderLineItem
EXCEPT
SELECT * FROM Reconstructed;
 
-- Should also return 0 rows

Check 2: 2NF Compliance Test

For each resulting table, verify no partial dependencies exist:

SQL: Verify 2NF Compliance
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- For a table with composite key, check if non-key values 
-- are consistent for each key-part value
 
-- Example: Verify OrderLineItems (key: OrderID, ProductID)
-- Check if any non-key attribute depends on just OrderID
 
SELECT OrderID, COUNT(DISTINCT Quantity) AS DistinctQuantities
FROM OrderLineItems
GROUP BY OrderID
HAVING COUNT(DISTINCT ProductID) > 1  -- Multiple products per order
   AND COUNT(DISTINCT Quantity) = 1;  -- But only one quantity value
 
-- If this returns rows, Quantity might depend on just OrderID
-- (would be a 2NF violation if true)
 
-- For OrderLineItems, this should return 0 rows because
-- different products in an order have different quantities

Check 3: Dependency Preservation Test

Verify all original constraints can be enforced:

•Primary keys: Each decomposed table should have an appropriate primary key
•Foreign keys: Relationships should be enforceable via FK constraints
•Uniqueness: Any unique constraints from the original should still be enforceable
•Data integrity: Business rules should be expressible as constraints on single tables

Summary: Mastering 2NF Through Practice

Through these comprehensive examples, you've seen 2NF normalization in action across diverse domains. Let's consolidate the patterns and principles:

Key Takeaways

•Entity Mixing Pattern: Tables combining multiple entity types (Order+Product+Customer) almost always have 2NF violations. Decompose by entity.
•Transitive vs Partial: Not every dependency is partial. If the determinant isn't part of the key, it's transitive (3NF), not partial (2NF).
•Hierarchical Keys: Composite keys often represent hierarchies (Course → Section → Enrollment). Decomposition reveals and enforces this hierarchy.
•Junction Tables: Well-designed junction tables with only relationship attributes are naturally 2NF-compliant.
•Verification is Essential: Always test lossless join and dependency preservation after decomposition.
•Proactive 3NF: While addressing 2NF, it's often efficient to also extract 3NF violations (transitive dependencies).

Module Complete!

Second Normal Form Mastered

5 / 5