= Database Normalization – Wedding Planner System =

== Introduction ==

This section presents a complete, formal, and provable normalization process for the Wedding Planner database.
The normalization is demonstrated step by step, starting from a denormalized relation and ending in Third Normal Form (3NF).

**Each transformation is justified using:**

-Functional dependencies

-Primary key verification

-Lossless join decomposition proof

-Functional dependency preservation proof

All examples are derived directly from the Wedding Planner domain.

== Initial Denormalized Relation (UNF) ==

The system initially stores all wedding-related information in a single relation.

|| Relation || Attributes ||
|| 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_date, start_time, end_time ||

This relation violates normalization principles.

=== Denormalized Table ===

|| user_id || first_name || last_name || email || wedding_id || wedding_date || wedding_budget || event_id || event_type || event_date || start_time || end_time ||guest_id || guest_first_name || guest_last_name || rsvp_status || attendance_status || venue_name || venue_type || photographer_name || band_name ||
|| 1 || Ana || Trajkovska || ana.trajkovska@gmail.com || 1 || 2026-06-20 || 8500 || 1 || Church Ceremony || 2026-06-20 || 12:00 || 13:00 || 1 || Ana || Markovska || accepted || attending || Lakeside Garden Venue || Outdoor Garden || Luna Studio || The Wedding Vibes ||
|| 1 || Ana || Trajkovska || ana.trajkovska@gmail.com || 1 || 2026-06-20 || 8500 || 2 || Reception || 2026-06-20 || 16:00 || 23:00 || 2 || Daniel || Stojanov || accepted || attending || Lakeside Garden Venue || Outdoor Garden || Luna Studio || The Wedding Vibes ||


== Identified Problems in UNF ==

|| Problem || Explanation ||
|| Repetition || Venue data is repeated for every wedding 
|| Update anomaly || Updating venue name requires multiple updates 
|| Insertion anomaly || Venue cannot exist without a wedding 
|| Deletion anomaly || Deleting a wedding removes venue data 
|| Mixed entities || User, wedding, venue, and booking data stored together ||

== Functional Dependency Analysis ==

Based on business rules of the Wedding Planner system, the following functional dependencies hold:

|| ID || Functional Dependency || Justification ||
|| FD1 || user_id → user_first_name, user_last_name, user_email || User attributes depend only on user_id 
|| FD2 || wedding_id → wedding_date, wedding_budget, user_id || A wedding uniquely defines its properties 
|| FD3 || venue_id → venue_name, venue_type, venue_capacity || Venue attributes depend only on venue_id 
|| FD4 || booking_id → venue_id, wedding_id, booking_date, start_time, end_time || A booking uniquely defines a venue reservation ||

== Candidate Key Determination ==

To uniquely identify a tuple in R, the following composite key is required:


Please review the proof explained in more details here -> [[Proof of Candidate Key]]

|| Candidate Key ||
|| (user_id, wedding_id, venue_id, booking_date) ||

This confirms that the initial relation has a composite primary key, which leads to partial dependencies.

== First Normal Form (1NF) ==

=== Definition ===
A relation is in 1NF if:

*All attributes contain atomic values

*No repeating groups exist

*The UNF relation contains repeating venue and booking attributes.

=== Transformation to 1NF ===

The relation is decomposed into atomic tuples by separating entity data.

|| Table || Attributes ||
|| R1 || user_id, user_first_name, user_last_name, user_email 
|| R2 || wedding_id, wedding_date, wedding_budget, user_id 
|| R3 || venue_id, venue_name, venue_type, venue_capacity 
|| R4 || booking_id, venue_id, wedding_id, booking_date, start_time, end_time ||

All attributes are now atomic.

Please review the proof explained in more details here -> [[Proof of 1N form]]

== Second Normal Form (2NF) ==

=== Definition ===
A relation is in 2NF if:

*It is in 1NF

*No non-key attribute is partially dependent on a composite key

=== Partial Dependency Proof ===

|| Attribute || Depends On || Violation ||
|| user_first_name || user_id || Partial dependency 
|| wedding_date || wedding_id || Partial dependency 
|| venue_name || venue_id || Partial dependency 

Thus, 2NF is violated in the original relation.

=== Decomposition to 2NF ===

|| Table || Primary Key || Attributes ||
|| user || user_id || user_first_name, user_last_name, user_email 
|| wedding || wedding_id || wedding_date, wedding_budget, user_id 
|| venue || venue_id || venue_name, venue_type, venue_capacity 
|| venue_booking || booking_id || venue_id, wedding_id, booking_date, start_time, end_time 

Each non-key attribute now fully depends on the primary key.

== Explanation of Entity Composition (User Table) ==

The user table exists because user data represents an independent entity.

|| Reason || Formal Explanation ||
|| Functional dependency || user_id → user attributes 
|| Independence || User may exist without a wedding 
|| Reusability || One user can manage multiple weddings 
|| Normalization rule || Prevents transitive dependencies ||

This decomposition is mandatory for correctness.

Please review the proof explained in more details here -> [[Proof of 2N]]

== Third Normal Form (3NF) ==

=== Definition ===
A relation is in 3NF if:

It is in 2NF

No transitive dependencies exist

=== Transitive Dependency Analysis ===

|| Dependency Chain || Explanation ||
|| wedding_id → user_id → user_email || Transitive dependency 
|| booking_id → venue_id → venue_name || Transitive dependency 

=== Removal of Transitive Dependencies ===

Each dependency is isolated into its own table, resulting in full 3NF compliance.

== Primary Key Verification ==

Each table has a primary key that uniquely identifies records.

|| Table || Primary Key || Uniqueness Proof ||
|| user || user_id || One user per ID 
|| wedding || wedding_id || One wedding per ID 
|| venue || venue_id || One venue per ID 
|| venue_booking || booking_id || One booking per ID ||

This ensures entity integrity.

Please review the proof explained in more details here -> [[Proof of 3N]]

== Lossless Join Decomposition Proof ==

=== Formal Rule ===

A decomposition of R into R1 and R2 is lossless if:

|| Condition ||
|| (R1 ∩ R2) → R1 OR (R1 ∩ R2) → R2 ||

=== Step-by-Step Explanation ===

|| Join || Common Attribute || Proof ||
|| wedding ⋈ user || user_id || user_id is PK in user 
|| venue_booking ⋈ wedding || wedding_id || wedding_id is PK 
|| venue_booking ⋈ venue || venue_id || venue_id is PK 

Each decomposition satisfies the lossless join condition.

❗ Lossless join is proven pairwise, not by joining all tables simultaneously.

== Functional Dependency Preservation Proof ==

=== Definition ===
A decomposition preserves dependencies if all FDs can be enforced without joins.

=== Verification ===

|| Functional Dependency || Table ||
|| user_id → user_first_name, user_last_name, user_email || user 
|| wedding_id → wedding_date, wedding_budget, user_id || wedding 
|| venue_id → venue_name, venue_type, venue_capacity || venue 
|| booking_id → venue_id, wedding_id, booking_date, start_time, end_time || venue_booking ||

All dependencies are preserved locally.

== Final Normalized Schema (3NF) ==

|| Table || Primary Key || Foreign Keys ||
|| user || user_id
|| wedding || wedding_id || user_id → user 
|| venue || venue_id 
|| venue_booking || booking_id || venue_id → venue, wedding_id → wedding ||

== Final Conclusion ==

The Wedding Planner database schema is fully normalized to Third Normal Form.

The normalization:

-Eliminates redundancy

-Prevents anomalies

-Preserves functional dependencies

-Guarantees lossless joins

-Uses provable primary keys