Changes between Version 1 and Version 2 of Normalization
- Timestamp:
- 08/31/26 18:24:39 (7 days ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
Normalization
v1 v2 3 3 This page documents Phase P5 of the Room Reservation System project. The goal of this phase is to check the database model using formal normalization methods and to improve the model if typical problems of un-normalized designs are found. 4 4 5 The normalization process starts from one theoretical de-normalized relation that contains all attributes from the model. From that relation, functional dependencies, candidate keys, and normal forms are analyzed. The relation is then decomposed step by step to the highest possible normal form while preserving functional dependencies and loss-less join properties. 6 7 == De-normalized database form == 5 The normalization process starts from one theoretical de-normalized relation that contains the important attributes from the project model. From that relation, functional dependencies, candidate keys, primary keys, normal forms and lossless-join properties are analyzed. The relation is then decomposed step by step into a normalized relational schema. 6 7 The final result is consistent with the relational schema from Phase P2 and with the implemented project database. 8 9 == De-normalized Database Form == 8 10 9 11 === Initial de-normalized relation === … … 11 13 The process starts from a single unified de-normalized relation. This relation is not implemented in the database. It is used only as a formal starting point for the normalization process. 12 14 13 The relation contains data about reservations, rooms, buildings, users, equipment, requested equipment, equipment assigned to rooms , and approvals. Attribute names are made globally unique in order to avoid duplicate attribute names.15 The relation contains data about reservations, rooms, buildings, users, equipment, requested equipment, equipment assigned to rooms and approvals. Attribute names are made globally unique in order to avoid ambiguity when the same entity type appears in different roles. 14 16 15 17 {{{ 16 18 R0( 17 reservation_id, 18 reservation_date, 19 start_time, 20 end_time, 21 status, 22 23 ``` 24 room_id, 25 room_code, 26 room_capacity, 27 room_type, 28 29 building_id, 30 building_name, 31 building_address, 32 33 requester_user_id, 34 requester_username, 35 requester_email, 36 requester_full_name, 37 requester_role, 38 39 requested_equipment_id, 40 requested_equipment_name, 41 requested_equipment_stock_quantity, 42 requested_quantity, 43 44 room_equipment_id, 45 room_equipment_name, 46 room_equipment_stock_quantity, 47 room_equipment_quantity, 48 49 approval_id, 50 approver_user_id, 51 approver_username, 52 approver_email, 53 approver_full_name, 54 approver_role, 55 56 decision, 57 decision_time, 58 note 59 ``` 60 61 ) 62 }}} 63 64 In this theoretical relation, the attributes ''requested_equipment_id'' and ''room_equipment_id'' both refer to equipment items, but they are named differently in the de-normalized form because the same entity type appears in two different roles. During normalization, these two groups are merged into one final ''equipment'' relation. 19 reservation_id, 20 reservation_date, 21 start_time, 22 end_time, 23 status, 24 25 room_id, 26 room_code, 27 room_capacity, 28 room_type, 29 30 building_id, 31 building_name, 32 building_address, 33 34 requester_user_id, 35 requester_username, 36 requester_email, 37 requester_full_name, 38 requester_role, 39 40 requested_equipment_id, 41 requested_equipment_name, 42 requested_equipment_stock_quantity, 43 requested_quantity, 44 45 room_equipment_id, 46 room_equipment_name, 47 room_equipment_stock_quantity, 48 room_equipment_quantity, 49 50 approval_id, 51 approver_user_id, 52 approver_username, 53 approver_email, 54 approver_full_name, 55 approver_role, 56 57 decision, 58 decision_time, 59 note 60 ) 61 }}} 62 63 In this theoretical relation, `requested_equipment_id` and `room_equipment_id` both refer to equipment items. They are named differently in the de-normalized form because the same entity type appears in two different roles. 64 65 During normalization, these two equipment groups are merged into one final `equipment` relation. 66 67 Similarly, requester and approver attributes both describe users. During normalization, these groups are merged into one final `users` relation. 65 68 66 69 === Functional dependencies === … … 72 75 73 76 room_id -> room_code, room_capacity, room_type, building_id 74 room_code -> room_id 77 room_code -> room_id, room_capacity, room_type, building_id 75 78 76 79 building_id -> building_name, building_address 77 building_name, building_address -> building_id78 80 79 81 requester_user_id -> requester_username, requester_email, requester_full_name, requester_role … … 86 88 87 89 requested_equipment_id -> requested_equipment_name, requested_equipment_stock_quantity 88 requested_equipment_name -> requested_equipment_id, requested_equipment_stock_quantity89 90 90 91 room_equipment_id -> room_equipment_name, room_equipment_stock_quantity 91 room_equipment_name -> room_equipment_id, room_equipment_stock_quantity 92 92 93 reservation_id, requested_equipment_id -> requested_quantity 94 95 room_id, room_equipment_id -> room_equipment_quantity 96 97 approval_id -> reservation_id, approver_user_id, decision, decision_time, note 98 reservation_id -> approval_id, approver_user_id, decision, decision_time, note 99 }}} 100 101 The dependency: 102 103 {{{ 104 reservation_id -> approval_id, approver_user_id, decision, decision_time, note 105 }}} 106 107 is valid for the final approval decision of a reservation. A reservation can be pending, so approval-related values may be missing until an approval decision is made. The business rule in this design is that a reservation can have at most one final approval decision. 108 109 === Candidate key of the de-normalized relation === 110 111 The de-normalized relation contains information about one reservation, one requested equipment item and one room-equipment item. 112 113 A complete row in the de-normalized relation is therefore identified by: 114 115 {{{ 116 K = (reservation_id, requested_equipment_id, room_equipment_id) 117 }}} 118 119 Closure of the proposed key: 120 121 {{{ 122 {reservation_id, requested_equipment_id, room_equipment_id}+ 123 }}} 124 125 Using the functional dependencies: 126 127 {{{ 128 reservation_id -> reservation_date, start_time, end_time, status, room_id, requester_user_id 129 room_id -> room_code, room_capacity, room_type, building_id 130 building_id -> building_name, building_address 131 requester_user_id -> requester_username, requester_email, requester_full_name, requester_role 132 requested_equipment_id -> requested_equipment_name, requested_equipment_stock_quantity 133 room_equipment_id -> room_equipment_name, room_equipment_stock_quantity 93 134 reservation_id, requested_equipment_id -> requested_quantity 94 135 room_id, room_equipment_id -> room_equipment_quantity 95 96 approval_id -> reservation_id, approver_user_id, decision, decision_time, note97 136 reservation_id -> approval_id, approver_user_id, decision, decision_time, note 98 }}} 99 100 The dependency ''reservation_id -> approval_id, approver_user_id, decision, decision_time, note'' is valid for reservations that already have an approval decision. A reservation may still be pending, so the approval-related values may be missing until an approval is created. The business rule is that one reservation can have at most one approval record. 101 102 === Candidate keys and primary key === 103 104 The de-normalized relation contains information about one reservation, the equipment requested in that reservation, and the equipment assigned to the selected room. Therefore, a complete row in the de-normalized relation is identified by a combination of the reservation and the two equipment-role identifiers. 105 106 Candidate keys for the de-normalized relation include: 107 108 {{{ 109 K1 = (reservation_id, requested_equipment_id, room_equipment_id) 110 K2 = (reservation_id, requested_equipment_name, room_equipment_id) 111 K3 = (reservation_id, requested_equipment_id, room_equipment_name) 112 K4 = (reservation_id, requested_equipment_name, room_equipment_name) 113 }}} 114 115 The selected primary key for the de-normalized relation is: 137 approver_user_id -> approver_username, approver_email, approver_full_name, approver_role 138 }}} 139 140 we get: 141 142 {{{ 143 {reservation_id, requested_equipment_id, room_equipment_id}+ = 144 { 145 reservation_id, 146 reservation_date, 147 start_time, 148 end_time, 149 status, 150 room_id, 151 room_code, 152 room_capacity, 153 room_type, 154 building_id, 155 building_name, 156 building_address, 157 requester_user_id, 158 requester_username, 159 requester_email, 160 requester_full_name, 161 requester_role, 162 requested_equipment_id, 163 requested_equipment_name, 164 requested_equipment_stock_quantity, 165 requested_quantity, 166 room_equipment_id, 167 room_equipment_name, 168 room_equipment_stock_quantity, 169 room_equipment_quantity, 170 approval_id, 171 approver_user_id, 172 approver_username, 173 approver_email, 174 approver_full_name, 175 approver_role, 176 decision, 177 decision_time, 178 note 179 } 180 }}} 181 182 Therefore, the proposed key determines all attributes of R0. 183 184 The key is minimal because: 185 186 * `reservation_id` alone does not determine all requested equipment rows. 187 * `requested_equipment_id` alone does not determine the reservation or room-equipment data. 188 * `room_equipment_id` alone does not determine the reservation or requested-equipment data. 189 * `(reservation_id, requested_equipment_id)` determines requested quantity, but does not determine room-equipment quantity. 190 * `(reservation_id, room_equipment_id)` does not determine the requested equipment item. 191 * `(requested_equipment_id, room_equipment_id)` does not determine the reservation. 192 193 Therefore, the candidate key used for the de-normalized relation is: 116 194 117 195 {{{ … … 119 197 }}} 120 198 121 This key is selected because it uses stable numeric identifiers instead of descriptive names. 199 The selected primary key for the de-normalized relation is the same candidate key: 200 201 {{{ 202 (reservation_id, requested_equipment_id, room_equipment_id) 203 }}} 204 205 This is only a theoretical primary key used for the normalization analysis. The de-normalized relation is not implemented in the database. 122 206 123 207 === Normal form of the de-normalized relation === 124 208 125 The de-normalized relation is in 1NF because all attributes contain atomic values. There are no repeating groups inside one attribute, and equipment occurrences are represented as separate rows. 126 127 However, the relation is not in 2NF because there are partial dependencies on parts of the composite key. Examples: 209 The de-normalized relation is in 1NF because all attributes contain atomic values. 210 211 However, the relation is not in 2NF because there are partial dependencies on parts of the composite key. 212 213 Examples: 128 214 129 215 {{{ … … 137 223 These dependencies show that some non-key attributes depend only on part of the composite key, not on the whole key. Because of this, the relation is not in 2NF. Consequently, it is also not in 3NF or BCNF. 138 224 139 == 1NF decomposition ==140 141 The first normal form requires that all attribute values are atomic and that repeating groups are removed.225 == 1NF Decomposition == 226 227 First normal form requires atomic attribute values and no repeating groups inside a single attribute. 142 228 143 229 The relation R0 is already represented with atomic values. Multiple requested equipment items and multiple equipment items assigned to a room are represented as separate rows instead of being stored as lists in one attribute. … … 149 235 {{{ 150 236 R0( 151 reservation_id,152 reservation_date,153 start_time,154 end_time,155 status,156 room_id,157 room_code,158 room_capacity,159 room_type,160 building_id,161 building_name,162 building_address,163 requester_user_id,164 requester_username,165 requester_email,166 requester_full_name,167 requester_role,168 requested_equipment_id,169 requested_equipment_name,170 requested_equipment_stock_quantity,171 requested_quantity,172 room_equipment_id,173 room_equipment_name,174 room_equipment_stock_quantity,175 room_equipment_quantity,176 approval_id,177 approver_user_id,178 approver_username,179 approver_email,180 approver_full_name,181 approver_role,182 decision,183 decision_time,184 note237 reservation_id, 238 reservation_date, 239 start_time, 240 end_time, 241 status, 242 room_id, 243 room_code, 244 room_capacity, 245 room_type, 246 building_id, 247 building_name, 248 building_address, 249 requester_user_id, 250 requester_username, 251 requester_email, 252 requester_full_name, 253 requester_role, 254 requested_equipment_id, 255 requested_equipment_name, 256 requested_equipment_stock_quantity, 257 requested_quantity, 258 room_equipment_id, 259 room_equipment_name, 260 room_equipment_stock_quantity, 261 room_equipment_quantity, 262 approval_id, 263 approver_user_id, 264 approver_username, 265 approver_email, 266 approver_full_name, 267 approver_role, 268 decision, 269 decision_time, 270 note 185 271 ) 186 272 }}} … … 188 274 The relation is in 1NF, but it is not in 2NF because of partial dependencies. 189 275 190 == 2NF decomposition ==276 == 2NF Decomposition == 191 277 192 278 A relation is in 2NF if it is in 1NF and every non-prime attribute is fully functionally dependent on the whole candidate key. 193 279 194 The relation R0 violates 2NF because many attributes depend only on part of the composite key. For example, reservation data depends only on ''reservation_id'', requested equipment data depends only on ''requested_equipment_id'', and room equipment data depends only on ''room_equipment_id''. 280 The relation R0 violates 2NF because many attributes depend only on part of the composite key. 281 282 Examples: 283 284 {{{ 285 reservation_id -> reservation_date, start_time, end_time, status, room_id, requester_user_id 286 287 requested_equipment_id -> requested_equipment_name, requested_equipment_stock_quantity 288 289 room_equipment_id -> room_equipment_name, room_equipment_stock_quantity 290 }}} 195 291 196 292 The first decomposition removes these partial dependencies. 197 293 198 === Decomposition step === 199 200 The following relations are obtained: 294 === Reservation data === 201 295 202 296 {{{ 203 297 R1_reservations( 204 reservation_id, 205 reservation_date, 206 start_time, 207 end_time, 208 status, 209 room_id, 210 requester_user_id 211 ) 212 }}} 213 214 Key: 298 reservation_id, 299 reservation_date, 300 start_time, 301 end_time, 302 status, 303 room_id, 304 requester_user_id 305 ) 306 }}} 307 308 Functional dependency: 309 310 {{{ 311 reservation_id -> reservation_date, start_time, end_time, status, room_id, requester_user_id 312 }}} 313 314 Candidate key and selected primary key: 215 315 216 316 {{{ … … 218 318 }}} 219 319 220 Functional dependencies preserved: 221 222 {{{ 223 reservation_id -> reservation_date, start_time, end_time, status, room_id, requester_user_id 224 }}} 320 === Room data === 225 321 226 322 {{{ 227 323 R1_rooms( 228 room_id, 229 room_code, 230 room_capacity, 231 room_type, 232 building_id, 233 building_name, 234 building_address 235 ) 236 }}} 237 238 Keys: 324 room_id, 325 room_code, 326 room_capacity, 327 room_type, 328 building_id, 329 building_name, 330 building_address 331 ) 332 }}} 333 334 Functional dependencies: 335 336 {{{ 337 room_id -> room_code, room_capacity, room_type, building_id, building_name, building_address 338 room_code -> room_id, room_capacity, room_type, building_id, building_name, building_address 339 }}} 340 341 Candidate keys: 239 342 240 343 {{{ … … 243 346 }}} 244 347 245 Functional dependencies preserved: 246 247 {{{ 248 room_id -> room_code, room_capacity, room_type, building_id, building_name, building_address 249 room_code -> room_id 250 }}} 348 Selected primary key: 349 350 {{{ 351 room_id 352 }}} 353 354 === User data in requester role === 251 355 252 356 {{{ 253 357 R1_requester_users( 254 requester_user_id, 255 requester_username, 256 requester_email, 257 requester_full_name, 258 requester_role 259 ) 260 }}} 261 262 Keys: 358 requester_user_id, 359 requester_username, 360 requester_email, 361 requester_full_name, 362 requester_role 363 ) 364 }}} 365 366 Functional dependencies: 367 368 {{{ 369 requester_user_id -> requester_username, requester_email, requester_full_name, requester_role 370 requester_username -> requester_user_id, requester_email, requester_full_name, requester_role 371 requester_email -> requester_user_id, requester_username, requester_full_name, requester_role 372 }}} 373 374 Candidate keys: 263 375 264 376 {{{ … … 268 380 }}} 269 381 270 Functional dependencies preserved:271 272 {{{ 273 requester_user_id -> requester_username, requester_email, requester_full_name, requester_role274 requester_username -> requester_user_id, requester_email, requester_full_name, requester_role 275 requester_email -> requester_user_id, requester_username, requester_full_name, requester_role 276 }}} 382 Selected primary key: 383 384 {{{ 385 requester_user_id 386 }}} 387 388 === User data in approver role === 277 389 278 390 {{{ 279 391 R1_approver_users( 280 approver_user_id, 281 approver_username, 282 approver_email, 283 approver_full_name, 284 approver_role 285 ) 286 }}} 287 288 Keys: 392 approver_user_id, 393 approver_username, 394 approver_email, 395 approver_full_name, 396 approver_role 397 ) 398 }}} 399 400 Functional dependencies: 401 402 {{{ 403 approver_user_id -> approver_username, approver_email, approver_full_name, approver_role 404 approver_username -> approver_user_id, approver_email, approver_full_name, approver_role 405 approver_email -> approver_user_id, approver_username, approver_full_name, approver_role 406 }}} 407 408 Candidate keys: 289 409 290 410 {{{ … … 294 414 }}} 295 415 296 Functional dependencies preserved:297 298 {{{ 299 approver_user_id -> approver_username, approver_email, approver_full_name, approver_role300 approver_username -> approver_user_id, approver_email, approver_full_name, approver_role 301 approver_email -> approver_user_id, approver_username, approver_full_name, approver_role 302 }}} 416 Selected primary key: 417 418 {{{ 419 approver_user_id 420 }}} 421 422 === Requested equipment data === 303 423 304 424 {{{ 305 425 R1_requested_equipment( 306 requested_equipment_id, 307 requested_equipment_name, 308 requested_equipment_stock_quantity 309 ) 310 }}} 311 312 Keys: 426 requested_equipment_id, 427 requested_equipment_name, 428 requested_equipment_stock_quantity 429 ) 430 }}} 431 432 Functional dependency: 433 434 {{{ 435 requested_equipment_id -> requested_equipment_name, requested_equipment_stock_quantity 436 }}} 437 438 Candidate key and selected primary key: 313 439 314 440 {{{ 315 441 requested_equipment_id 316 requested_equipment_name 317 }}} 318 319 Functional dependencies preserved: 320 321 {{{ 322 requested_equipment_id -> requested_equipment_name, requested_equipment_stock_quantity 323 requested_equipment_name -> requested_equipment_id, requested_equipment_stock_quantity 324 }}} 442 }}} 443 444 === Room equipment item data === 325 445 326 446 {{{ 327 447 R1_room_equipment_item( 328 room_equipment_id, 329 room_equipment_name, 330 room_equipment_stock_quantity 331 ) 332 }}} 333 334 Keys: 448 room_equipment_id, 449 room_equipment_name, 450 room_equipment_stock_quantity 451 ) 452 }}} 453 454 Functional dependency: 455 456 {{{ 457 room_equipment_id -> room_equipment_name, room_equipment_stock_quantity 458 }}} 459 460 Candidate key and selected primary key: 335 461 336 462 {{{ 337 463 room_equipment_id 338 room_equipment_name 339 }}} 340 341 Functional dependencies preserved: 342 343 {{{ 344 room_equipment_id -> room_equipment_name, room_equipment_stock_quantity 345 room_equipment_name -> room_equipment_id, room_equipment_stock_quantity 346 }}} 464 }}} 465 466 === Reservation-equipment quantity === 347 467 348 468 {{{ 349 469 R1_reservation_equipment( 350 reservation_id, 351 requested_equipment_id, 352 requested_quantity 353 ) 354 }}} 355 356 Key: 470 reservation_id, 471 requested_equipment_id, 472 requested_quantity 473 ) 474 }}} 475 476 Functional dependency: 477 478 {{{ 479 reservation_id, requested_equipment_id -> requested_quantity 480 }}} 481 482 Candidate key and selected primary key: 357 483 358 484 {{{ … … 360 486 }}} 361 487 362 Functional dependency preserved: 363 364 {{{ 365 reservation_id, requested_equipment_id -> requested_quantity 366 }}} 488 === Room-equipment quantity === 367 489 368 490 {{{ 369 491 R1_room_equipment( 370 room_id, 371 room_equipment_id, 372 room_equipment_quantity 373 ) 374 }}} 375 376 Key: 492 room_id, 493 room_equipment_id, 494 room_equipment_quantity 495 ) 496 }}} 497 498 Functional dependency: 499 500 {{{ 501 room_id, room_equipment_id -> room_equipment_quantity 502 }}} 503 504 Candidate key and selected primary key: 377 505 378 506 {{{ … … 380 508 }}} 381 509 382 Functional dependency preserved: 383 384 {{{ 385 room_id, room_equipment_id -> room_equipment_quantity 386 }}} 510 === Approval data === 387 511 388 512 {{{ 389 513 R1_approvals( 390 approval_id, 391 reservation_id, 392 approver_user_id, 393 decision, 394 decision_time, 395 note 396 ) 397 }}} 398 399 Keys: 514 approval_id, 515 reservation_id, 516 approver_user_id, 517 decision, 518 decision_time, 519 note 520 ) 521 }}} 522 523 Functional dependencies: 524 525 {{{ 526 approval_id -> reservation_id, approver_user_id, decision, decision_time, note 527 reservation_id -> approval_id, approver_user_id, decision, decision_time, note 528 }}} 529 530 Candidate keys: 400 531 401 532 {{{ … … 404 535 }}} 405 536 406 Functional dependencies preserved: 407 408 {{{ 409 approval_id -> reservation_id, approver_user_id, decision, decision_time, note 410 reservation_id -> approval_id, approver_user_id, decision, decision_time, note 411 }}} 412 413 === Loss-less join and dependency preservation === 414 415 The decomposition to 2NF is loss-less because every decomposition step uses a determinant that is a key in one of the resulting relations. For example, reservation details are separated using ''reservation_id'', room details are separated using ''room_id'', and equipment details are separated using equipment identifiers. 416 417 The functional dependencies are preserved because every dependency from the original relation is represented in one of the decomposed relations. 418 419 After this step, the relations are in 2NF. However, some relations are still not in 3NF because transitive dependencies still exist. 420 421 == 3NF decomposition == 537 Selected primary key: 538 539 {{{ 540 approval_id 541 }}} 542 543 === 2NF conclusion === 544 545 After this decomposition, the partial dependencies from the original composite key are removed. 546 547 The resulting relations are in 2NF. However, some transitive dependencies still exist, especially between rooms and buildings. Therefore, the schema must be checked for 3NF. 548 549 == 3NF Decomposition == 422 550 423 551 A relation is in 3NF if it is in 2NF and no non-prime attribute depends transitively on a candidate key. 424 552 425 The relation ''R1_rooms''still contains a transitive dependency:553 The relation `R1_rooms` still contains a transitive dependency: 426 554 427 555 {{{ … … 436 564 }}} 437 565 438 This means that building data depends on ''room_id'' only through ''building_id''. To remove this transitive dependency, building data is decomposed into a separate relation.566 This means that building data depends on `room_id` only through `building_id`. To remove this transitive dependency, building data is decomposed into a separate relation. 439 567 440 568 === Decomposition of room and building data === … … 444 572 {{{ 445 573 R1_rooms( 446 room_id,447 room_code,448 room_capacity,449 room_type,450 building_id,451 building_name,452 building_address574 room_id, 575 room_code, 576 room_capacity, 577 room_type, 578 building_id, 579 building_name, 580 building_address 453 581 ) 454 582 }}} … … 458 586 {{{ 459 587 buildings( 460 building_id, 461 name, 462 address 463 ) 464 }}} 465 466 Keys: 588 building_id, 589 name, 590 address 591 ) 592 }}} 593 594 Functional dependency: 595 596 {{{ 597 building_id -> name, address 598 }}} 599 600 Candidate key and selected primary key: 467 601 468 602 {{{ 469 603 building_id 470 (name, address) 604 }}} 605 606 and: 607 608 {{{ 609 rooms( 610 room_id, 611 building_id, 612 room_code, 613 capacity, 614 type 615 ) 471 616 }}} 472 617 … … 474 619 475 620 {{{ 476 building_id -> name, address 477 name, address -> building_id 478 }}} 479 480 {{{ 481 rooms( 482 room_id, 483 building_id, 484 room_code, 485 capacity, 486 type 487 ) 488 }}} 489 490 Keys: 621 room_id -> building_id, room_code, capacity, type 622 room_code -> room_id, building_id, capacity, type 623 }}} 624 625 Candidate keys: 491 626 492 627 {{{ … … 495 630 }}} 496 631 632 Selected primary key: 633 634 {{{ 635 room_id 636 }}} 637 638 This removes the transitive dependency between rooms and building attributes. 639 640 === Merging user roles === 641 642 The 2NF decomposition produced separate requester and approver user relations because the same entity type appeared in different roles in the de-normalized relation. 643 644 However, both groups represent the same real entity set: users of the system. Therefore, they are merged into one relation: 645 646 {{{ 647 users( 648 user_id, 649 username, 650 email, 651 full_name, 652 role 653 ) 654 }}} 655 497 656 Functional dependencies: 498 657 499 658 {{{ 500 room_id -> building_id, room_code, capacity, type 501 room_code -> room_id 502 }}} 503 504 This removes the transitive dependency between rooms and building attributes. 505 506 === Merging user roles === 507 508 The 2NF decomposition produced separate requester and approver user relations because the same entity type appeared in different roles in the de-normalized relation. 509 510 However, both groups represent the same real entity set: users of the system. Therefore, they are merged into one relation: 511 512 {{{ 513 users( 514 user_id, 515 username, 516 email, 517 full_name, 518 role 519 ) 520 }}} 521 522 Keys: 659 user_id -> username, email, full_name, role 660 username -> user_id, email, full_name, role 661 email -> user_id, username, full_name, role 662 }}} 663 664 Candidate keys: 523 665 524 666 {{{ … … 528 670 }}} 529 671 672 Selected primary key: 673 674 {{{ 675 user_id 676 }}} 677 678 In the final schema, `reservations.user_id` references the requester, while `approvals.approver_id` references the user who made the approval decision. 679 680 === Merging equipment roles === 681 682 The 2NF decomposition produced separate requested-equipment and room-equipment item relations because equipment appeared in two different roles in the de-normalized relation. 683 684 However, both groups represent the same entity set: equipment types. Therefore, they are merged into one relation: 685 686 {{{ 687 equipment( 688 equipment_id, 689 name, 690 stock_quantity 691 ) 692 }}} 693 694 Functional dependency: 695 696 {{{ 697 equipment_id -> name, stock_quantity 698 }}} 699 700 Candidate key and selected primary key: 701 702 {{{ 703 equipment_id 704 }}} 705 706 The relation `room_equipment` represents equipment assigned to rooms, while `reservation_equipment` represents equipment requested as part of reservations. 707 708 === Final relations after 3NF decomposition === 709 710 After removing partial and transitive dependencies, the resulting relations are: 711 712 {{{ 713 buildings( 714 building_id, 715 name, 716 address 717 ) 718 }}} 719 720 {{{ 721 rooms( 722 room_id, 723 building_id, 724 room_code, 725 capacity, 726 type 727 ) 728 }}} 729 730 {{{ 731 equipment( 732 equipment_id, 733 name, 734 stock_quantity 735 ) 736 }}} 737 738 {{{ 739 room_equipment( 740 room_id, 741 equipment_id, 742 quantity 743 ) 744 }}} 745 746 {{{ 747 users( 748 user_id, 749 username, 750 email, 751 full_name, 752 role 753 ) 754 }}} 755 756 {{{ 757 reservations( 758 reservation_id, 759 room_id, 760 user_id, 761 reservation_date, 762 start_time, 763 end_time, 764 status 765 ) 766 }}} 767 768 {{{ 769 reservation_equipment( 770 reservation_id, 771 equipment_id, 772 requested_quantity 773 ) 774 }}} 775 776 {{{ 777 approvals( 778 approval_id, 779 reservation_id, 780 approver_id, 781 decision, 782 decision_time, 783 note 784 ) 785 }}} 786 787 {{{ 788 user_credentials( 789 user_id, 790 password_hash, 791 created_at, 792 updated_at 793 ) 794 }}} 795 796 The `user_credentials` relation is an application authentication extension used in the final implementation. It is also normalized because the password hash and credential timestamps depend on `user_id`. 797 798 == Formal Candidate Key and Primary Key Proof == 799 800 This section formally documents the candidate keys and selected primary keys for the final normalized relations. 801 802 The proof is based on attribute closure. For each relation, the selected key determines all attributes in the relation and is minimal. 803 804 === Buildings === 805 806 Relation: 807 808 {{{ 809 Buildings(building_id, name, address) 810 }}} 811 812 Functional dependency: 813 814 {{{ 815 building_id -> name, address 816 }}} 817 818 Closure: 819 820 {{{ 821 {building_id}+ = {building_id, name, address} 822 }}} 823 824 Therefore, `building_id` determines all attributes in the relation. 825 826 Candidate key: 827 828 {{{ 829 building_id 830 }}} 831 832 Selected primary key: 833 834 {{{ 835 building_id 836 }}} 837 838 No other candidate key is used because the documented functional dependencies do not show that `name` or `address` can uniquely determine a building. 839 840 === Rooms === 841 842 Relation: 843 844 {{{ 845 Rooms(room_id, building_id, room_code, capacity, type) 846 }}} 847 848 Functional dependencies: 849 850 {{{ 851 room_id -> building_id, room_code, capacity, type 852 room_code -> room_id, building_id, capacity, type 853 }}} 854 855 Closure of `room_id`: 856 857 {{{ 858 {room_id}+ = {room_id, building_id, room_code, capacity, type} 859 }}} 860 861 Closure of `room_code`: 862 863 {{{ 864 {room_code}+ = {room_code, room_id, building_id, capacity, type} 865 }}} 866 867 Candidate keys: 868 869 {{{ 870 room_id 871 room_code 872 }}} 873 874 Selected primary key: 875 876 {{{ 877 room_id 878 }}} 879 880 `room_code` is an alternate candidate key because room codes are unique in the system. The surrogate key `room_id` is selected as the primary key because it is stable and convenient for foreign-key references from reservations and room-equipment assignments. 881 882 No other candidate keys exist because `building_id`, `capacity` and `type` do not uniquely identify a room. 883 884 === Equipment === 885 886 Relation: 887 888 {{{ 889 Equipment(equipment_id, name, stock_quantity) 890 }}} 891 892 Functional dependency: 893 894 {{{ 895 equipment_id -> name, stock_quantity 896 }}} 897 898 Closure: 899 900 {{{ 901 {equipment_id}+ = {equipment_id, name, stock_quantity} 902 }}} 903 904 Candidate key: 905 906 {{{ 907 equipment_id 908 }}} 909 910 Selected primary key: 911 912 {{{ 913 equipment_id 914 }}} 915 916 No other candidate key is used because the documented dependencies do not require equipment name to uniquely determine the full equipment record. 917 918 === RoomEquipment === 919 920 Relation: 921 922 {{{ 923 RoomEquipment(room_id, equipment_id, quantity) 924 }}} 925 926 Functional dependency: 927 928 {{{ 929 room_id, equipment_id -> quantity 930 }}} 931 932 Closure: 933 934 {{{ 935 {room_id, equipment_id}+ = {room_id, equipment_id, quantity} 936 }}} 937 938 Candidate key: 939 940 {{{ 941 (room_id, equipment_id) 942 }}} 943 944 Selected primary key: 945 946 {{{ 947 (room_id, equipment_id) 948 }}} 949 950 The key is minimal because: 951 952 * `room_id` alone does not determine the quantity, since a room can contain multiple equipment types. 953 * `equipment_id` alone does not determine the quantity, since the same equipment type can exist in multiple rooms. 954 955 Therefore, both attributes are required. 956 957 === Users === 958 959 Relation: 960 961 {{{ 962 Users(user_id, username, email, full_name, role) 963 }}} 964 530 965 Functional dependencies: 531 966 … … 536 971 }}} 537 972 538 In the final schema, ''reservations.user_id'' references the requester, while ''approvals.approver_id'' references the user who made the approval decision. 539 540 === Merging equipment roles === 541 542 The 2NF decomposition produced separate requested-equipment and room-equipment item relations because equipment appeared in two different roles in the de-normalized relation. 543 544 However, both groups represent the same entity set: equipment types. Therefore, they are merged into one relation: 545 546 {{{ 547 equipment( 548 equipment_id, 549 name, 550 stock_quantity 551 ) 552 }}} 553 554 Keys: 555 556 {{{ 557 equipment_id 558 name 973 Closure of `user_id`: 974 975 {{{ 976 {user_id}+ = {user_id, username, email, full_name, role} 977 }}} 978 979 Closure of `username`: 980 981 {{{ 982 {username}+ = {username, user_id, email, full_name, role} 983 }}} 984 985 Closure of `email`: 986 987 {{{ 988 {email}+ = {email, user_id, username, full_name, role} 989 }}} 990 991 Candidate keys: 992 993 {{{ 994 user_id 995 username 996 email 997 }}} 998 999 Selected primary key: 1000 1001 {{{ 1002 user_id 1003 }}} 1004 1005 `username` and `email` are alternate candidate keys because they are unique. The primary key is `user_id` because it is stable and used as a foreign key in reservations, approvals and user credentials. 1006 1007 No other candidate key exists because `full_name` and `role` do not uniquely identify a user. 1008 1009 === Reservations === 1010 1011 Relation: 1012 1013 {{{ 1014 Reservations(reservation_id, room_id, user_id, reservation_date, start_time, end_time, status) 1015 }}} 1016 1017 Functional dependency: 1018 1019 {{{ 1020 reservation_id -> room_id, user_id, reservation_date, start_time, end_time, status 1021 }}} 1022 1023 Closure: 1024 1025 {{{ 1026 {reservation_id}+ = {reservation_id, room_id, user_id, reservation_date, start_time, end_time, status} 1027 }}} 1028 1029 Candidate key: 1030 1031 {{{ 1032 reservation_id 1033 }}} 1034 1035 Selected primary key: 1036 1037 {{{ 1038 reservation_id 1039 }}} 1040 1041 No other candidate key is used. Although combinations such as room, date, start time and end time are important for overlap detection, they are not selected as candidate keys because the system also supports pending, rejected, cancelled and equipment-related reservation scenarios. Business rules about overlap are enforced separately through constraints and triggers, not by replacing the reservation primary key. 1042 1043 === ReservationEquipment === 1044 1045 Relation: 1046 1047 {{{ 1048 ReservationEquipment(reservation_id, equipment_id, requested_quantity) 1049 }}} 1050 1051 Functional dependency: 1052 1053 {{{ 1054 reservation_id, equipment_id -> requested_quantity 1055 }}} 1056 1057 Closure: 1058 1059 {{{ 1060 {reservation_id, equipment_id}+ = {reservation_id, equipment_id, requested_quantity} 1061 }}} 1062 1063 Candidate key: 1064 1065 {{{ 1066 (reservation_id, equipment_id) 1067 }}} 1068 1069 Selected primary key: 1070 1071 {{{ 1072 (reservation_id, equipment_id) 1073 }}} 1074 1075 The key is minimal because: 1076 1077 * `reservation_id` alone does not determine the requested quantity for one equipment item, since a reservation can request multiple equipment types. 1078 * `equipment_id` alone does not determine the requested quantity, since the same equipment type can be requested in many reservations. 1079 1080 Therefore, both attributes are required. 1081 1082 === Approvals === 1083 1084 Relation: 1085 1086 {{{ 1087 Approvals(approval_id, reservation_id, approver_id, decision, decision_time, note) 559 1088 }}} 560 1089 … … 562 1091 563 1092 {{{ 1093 approval_id -> reservation_id, approver_id, decision, decision_time, note 1094 reservation_id -> approval_id, approver_id, decision, decision_time, note 1095 }}} 1096 1097 Closure of `approval_id`: 1098 1099 {{{ 1100 {approval_id}+ = {approval_id, reservation_id, approver_id, decision, decision_time, note} 1101 }}} 1102 1103 Closure of `reservation_id`: 1104 1105 {{{ 1106 {reservation_id}+ = {reservation_id, approval_id, approver_id, decision, decision_time, note} 1107 }}} 1108 1109 Candidate keys: 1110 1111 {{{ 1112 approval_id 1113 reservation_id 1114 }}} 1115 1116 Selected primary key: 1117 1118 {{{ 1119 approval_id 1120 }}} 1121 1122 `reservation_id` is an alternate candidate key because the design stores at most one final approval decision for each reservation. 1123 1124 === UserCredentials === 1125 1126 Relation: 1127 1128 {{{ 1129 UserCredentials(user_id, password_hash, created_at, updated_at) 1130 }}} 1131 1132 Functional dependency: 1133 1134 {{{ 1135 user_id -> password_hash, created_at, updated_at 1136 }}} 1137 1138 Closure: 1139 1140 {{{ 1141 {user_id}+ = {user_id, password_hash, created_at, updated_at} 1142 }}} 1143 1144 Candidate key: 1145 1146 {{{ 1147 user_id 1148 }}} 1149 1150 Selected primary key: 1151 1152 {{{ 1153 user_id 1154 }}} 1155 1156 This relation uses `user_id` as both a primary key and a foreign key to `Users`. This creates a one-to-one relationship between a user and the user's authentication credentials. 1157 1158 == Formal Lossless-Join Proof == 1159 1160 This section proves that the decompositions used in the normalized schema are lossless. 1161 1162 For a binary decomposition of a relation R into R1 and R2, the decomposition is lossless if: 1163 1164 {{{ 1165 R1 ∩ R2 -> R1 1166 }}} 1167 1168 or: 1169 1170 {{{ 1171 R1 ∩ R2 -> R2 1172 }}} 1173 1174 In other words, the common attributes between the two decomposed relations must functionally determine all attributes of at least one of the decomposed relations. 1175 1176 === Buildings and Rooms === 1177 1178 Decomposition: 1179 1180 {{{ 1181 Buildings(building_id, name, address) 1182 Rooms(room_id, building_id, room_code, capacity, type) 1183 }}} 1184 1185 Common attribute: 1186 1187 {{{ 1188 Buildings ∩ Rooms = {building_id} 1189 }}} 1190 1191 Functional dependency: 1192 1193 {{{ 1194 building_id -> name, address 1195 }}} 1196 1197 Since `building_id` determines all attributes of `Buildings`, the common attribute determines the `Buildings` relation. 1198 1199 Therefore: 1200 1201 {{{ 1202 {building_id} -> Buildings 1203 }}} 1204 1205 The decomposition between `Buildings` and `Rooms` is lossless. 1206 1207 === Rooms and Reservations === 1208 1209 Decomposition: 1210 1211 {{{ 1212 Rooms(room_id, building_id, room_code, capacity, type) 1213 Reservations(reservation_id, room_id, user_id, reservation_date, start_time, end_time, status) 1214 }}} 1215 1216 Common attribute: 1217 1218 {{{ 1219 Rooms ∩ Reservations = {room_id} 1220 }}} 1221 1222 Functional dependency: 1223 1224 {{{ 1225 room_id -> building_id, room_code, capacity, type 1226 }}} 1227 1228 Since `room_id` determines all attributes of `Rooms`, the common attribute determines the `Rooms` relation. 1229 1230 Therefore: 1231 1232 {{{ 1233 {room_id} -> Rooms 1234 }}} 1235 1236 The decomposition between `Rooms` and `Reservations` is lossless for room-based reservations. 1237 1238 The design also supports equipment-only reservations where `room_id` may be null. Equipment-only reservation resources are represented separately through `ReservationEquipment`. 1239 1240 === Users and Reservations === 1241 1242 Decomposition: 1243 1244 {{{ 1245 Users(user_id, username, email, full_name, role) 1246 Reservations(reservation_id, room_id, user_id, reservation_date, start_time, end_time, status) 1247 }}} 1248 1249 Common attribute: 1250 1251 {{{ 1252 Users ∩ Reservations = {user_id} 1253 }}} 1254 1255 Functional dependency: 1256 1257 {{{ 1258 user_id -> username, email, full_name, role 1259 }}} 1260 1261 Since `user_id` determines all attributes of `Users`, the common attribute determines the `Users` relation. 1262 1263 Therefore: 1264 1265 {{{ 1266 {user_id} -> Users 1267 }}} 1268 1269 The decomposition between `Users` and `Reservations` is lossless. 1270 1271 === Rooms and RoomEquipment === 1272 1273 Decomposition: 1274 1275 {{{ 1276 Rooms(room_id, building_id, room_code, capacity, type) 1277 RoomEquipment(room_id, equipment_id, quantity) 1278 }}} 1279 1280 Common attribute: 1281 1282 {{{ 1283 Rooms ∩ RoomEquipment = {room_id} 1284 }}} 1285 1286 Functional dependency: 1287 1288 {{{ 1289 room_id -> building_id, room_code, capacity, type 1290 }}} 1291 1292 Since `room_id` determines all attributes of `Rooms`, the common attribute determines the `Rooms` relation. 1293 1294 Therefore: 1295 1296 {{{ 1297 {room_id} -> Rooms 1298 }}} 1299 1300 The decomposition between `Rooms` and `RoomEquipment` is lossless. 1301 1302 === Equipment and RoomEquipment === 1303 1304 Decomposition: 1305 1306 {{{ 1307 Equipment(equipment_id, name, stock_quantity) 1308 RoomEquipment(room_id, equipment_id, quantity) 1309 }}} 1310 1311 Common attribute: 1312 1313 {{{ 1314 Equipment ∩ RoomEquipment = {equipment_id} 1315 }}} 1316 1317 Functional dependency: 1318 1319 {{{ 564 1320 equipment_id -> name, stock_quantity 565 name -> equipment_id, stock_quantity 566 }}} 567 568 The relation ''room_equipment'' represents equipment assigned to rooms, while ''reservation_equipment'' represents equipment requested as part of reservations. 569 570 === Final relations after 3NF decomposition === 571 572 After removing partial and transitive dependencies, the resulting relations are: 573 574 {{{ 575 buildings( 576 building_id, 577 name, 578 address 579 ) 580 }}} 581 582 {{{ 583 rooms( 584 room_id, 585 building_id, 586 room_code, 587 capacity, 588 type 589 ) 590 }}} 591 592 {{{ 593 equipment( 594 equipment_id, 595 name, 596 stock_quantity 597 ) 598 }}} 599 600 {{{ 601 room_equipment( 602 room_id, 603 equipment_id, 604 quantity 605 ) 606 }}} 607 608 {{{ 609 users( 610 user_id, 611 username, 612 email, 613 full_name, 614 role 615 ) 616 }}} 617 618 {{{ 619 reservations( 620 reservation_id, 621 room_id, 622 user_id, 623 reservation_date, 624 start_time, 625 end_time, 626 status 627 ) 628 }}} 629 630 {{{ 631 reservation_equipment( 632 reservation_id, 633 equipment_id, 634 requested_quantity 635 ) 636 }}} 637 638 {{{ 639 approvals( 640 approval_id, 641 reservation_id, 642 approver_id, 643 decision, 644 decision_time, 645 note 646 ) 647 }}} 648 649 === Loss-less join and dependency preservation === 650 651 The 3NF decomposition is loss-less because each decomposition separates attributes based on a functional dependency where the determinant becomes a key in one of the resulting relations. 652 653 Examples: 1321 }}} 1322 1323 Since `equipment_id` determines all attributes of `Equipment`, the common attribute determines the `Equipment` relation. 1324 1325 Therefore: 1326 1327 {{{ 1328 {equipment_id} -> Equipment 1329 }}} 1330 1331 The decomposition between `Equipment` and `RoomEquipment` is lossless. 1332 1333 === Reservations and ReservationEquipment === 1334 1335 Decomposition: 1336 1337 {{{ 1338 Reservations(reservation_id, room_id, user_id, reservation_date, start_time, end_time, status) 1339 ReservationEquipment(reservation_id, equipment_id, requested_quantity) 1340 }}} 1341 1342 Common attribute: 1343 1344 {{{ 1345 Reservations ∩ ReservationEquipment = {reservation_id} 1346 }}} 1347 1348 Functional dependency: 1349 1350 {{{ 1351 reservation_id -> room_id, user_id, reservation_date, start_time, end_time, status 1352 }}} 1353 1354 Since `reservation_id` determines all attributes of `Reservations`, the common attribute determines the `Reservations` relation. 1355 1356 Therefore: 1357 1358 {{{ 1359 {reservation_id} -> Reservations 1360 }}} 1361 1362 The decomposition between `Reservations` and `ReservationEquipment` is lossless. 1363 1364 === Equipment and ReservationEquipment === 1365 1366 Decomposition: 1367 1368 {{{ 1369 Equipment(equipment_id, name, stock_quantity) 1370 ReservationEquipment(reservation_id, equipment_id, requested_quantity) 1371 }}} 1372 1373 Common attribute: 1374 1375 {{{ 1376 Equipment ∩ ReservationEquipment = {equipment_id} 1377 }}} 1378 1379 Functional dependency: 1380 1381 {{{ 1382 equipment_id -> name, stock_quantity 1383 }}} 1384 1385 Since `equipment_id` determines all attributes of `Equipment`, the common attribute determines the `Equipment` relation. 1386 1387 Therefore: 1388 1389 {{{ 1390 {equipment_id} -> Equipment 1391 }}} 1392 1393 The decomposition between `Equipment` and `ReservationEquipment` is lossless. 1394 1395 === Reservations and Approvals === 1396 1397 Decomposition: 1398 1399 {{{ 1400 Reservations(reservation_id, room_id, user_id, reservation_date, start_time, end_time, status) 1401 Approvals(approval_id, reservation_id, approver_id, decision, decision_time, note) 1402 }}} 1403 1404 Common attribute: 1405 1406 {{{ 1407 Reservations ∩ Approvals = {reservation_id} 1408 }}} 1409 1410 Functional dependency: 1411 1412 {{{ 1413 reservation_id -> room_id, user_id, reservation_date, start_time, end_time, status 1414 }}} 1415 1416 Since `reservation_id` determines all attributes of `Reservations`, the common attribute determines the `Reservations` relation. 1417 1418 Therefore: 1419 1420 {{{ 1421 {reservation_id} -> Reservations 1422 }}} 1423 1424 The decomposition between `Reservations` and `Approvals` is lossless. 1425 1426 === Users and Approvals === 1427 1428 The approval relation stores the approver using `approver_id`, which references `Users(user_id)`. 1429 1430 Decomposition: 1431 1432 {{{ 1433 Users(user_id, username, email, full_name, role) 1434 Approvals(approval_id, reservation_id, approver_id, decision, decision_time, note) 1435 }}} 1436 1437 Relationship attribute: 1438 1439 {{{ 1440 approver_id references user_id 1441 }}} 1442 1443 Functional dependency in `Users`: 1444 1445 {{{ 1446 user_id -> username, email, full_name, role 1447 }}} 1448 1449 After matching `approver_id` with `user_id`, the referenced user identifier determines all attributes of the corresponding user. 1450 1451 Therefore, the decomposition between approver data and approval data is lossless. 1452 1453 === Users and UserCredentials === 1454 1455 Decomposition: 1456 1457 {{{ 1458 Users(user_id, username, email, full_name, role) 1459 UserCredentials(user_id, password_hash, created_at, updated_at) 1460 }}} 1461 1462 Common attribute: 1463 1464 {{{ 1465 Users ∩ UserCredentials = {user_id} 1466 }}} 1467 1468 Functional dependencies: 1469 1470 {{{ 1471 user_id -> username, email, full_name, role 1472 user_id -> password_hash, created_at, updated_at 1473 }}} 1474 1475 Since `user_id` determines all attributes in both `Users` and `UserCredentials`, the decomposition is lossless. 1476 1477 This also preserves the intended one-to-one relationship between a user profile and user credentials. 1478 1479 == BCNF Check == 1480 1481 A relation is in BCNF if for every non-trivial functional dependency X -> Y, X is a superkey. 1482 1483 The final relations are checked for BCNF as follows. 1484 1485 === buildings === 1486 1487 Relation: 1488 1489 {{{ 1490 buildings(building_id, name, address) 1491 }}} 1492 1493 Functional dependency: 654 1494 655 1495 {{{ … … 657 1497 }}} 658 1498 659 is preserved in: 660 661 {{{ 662 buildings(building_id, name, address) 663 }}} 664 665 and the relation can be joined back with: 1499 Candidate key: 1500 1501 {{{ 1502 building_id 1503 }}} 1504 1505 The determinant is a candidate key, so the relation is in BCNF. 1506 1507 === rooms === 1508 1509 Relation: 666 1510 667 1511 {{{ … … 669 1513 }}} 670 1514 671 using the foreign key ''building_id''.672 673 Similarly, user data and equipment data are separated into their own relations and referenced by identifiers from reservations, approvals, room equipment, and reservation equipment.674 675 The decomposition preserves the functional dependencies because each important dependency is represented inside one of the final relations.676 677 == BCNF if possible ==678 679 A relation is in BCNF if for every non-trivial functional dependency X -> Y, X is a superkey.680 681 The final relations are checked for BCNF as follows.682 683 === buildings ===684 685 Relation:686 687 {{{688 buildings(building_id, name, address)689 }}}690 691 1515 Functional dependencies: 692 1516 693 1517 {{{ 694 building_id -> name, address695 name, address -> building_id696 }}}697 698 Candidate keys:699 700 {{{701 building_id702 (name, address)703 }}}704 705 All determinants are candidate keys, so the relation is in BCNF.706 707 === rooms ===708 709 Relation:710 711 {{{712 rooms(room_id, building_id, room_code, capacity, type)713 }}}714 715 Functional dependencies:716 717 {{{718 1518 room_id -> building_id, room_code, capacity, type 719 room_code -> room_id 1519 room_code -> room_id, building_id, capacity, type 720 1520 }}} 721 1521 … … 737 1537 }}} 738 1538 739 Functional dependenc ies:1539 Functional dependency: 740 1540 741 1541 {{{ 742 1542 equipment_id -> name, stock_quantity 743 name -> equipment_id, stock_quantity 744 }}} 745 746 Candidate keys: 1543 }}} 1544 1545 Candidate key: 747 1546 748 1547 {{{ 749 1548 equipment_id 750 name 751 }}} 752 753 All determinants are candidate keys, so the relation is in BCNF. 1549 }}} 1550 1551 The determinant is a candidate key, so the relation is in BCNF. 754 1552 755 1553 === room_equipment === … … 809 1607 }}} 810 1608 811 Functional dependenc ies:1609 Functional dependency: 812 1610 813 1611 {{{ 814 1612 reservation_id -> room_id, user_id, reservation_date, start_time, end_time, status 815 room_id, reservation_date, start_time, end_time -> reservation_id 816 }}} 817 818 Candidate keys: 1613 }}} 1614 1615 Candidate key: 819 1616 820 1617 {{{ 821 1618 reservation_id 822 (room_id, reservation_date, start_time, end_time) for room-based reservations 823 }}} 824 825 The determinant ''reservation_id'' is the primary key. The determinant ''(room_id, reservation_date, start_time, end_time)'' is an alternative key for room-based reservations. Therefore, the relation is in BCNF. 826 827 The attribute ''room_id'' is optional because equipment-only reservations are allowed. 1619 }}} 1620 1621 The determinant is the candidate key, so the relation is in BCNF. 1622 1623 Business rules about overlapping reservations are enforced through constraints and triggers. They are not treated as functional dependencies for the purpose of selecting the primary key. 828 1624 829 1625 === reservation_equipment === … … 873 1669 All determinants are candidate keys, so the relation is in BCNF. 874 1670 875 === BCNF conclusion === 876 877 All final relations are in BCNF. The decomposition also preserves the important functional dependencies and has loss-less join properties. Therefore, no further decomposition is required. 878 879 == Final result and discussion == 1671 === user_credentials === 1672 1673 Relation: 1674 1675 {{{ 1676 user_credentials(user_id, password_hash, created_at, updated_at) 1677 }}} 1678 1679 Functional dependency: 1680 1681 {{{ 1682 user_id -> password_hash, created_at, updated_at 1683 }}} 1684 1685 Candidate key: 1686 1687 {{{ 1688 user_id 1689 }}} 1690 1691 The determinant is the candidate key, so the relation is in BCNF. 1692 1693 == Final Result and Discussion == 880 1694 881 1695 === Normalized relational model === … … 885 1699 {{{ 886 1700 buildings( 887 building_id PK,888 name,889 address1701 building_id PK, 1702 name, 1703 address 890 1704 ) 891 1705 }}} … … 893 1707 {{{ 894 1708 rooms( 895 room_id PK,896 building_id FK,897 room_code AK,898 capacity,899 type1709 room_id PK, 1710 building_id FK, 1711 room_code AK, 1712 capacity, 1713 type 900 1714 ) 901 1715 }}} … … 903 1717 {{{ 904 1718 equipment( 905 equipment_id PK,906 name AK,907 stock_quantity1719 equipment_id PK, 1720 name, 1721 stock_quantity 908 1722 ) 909 1723 }}} … … 911 1725 {{{ 912 1726 room_equipment( 913 room_id PK, FK,914 equipment_id PK, FK,915 quantity1727 room_id PK, FK, 1728 equipment_id PK, FK, 1729 quantity 916 1730 ) 917 1731 }}} … … 919 1733 {{{ 920 1734 users( 921 user_id PK,922 username AK,923 email AK,924 full_name,925 role1735 user_id PK, 1736 username AK, 1737 email AK, 1738 full_name, 1739 role 926 1740 ) 927 1741 }}} … … 929 1743 {{{ 930 1744 reservations( 931 reservation_id PK,932 room_id FK,933 user_id FK,934 reservation_date,935 start_time,936 end_time,937 status1745 reservation_id PK, 1746 room_id FK, 1747 user_id FK, 1748 reservation_date, 1749 start_time, 1750 end_time, 1751 status 938 1752 ) 939 1753 }}} … … 941 1755 {{{ 942 1756 reservation_equipment( 943 reservation_id PK, FK,944 equipment_id PK, FK,945 requested_quantity1757 reservation_id PK, FK, 1758 equipment_id PK, FK, 1759 requested_quantity 946 1760 ) 947 1761 }}} … … 949 1763 {{{ 950 1764 approvals( 951 approval_id PK, 952 reservation_id FK, AK, 953 approver_id FK, 954 decision, 955 decision_time, 956 note 1765 approval_id PK, 1766 reservation_id AK, FK, 1767 approver_id FK, 1768 decision, 1769 decision_time, 1770 note 1771 ) 1772 }}} 1773 1774 {{{ 1775 user_credentials( 1776 user_id PK, FK, 1777 password_hash, 1778 created_at, 1779 updated_at 957 1780 ) 958 1781 }}} … … 960 1783 === Discussion === 961 1784 962 The normalization process started from a single de-normalized relation containing all attributes from the model. That relation was in 1NF, but it was not in 2NF because many attributes depended only on parts of the composite key. The decomposition removed partial dependencies, then removed transitive dependencies, and finally checked the resulting relations for BCNF. 963 964 The final normalized design contains separate relations for buildings, rooms, equipment, users, reservations, room equipment, reservation equipment, and approvals. 1785 The normalization process started from a single de-normalized relation containing the important attributes from the model. That relation was in 1NF, but it was not in 2NF because many attributes depended only on parts of the composite key. 1786 1787 The decomposition removed partial dependencies, then removed transitive dependencies, and finally checked the resulting relations for BCNF. 1788 1789 The final normalized design contains separate relations for: 1790 1791 * buildings 1792 * rooms 1793 * equipment 1794 * room_equipment 1795 * users 1796 * reservations 1797 * reservation_equipment 1798 * approvals 1799 * user_credentials 965 1800 966 1801 The M:N relationship between rooms and equipment is represented by: … … 978 1813 This prevents repeated data and allows equipment to be managed independently from both rooms and reservations. 979 1814 1815 The authentication data is separated into: 1816 1817 {{{ 1818 user_credentials(user_id, password_hash, created_at, updated_at) 1819 }}} 1820 1821 This avoids storing password hashes directly in the `users` relation and keeps authentication details separate from general user profile data. 1822 980 1823 === Comparison with Phase P2 === 981 1824 982 1825 The final normalized model obtained in this phase is consistent with the relational design from Phase P2. The same main relations are used: 983 1826 984 * buildings985 * rooms986 * equipment987 * room_equipment988 * users989 * reservations990 * reservation_equipment991 * approvals992 993 Th is means that the Phase P2 relational schema was already designed in a normalized way. No structural change to the existing PostgreSQL database schema is required after the normalization check.994 995 Th e existing Phase P2 design will continue to be used in the following project phases.1827 * buildings 1828 * rooms 1829 * equipment 1830 * room_equipment 1831 * users 1832 * reservations 1833 * reservation_equipment 1834 * approvals 1835 1836 The `user_credentials` relation was added later as an application-level authentication extension in the final implementation. It is also normalized and connected to `users` through a one-to-one relationship. 1837 1838 This means that the Phase P2 relational schema was already designed in a normalized way. No structural change to the main database model is required after the normalization check. 996 1839 997 1840 == Conclusion == 998 1841 999 The formal normalization process shows that the final relational model is in BCNF. The decomposition preserves the functional dependencies, has loss-less join properties, and matches the already implemented relational schema from Phase P2. 1000 1001 Therefore, the current database design is appropriate for the next phases of the project. 1842 The formal normalization process shows that the final relational model is in BCNF. 1843 1844 The documentation now includes: 1845 1846 * formal candidate key proof using attribute closure 1847 * selected primary keys for every final relation 1848 * explanation why the selected keys are minimal 1849 * formal lossless-join proof for the decompositions 1850 * BCNF verification for every final relation 1851 1852 The decomposition preserves the important functional dependencies, has lossless-join properties and matches the implemented relational schema. 1853 1854 Therefore, the current database design is appropriate for the final Room Reservation System implementation.
