= 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 ==

[attachment:schema_creation.sql]

== DML Script ==

[attachment:data_load.sql]

== Relational Diagram ==

[[Image(relational_schema.png, width=100%)]]