Changes between Version 7 and Version 8 of P5

Timestamp:

05/08/26 20:11:25 (3 weeks ago)

Author:

211171

Comment:

--

P5

@@ -1 @@
-= 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.
-
-Note: venue_type in the UNF is an attribute that is later normalized into a separate relation.
-
-=== 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 ||
-
-
-== 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 ||
+= Wedding Planner Database Normalization =
+
+== 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.
+
+=== Initial Denormalized Relation ===
+
+R(
+user_id, first_name, last_name, email, phone_number, gender, birthday,
+wedding_id, date, budget, notes,
+venue_booking_id, vb_date, vb_start, vb_end, vb_status, vb_price,
+venue_id, venue_name, location, city, capacity, price_per_guest, venue_type,
+photographer_id, photographer_name, photo_price_per_hour,
+band_id, band_name, band_genre, band_price_per_hour,
+registrar_id, registrar_name, registrar_location,
+church_id, church_name, priest_name,
+guest_id, guest_first_name, guest_last_name, rsvp_status, attendance_status, table_number, event_type
+)
+
+== Functional Dependencies ==
+
+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}
 
 == Candidate Key Determination ==
 
-To uniquely identify a tuple in R, the following composite key is required:
-
-
-Please review the proof explained in more details 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.
-This composite key is minimal because removing any attribute (user_id, wedding_id, venue_id, or booking_date) results in ambiguity and loss of uniqueness. No proper subset of this key functionally determines all attributes of R.
+To uniquely identify a tuple in the denormalized relation, identifiers from all independent entity branches are required.
+
+=== Candidate Key ===
+
+K = {
+attendance_id,
+venue_booking_id,
+photographer_booking_id,
+band_booking_id,
+registrar_booking_id,
+priest_id
+}
+
+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.
 
 == 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.
+Every attribute contains atomic values
+No repeating groups exist
+Each tuple is uniquely identifiable
+Columns contain consistent data types
 
 === 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.
-
-Please review the proof explained in more details here -> [[Proof of 1N form]]
+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.
+
+=== Superkey Construction ===
+
+A superkey for the denormalized relation is:
+
+K = {
+user_id,
+wedding_id,
+venue_booking_id,
+photographer_booking_id,
+band_booking_id,
+registrar_booking_id,
+church_id,
+event_id,
+guest_id,
+response_id,
+attendance_id
+}
+
+=== Minimality Check ===
+
+Removing any identifier causes loss of at least one independent entity branch.
+
+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.
 
 == 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.
