Question

Build a conceptional model for a Hotel Management System. The solution should be presented as an ER-diagram. Base your design on the following requirements.

• The database should record information about Customer, Hotel, Booking, Rooms, Employee, Feedback, and Payments.

• A Customer has a name which consists of firstName, middleName and lastName. Customers are identified by a unique customerID. A Customer has an Address, phoneNo and email address – Customers can place any number of Bookings (including none). Customer may provide Feedback for each Booking they have placed (optional). For every Booking, a Booking has to make a Payment. Customer is associated with at least one address and multiple customer can live in the same place.

• A Room is identified by its number and has a type, and a description. – Rooms are part of a Hotel.

• A hotel is identified by it is name and address ID and has a stars rating

• A Booking is uniquely identified by a bookingId. A Booking is created by a Customer. For each booking we store a Total Amount of reserved rooms, and a price for room on the booked day, period and a Date. – A Booking is associated with one or more Rooms. For each Room in a Booking, we have to record how many of the Room type is reserved for the stay, for example (2 Queen, 1 king bedroom and a suite). A booking is a made at one hotel, some hotels may not have any booking.

• Employee is identified by an employee number. An employee works in one or more hotels. An employee has a name and address and birth year. In each hotel you have a permanent employee taking a yearly salary and temporary employee working on hourly rates.

• An Address consists of a unique addrID, street and has streetNumber, city, state and zipcode. The attributes city and state can be derived from the attribute zipcode. – There may be some Addresses which are not be associated with any Customer or Hotel.

• A Payment is identified by the Booking for which the payment was made. It consists of the amountPaid and paymentMethod (Credit Card, E-Check, etc.

Answer 1

consideration included in the diagram

since the employee can be a permanent employee or a temporary one.

So in the Employee table we have created an attribute say Employee_type

Yearly_salary can be set as NULL if the Employee_type is Temporary

Or you may include the is-a relationship from Employee to create two subclass(Extended ER diagram)