Designing a Hotel Database

The assignment is focused on designing a hotel management database, where two tables (HOTELROOM and GUEST) are initially designed without any relationship between them. An employee establishes a relationship between the two tables by adding the GuestNum column to the HOTELROOM table, creating a foreign key that connects each room with its assigned guest. The design is then modified to accommodate multiple rooms being reserved by a single guest. Finally, the assignment asks students to identify and remove redundancy in the existing design and provide their own optimized database design.

General Directions
This assignment is focused on database design, so there is no database to work with. It will be your
job to design one. You will not create the database in MySQL; we will cover that topic later. Instead,
you will demonstrate your understanding of design principles. In the last task, you will create your
own database design. I do not want a drawing of the database design, although that may help you
conceptualize the design, so I recommend it if it helps. However, do not turn in the drawing.
For the final task, your database design should be a collection of tables that are described one at a
time in this format. The foreign key is optional and should only be listed if it applies.
tablename(column1, column2, ...columnN)
primary key: columnname
[foreign key: columnname]
The tablename should be the name of the table and the parentheses should hold the list of all of the
column names in that table. Here is an example of a table named HOTELROOM that has four
columns (RoomNum, type, price, GuestNum). The Primary key column name is RoomNum and the
foreign key column name is GuestNum:
HOTELROOM(RoomNum, type, price, GuestNum)
Primary Key: RoomNum
Foreign Key: GuestNum
The database design should include all tables listed in any order.
Task 01:
There is a hypothetical hotel that keeps track of all of their rooms in the hotel and all of the guests in
each of the rooms. They have two tables that have the following structure:
HOTELROOM(RoomNum, type, price)
Primary Key: RoomNum
GUEST(GuestNum, name, address)
Primary Key: GuestNum
Note that there is currently no relationship between the tables.
Here is the data (see below).

An employee of the hotel needs to be able to find which guest is assigned to which room, so that
employee creates a relationship between the two tables, like this:
HOTELROOM(RoomNum, type, price, GuestNum)
Primary Key: RoomNum
Foreign Key: GuestNum
GUEST(GuestNum, name, address)
Primary Key: GuestNum
Here is the data once the relationship is established (see below). Assume that this is all of the data
and any new data would follow the same relationship pattern as demonstrated in the existing data:
Was the relationship established correctly? How can you tell?
Task 02:
It doesn't happen very often, but occasionally a very rich and famous guest will reserve multiple
rooms. In fact, the President of the United States once stayed at this hotel, and for security reasons,
the entire floor was reserved for him. The tables were modified to handle this new relationship. The
tables below reflect the design change.

Was the relationship established correctly? How can you tell?
Task 03:
There is redundancy in this design.
A) How could this design could potentially lead to errors in the data?
B) How could this design negatively impact the front desk personnel who have to use it?
C) Where is the redundancy in the data in these tables?
D) How could you change the table structures to remove the redundancy?
Explain your reasoning in general terms, but do not provide a database design here; that will be the
next question.
Task 04:
Provide what you consider to be the best database design for the data provided and explain why it is
the best design. Display the data as it would reside in your design.