Changes between Version 9 and Version 10 of P5

Timestamp:

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

Author:

211171

Comment:

--

P5

@@ -3 +3 @@
 == Denormalized Form ==
 
-A single flat relation initially stores all entities and relationships of the Wedding Planner system in one table, without structural separation.
-This causes repeating groups, redundancy, update anomalies, insertion anomalies, and deletion anomalies.
+A single flat table containing all entities and their relationships, with no structural constraints.  
+Multi-valued attributes and repeating groups are present.
 
 === Initial Denormalized Relation ===
@@ -24 +24 @@
 The following functional dependencies hold in the Wedding Planner system:
 
-user_id → first_name, last_name, email, phone_number, gender, birthday
-wedding_id → date, budget, notes, user_id
-event_id → event_type, date, start_time, end_time, status, wedding_id
-guest_id → guest_first_name, guest_last_name, email, wedding_id
-response_id → status, response_date, guest_id, event_id
-attendance_id → status, table_number, role, guest_id, event_id
-venue_booking_id → vb_date, vb_start, vb_end, vb_status, vb_price, venue_id, wedding_id
-venue_id → venue_name, location, city, address, capacity, menu, phone_number, price_per_guest, type_id
-type_id → type_name
-photographer_booking_id → date, start_time, end_time, status, photographer_id, wedding_id
-photographer_id → photographer_name, email, phone_number, price_per_hour
-band_booking_id → date, start_time, end_time, status, band_id, wedding_id
-band_id → band_name, band_genre, equipment, phone_number, price_per_hour
-registrar_booking_id → start_time, end_time, price, status, registrar_id, wedding_id
-registrar_id → registrar_name, contact, location, working_hours
-church_id → church_name, location, contact, wedding_id
-priest_id → priest_name, contact, church_id
-
-=== Additional Business Constraints ===
-
-The following additional dependencies also hold:
-
-{guest_id, event_id} → {response_id, rsvp_status, response_date}
-{guest_id, event_id} → {attendance_id, attendance_status, table_number}
-{venue_id, vb_date, vb_start} → {venue_booking_id}
-{wedding_id} → {venue_booking_id, photographer_booking_id, band_booking_id, registrar_booking_id, church_id}
+* user_id → first_name, last_name, email, phone_number, gender, birthday
+* wedding_id → date, budget, notes, user_id
+* event_id → event_type, date, start_time, end_time, status, wedding_id
+* guest_id → guest_first_name, guest_last_name, email, wedding_id
+* response_id → status, response_date, guest_id, event_id
+* attendance_id → status, table_number, role, guest_id, event_id
+* venue_booking_id → vb_date, vb_start, vb_end, vb_status, vb_price, venue_id, wedding_id
+* venue_id → venue_name, location, city, address, capacity, menu, phone_number, price_per_guest, type_id
+* type_id → type_name
+* photographer_booking_id → date, start_time, end_time, status, photographer_id, wedding_id
+* photographer_id → photographer_name, email, phone_number, price_per_hour
+* band_booking_id → date, start_time, end_time, status, band_id, wedding_id
+* band_id → band_name, band_genre, equipment, phone_number, price_per_hour
+* registrar_booking_id → start_time, end_time, price, status, registrar_id, wedding_id
+* registrar_id → registrar_name, contact, location, working_hours
+* church_id → church_name, location, contact, wedding_id
+* priest_id → priest_name, contact, church_id
+
+=== Additional Functional Dependencies ===
+
+The following additional functional dependencies also hold in R due to business constraints:
+
+* {guest_id, event_id} → {response_id, rsvp_status, response_date}
+* {guest_id, event_id} → {attendance_id, attendance_status, table_number}
+* {venue_id, vb_date, vb_start} → {venue_booking_id}
+* {wedding_id} → {venue_booking_id, photographer_booking_id, band_booking_id, registrar_booking_id, church_id}
 
 == Candidate Key Determination ==
 
-To uniquely identify a tuple in the denormalized relation, identifiers from all independent entity branches are required.
+Attribute set X is a candidate key if:
+
+* X⁺ = R
+* X is minimal
 
 === Candidate Key ===
