Changes between Version 2 and Version 3 of P6


Ignore:
Timestamp:
01/13/26 14:41:04 (7 days ago)
Author:
213087
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • P6

    v2 v3  
    1 = Database Normalization – Wedding Planner System =
    2 
    3 This page describes the normalization process of the ''Wedding Planner'' database system.
    4 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.
    5 
    6 
    7 == Denormalized Form (UNF) ==
    8 
    9 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.
    10 This form contains repeating groups, redundancy, and multiple anomalies.
    11 
    12 === Denormalized Table ===
    13 
    14 || 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 ||
    15 || 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 ||
    16 || 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 ||
    17 
    18 
    19 
    20 
    21 === Functional Dependencies (UNF) ===
    22 
    23 {user_id} → {first_name, last_name, email}
    24 
    25 {wedding_id} → {wedding_date, wedding_budget}
    26 
    27 {event_id} → {event_type, event_date, start_time, end_time}
    28 
    29 {guest_id} → {guest_first_name, guest_last_name}
    30 
    31 {venue_name} → {venue_type}
    32 
    33 === Problems in UNF ===
    34 
    35 *Massive data redundancy
    36 
    37 
    38 
    39 *Repeating groups (events, guests, services)
    40 
    41 
    42 
    43 *Update anomalies
    44 
    45 
    46 *Insertion anomalies
    47 
    48 
    49 *Deletion anomalies
    50 
    51 == First Normal Form (1NF) ==
    52 
    53 The database is in First Normal Form when:
    54 
    55 All attributes contain atomic values
    56 
    57 There are no repeating groups
    58 
    59 Each row is uniquely identifiable
    60 
    61 A composite primary key is assumed to uniquely identify each row in the initial relation.
    62 
    63 === 1NF Relation ===
    64 
    65 R(
    66 user_id, first_name, last_name, email,
    67 wedding_id, wedding_date, wedding_budget,
    68 event_id, event_type, event_date, start_time, end_time,
    69 guest_id, guest_first_name, guest_last_name,
    70 rsvp_status, attendance_status,
    71 venue_name, venue_type,
    72 photographer_name, band_name
    73 )
    74 
    75 Primary Key:
    76 (user_id, wedding_id, event_id, guest_id)
    77 
    78 == Second Normal Form (2NF) ==
    79 
    80 Second Normal Form removes partial dependencies.
    81 Attributes that depend only on part of the composite key are separated into new relations.
    82 
    83 === Decomposition ===
    84 
    85 === USER ===
    86 
    87 || user_id || first_name || last_name || email ||
    88 || 1 || Ana || Trajkovska || ana.trajkovska@gmail.com||
    89 
    90 FD:
    91 {user_id} → {first_name, last_name, email}
    92 
    93 === WEDDING ===
    94 
    95 || wedding_id || date || budget || user_id ||
    96 || 1 || 2026-06-20 || 8500 || 1 ||
    97 
    98 FD:
    99 {wedding_id} → {wedding_date, wedding_budget, user_id}
    100 
    101 === EVENT ===
    102 
    103 || event_id || event_type || date || start_time || end_time || wedding_id ||
    104 || 1 || Church Ceremony || 2026-06-20 || 12:00 || 13:00 || 1 ||
    105 || 2 || Reception || 2026-06-20 || 16:00 || 23:00 || 1 ||
    106 
    107 FD:
    108 {event_id} → {event_type, event_date, start_time, end_time, wedding_id}
    109 
    110 === GUEST ===
    111 
    112 || guest_id || first_name || last_name || wedding_id ||
    113 || 1 || Ana || Markovska || 1 ||
    114 || 2 || Daniel || Stojanov || 1 ||
    115 
    116 FD:
    117 {guest_id} → {first_name, last_name, wedding_id}
    118 
    119 === Lossless Join Test (2NF) ===
    120 
    121 Since the intersection of decomposed relations contains primary keys that determine all attributes of the smaller relations, the decomposition is lossless.
    122 
    123 == Third Normal Form (3NF) ==
    124 
    125 Third Normal Form removes transitive dependencies.
    126 
    127 === Venue Separation ===
    128 
    129 === VENUE_TYPE ===
    130 
    131 || type_id || type_name ||
    132 || 3 || Outdoor Garden ||
    133 
    134 FD:
    135 {type_id} → {type_name}
    136 
    137 === VENUE ===
    138 
    139 || venue_id || name || location || city || capacity || price_per_guest || type_id ||
    140 || 1 || Lakeside Garden Venue || Matka || Skopje || 200 || 35 || 3 ||
    141 
    142 FD:
    143 {venue_id} → {name, location, city, capacity, price_per_guest, type_id}
    144 
    145 === EVENT RSVP ===
    146 
    147 || response_id || guest_id || event_id || status || response_date ||
    148 || 1 || 1 || 1 || accepted || 2026-05-15 ||
    149 || 2 || 2 || 2 || accepted || 2026-05-15 ||
    150 
    151 Composite uniqueness:
    152 (guest_id, event_id)
    153 
    154 === Attendance ===
    155 
    156 || attendance_id || guest_id || event_id || status || table_number || role ||
    157 || 1 || 1 || 1 || attending || 5 || Guest ||
    158 || 2 || 2 || 2 || attending || 10 || Guest ||
    159 
    160 == Final Normalized Schema (3NF / BCNF) ==
    161 
    162 USER(user_id, first_name, last_name, email)
    163 
    164 WEDDING(wedding_id, date, budget, user_id)
    165 
    166 EVENT(event_id, event_type, date, start_time, end_time, wedding_id)
    167 
    168 GUEST(guest_id, first_name, last_name, wedding_id)
    169 
    170 EVENT_RSVP(response_id, guest_id, event_id, status, response_date)
    171 
    172 ATTENDANCE(attendance_id, guest_id, event_id, status, table_number, role)
    173 
    174 VENUE(venue_id, name, location, city, capacity, price_per_guest, type_id)
    175 
    176 VENUE_TYPE(type_id, type_name)
    177 
    178 PHOTOGRAPHER(photographer_id, name, price_per_hour)
    179 
    180 BAND(band_id, band_name, price_per_hour)
    181 
    182 == Conclusion ==
    183 
    184 The normalization process eliminates redundancy, prevents anomalies, and produces a clean relational schema fully aligned with the ER diagram and SQL implementation.
    185 The final design is lossless, dependency-preserving, and in BCNF, making it suitable for a real-world wedding planner system.
     1=Phase 6
     2=Complex DB Reports (SQL, Stored Procedures, Relational Algebra)