X Hotel Database Normalization: UNF to 3NF

(Based on Case Study Requirements and ERD Analysis)

Unnormalized Form (UNF) Example

Based on the X Hotel case study and your ERD design, here's a comprehensive example of data in UNF - a single flat table containing all information that would typically be collected in the hotel booking system:

Hotel_Booking_System_Complete (UNF)

Critical Problems with UNF:

1. Repeating Groups: Multiple room details (RoomID, RoomNumber, RoomType, etc.) in single row
2. Data Redundancy: Hotel, Branch, and Manager information repeated for each booking
3. Update Anomalies: Changing manager details requires updating multiple records
4. Insert Anomalies: Cannot add new branches without having bookings
5. Delete Anomalies: Deleting a booking loses branch/manager information
6. Inconsistent Data: Multiple values in single cells violate atomic value rule
7. Storage Waste: Massive duplication of data

STEP 1: Convert UNF to First Normal Form (1NF)

Rule for 1NF:

• Each cell must contain only atomic (single) values
• Each record must be unique
• No repeating groups allowed

Actions Taken:

1. Eliminate Repeating Groups: Separate multi-valued attributes (RoomID, RoomNumber, RoomType, RoomRate, Capacity)
2. Create Atomic Values: Split combined room information into separate rows
3. Ensure Unique Records: Each row represents one room in one booking
4. Establish Primary Key: Use composite key (ReservationID, RoomID)

Tables in 1NF:

Hotel_Booking_System_1NF

Primary Key: (ReservationID, RoomID)

Achievements:

• ✅ All cells contain atomic values
• ✅ Each record is unique
• ✅ No repeating groups
• ❌ Still has redundancy and anomalies

STEP 2: Convert 1NF to Second Normal Form (2NF)

Rule for 2NF:

• Must be in 1NF
• All non-key attributes must be fully functionally dependent on the entire primary key
• Eliminate partial dependencies

Functional Dependencies Analysis:

Primary Key: (ReservationID, RoomID)

Full Dependencies (depend on both ReservationID AND RoomID):
- None identified (this is the problem!)

Partial Dependencies (depend on only part of the key):
ReservationID → CustomerID, FirstName, LastName, CustomerEmail, ContactNumber, Address, BookingDate, CheckInDate, CheckOutDate, EmergencyContact, SpecialRequests, BranchID, CheckInID, CheckInTime, CheckOutID, CheckOutTime, InvoiceID, InvoiceDescription, TotalAmount, CustomerName, FeedbackID, Rating, Review

RoomID → RoomNumber, RoomType, RoomRate, Capacity, RoomAvailable, BranchID

Additional Dependencies:
CustomerID → FirstName, LastName, CustomerEmail, ContactNumber, Address
BranchID → BranchLocation, ManagerID, HotelID
ManagerID → ManagerName, ManagerEmail, ManagerPhone
HotelID → HotelName, EmailAddress
InvoiceID → InvoiceDescription, TotalAmount, CustomerName
FeedbackID → Rating, Review
CheckInID → CheckInTime
CheckOutID → CheckOutTime

Actions Taken: Create separate tables to eliminate partial dependencies

Tables in 2NF:

Hotel

Primary Key: HotelID

Manager

Primary Key: ManagerID

Branch

Primary Key: BranchID Foreign Keys: ManagerID → Manager(ManagerID), HotelID → Hotel(HotelID)

Customer

Primary Key: CustomerID Unique Constraint: CustomerEmail

Room

Primary Key: RoomID Foreign Key: BranchID → Branch(BranchID)

Reservation

Primary Key: ReservationID Foreign Keys: CustomerID → Customer(CustomerID), BranchID → Branch(BranchID)

Reservation_Room (Junction Table)

Primary Key: (ReservationID, RoomID) Foreign Keys: ReservationID → Reservation(ReservationID), RoomID → Room(RoomID)

CheckIn

Primary Key: CheckInID Foreign Key: ReservationID → Reservation(ReservationID)

CheckOut

Primary Key: CheckOutID Foreign Key: ReservationID → Reservation(ReservationID)

