= 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.

[[Image(wedding_planner_version_6_erd.png, width=100%)]]

== DDL Script

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

[[attachment: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.

[[attachment:data_load.sql]]