@@ -66 +69 @@
 }
 
-Please review the detailed proof here → [[Proof of Candidate Key]]
-
-=== Minimality Verification ===
-
-Each attribute in K is necessary:
-
-Removing attendance_id loses the guest/event attendance branch
-Removing venue_booking_id loses venue reservation information
-Removing photographer_booking_id loses photographer booking information
-Removing band_booking_id loses band booking information
-Removing registrar_booking_id loses registrar booking information
-Removing priest_id loses priest information because church_id is derived through priest_id
-
-Since removing any attribute prevents determining all attributes of R, the key is minimal.
+The detailed proof is provided here → [[Proof of Candidate Key]]
+
+=== Minimality Check ===
+
+* Removing attendance_id loses the guest/event branch
+* Removing venue_booking_id loses the venue booking branch
+* Removing photographer_booking_id loses the photographer booking branch
+* Removing band_booking_id loses the band booking branch
+* Removing registrar_booking_id loses the registrar booking branch
+* Removing priest_id loses priest information because a church may contain multiple priests
+
+Therefore every attribute is required and the key is minimal.
+
+== Sample Denormalized Data ==
+
+|| user_id || first_name || wedding_id || date || budget || venue_name || photographer_name || band_name || church_name || guest_fname || rsvp_status ||
+|| 1 || Ana || 1 || 2026-06-20 || 8500.00 || Lakeside Garden || Luna Studio || Wedding Vibes || St. Clement || Daniel || accepted ||
+|| 2 || Stefan || 2 || 2026-09-05 || 12000.00 || Royal Hall || Golden Frame || Balkan Groove || St. Panteleimon || none || none ||
 
 == First Normal Form (1NF) ==
@@ -85 +92 @@
 === Definition ===
 
-A relation is in 1NF if:
-
-Every attribute contains atomic values
-No repeating groups exist
-Each tuple is uniquely identifiable
-Columns contain consistent data types
+A relation is in 1NF when:
+
+* Row ordering carries no meaning
+* Every attribute contains atomic values
+* Data types are consistent
+* A primary key uniquely identifies each tuple
+* No repeating groups exist
 
 === Transformation to 1NF ===
 
-The original denormalized relation contains multiple conceptual entities combined into a single table.
-Repeating groups are eliminated by separating each entity occurrence into individual tuples.
-
-All attributes now contain atomic values.
+The original denormalized relation contains multiple conceptual entities combined into one structure.
+
+Examples include:
+
+* User information
+* Wedding information
+* Guest information
+* Event information
+* Venue booking information
+* Photographer booking information
+* Band booking information
+* Registrar booking information
+* Church and priest information
+
+Repeating groups are eliminated by separating repeating occurrences into individual tuples.
+
+All attributes become atomic, satisfying the requirements of 1NF.
 
 === Superkey Construction ===
 
-A superkey for the denormalized relation is:
+To uniquely identify a tuple in the denormalized relation, identifiers from all independent entity groups must be included.
+
+A valid superkey is:
 
 K = {
@@ -117 +140 @@
 }
 
-=== Minimality Check ===
-
-Removing any identifier causes loss of at least one independent entity branch.
+=== Minimality Verification ===
+
+Removing any identifier causes at least one branch of the relation to become undetermined.
 
 Examples:
 
-Removing venue_booking_id removes venue booking information
-Removing photographer_booking_id removes photographer booking information
-Removing attendance_id removes attendance information
-
-Therefore the superkey is necessary for uniqueness in the denormalized structure.
+* Removing venue_booking_id loses venue booking information
+* Removing photographer_booking_id loses photographer booking information
+* Removing attendance_id loses attendance information
+* Removing church_id loses church information
+
+Therefore the superkey is necessary for uniqueness.
+
+== Transition to Second Normal Form (2NF) ==
+
+To achieve 2NF:
+
+* Partial dependencies must be removed
+* Attributes depending only on part of a composite key must be separated
+* Each resulting relation must contain attributes fully dependent on its own primary key
+
+This results in decomposition into multiple smaller relations.
 
 == Second Normal Form (2NF) ==
