Normalization
De-normalized Database Form
The normalization process starts from a single universal relation U that contains all attributes from the ER model, as if all data resided in one table. Attribute names are made globally unique by prefixing where necessary.
U ( reservation_id, start_time, end_time, status, purpose, created_at, recurrence_group_id,
user_id, first_name, last_name, email, password, user_type_id, user_type_name, user_type_description,
approved_by,
resource_id, resource_name, resource_description, available_from, available_to, available_weekends,
resource_type_id, resource_type_name, is_physical,
location_id, building, room )
The relation has 27 attributes total, representing reservation data, user data, resource data, and their associated types and locations.
Functional Dependencies
Initial Set of Functional Dependencies
The following functional dependencies are derived from the semantics of the domain:
| ID | Functional Dependency | Source |
| FD1 | user_type_id -> user_type_name, user_type_description | UserType entity; type_id uniquely identifies each user type |
| FD2 | user_type_name -> user_type_id, user_type_description | UserType entity; type_name is also unique (UNIQUE constraint) |
| FD3 | user_id -> first_name, last_name, email, password, user_type_id | User entity; user_id uniquely identifies each user |
| FD4 | email -> user_id, first_name, last_name, password, user_type_id | User entity; email is also unique (UNIQUE constraint) |
| FD5 | resource_type_id -> resource_type_name, is_physical | ResourceType entity; type_id uniquely identifies each resource type |
| FD6 | resource_type_name -> resource_type_id, is_physical | ResourceType entity; type_name is also unique (UNIQUE constraint) |
| FD7 | location_id -> building, room | Location entity; location_id uniquely identifies each location |
| FD8 | {building, room} -> location_id | Location entity; building+room combination is unique (composite UNIQUE constraint) |
| FD9 | resource_id -> resource_name, resource_description, available_from, available_to, available_weekends, resource_type_id, location_id | Resource entity; resource_id uniquely identifies each resource |
| FD10 | reservation_id -> start_time, end_time, status, purpose, created_at, recurrence_group_id, user_id, approved_by, resource_id | Reservation entity; reservation_id uniquely identifies each reservation |
Canonical Cover
To obtain the canonical cover, we apply the following reductions:
- Reduce FD2: Since user_type_name -> user_type_id (FD2) and user_type_id -> user_type_description (FD1), the attribute user_type_description is derivable by transitivity. Reduced to: user_type_name -> user_type_id
- Reduce FD4: Since email -> user_id (FD4) and user_id -> first_name, last_name, password, user_type_id (FD3), all attributes except user_id are derivable by transitivity. Reduced to: email -> user_id
- Reduce FD6: Since resource_type_name -> resource_type_id (FD6) and resource_type_id -> is_physical (FD5), the attribute is_physical is derivable by transitivity. Reduced to: resource_type_name -> resource_type_id
No further reductions are possible (no extraneous left-hand-side attributes, no redundant FDs).
Canonical Cover Fc:
| ID | Functional Dependency |
| FD1 | user_type_id -> user_type_name, user_type_description |
| FD2' | user_type_name -> user_type_id |
| FD3 | user_id -> first_name, last_name, email, password, user_type_id |
| FD4' | email -> user_id |
| FD5 | resource_type_id -> resource_type_name, is_physical |
| FD6' | resource_type_name -> resource_type_id |
| FD7 | location_id -> building, room |
| FD8 | {building, room} -> location_id |
| FD9 | resource_id -> resource_name, resource_description, available_from, available_to, available_weekends, resource_type_id, location_id |
| FD10 | reservation_id -> start_time, end_time, status, purpose, created_at, recurrence_group_id, user_id, approved_by, resource_id |
Candidate Keys and Primary Key
To find candidate keys of U, we compute attribute closures.
Attribute Classification (Left / Right Side)
| Attribute | Left Side | Right Side | Classification |
| reservation_id | Yes (FD10) | No | Left only |
| start_time | No | Yes (FD10) | Right only |
| end_time | No | Yes (FD10) | Right only |
| status | No | Yes (FD10) | Right only |
| purpose | No | Yes (FD10) | Right only |
| created_at | No | Yes (FD10) | Right only |
| recurrence_group_id | No | Yes (FD10) | Right only |
| user_id | Yes (FD3) | Yes (FD4', FD10) | Both |
| first_name | No | Yes (FD3) | Right only |
| last_name | No | Yes (FD3) | Right only |
| Yes (FD4') | Yes (FD3) | Both | |
| password | No | Yes (FD3) | Right only |
| user_type_id | Yes (FD1) | Yes (FD2', FD3) | Both |
| user_type_name | Yes (FD2') | Yes (FD1) | Both |
| user_type_description | No | Yes (FD1) | Right only |
| approved_by | No | Yes (FD10) | Right only |
| resource_id | Yes (FD9) | Yes (FD10) | Both |
| resource_name | No | Yes (FD9) | Right only |
| resource_description | No | Yes (FD9) | Right only |
| available_from | No | Yes (FD9) | Right only |
| available_to | No | Yes (FD9) | Right only |
| available_weekends | No | Yes (FD9) | Right only |
| resource_type_id | Yes (FD5) | Yes (FD6', FD9) | Both |
| resource_type_name | Yes (FD6') | Yes (FD5) | Both |
| is_physical | No | Yes (FD5) | Right only |
| location_id | Yes (FD7) | Yes (FD8, FD9) | Both |
| building | Yes (FD8) | Yes (FD7) | Both |
| room | Yes (FD8) | Yes (FD7) | Both |
Attributes appearing ONLY on the left side: reservation_id
This attribute must be part of every candidate key.
Closure Computation
{reservation_id}+
= {reservation_id}
apply FD10: + {start_time, end_time, status, purpose, created_at, recurrence_group_id, user_id, approved_by, resource_id}
apply FD3: + {first_name, last_name, email, password, user_type_id}
apply FD1: + {user_type_name, user_type_description}
apply FD9: + {resource_name, resource_description, available_from, available_to, available_weekends, resource_type_id, location_id}
apply FD5: + {resource_type_name, is_physical}
apply FD7: + {building, room}
= U (all 27 attributes)
Since {reservation_id}+ = U, reservation_id is a superkey. It is a single attribute, so it is minimal, therefore it is a candidate key.
Since reservation_id is the only attribute appearing exclusively on the left side of all FDs, and its closure already covers U, no other minimal set of attributes can form a candidate key. Therefore, {reservation_id} is the only candidate key.
Primary key selected: reservation_id
Prime attribute: reservation_id
Non-prime attributes: all remaining 26 attributes
1NF Decomposition
For a relation to be in 1NF, all attribute values must be atomic (no repeating groups, no multi-valued attributes).
Check: All 27 attributes of U have atomic domains:
- VARCHAR for text attributes (first_name, last_name, email, etc.)
- INTEGER for identifiers (user_id, resource_id, etc.)
- BOOLEAN for flags (is_physical, available_weekends)
- TIMESTAMP for date/time values (start_time, end_time, created_at)
- TIME for daily time values (available_from, available_to)
- UUID for group identifiers (recurrence_group_id)
There are no multi-valued or composite attributes. U is already in 1NF. No decomposition is needed at this stage.
2NF Decomposition
For a relation to be in 2NF, it must be in 1NF and have no partial dependencies (no non-prime attribute may depend on a proper subset of a candidate key).
Check: The only candidate key of U is {reservation_id}, which is a single attribute. A single-attribute key has no proper subsets, therefore partial dependencies are impossible by definition.
U is already in 2NF. No decomposition is needed at this stage.
3NF Decomposition
For a relation to be in 3NF, it must be in 2NF and for every non-trivial FD X -> A: either X is a superkey, or A is a prime attribute.
Check: The following FDs violate 3NF because their left-hand sides are not superkeys and their right-hand sides contain only non-prime attributes:
| FD | Violation | Transitive Chain |
| FD1: user_type_id -> user_type_name, user_type_description | user_type_id is not a superkey | reservation_id -> user_id -> user_type_id -> {user_type_name, user_type_description} |
| FD3: user_id -> first_name, last_name, email, password, user_type_id | user_id is not a superkey | reservation_id -> user_id -> {first_name, last_name, email, password, user_type_id} |
| FD5: resource_type_id -> resource_type_name, is_physical | resource_type_id is not a superkey | reservation_id -> resource_id -> resource_type_id -> {resource_type_name, is_physical} |
| FD7: location_id -> building, room | location_id is not a superkey | reservation_id -> resource_id -> location_id -> {building, room} |
| FD9: resource_id -> resource_name, ... | resource_id is not a superkey | reservation_id -> resource_id -> {resource_name, ...} |
U is NOT in 3NF. We proceed with step-by-step decomposition, starting from the deepest transitive dependency chains.
Step 1: Decompose UserTypes from U
Relation analyzed: U (27 attributes, key: {reservation_id})
FDs that apply: FD1, FD2', FD3, FD4', FD5, FD6', FD7, FD8, FD9, FD10
Violating FD selected: FD1: user_type_id -> user_type_name, user_type_description
user_type_id is not a superkey of U, and user_type_name, user_type_description are non-prime. This is a transitive dependency: reservation_id -> user_id -> user_type_id -> {user_type_name, user_type_description}.
Decomposition:
- R1(user_type_id, user_type_name, user_type_description)
- FDs in R1: user_type_id -> user_type_name, user_type_description; user_type_name -> user_type_id
- Candidate keys: {user_type_id}, {user_type_name}
- Primary key: user_type_id
- Normal form: BCNF (all FD determinants are superkeys)
- U1 = U - {user_type_name, user_type_description}, keeping user_type_id
U1 ( reservation_id, start_time, end_time, status, purpose, created_at, recurrence_group_id, user_id, first_name, last_name, email, password, user_type_id, approved_by, resource_id, resource_name, resource_description, available_from, available_to, available_weekends, resource_type_id, resource_type_name, is_physical, location_id, building, room )- Key of U1: {reservation_id}
Lossless join check: R1 ∩ U1 = {user_type_id}, which is a candidate key of R1. Lossless join preserved. ✓
Dependency preservation: FD1 and FD2' are preserved in R1; all other FDs remain in U1. ✓
Step 2: Decompose Users from U1
Relation analyzed: U1 (25 attributes, key: {reservation_id})
FDs that apply: FD3, FD4', FD5, FD6', FD7, FD8, FD9, FD10
Violating FD selected: FD3: user_id -> first_name, last_name, email, password, user_type_id
user_id is not a superkey of U1, and all RHS attributes are non-prime. This is a transitive dependency: reservation_id -> user_id -> {first_name, last_name, email, password, user_type_id}.
Decomposition:
- R2(user_id, first_name, last_name, email, password, user_type_id)
- FDs in R2: user_id -> first_name, last_name, email, password, user_type_id; email -> user_id
- Candidate keys: {user_id}, {email} (since {email}+ = {email, user_id, first_name, last_name, password, user_type_id} = R2)
- Primary key: user_id
- Normal form: BCNF (both user_id and email are superkeys)
- U2 = U1 - {first_name, last_name, email, password, user_type_id}, keeping user_id
U2 ( reservation_id, start_time, end_time, status, purpose, created_at, recurrence_group_id, user_id, approved_by, resource_id, resource_name, resource_description, available_from, available_to, available_weekends, resource_type_id, resource_type_name, is_physical, location_id, building, room )- Key of U2: {reservation_id}
Lossless join check: R2 ∩ U2 = {user_id}, which is a candidate key of R2. Lossless join preserved. ✓
Dependency preservation: FD3 and FD4' are preserved in R2; all other FDs remain in U2. ✓
Step 3: Decompose ResourceTypes from U2
Relation analyzed: U2 (20 attributes, key: {reservation_id})
FDs that apply: FD5, FD6', FD7, FD8, FD9, FD10
Violating FD selected: FD5: resource_type_id -> resource_type_name, is_physical
resource_type_id is not a superkey of U2. This is a transitive dependency: reservation_id -> resource_id -> resource_type_id -> {resource_type_name, is_physical}.
Decomposition:
- R3(resource_type_id, resource_type_name, is_physical)
- FDs in R3: resource_type_id -> resource_type_name, is_physical; resource_type_name -> resource_type_id
- Candidate keys: {resource_type_id}, {resource_type_name}
- Primary key: resource_type_id
- Normal form: BCNF (all FD determinants are superkeys)
- U3 = U2 - {resource_type_name, is_physical}, keeping resource_type_id
U3 ( reservation_id, start_time, end_time, status, purpose, created_at, recurrence_group_id, user_id, approved_by, resource_id, resource_name, resource_description, available_from, available_to, available_weekends, resource_type_id, location_id, building, room )- Key of U3: {reservation_id}
Lossless join check: R3 ∩ U3 = {resource_type_id}, which is a candidate key of R3. Lossless join preserved. ✓
Dependency preservation: FD5 and FD6' are preserved in R3; all other FDs remain in U3. ✓
Step 4: Decompose Locations from U3
Relation analyzed: U3 (18 attributes, key: {reservation_id})
FDs that apply: FD7, FD8, FD9, FD10
Violating FD selected: FD7: location_id -> building, room
location_id is not a superkey of U3. This is a transitive dependency: reservation_id -> resource_id -> location_id -> {building, room}.
Decomposition:
- R4(location_id, building, room)
- FDs in R4: location_id -> building, room; {building, room} -> location_id
- Candidate keys: {location_id}, {building, room}
- Primary key: location_id
- Normal form: BCNF (all FD determinants are superkeys)
- U4 = U3 - {building, room}, keeping location_id
U4 ( reservation_id, start_time, end_time, status, purpose, created_at, recurrence_group_id, user_id, approved_by, resource_id, resource_name, resource_description, available_from, available_to, available_weekends, resource_type_id, location_id )- Key of U4: {reservation_id}
Lossless join check: R4 ∩ U4 = {location_id}, which is a candidate key of R4. Lossless join preserved. ✓
Dependency preservation: FD7 and FD8 are preserved in R4; all other FDs remain in U4. ✓
Step 5: Decompose Resources from U4
Relation analyzed: U4 (16 attributes, key: {reservation_id})
FDs that apply: FD9, FD10
Violating FD selected: FD9: resource_id -> resource_name, resource_description, available_from, available_to, available_weekends, resource_type_id, location_id
resource_id is not a superkey of U4. This is a transitive dependency: reservation_id -> resource_id -> {resource_name, resource_description, ...}.
Decomposition:
- R5(resource_id, resource_name, resource_description, available_from, available_to, available_weekends, resource_type_id, location_id)
- FDs in R5: resource_id -> resource_name, resource_description, available_from, available_to, available_weekends, resource_type_id, location_id
- Candidate key: {resource_id}
- Primary key: resource_id
- Normal form: BCNF (the only FD has superkey determinant)
- U5 = U4 - {resource_name, resource_description, available_from, available_to, available_weekends, resource_type_id, location_id}, keeping resource_id
U5 ( reservation_id, start_time, end_time, status, purpose, created_at, recurrence_group_id, user_id, approved_by, resource_id )- Key of U5: {reservation_id}
Lossless join check: R5 ∩ U5 = {resource_id}, which is a candidate key of R5. Lossless join preserved. ✓
Dependency preservation: FD9 is preserved in R5; FD10 remains in U5. ✓
Step 6: Verify U5 (Reservations)
Relation analyzed: U5 (10 attributes)
FDs in U5: FD10: reservation_id -> start_time, end_time, status, purpose, created_at, recurrence_group_id, user_id, approved_by, resource_id
Candidate key: {reservation_id}
Primary key: reservation_id
Every FD in U5 has a superkey (reservation_id) on the left-hand side. There are no transitive dependencies. U5 is in 3NF. ✓
3NF Result
After decomposition, we have six relations, all in 3NF:
| Relation | Attributes | Candidate Keys | Primary Key |
| R1 | user_type_id, user_type_name, user_type_description | {user_type_id}, {user_type_name} | user_type_id |
| R2 | user_id, first_name, last_name, email, password, user_type_id | {user_id}, {email} | user_id |
| R3 | resource_type_id, resource_type_name, is_physical | {resource_type_id}, {resource_type_name} | resource_type_id |
| R4 | location_id, building, room | {location_id}, {building, room} | location_id |
| R5 | resource_id, resource_name, resource_description, available_from, available_to, available_weekends, resource_type_id, location_id | {resource_id} | resource_id |
| R6 | reservation_id, start_time, end_time, status, purpose, created_at, recurrence_group_id, user_id, approved_by, resource_id | {reservation_id} | reservation_id |
All functional dependencies from Fc are preserved:
| FD | Preserved in |
| FD1: user_type_id -> user_type_name, user_type_description | R1 |
| FD2': user_type_name -> user_type_id | R1 |
| FD3: user_id -> first_name, last_name, email, password, user_type_id | R2 |
| FD4': email -> user_id | R2 |
| FD5: resource_type_id -> resource_type_name, is_physical | R3 |
| FD6': resource_type_name -> resource_type_id | R3 |
| FD7: location_id -> building, room | R4 |
| FD8: {building, room} -> location_id | R4 |
| FD9: resource_id -> resource_name, ... | R5 |
| FD10: reservation_id -> start_time, ... | R6 |
BCNF Verification
A relation is in BCNF if for every non-trivial FD X -> A, X is a superkey. We verify each relation from the 3NF result.
R1 (UserTypes)
FDs: user_type_id -> user_type_name, user_type_description; user_type_name -> user_type_id
Candidate keys: {user_type_id}, {user_type_name}
| FD | Determinant | Superkey? |
| user_type_id -> user_type_name, user_type_description | user_type_id | Yes |
| user_type_name -> user_type_id | user_type_name | Yes |
R1 is in BCNF. ✓
R2 (Users)
FDs: user_id -> first_name, last_name, email, password, user_type_id; email -> user_id
Candidate keys: {user_id}, {email} (verified: {email}+ = {email, user_id, first_name, last_name, password, user_type_id} = R2)
| FD | Determinant | Superkey? |
| user_id -> first_name, last_name, email, password, user_type_id | user_id | Yes |
| email -> user_id | Yes |
R2 is in BCNF. ✓
R3 (ResourceTypes)
FDs: resource_type_id -> resource_type_name, is_physical; resource_type_name -> resource_type_id
Candidate keys: {resource_type_id}, {resource_type_name}
| FD | Determinant | Superkey? |
| resource_type_id -> resource_type_name, is_physical | resource_type_id | Yes |
| resource_type_name -> resource_type_id | resource_type_name | Yes |
R3 is in BCNF. ✓
R4 (Locations)
FDs: location_id -> building, room; {building, room} -> location_id
Candidate keys: {location_id}, {building, room}
| FD | Determinant | Superkey? |
| location_id -> building, room | location_id | Yes |
| {building, room} -> location_id | {building, room} | Yes |
R4 is in BCNF. ✓
R5 (Resources)
FDs: resource_id -> resource_name, resource_description, available_from, available_to, available_weekends, resource_type_id, location_id
Candidate keys: {resource_id}
| FD | Determinant | Superkey? |
| resource_id -> resource_name, resource_description, ... | resource_id | Yes |
R5 is in BCNF. ✓
R6 (Reservations)
FDs: reservation_id -> start_time, end_time, status, purpose, created_at, recurrence_group_id, user_id, approved_by, resource_id
Candidate keys: {reservation_id}
| FD | Determinant | Superkey? |
| reservation_id -> start_time, end_time, ... | reservation_id | Yes |
R6 is in BCNF. ✓
Conclusion: All six relations are in BCNF. No further decomposition is needed. The decomposition preserves all functional dependencies and maintains the lossless join property.
Final Result and Discussion
Normalized Relational Model
The normalization process yields the following six relations, all in BCNF:
- UserTypes (user_type_id PK, user_type_name UNIQUE NOT NULL, user_type_description NOT NULL)
- Users (user_id PK, first_name NOT NULL, last_name NOT NULL, email UNIQUE NOT NULL, password NOT NULL, user_type_id NOT NULL FK->UserTypes)
- ResourceTypes (resource_type_id PK, resource_type_name UNIQUE NOT NULL, is_physical NOT NULL)
- Locations (location_id PK, building NOT NULL, room NOT NULL, UNIQUE(building, room))
- Resources (resource_id PK, resource_name NOT NULL, resource_description NOT NULL, available_from NOT NULL, available_to NOT NULL, available_weekends NOT NULL, resource_type_id NOT NULL FK->ResourceTypes, location_id FK->Locations)
- Reservations (reservation_id PK, start_time NOT NULL, end_time NOT NULL, status NOT NULL, purpose NOT NULL, created_at NOT NULL, recurrence_group_id, user_id NOT NULL FK->Users, approved_by FK->Users, resource_id NOT NULL FK->Resources)
Discussion
The normalization process, starting from the universal de-normalized relation and proceeding through formal decomposition, produces exactly the same six relations as the Phase 2 relational design. This confirms that the original ER model (Phase 1) and its relational mapping (Phase 2) were already well-normalized.
The key reasons for this outcome:
- The ER model correctly identified separate entities (UserTypes, Users, ResourceTypes, Locations, Resources, Reservations) corresponding to distinct real-world concepts, each with its own independent set of attributes.
- Each entity was mapped to its own relation with a surrogate primary key, and relationships were represented through foreign keys.
- No entity combined attributes from multiple independent concepts, so no transitive dependencies existed within any individual table.
Since the normalization result matches the existing design, no changes to the database schema are required. The Phase 2 relational design and DDL scripts remain valid and will continue to be used for all subsequent project phases.
