Second Normal Form (2NF) – Detailed Explanation

Second Normal Form is the first place where real normalization starts. The goal of 2NF is to remove partial dependencies, which cause redundancy and anomalies.

Formal Definition of 2NF

A relation R is in Second Normal Form (2NF) if and only if:

*R is already in First Normal Form (1NF)

*No non-key attribute is functionally dependent on only a part of a composite primary key

Formally:

If X → A is a functional dependency in R, and X is a proper subset of a candidate key, then A must be a prime attribute, otherwise 2NF is violated.

Context: Why 2NF Is Relevant in Wedding Planner

In the Wedding Planner system, multiple real-world entities appear together:

*Users, Weddings, Venues, Venue bookings

When all of these are stored in one relation, identification requires more than one attribute, which creates a composite key.

Initial Relation Before 2NF

Relation

R(user_id, user_first_name, user_last_name, user_email, wedding_id, wedding_date, wedding_budget, venue_id, venue_name, venue_type, venue_capacity, booking_id, booking_date, start_time, end_time)

This relation is already in 1NF (all values are atomic), but that does not guarantee correctness.

Candidate Key Identification (Required for 2NF)

To analyze 2NF, we must first know the candidate key.

A tuple in R is uniquely identified only when all business dimensions are fixed:

Candidate Key

(user_id, wedding_id, venue_id, booking_date)

Justification:

*A user can manage multiple weddings

*A wedding can book multiple venues

*A venue can be booked on different dates

*Only the full combination guarantees uniqueness

This means the primary key is composite, which makes partial dependency possible.

Functional Dependencies in the Relation

ID	Functional Dependency	Meaning in Wedding Planner
FD1	user_id → user_first_name, user_last_name, user_email	User data depends only on the user
FD2	wedding_id → wedding_date, wedding_budget	Wedding data depends only on the wedding
FD3	venue_id → venue_name, venue_type, venue_capacity	Venue data depends only on the venue
FD4	booking_id → venue_id, wedding_id, booking_date, start_time, end_time	Booking defines reservation details

Proof of Partial Dependencies (2NF Violation)

A partial dependency exists when:

1.It's a non-key attribute

2.Depends on part of the composite key

3.Instead of the entire key

Partial Dependency Table

Non-Key Attribute	Depends On	Why This Violates 2NF
user_first_name	user_id	user_id is only part of the key
user_email	user_id	Does not depend on wedding or venue
wedding_date	wedding_id	Independent of user and venue
venue_name	venue_id	Independent of wedding and user

Each of these attributes depends on only one component of the composite key.

📌 Conclusion: The relation violates Second Normal Form

Why This Is a Problem

Problem	Example
Redundancy	User name repeated for every booking
Update anomaly	Changing email requires multiple updates
Insertion anomaly	Cannot store venue without booking
Deletion anomaly	Deleting booking deletes venue data

These issues are exactly what 2NF is designed to prevent.

Decomposition Strategy for 2NF

To reach 2NF, we must:

1.Separate attributes that depend on different parts of the key

2.Create new relations where each non-key attribute depends on the full primary key

Decomposition into 2NF Relations

USER

Table	Primary Key	Attributes
user	user_id	user_first_name, user_last_name, user_email

Why this works:

user_id → all user attributes

No partial dependency is possible

WEDDING

Table	Primary Key	Attributes
wedding	wedding_id	wedding_date, wedding_budget, user_id

Why this works:

wedding_id → wedding_date, wedding_budget

user_id is a foreign key, not a determinant

VENUE

Table	Primary Key	Attributes
venue	venue_id	venue_name, venue_type, venue_capacity

Why this works:

venue_id fully determines all venue attributes

VENUE_BOOKING

Table	Primary Key	Attributes
venue_booking	booking_id	venue_id, wedding_id, booking_date, start_time, end_time

Why this works:

booking_id uniquely identifies a reservation

All attributes depend on booking_id

Verification That 2NF Is Achieved

For each table:

Table	Full Dependency Check
user	All attributes depend on user_id
wedding	All attributes depend on wedding_id
venue	All attributes depend on venue_id
venue_booking	All attributes depend on booking_id

There are:

No composite keys with partial dependencies

No attributes depending on subsets of keys

✅ All relations satisfy Second Normal Form

Key Insight

Second Normal Form is achieved not by removing composite keys, but by ensuring that no non-key attribute depends on only part of a composite key.

In the Wedding Planner system, this required separating user, wedding, venue, and booking into independent relations.