| | 1 | = Relational Design = |
| | 2 | |
| | 3 | == Relational Schema == |
| | 4 | |
| | 5 | The relational schema was derived from the ER model using '''partial transformation'''. Primary keys are '''underlined''', foreign keys are marked with ''(FK)''. |
| | 6 | |
| | 7 | {{{ |
| | 8 | user_types(__type_id__, type_name, description) |
| | 9 | |
| | 10 | users(__user_id__, first_name, last_name, email, password, type_id(FK)) |
| | 11 | FK: type_id → user_types(type_id) |
| | 12 | |
| | 13 | resource_types(__type_id__, type_name, is_physical) |
| | 14 | |
| | 15 | locations(__location_id__, building, room) |
| | 16 | UNIQUE: (building, room) |
| | 17 | |
| | 18 | resources(__resource_id__, name, description, available_from, available_to, available_weekends, type_id(FK), location_id(FK)) |
| | 19 | FK: type_id → resource_types(type_id) |
| | 20 | FK: location_id → locations(location_id) [NULLABLE] |
| | 21 | |
| | 22 | reservations(__reservation_id__, start_time, end_time, status, purpose, created_at, recurrence_group_id, user_id(FK), approved_by(FK), resource_id(FK)) |
| | 23 | FK: user_id → users(user_id) |
| | 24 | FK: approved_by → users(user_id) [NULLABLE] |
| | 25 | FK: resource_id → resources(resource_id) |
| | 26 | }}} |
| | 27 | |
| | 28 | == DDL Script == |
| | 29 | |
| | 30 | [attachment:schema_creation.sql] |
| | 31 | |
| | 32 | == DML Script == |
| | 33 | |
| | 34 | [attachment:data_load.sql] |
| | 35 | |
| | 36 | == Relational Diagram == |
| | 37 | |
| | 38 | [[Image(relational_schema.png, width=100%)]] |
