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:

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:

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.

The exported relational diagram is attached to this page:

relational_schema.jpg​