| 13 | | This section lists all entity sets and relationships included in the entity-relationship model. For each entity set, the description, candidate keys, selected primary key, attributes, data types, and restrictions are documented. For each relationship, the meaning, cardinality, and participation constraints are described. |
| | 13 | This section lists all entity sets and relationships included in the entity-relationship model. For each entity set, the description, candidate keys, selected primary key, attributes, data types, and restrictions are documented. For each relationship, the meaning, cardinality, participation constraints, and possible relationship attributes are described. |
| 19 | | '''Description:''' Buildings represents physical buildings that contain multiple reservable rooms. This entity is needed because rooms are located in specific buildings, and the system must be able to group rooms by their physical location. |
| 20 | | |
| 21 | | '''Candidate keys:''' |
| 22 | | |
| 23 | | * building_id – selected as the primary key because it is stable and guaranteed to be unique. |
| 24 | | * (name, address) – possible candidate key only if institutional rules guarantee uniqueness, but it is not sufficiently reliable and therefore it is not selected as the primary key. |
| | 19 | '''Description:''' Buildings represents physical buildings that contain reservable rooms. This entity is needed because rooms are located in specific buildings, and the system must be able to group rooms by their physical location. |
| | 20 | |
| | 21 | '''Candidate keys:''' |
| | 22 | |
| | 23 | * ''building_id'' – selected as the primary key because it is stable and guaranteed to be unique. |
| | 24 | * ''(name, address)'' – possible candidate key only if institutional rules guarantee uniqueness, but it is not sufficiently reliable and therefore it is not selected as the primary key. |
| 63 | | ==== !RoomEquipment ==== |
| 64 | | |
| 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. |
| 66 | | |
| 67 | | '''Candidate keys:''' |
| 68 | | |
| 69 | | * (room_id, equipment_id) – selected as the primary key because the same equipment type should appear only once for the same room. |
| 70 | | |
| 71 | | '''Attributes:''' |
| 72 | | |
| 73 | | * ''room_id'' (FK reference to Rooms) – required, part of the composite primary key. |
| 74 | | * ''equipment_id'' (FK reference to Equipment) – required, part of the composite primary key. |
| 75 | | * ''quantity'' (INTEGER) – required, must be greater than 0. |
| 76 | | |
| 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. |
| 98 | | |
| 99 | | '''Candidate keys:''' |
| 100 | | |
| 101 | | * reservation_id – selected as the primary key because it is stable and unique. |
| 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. |
| | 83 | '''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 a resource is reserved or requested. The system must prevent overlapping room reservations for the same room and time interval. |
| | 84 | |
| | 85 | '''Candidate keys:''' |
| | 86 | |
| | 87 | * ''reservation_id'' – selected as the primary key because it is stable and unique. |
| | 88 | * A natural uniqueness rule may be based on the selected room, reservation date, start time, and end time for room-based reservations, but this rule depends on the relationship with Rooms and is therefore not selected as the primary key. |
| 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. |
| 125 | | |
| 155 | | ==== contains (Rooms–!RoomEquipment) ==== |
| 156 | | |
| 157 | | '''Meaning:''' The relationship contains connects Rooms and !RoomEquipment. It shows that a room may contain registered equipment entries with quantities. |
| 158 | | |
| 159 | | '''Cardinality:''' Rooms (1) — !RoomEquipment (N) |
| 160 | | |
| 161 | | '''Participation:''' |
| 162 | | |
| 163 | | * !RoomEquipment participation is total because each equipment entry must refer to exactly one room. |
| 164 | | * Room participation is partial because a room may have no equipment registered. |
| 165 | | |
| 166 | | ==== used_in (Equipment–!RoomEquipment) ==== |
| 167 | | |
| 168 | | '''Meaning:''' The relationship used_in connects Equipment and !RoomEquipment. It shows that an equipment type can be used in room-equipment entries. |
| 169 | | |
| 170 | | '''Cardinality:''' Equipment (1) — !RoomEquipment (N) |
| 171 | | |
| 172 | | '''Participation:''' |
| 173 | | |
| 174 | | * !RoomEquipment participation is total because each entry must refer to exactly one equipment type. |
| 175 | | * Equipment participation is partial because an equipment type may exist without being assigned to any room. |
| 176 | | |
| 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. |
| | 149 | ==== has_equipment (Rooms–Equipment) ==== |
| | 150 | |
| | 151 | '''Meaning:''' The relationship ''has_equipment'' connects Rooms and Equipment. It shows which equipment types are available in which rooms and in what quantity. |
| | 152 | |
| | 153 | '''Cardinality:''' Rooms (M) — Equipment (N) |
| | 154 | |
| | 155 | '''Participation:''' |
| | 156 | |
| | 157 | * Room participation is partial because a room may have no equipment assigned. |
| | 158 | * Equipment participation is partial because an equipment type may exist only in general stock and may not be assigned to any room. |
| | 159 | |
| | 160 | '''Relationship attributes:''' |
| | 161 | |
| | 162 | * ''quantity'' (INTEGER) – required for each relationship instance, must be greater than 0. |
| | 163 | |
| | 164 | ==== requests_equipment (Reservations–Equipment) ==== |
| | 165 | |
| | 166 | '''Meaning:''' The relationship ''requests_equipment'' connects Reservations and Equipment. It shows which equipment types are requested as part of a reservation and in what quantity. |
| | 167 | |
| | 168 | '''Cardinality:''' Reservations (M) — Equipment (N) |
| | 169 | |
| | 170 | '''Participation:''' |
| | 171 | |
| 259 | | * '''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. |
| | 218 | * '''v01''' – Initial version of the ER model. This version included Buildings, Rooms, Equipment, RoomEquipment as an associative entity, Users, Reservations, and Approvals. It also defined the required relationships, cardinalities, participation constraints, candidate keys, primary keys, and main entity attributes. |
| | 219 | |
| | 220 | * '''v02''' – Updated version of the ER model based on supervisor feedback. This version generalized 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. |
| | 221 | |
| | 222 | * '''v03''' – Corrected version of the ER model based on additional supervisor feedback. The associative entities RoomEquipment and ReservationEquipment were removed from the conceptual ER model. Instead, the many-to-many relationship between Rooms and Equipment is represented as ''has_equipment'' with the relationship attribute ''quantity'', and the many-to-many relationship between Reservations and Equipment is represented as ''requests_equipment'' with the relationship attribute ''requested_quantity''. This version avoids foreign keys in the ER model and keeps only strong entity sets and conceptual relationships. |
