Changes between Version 6 and Version 7 of RelationalDesign
- Timestamp:
- 06/25/26 10:52:42 (2 weeks ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
RelationalDesign
v6 v7 8 8 9 9 In the corrected ER model v.03, ''has_equipment'' is a many-to-many relationship between Rooms and Equipment with the relationship attribute ''quantity''. Therefore, it is transformed into the relation ''room_equipment''. Similarly, ''requests_equipment'' is a many-to-many relationship between Reservations and Equipment with the relationship attribute ''requested_quantity'', so it is transformed into the relation ''reservation_equipment''. 10 11 The optional relationship ''includes_room'' between Rooms and Reservations is represented by the nullable foreign key ''room_id'' in the ''reservations'' relation, because a reservation may be equipment-only and may not include a room. The relationship ''makes'' between Users and Reservations is represented by the required foreign key ''user_id'' in ''reservations''. The relationship ''has_approval'' between Reservations and Approvals is represented by the required and unique foreign key ''reservation_id'' in ''approvals'', because one reservation can have at most one approval record. 10 12 11 13 === Relations === … … 49 51 50 52 * room_id is the primary key. 51 * building_id is required .53 * building_id is required because each room must belong to exactly one building. 52 54 * room_code is required and unique. 53 55 * capacity must be greater than 0. … … 77 79 ) 78 80 81 This relation is created from the M:N relationship ''has_equipment'' between Rooms and Equipment in the corrected ER model v.03. The relationship attribute ''quantity'' becomes an attribute of this relation. 82 79 83 Candidate keys: 80 84 … … 89 93 90 94 * (room_id, equipment_id) is the composite primary key. 95 * room_id is required. 96 * equipment_id is required. 91 97 * quantity is required and must be greater than 0. 92 93 This relation is created from the M:N relationship has_equipment between Rooms and Equipment. The relationship attribute quantity becomes an attribute of this relation.94 98 95 99 '''users'''( … … 129 133 130 134 * reservation_id 131 * (room_id, reservation_date, start_time, end_time) for room-based reservations135 * (room_id, reservation_date, start_time, end_time) for exact room-based reservation intervals 132 136 133 137 Foreign keys: … … 140 144 * reservation_id is the primary key. 141 145 * room_id is optional because equipment-only reservations are allowed. 142 * user_id is required .146 * user_id is required because each reservation must be created by exactly one user. 143 147 * reservation_date is required. 144 148 * start_time is required. … … 147 151 * (room_id, reservation_date, start_time, end_time) is unique for exact room reservation intervals. 148 152 153 The uniqueness constraint on (room_id, reservation_date, start_time, end_time) prevents duplicate reservations for the exact same room and time interval. General overlapping intervals are treated as a business rule and are checked by the application/prototype logic. A stricter database-level implementation can be added later using a trigger or exclusion constraint if required. 154 149 155 '''reservation_equipment'''( 150 156 '''reservation_id''' PK, FK, … … 153 159 ) 154 160 161 This relation is created from the M:N relationship ''requests_equipment'' between Reservations and Equipment in the corrected ER model v.03. The relationship attribute ''requested_quantity'' becomes an attribute of this relation. 162 155 163 Candidate keys: 156 164 … … 165 173 166 174 * (reservation_id, equipment_id) is the composite primary key. 175 * reservation_id is required. 176 * equipment_id is required. 167 177 * requested_quantity is required and must be greater than 0. 168 178 … … 190 200 * approval_id is the primary key. 191 201 * reservation_id is required and unique, because one reservation can have at most one approval record. 192 * approver_id is required .202 * approver_id is required because each approval decision must be created by exactly one authorized user. 193 203 * decision is restricted to the values: approved, rejected. 194 204 * decision_time is required.
