Changes between Version 3 and Version 4 of P2


Ignore:
Timestamp:
01/09/26 17:14:40 (11 days ago)
Author:
193284
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • P2

    v3 v4  
    1 = P2 - Logical and Physical Design, Database Creation (DDL)
     1= Phase P2: Logical and Physical Design (DDL)
    22
    3 == Description
    4 This phase covers the logical and physical database design derived from the ER model.
    5 It includes the relational schema (tables with primary and foreign keys) and representative SQL DDL statements.
    63
    7 == Logical Database Design (Relational Schema)
     4== Relational Design
    85
    9 The logical database design is derived from the ER model and defines the relational schema
    10 with primary and foreign keys.
     6This phase presents the logical and physical design of the database based on the ER model from Phase P1.
     7The ER model was transformed into a relational schema using partial transformation.
     8All entities and relationships were mapped into relational tables with clearly defined primary keys, foreign keys, and constraints.
     9The schema reflects the updated ER model, including the 1:1 relationship between Wedding and Church, without a separate Church_booking entity.
    1110
     11
     12== Relational Schema
    1213User(user_id PK, first_name, last_name, email UNIQUE, phone_number, gender, birthday)
     14Wedding(wedding_id PK, date, budget, notes, user_id FK)
     15Event(event_id PK, event_type, date, start_time, end_time, status, wedding_id FK)
     16Guest(guest_id PK, first_name, last_name, email, wedding_id FK)
     17Event_RSVP(response_id PK, status, response_date, guest_id FK, event_id FK)
     18Attendance(attendance_id PK, status, table_number, role, guest_id FK, event_id FK)
     19Venue(venue_id PK, name, location, city, address, capacity, menu, phone_number, price_per_guest, type_id FK)
     20Venue_Type(type_id PK, type_name)
     21Venue_booking(booking_id PK, date, start_time, end_time, status, price, venue_id FK, wedding_id FK)
     22Photographer(photographer_id PK, name, email, phone_number, price_per_hour)
     23Photographer_booking(booking_id PK, date, start_time, end_time, status, photographer_id FK, wedding_id FK)
     24Band(band_id PK, band_name, genre, equipment, phone_number, price_per_hour)
     25Band_booking(booking_id PK, date, start_time, end_time, status, band_id FK, wedding_id FK)
     26Church(church_id PK, name, location, contact, wedding_id FK UNIQUE)
     27Priest(priest_id PK, name, contact, church_id FK)
    1328
    14 Wedding(wedding_id PK, date, budget, notes, user_id FK -> User.user_id)
    1529
    16 Event(event_id PK, event_type, date, start_time, end_time, status, wedding_id FK -> Wedding.wedding_id)
     30== Constraints
    1731
    18 Guest(guest_id PK, first_name, last_name, email, wedding_id FK -> Wedding.wedding_id)
     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.
    1935
    20 Event_RSVP(response_id PK, status, response_date, guest_id FK -> Guest.guest_id, event_id FK -> Event.event_id)
    2136
    22 Attendance(attendance_id PK, status, role, table_number NULL, guest_id FK -> Guest.guest_id, event_id FK -> Event.event_id)
    23 Note: table_number is optional because not all events require seating arrangements.
     37== DDL Script
    2438
    25 Venue_Type(type_id PK, type_name)
     39The SQL script for creating the database schema, including all tables, constraints, and relationships, is provided as an attachment.
     40File: schema_creation.sql
    2641
    27 Venue(venue_id PK, name, location, city, address, capacity, menu, phone_number, price_per_guest, type_id FK -> Venue_Type.type_id)
    2842
    29 Venue_booking(booking_id PK, date, start_time, end_time, status, price, wedding_id FK -> Wedding.wedding_id, venue_id FK -> Venue.venue_id)
     43== DML Script
    3044
    31 Band(band_id PK, band_name, genre, equipment, phone_number, price_per_hour)
     45The SQL script for populating the database with sample data is provided as an attachment.
     46File: data_load.sql
    3247
    33 Band_booking(booking_id PK, date, start_time, end_time, status, wedding_id FK -> Wedding.wedding_id, band_id FK -> Band.band_id)
    3448
    35 Photographer(photographer_id PK, name, email UNIQUE, phone_number, price_per_hour)
     49== Relational Diagram
    3650
    37 Photographer_booking(booking_id PK, date, start_time, end_time, status, wedding_id FK -> Wedding.wedding_id, photographer_id FK -> Photographer.photographer_id)
    38 
    39 Church(church_id PK, name, location, contact)
    40 
    41 Church_booking(booking_id PK, date, start_time, end_time, status, wedding_id FK -> Wedding.wedding_id, church_id FK -> Church.church_id)
    42 
    43 Registrar(registrar_id PK, name, contact, location, working_hours)
    44 
    45 Registrar_booking(booking_id PK, date, start_time, end_time, status, price, wedding_id FK -> Wedding.wedding_id, registrar_id FK -> Registrar.registrar_id)
    46 
    47 Priest(priest_id PK, name, contact)
    48 (Connected to Church as 1:N if modeled in ER)
    49 
    50 == Physical Database Design (SQL DDL)
    51 
    52 The following SQL DDL statements illustrate the creation of the core database tables.
    53 Only representative tables are shown; the remaining tables follow the same design principles.
    54 
    55 {{{
    56 CREATE TABLE User (
    57     user_id INT PRIMARY KEY,
    58     first_name VARCHAR(50) NOT NULL,
    59     last_name VARCHAR(50) NOT NULL,
    60     email VARCHAR(100) UNIQUE NOT NULL,
    61     phone_number VARCHAR(20),
    62     gender VARCHAR(10),
    63     birthday DATE
    64 );
    65 
    66 CREATE TABLE Wedding (
    67     wedding_id INT PRIMARY KEY,
    68     date DATE NOT NULL,
    69     budget DECIMAL(10,2),
    70     notes TEXT,
    71     user_id INT NOT NULL,
    72     FOREIGN KEY (user_id) REFERENCES User(user_id)
    73 );
    74 
    75 CREATE TABLE Event (
    76     event_id INT PRIMARY KEY,
    77     event_type VARCHAR(50) NOT NULL,
    78     date DATE NOT NULL,
    79     start_time TIME NOT NULL,
    80     end_time TIME NOT NULL,
    81     status VARCHAR(20) NOT NULL,
    82     wedding_id INT NOT NULL,
    83     FOREIGN KEY (wedding_id) REFERENCES Wedding(wedding_id)
    84 );
    85 
    86 CREATE TABLE Venue_Type (
    87     type_id INT PRIMARY KEY,
    88     type_name VARCHAR(50) NOT NULL
    89 );
    90 
    91 CREATE TABLE Venue (
    92     venue_id INT PRIMARY KEY,
    93     name VARCHAR(100) NOT NULL,
    94     location VARCHAR(100) NOT NULL,
    95     city VARCHAR(50) NOT NULL,
    96     address VARCHAR(150) NOT NULL,
    97     capacity INT NOT NULL,
    98     menu TEXT,
    99     phone_number VARCHAR(20),
    100     price_per_guest DECIMAL(10,2) NOT NULL,
    101     type_id INT NOT NULL,
    102     FOREIGN KEY (type_id) REFERENCES Venue_Type(type_id)
    103 );
    104 
    105 CREATE TABLE Venue_booking (
    106     booking_id INT PRIMARY KEY,
    107     date DATE NOT NULL,
    108     start_time TIME NOT NULL,
    109     end_time TIME NOT NULL,
    110     status VARCHAR(20) NOT NULL,
    111     price DECIMAL(10,2) NOT NULL,
    112     wedding_id INT NOT NULL,
    113     venue_id INT NOT NULL,
    114     FOREIGN KEY (wedding_id) REFERENCES Wedding(wedding_id),
    115     FOREIGN KEY (venue_id) REFERENCES Venue(venue_id)
    116 );
    117 }}}
    118 
    119 == Constraints and Assumptions
    120 
    121 * Primary keys uniquely identify each entity.
    122 * Foreign keys enforce referential integrity.
    123 * Each wedding is managed by exactly one user.
    124 * RSVP and Attendance link Guests to Events (M:N relationships resolved with associative entities).
    125 * Booking tables (Venue_booking, Band_booking, Photographer_booking, Church_booking, Registrar_booking) prevent double-booking:
    126   a resource cannot be reserved in overlapping time intervals on the same date (enforced at application level and/or by additional database checks).
     51The relational schema diagram was generated using DBeaver with crow-foot notation and reflects the final relational design.
     52[[Image(relational_schema.jpg, width=100%)]]