Relational Design

Description

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.

Relational Schema

Table: user

Stores registered users who organize weddings.

user(

user_id PK, first_name, last_name, email UNIQUE, phone_number, gender, birthday

)

Table: wedding

Represents weddings created and managed by users. Acts as the central table of the system.

wedding(

wedding_id PK, date, budget, notes, type, status, user_id FK → user(user_id)

)

Table: church

Represents churches where wedding ceremonies can take place. Each wedding can be associated with one church.

church(

church_id PK, name, location, contact, wedding_id FK → wedding(wedding_id) UNIQUE

)

Table: priest

Represents priests associated with churches.

priest(

priest_id PK, name, contact, church_id FK → church(church_id)

)

Table: event

Represents events that are part of a wedding (e.g. ceremony, reception).

event(

event_id PK, event_type, date, start_time, end_time, status, wedding_id FK → wedding(wedding_id)

)

Table: guest

Represents guests invited to a wedding.

guest(

guest_id PK, first_name, last_name, email, wedding_id FK → wedding(wedding_id)

)

Table: event_rsvp

Stores guest responses to event invitations. Resolves the many-to-many relationship between guest and event.

event_rsvp(

response_id PK, status, response_date, guest_id FK → guest(guest_id), event_id FK → event(event_id), UNIQUE (guest_id, event_id)

)

Table: attendance

Represents actual guest participation in events, including seating and role information.

attendance(

attendance_id PK, status, table_number, role, guest_id FK → guest(guest_id), event_id FK → event(event_id), UNIQUE (guest_id, event_id)

)

Table: venue_type

Defines categories of venues.

venue_type(

type_id PK, type_name UNIQUE

)

Table: venue

Represents venues available for wedding events.

venue(

venue_id PK, name, location, city, address, capacity, menu, phone_number, price_per_guest, type_id FK → venue_type(type_id)

)

Table: venue_booking

Represents reservations of venues for weddings.

venue_booking(

booking_id PK, date, start_time, end_time, status, price, venue_id FK → venue(venue_id), wedding_id FK → wedding(wedding_id)

)

Table: photographer

Represents photographers available for weddings.

photographer(

photographer_id PK, name, email UNIQUE, phone_number, price_per_hour

)

Table: photographer_booking

Represents photographer reservations for weddings.

photographer_booking(

booking_id PK, date, start_time, end_time, status, photographer_id FK → photographer(photographer_id), wedding_id FK → wedding(wedding_id)

)

Table: band

Represents music bands available for weddings.

band(

band_id PK, band_name, genre, equipment, phone_number, price_per_hour

)

Table: band_booking

Represents band reservations for weddings.

band_booking(

booking_id PK, date, start_time, end_time, status, band_id FK → band(band_id), wedding_id FK → wedding(wedding_id)

)

Table: registrar

Represents civil registrars who can officiate weddings.

registrar(

registrar_id PK, name, contact, location, working_hours

)

Table: registrar_booking

Represents registrar reservations.

registrar_booking(

booking_id PK, date, start_time, end_time, status, registrar_id FK → registrar(registrar_id)

)

Integrity Constraints

• Primary keys uniquely identify each table row.
• Foreign keys enforce referential integrity between related tables.
• UNIQUE constraints prevent duplicate RSVP and attendance records.
• CHECK constraints ensure valid time intervals (end_time > start_time).
• Cascading rules ensure consistent updates and deletions.

Mapping from ER Model

Each entity in the ER model is mapped to a table. Many-to-many relationships are resolved using associative tables (event_rsvp, attendance, venue_booking, band_booking, photographer_booking). All booking entities are associated directly with wedding, reflecting the final ER model design.

Relational Design History

v0.1 – Initial relational schema v0.2 – Alignment with updated ER model v0.3 – Removed incorrect event-level reservations v0.4 – Refined constraints and keys v0.5 – Final relational design aligned with ER Model Version 5