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 reservable rooms. *Candidate keys:

• building_id (selected as primary key because it is stable and guaranteed to be unique)
• (optional candidate) (name, address) if institutional rules guarantee uniqueness; however, this is not considered sufficiently reliable and therefore not selected

*Attributes:

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

Rooms

*Description: Represents a reservable room (e.g., classroom, office, meeting room, lab) located in exactly one building. *Candidate keys:

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

*Attributes:

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

Equipment

*Description: Represents a type 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; typically 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 the quantity of a specific equipment type available 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; constraint: quantity ≥ 0 (typically > 0 when present)

Users

*Description: Represents system users who can create reservations and, if authorized, approve or reject them. *Candidate keys:

• user_id (selected as primary key)
• username (candidate key; must be unique)
• email (candidate key; must be unique)

*Attributes:

• user_id (VARCHAR2(128) or NUMBER) – required, unique identifier (PK)
• username (VARCHAR2(128)) – required; UNIQUE constraint recommended
• email (VARCHAR2(128)) – required; UNIQUE constraint recommended; must follow standard email format
• 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 interval. *Candidate keys:

• reservation_id (selected as primary key)
• (room_id, reservation_date, start_time, end_time) may form a natural candidate key under strict business constraints; however, a surrogate primary key is preferred

*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 (admin/approver). Each approval records the decision, decision time, and an optional note. *Candidate keys:

• approval_id (selected as primary key)
• reservation_id may be unique if the business rule enforces at most one approval per reservation

*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 contain zero or more rooms).

contains (Rooms–RoomEquipment)

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

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

used_in (Equipment–RoomEquipment)

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

• RoomEquipment participation is total (each entry must refer to exactly one equipment type).
• Equipment participation is partial (an equipment type may exist without being assigned to any room).

is_reserved_for (Rooms–Reservations)

*Meaning: Each reservation is made 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 zero reservations).

makes (Users–Reservations)

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

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

has_approval (Reservations–Approvals)

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

• Approval participation is total (an approval cannot exist without a reservation).
• Reservation participation is partial (a reservation may exist without an approval record, e.g., pending state).

approves (Users–Approvals)

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

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

Design Assumptions

• Each room belongs to exactly one building.
• A reservation cannot overlap with another reservation for the same room and time interval.
• A reservation may exist without an approval (status = pending).
• Equipment types may exist without being assigned to any room.
• Only users with appropriate roles (admin/approver) are allowed to create approval records.

Entity-Relationship Model History

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