| Version 3 (modified by , 6 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
- Data duplication (same user, wedding, venue repeated)
- Repeating groups (events and guests)
- Update anomalies
- Insertion anomalies
- Deletion anomalies
First Normal Form (1NF)
Theoretical Explanation
A relation is in First Normal Form (1NF) if:
- all attributes contain atomic values
- there are no repeating groups
- each record can be uniquely identified
In this step, the table structure remains the same, but we define a composite primary key to uniquely identify each row.
1NF Relation
Primary Key: (user_id, wedding_id, event_id, guest_id)
All values are atomic and no multivalued attributes exist:
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)
Theoretical Explanation
A relation is in Second Normal Form (2NF) if:
- it is already in 1NF
- no non-key attribute depends on only part of a composite key
Partial dependencies are removed by decomposing the table into smaller 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 Explanation (2NF)
The decomposition is lossless because:
- primary keys are preserved
- joining the relations reconstructs the original UNF data without loss
Third Normal Form (3NF)
Theoretical Explanation
A relation is in Third Normal Form (3NF) if:
- it is already in 2NF
- no transitive dependencies exist
This means non-key attributes must depend only on the primary key.
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.
