Database Design for Cat's Whiskers Hotel Booking System

This assignment presents a comprehensive database design for the Cat's Whiskers Hotel, a boarding cattery. The solution details the database design process, starting with an unnormalized schema and progressing through First, Second, and Third Normal Forms (1NF, 2NF, and 3NF). The design includes an Entity Relationship Diagram (ERD) to illustrate the relationships between entities such as Customers, Pets, Rooms, and Bookings. The assignment breaks down the transformation of data from unnormalized forms to normalized forms, creating tables for Customers, Pets, Rooms, Bookings, Booking_Pets, and Payments. The final normalized database structure is optimized to reduce redundancy and ensure data integrity. The solution also outlines the advantages and disadvantages of database normalization, providing a balanced view of the design choices made. The design aims to automate the hotel's booking system, keeping track of rooms, bookings, payments, and customer and pet information.

Student Name
Student ID 1
Database Design Process – Cat’s Whiskers Hotel
Submitted By
Course
Professor
Date

Student Name
Student ID 2
Introduction
The Cat's Whiskers Hotel is small, boarding cattery for cats which is nearby to a farm in
countryside. This animal hotel is planning to open full-fledged in 2020. As this hotel is only for
cats, cattery will provide a calm and extravagant experience for pet felines whereas their
proprietors are away, making a calm, warm and safe environment for their clients' adored cats
while accepting the best quality of care and consideration from the cattery's feline adoring
owner.
There are 10 well-equipped uniquely floor tiled warmed rooms that include activity walls,
scratching posts, TVs displaying goldfish, mice or bird videos, comfortable and easy to wash
bedding and are intended to make a comfortable environment for felines to remain, play,
unwind and have a sense of security in. While each room has a protected twofold coated
entryway and a window, the window can be opened by staff (assuming the rainclouds blow over)
to allow the access of felines to their very own protected, encased outdoors private galleries.
Every room in this hotel has an organized IP dish tilt-zoom CCTV camera, and the feed is
likewise connected with the cattery's wide CCTV framework. The overall framework is for
security and risk reasons, the framework likewise permits owners to access CCTV cameras in the
rooms. The owners can check their felines using mobile application. Its very time consuming for
Cat whisker Hotel to keep all the records of customers and their pets manually.
Being database administrator, I have been asked to design a database system in order to develop
automate booking system for this Cat’s hotel. We have to design a database for the Cat Hotel that
can help in keeping records of all rooms, booked rooms and the rooms that are available for next
booking. The database will also keep record of all the payments made by the customers and the

Student Name
Student ID 3
bookings that are with the pending payments. The administrator can easily search for the rooms
that are available and can make booking in that rooms. Also, this database will keep the record of
all the payments made by the customer whether its full payment or any partial payment.
Entity Relationship Diagram
According to the given scenario, we have taken two entities – Customer and pets. Pets is a
conceptual as it is a weak entity as it depends on rooms that are booked and the customer who
has booked the room. The booking of a room depends on the customer for his pets that can be
more than one or the complete family. We can say that room booking depends on the special
requirements that are necessary for the pet whether its medical requirements, and its dietary plan.
The Unnormalized Schema
The above requirements in the unnormalized schema will be as following:
Customer_Room (CustomerID, Name, ContactNo, PetName, BookingID, RoomNo, RoomType,
BookingDate, No_of_days, MealRequired, Cost)
Customer_Room

Student Name
Student ID 4
CustomerI
D
Name ContactNo PetNam
e
BookingID RoomNo RoomType BookingDate No_of_Days MealRequired Cost
C001 Anderson 989999999 Pussy,
Silky
1 R01 2 Cat Sharing 12/10/2019 2 Luxury 60
C002 Randhir
K.
23456789 Shinoya 2 R02 1 Cat Sharing 12/11/2019 2 Standard 35
C003 Shinoma 987654322 Tuffy 3 R03 1 Cat Sharing 12/12/2019 1 Standard 17
C004 Sanjana 456789292 Diana 4 R04 1 Cat Sharing 12/13/2019 1 Standard 17
C005 Michael 456789292 Shimona 5 R05 1 Cat Sharing 12/13/2019 1 Standard 17
First Normal Form (1NF)
A database is in first normalized form if:
 Every cell consists of single value
 Every row must be unique
 Values that are stored in the column must be of same domain.
In case of above unnormalized table, we must divide this data in two tables. In first normalized
form, there must be only atomic values. Therefore, we have to split above composite attributes
into simple atomic values. We have to divide Customer_Room into Customer_Pets and
Customer_Rooms tables. The above tables become:
Customer_Pets
CustomerID Name ContactNo PetName
C001 Anderson 989999999 Pussy
C001 Anderson 989999999 Silky
C002 Randhir K. 23456789 Shinoya
C003 Shinoma 987654322 Tuffy
C004 Sanjana 456789292 Diana
Customer_Rooms

Student Name
Student ID 5
CustomerID BookingI
D
RoomNo RoomType BookingDat
e
No_of_Days MealRequired Cost
C001 1 R01 2 Cat Sharing 12/10/2019 2 Luxury 60
C001 1 R01 1 Cat Sharing 12/10/2019 2 Luxury 60
C002 2 R02 1 Cat Sharing 12/11/2019 2 Standard 35
C003 3 R03 1 Cat Sharing 12/12/2019 1 Standard 17
C004 4 R04 1 Cat Sharing 12/13/2019 1 Standard 17
Second Normalized Form
According to second normalized form:
 A table must be in 1NF.
 And no partial dependency exists in the table.
