| Version 4 (modified by , 13 days ago) ( diff ) |
|---|
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 | 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 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.
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.
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.
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
