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)

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 UNIQUE, phone_number, gender, birthday)

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 -> Wedding.wedding_id)

Guest(guest_id PK, first_name, last_name, email, wedding_id FK -> Wedding.wedding_id)

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_Type(type_id PK, type_name)

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)

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)
);

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

• Primary keys uniquely identify each entity.
• Foreign keys enforce referential integrity.
• Each wedding is managed by exactly one user.
• 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).