Changes between Version 12 and Version 13 of ERModel


Ignore:
Timestamp:
02/04/26 20:06:28 (5 days ago)
Author:
193284
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • ERModel

    v12 v13  
    22
    33== Description
    4 This page contains the Entity–Relationship (ER) model description and diagrams for the Wedding Planner system. The ER model captures the core entities, their attributes, and relationships required to support wedding planning, event organization, guest management, and service reservations.
    5 
    6 == Entity-Relationship Model Wedding_Planner
     4This page describes the Entity–Relationship (ER) model of the Wedding Planner system. The ER model defines the main entities, their attributes, and the relationships between them, in order to support wedding planning, event organization, guest management, and service reservations.
     5
     6== Entity-Relationship Model: Wedding Planner
    77
    88== Diagram
    9 [[Image(Wedding_Planner_Verison5.png, width=100%)]]
     9[[Image(Wedding_Planner_Version5.png, width=100%)]]
    1010
    1111== Data requirements
    … …  
    1414
    1515Explanation:
    16 The User entity represents a registered person who uses the system to organize and manage weddings and related events. It stores personal and contact information and acts as the main system actor. A user can create and manage multiple weddings, invite guests, and reserve services. This entity is modeled separately to centralize user data, support authentication, and establish ownership of weddings.
    17 
    18 Candidate keys:
    19 
    20 user_id – selected as the primary key because it is system-generated, stable, and unique
    21 
    22 email – candidate key because it is unique per user, but not chosen as primary since it may change
    23 
    24 Attributes:
    25 
    26 user_id – numeric, required
    27 
    28 first_name – text, required
    29 
    30 last_name – text, required
    31 
    32 email – text, required, valid email format
    33 
    34 phone_number – text, optional
    35 
    36 gender – text, optional
    37 
    38 birthday – date, optional
     16The User entity represents a registered person who uses the system to organize and manage weddings. A user can create and manage multiple weddings and is responsible for all planning activities. This entity stores personal and contact information and represents the owner of weddings in the system.
     17
     18Candidate keys:
     19user_id – selected as the primary key because it is system-generated and unique 
     20email – candidate key because it is unique per user, but not selected as primary since it may change
     21
     22Attributes:
     23user_id – numeric, required 
     24first_name – text, required 
     25last_name – text, required 
     26email – text, required 
     27phone_number – text, optional 
     28gender – text, optional 
     29birthday – date, optional 
    3930
    4031== Entity: Wedding
    4132
    4233Explanation:
    43 The Wedding entity represents a specific wedding being planned and managed within the system. It is the central entity that connects all guests, events, reservations, and service bookings. Modeling it separately allows the system to manage multiple weddings independently and associate all related data consistently.
    44 
    45 Candidate keys:
    46 
    47 wedding_id – selected as the primary key as a unique system-generated identifier
    48 
    49 Attributes:
    50 
    51 wedding_id – numeric, required
    52 
    53 date – date, required
    54 
    55 budget – numeric, optional
    56 
    57 notes – text, optional
    58 
    59 type – text, optional
    60 
    61 status – text, optional
     34The Wedding entity represents a specific wedding being planned in the system. It is the central entity that connects guests, events, and all service bookings. Each wedding belongs to one user and acts as the main context for all related data.
     35
     36Candidate keys:
     37wedding_id – selected as the primary key because it uniquely identifies each wedding
     38
     39Attributes:
     40wedding_id – numeric, required 
     41date – date, required 
     42budget – numeric, optional 
     43notes – text, optional 
     44type – text, optional 
     45status – text, optional 
    6246
    6347== Entity: Event
    6448
    6549Explanation:
    66 The Event entity represents individual events associated with a wedding, such as ceremonies, receptions, or celebrations. Modeling events separately allows each wedding to have multiple scheduled events with different times and purposes.
    67 
    68 Candidate keys:
    69 
    70 event_id – selected as the primary key because it uniquely identifies each event
    71 
    72 Attributes:
    73 
    74 event_id – numeric, required
    75 
    76 event_type – text, required
    77 
    78 date – date, required
    79 
    80 start_time – time, required
    81 
    82 end_time – time, required
    83 
    84 status – text, required
     50The Event entity represents individual events related to a wedding, such as a ceremony or reception. Each wedding can have multiple events, scheduled at different times.
     51
     52Candidate keys:
     53event_id – selected as the primary key
     54
     55Attributes:
     56event_id – numeric, required 
     57event_type – text, required 
     58date – date, required 
     59start_time – time, required 
     60end_time – time, required 
     61status – text, required 
     62
     63== Entity: Guest
     64
     65Explanation:
     66The Guest entity represents people invited to a wedding. Guests do not need system accounts, therefore they are modeled separately from users.
     67
     68Candidate keys:
     69guest_id – selected as the primary key
     70
     71Attributes:
     72guest_id – numeric, required 
     73first_name – text, required 
     74last_name – text, required 
     75email – text, optional 
    8576
    8677== Entity: Event_RSVP
    8778
    8879Explanation:
    89 The Event_RSVP entity stores responses from guests regarding their attendance at specific events. It is required to track confirmations, declines, and response dates for event planning purposes.
    90 
    91 Candidate keys:
    92 
    93 response_id – selected as the primary key because it uniquely identifies each RSVP record
    94 
    95 Attributes:
    96 
    97 response_id – numeric, required
    98 
    99 status – text, required
    100 
    101 response_date – date, required
    102 
    103 == Entity: Guest
    104 
    105 Explanation:
    106 The Guest entity represents individuals invited to a wedding. Guests do not need system accounts, therefore they are modeled separately from users. This allows flexible guest management without authentication requirements.
    107 
    108 Candidate keys:
    109 
    110 guest_id – selected as the primary key
    111 
    112 Attributes:
    113 
    114 guest_id – numeric, required
    115 
    116 first_name – text, required
    117 
    118 last_name – text, required
    119 
    120 email – text, optional
     80The Event_RSVP entity stores guest responses to event invitations. It allows tracking whether a guest has accepted or declined an invitation and when the response was given.
     81
     82Candidate keys:
     83(guest_id, event_id) – natural composite key 
     84response_id – surrogate primary key used in the relational model
     85
     86Attributes:
     87response_id – numeric, required 
     88status – text, required 
     89response_date – date, required 
    12190
    12291== Entity: Attendance
    12392
    12493Explanation:
    125 The Attendance entity represents the actual participation of guests in wedding events. It allows tracking attendance status, assigned roles, and seating arrangements. Seating information is optional, as not all events require tables (e.g., ceremonies).
    126 
    127 Candidate keys:
    128 
    129 attendance_id – selected as the primary key
    130 
    131 Attributes:
    132 
    133 attendance_id – numeric, required
    134 
    135 status – text, required
    136 
    137 table_number – numeric, optional
    138 
    139 role – text, required
     94The Attendance entity represents the actual participation of guests in events. It stores attendance status, guest role, and seating information when applicable. Seating is optional because not all events require tables.
     95
     96Candidate keys:
     97(guest_id, event_id) – natural composite key 
     98attendance_id – surrogate primary key used in the relational model
     99
     100Attributes:
     101attendance_id – numeric, required 
     102status – text, required 
     103table_number – numeric, optional 
     104role – text, required 
    140105
    141106== Entity: Venue
    142107
    143108Explanation:
    144 The Venue entity represents physical locations where wedding-related events take place. It stores capacity, pricing, and contact information to support venue selection and booking.
    145 
    146 Candidate keys:
    147 
     109The Venue entity represents physical locations where wedding events can be held. It stores information needed for capacity planning and pricing.
     110
     111Candidate keys:
    148112venue_id – selected as the primary key
    149113
    150114Attributes:
    151 
    152 venue_id – numeric, required
    153 
    154 name – text, required
    155 
    156 location – text, required
    157 
    158 city – text, required
    159 
    160 address – text, required
    161 
    162 capacity – numeric, required
    163 
    164 menu – text, optional
    165 
    166 phone_number – text, optional
    167 
    168 price_per_guest – numeric, required
     115venue_id – numeric, required 
     116name – text, required 
     117location – text, required 
     118city – text, required 
     119address – text, required 
     120capacity – numeric, required 
     121menu – text, optional 
     122phone_number – text, optional 
     123price_per_guest – numeric, required 
    169124
    170125== Entity: Venue_Type
    171126
    172127Explanation:
    173 The Venue_Type entity categorizes venues (e.g., restaurant, hall, outdoor space). It is modeled separately to ensure data consistency and allow easy extension.
    174 
    175 Candidate keys:
    176 
     128The Venue_Type entity defines categories of venues, such as restaurant or wedding hall. It is modeled separately to avoid data duplication.
     129
     130Candidate keys:
    177131type_id – selected as the primary key
    178132
    179133Attributes:
    180 
    181 type_id – numeric, required
    182 
    183 type_name – text, required
     134type_id – numeric, required 
     135type_name – text, required 
    184136
    185137== Entity: Venue_booking
    186138
    187139Explanation:
    188 The Venue_booking entity represents reservations of venues for weddings. It resolves the many-to-many relationship between weddings and venues and stores scheduling and pricing information.
    189 
    190 Candidate keys:
    191 
     140The Venue_booking entity represents reservations of venues for weddings. It connects weddings and venues and stores booking details.
     141
     142Candidate keys:
    192143booking_id – selected as the primary key
    193144
    194145Attributes:
    195 
    196 booking_id – numeric, required
    197 
    198 date – date, required
    199 
    200 start_time – time, required
    201 
    202 end_time – time, required
    203 
    204 status – text, required
    205 
    206 price – numeric, required
     146booking_id – numeric, required 
     147date – date, required 
     148start_time – time, required 
     149end_time – time, required 
     150status – text, required 
     151price – numeric, required 
    207152
    208153== Entity: Photographer
    209154
    210155Explanation:
    211 The Photographer entity represents professional photographers available for weddings. It stores contact and pricing information to support booking and comparison.
    212 
    213 Candidate keys:
    214 
     156The Photographer entity represents photographers available for booking.
     157
     158Candidate keys:
    215159photographer_id – selected as the primary key
    216160
    217161Attributes:
    218 
    219 photographer_id – numeric, required
    220 
    221 name – text, required
    222 
    223 email – text, required
    224 
    225 phone_number – text, required
    226 
    227 price_per_hour – numeric, required
     162photographer_id – numeric, required 
     163name – text, required 
     164email – text, required 
     165phone_number – text, required 
     166price_per_hour – numeric, required 
    228167
    229168== Entity: Photographer_booking
    230169
    231170Explanation:
    232 The Photographer_booking entity records photographer reservations for specific weddings and time intervals.
    233 
    234 Candidate keys:
    235 
     171The Photographer_booking entity stores photographer reservations for weddings.
     172
     173Candidate keys:
    236174booking_id – selected as the primary key
    237175
    238176Attributes:
    239 
    240 booking_id – numeric, required
    241 
    242 date – date, required
    243 
    244 start_time – time, required
    245 
    246 end_time – time, required
    247 
    248 status – text, required
     177booking_id – numeric, required 
     178date – date, required 
     179start_time – time, required 
     180end_time – time, required 
     181status – text, required 
    249182
    250183== Entity: Band
    251184
    252185Explanation:
    253 The Band entity represents music performers available for weddings. It stores information needed for selection and booking.
    254 
    255 Candidate keys:
    256 
     186The Band entity represents music performers available for weddings.
     187
     188Candidate keys:
    257189band_id – selected as the primary key
    258190
    259191Attributes:
    260 
    261 band_id – numeric, required
    262 
    263 band_name – text, required
    264 
    265 genre – text, required
    266 
    267 equipment – text, optional
    268 
    269 phone_number – text, required
    270 
    271 price_per_hour – numeric, required
     192band_id – numeric, required 
     193band_name – text, required 
     194genre – text, required 
     195equipment – text, optional 
     196phone_number – text, required 
     197price_per_hour – numeric, required 
    272198
    273199== Entity: Band_booking
    274200
    275201Explanation:
    276 The Band_booking entity represents reservations of bands for weddings, including timing and booking status.
    277 
    278 Candidate keys:
    279 
     202The Band_booking entity stores reservations of bands for weddings.
     203
     204Candidate keys:
    280205booking_id – selected as the primary key
    281206
    282207Attributes:
    283 
    284 booking_id – numeric, required
    285 
    286 date – date, required
    287 
    288 start_time – time, required
    289 
    290 end_time – time, required
    291 
    292 status – text, required
     208booking_id – numeric, required 
     209date – date, required 
     210start_time – time, required 
     211end_time – time, required 
     212status – text, required 
    293213
    294214== Entity: Church
    … …  
    298218
    299219Candidate keys:
    300 
    301220church_id – selected as the primary key
    302221
    303222Attributes:
    304 
    305 church_id – numeric, required
    306 
    307 name – text, required
    308 
    309 location – text, required
    310 
    311 contact – text, required
     223church_id – numeric, required 
     224name – text, required 
     225location – text, required 
     226contact – text, required 
    312227
    313228== Entity: Priest
    314229
    315230Explanation:
    316 The Priest entity represents clergy members who perform wedding ceremonies. It is modeled separately to allow flexible assignment to ceremonies.
    317 
    318 Candidate keys:
    319 
     231The Priest entity represents clergy members who perform wedding ceremonies.
     232
     233Candidate keys:
    320234priest_id – selected as the primary key
    321235
    322236Attributes:
    323 
    324 priest_id – numeric, required
    325 
    326 name – text, required
    327 
    328 contact – text, required
     237priest_id – numeric, required 
     238name – text, required 
     239contact – text, required 
    329240
    330241== Relationships and Constraints
    331242
    332 === User – Wedding
    333 
    334 One User can organize multiple Weddings (1:N)
    335 
    336 Each Wedding belongs to exactly one User (mandatory)
    337 
    338 === Wedding – Event
    339 
    340 One Wedding can have multiple Events (1:N)
    341 
    342 Each Event belongs to exactly one Wedding
    343 
    344 === Wedding – Guest
    345 
    346 One Wedding can invite multiple Guests (1:N)
    347 
    348 Each Guest is associated with one Wedding
    349 
    350 === Guest – Event (RSVP)
    351 
    352 Guests can RSVP to multiple Events and Events can have multiple RSVPs (M:N)
    353 
    354 Event_RSVP stores RSVP status and response date
    355 
    356 === Guest – Event (Attendance)
    357 
    358 Guests can attend multiple Events and Events can have multiple Attendance records (M:N)
    359 
    360 Attendance stores role, seating, and attendance status
    361 
    362 === Wedding – Venue_booking – Venue
    363 
    364 One Wedding can have multiple Venue_bookings
    365 
    366 One Venue can be booked multiple times
    367 
    368 Constraint: no overlapping bookings for the same Venue
    369 
    370 === Wedding – Band_booking – Band
    371 
    372 One Wedding can have multiple Band_bookings
    373 
    374 Constraint: no overlapping bookings for the same Band
    375 
    376 === Wedding – Photographer_booking – Photographer
    377 
    378 One Wedding can have multiple Photographer_bookings
    379 
    380 Constraint: no overlapping bookings for the same Photographer
    381 
    382 === Wedding – Church
    383 
    384 Each Wedding is associated with exactly one Church (1:1)
    385 
    386 A Church cannot be assigned to more than one Wedding at the same date and time
     243User – Wedding:
     244One User can organize multiple Weddings (1:N). Each Wedding belongs to exactly one User.
     245
     246Wedding – Event:
     247One Wedding can have multiple Events (1:N). Each Event belongs to one Wedding.
     248
     249Wedding – Guest:
     250One Wedding can invite multiple Guests (1:N).
     251
     252Guest – Event (RSVP):
     253Guests can RSVP to multiple Events and Events can have multiple RSVPs (M:N).
     254
     255Guest – Event (Attendance):
     256Guests can attend multiple Events and Events can have multiple Attendance records (M:N).
     257
     258Wedding – Venue_booking – Venue:
     259A Wedding can have multiple Venue bookings and a Venue can be booked multiple times. Overlapping bookings for the same venue are not allowed.
     260
     261Wedding – Band_booking – Band:
     262A Wedding can book multiple Bands. Overlapping bookings for the same band are not allowed.
     263
     264Wedding – Photographer_booking – Photographer:
     265A Wedding can book multiple Photographers. Overlapping bookings for the same photographer are not allowed.
     266
     267Wedding – Church:
     268Each Wedding is associated with exactly one Church. A Church cannot be assigned to more than one Wedding at the same date and time.
    387269
    388270== Entity-Relationship Model History
    389271
    390 v0.1 – Initial ER model
    391 
    392 v0.2 – Updated Church–Wedding relationship
    393 
    394 v0.3 – Refined Church–Wedding constraints
    395 
    396 v0.4 – Fourth version of the ER model with refined Church–Wedding relationship
    397 
    398 v0.5 – Final version of the ER model with improved clarity, consistency, and full alignment with the relational database design
     272v0.1 – Initial ER model 
     273v0.2 – Updated Church–Wedding relationship 
     274v0.3 – Refined Church–Wedding constraints 
     275v0.4 – Improved clarity and structure 
     276v0.5 – Final ER model aligned with the relational database design