= Wedding Planner Database Normalization = == Denormalized Form == 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 === 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 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 == Attribute set X is a candidate key if: * X⁺ = R * X is minimal === Candidate Key === K = { attendance_id, venue_booking_id, photographer_booking_id, band_booking_id, registrar_booking_id, priest_id } 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) == === Definition === 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 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 === To uniquely identify a tuple in the denormalized relation, identifiers from all independent entity groups must be included. A valid superkey 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 Verification === Removing any identifier causes at least one branch of the relation to become undetermined. Examples: * 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) == === Definition === A relation is in 2NF if: * It is already in 1NF * Every non-key attribute depends on the entire primary key * No partial dependencies exist === Partial Dependency Violations === 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. ---- === 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).