Phase P2: Logical and Physical Design (DDL & DML)

Relational Schema

The relational schema was derived directly from the ER model using standard ER-to-relational mapping rules.

Each strong entity is mapped to a separate table. Each relationship is mapped based on its cardinality. Associative tables are introduced for all many-to-many relationships.

The resulting schema consists of the following relations:

USER(user_id, first_name, last_name, email, phone_number, gender, birthday)

WEDDING(wedding_id, date, budget, notes, type, status, user_id*)

EVENT(event_id, event_type, date, start_time, end_time, status, wedding_id*)

GUEST(guest_id, first_name, last_name, email, wedding_id*)

EVENT_RSVP(response_id, status, response_date, guest_id*, event_id*)

ATTENDANCE(attendance_id, status, table_number, role, guest_id*, event_id*)

VENUE_TYPE(type_id, type_name)

VENUE(venue_id, name, location, city, address, capacity, menu,

phone_number, price_per_guest, type_id*)

VENUE_BOOKING(booking_id, date, start_time, end_time, status, price,

venue_id*, wedding_id*)

PHOTOGRAPHER(photographer_id, name, email, phone_number, price_per_hour)

PHOTOGRAPHER_BOOKING(booking_id, date, start_time, end_time, status,

photographer_id*, wedding_id*)

BAND(band_id, band_name, genre, equipment, phone_number, price_per_hour)

BAND_BOOKING(booking_id, date, start_time, end_time, status,

band_id*, wedding_id*)

CHURCH(church_id, name, location, contact, wedding_id*)

PRIEST(priest_id, name, contact, church_id*)

REGISTRAR(registrar_id, name, contact, location, working_hours)

REGISTRAR_BOOKING(booking_id, date, start_time, end_time, status, registrar_id*)

(* denotes foreign keys)

Relational Diagram

The relational diagram was generated directly from the implemented database schema using DBeaver, ensuring full consistency with the DDL implementation.

DDL Script

The DDL script defines the complete database structure, including all tables, primary keys, foreign keys, and integrity constraints.

schema_creation.sql​

DML Script

The DML script populates the database with sample data that respects all defined constraints and demonstrates correct relationships between entities.

data_load.sql​