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

== Overview

In Phase P2, the conceptual ER model from Phase P1 was transformed into a logical and physical relational database design.
The goal of this phase is to ensure data integrity, correct relational structure, and implementation of business logic rules
through appropriate constraints at the database level.

The database schema was implemented in PostgreSQL and verified using DBeaver.

== Relational Design

The ER model from Phase P1 was transformed into a relational schema using standard ER-to-Relational mapping rules.
All entities were mapped into relational tables with primary keys, while relationships were implemented using foreign keys
and associative (booking) tables for N:M relationships.

Data integrity and business rules are enforced using:
* PRIMARY KEY and FOREIGN KEY constraints
* UNIQUE constraints
* CHECK constraints (business logic validation)
* Referential actions (ON UPDATE / ON DELETE)

A 1:1 relationship between Wedding and Church is implemented using a UNIQUE foreign key in the Church table.

== Relational Schema

User(user_id PK, first_name, last_name, email UNIQUE, phone_number, gender, birthday)  
Wedding(wedding_id PK, date, budget, notes, user_id FK)  
Church(church_id PK, name, location, contact, wedding_id FK UNIQUE)  
Priest(priest_id PK, name, contact, church_id FK)  

Venue_Type(type_id PK, type_name UNIQUE)  
Venue(venue_id PK, name, location, city, address, capacity, menu, phone_number, price_per_guest, type_id FK)  
Venue_Booking(booking_id PK, date, start_time, end_time, status, price, venue_id FK, wedding_id FK)  

Photographer(photographer_id PK, name, email UNIQUE, phone_number, price_per_hour)  
Photographer_Booking(booking_id PK, date, start_time, end_time, status, photographer_id FK, wedding_id FK)  

Band(band_id PK, band_name, genre, equipment, phone_number, price_per_hour)  
Band_Booking(booking_id PK, date, start_time, end_time, status, band_id FK, wedding_id FK)  

Registrar(registrar_id PK, name, contact, location, working_hours)  
Registrar_Booking(booking_id PK, date, start_time, end_time, status, registrar_id FK)  

Event(event_id PK, event_type, date, start_time, end_time, status, wedding_id FK)  
Guest(guest_id PK, first_name, last_name, email, wedding_id FK)  
Event_RSVP(response_id PK, status, response_date, guest_id FK, event_id FK)  
Attendance(attendance_id PK, status, table_number, role, guest_id FK, event_id FK)

== Data Integrity and Business Logic Constraints

Data integrity is enforced directly at the database level using constraints defined in the DDL script:

* Referential integrity is enforced through FOREIGN KEY constraints between related tables
* Uniqueness constraints ensure correct cardinalities (e.g. one Church per Wedding)
* CHECK constraints enforce business rules such as:
  - end_time must be greater than start_time for events and bookings
  - numeric values (prices, capacity) must be positive
  - table_number must be greater than zero if provided
* Cascading actions ensure consistency when related records are updated or deleted

These constraints ensure that invalid or inconsistent data cannot be inserted into the database.

== Relational Diagram

The relational diagram was generated directly from the PostgreSQL schema using DBeaver.
It presents all tables, primary keys, foreign keys, and relationships using crow-foot notation.

[[Image(db_202526z_va_prj_wedding_planner v.2 - project.png)]]

== DDL Script

The DDL script defines the complete database schema, including tables, constraints, and indexes.

[[attachment:schema_creation.sql|DDL Script]]

== DML Script

The DML script inserts sample data into all tables, ensuring consistency with the defined constraints.

[[attachment:data_load.sql|DML Script]]