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:

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​