@@ -135 +169 @@
 A relation is in 2NF if:
 
-It is already in 1NF
-Every non-key attribute is fully functionally dependent on the entire primary key
-No partial dependencies exist
+* It is already in 1NF
+* Every non-key attribute depends on the entire primary key
+* No partial dependencies exist
 
 === Partial Dependency Violations ===
 
-The original relation violates 2NF because:
-
-user_id → user attributes
-wedding_id → wedding attributes
-venue_id → venue attributes
-
-These dependencies rely only on part of the composite key.
-
-=== Decomposition into 2NF Relations ===
-
-|| Relation || Primary Key ||
-|| USER || user_id ||
-|| WEDDING || wedding_id ||
-|| VENUE_TYPE || type_id ||
-|| VENUE || venue_id ||
-|| VENUE_BOOKING || booking_id ||
-|| PHOTOGRAPHER || photographer_id ||
-|| PHOTOGRAPHER_BOOKING || booking_id ||
-|| BAND || band_id ||
-|| BAND_BOOKING || booking_id ||
-|| REGISTRAR || registrar_id ||
-|| REGISTRAR_BOOKING || booking_id ||
-|| CHURCH || church_id ||
-|| PRIEST || priest_id ||
-|| EVENT || event_id ||
-|| GUEST || guest_id ||
-|| EVENT_RSVP || response_id ||
-|| ATTENDANCE || attendance_id ||
-
-Each resulting relation contains attributes fully dependent on its primary key.
-
-=== Lossless Join Verification ===
-
-Every decomposition satisfies the lossless join condition:
-
-(Ri ∩ Rj) → Ri OR (Ri ∩ Rj) → Rj
-
-Examples:
-
-|| Join || Common Attribute || Proof ||
-|| USER ⋈ WEDDING || user_id || user_id is PK in USER ||
-|| VENUE_BOOKING ⋈ VENUE || venue_id || venue_id is PK in VENUE ||
-|| EVENT_RSVP ⋈ EVENT || event_id || event_id is PK in EVENT ||
-
-Therefore all decompositions are 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
-
-=== Transitive Dependency Example ===
-
-venue_id → type_id → type_name
-
-Here, type_name depends transitively on venue_id through type_id.
-
-=== Resolution ===
-
-To eliminate the transitive dependency:
-
-VENUE_TYPE(type_id, type_name) is created
-VENUE stores type_id as a foreign key
-
-This removes the transitive dependency while preserving all functional dependencies.
-
-=== Final 3NF Relations ===
-
-|| Relation || Primary Key || Foreign Keys ||
-|| USER || user_id || — ||
-|| WEDDING || wedding_id || user_id → USER ||
-|| VENUE_TYPE || type_id || — ||
-|| VENUE || venue_id || type_id → VENUE_TYPE ||
-|| VENUE_BOOKING || booking_id || venue_id → VENUE, wedding_id → WEDDING ||
-|| PHOTOGRAPHER || photographer_id || — ||
-|| PHOTOGRAPHER_BOOKING || booking_id || photographer_id → PHOTOGRAPHER, wedding_id → WEDDING ||
-|| BAND || band_id || — ||
-|| BAND_BOOKING || booking_id || band_id → BAND, wedding_id → WEDDING ||
-|| REGISTRAR || registrar_id || — ||
-|| REGISTRAR_BOOKING || booking_id || registrar_id → REGISTRAR, wedding_id → WEDDING ||
-|| CHURCH || church_id || wedding_id → WEDDING ||
-|| PRIEST || priest_id || church_id → CHURCH ||
-|| EVENT || event_id || wedding_id → WEDDING ||
-|| GUEST || guest_id || wedding_id → WEDDING ||
-|| EVENT_RSVP || response_id || guest_id → GUEST, event_id → EVENT ||
-|| ATTENDANCE || attendance_id || guest_id → GUEST, event_id → EVENT ||
-
-== BCNF Verification ==
-
-A relation is in BCNF if for every non-trivial dependency:
-
-X → Y
-
-X is a superkey.
-
-All final relations satisfy this condition because every determinant is a candidate key or superkey.
-
-== Final Conclusion ==
-
-The Wedding Planner database schema has been formally normalized to Third Normal Form (3NF) and BCNF.
-
-The normalization process:
-
-Eliminates redundancy
-Prevents update anomalies
-Prevents insertion anomalies
-Prevents deletion anomalies
-Preserves functional dependencies
-Guarantees lossless joins
-Ensures entity integrity through verified primary keys
-
-The final schema consists of 17 fully normalized relations.
+Examples of partial dependencies in the original relation:
+
+* user_id → user attributes
+* wedding_id → wedding attributes
+* venue_id → venue attributes
+
+Therefore the original relation violates 2NF.
+
+== 2NF Decomposition ==
+
+=== R1 – USER ===
+
+{user_id} → {first_name, last_name, email, phone_number, gender, birthday}
+
+=== Candidate Key Verification ===
+
+user_id is the surrogate primary key.
+
+No partial dependencies exist.
+
+=== Sample Data ===
+
+|| user_id || first_name || last_name || email || phone_number || gender || birthday ||
+|| 1 || Ana || Trajkovska || ana.trajkovska@gmail.com || +38970111222 || Female || 2004-07-12 ||
+|| 2 || Stefan || Petrovski || stefan.petrovski@gmail.com || +38970333444 || Male || 2003-11-20 ||
+
+=== Lossless Join Test ===
+
+R ∩ R1 = {user_id, first_name, last_name, email, phone_number, gender, birthday}
+
+Since:
+
+user_id → R1
+
+The decomposition is lossless.
+
+----
+
+=== R2 – WEDDING ===
+
+{wedding_id} → {date, budget, notes, user_id}
+
+=== Candidate Key Verification ===
+
+wedding_id is the surrogate primary key.
+
+=== Sample Data ===
+
+|| wedding_id || date || budget || notes || user_id ||
+|| 1 || 2026-06-20 || 8500.00 || Summer wedding || 1 ||
+|| 2 || 2026-09-05 || 12000.00 || Elegant indoor wedding || 2 ||
+
+=== Lossless Join Test ===
+
+R1.1 ∩ R2 = {wedding_id, date, budget, notes, user_id}
+
+Since:
+
+wedding_id → R2
+
+The decomposition is lossless.
+
+----
+
+=== R3 – VENUE_TYPE ===
+
+{type_id} → {type_name}
+
+=== Candidate Key Verification ===
+
+type_id is the surrogate primary key.
+
+=== Sample Data ===
+
+|| type_id || type_name ||
+|| 1 || Restaurant ||
+|| 2 || Wedding Hall ||
+|| 3 || Outdoor Garden ||
+
+=== Lossless Join Test ===
+
+R2.1 ∩ R3 = {type_id, type_name}
+
+Since:
+
+type_id → type_name
+
+The decomposition is lossless.
+
+----
+
+=== R4 – VENUE ===
+
+VENUE(
+venue_id,
+name,
+location,
+city,
+address,
+capacity,
+menu,
+phone_number,
+price_per_guest,
+type_id
+)
+
+=== Candidate Key Verification ===
+
+venue_id is the surrogate primary key.
+
+=== Sample Data ===
+
+|| venue_id || name || location || city || address || capacity || menu || phone_number || price_per_guest || type_id ||
+|| 1 || Lakeside Garden || Matka || Skopje || Matka 12 || 200 || Garden menu || +38971123456 || 35 || 3 ||
+|| 2 || Royal Hall || Centar || Skopje || Main St 5 || 350 || Full menu || +38972234567 || 45 || 2 ||
+
+=== Lossless Join Test ===
+
+R3.1 ∩ R4 = {venue_id, name, location, city, address, capacity, menu, phone_number, price_per_guest, type_id}
+
+Since:
+
+venue_id → R4
+
+The decomposition is lossless.
+
+----
+
+=== R5 – VENUE_BOOKING ===
+
+{booking_id} → {date, start_time, end_time, status, price, venue_id, wedding_id}
+
+=== Candidate Key Verification ===
+
+booking_id is the surrogate primary key.
+
+=== Sample Data ===
+
+|| booking_id || date || start_time || end_time || status || price || venue_id || wedding_id ||
+|| 1 || 2026-06-20 || 16:00 || 23:00 || confirmed || 7000 || 1 || 1 ||
+|| 2 || 2026-09-05 || 17:00 || 23:30 || confirmed || 9500 || 2 || 2 ||
+
+=== Lossless Join Test ===
+
+R4.1 ∩ R5 = {venue_booking_id, date, start_time, end_time, status, price, venue_id, wedding_id}
+
+Since:
+
+venue_booking_id → R5
+
+The decomposition is lossless.
+
+----
+
+=== R6 – PHOTOGRAPHER ===
+
+{photographer_id} → {name, email, phone_number, price_per_hour}
+
+=== Candidate Key Verification ===
+
+photographer_id is the surrogate primary key.
+
+=== Sample Data ===
+
+|| photographer_id || name || email || phone_number || price_per_hour ||
+|| 1 || Luna Studio || luna@studio.mk || +38970101010 || 55 ||
+|| 2 || Golden Frame || golden@frame.mk || +38970202020 || 65 ||
+
+=== Lossless Join Test ===
+
+R5.1 ∩ R6 = {photographer_id, name, email, phone_number, price_per_hour}
+
+Since:
+
+photographer_id → R6
+
+The decomposition is lossless.
+
+----
+
+=== R7 – PHOTOGRAPHER_BOOKING ===
+
+{booking_id} → {date, start_time, end_time, status, photographer_id, wedding_id}
+
+=== Candidate Key Verification ===
+
+booking_id is the surrogate primary key.
+
+=== Sample Data ===
+
+|| booking_id || date || start_time || end_time || status || photographer_id || wedding_id ||
+|| 1 || 2026-06-20 || 14:00 || 22:00 || confirmed || 1 || 1 ||
+|| 2 || 2026-09-05 || 12:00 || 21:00 || pending || 2 || 2 ||
+
+=== Lossless Join Test ===
+
+R6.1 ∩ R7 = {photographer_booking_id, date, start_time, end_time, status, photographer_id, wedding_id}
+
+Since:
+
+photographer_booking_id → R7
+
+The decomposition is lossless.
+
+----
+
+=== R8 – BAND ===
+
+{band_id} → {band_name, genre, equipment, phone_number, price_per_hour}
+
+=== Candidate Key Verification ===
+
+band_id is the surrogate primary key.
+
+=== Sample Data ===
+
+|| band_id || band_name || genre || equipment || phone_number || price_per_hour ||
+|| 1 || Wedding Vibes || Pop || Sound + lights || +38970909090 || 80 ||
+|| 2 || Balkan Groove || Traditional || Full instruments || +38970707070 || 95 ||
+
+=== Lossless Join Test ===
+
+R7.1 ∩ R8 = {band_id, band_name, genre, equipment, phone_number, price_per_hour}
+
+Since:
+
+band_id → R8
+
+The decomposition is lossless.
+
+----
+
+=== R9 – BAND_BOOKING ===
+
+{booking_id} → {date, start_time, end_time, status, band_id, wedding_id}
+
+=== Candidate Key Verification ===
+
+booking_id is the surrogate primary key.
+
+=== Sample Data ===
+
+|| booking_id || date || start_time || end_time || status || band_id || wedding_id ||
+|| 1 || 2026-06-20 || 18:00 || 23:00 || confirmed || 1 || 1 ||
+|| 2 || 2026-09-05 || 19:00 || 23:30 || confirmed || 2 || 2 ||
+
+=== Lossless Join Test ===
+
+R8.1 ∩ R9 = {band_booking_id, date, start_time, end_time, status, band_id, wedding_id}
+
+Since:
+
+band_booking_id → R9
+
+The decomposition is lossless.
+
+----