Changes between Version 2 and Version 3 of ERModel


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

--

Legend:

Unmodified
Added
Removed
Modified

  • ERModel

    v2 v3  
    1212
    1313==== Buildings ====
    14 *'''Description:''' Represents a physical building that contains multiple rooms that can be reserved.
     14*'''Description:''' Represents a physical building that contains multiple reservable rooms.
    1515*'''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
     16 * building_id (selected as primary key because it is stable and guaranteed to be unique)
     17 * (optional candidate) (name, address) if institutional rules guarantee uniqueness; however, this is not considered sufficiently reliable and therefore not selected
    1818*'''Attributes:'''
    19  * ''building_id'' (NUMBER(9,2) / or NUMBER) – required, unique identifier (PK)
     19 * ''building_id'' (NUMBER or NUMBER(9,2)) – required, unique identifier (PK)
    2020 * ''name'' (VARCHAR2(128)) – required
    2121 * ''address'' (VARCHAR2(128)) – required
    2222
    2323==== Rooms ====
    24 *'''Description:''' Represents a reservable room (classroom/office/meeting room) located in a building.
     24*'''Description:''' Represents a reservable room (e.g., classroom, office, meeting room, lab) located in exactly one building.
    2525*'''Candidate keys:'''
    2626 * room_id (selected as primary key because it is stable and unique)
    27  * room_code (candidate key if it is unique across the organization)
     27 * room_code (candidate key if guaranteed to be unique across the organization)
    2828*'''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
     29 * ''room_id'' (VARCHAR2(128) or NUMBER) – required, unique identifier (PK)
     30 * ''room_code'' (VARCHAR2(128)) – required; recommended UNIQUE constraint
     31 * ''capacity'' (NUMBER) – required; constraint: capacity > 0
    3232 * ''type'' (VARCHAR2(128)) – required; suggested domain: {classroom, office, meeting_room, lab, ...}
    3333
    3434==== Equipment ====
    35 *'''Description:''' Represents an item of equipment that may exist in rooms (e.g., projector, whiteboard, microphone).
     35*'''Description:''' Represents a type of equipment that may exist in rooms (e.g., projector, whiteboard, microphone).
    3636*'''Candidate keys:'''
    3737 * equipment_id (selected as primary key)
    38  * name (candidate only if guaranteed unique, usually not guaranteed)
     38 * name (candidate only if guaranteed unique; typically not guaranteed)
    3939*'''Attributes:'''
    40  * ''equipment_id'' (VARCHAR2(128) / or NUMBER) – required, unique identifier (PK)
     40 * ''equipment_id'' (VARCHAR2(128) or NUMBER) – required, unique identifier (PK)
    4141 * ''name'' (VARCHAR2(128)) – required
    4242
    4343==== 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.
     44*'''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.
    4545*'''Candidate keys:'''
    46  * (room_id, equipment_id) (selected as primary key, natural composite key)
     46 * (room_id, equipment_id) – selected as primary key (natural composite key)
    4747*'''Attributes:'''
    4848 * ''room_id'' (FK reference to Rooms) – required
    4949 * ''equipment_id'' (FK reference to Equipment) – required
    50  * ''quantity'' (NUMBER) – required; value interval: quantity >= 0 (usually > 0 if present)
     50 * ''quantity'' (NUMBER) – required; constraint: quantity ≥ 0 (typically > 0 when present)
    5151
    5252==== Users ====
    53 *'''Description:''' Represents system users who can create reservations and (if authorized) approve them.
     53*'''Description:''' Represents system users who can create reservations and, if authorized, approve or reject them.
    5454*'''Candidate keys:'''
    5555 * user_id (selected as primary key)
    56  * username (candidate key, should be unique)
    57  * email (candidate key, should be unique)
     56 * username (candidate key; must be unique)
     57 * email (candidate key; must be unique)
    5858*'''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 '@')
     59 * ''user_id'' (VARCHAR2(128) or NUMBER) – required, unique identifier (PK)
     60 * ''username'' (VARCHAR2(128)) – required; UNIQUE constraint recommended
     61 * ''email'' (VARCHAR2(128)) – required; UNIQUE constraint recommended; must follow standard email format
    6262 * ''full_name'' (VARCHAR2(128)) – required
    63  * ''role'' (VARCHAR2(128)) – required; suggested domain: {regular, admin/approver}
     63 * ''role'' (VARCHAR2(128)) – required; suggested domain: {regular, admin, approver}
    6464
    6565==== Reservations ====
    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.
     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 interval.
    6767*'''Candidate keys:'''
    6868 * 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
     69 * (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
    7070*'''Attributes:'''
    71  * ''reservation_id'' (VARCHAR2(128) / or NUMBER) – required, unique identifier (PK)
     71 * ''reservation_id'' (VARCHAR2(128) or NUMBER) – required, unique identifier (PK)
    7272 * ''reservation_date'' (DATE) – required
    7373 * ''start_time'' (DATE or TIME representation) – required
    … …  
    7676
    7777==== 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.
     78*'''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.
    7979*'''Candidate keys:'''
    8080 * approval_id (selected as primary key)
    81  * reservation_id could be unique because one reservation has at most one approval (depending on business rule)
     81 * reservation_id may be unique if the business rule enforces at most one approval per reservation
    8282*'''Attributes:'''
    83  * ''approval_id'' (VARCHAR2(128) / or NUMBER) – required, unique identifier (PK)
     83 * ''approval_id'' (VARCHAR2(128) or NUMBER) – required, unique identifier (PK)
    8484 * ''decision'' (VARCHAR2(128)) – required; suggested domain: {approved, rejected}
    8585 * ''decision_time'' (DATE) – required
    … …  
    9393*'''Participation:'''
    9494 * 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).
     95 * Building participation is partial (a building may contain zero or more rooms).
    9696
    9797==== contains (Rooms–RoomEquipment) ====
    98 *'''Meaning:''' A room contains equipment entries (with quantities).
     98*'''Meaning:''' A room contains equipment entries with associated quantities.
    9999*'''Cardinality:''' Rooms (1) — RoomEquipment (N)
    100100*'''Participation:'''
    101  * RoomEquipment participation is total (each RoomEquipment must belong to exactly one room).
     101 * RoomEquipment participation is total (each entry must refer to exactly one room).
    102102 * Room participation is partial (a room may have no equipment registered).
    103103
    104104==== used_in (Equipment–RoomEquipment) ====
    105 *'''Meaning:''' Equipment item appears in room-equipment entries.
     105*'''Meaning:''' Equipment types appear in room-equipment entries.
    106106*'''Cardinality:''' Equipment (1) — RoomEquipment (N)
    107107*'''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).
     108 * RoomEquipment participation is total (each entry must refer to exactly one equipment type).
     109 * Equipment participation is partial (an equipment type may exist without being assigned to any room).
    110110
    111111==== is_reserved_for (Rooms–Reservations) ====
    112 *'''Meaning:''' Each reservation is for exactly one room.
     112*'''Meaning:''' Each reservation is made for exactly one room.
    113113*'''Cardinality:''' Rooms (1) — Reservations (N)
    114114*'''Participation:'''
    115115 * Reservation participation is total (each reservation must specify a room).
    116  * Room participation is partial (a room may have no reservations).
     116 * Room participation is partial (a room may have zero reservations).
    117117
    118118==== makes (Users–Reservations) ====
    … …  
    120120*'''Cardinality:''' Users (1) — Reservations (N)
    121121*'''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).
     122 * Reservation participation is total (each reservation must be created by exactly one user).
     123 * User participation is partial (a user may create zero reservations).
    124124
    125125==== has_approval (Reservations–Approvals) ====
    126 *'''Meaning:''' A reservation may have an approval decision.
    127 *'''Cardinality:''' Reservations (1) — Approvals (1) with optional on reservation side
     126*'''Meaning:''' A reservation may have an associated approval decision.
     127*'''Cardinality:''' Reservations (1) — Approvals (1), optional on the reservation side
    128128*'''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).
     129 * Approval participation is total (an approval cannot exist without a reservation).
     130 * Reservation participation is partial (a reservation may exist without an approval record, e.g., pending state).
    131131
    132132==== approves (Users–Approvals) ====
    … …  
    134134*'''Cardinality:''' Users (1) — Approvals (N)
    135135*'''Participation:'''
    136  * Approval participation is total (each approval is made by exactly one user).
     136 * Approval participation is total (each approval must be created by exactly one user).
    137137 * User participation is partial (not every user must approve reservations).
     138
     139== Design Assumptions ==
     140
     141* Each room belongs to exactly one building.
     142* A reservation cannot overlap with another reservation for the same room and time interval.
     143* A reservation may exist without an approval (status = pending).
     144* Equipment types may exist without being assigned to any room.
     145* Only users with appropriate roles (admin/approver) are allowed to create approval records.
    138146
    139147== Entity-Relationship Model History ==
    140148
    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.
     149* '''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.