Changes between Version 9 and Version 10 of ERModel


Ignore:
Timestamp:
06/18/26 18:07:55 (31 hours ago)
Author:
223091
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • ERModel

    v9 v10  
    1 = Entity-Relationship Model v.01 =
     1= Entity-Relationship Model v.02 =
    22
    33== Diagram ==
    44
    5 [[Image(ERModel_v01.png, width=100%)]]
     5[[Image(ERModel_v02.png, width=100%)]]
    66
    77A full-page width image of the latest version of the entity-relationship diagram is shown above.
    88
    9 (Attached files: ''ERModel_v01.xml'' and ''ERModel_v01.png'')
     9(Attached files: ''ERModel_v01.xml'', ''ERModel_v01.png'', ''ERModel_v02.xml'' and ''ERModel_v02.png'')
    1010
    1111== Data requirements ==
    … …  
    3232==== Rooms ====
    3333
    34 '''Description:''' Rooms represents reservable rooms such as classrooms, offices, meeting rooms, or laboratories. Each room is located in exactly one building and can be reserved by users for specific dates and time intervals.
     34'''Description:''' Rooms represents reservable rooms such as classrooms, offices, meeting rooms, or laboratories. Each room is located in exactly one building. A room may be included in reservations for specific dates and time intervals, but reservations may also exist without a room when they are only equipment requests.
    3535
    3636'''Candidate keys:'''
    … …  
    4848==== Equipment ====
    4949
    50 '''Description:''' Equipment represents types of equipment that may be available in rooms, such as projectors, whiteboards, microphones, or computers. This entity is needed so that equipment can be managed independently and assigned to different rooms.
     50'''Description:''' Equipment represents types of equipment that may be available in rooms or in general stock, such as projectors, whiteboards, microphones, computers, cables, or other reservable equipment. This entity is needed so that equipment can be managed independently, assigned to specific rooms, or requested separately as part of reservations.
    5151
    5252'''Candidate keys:'''
    … …  
    5959* ''equipment_id'' (INTEGER) – required, unique identifier, primary key.
    6060* ''name'' (VARCHAR(128)) – required, name of the equipment type.
     61* ''stock_quantity'' (INTEGER) – required, number of equipment items available in general stock, must be greater than or equal to 0.
    6162
    6263==== !RoomEquipment ====
    6364
    64 '''Description:''' !RoomEquipment is an associative entity that models the many-to-many relationship between Rooms and Equipment. It stores information about which equipment type is available in which room and in what quantity.
     65'''Description:''' !RoomEquipment is an associative entity that models the many-to-many relationship between Rooms and Equipment. It stores information about which equipment type is available in which room and in what quantity. This entity is used only for equipment assigned to rooms, while general equipment stock is represented by the stock_quantity attribute in Equipment.
    6566
    6667'''Candidate keys:'''
    … …  
    7273* ''room_id'' (FK reference to Rooms) – required, part of the composite primary key.
    7374* ''equipment_id'' (FK reference to Equipment) – required, part of the composite primary key.
    74 * ''quantity'' (INTEGER) – required, must be greater than or equal to 0; normally greater than 0 when an equipment entry exists.
     75* ''quantity'' (INTEGER) – required, must be greater than 0.
    7576
    7677==== Users ====
    7778
    78 '''Description:''' Users represents people who have access to the reservation system. Users can create reservations, and users with appropriate roles can approve or reject reservation requests.
     79'''Description:''' Users represents people who have access to the reservation system. Users can create reservation requests, and users with appropriate roles can approve or reject reservations.
    7980
    8081'''Candidate keys:'''
    … …  
    9495==== Reservations ====
    9596
    96 '''Description:''' Reservations represents reservation requests made by users for specific rooms, dates, and time intervals. This entity is central to the system because it stores when and by whom a room is reserved. The system must prevent overlapping reservations for the same room and time interval.
     97'''Description:''' Reservations represents reservation requests made by users for specific dates and time intervals. A reservation may include a room, requested equipment, or both. This entity is central to the system because it stores when and by whom a resource is reserved or requested. The system must prevent overlapping room reservations for the same room and time interval.
    9798
    9899'''Candidate keys:'''
    99100
    100101* reservation_id – selected as the primary key because it is stable and unique.
    101 * (room_id, reservation_date, start_time, end_time) – may form a natural candidate key under strict business constraints, but a surrogate primary key is preferred for easier referencing.
     102* (room_id, reservation_date, start_time, end_time) – may form a natural candidate key for room-based reservations under strict business constraints, but a surrogate primary key is preferred for easier referencing.
    102103
    103104'''Attributes:'''
    … …  
    105106* ''reservation_id'' (INTEGER) – required, unique identifier, primary key.
    106107* ''reservation_date'' (DATE) – required, date of the reservation.
    107 * ''start_time'' (DATE or TIME representation) – required, start time of the reservation.
    108 * ''end_time'' (DATE or TIME representation) – required, must be greater than start_time.
     108* ''start_time'' (TIME) – required, start time of the reservation.
     109* ''end_time'' (TIME) – required, must be greater than start_time.
    109110* ''status'' (VARCHAR(128)) – required, suggested domain: {pending, approved, rejected, cancelled}.
     111
     112==== !ReservationEquipment ====
     113
     114'''Description:''' !ReservationEquipment is an associative entity that models equipment requested as part of a reservation. It allows the system to support reservations that include only equipment, only a room, or both a room and equipment.
     115
     116'''Candidate keys:'''
     117
     118* (reservation_id, equipment_id) – selected as the primary key because the same equipment type should appear only once in the same reservation request.
     119
     120'''Attributes:'''
     121
     122* ''reservation_id'' (FK reference to Reservations) – required, part of the composite primary key.
     123* ''equipment_id'' (FK reference to Equipment) – required, part of the composite primary key.
     124* ''requested_quantity'' (INTEGER) – required, quantity of equipment requested, must be greater than 0.
    110125
    111126==== Approvals ====
    … …  
    122137* ''approval_id'' (INTEGER) – required, unique identifier, primary key.
    123138* ''decision'' (VARCHAR(128)) – required, suggested domain: {approved, rejected}.
    124 * ''decision_time'' (DATE) – required, date and time when the decision was made.
     139* ''decision_time'' (TIMESTAMP) – required, date and time when the decision was made.
    125140* ''note'' (VARCHAR(128)) – optional, additional explanation for the decision.
    126141
    … …  
    160175* Equipment participation is partial because an equipment type may exist without being assigned to any room.
    161176
    162 ==== is_reserved_for (Rooms–Reservations) ====
    163 
    164 '''Meaning:''' The relationship is_reserved_for connects Rooms and Reservations. It shows that each reservation is made for one specific room.
     177==== includes_room (Rooms–Reservations) ====
     178
     179'''Meaning:''' The relationship includes_room connects Rooms and Reservations. It shows that a reservation may include one room. A reservation can also exist without a room when it is only an equipment request.
    165180
    166181'''Cardinality:''' Rooms (1) — Reservations (N)
    … …  
    168183'''Participation:'''
    169184
    170 * Reservation participation is total because each reservation must specify exactly one room.
    171185* Room participation is partial because a room may have zero reservations.
     186* Reservation participation is partial because a reservation may be only for equipment and may not include a room.
    172187
    173188==== makes (Users–Reservations) ====
    … …  
    182197* User participation is partial because a user may create zero reservations.
    183198
     199==== requests_equipment (Reservations–!ReservationEquipment) ====
     200
     201'''Meaning:''' The relationship requests_equipment connects Reservations and !ReservationEquipment. It shows that a reservation may include one or more requested equipment items.
     202
     203'''Cardinality:''' Reservations (1) — !ReservationEquipment (N)
     204
     205'''Participation:'''
     206
     207* !ReservationEquipment participation is total because each equipment request entry must refer to exactly one reservation.
     208* Reservation participation is partial because a reservation may include only a room and no requested equipment.
     209
     210==== is_requested_in (Equipment–!ReservationEquipment) ====
     211
     212'''Meaning:''' The relationship is_requested_in connects Equipment and !ReservationEquipment. It shows that an equipment type may be requested in reservation-equipment entries.
     213
     214'''Cardinality:''' Equipment (1) — !ReservationEquipment (N)
     215
     216'''Participation:'''
     217
     218* !ReservationEquipment participation is total because each equipment request entry must refer to exactly one equipment type.
     219* Equipment participation is partial because an equipment type may exist without being requested in any reservation.
     220
    184221==== has_approval (Reservations–Approvals) ====
    185222
    … …  
    208245* Each room belongs to exactly one building.
    209246* A reservation is created by exactly one user.
    210 * A reservation is made for exactly one room.
    211 * A reservation cannot overlap with another reservation for the same room and time interval.
     247* A reservation may be made for a room, for equipment, or for both.
     248* A reservation must include at least one requested resource: either a room or equipment.
     249* Equipment may be assigned to a specific room, but it may also exist only in general stock.
     250* !RoomEquipment represents equipment available in a specific room.
     251* !ReservationEquipment represents equipment requested as part of a reservation.
     252* A reservation cannot overlap with another room reservation for the same room and time interval.
    212253* A reservation may exist without an approval record while it is still pending.
    213 * Equipment types may exist without being assigned to any room.
    214 * The !RoomEquipment entity is used to model the many-to-many relationship between Rooms and Equipment.
     254* Equipment types may exist without being assigned to any room or requested in any reservation.
    215255* Only users with appropriate roles, such as admin or approver, are allowed to create approval records.
    216256
    … …  
    218258
    219259* '''v01''' – Initial version of the ER model. This version includes Buildings, Rooms, Equipment, !RoomEquipment as an associative entity, Users, Reservations, and Approvals. It also defines the required relationships, cardinalities, participation constraints, candidate keys, primary keys, and main entity attributes.
     260
     261* '''v02''' – Updated version of the ER model based on supervisor feedback. This version generalizes reservations so that a reservation may include a room, equipment, or both. A new associative entity, !ReservationEquipment, was added to represent equipment requested as part of a reservation. The Equipment entity was extended with stock_quantity to represent equipment available in general stock and not necessarily assigned to a specific room. The Rooms–Reservations relationship was changed into includes_room so that equipment-only reservations are allowed.