= Database Normalization – Wedding Planner System =

This page describes the normalization process of the ''Wedding Planner'' database system.
The goal is to transform an initial denormalized structure into a fully normalized relational schema that is lossless, dependency-preserving, and aligned with the ER diagram and SQL implementation.


== Denormalized Form (UNF) ==

The denormalized form represents a structure where all data related to users, weddings, events, guests, venues, and service providers are stored in a single table, without enforcing normalization rules.
This form contains repeating groups, redundancy, and multiple anomalies.

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




=== Functional Dependencies (UNF) ===

{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}

{venue_name} → {venue_type}

=== Problems in UNF ===

*Massive data redundancy



*Repeating groups (events, guests, services)



*Update anomalies


*Insertion anomalies


*Deletion anomalies

== First Normal Form (1NF) ==

The database is in First Normal Form when:

All attributes contain atomic values

There are no repeating groups

Each row is uniquely identifiable

A composite primary key is assumed to uniquely identify each row in the initial relation.

=== 1NF Relation ===

R(
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
)

Primary Key:
(user_id, wedding_id, event_id, guest_id)

== Second Normal Form (2NF) ==

Second Normal Form removes partial dependencies.
Attributes that depend only on part of the composite key are separated into new relations.

=== Decomposition ===

=== USER ===

|| user_id || first_name || last_name || email ||
|| 1 || Ana || Trajkovska || ana.trajkovska@gmail.com||

FD:
{user_id} → {first_name, last_name, email}

=== WEDDING ===

|| wedding_id || date || budget || user_id ||
|| 1 || 2026-06-20 || 8500 || 1 ||

FD:
{wedding_id} → {wedding_date, wedding_budget, user_id}

=== EVENT ===

|| event_id || event_type || date || start_time || end_time || wedding_id ||
|| 1 || Church Ceremony || 2026-06-20 || 12:00 || 13:00 || 1 ||
|| 2 || Reception || 2026-06-20 || 16:00 || 23:00 || 1 ||

FD:
{event_id} → {event_type, event_date, start_time, end_time, wedding_id}

=== GUEST ===

|| guest_id || first_name || last_name || wedding_id ||
|| 1 || Ana || Markovska || 1 ||
|| 2 || Daniel || Stojanov || 1 ||

FD:
{guest_id} → {first_name, last_name, wedding_id}

=== Lossless Join Test (2NF) ===

Since the intersection of decomposed relations contains primary keys that determine all attributes of the smaller relations, the decomposition is lossless.

== Third Normal Form (3NF) ==

Third Normal Form removes transitive dependencies.

=== Venue Separation ===

=== VENUE_TYPE ===

|| type_id || type_name ||
|| 3 || Outdoor Garden ||

FD:
{type_id} → {type_name}

=== VENUE ===

|| venue_id || name || location || city || capacity || price_per_guest || type_id ||
|| 1 || Lakeside Garden Venue || Matka || Skopje || 200 || 35 || 3 ||

FD:
{venue_id} → {name, location, city, capacity, price_per_guest, type_id}

=== EVENT RSVP ===

|| response_id || guest_id || event_id || status || response_date ||
|| 1 || 1 || 1 || accepted || 2026-05-15 ||
|| 2 || 2 || 2 || accepted || 2026-05-15 ||

Composite uniqueness:
(guest_id, event_id)

=== Attendance ===

|| attendance_id || guest_id || event_id || status || table_number || role ||
|| 1 || 1 || 1 || attending || 5 || Guest ||
|| 2 || 2 || 2 || attending || 10 || Guest ||

== Final Normalized Schema (3NF / BCNF) ==

USER(user_id, first_name, last_name, email)

WEDDING(wedding_id, date, budget, user_id)

EVENT(event_id, event_type, date, start_time, end_time, wedding_id)

GUEST(guest_id, first_name, last_name, wedding_id)

EVENT_RSVP(response_id, guest_id, event_id, status, response_date)

ATTENDANCE(attendance_id, guest_id, event_id, status, table_number, role)

VENUE(venue_id, name, location, city, capacity, price_per_guest, type_id)

VENUE_TYPE(type_id, type_name)

PHOTOGRAPHER(photographer_id, name, price_per_hour)

BAND(band_id, band_name, price_per_hour)

== Conclusion ==

The normalization process eliminates redundancy, prevents anomalies, and produces a clean relational schema fully aligned with the ER diagram and SQL implementation.
The final design is lossless, dependency-preserving, and in BCNF, making it suitable for a real-world wedding planner system.