Changes between Version 2 and Version 3 of P2

Timestamp:

01/03/26 14:44:53 (2 weeks ago)

Author:

193284

Comment:

Updated P2: finalized relational schema + sample SQL DDL and constraints.

P2

@@ +1 @@
+= P2 - Logical and Physical Design, Database Creation (DDL)
+
+== Description
+This phase covers the logical and physical database design derived from the ER model.
+It includes the relational schema (tables with primary and foreign keys) and representative SQL DDL statements.
+
 == Logical Database Design (Relational Schema)
 
@@ -4 +10 @@
 with primary and foreign keys.
 
-User(user_id PK, first_name, last_name, email, phone_number, gender, birthday)
+User(user_id PK, first_name, last_name, email UNIQUE, phone_number, gender, birthday)
 
-Wedding(wedding_id PK, date, budget, notes, user_id FK)
+Wedding(wedding_id PK, date, budget, notes, user_id FK -> User.user_id)
 
-Event(event_id PK, event_type, date, start_time, end_time, status, wedding_id FK)
+Event(event_id PK, event_type, date, start_time, end_time, status, wedding_id FK -> Wedding.wedding_id)
 
-Guest(guest_id PK, first_name, last_name, email, wedding_id FK)
+Guest(guest_id PK, first_name, last_name, email, wedding_id FK -> Wedding.wedding_id)
 
-Attendance(attendance_id PK, status, role, table_number, guest_id FK, event_id FK)
+Event_RSVP(response_id PK, status, response_date, guest_id FK -> Guest.guest_id, event_id FK -> Event.event_id)
+
+Attendance(attendance_id PK, status, role, table_number NULL, guest_id FK -> Guest.guest_id, event_id FK -> Event.event_id)
 Note: table_number is optional because not all events require seating arrangements.
-
-Venue(venue_id PK, name, location, city, address, capacity, menu, phone_number, price_per_guest)
 
 Venue_Type(type_id PK, type_name)
 
-Venue_booking(booking_id PK, date, start_time, end_time, status, price, wedding_id FK, venue_id FK)
+Venue(venue_id PK, name, location, city, address, capacity, menu, phone_number, price_per_guest, type_id FK -> Venue_Type.type_id)
 
+Venue_booking(booking_id PK, date, start_time, end_time, status, price, wedding_id FK -> Wedding.wedding_id, venue_id FK -> Venue.venue_id)
+
+Band(band_id PK, band_name, genre, equipment, phone_number, price_per_hour)
+
+Band_booking(booking_id PK, date, start_time, end_time, status, wedding_id FK -> Wedding.wedding_id, band_id FK -> Band.band_id)
+
+Photographer(photographer_id PK, name, email UNIQUE, phone_number, price_per_hour)
+
+Photographer_booking(booking_id PK, date, start_time, end_time, status, wedding_id FK -> Wedding.wedding_id, photographer_id FK -> Photographer.photographer_id)
+
+Church(church_id PK, name, location, contact)
+
+Church_booking(booking_id PK, date, start_time, end_time, status, wedding_id FK -> Wedding.wedding_id, church_id FK -> Church.church_id)
+
+Registrar(registrar_id PK, name, contact, location, working_hours)
+
+Registrar_booking(booking_id PK, date, start_time, end_time, status, price, wedding_id FK -> Wedding.wedding_id, registrar_id FK -> Registrar.registrar_id)
+
+Priest(priest_id PK, name, contact)
+(Connected to Church as 1:N if modeled in ER)
 
 == Physical Database Design (SQL DDL)
@@ -57 +83 @@
     FOREIGN KEY (wedding_id) REFERENCES Wedding(wedding_id)
 );
+
+CREATE TABLE Venue_Type (
+    type_id INT PRIMARY KEY,
+    type_name VARCHAR(50) NOT NULL
+);
+
+CREATE TABLE Venue (
+    venue_id INT PRIMARY KEY,
+    name VARCHAR(100) NOT NULL,
+    location VARCHAR(100) NOT NULL,
+    city VARCHAR(50) NOT NULL,
+    address VARCHAR(150) NOT NULL,
+    capacity INT NOT NULL,
+    menu TEXT,
+    phone_number VARCHAR(20),
+    price_per_guest DECIMAL(10,2) NOT NULL,
+    type_id INT NOT NULL,
+    FOREIGN KEY (type_id) REFERENCES Venue_Type(type_id)
+);
+
+CREATE TABLE Venue_booking (
+    booking_id INT PRIMARY KEY,
+    date DATE NOT NULL,
+    start_time TIME NOT NULL,
+    end_time TIME NOT NULL,
+    status VARCHAR(20) NOT NULL,
+    price DECIMAL(10,2) NOT NULL,
+    wedding_id INT NOT NULL,
+    venue_id INT NOT NULL,
+    FOREIGN KEY (wedding_id) REFERENCES Wedding(wedding_id),
+    FOREIGN KEY (venue_id) REFERENCES Venue(venue_id)
+);
 }}}
-
 
 == Constraints and Assumptions
@@ -65 +122 @@
 * Foreign keys enforce referential integrity.
 * Each wedding is managed by exactly one user.
-* Bookings cannot overlap for the same resource.
+* RSVP and Attendance link Guests to Events (M:N relationships resolved with associative entities).
+* Booking tables (Venue_booking, Band_booking, Photographer_booking, Church_booking, Registrar_booking) prevent double-booking:
+  a resource cannot be reserved in overlapping time intervals on the same date (enforced at application level and/or by additional database checks).