Changes between Version 5 and Version 6 of P2


Ignore:
Timestamp:
01/10/26 16:57:38 (10 days ago)
Author:
193284
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • P2

    v5 v6  
    44== Relational Design
    55
    6 This phase presents the logical and physical design of the database based on the ER model from Phase P1.
    7 The ER model was transformed into a relational schema using partial transformation.
    8 All entities and relationships were mapped into relational tables with clearly defined primary keys, foreign keys, and constraints.
    9 The schema reflects the updated ER model, including the 1:1 relationship between Wedding and Church, without a separate Church_booking entity.
     6In this phase, the ER model from Phase P1 was transformed into a relational schema using partial transformation.
     7All entities and relationships were mapped into relational tables with defined primary keys, foreign keys, and integrity constraints.
     8The 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).
    109
    1110
    1211== Relational Schema
    13 User(user_id PK, first_name, last_name, email UNIQUE, phone_number, gender, birthday)
    14 Wedding(wedding_id PK, date, budget, notes, user_id FK)
    15 Event(event_id PK, event_type, date, start_time, end_time, status, wedding_id FK)
    16 Guest(guest_id PK, first_name, last_name, email, wedding_id FK)
    17 Event_RSVP(response_id PK, status, response_date, guest_id FK, event_id FK)
     12
     13User(user_id PK, first_name, last_name, email UNIQUE, phone_number, gender, birthday) 
     14Wedding(wedding_id PK, date, budget, notes, user_id FK) 
     15Church(church_id PK, name, location, contact, wedding_id FK UNIQUE) 
     16Priest(priest_id PK, name, contact, church_id FK) 
     17Venue_Type(type_id PK, type_name) 
     18Venue(venue_id PK, name, location, city, address, capacity, menu, phone_number, price_per_guest, type_id FK) 
     19Venue_Booking(booking_id PK, date, start_time, end_time, status, price, venue_id FK, wedding_id FK) 
     20Photographer(photographer_id PK, name, email, phone_number, price_per_hour) 
     21Photographer_Booking(booking_id PK, date, start_time, end_time, status, photographer_id FK, wedding_id FK) 
     22Band(band_id PK, band_name, genre, equipment, phone_number, price_per_hour) 
     23Band_Booking(booking_id PK, date, start_time, end_time, status, band_id FK, wedding_id FK) 
     24Event(event_id PK, event_type, date, start_time, end_time, status, wedding_id FK) 
     25Guest(guest_id PK, first_name, last_name, email, wedding_id FK) 
     26Event_RSVP(response_id PK, status, response_date, guest_id FK, event_id FK) 
    1827Attendance(attendance_id PK, status, table_number, role, guest_id FK, event_id FK)
    19 Venue(venue_id PK, name, location, city, address, capacity, menu, phone_number, price_per_guest, type_id FK)
    20 Venue_Type(type_id PK, type_name)
    21 Venue_booking(booking_id PK, date, start_time, end_time, status, price, venue_id FK, wedding_id FK)
    22 Photographer(photographer_id PK, name, email, phone_number, price_per_hour)
    23 Photographer_booking(booking_id PK, date, start_time, end_time, status, photographer_id FK, wedding_id FK)
    24 Band(band_id PK, band_name, genre, equipment, phone_number, price_per_hour)
    25 Band_booking(booking_id PK, date, start_time, end_time, status, band_id FK, wedding_id FK)
    26 Church(church_id PK, name, location, contact, wedding_id FK UNIQUE)
    27 Priest(priest_id PK, name, contact, church_id FK)
    2828
    2929
    30 == Constraints
     30== Relational Diagram
    3131
    32 • Referential integrity enforced through foreign keys.
    33 • UNIQUE constraints applied where required (e.g., User.email, Church.wedding_id).
    34 • Time-overlap constraints apply for Venue_booking, Band_booking, and Photographer_booking to prevent conflicting reservations.
     32The relational schema diagram was generated in DBeaver (ER Diagram tab) from the created objects in the project schema and exported as an image file. 
     33The diagram uses crow-foot notation and presents all tables and relationships.
    3534
     35Attachment: relational_schema.jpg
    3636
    37 == DDL Script
     37== DDL Script for creating the database schema and objects
     38[[attachment:schema_creation.sql|DDL Script]]
    3839
    39 The SQL script for creating the database schema, including all tables, constraints, and relationships, is provided as an attachment.
    40 File: schema_creation.sql
    41 
    42 
    43 == DML Script
    44 
    45 The SQL script for populating the database with sample data is provided as an attachment.
    46 File: data_load.sql
    47 
     40== DML script for filling tables with data
     41[[attachment:data_load.sql|DML Script]]