= Normalization = 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. 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. The final result is consistent with the relational schema from Phase P2 and with the implemented project database. == De-normalized Database Form == === Initial de-normalized relation === 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. 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. {{{ R0( reservation_id, reservation_date, start_time, end_time, status, room_id, room_code, room_capacity, room_type, building_id, building_name, building_address, requester_user_id, requester_username, requester_email, requester_full_name, requester_role, requested_equipment_id, requested_equipment_name, requested_equipment_stock_quantity, requested_quantity, room_equipment_id, room_equipment_name, room_equipment_stock_quantity, room_equipment_quantity, approval_id, approver_user_id, approver_username, approver_email, approver_full_name, approver_role, decision, decision_time, note ) }}} 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. During normalization, these two equipment groups are merged into one final `equipment` relation. Similarly, requester and approver attributes both describe users. During normalization, these groups are merged into one final `users` relation. === Functional dependencies === The following functional dependencies are valid in the de-normalized relation. For readability, they are written in grouped form. Each grouped dependency can be split into dependencies with one attribute on the right-hand side. {{{ reservation_id -> reservation_date, start_time, end_time, status, room_id, requester_user_id room_id -> room_code, room_capacity, room_type, building_id room_code -> room_id, room_capacity, room_type, building_id building_id -> building_name, building_address requester_user_id -> requester_username, requester_email, requester_full_name, requester_role requester_username -> requester_user_id, requester_email, requester_full_name, requester_role requester_email -> requester_user_id, requester_username, requester_full_name, requester_role approver_user_id -> approver_username, approver_email, approver_full_name, approver_role approver_username -> approver_user_id, approver_email, approver_full_name, approver_role approver_email -> approver_user_id, approver_username, approver_full_name, approver_role requested_equipment_id -> requested_equipment_name, requested_equipment_stock_quantity room_equipment_id -> room_equipment_name, room_equipment_stock_quantity reservation_id, requested_equipment_id -> requested_quantity room_id, room_equipment_id -> room_equipment_quantity approval_id -> reservation_id, approver_user_id, decision, decision_time, note reservation_id -> approval_id, approver_user_id, decision, decision_time, note }}} The dependency: {{{ reservation_id -> approval_id, approver_user_id, decision, decision_time, note }}} 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. === Candidate key of the de-normalized relation === The de-normalized relation contains information about one reservation, one requested equipment item and one room-equipment item. A complete row in the de-normalized relation is therefore identified by: {{{ K = (reservation_id, requested_equipment_id, room_equipment_id) }}} Closure of the proposed key: {{{ {reservation_id, requested_equipment_id, room_equipment_id}+ }}} Using the functional dependencies: {{{ reservation_id -> reservation_date, start_time, end_time, status, room_id, requester_user_id room_id -> room_code, room_capacity, room_type, building_id building_id -> building_name, building_address requester_user_id -> requester_username, requester_email, requester_full_name, requester_role requested_equipment_id -> requested_equipment_name, requested_equipment_stock_quantity room_equipment_id -> room_equipment_name, room_equipment_stock_quantity reservation_id, requested_equipment_id -> requested_quantity room_id, room_equipment_id -> room_equipment_quantity reservation_id -> approval_id, approver_user_id, decision, decision_time, note approver_user_id -> approver_username, approver_email, approver_full_name, approver_role }}} we get: {{{ {reservation_id, requested_equipment_id, room_equipment_id}+ = { reservation_id, reservation_date, start_time, end_time, status, room_id, room_code, room_capacity, room_type, building_id, building_name, building_address, requester_user_id, requester_username, requester_email, requester_full_name, requester_role, requested_equipment_id, requested_equipment_name, requested_equipment_stock_quantity, requested_quantity, room_equipment_id, room_equipment_name, room_equipment_stock_quantity, room_equipment_quantity, approval_id, approver_user_id, approver_username, approver_email, approver_full_name, approver_role, decision, decision_time, note } }}} Therefore, the proposed key determines all attributes of R0. The key is minimal because: * `reservation_id` alone does not determine all requested equipment rows. * `requested_equipment_id` alone does not determine the reservation or room-equipment data. * `room_equipment_id` alone does not determine the reservation or requested-equipment data. * `(reservation_id, requested_equipment_id)` determines requested quantity, but does not determine room-equipment quantity. * `(reservation_id, room_equipment_id)` does not determine the requested equipment item. * `(requested_equipment_id, room_equipment_id)` does not determine the reservation. Therefore, the candidate key used for the de-normalized relation is: {{{ (reservation_id, requested_equipment_id, room_equipment_id) }}} The selected primary key for the de-normalized relation is the same candidate key: {{{ (reservation_id, requested_equipment_id, room_equipment_id) }}} This is only a theoretical primary key used for the normalization analysis. The de-normalized relation is not implemented in the database. === Normal form of the de-normalized relation === The de-normalized relation is in 1NF because all attributes contain atomic values. However, the relation is not in 2NF because there are partial dependencies on parts of the composite key. Examples: {{{ reservation_id -> reservation_date, start_time, end_time, status, room_id, requester_user_id requested_equipment_id -> requested_equipment_name, requested_equipment_stock_quantity room_equipment_id -> room_equipment_name, room_equipment_stock_quantity }}} 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. == 1NF Decomposition == First normal form requires atomic attribute values and no repeating groups inside a single attribute. 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. Therefore, no additional decomposition is required for 1NF. The relation after this step is still: {{{ R0( reservation_id, reservation_date, start_time, end_time, status, room_id, room_code, room_capacity, room_type, building_id, building_name, building_address, requester_user_id, requester_username, requester_email, requester_full_name, requester_role, requested_equipment_id, requested_equipment_name, requested_equipment_stock_quantity, requested_quantity, room_equipment_id, room_equipment_name, room_equipment_stock_quantity, room_equipment_quantity, approval_id, approver_user_id, approver_username, approver_email, approver_full_name, approver_role, decision, decision_time, note ) }}} The relation is in 1NF, but it is not in 2NF because of partial dependencies. == 2NF Decomposition == A relation is in 2NF if it is in 1NF and every non-prime attribute is fully functionally dependent on the whole candidate key. The relation R0 violates 2NF because many attributes depend only on part of the composite key. Examples: {{{ reservation_id -> reservation_date, start_time, end_time, status, room_id, requester_user_id requested_equipment_id -> requested_equipment_name, requested_equipment_stock_quantity room_equipment_id -> room_equipment_name, room_equipment_stock_quantity }}} The first decomposition removes these partial dependencies. === Reservation data === {{{ R1_reservations( reservation_id, reservation_date, start_time, end_time, status, room_id, requester_user_id ) }}} Functional dependency: {{{ reservation_id -> reservation_date, start_time, end_time, status, room_id, requester_user_id }}} Candidate key and selected primary key: {{{ reservation_id }}} === Room data === {{{ R1_rooms( room_id, room_code, room_capacity, room_type, building_id, building_name, building_address ) }}} Functional dependencies: {{{ room_id -> room_code, room_capacity, room_type, building_id, building_name, building_address room_code -> room_id, room_capacity, room_type, building_id, building_name, building_address }}} Candidate keys: {{{ room_id room_code }}} Selected primary key: {{{ room_id }}} === User data in requester role === {{{ R1_requester_users( requester_user_id, requester_username, requester_email, requester_full_name, requester_role ) }}} Functional dependencies: {{{ requester_user_id -> requester_username, requester_email, requester_full_name, requester_role requester_username -> requester_user_id, requester_email, requester_full_name, requester_role requester_email -> requester_user_id, requester_username, requester_full_name, requester_role }}} Candidate keys: {{{ requester_user_id requester_username requester_email }}} Selected primary key: {{{ requester_user_id }}} === User data in approver role === {{{ R1_approver_users( approver_user_id, approver_username, approver_email, approver_full_name, approver_role ) }}} Functional dependencies: {{{ approver_user_id -> approver_username, approver_email, approver_full_name, approver_role approver_username -> approver_user_id, approver_email, approver_full_name, approver_role approver_email -> approver_user_id, approver_username, approver_full_name, approver_role }}} Candidate keys: {{{ approver_user_id approver_username approver_email }}} Selected primary key: {{{ approver_user_id }}} === Requested equipment data === {{{ R1_requested_equipment( requested_equipment_id, requested_equipment_name, requested_equipment_stock_quantity ) }}} Functional dependency: {{{ requested_equipment_id -> requested_equipment_name, requested_equipment_stock_quantity }}} Candidate key and selected primary key: {{{ requested_equipment_id }}} === Room equipment item data === {{{ R1_room_equipment_item( room_equipment_id, room_equipment_name, room_equipment_stock_quantity ) }}} Functional dependency: {{{ room_equipment_id -> room_equipment_name, room_equipment_stock_quantity }}} Candidate key and selected primary key: {{{ room_equipment_id }}} === Reservation-equipment quantity === {{{ R1_reservation_equipment( reservation_id, requested_equipment_id, requested_quantity ) }}} Functional dependency: {{{ reservation_id, requested_equipment_id -> requested_quantity }}} Candidate key and selected primary key: {{{ (reservation_id, requested_equipment_id) }}} === Room-equipment quantity === {{{ R1_room_equipment( room_id, room_equipment_id, room_equipment_quantity ) }}} Functional dependency: {{{ room_id, room_equipment_id -> room_equipment_quantity }}} Candidate key and selected primary key: {{{ (room_id, room_equipment_id) }}} === Approval data === {{{ R1_approvals( approval_id, reservation_id, approver_user_id, decision, decision_time, note ) }}} Functional dependencies: {{{ approval_id -> reservation_id, approver_user_id, decision, decision_time, note reservation_id -> approval_id, approver_user_id, decision, decision_time, note }}} Candidate keys: {{{ approval_id reservation_id }}} Selected primary key: {{{ approval_id }}} === 2NF conclusion === After this decomposition, the partial dependencies from the original composite key are removed. 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. == 3NF Decomposition == A relation is in 3NF if it is in 2NF and no non-prime attribute depends transitively on a candidate key. The relation `R1_rooms` still contains a transitive dependency: {{{ room_id -> building_id building_id -> building_name, building_address }}} Therefore: {{{ room_id -> building_name, building_address }}} 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. === Decomposition of room and building data === The relation: {{{ R1_rooms( room_id, room_code, room_capacity, room_type, building_id, building_name, building_address ) }}} is decomposed into: {{{ buildings( building_id, name, address ) }}} Functional dependency: {{{ building_id -> name, address }}} Candidate key and selected primary key: {{{ building_id }}} and: {{{ rooms( room_id, building_id, room_code, capacity, type ) }}} Functional dependencies: {{{ room_id -> building_id, room_code, capacity, type room_code -> room_id, building_id, capacity, type }}} Candidate keys: {{{ room_id room_code }}} Selected primary key: {{{ room_id }}} This removes the transitive dependency between rooms and building attributes. === Merging user roles === The 2NF decomposition produced separate requester and approver user relations because the same entity type appeared in different roles in the de-normalized relation. However, both groups represent the same real entity set: users of the system. Therefore, they are merged into one relation: {{{ users( user_id, username, email, full_name, role ) }}} Functional dependencies: {{{ user_id -> username, email, full_name, role username -> user_id, email, full_name, role email -> user_id, username, full_name, role }}} Candidate keys: {{{ user_id username email }}} Selected primary key: {{{ user_id }}} In the final schema, `reservations.user_id` references the requester, while `approvals.approver_id` references the user who made the approval decision. === Merging equipment roles === The 2NF decomposition produced separate requested-equipment and room-equipment item relations because equipment appeared in two different roles in the de-normalized relation. However, both groups represent the same entity set: equipment types. Therefore, they are merged into one relation: {{{ equipment( equipment_id, name, stock_quantity ) }}} Functional dependency: {{{ equipment_id -> name, stock_quantity }}} Candidate key and selected primary key: {{{ equipment_id }}} The relation `room_equipment` represents equipment assigned to rooms, while `reservation_equipment` represents equipment requested as part of reservations. === Final relations after 3NF decomposition === After removing partial and transitive dependencies, the resulting relations are: {{{ buildings( building_id, name, address ) }}} {{{ rooms( room_id, building_id, room_code, capacity, type ) }}} {{{ equipment( equipment_id, name, stock_quantity ) }}} {{{ room_equipment( room_id, equipment_id, quantity ) }}} {{{ users( user_id, username, email, full_name, role ) }}} {{{ reservations( reservation_id, room_id, user_id, reservation_date, start_time, end_time, status ) }}} {{{ reservation_equipment( reservation_id, equipment_id, requested_quantity ) }}} {{{ approvals( approval_id, reservation_id, approver_id, decision, decision_time, note ) }}} {{{ user_credentials( user_id, password_hash, created_at, updated_at ) }}} 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`. == Formal Candidate Key and Primary Key Proof == This section formally documents the candidate keys and selected primary keys for the final normalized relations. The proof is based on attribute closure. For each relation, the selected key determines all attributes in the relation and is minimal. === Buildings === Relation: {{{ Buildings(building_id, name, address) }}} Functional dependency: {{{ building_id -> name, address }}} Closure: {{{ {building_id}+ = {building_id, name, address} }}} Therefore, `building_id` determines all attributes in the relation. Candidate key: {{{ building_id }}} Selected primary key: {{{ building_id }}} No other candidate key is used because the documented functional dependencies do not show that `name` or `address` can uniquely determine a building. === Rooms === Relation: {{{ Rooms(room_id, building_id, room_code, capacity, type) }}} Functional dependencies: {{{ room_id -> building_id, room_code, capacity, type room_code -> room_id, building_id, capacity, type }}} Closure of `room_id`: {{{ {room_id}+ = {room_id, building_id, room_code, capacity, type} }}} Closure of `room_code`: {{{ {room_code}+ = {room_code, room_id, building_id, capacity, type} }}} Candidate keys: {{{ room_id room_code }}} Selected primary key: {{{ room_id }}} `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. No other candidate keys exist because `building_id`, `capacity` and `type` do not uniquely identify a room. === Equipment === Relation: {{{ Equipment(equipment_id, name, stock_quantity) }}} Functional dependency: {{{ equipment_id -> name, stock_quantity }}} Closure: {{{ {equipment_id}+ = {equipment_id, name, stock_quantity} }}} Candidate key: {{{ equipment_id }}} Selected primary key: {{{ equipment_id }}} No other candidate key is used because the documented dependencies do not require equipment name to uniquely determine the full equipment record. === RoomEquipment === Relation: {{{ RoomEquipment(room_id, equipment_id, quantity) }}} Functional dependency: {{{ room_id, equipment_id -> quantity }}} Closure: {{{ {room_id, equipment_id}+ = {room_id, equipment_id, quantity} }}} Candidate key: {{{ (room_id, equipment_id) }}} Selected primary key: {{{ (room_id, equipment_id) }}} The key is minimal because: * `room_id` alone does not determine the quantity, since a room can contain multiple equipment types. * `equipment_id` alone does not determine the quantity, since the same equipment type can exist in multiple rooms. Therefore, both attributes are required. === Users === Relation: {{{ Users(user_id, username, email, full_name, role) }}} Functional dependencies: {{{ user_id -> username, email, full_name, role username -> user_id, email, full_name, role email -> user_id, username, full_name, role }}} Closure of `user_id`: {{{ {user_id}+ = {user_id, username, email, full_name, role} }}} Closure of `username`: {{{ {username}+ = {username, user_id, email, full_name, role} }}} Closure of `email`: {{{ {email}+ = {email, user_id, username, full_name, role} }}} Candidate keys: {{{ user_id username email }}} Selected primary key: {{{ user_id }}} `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. No other candidate key exists because `full_name` and `role` do not uniquely identify a user. === Reservations === Relation: {{{ Reservations(reservation_id, room_id, user_id, reservation_date, start_time, end_time, status) }}} Functional dependency: {{{ reservation_id -> room_id, user_id, reservation_date, start_time, end_time, status }}} Closure: {{{ {reservation_id}+ = {reservation_id, room_id, user_id, reservation_date, start_time, end_time, status} }}} Candidate key: {{{ reservation_id }}} Selected primary key: {{{ reservation_id }}} 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. === ReservationEquipment === Relation: {{{ ReservationEquipment(reservation_id, equipment_id, requested_quantity) }}} Functional dependency: {{{ reservation_id, equipment_id -> requested_quantity }}} Closure: {{{ {reservation_id, equipment_id}+ = {reservation_id, equipment_id, requested_quantity} }}} Candidate key: {{{ (reservation_id, equipment_id) }}} Selected primary key: {{{ (reservation_id, equipment_id) }}} The key is minimal because: * `reservation_id` alone does not determine the requested quantity for one equipment item, since a reservation can request multiple equipment types. * `equipment_id` alone does not determine the requested quantity, since the same equipment type can be requested in many reservations. Therefore, both attributes are required. === Approvals === Relation: {{{ Approvals(approval_id, reservation_id, approver_id, decision, decision_time, note) }}} Functional dependencies: {{{ approval_id -> reservation_id, approver_id, decision, decision_time, note reservation_id -> approval_id, approver_id, decision, decision_time, note }}} Closure of `approval_id`: {{{ {approval_id}+ = {approval_id, reservation_id, approver_id, decision, decision_time, note} }}} Closure of `reservation_id`: {{{ {reservation_id}+ = {reservation_id, approval_id, approver_id, decision, decision_time, note} }}} Candidate keys: {{{ approval_id reservation_id }}} Selected primary key: {{{ approval_id }}} `reservation_id` is an alternate candidate key because the design stores at most one final approval decision for each reservation. === UserCredentials === Relation: {{{ UserCredentials(user_id, password_hash, created_at, updated_at) }}} Functional dependency: {{{ user_id -> password_hash, created_at, updated_at }}} Closure: {{{ {user_id}+ = {user_id, password_hash, created_at, updated_at} }}} Candidate key: {{{ user_id }}} Selected primary key: {{{ user_id }}} 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. == Formal Lossless-Join Proof == This section proves that the decompositions used in the normalized schema are lossless. For a binary decomposition of a relation R into R1 and R2, the decomposition is lossless if: {{{ R1 ∩ R2 -> R1 }}} or: {{{ R1 ∩ R2 -> R2 }}} In other words, the common attributes between the two decomposed relations must functionally determine all attributes of at least one of the decomposed relations. === Buildings and Rooms === Decomposition: {{{ Buildings(building_id, name, address) Rooms(room_id, building_id, room_code, capacity, type) }}} Common attribute: {{{ Buildings ∩ Rooms = {building_id} }}} Functional dependency: {{{ building_id -> name, address }}} Since `building_id` determines all attributes of `Buildings`, the common attribute determines the `Buildings` relation. Therefore: {{{ {building_id} -> Buildings }}} The decomposition between `Buildings` and `Rooms` is lossless. === Rooms and Reservations === Decomposition: {{{ Rooms(room_id, building_id, room_code, capacity, type) Reservations(reservation_id, room_id, user_id, reservation_date, start_time, end_time, status) }}} Common attribute: {{{ Rooms ∩ Reservations = {room_id} }}} Functional dependency: {{{ room_id -> building_id, room_code, capacity, type }}} Since `room_id` determines all attributes of `Rooms`, the common attribute determines the `Rooms` relation. Therefore: {{{ {room_id} -> Rooms }}} The decomposition between `Rooms` and `Reservations` is lossless for room-based reservations. The design also supports equipment-only reservations where `room_id` may be null. Equipment-only reservation resources are represented separately through `ReservationEquipment`. === Users and Reservations === Decomposition: {{{ Users(user_id, username, email, full_name, role) Reservations(reservation_id, room_id, user_id, reservation_date, start_time, end_time, status) }}} Common attribute: {{{ Users ∩ Reservations = {user_id} }}} Functional dependency: {{{ user_id -> username, email, full_name, role }}} Since `user_id` determines all attributes of `Users`, the common attribute determines the `Users` relation. Therefore: {{{ {user_id} -> Users }}} The decomposition between `Users` and `Reservations` is lossless. === Rooms and RoomEquipment === Decomposition: {{{ Rooms(room_id, building_id, room_code, capacity, type) RoomEquipment(room_id, equipment_id, quantity) }}} Common attribute: {{{ Rooms ∩ RoomEquipment = {room_id} }}} Functional dependency: {{{ room_id -> building_id, room_code, capacity, type }}} Since `room_id` determines all attributes of `Rooms`, the common attribute determines the `Rooms` relation. Therefore: {{{ {room_id} -> Rooms }}} The decomposition between `Rooms` and `RoomEquipment` is lossless. === Equipment and RoomEquipment === Decomposition: {{{ Equipment(equipment_id, name, stock_quantity) RoomEquipment(room_id, equipment_id, quantity) }}} Common attribute: {{{ Equipment ∩ RoomEquipment = {equipment_id} }}} Functional dependency: {{{ equipment_id -> name, stock_quantity }}} Since `equipment_id` determines all attributes of `Equipment`, the common attribute determines the `Equipment` relation. Therefore: {{{ {equipment_id} -> Equipment }}} The decomposition between `Equipment` and `RoomEquipment` is lossless. === Reservations and ReservationEquipment === Decomposition: {{{ Reservations(reservation_id, room_id, user_id, reservation_date, start_time, end_time, status) ReservationEquipment(reservation_id, equipment_id, requested_quantity) }}} Common attribute: {{{ Reservations ∩ ReservationEquipment = {reservation_id} }}} Functional dependency: {{{ reservation_id -> room_id, user_id, reservation_date, start_time, end_time, status }}} Since `reservation_id` determines all attributes of `Reservations`, the common attribute determines the `Reservations` relation. Therefore: {{{ {reservation_id} -> Reservations }}} The decomposition between `Reservations` and `ReservationEquipment` is lossless. === Equipment and ReservationEquipment === Decomposition: {{{ Equipment(equipment_id, name, stock_quantity) ReservationEquipment(reservation_id, equipment_id, requested_quantity) }}} Common attribute: {{{ Equipment ∩ ReservationEquipment = {equipment_id} }}} Functional dependency: {{{ equipment_id -> name, stock_quantity }}} Since `equipment_id` determines all attributes of `Equipment`, the common attribute determines the `Equipment` relation. Therefore: {{{ {equipment_id} -> Equipment }}} The decomposition between `Equipment` and `ReservationEquipment` is lossless. === Reservations and Approvals === Decomposition: {{{ Reservations(reservation_id, room_id, user_id, reservation_date, start_time, end_time, status) Approvals(approval_id, reservation_id, approver_id, decision, decision_time, note) }}} Common attribute: {{{ Reservations ∩ Approvals = {reservation_id} }}} Functional dependency: {{{ reservation_id -> room_id, user_id, reservation_date, start_time, end_time, status }}} Since `reservation_id` determines all attributes of `Reservations`, the common attribute determines the `Reservations` relation. Therefore: {{{ {reservation_id} -> Reservations }}} The decomposition between `Reservations` and `Approvals` is lossless. === Users and Approvals === The approval relation stores the approver using `approver_id`, which references `Users(user_id)`. Decomposition: {{{ Users(user_id, username, email, full_name, role) Approvals(approval_id, reservation_id, approver_id, decision, decision_time, note) }}} Relationship attribute: {{{ approver_id references user_id }}} Functional dependency in `Users`: {{{ user_id -> username, email, full_name, role }}} After matching `approver_id` with `user_id`, the referenced user identifier determines all attributes of the corresponding user. Therefore, the decomposition between approver data and approval data is lossless. === Users and UserCredentials === Decomposition: {{{ Users(user_id, username, email, full_name, role) UserCredentials(user_id, password_hash, created_at, updated_at) }}} Common attribute: {{{ Users ∩ UserCredentials = {user_id} }}} Functional dependencies: {{{ user_id -> username, email, full_name, role user_id -> password_hash, created_at, updated_at }}} Since `user_id` determines all attributes in both `Users` and `UserCredentials`, the decomposition is lossless. This also preserves the intended one-to-one relationship between a user profile and user credentials. == BCNF Check == A relation is in BCNF if for every non-trivial functional dependency X -> Y, X is a superkey. The final relations are checked for BCNF as follows. === buildings === Relation: {{{ buildings(building_id, name, address) }}} Functional dependency: {{{ building_id -> name, address }}} Candidate key: {{{ building_id }}} The determinant is a candidate key, so the relation is in BCNF. === rooms === Relation: {{{ rooms(room_id, building_id, room_code, capacity, type) }}} Functional dependencies: {{{ room_id -> building_id, room_code, capacity, type room_code -> room_id, building_id, capacity, type }}} Candidate keys: {{{ room_id room_code }}} All determinants are candidate keys, so the relation is in BCNF. === equipment === Relation: {{{ equipment(equipment_id, name, stock_quantity) }}} Functional dependency: {{{ equipment_id -> name, stock_quantity }}} Candidate key: {{{ equipment_id }}} The determinant is a candidate key, so the relation is in BCNF. === room_equipment === Relation: {{{ room_equipment(room_id, equipment_id, quantity) }}} Functional dependency: {{{ room_id, equipment_id -> quantity }}} Candidate key: {{{ (room_id, equipment_id) }}} The determinant is the candidate key, so the relation is in BCNF. === users === Relation: {{{ users(user_id, username, email, full_name, role) }}} Functional dependencies: {{{ user_id -> username, email, full_name, role username -> user_id, email, full_name, role email -> user_id, username, full_name, role }}} Candidate keys: {{{ user_id username email }}} All determinants are candidate keys, so the relation is in BCNF. === reservations === Relation: {{{ reservations(reservation_id, room_id, user_id, reservation_date, start_time, end_time, status) }}} Functional dependency: {{{ reservation_id -> room_id, user_id, reservation_date, start_time, end_time, status }}} Candidate key: {{{ reservation_id }}} The determinant is the candidate key, so the relation is in BCNF. 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. === reservation_equipment === Relation: {{{ reservation_equipment(reservation_id, equipment_id, requested_quantity) }}} Functional dependency: {{{ reservation_id, equipment_id -> requested_quantity }}} Candidate key: {{{ (reservation_id, equipment_id) }}} The determinant is the candidate key, so the relation is in BCNF. === approvals === Relation: {{{ approvals(approval_id, reservation_id, approver_id, decision, decision_time, note) }}} Functional dependencies: {{{ approval_id -> reservation_id, approver_id, decision, decision_time, note reservation_id -> approval_id, approver_id, decision, decision_time, note }}} Candidate keys: {{{ approval_id reservation_id }}} All determinants are candidate keys, so the relation is in BCNF. === user_credentials === Relation: {{{ user_credentials(user_id, password_hash, created_at, updated_at) }}} Functional dependency: {{{ user_id -> password_hash, created_at, updated_at }}} Candidate key: {{{ user_id }}} The determinant is the candidate key, so the relation is in BCNF. == Final Result and Discussion == === Normalized relational model === The final normalized relational model is: {{{ buildings( building_id PK, name, address ) }}} {{{ rooms( room_id PK, building_id FK, room_code AK, capacity, type ) }}} {{{ equipment( equipment_id PK, name, stock_quantity ) }}} {{{ room_equipment( room_id PK, FK, equipment_id PK, FK, quantity ) }}} {{{ users( user_id PK, username AK, email AK, full_name, role ) }}} {{{ reservations( reservation_id PK, room_id FK, user_id FK, reservation_date, start_time, end_time, status ) }}} {{{ reservation_equipment( reservation_id PK, FK, equipment_id PK, FK, requested_quantity ) }}} {{{ approvals( approval_id PK, reservation_id AK, FK, approver_id FK, decision, decision_time, note ) }}} {{{ user_credentials( user_id PK, FK, password_hash, created_at, updated_at ) }}} === Discussion === 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. The decomposition removed partial dependencies, then removed transitive dependencies, and finally checked the resulting relations for BCNF. The final normalized design contains separate relations for: * buildings * rooms * equipment * room_equipment * users * reservations * reservation_equipment * approvals * user_credentials The M:N relationship between rooms and equipment is represented by: {{{ room_equipment(room_id, equipment_id, quantity) }}} The M:N relationship between reservations and requested equipment is represented by: {{{ reservation_equipment(reservation_id, equipment_id, requested_quantity) }}} This prevents repeated data and allows equipment to be managed independently from both rooms and reservations. The authentication data is separated into: {{{ user_credentials(user_id, password_hash, created_at, updated_at) }}} This avoids storing password hashes directly in the `users` relation and keeps authentication details separate from general user profile data. === Comparison with Phase P2 === The final normalized model obtained in this phase is consistent with the relational design from Phase P2. The same main relations are used: * buildings * rooms * equipment * room_equipment * users * reservations * reservation_equipment * approvals 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. 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. == Conclusion == The formal normalization process shows that the final relational model is in BCNF. The documentation now includes: * formal candidate key proof using attribute closure * selected primary keys for every final relation * explanation why the selected keys are minimal * formal lossless-join proof for the decompositions * BCNF verification for every final relation The decomposition preserves the important functional dependencies, has lossless-join properties and matches the implemented relational schema. Therefore, the current database design is appropriate for the final Room Reservation System implementation.