Develop an entity-relationship diagram (ERD) using Chen notation for the case study

described below. You are to clearly:

identify entity sets, relationship sets, all attributes, and primary keys;

specify connectivities for relationship sets;

specify overlapping/disjoint constraints, if there is a specialization/generalization

hierarchy.

To simplify ERD, assume that phone numbers, addresses, and names are atomic attributes (no

need to replace them by a set of atomic fields).

Case Study

An all-inclusive resort located on an island in the Atlantic Ocean offers packages for tourists

around the world. The resort management works with several tour operators distributing the

resorts packages. Each tour operator is described by its ID, name of the company and contact

phone. A tour operator may work with one or more airlines to bring tourists to the resort. An

airline is described by its name, phone, and website. Each tour operator has representatives at the

resort who arrange guest transportation, additional excursions, and deal with guests concerns. A

representative is described by their ID, name, and phone number. Tour operators representatives

are assigned to take care of several guests for the entire stay of the guests. This assignment is

important for the resort management.

The resort accommodates guests in 33 2- and 3-storey buildings. Some of the buildings

face the ocean. Buildings are numbered from 1 to 33 and are described by their number, category

(2- or 3-storey), and view (ocean, forest, or other buildings). Each building contains from 8 to 12

rooms. Rooms are numbered from 1 to the maximal number of rooms in the building, and are

described by the number of beds in the room and a category, e.g., standard, executive, etc.

All guests are described by GID, name, home address. All guests are classified into

principal and regular. Only an adult person can be a principal guest. It is important to record a

status for each regular guest. The status indicates whether the guest is an adult, child or teenager.

Principal guests credit card number is required. A room is reserved for a principal guest, and up

to two regular guests are allowed to share the room along with the person who booked the room.

Guest accommodation in the resort must be recorded in the database. A principal guest is

responsible for all charges related to the room. The charges can be associated with the services not

included in the package or damages made to the resort by the guests.

Available services are described by service ID, title, and an associated fee. Some services

require reservation. The reservation is associated with the room. It is necessary to record the

requested service, guest requested the service, room which account will be billed, and the date of

the service. The charges must be paid by the principal guest before or at check-out.

Food is served at four restaurants. A restaurant is described by its ID, name, type, e.g.,

buffet or ala carte, and the theme, e.g., French, Caribbean, etc. Every guest who comes to a

restaurant must be recorded. It is necessary to know, the restaurant, the guest, date and type of

meal the guest received, e.g., breakfast, lunch, or dinner.

The resort employees are cooks, waiters, and workers. All employees are described by

employee ID, name, and address. A cook has assigned category. Knowledge of foreign language

is required for a waiter. A worker is described by their specialization. There are few managers in

each group of employees. Regular employees receive their assignments and report to the managers.

Some managers must report to other manager, who reports to the resort Director.

Each cook works in a specific restaurant. Each restaurant may have several cooks. Waiters

may work in different restaurants including the Buffet according to the timetable. The timetable

may vary.