Changes between Version 8 and Version 9 of P2


Ignore:
Timestamp:
01/26/26 23:25:42 (2 weeks ago)
Author:
193284
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • P2

    v8 v9  
    1 = Phase P2: Logical and Physical Design (DDL)
     1= Phase P2: Logical and Physical Design (DDL & DML)
    22
     3== Overview
     4
     5In Phase P2, the conceptual ER model from Phase P1 was transformed into a logical and physical relational database design.
     6The goal of this phase is to ensure data integrity, correct relational structure, and implementation of business logic rules
     7through appropriate constraints at the database level.
     8
     9The database schema was implemented in PostgreSQL and verified using DBeaver.
    310
    411== Relational Design
    512
    6 In this phase, the ER model from Phase P1 was transformed into a relational schema using partial transformation.
    7 All entities and relationships were mapped into relational tables with defined primary keys, foreign keys, and integrity constraints.
    8 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).
     13The ER model from Phase P1 was transformed into a relational schema using standard ER-to-Relational mapping rules.
     14All entities were mapped into relational tables with primary keys, while relationships were implemented using foreign keys
     15and associative (booking) tables for N:M relationships.
    916
     17Data integrity and business rules are enforced using:
     18* PRIMARY KEY and FOREIGN KEY constraints
     19* UNIQUE constraints
     20* CHECK constraints (business logic validation)
     21* Referential actions (ON UPDATE / ON DELETE)
     22
     23A 1:1 relationship between Wedding and Church is implemented using a UNIQUE foreign key in the Church table.
    1024
    1125== Relational Schema
    … …  
    1529Church(church_id PK, name, location, contact, wedding_id FK UNIQUE) 
    1630Priest(priest_id PK, name, contact, church_id FK) 
    17 Venue_Type(type_id PK, type_name) 
     31
     32Venue_Type(type_id PK, type_name UNIQUE) 
    1833Venue(venue_id PK, name, location, city, address, capacity, menu, phone_number, price_per_guest, type_id FK) 
    1934Venue_Booking(booking_id PK, date, start_time, end_time, status, price, venue_id FK, wedding_id FK) 
    20 Photographer(photographer_id PK, name, email, phone_number, price_per_hour) 
     35
     36Photographer(photographer_id PK, name, email UNIQUE, phone_number, price_per_hour) 
    2137Photographer_Booking(booking_id PK, date, start_time, end_time, status, photographer_id FK, wedding_id FK) 
     38
    2239Band(band_id PK, band_name, genre, equipment, phone_number, price_per_hour) 
    2340Band_Booking(booking_id PK, date, start_time, end_time, status, band_id FK, wedding_id FK) 
     41
     42Registrar(registrar_id PK, name, contact, location, working_hours) 
     43Registrar_Booking(booking_id PK, date, start_time, end_time, status, registrar_id FK) 
     44
    2445Event(event_id PK, event_type, date, start_time, end_time, status, wedding_id FK) 
    2546Guest(guest_id PK, first_name, last_name, email, wedding_id FK) 
    … …  
    2748Attendance(attendance_id PK, status, table_number, role, guest_id FK, event_id FK)
    2849
     50== Data Integrity and Business Logic Constraints
     51
     52Data integrity is enforced directly at the database level using constraints defined in the DDL script:
     53
     54* Referential integrity is enforced through FOREIGN KEY constraints between related tables
     55* Uniqueness constraints ensure correct cardinalities (e.g. one Church per Wedding)
     56* CHECK constraints enforce business rules such as:
     57  - end_time must be greater than start_time for events and bookings
     58  - numeric values (prices, capacity) must be positive
     59  - table_number must be greater than zero if provided
     60* Cascading actions ensure consistency when related records are updated or deleted
     61
     62These constraints ensure that invalid or inconsistent data cannot be inserted into the database.
    2963
    3064== Relational Diagram
    3165
    32 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. 
    33 The diagram uses crow-foot notation and presents all tables and relationships.
     66The relational diagram was generated directly from the PostgreSQL schema using DBeaver.
     67It presents all tables, primary keys, foreign keys, and relationships using crow-foot notation.
    3468
    35 Attachment: relational_schema.jpg
     69[[Image(db_202526z_va_prj_wedding_planner v.2 - project.png)]]
    3670
    37 == DDL Script for creating the database schema and objects
     71== DDL Script
     72
     73The DDL script defines the complete database schema, including tables, constraints, and indexes.
     74
    3875[[attachment:schema_creation.sql|DDL Script]]
    3976
    40 == DML script for filling tables with data
     77== DML Script
     78
     79The DML script inserts sample data into all tables, ensuring consistency with the defined constraints.
     80
    4181[[attachment:data_load.sql|DML Script]]
    42 
    43 
    44 == Relational Diagram ==
    45 
    46 [[Image(db_202526z_va_prj_wedding_planner2025 - project.png)]]
    47