Database Design Assignment: Hotel Room, Guest Data, and Relationships

This assignment delves into the principles of database design using a hotel data scenario. The first task examines the initial relationship between hotel rooms and guests, evaluating whether the relationship was correctly established. The second task explores the implications of a guest reserving multiple rooms, assessing the design's effectiveness in handling this new relationship. The third task focuses on identifying and addressing data redundancy within the existing design, analyzing potential errors and inefficiencies. Finally, the assignment culminates in the student proposing an optimized database design for the provided data, justifying the design choices, and demonstrating how the data would be organized within the new structure. The assignment emphasizes understanding of primary keys, foreign keys, and the importance of efficient data modeling.

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.