Logical Database Design (Relational Schema)

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.