= Entity-Relationship Model v01 =

== Diagram ==

[[Image(ERModel_v01.jpg, width=100%)]]

(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.