Changes between Version 10 and Version 11 of P5

Timestamp:

05/08/26 20:16:14 (3 weeks ago)

Author:

211171

Comment:

--

P5

@@ -432 +432 @@
 
 ----
+=== R10 – REGISTRAR ===
+
+{registrar_id} → {name, contact, location, working_hours}
+
+=== Candidate Key Verification ===
+
+registrar_id is the surrogate primary key.
+
+No partial dependencies exist.
+
+=== Sample Data ===
+
+|| registrar_id || name || contact || location || working_hours ||
+|| 1 || Skopje Civil Registry || +38970123456 || Skopje || 08:00-16:00 ||
+|| 2 || Centar Registry || +38970222333 || Skopje || 09:00-17:00 ||
+
+=== Lossless Join Test ===
+
+R9.1 ∩ R10 = {registrar_id, name, contact, location, working_hours}
+
+Since:
+
+registrar_id → R10
+
+The decomposition is lossless.
+
+----
+
+=== R11 – REGISTRAR_BOOKING ===
+
+{booking_id} → {date, start_time, end_time, status, registrar_id, wedding_id}
+
+=== Candidate Key Verification ===
+
+booking_id is the surrogate primary key.
+
+No partial dependencies exist.
+
+=== Sample Data ===
+
+|| booking_id || date || start_time || end_time || status || registrar_id || wedding_id ||
+|| 1 || 2026-06-20 || 10:00 || 10:30 || confirmed || 1 || 1 ||
+|| 2 || 2026-09-05 || 11:00 || 11:30 || confirmed || 2 || 2 ||
+
+=== Lossless Join Test ===
+
+R10.1 ∩ R11 = {registrar_booking_id, date, start_time, end_time, status, registrar_id, wedding_id}
+
+Since:
+
+registrar_booking_id → R11
+
+The decomposition is lossless.
+
+----
+
+=== R12 – CHURCH ===
+
+{church_id} → {name, location, contact, wedding_id}
+
+=== Candidate Key Verification ===
+
+church_id is the surrogate primary key.
+
+No partial dependencies exist.
+
+=== Sample Data ===
+
+|| church_id || name || location || contact || wedding_id ||
+|| 1 || St. Clement Church || Skopje || contact@church.mk || 1 ||
+|| 2 || St. Panteleimon || Nerezi || info@church.mk || 2 ||
+
+=== Lossless Join Test ===
+
+R11.1 ∩ R12 = {church_id, church_name, location, contact, wedding_id}
+
+Since:
+
+church_id → R12
+
+The decomposition is lossless.
+
+----
+
+=== R13 – PRIEST ===
+
+{priest_id} → {name, contact, church_id}
+
+=== Candidate Key Verification ===
+
+priest_id is the surrogate primary key.
+
+No partial dependencies exist.
+
+=== Sample Data ===
+
+|| priest_id || name || contact || church_id ||
+|| 1 || Father Nikola || +38970123456 || 1 ||
+|| 2 || Father Petar || +38970222333 || 2 ||
+
+=== Lossless Join Test ===
+
+R12.1 ∩ R13 = {priest_id, priest_name, priest_contact, church_id}
+
+Since:
+
+priest_id → R13
+
+The decomposition is lossless.
+
+----
+
+=== R14 – EVENT ===
+
+{event_id} → {event_type, date, start_time, end_time, status, wedding_id}
+
+=== Candidate Key Verification ===
+
+event_id is the surrogate primary key.
+
+No partial dependencies exist.
+
+=== Sample Data ===
+
+|| event_id || event_type || date || start_time || end_time || status || wedding_id ||
+|| 1 || Ceremony || 2026-06-20 || 12:00 || 13:00 || scheduled || 1 ||
+|| 2 || Reception || 2026-06-20 || 16:00 || 23:00 || scheduled || 1 ||
+
+=== Lossless Join Test ===
+
+R13.1 ∩ R14 = {event_id, event_type, date, start_time, end_time, status, wedding_id}
+
+Since:
+
+event_id → R14
+
+The decomposition is lossless.
+
+----
+
+=== R15 – GUEST ===
+
+{guest_id} → {first_name, last_name, email, wedding_id}
+
+=== Candidate Key Verification ===
+
+guest_id is the surrogate primary key.
+
+No partial dependencies exist.
+
+=== Sample Data ===
+
+|| guest_id || first_name || last_name || email || wedding_id ||
+|| 1 || Ana || Markovska || ana.m@gmail.com || 1 ||
+|| 2 || Daniel || Stojanov || daniel.s@gmail.com || 1 ||
+
+=== Lossless Join Test ===
+
+R14.1 ∩ R15 = {guest_id, first_name, last_name, email, wedding_id}
+
+Since:
+
+guest_id → R15
+
+The decomposition is lossless.
+
+----
+
+=== R16 – EVENT_RSVP ===
+
+{response_id} → {status, response_date, guest_id, event_id}
+
+=== Candidate Key Verification ===
+
+response_id is the surrogate primary key.
+
+No partial dependencies exist.
+
+=== Sample Data ===
+
+|| response_id || status || response_date || guest_id || event_id ||
+|| 1 || accepted || 2026-05-15 || 1 || 1 ||
+|| 2 || accepted || 2026-05-15 || 2 || 2 ||
+
+=== Lossless Join Test ===
+
+R15.1 ∩ R16 = {response_id, status, response_date, guest_id, event_id}
+
+Since:
+
+response_id → R16
+
+The decomposition is lossless.
+
+----
+
+=== R17 – ATTENDANCE ===
+
+{attendance_id} → {status, table_number, role, guest_id, event_id}
+
+=== Candidate Key Verification ===
+
+attendance_id is the surrogate primary key.
+
+No partial dependencies exist.
+
+=== Sample Data ===
+
+|| attendance_id || status || table_number || role || guest_id || event_id ||
+|| 1 || attending || 5 || Guest || 1 || 2 ||
+|| 2 || attending || 7 || Guest || 2 || 2 ||
+
+=== Lossless Join Test ===
+
+R16.1 ∩ R17 = {attendance_id, status, table_number, role, guest_id, event_id}
+
+Since:
+
+attendance_id → R17
+
+The decomposition is lossless.
+
+== Third Normal Form (3NF) ==
+
+=== Definition ===
+
+A relation is in 3NF if:
+
+* It is already in 2NF
+* No transitive dependencies exist
+* Every non-key attribute depends only on the primary key
+
+=== Transition from 2NF to 3NF ===
+
+After decomposition into 2NF, each relation is examined for transitive dependencies.
+
+A transitive dependency exists when a non-key attribute depends on another non-key attribute.
+
+Example:
+
+venue_id → type_id → type_name
+
+Here:
+
+* type_name depends on type_id
+* type_id depends on venue_id
+
+Therefore type_name does not directly depend on the primary key venue_id.
+
+This violates 3NF.
+
+=== Resolution ===
+
+To eliminate the transitive dependency:
+
+* VENUE_TYPE(type_id, type_name) is extracted as a separate relation
+* VENUE stores type_id as a foreign key
+
+This preserves all functional dependencies while eliminating transitivity.
+
+=== Verification of Remaining Relations ===
+
+All remaining relations are examined and found to contain:
+
+* No partial dependencies
+* No transitive dependencies
+* Only direct dependencies on the primary key
+
+Therefore all final relations satisfy 3NF.
+
+== Boyce-Codd Normal Form (BCNF) ==
+
+=== Definition ===
+
+A relation is in BCNF if for every non-trivial functional dependency:
+
+X → Y
+
+X is a superkey.
+
+=== BCNF Verification ===
+
+Each final relation satisfies BCNF because:
+
+* Every determinant is a candidate key or superkey
+* No dependency violates BCNF conditions
+
+== Final Schema Summary ==
+
+|| Relation || Primary Key || Foreign Keys || Normal Form ||
+|| USER || user_id || — || 3NF / BCNF ||
+|| WEDDING || wedding_id || user_id → USER || 3NF / BCNF ||
+|| VENUE_TYPE || type_id || — || 3NF / BCNF ||
+|| VENUE || venue_id || type_id → VENUE_TYPE || 3NF / BCNF ||
+|| VENUE_BOOKING || booking_id || venue_id → VENUE, wedding_id → WEDDING || 3NF / BCNF ||
+|| PHOTOGRAPHER || photographer_id || — || 3NF / BCNF ||
+|| PHOTOGRAPHER_BOOKING || booking_id || photographer_id → PHOTOGRAPHER, wedding_id → WEDDING || 3NF / BCNF ||
+|| BAND || band_id || — || 3NF / BCNF ||
+|| BAND_BOOKING || booking_id || band_id → BAND, wedding_id → WEDDING || 3NF / BCNF ||
+|| REGISTRAR || registrar_id || — || 3NF / BCNF ||
+|| REGISTRAR_BOOKING || booking_id || registrar_id → REGISTRAR, wedding_id → WEDDING || 3NF / BCNF ||
+|| CHURCH || church_id || wedding_id → WEDDING || 3NF / BCNF ||
+|| PRIEST || priest_id || church_id → CHURCH || 3NF / BCNF ||
+|| EVENT || event_id || wedding_id → WEDDING || 3NF / BCNF ||
+|| GUEST || guest_id || wedding_id → WEDDING || 3NF / BCNF ||
+|| EVENT_RSVP || response_id || guest_id → GUEST, event_id → EVENT || 3NF / BCNF ||
+|| ATTENDANCE || attendance_id || guest_id → GUEST, event_id → EVENT || 3NF / BCNF ||
+
+== Final Conclusion ==
+
+Through formal normalization analysis, verified functional dependencies, candidate key analysis, and lossless join decomposition tests, the Wedding Planner database schema has been fully normalized.
+
+The normalization process successfully:
+
+* Eliminates redundancy
+* Prevents insertion anomalies
+* Prevents deletion anomalies
+* Prevents update anomalies
+* Preserves all functional dependencies
+* Guarantees lossless joins
+* Ensures entity integrity through verified primary keys
+
+The final schema consists of 17 relations, all satisfying Third Normal Form (3NF) and Boyce-Codd Normal Form (BCNF).