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