Invoice

Primary Key: InvoiceID Foreign Key: ReservationID → Reservation(ReservationID)

Feedback

Primary Key: FeedbackID Foreign Key: ReservationID → Reservation(ReservationID)

Achievements:

• ✅ All partial dependencies eliminated
• ✅ Data redundancy significantly reduced
• ✅ Update anomalies mostly resolved
• ❌ Some transitive dependencies still exist

STEP 3: Convert 2NF to Third Normal Form (3NF)

Rule for 3NF:

• Must be in 2NF
• Eliminate all transitive dependencies
• No non-key attribute should depend on another non-key attribute

Transitive Dependencies Analysis:

Transitive Dependencies Found:

In Invoice table:
ReservationID → CustomerID → FirstName, LastName (CustomerName)
This creates: InvoiceID → ReservationID → CustomerID → CustomerName

In Branch table:
BranchID → ManagerID → ManagerName, ManagerEmail, ManagerPhone
(Already properly normalized - Manager is separate entity)

In Room table:
RoomID → BranchID → BranchLocation, ManagerID, HotelID
(Already properly normalized - Branch is separate entity)

Actions Taken: Remove transitive dependencies

Final Tables in 3NF:

Hotel

Primary Key: HotelID

Manager

Primary Key: ManagerID

Branch

Primary Key: BranchID Foreign Keys: ManagerID → Manager(ManagerID), HotelID → Hotel(HotelID)

Customer

Primary Key: CustomerID Unique Constraint: CustomerEmail

Room

Primary Key: RoomID Foreign Key: BranchID → Branch(BranchID) Note: RoomAvailable removed (should be calculated based on reservations)

Reservation

Primary Key: ReservationID Foreign Keys: CustomerID → Customer(CustomerID), BranchID → Branch(BranchID)

Reservation_Room

Primary Key: (ReservationID, RoomID) Foreign Keys: ReservationID → Reservation(ReservationID), RoomID → Room(RoomID)

CheckIn

Primary Key: CheckInID Foreign Key: ReservationID → Reservation(ReservationID)

CheckOut

Primary Key: CheckOutID Foreign Key: ReservationID → Reservation(ReservationID)

Invoice

Primary Key: InvoiceID Foreign Key: ReservationID → Reservation(ReservationID) Note: CustomerName removed (get from Reservation → Customer relationship)

Feedback

Primary Key: FeedbackID Foreign Key: ReservationID → Reservation(ReservationID)

Summary of Normalization Process

Problems Eliminated:

Data Redundancy:

• Before: Customer info repeated in every room booking
• After: Customer info stored once in Customer table

Update Anomalies:

• Before: Changing manager info required updating multiple records
• After: Manager info updated once in Manager table

Insert Anomalies:

• Before: Cannot add new branch without existing bookings
• After: Can add branches independently

Delete Anomalies:

• Before: Deleting booking loses branch/manager information
• After: Branch/manager info preserved independently

Key Design Decisions:

1. Hotel Entity: Included to support multi-hotel expansion
2. Separate Check-in/Check-out: Allows flexible timing management
3. Junction Table: Handles many-to-many relationship between reservations and rooms
4. Room Availability: Calculated field rather than stored attribute
5. Customer Name in Invoice: Removed to eliminate transitive dependency

Business Rules Enforced:

1. Unique Email: Prevents duplicate customer registrations
2. Branch-Room Relationship: Ensures rooms belong to specific branches
3. Reservation-Branch Consistency: Customers can only book rooms from one branch per reservation
4. Manager-Branch Relationship: Each branch has exactly one manager
5. Rating Scale: 1-5 scale maintained through application logic

Final Database Benefits:

• Minimal Redundancy: Each piece of data stored once
• Data Integrity: Referential integrity through foreign keys
• Scalability: Easy to add new hotels, branches, rooms
• Flexibility: Supports complex booking scenarios
• Maintainability: Changes isolated to specific tables
• Performance: Optimized for queries and updates

This 3NF design provides a robust, scalable foundation for X Hotel's booking management system that fully meets all case study requirements while maintaining optimal database structure.