Changes between Version 1 and Version 2 of P2

Timestamp:

01/03/26 13:28:43 (2 weeks ago)

Author:

193284

Comment:

Added logical and physical database design including relational schema and SQL DDL

P2

@@ -1 @@
-= Phase P2 – Relational Model
+== Logical Database Design (Relational Schema)
 
-This phase describes the relational schema derived from the ER model.
+The logical database design is derived from the ER model and defines the relational schema
+with primary and foreign keys.
+
+User(user_id PK, first_name, last_name, email, phone_number, gender, birthday)
+
+Wedding(wedding_id PK, date, budget, notes, user_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)
+
+Attendance(attendance_id PK, status, role, table_number, guest_id FK, event_id FK)
+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)
+
+
+== Physical Database Design (SQL DDL)
+
+The following SQL DDL statements illustrate the creation of the core database tables.
+Only representative tables are shown; the remaining tables follow the same design principles.
+
+{{{
+CREATE TABLE User (
+    user_id INT PRIMARY KEY,
+    first_name VARCHAR(50) NOT NULL,
+    last_name VARCHAR(50) NOT NULL,
+    email VARCHAR(100) UNIQUE NOT NULL,
+    phone_number VARCHAR(20),
+    gender VARCHAR(10),
+    birthday DATE
+);
+
+CREATE TABLE Wedding (
+    wedding_id INT PRIMARY KEY,
+    date DATE NOT NULL,
+    budget DECIMAL(10,2),
+    notes TEXT,
+    user_id INT NOT NULL,
+    FOREIGN KEY (user_id) REFERENCES User(user_id)
+);
+
+CREATE TABLE Event (
+    event_id INT PRIMARY KEY,
+    event_type VARCHAR(50) NOT NULL,
+    date DATE NOT NULL,
+    start_time TIME NOT NULL,
+    end_time TIME NOT NULL,
+    status VARCHAR(20) NOT NULL,
+    wedding_id INT NOT NULL,
+    FOREIGN KEY (wedding_id) REFERENCES Wedding(wedding_id)
+);
+}}}
+
+
+== Constraints and Assumptions
+
+* Primary keys uniquely identify each entity.
+* Foreign keys enforce referential integrity.
+* Each wedding is managed by exactly one user.
+* Bookings cannot overlap for the same resource.