ERD Grand Hotel Memphis Case


Draw an enhanced entity-relationship diagram that describes the following business environment.
The Grand Hotel of Memphis has many rooms and several restaurants. The hotel wants to keep track of its rooms, employees, its guests, and its restaurants, as follows.
For every employee, the hotel wants to maintain their employee number, home address, home telephone number, and the number of years they have worked for the hotel. An employee can be a desk clerk, a maintenance worker, or a chef, but there are other job classifications, as well. However, an employee cannot be in more than one of these job categories.
For desk clerks, the hotel wants to store their education level, shift preference, and country of origin.
For chefs, the hotel wants to store their primary function (executive chef, dessert chef, salad maker, etc.), and their annual salary. Each restaurant has a name, size in square feet, cuisine, and the maximum number of patrons it can accommodate at one time. Each restaurant has several chefs. A chef can only work in one restaurant, but a newly hired chef may not have been assigned to any restaurant, yet.
The hotel wants to keep track of the date of hire and hourly wage of each maintenance worker. There are only three categories of maintenance workers: plumber, electrician, or handyman. A worker may have more than one of these skills and so fall into more than one of these categories. For plumbers, the hotel wants to know how many years the plumber has been a qualified plumber. For electricians, the hotel wants to know the date the electrician was licensed by the state. For handymen, the hotel wants to know the one main skill the person has and the one secondary skill.
Each hotel room is identified by a room number. The hotel wants to store the size of the room in square feet, the date the mattress was last replaced, and the type of view the room has. A handyman may have worked on many rooms over time and a room may have been worked on by many handymen. For each such service, the hotel wants to know the date of service, the duration of the service, and the type of service.
Of course, the hotel wants to keep records on its guests. Each guest has a unique guest number, a name, home address, and home telephone number. The hotel wants to keep historical records of which guest stayed in which room, the check-in date, the check-out date, and the room rate.

Leave a Reply

Your email address will not be published. Required fields are marked *



  • File Format: .png
  • Total Number of Pages: 1
  • Customization: Available, Write to info@libraay.com for any changes needed in the diagram