Relational Design

Relational Schema

The relational schema was derived from the ER model using partial transformation. Primary keys are underlined, foreign keys are marked with (FK).

user_types(__type_id__, type_name, description)

users(__user_id__, first_name, last_name, email, password, type_id(FK))
    FK: type_id → user_types(type_id)

resource_types(__type_id__, type_name, is_physical)

locations(__location_id__, building, room)
    UNIQUE: (building, room)

resources(__resource_id__, name, description, available_from, available_to, available_weekends, type_id(FK), location_id(FK))
    FK: type_id → resource_types(type_id)
    FK: location_id → locations(location_id) [NULLABLE]

reservations(__reservation_id__, start_time, end_time, status, purpose, created_at, recurrence_group_id, user_id(FK), approved_by(FK), resource_id(FK))
    FK: user_id → users(user_id)
    FK: approved_by → users(user_id) [NULLABLE]
    FK: resource_id → resources(resource_id)

DDL Script

schema_creation.sql​

DML Script

data_load.sql​

Relational Diagram