Consider the following hotel database: ?

--> Each hotel is uniquely identified by its hotel ID. For each hotel, we also record these attributes: name, e-mail, contact number, owner and address. The Address is composed of a suburb and a street. Each hotel can have multiple owners. ?

-->There are a lot of staff working at the hotels. A staff is uniquely identified by his/her ID. For each staff, we also record his/her name, phone number and email. Each staff works in one or more hotels. ?

-->Each hotel owns one room or more rooms, and each room is identified by the room number and the hotel ID. We also record the phone number and the description of each room. ?

-->A customer is uniquely identified by his/her email. For each customer, we also record his/her name, phone number and gender. ?

-->A reservation is uniquely identified by a reservation ID. A reservation must be created by exact one customer.

A customer can have zero or more reservations. A reservation must involve one hotel, and a customer cannot reserve the rooms from multiple hotels in one reservation. For each reservation, it must contain one or more rooms. We record the number of reserved rooms, total price, the date of arrival and date of departure. Besides, each hotel and room can be included in zero or more reservations.

(a)Draw an ER diagram to represent this scenario and clearly state the assumptions you make if any.

(b)Translate the ER diagram of the above question into a relational model.

pur-new-sol

Related Questions