Phase P2: Logical and Physical Design (DDL)

Relational Design

This phase presents the logical and physical design of the database based on the ER model from Phase P1. The ER model was transformed into a relational schema using partial transformation. All entities and relationships were mapped into relational tables with clearly defined primary keys, foreign keys, and constraints. The schema reflects the updated ER model, including the 1:1 relationship between Wedding and Church, without a separate Church_booking entity.

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) 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) Venue(venue_id PK, name, location, city, address, capacity, menu, phone_number, price_per_guest, type_id FK) Venue_Type(type_id PK, type_name) Venue_booking(booking_id PK, date, start_time, end_time, status, price, venue_id FK, wedding_id FK) Photographer(photographer_id PK, name, email, 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) Church(church_id PK, name, location, contact, wedding_id FK UNIQUE) Priest(priest_id PK, name, contact, church_id FK)

Constraints

• Referential integrity enforced through foreign keys.
• UNIQUE constraints applied where required (e.g., User.email, Church.wedding_id).
• Time-overlap constraints apply for Venue_booking, Band_booking, and Photographer_booking to prevent conflicting reservations.

DDL Script

The SQL script for creating the database schema, including all tables, constraints, and relationships, is provided as an attachment. File: schema_creation.sql

DML Script

The SQL script for populating the database with sample data is provided as an attachment. File: data_load.sql

Relational Diagram

The relational schema diagram was generated using DBeaver with crow-foot notation and reflects the final relational design.