| Version 2 (modified by , 8 days ago) ( diff ) |
|---|
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 | 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@… | 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@… | 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 | |
| 1 | Ana | Trajkovska | ana.trajkovska@… |
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.
