Search code examples
sqlentity-frameworkdatabase-designentity-relationshiperd

Entity Relationship Diagram for Hotel

I need to create an ERD for a hotel, the question reads as follows:

"The owner of a set of hotels would like to have a hotel management system built for his or her hotels. The hotels are located in major cities and tourist attraction areas of South Africa. In each hotel, employees are hired to perform various duties. In each hotel, guests make reservations for rooms of their choice, and they are billed according to the type of room they have reserved. Given the business scenario above, please draw an entity relationship diagram indicating all entities and relationships (cardinality). "

This is what I have created:

enter image description here

My Questions

  • if Employee_ID is a foreign_key in Hotel, will it be a foreign_key in Employees table aswell or does it just change to a Primary key?

  • Is it correct / okay to add Hotel_code, and Room_ID in the Reservation table? Is Hotel_CODE a foreign key in RESERVATION table?

  • Is the overall design of the ERD correct or to an acceptable level including keys and attributes

If someone could skim this over for me it would be much appreciated

Solution

  • Take this with a grain of salt as I'm not 100% sure:

    • The Employee shoud have a foreign key to the Hotel_code (assuming one employee does not work for multiple hotels)
    • The hotel does not have a foreign key to the employee
    • The reservation should have a foreign key to the hotel_code
    • The Guest does not need a foreign key to the reservation
    • Guest_Id should not be part of reservations PK
    • the bill should have a foreign key to the reservation instead of to the guest
    • the reservation could have additional information about the duration of the stay
    • the room does not need a guest_id and res_nr