Changes between Initial Version and Version 1 of Proof of 2N

Timestamp:

01/27/26 23:57:58 (13 days ago)

Author:

213087

Comment:

--

Proof of 2N

@@ +1 @@
+== 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.**