Changes between Version 1 and Version 2 of P6

Timestamp:

01/13/26 00:08:50 (8 days ago)

Author:

213087

Comment:

--

P6

@@ -1 @@
-= Phase P6
+= Database Normalization – Wedding Planner System =
 
-== Content
-To be defined.
+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 || 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 ||
+|| 1 || Ana || Trajkovska || ana.trajkovska@gmail.com || 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@gmail.com || 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 || 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 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.