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.
Note: venue_type in the UNF is an attribute that is later normalized into a separate relation.
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 |
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. This composite key is minimal because removing any attribute (user_id, wedding_id, venue_id, or booking_date) results in ambiguity and loss of uniqueness. No proper subset of this key functionally determines all attributes of R.
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 and in all resulting relations, no non-key attribute functionally determines another non-key attribute.
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
