A resort is interested to keep track of all the rooms in its location for maintenance and cleaning purpose. It has several buildings and each building has many rooms. For simplicity, assume that there are three types of room. Also a room could be in different statuses at a particular time occupied, check in ready, need cleaning, need maintenance, checked out, vacant. These statuses are not mutually exclusive. As an example, a room could both need maintenance and need cleaning and it could be either occupied or vacant. So one of the requirements is to figure out how many different statuses you would need. Also you need to keep track of cleaning employees and maintenance employees. A person cannot be both at the same time. Also each building has a manager. The manager regularly inspects the rooms and determines when a room needs maintenance. If a room needs maintenance she creates a ticket and assigns it to an employee. After finishing the maintenance, an employee closes the ticket.

Please list the entities you have and for each of them describe why you decided to have that entity. [10]

Draw the E-R diagram showing the important attributes for each entity. You must show the relationships among the entities accurately and it is not necessary to show the foreign keys. [15]

Using crow's feet notation, develop a data model (E-R diagram) for the following scenario (15)

What is Referential Integrity? Explain using an example. In your project give some examples where you enforced referential integrity. What is the purpose of enforcing referential integrity?