-
-Please review the proof explained in more details here -> [[Proof of 2N]]
+It is already in 1NF
+Every non-key attribute is fully functionally dependent 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 in 2NF
-
+It is already 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 and in all resulting relations, no non-key attribute functionally determines another non-key attribute.
-
-Please review the proof explained in more details here -> [[Proof of 3N]]
-
-== 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 ||
+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 is fully normalized to Third Normal Form.
-
-The normalization:
-
--Eliminates redundancy
-
--Prevents anomalies
-
--Preserves functional dependencies
-
--Guarantees lossless joins
-
--Uses provable primary keys
+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.
+
+= Proof of Candidate Key =
+
+== Definition ==
+
+A set of attributes X is a candidate key if:
+
+X⁺ = R
+X is minimal
+
+The closure X⁺ is computed by repeatedly applying functional dependencies until no additional attributes can be derived.
+
+== Step 1 – Testing Single Attribute Candidate Keys ==
+
+=== Attempt 1 ===
+
+K = {attendance_id}
+
+=== Closure ===
+
+(attendance_id)⁺ gives:
+
+attendance_status
+table_number
+guest_id
+event_id
+
+Using additional dependencies:
+
+event_id → event_type, wedding_id
+guest_id → guest_first_name, guest_last_name, rsvp_status, wedding_id
+wedding_id → date, budget, notes, user_id
+user_id → first_name, last_name, email, phone_number, gender, birthday
+
+=== Missing Dependencies ===
+
+The closure still does NOT determine:
+
+venue booking information
+photographer booking information
+band booking information
+registrar booking information
+priest and church information
+
+Therefore:
+
+(attendance_id)⁺ ≠ R
+
+Conclusion:
+
+attendance_id is NOT a candidate key.
+
+== Step 2 – Testing Two Attributes ==
+
+K = {attendance_id, priest_id}
+
+=== Closure ===
+
+Additional attributes derived:
+
+priest_name
+priest_contact
+church_id
+church_name
+church_location
+
+=== Missing Dependencies ===
+
+Still missing:
+
+venue booking information
+photographer booking information
+band booking information
+registrar booking information
+
+Therefore:
+
+(attendance_id, priest_id)⁺ ≠ R
+
+Conclusion:
+
+K is NOT a candidate key.
+
+== Step 3 – Testing Three Attributes ==
+
+K = {
+attendance_id,
+venue_booking_id,
+photographer_booking_id
+}
+
+=== Closure ===
+
+Additional attributes derived:
+
+venue information
+photographer information
+wedding information
+user information
+
+=== Missing Dependencies ===
+
+Still missing:
+
+band booking information
+registrar booking information
+priest/church information
+
+Therefore:
+
+K⁺ ≠ R
+
+Conclusion:
+
+K is NOT a candidate key.
+
+== Step 4 – Testing Larger Attribute Sets ==
+
+K = {
+attendance_id,
+priest_id,
+venue_booking_id,
+band_booking_id
+}
+
+=== Missing Dependencies ===
+
+Still missing:
+
+photographer booking information
+registrar booking information
+
+Therefore:
+
+K⁺ ≠ R
+
+Conclusion:
+
+K is NOT a candidate key.
+
+== Step 5 – Including All Booking Branches ==
+
+K = {
+attendance_id,
+venue_booking_id,
+photographer_booking_id,
+band_booking_id,
+registrar_booking_id
+}
+
+=== Missing Dependencies ===
+
+Still missing:
+
+priest information
+church information
+
+Therefore:
+
+K⁺ ≠ R
+
+Conclusion:
+
+K is NOT a candidate key.
+
+== Step 6 – Final Candidate Key ==
+
+K = {
+attendance_id,
+venue_booking_id,
+photographer_booking_id,
+band_booking_id,
+registrar_booking_id,
+priest_id
+}
+
+=== Closure ===
+
+Using all functional dependencies:
+
+attendance branch is determined
+event branch is determined
+guest branch is determined
+wedding branch is determined
+venue booking branch is determined
+photographer booking branch is determined
+band booking branch is determined
+registrar booking branch is determined
+priest branch is determined
+church branch is determined
+
+No attributes remain undetermined.
+
+Therefore:
+
+K⁺ = R
+
+Conclusion:
+
+K is a candidate key.
+
+== Minimality Proof ==
+
+A candidate key must be minimal.
+
+Removing any attribute from K causes loss of at least one independent entity branch:
+
+|| Removed Attribute || Lost Information ||
+|| attendance_id || attendance branch ||
+|| venue_booking_id || venue booking branch ||
+|| photographer_booking_id || photographer booking branch ||
+|| band_booking_id || band booking branch ||
+|| registrar_booking_id || registrar booking branch ||
+|| priest_id || priest/church branch ||
+
+Therefore every attribute in K is necessary.
+
+== Why church_id Is Not Included ==
+
+Suppose we define:
+
+K = {
+attendance_id,
+venue_booking_id,
+photographer_booking_id,
+band_booking_id,
+registrar_booking_id,
+priest_id,
+church_id
+}
+
+This violates minimality because:
+
+priest_id → church_id
+
+church_id is already derivable from priest_id.
+
+Therefore church_id is redundant.
+
+The resulting set would be a superkey, NOT a candidate key.
+
+== Final Candidate Key ==
+
+K = {
+attendance_id,
+venue_booking_id,
+photographer_booking_id,
+band_booking_id,
+registrar_booking_id,
+priest_id
+}
+
+This key:
+
+Determines all attributes of R
+Is minimal
+Satisfies the formal definition of a candidate key