= Relational Design =

== Relational schema ==

This phase transforms the corrected 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, one-to-many relationships are represented using foreign keys, and many-to-many relationships are transformed into separate relations with composite primary keys. Optional participation is represented using nullable foreign keys where appropriate.

In the corrected ER model v.03, ''has_equipment'' is a many-to-many relationship between Rooms and Equipment with the relationship attribute ''quantity''. Therefore, it is transformed into the relation ''room_equipment''. Similarly, ''requests_equipment'' is a many-to-many relationship between Reservations and Equipment with the relationship attribute ''requested_quantity'', so it is transformed into the relation ''reservation_equipment''.

The optional relationship ''includes_room'' between Rooms and Reservations is represented by the nullable foreign key ''room_id'' in the ''reservations'' relation, because a reservation may be equipment-only and may not include a room. The relationship ''makes'' between Users and Reservations is represented by the required foreign key ''user_id'' in ''reservations''. The relationship ''has_approval'' between Reservations and Approvals is represented by the required and unique foreign key ''reservation_id'' in ''approvals'', because one reservation can have at most one approval record.

=== 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 because each room must belong to exactly one building.
* 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
)

This relation is created from the M:N relationship ''has_equipment'' between Rooms and Equipment in the corrected ER model v.03. The relationship attribute ''quantity'' becomes an attribute of this relation.

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.
* room_id is required.
* equipment_id is required.
* 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 exact room-based reservation intervals

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 because each reservation must be created by exactly one user.
* 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.

The uniqueness constraint on (room_id, reservation_date, start_time, end_time) prevents duplicate reservations for the exact same room and time interval. General overlapping intervals are treated as a business rule and are checked by the application/prototype logic. A stricter database-level implementation can be added later using a trigger or exclusion constraint if required.

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

This relation is created from the M:N relationship ''requests_equipment'' between Reservations and Equipment in the corrected ER model v.03. The relationship attribute ''requested_quantity'' becomes an attribute of this relation.

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.
* reservation_id is required.
* equipment_id is required.
* 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 because each approval decision must be created by exactly one authorized user.
* 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]

== Normalization result ==

The formal normalization process performed in Phase P5 confirmed that the relational schema from Phase P2 is already normalized to BCNF. The final normalized model obtained in Phase P5 contains the same relations as the implemented Phase P2 design. Therefore, no structural changes to the PostgreSQL schema, DDL script, DML script, or prototype implementation are required.