Changes between Version 1 and Version 2 of ERModel


Ignore:
Timestamp:
02/11/26 10:30:45 (13 days ago)
Author:
223091
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • ERModel

    v1 v2  
    1 = Entity-Relationship Model v.1 =
     1= Entity-Relationship Model v01 =
    22
    3 '''Diagram'''
     3== Diagram ==
    44
    5 [[Image(ERModel_v1.png)]]
     5[[Image(ERModel_v01.jpg, width=100%)]]
    66
    7 '''Data requirements'''
     7(Attached files: ''ERModel_v01.xml'' and ''ERModel_v01.jpg'')
     8
     9== Data requirements ==
    810
    911=== Entities ===
    1012
    11 ==== Users ====
    12 Represents the users of the system who can make room reservations or manage the system.
    13 
    14  * '''Candidate keys''': username, email 
    15  * '''Primary key''': user_id
    16 
    17  * '''Attributes''':
    18   * user_id – integer, required
    19   * username – text, required, unique
    20   * email – text, required, unique
    21   * role – text, required (user/admin)
     13==== Buildings ====
     14*'''Description:''' Represents a physical building that contains multiple rooms that can be reserved.
     15*'''Candidate keys:'''
     16 * building_id (selected as primary key because it is stable and unique)
     17 * (optional candidate) (name, address) if the institution guarantees uniqueness, but it is not reliable -> not selected
     18*'''Attributes:'''
     19 * ''building_id'' (NUMBER(9,2) / or NUMBER) – required, unique identifier (PK)
     20 * ''name'' (VARCHAR2(128)) – required
     21 * ''address'' (VARCHAR2(128)) – required
    2222
    2323==== Rooms ====
    24 Represents rooms that can be reserved in the system.
     24*'''Description:''' Represents a reservable room (classroom/office/meeting room) located in a building.
     25*'''Candidate keys:'''
     26 * room_id (selected as primary key because it is stable and unique)
     27 * room_code (candidate key if it is unique across the organization)
     28*'''Attributes:'''
     29 * ''room_id'' (VARCHAR2(128) / or NUMBER) – required, unique identifier (PK)
     30 * ''room_code'' (VARCHAR2(128)) – required; should be unique (recommended UNIQUE constraint)
     31 * ''capacity'' (NUMBER or NUMBER(9,2)) – required; value interval: capacity > 0
     32 * ''type'' (VARCHAR2(128)) – required; suggested domain: {classroom, office, meeting_room, lab, ...}
    2533
    26  * '''Candidate keys''': room_number 
    27  * '''Primary key''': room_id
     34==== Equipment ====
     35*'''Description:''' Represents an item of equipment that may exist in rooms (e.g., projector, whiteboard, microphone).
     36*'''Candidate keys:'''
     37 * equipment_id (selected as primary key)
     38 * name (candidate only if guaranteed unique, usually not guaranteed)
     39*'''Attributes:'''
     40 * ''equipment_id'' (VARCHAR2(128) / or NUMBER) – required, unique identifier (PK)
     41 * ''name'' (VARCHAR2(128)) – required
    2842
    29  * '''Attributes''':
    30   * room_id – integer, required
    31   * room_number – text, required, unique
    32   * capacity – integer, required
    33   * type – text, required (classroom, office, meeting room)
     43==== RoomEquipment ====
     44*'''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.
     45*'''Candidate keys:'''
     46 * (room_id, equipment_id) (selected as primary key, natural composite key)
     47*'''Attributes:'''
     48 * ''room_id'' (FK reference to Rooms) – required
     49 * ''equipment_id'' (FK reference to Equipment) – required
     50 * ''quantity'' (NUMBER) – required; value interval: quantity >= 0 (usually > 0 if present)
     51
     52==== Users ====
     53*'''Description:''' Represents system users who can create reservations and (if authorized) approve them.
     54*'''Candidate keys:'''
     55 * user_id (selected as primary key)
     56 * username (candidate key, should be unique)
     57 * email (candidate key, should be unique)
     58*'''Attributes:'''
     59 * ''user_id'' (VARCHAR2(128) / or NUMBER) – required, unique identifier (PK)
     60 * ''username'' (VARCHAR2(128)) – required; should be unique
     61 * ''email'' (VARCHAR2(128)) – required; should be unique; format constraint (contains '@')
     62 * ''full_name'' (VARCHAR2(128)) – required
     63 * ''role'' (VARCHAR2(128)) – required; suggested domain: {regular, admin/approver}
    3464
    3565==== Reservations ====
    36 Represents reservations made by users for specific rooms and time intervals.
     66*'''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.
     67*'''Candidate keys:'''
     68 * reservation_id (selected as primary key)
     69 * (room_id, reservation_date, start_time, end_time) may form a natural candidate key, but usually we still keep a surrogate PK
     70*'''Attributes:'''
     71 * ''reservation_id'' (VARCHAR2(128) / or NUMBER) – required, unique identifier (PK)
     72 * ''reservation_date'' (DATE) – required
     73 * ''start_time'' (DATE or TIME representation) – required
     74 * ''end_time'' (DATE or TIME representation) – required; constraint: end_time > start_time
     75 * ''status'' (VARCHAR2(128)) – required; suggested domain: {pending, approved, rejected, cancelled}
    3776
    38  * '''Candidate keys''': (room_id, start_time, end_time) 
    39  * '''Primary key''': reservation_id
    40 
    41  * '''Attributes''':
    42   * reservation_id – integer, required
    43   * start_time – datetime, required
    44   * end_time – datetime, required
    45   * user_id – integer, required
    46   * room_id – integer, required
     77==== Approvals ====
     78*'''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.
     79*'''Candidate keys:'''
     80 * approval_id (selected as primary key)
     81 * reservation_id could be unique because one reservation has at most one approval (depending on business rule)
     82*'''Attributes:'''
     83 * ''approval_id'' (VARCHAR2(128) / or NUMBER) – required, unique identifier (PK)
     84 * ''decision'' (VARCHAR2(128)) – required; suggested domain: {approved, rejected}
     85 * ''decision_time'' (DATE) – required
     86 * ''note'' (VARCHAR2(128)) – optional
    4787
    4888=== Relationships ===
    4989
    50  * '''Users – Reservations''': One user can create multiple reservations, but each reservation is created by exactly one user.
    51  * '''Rooms – Reservations''': One room can be reserved multiple times, but each reservation refers to exactly one room.
     90==== has (Buildings–Rooms) ====
     91*'''Meaning:''' A building contains rooms.
     92*'''Cardinality:''' Buildings (1) — Rooms (N)
     93*'''Participation:'''
     94 * Room participation is total (each room must belong to exactly one building).
     95 * Building participation is partial (a building may have zero or more rooms).
     96
     97==== contains (Rooms–RoomEquipment) ====
     98*'''Meaning:''' A room contains equipment entries (with quantities).
     99*'''Cardinality:''' Rooms (1) — RoomEquipment (N)
     100*'''Participation:'''
     101 * RoomEquipment participation is total (each RoomEquipment must belong to exactly one room).
     102 * Room participation is partial (a room may have no equipment registered).
     103
     104==== used_in (Equipment–RoomEquipment) ====
     105*'''Meaning:''' Equipment item appears in room-equipment entries.
     106*'''Cardinality:''' Equipment (1) — RoomEquipment (N)
     107*'''Participation:'''
     108 * RoomEquipment participation is total (each entry refers to exactly one equipment item).
     109 * Equipment participation is partial (an equipment type may be defined even if not currently used in any room).
     110
     111==== is_reserved_for (Rooms–Reservations) ====
     112*'''Meaning:''' Each reservation is for exactly one room.
     113*'''Cardinality:''' Rooms (1) — Reservations (N)
     114*'''Participation:'''
     115 * Reservation participation is total (each reservation must specify a room).
     116 * Room participation is partial (a room may have no reservations).
     117
     118==== makes (Users–Reservations) ====
     119*'''Meaning:''' Users create reservations.
     120*'''Cardinality:''' Users (1) — Reservations (N)
     121*'''Participation:'''
     122 * Reservation participation is total (each reservation must be created by one user).
     123 * User participation is partial (a user may have zero reservations).
     124
     125==== has_approval (Reservations–Approvals) ====
     126*'''Meaning:''' A reservation may have an approval decision.
     127*'''Cardinality:''' Reservations (1) — Approvals (1) with optional on reservation side
     128*'''Participation:'''
     129 * Approval participation is total (approval cannot exist without reservation).
     130 * Reservation participation is partial (reservation may still be pending with no approval record).
     131
     132==== approves (Users–Approvals) ====
     133*'''Meaning:''' Authorized users approve or reject reservations.
     134*'''Cardinality:''' Users (1) — Approvals (N)
     135*'''Participation:'''
     136 * Approval participation is total (each approval is made by exactly one user).
     137 * User participation is partial (not every user must approve reservations).
    52138
    53139== Entity-Relationship Model History ==
    54140
    55  * '''v1''' – Initial ER model created according to the project requirements.
     141* '''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.