Entity-Relationship Model v01

Diagram

(Attached files: ERModel_v01.xml and ERModel_v01.jpg)

Data requirements

Entities

Buildings

*Description: Represents a physical building that contains multiple rooms that can be reserved. *Candidate keys:

• building_id (selected as primary key because it is stable and unique)
• (optional candidate) (name, address) if the institution guarantees uniqueness, but it is not reliable -> not selected

*Attributes:

• building_id (NUMBER(9,2) / or NUMBER) – required, unique identifier (PK)
• name (VARCHAR2(128)) – required
• address (VARCHAR2(128)) – required

Rooms

*Description: Represents a reservable room (classroom/office/meeting room) located in a building. *Candidate keys:

• room_id (selected as primary key because it is stable and unique)
• room_code (candidate key if it is unique across the organization)

*Attributes:

• room_id (VARCHAR2(128) / or NUMBER) – required, unique identifier (PK)
• room_code (VARCHAR2(128)) – required; should be unique (recommended UNIQUE constraint)
• capacity (NUMBER or NUMBER(9,2)) – required; value interval: capacity > 0
• type (VARCHAR2(128)) – required; suggested domain: {classroom, office, meeting_room, lab, ...}

Equipment

*Description: Represents an item of equipment that may exist in rooms (e.g., projector, whiteboard, microphone). *Candidate keys:

• equipment_id (selected as primary key)
• name (candidate only if guaranteed unique, usually not guaranteed)

*Attributes:

• equipment_id (VARCHAR2(128) / or NUMBER) – required, unique identifier (PK)
• name (VARCHAR2(128)) – required

RoomEquipment

*Description: Associative entity that models the many-to-many relationship between Rooms and Equipment. It stores how many units of a specific equipment exist in a room. *Candidate keys:

• (room_id, equipment_id) (selected as primary key, natural composite key)

*Attributes:

• room_id (FK reference to Rooms) – required
• equipment_id (FK reference to Equipment) – required
• quantity (NUMBER) – required; value interval: quantity >= 0 (usually > 0 if present)

Users

*Description: Represents system users who can create reservations and (if authorized) approve them. *Candidate keys:

• user_id (selected as primary key)
• username (candidate key, should be unique)
• email (candidate key, should be unique)

*Attributes:

• user_id (VARCHAR2(128) / or NUMBER) – required, unique identifier (PK)
• username (VARCHAR2(128)) – required; should be unique
• email (VARCHAR2(128)) – required; should be unique; format constraint (contains '@')
• full_name (VARCHAR2(128)) – required
• role (VARCHAR2(128)) – required; suggested domain: {regular, admin/approver}

Reservations

*Description: Represents a reservation request made by a user for a specific room, date and time interval. The system must prevent overlapping reservations for the same room and time. *Candidate keys:

• reservation_id (selected as primary key)
• (room_id, reservation_date, start_time, end_time) may form a natural candidate key, but usually we still keep a surrogate PK

*Attributes:

• reservation_id (VARCHAR2(128) / or NUMBER) – required, unique identifier (PK)
• reservation_date (DATE) – required
• start_time (DATE or TIME representation) – required
• end_time (DATE or TIME representation) – required; constraint: end_time > start_time
• status (VARCHAR2(128)) – required; suggested domain: {pending, approved, rejected, cancelled}

Approvals

*Description: Represents an approval decision for a reservation, created by an authorized user (approver/admin). Each approval stores decision, decision time and an optional note. *Candidate keys:

• approval_id (selected as primary key)
• reservation_id could be unique because one reservation has at most one approval (depending on business rule)

*Attributes:

• approval_id (VARCHAR2(128) / or NUMBER) – required, unique identifier (PK)
• decision (VARCHAR2(128)) – required; suggested domain: {approved, rejected}
• decision_time (DATE) – required
• note (VARCHAR2(128)) – optional

Relationships

has (Buildings–Rooms)

*Meaning: A building contains rooms. *Cardinality: Buildings (1) — Rooms (N) *Participation:

• Room participation is total (each room must belong to exactly one building).
• Building participation is partial (a building may have zero or more rooms).

contains (Rooms–RoomEquipment)

*Meaning: A room contains equipment entries (with quantities). *Cardinality: Rooms (1) — RoomEquipment (N) *Participation:

• RoomEquipment participation is total (each RoomEquipment must belong to exactly one room).
• Room participation is partial (a room may have no equipment registered).

used_in (Equipment–RoomEquipment)

*Meaning: Equipment item appears in room-equipment entries. *Cardinality: Equipment (1) — RoomEquipment (N) *Participation:

• RoomEquipment participation is total (each entry refers to exactly one equipment item).
• Equipment participation is partial (an equipment type may be defined even if not currently used in any room).

is_reserved_for (Rooms–Reservations)

*Meaning: Each reservation is for exactly one room. *Cardinality: Rooms (1) — Reservations (N) *Participation:

• Reservation participation is total (each reservation must specify a room).
• Room participation is partial (a room may have no reservations).

makes (Users–Reservations)

*Meaning: Users create reservations. *Cardinality: Users (1) — Reservations (N) *Participation:

• Reservation participation is total (each reservation must be created by one user).
• User participation is partial (a user may have zero reservations).

has_approval (Reservations–Approvals)

*Meaning: A reservation may have an approval decision. *Cardinality: Reservations (1) — Approvals (1) with optional on reservation side *Participation:

• Approval participation is total (approval cannot exist without reservation).
• Reservation participation is partial (reservation may still be pending with no approval record).

approves (Users–Approvals)

*Meaning: Authorized users approve or reject reservations. *Cardinality: Users (1) — Approvals (N) *Participation:

• Approval participation is total (each approval is made by exactly one user).
• User participation is partial (not every user must approve reservations).

Entity-Relationship Model History

• v01 – Initial version of the ER model. Includes: Buildings, Rooms, Equipment, RoomEquipment (associative entity), Users, Reservations and Approvals, along with all required relationships and cardinalities.