Phase P2: Logical and Physical Design (DDL)

Relational Design

In this phase, the ER model from Phase P1 was transformed into a relational schema using partial transformation. All entities and relationships were mapped into relational tables with defined primary keys, foreign keys, and integrity constraints. The schema is aligned with the updated ER model, including the 1:1 relationship between Wedding and Church (implemented through a UNIQUE foreign key in Church).

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) 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, 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) 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)

Relational Diagram

The relational schema diagram was generated in DBeaver (ER Diagram tab) from the created objects in the project schema and exported as an image file. The diagram uses crow-foot notation and presents all tables and relationships.

Attachment: relational_schema.jpg

DDL Script for creating the database schema and objects

DDL Script​

DML script for filling tables with data

DML Script​

Relational Diagram