Changes between Version 14 and Version 15 of P2


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

--

Legend:

Unmodified
Added
Removed
Modified

  • P2

    v14 v15  
    1 = Relational Design
     1= Logical and Physical Design
    22
    3 == Description
    4 This page describes the relational database design of the Wedding Planner system. The relational model is derived directly from the final ER model and defines the database tables, their attributes, primary keys, foreign keys, and integrity constraints. The design ensures data consistency, avoids redundancy, and supports all system requirements.
     3== Relational Schema (Mapping Transformation)
    54
    65== Diagram
    76[[Image(final_version.png, width=100%)]]
    87
    9 == Relational Schema
     8=== Notation
    109
    11 == Table: user
    12 Stores registered users who organize weddings.
     10* primary keys – **bold and underlined**
     11* NOT NULL attributes – **bold**
     12* foreign keys – marked with * after the attribute name and the referenced table in parentheses
     13* other attributes – no special marking
    1314
    14 user(
    15     user_id PK,
    16     first_name,
    17     last_name,
    18     email UNIQUE,
    19     phone_number,
    20     gender,
    21     birthday
    22 )
     15=== Tables
    2316
    24 == Table: wedding
    25 Represents weddings created and managed by users. Acts as the central table of the system.
     17* USER (**__user_id__**, **first_name**, **last_name**, **email**, phone_number, gender, birthday)
    2618
    27 wedding(
    28     wedding_id PK,
    29     date,
    30     budget,
    31     notes,
    32     type,
    33     status,
    34     user_id FK → user(user_id)
    35 )
     19* WEDDING (**__wedding_id__**, **date**, budget, notes, type, status, **user_id*** (USER))
    3620
    37 == Table: church
    38 Represents churches where wedding ceremonies can take place. Each wedding can be associated with one church.
     21* CHURCH (**__church_id__**, **name**, **location**, **contact**, wedding_id* (WEDDING))
    3922
    40 church(
    41     church_id PK,
    42     name,
    43     location,
    44     contact,
    45     wedding_id FK → wedding(wedding_id) UNIQUE
    46 )
     23* PRIEST (**__priest_id__**, **name**, **contact**, **church_id*** (CHURCH))
    4724
    48 == Table: priest
    49 Represents priests associated with churches.
     25* EVENT (**__event_id__**, **event_type**, **date**, **start_time**, **end_time**, **status**, **wedding_id*** (WEDDING))
    5026
    51 priest(
    52     priest_id PK,
    53     name,
    54     contact,
    55     church_id FK → church(church_id)
    56 )
     27* GUEST (**__guest_id__**, **first_name**, **last_name**, email, **wedding_id*** (WEDDING))
    5728
    58 == Table: event
    59 Represents events that are part of a wedding (e.g. ceremony, reception).
     29* EVENT_RSVP (**__response_id__**, **status**, **response_date**, **guest_id*** (GUEST), **event_id*** (EVENT))
    6030
    61 event(
    62     event_id PK,
    63     event_type,
    64     date,
    65     start_time,
    66     end_time,
    67     status,
    68     wedding_id FK → wedding(wedding_id)
    69 )
     31* ATTENDANCE (**__attendance_id__**, **status**, table_number, **role**, **guest_id*** (GUEST), **event_id*** (EVENT))
    7032
    71 == Table: guest
    72 Represents guests invited to a wedding.
     33* VENUE_TYPE (**__type_id__**, **type_name**)
    7334
    74 guest(
    75     guest_id PK,
    76     first_name,
    77     last_name,
    78     email,
    79     wedding_id FK → wedding(wedding_id)
    80 )
     35* VENUE (**__venue_id__**, **name**, **location**, **city**, **address**, **capacity**, menu, phone_number, **price_per_guest**, **type_id*** (VENUE_TYPE))
    8136
    82 == Table: event_rsvp
    83 Stores guest responses to event invitations. Resolves the many-to-many relationship between guest and event.
     37* VENUE_BOOKING (**__booking_id__**, **date**, **start_time**, **end_time**, **status**, **price**, **venue_id*** (VENUE), **wedding_id*** (WEDDING))
    8438
    85 event_rsvp(
    86     response_id PK,
    87     status,
    88     response_date,
    89     guest_id FK → guest(guest_id),
    90     event_id FK → event(event_id),
    91     UNIQUE (guest_id, event_id)
    92 )
     39* PHOTOGRAPHER (**__photographer_id__**, **name**, **email**, **phone_number**, **price_per_hour**)
    9340
    94 == Table: attendance
    95 Represents actual guest participation in events, including seating and role information.
     41* PHOTOGRAPHER_BOOKING (**__booking_id__**, **date**, **start_time**, **end_time**, **status**, **photographer_id*** (PHOTOGRAPHER), **wedding_id*** (WEDDING))
    9642
    97 attendance(
    98     attendance_id PK,
    99     status,
    100     table_number,
    101     role,
    102     guest_id FK → guest(guest_id),
    103     event_id FK → event(event_id),
    104     UNIQUE (guest_id, event_id)
    105 )
     43* BAND (**__band_id__**, **band_name**, **genre**, equipment, **phone_number**, **price_per_hour**)
    10644
    107 == Table: venue_type
    108 Defines categories of venues.
     45* BAND_BOOKING (**__booking_id__**, **date**, **start_time**, **end_time**, **status**, **band_id*** (BAND), **wedding_id*** (WEDDING))
    10946
    110 venue_type(
    111     type_id PK,
    112     type_name UNIQUE
    113 )
     47* REGISTRAR (**__registrar_id__**, **name**, contact, location, working_hours)
    11448
    115 == Table: venue
    116 Represents venues available for wedding events.
     49* REGISTRAR_BOOKING (**__booking_id__**, **date**, **start_time**, **end_time**, **status**, **registrar_id*** (REGISTRAR))
    11750
    118 venue(
    119     venue_id PK,
    120     name,
    121     location,
    122     city,
    123     address,
    124     capacity,
    125     menu,
    126     phone_number,
    127     price_per_guest,
    128     type_id FK → venue_type(type_id)
    129 )
     51== DDL Script for Table Creation and Deletion
    13052
    131 == Table: venue_booking
    132 Represents reservations of venues for weddings.
     53The DDL script implements the relational schema described above.
     54It defines all tables, primary and foreign keys, as well as constraints required to ensure data integrity.
    13355
    134 venue_booking(
    135     booking_id PK,
    136     date,
    137     start_time,
    138     end_time,
    139     status,
    140     price,
    141     venue_id FK → venue(venue_id),
    142     wedding_id FK → wedding(wedding_id)
    143 )
     56[[Attachment(schema_creation.sql)]]
    14457
    145 == Table: photographer
    146 Represents photographers available for weddings.
     58== DML Script for Data Population
    14759
    148 photographer(
    149     photographer_id PK,
    150     name,
    151     email UNIQUE,
    152     phone_number,
    153     price_per_hour
    154 )
     60The DML script contains sample data used for testing the system and demonstrating the relationships between the tables.
    15561
    156 == Table: photographer_booking
    157 Represents photographer reservations for weddings.
    158 
    159 photographer_booking(
    160     booking_id PK,
    161     date,
    162     start_time,
    163     end_time,
    164     status,
    165     photographer_id FK → photographer(photographer_id),
    166     wedding_id FK → wedding(wedding_id)
    167 )
    168 
    169 == Table: band
    170 Represents music bands available for weddings.
    171 
    172 band(
    173     band_id PK,
    174     band_name,
    175     genre,
    176     equipment,
    177     phone_number,
    178     price_per_hour
    179 )
    180 
    181 == Table: band_booking
    182 Represents band reservations for weddings.
    183 
    184 band_booking(
    185     booking_id PK,
    186     date,
    187     start_time,
    188     end_time,
    189     status,
    190     band_id FK → band(band_id),
    191     wedding_id FK → wedding(wedding_id)
    192 )
    193 
    194 == Table: registrar
    195 Represents civil registrars who can officiate weddings.
    196 
    197 registrar(
    198     registrar_id PK,
    199     name,
    200     contact,
    201     location,
    202     working_hours
    203 )
    204 
    205 == Table: registrar_booking
    206 Represents registrar reservations.
    207 
    208 registrar_booking(
    209     booking_id PK,
    210     date,
    211     start_time,
    212     end_time,
    213     status,
    214     registrar_id FK → registrar(registrar_id)
    215 )
    216 
    217 == Integrity Constraints
    218 
    219 * Primary keys uniquely identify each table row.
    220 * Foreign keys enforce referential integrity between related tables.
    221 * UNIQUE constraints prevent duplicate RSVP and attendance records.
    222 * CHECK constraints ensure valid time intervals (end_time > start_time).
    223 * Cascading rules ensure consistent updates and deletions.
    224 
    225 == Mapping from ER Model
    226 Each entity in the ER model is mapped to a table.
    227 Many-to-many relationships are resolved using associative tables (event_rsvp, attendance, venue_booking, band_booking, photographer_booking).
    228 All booking entities are associated directly with wedding, reflecting the final ER model design.
    229 
    230 == Relational Design History
    231 
    232 v0.1 – Initial relational schema 
    233 v0.2 – Alignment with updated ER model 
    234 v0.3 – Removed incorrect event-level reservations 
    235 v0.4 – Refined constraints and keys 
    236 v0.5 – Final relational design aligned with ER Model Version 5
     62[[Attachment(data_load.sql)]]