== 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.**