The functional dependency must depend on the primary keys. Here, we will further split the
above table as following:
Customer (CustomerID, Name, ContactNo, No_of_pets, PetName)
CustomerI
D
Name ContactNo No_of_pets Pername
C001 Anderson 989999999 2 Pussy, Silky
C002 Randhir K. 23456789 1 Shinoya
C003 Shinoma 987654322 1 Tuffy
C004 Sanjana 456789292 1 Diana
C005 Michael 657865677 1 Miami
Booking (BookingID, CustomerID, BookingDate, RoomNo, No_of_Days, MealRequired)
BookingI
D
CustomerID RoomNo BookingDate No_of_Days MealRequired Cost
1 C001 R01 12/10/2019 2 Luxury 60
2 C002 R01 12/10/2019 2 Luxury 60
3 C003 R02 12/11/2019 2 Standard 35

Student Name
Student ID 6
4 C004 R03 12/12/2019 1 Standard 17
5 C005 R04 12/13/2019 1 Standard 17
Rooms (RoomNo, RoomType, Price, MaxOccupancy, RoomFacilities, Status)
RoomNo RoomType Price MaxOccupancy RoomFacilities Status
R01 2 Cat Sharing 60 2 All (Luxurious) Booked
R02 1 Cat Sharing 35 1 All Booked
R03 1 Cat Sharing 17 1 All Booked
R04 1 Cat Sharing 17 1 All Booked
Third Normalized Form
A database is in third normalized form, if it is in first and second normalized form and no
transitive dependency exist in the database. Now the above tables will be further splitted into
small tables in order to convert it into third normalized form. We will divide pets in another table
as a customer can have more than one pet.
Customer (CustomerID, Name, ContactNo, No_of_pets)
CustomerID Name ContactNo No_of_pets
C001 Anderson 989999999 2
C002 Randhir K. 23456789 1
C003 Shinoma 987654322 1
C004 Sanjana 456789292 1
C005 Michael 657865677 1
Pets (PetID, PetName, CustomerID, Color, DietaryPlan, Description, MedicalProblem,
LastVaccinationDate)
PetID PetName CustomerID Color DietaryPlan Description MedicalProblem LastVacDate

Student Name
Student ID 7
P001 Pussy C001 White Luxury NA None 01/01/2019
P002 Silky C001 Brown Luxury NA None 01/01/2019
P003 Shinoya C002 Black Standard NA None 01/01/2019
P004 Tuffy C003 White Standard NA None 01/01/2019
P005 Diana C004 White Standard NA None 01/01/2019
P006 Miami C005 Black Standard NA None 01/01/2019
Rooms (RoomNo, RoomType, Price, MaxOccupancy, RoomFacilities, Status)
RoomNo RoomType Price MaxOccupancy RoomFacilities Status
R01 2 Cat Sharing 60 2 All (Luxurious) Booked
R02 1 Cat Sharing 35 1 All Booked
R03 1 Cat Sharing 17 1 All Booked
R04 1 Cat Sharing 17 1 All Booked
Booking (BookingID, CustomerID, BookingDate, No_of_Days, TotalAmount)
BookingI
D
CustomerI
D
RoomN
o
BookingDat
e
No_of_Day
s
TotalAmoun
t
1 C001 R01 12/10/2019 2 120
2 C002 R02 12/10/2019 2 60
3 C003 R03 12/11/2019 2 35
4 C004 R04 12/12/2019 1 17
5 C005 R05 12/13/2019 1 17
Booking_Pets (ID, BookingID, PetID, RoomNo, MealRequired, CheckInDate, CheckOutDate)
ID BookingID CustomerID RoomNo BookingDate MealRequired CheckInDate CheckOutDate
1 1 C001 R01 12/10/2019 Luxury 12/10/2019 12/12/2019
2 1 C001 R01 12/10/2019 Luxury 12/10/2019 12/12/2019
3 2 C002 R02 12/11/2019 Standard 12/11/2019 12/13/2019
4 3 C003 R03 12/12/2019 Standard 12/12/2019 12/13/2019
5 4 C004 R04 12/13/2019 Standard 12/13/2019 12/14/2019
6 5 C005 R05 12/13/2019 Standard 12/13/2019 12/14/2019

Student Name
Student ID 8
Payment (PaymentID, BookingID, PaymentDate, PaymentMode, Amount)
PaymentID BookingID PaymentDate PaymentMode Amount
1 1 12/10/2019 Cheque 20
2 2 12/11/2019 Cash 60
3 3 12/12/2019 Cash 35
4 4 12/13/2019 Cash 17
5 5 12/14/2019 Cheque 17
6 1 12/13/2019 Cheque 100
Advantages and Disadvantages of Normalization
Advantages
1. Data Redundancy – Normalization reduces data redundancy in a database. It helps in
reducing size and also minimize data duplication and by converting data into normalized form
will ensure data to be stored only once.
2. Groups Data in Logical Form – With the conversion of data in normalized form, data is
grouped in more logical form.
3. Better Performance – Organizing database in normalized form can help in better
performance of the database by minimizing the response time while querying or searching
particular records in a database.
4. Compaction – Normalization of a database will split an unnormalized table into small tables
that can help in data and table compaction. Which implies to get smaller required size.
Disadvantages
1. With the complicated database, it becomes harder to understand the actual structure of
database.

Student Name
Student ID 9
2. It becomes extremely complicated to query a database which is optimized for further
applications.
3. The experience database administrator will be required for further data backup and for further
querying the database.
4. Table joins will be required to query the database and it make data query a complicated
process.
5. Sometimes, when we have a large number of small tables, database performance gets slow.