= Relational Design =

== Relational schema ==

This phase transforms the entity-relationship model from Phase P1 into a relational database schema implemented in PostgreSQL. The database objects are created in the official project schema named '''project'''.

The relational model was created using partial transformation. Strong entity sets are transformed into separate relations, while many-to-many relationships and associative entity sets are transformed into separate relations with composite primary keys. Optional participation is represented using nullable foreign keys where appropriate.

=== Relations ===

'''buildings'''(
'''building_id''' PK,
name,
address
)

Candidate keys:

* building_id
* (name, address)

Constraints:

* building_id is the primary key.
* name is required.
* address is required.
* (name, address) is unique.

'''rooms'''(
'''room_id''' PK,
building_id FK,
room_code AK,
capacity,
type
)

Candidate keys:

* room_id
* room_code

Foreign keys:

* building_id references buildings(building_id)

Constraints:

* room_id is the primary key.
* building_id is required.
* room_code is required and unique.
* capacity must be greater than 0.
* type is restricted to the values: classroom, office, meeting_room, lab.

'''equipment'''(
'''equipment_id''' PK,
name AK,
stock_quantity
)

Candidate keys:

* equipment_id
* name

Constraints:

* equipment_id is the primary key.
* name is required and unique.
* stock_quantity is required and must be greater than or equal to 0.

'''room_equipment'''(
'''room_id''' PK, FK,
'''equipment_id''' PK, FK,
quantity
)

Candidate keys:

* (room_id, equipment_id)

Foreign keys:

* room_id references rooms(room_id)
* equipment_id references equipment(equipment_id)

Constraints:

* (room_id, equipment_id) is the composite primary key.
* quantity is required and must be greater than 0.

'''users'''(
'''user_id''' PK,
username AK,
email AK,
full_name,
role
)

Candidate keys:

* user_id
* username
* email

Constraints:

* user_id is the primary key.
* username is required and unique.
* email is required and unique.
* full_name is required.
* role is restricted to the values: regular, admin, approver.
* email must contain the character @.

'''reservations'''(
'''reservation_id''' PK,
room_id FK,
user_id FK,
reservation_date,
start_time,
end_time,
status
)

Candidate keys:

* reservation_id
* (room_id, reservation_date, start_time, end_time) for room-based reservations

Foreign keys:

* room_id references rooms(room_id)
* user_id references users(user_id)

Constraints:

* reservation_id is the primary key.
* room_id is optional because equipment-only reservations are allowed.
* user_id is required.
* reservation_date is required.
* start_time is required.
* end_time is required and must be greater than start_time.
* status is restricted to the values: pending, approved, rejected, cancelled.
* (room_id, reservation_date, start_time, end_time) is unique for exact room reservation intervals.

'''reservation_equipment'''(
'''reservation_id''' PK, FK,
'''equipment_id''' PK, FK,
requested_quantity
)

Candidate keys:

* (reservation_id, equipment_id)

Foreign keys:

* reservation_id references reservations(reservation_id)
* equipment_id references equipment(equipment_id)

Constraints:

* (reservation_id, equipment_id) is the composite primary key.
* requested_quantity is required and must be greater than 0.

'''approvals'''(
'''approval_id''' PK,
reservation_id FK, AK,
approver_id FK,
decision,
decision_time,
note
)

Candidate keys:

* approval_id
* reservation_id

Foreign keys:

* reservation_id references reservations(reservation_id)
* approver_id references users(user_id)

Constraints:

* approval_id is the primary key.
* reservation_id is required and unique, because one reservation can have at most one approval record.
* approver_id is required.
* decision is restricted to the values: approved, rejected.
* decision_time is required.
* note is optional.

== DDL script for creating the database schema and objects ==

The DDL script recreates the '''project''' schema and creates all required tables, primary keys, foreign keys, unique constraints, and check constraints.

The script is attached to this page:

[attachment:schema_creation.sql schema_creation.sql]

== DML script for filling tables with data ==

The DML script clears the existing data from the created tables and inserts realistic sample data for the Room Reservation System. The inserted data includes buildings, rooms, equipment, equipment assigned to rooms, users, reservations, requested equipment, and approval decisions.

The sample data includes examples of:

* room-only reservations,
* equipment-only reservations,
* reservations that include both a room and equipment,
* approved, rejected, cancelled, and pending reservation statuses.

The script is attached to this page:

[attachment:data_load.sql data_load.sql]

== Relational diagram ==

The relational diagram was generated in DBeaver from the created PostgreSQL tables in the '''project''' schema. The diagram shows the implemented relations and the foreign key references between them.

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

The exported relational diagram is attached to this page:

[attachment:relational_schema.jpg relational_schema.jpg]