wiki:Normalization

Version 2 (modified by 223091, 7 days ago) ( diff )

--

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.

Note: See TracWiki for help on using the wiki.