Changes between Version 3 and Version 4 of P5

Timestamp:

01/27/26 23:17:30 (13 days ago)

Author:

213087

Comment:

--

P5

@@ -1 +1 @@
 = 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.
+== 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 ===
@@ -17 +34 @@
 
 
-
-
-=== 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
+== 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) ==
 
-=== 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)
+=== 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) ==
 
-=== 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 || 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 Explanation (2NF) ===
-
-The decomposition is lossless because:
-• primary keys are preserved
-• joining the relations reconstructs the original UNF data without loss
+=== 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) ==
 
-=== 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.
+=== 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