Changes between Version 1 and Version 2 of AdvancedDatabaseDevelopment
- Timestamp:
- 07/03/26 13:45:29 (7 days ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
AdvancedDatabaseDevelopment
v1 v2 8 8 9 9 [attachment:advanced_database_development.sql advanced_database_development.sql] 10 11 The following screenshot shows the successful execution of the Phase P7 SQL script in DBeaver. 12 13 [[Image(p7_script_execution.png, width=100%)]] 10 14 11 15 == Data constraints requirements: Prevent overlapping active room reservations == … … 69 73 === Test example === 70 74 75 The following insert attempts to create an overlapping reservation for room_id 3 on 2026-03-01 from 09:30 to 10:30. 76 71 77 {{{ 72 78 INSERT INTO project.reservations ( … … 88 94 }}} 89 95 90 If there is already an active reservation for the same room and overlapping time interval, the trigger rejects the insert. 96 If there is already an active reservation for the same room and an overlapping time interval, the trigger rejects the insert. 97 98 The following screenshot shows that the trigger rejects an overlapping active room reservation. 99 100 [[Image(p7_room_overlap_trigger_error.png, width=100%)]] 101 102 === Discussion === 103 104 This rule is implemented at database level, so it protects the database even if a reservation is inserted manually through SQL and not only through the Java prototype application. It is more advanced than the Phase P2 unique constraint because it checks overlapping time intervals, not only identical intervals. 91 105 92 106 == Data constraints requirements: Reservation must contain at least one resource == … … 100 114 * both a room and equipment. 101 115 102 However, a reservation without both a room and requested equipment is invalid. This requirement cannot be enforced by a simple column-level CHECK constraint because therequested equipment is stored in a separate relation, ''project.reservation_equipment''.103 104 Therefore, a deferred constraint trigger is used. The trigger checks the rule at transaction commit time, after all related rows have been inserted.116 However, a reservation without both a room and requested equipment is invalid. This requirement cannot be enforced by a simple column-level CHECK constraint because requested equipment is stored in a separate relation, ''project.reservation_equipment''. 117 118 Therefore, deferred constraint triggers are used. The rule is checked at transaction commit time, after all related rows have been inserted or modified. 105 119 106 120 === Implementation === 107 121 108 The trigger function checks whether thereservation has either a room or at least one requested equipment item.122 The following trigger function checks whether a reservation has either a room or at least one requested equipment item. 109 123 110 124 {{{ … … 135 149 }}} 136 150 137 The trigger is created as a deferred constraint trigger .151 The trigger is created as a deferred constraint trigger on the ''project.reservations'' table. 138 152 139 153 {{{ … … 146 160 }}} 147 161 148 A second deferred trigger checks the same rule when requested equipment records are inserted, updated, or deleted. 162 A second deferred trigger checks the same rule when records in ''project.reservation_equipment'' are inserted, updated, or deleted. 163 164 {{{ 165 CREATE OR REPLACE FUNCTION project.fn_check_reservation_equipment_resource() 166 RETURNS TRIGGER AS $$ 167 DECLARE 168 v_reservation_id INTEGER; 169 v_room_id INTEGER; 170 v_has_equipment BOOLEAN; 171 BEGIN 172 v_reservation_id := COALESCE(NEW.reservation_id, OLD.reservation_id); 173 174 ``` 175 SELECT room_id 176 INTO v_room_id 177 FROM project.reservations 178 WHERE reservation_id = v_reservation_id; 179 180 IF NOT FOUND THEN 181 RETURN COALESCE(NEW, OLD); 182 END IF; 183 184 SELECT EXISTS ( 185 SELECT 1 186 FROM project.reservation_equipment re 187 WHERE re.reservation_id = v_reservation_id 188 ) 189 INTO v_has_equipment; 190 191 IF v_room_id IS NULL AND NOT v_has_equipment THEN 192 RAISE EXCEPTION 193 'Reservation % must include at least one resource: either a room or requested equipment.', 194 v_reservation_id; 195 END IF; 196 197 RETURN COALESCE(NEW, OLD); 198 ``` 199 200 END; 201 $$ LANGUAGE plpgsql; 202 }}} 149 203 150 204 {{{ … … 159 213 === Discussion === 160 214 161 This rule is important because the conceptual model allows flexible reservations, but it does not allow empty reservations. The use of a deferred constraint triggeris appropriate because equipment-only reservations require data in both ''project.reservations'' and ''project.reservation_equipment''.215 This rule is important because the conceptual model allows flexible reservations, but it does not allow empty reservations. The use of deferred constraint triggers is appropriate because equipment-only reservations require data in both ''project.reservations'' and ''project.reservation_equipment''. 162 216 163 217 == Data constraints requirements: Requested equipment stock availability == … … 178 232 === Implementation === 179 233 180 The function ''project.fn_validate_equipment_availability'' checks whether enough equipment is available .234 The function ''project.fn_validate_equipment_availability'' checks whether enough equipment is available for one reservation. 181 235 182 236 {{{ … … 238 292 }}} 239 293 294 The following trigger function executes the validation when requested equipment is inserted or updated. 295 296 {{{ 297 CREATE OR REPLACE FUNCTION project.fn_trg_validate_equipment_availability() 298 RETURNS TRIGGER AS $$ 299 BEGIN 300 PERFORM project.fn_validate_equipment_availability( 301 COALESCE(NEW.reservation_id, OLD.reservation_id) 302 ); 303 304 ``` 305 RETURN COALESCE(NEW, OLD); 306 ``` 307 308 END; 309 $$ LANGUAGE plpgsql; 310 }}} 311 240 312 The validation is executed when requested equipment is inserted or updated, and also when reservation date, time, or status changes. 241 313 … … 246 318 FOR EACH ROW 247 319 EXECUTE FUNCTION project.fn_trg_validate_equipment_availability(); 248 320 }}} 321 322 {{{ 249 323 CREATE TRIGGER trg_validate_equipment_availability_on_reservation 250 324 AFTER UPDATE OF reservation_date, start_time, end_time, status … … 257 331 === Discussion === 258 332 259 This requirement protects the database from accepting equipment reservations that exceed the available stock during the same period. It is implemented at database level, so the rule is enforced even if the data is inserted outside the prototype application.333 This requirement protects the database from accepting equipment reservations that exceed the available general stock during the same period. It is implemented at database level, so the rule is enforced even if the data is inserted outside the prototype application. 260 334 261 335 == Stored function: Approve or reject reservation == … … 364 438 {{{ 365 439 SELECT * 366 FROM project.v_pending_reservation_details; 440 FROM project.v_pending_reservation_details 441 ORDER BY reservation_date, start_time, reservation_id; 367 442 }}} 368 443 … … 379 454 }}} 380 455 456 The following screenshot shows the result returned by the stored function after approving a pending reservation. 457 458 [[Image(p7_approve_reject_function_result.png, width=100%)]] 459 381 460 After execution, the result can be checked in the base tables: 382 461 … … 391 470 }}} 392 471 472 The following screenshot shows that the approval record was inserted into the ''project.approvals'' table. 473 474 [[Image(p7_approval_verification.png, width=100%)]] 475 476 === Discussion === 477 478 The stored function centralizes the approval operation. Instead of performing separate manual SQL statements for updating the reservation and inserting an approval record, the system can call one function that validates the reservation, validates the approver, updates the reservation status, and inserts the approval decision. 479 393 480 == Trigger: Validate direct approval writes == 394 481 … … 397 484 Even if an approval is inserted directly into the ''project.approvals'' table, the database must still check whether the approver has an appropriate role. This prevents invalid approval records from being inserted manually. 398 485 486 Another consistency requirement is that when an approval decision is inserted, the corresponding reservation status should remain synchronized with the approval decision. 487 399 488 === Implementation === 400 489 401 The trigger ''trg_validate_approval_direct_write'' checks the role of the approver before an approval row is inserted or updated. 490 The following trigger function checks the role of the approver before an approval row is inserted or updated. 491 492 {{{ 493 CREATE OR REPLACE FUNCTION project.fn_validate_approval_direct_write() 494 RETURNS TRIGGER AS $$ 495 DECLARE 496 v_role VARCHAR(128); 497 BEGIN 498 SELECT role 499 INTO v_role 500 FROM project.users 501 WHERE user_id = NEW.approver_id; 502 503 ``` 504 IF NOT FOUND THEN 505 RAISE EXCEPTION 'Approver user % does not exist.', NEW.approver_id; 506 END IF; 507 508 IF v_role NOT IN ('admin', 'approver') THEN 509 RAISE EXCEPTION 510 'User % cannot approve reservations because role % is not allowed.', 511 NEW.approver_id, 512 v_role; 513 END IF; 514 515 RETURN NEW; 516 ``` 517 518 END; 519 $$ LANGUAGE plpgsql; 520 }}} 402 521 403 522 {{{ … … 409 528 }}} 410 529 411 Another trigger synchronizes the reservation status after an approval is inserted. 530 The following trigger function synchronizes the reservation status after an approval is inserted. 531 532 {{{ 533 CREATE OR REPLACE FUNCTION project.fn_sync_reservation_status_after_approval() 534 RETURNS TRIGGER AS $$ 535 BEGIN 536 UPDATE project.reservations 537 SET status = NEW.decision 538 WHERE reservation_id = NEW.reservation_id; 539 540 ``` 541 RETURN NEW; 542 ``` 543 544 END; 545 $$ LANGUAGE plpgsql; 546 }}} 412 547 413 548 {{{ … … 419 554 }}} 420 555 556 === Discussion === 557 558 These triggers make the approval table safer against manual changes. They also ensure that the reservation status remains consistent with the inserted approval decision. 559 421 560 == Custom domain: Approval decision domain == 422 561 … … 433 572 434 573 This custom domain is used as the type of the ''p_decision'' parameter in the stored function ''project.fn_approve_or_reject_reservation''. 574 575 === Discussion === 576 577 The custom domain improves consistency because the allowed approval decision values are defined once and can be reused in database programming objects. 435 578 436 579 == View: Pending reservation details == … … 488 631 ORDER BY reservation_date, start_time, reservation_id; 489 632 }}} 633 634 The following screenshot shows the result of the pending reservation details view. 635 636 [[Image(p7_pending_reservation_details_view.png, width=100%)]] 637 638 === Discussion === 639 640 This view is useful for the approval scenario because it gives the approver one readable result instead of requiring manual joins across reservations, users, rooms, buildings, equipment, and reservation equipment. 490 641 491 642 == View: Quarterly room utilization == … … 581 732 }}} 582 733 734 The following screenshot shows the result of the quarterly room utilization view. 735 736 [[Image(p7_quarterly_room_utilization_view.png, width=100%)]] 737 738 === Discussion === 739 740 This view provides analytical information about room usage. It uses grouping, aggregation, filtering by status, window functions, ranking, and a calculated utilization level. It can support future reports in the application. 741 583 742 == Final discussion == 584 743 … … 595 754 * a custom domain is used for approval decisions. 596 755 597 These additions are more advanced than the basic constraints from Phase P2 because they require triggers, stored functions, cross-table checks, aggregation, and procedural database logic. 756 These additions are more advanced than the basic constraints from Phase P2 because they require triggers, stored functions, cross-table checks, aggregation, window functions, and procedural database logic. 757 758 The implementation is suitable for the following project phases because it keeps the relational design from Phase P2, while adding stronger consistency rules and reusable database-level functionality.
