Second Normal Form:… — Second Normal Form:… | OneNoughtOne

0/278

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} \to OrderDate, CustomerID, CustomerName, CustomerEmail, ShippingAddress {ProductID} \to ProductName, ProductCategory, UnitPrice Note: CustomerID \to 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 \to Section \to 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} \to 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} \to ISBN, Title, Author, Publisher, PublicationYear {BookID, CopyNumber} \to BranchID {MemberID} \to 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 \to BookCopy \to 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} \to ? Neither JoinDate nor Role depends on StudentID alone (a student can have different join dates and roles for different clubs) {ClubID} \to ? Neither JoinDate nor Role depends on ClubID alone (a club has many members with different join dates and roles) {StudentID, ClubID} \to 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 \to 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

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} \to OrderDate, CustomerID, CustomerName, CustomerEmail, ShippingAddress {ProductID} \to ProductName, ProductCategory, UnitPrice Note: CustomerID \to 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 \to Section \to 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} \to 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} \to ISBN, Title, Author, Publisher, PublicationYear {BookID, CopyNumber} \to BranchID {MemberID} \to 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 \to BookCopy \to 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} \to ? Neither JoinDate nor Role depends on StudentID alone (a student can have different join dates and roles for different clubs) {ClubID} \to ? Neither JoinDate nor Role depends on ClubID alone (a club has many members with different join dates and roles) {StudentID, ClubID} \to 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 \to 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

Second Normal Form: Comprehensive Examples

Theory Meets Practice

Example 1: E-Commerce Order System

Example 2: University Course Registration

Example 3: Manufacturing Work Orders

Example 4: Flight Booking System

Example 5: Library Book Loans

Example 6: Recognizing When 2NF Is Already Satisfied

Verification: Confirming Your Decomposition

There’s more to understand.

Second Normal Form: Comprehensive Examples

Theory Meets Practice

Example 1: E-Commerce Order System

Example 2: University Course Registration

Example 3: Manufacturing Work Orders

Example 4: Flight Booking System

Example 5: Library Book Loans

Example 6: Recognizing When 2NF Is Already Satisfied

Verification: Confirming Your Decomposition

There’s more to understand.