An objective of this task is to create a conceptual schema of a sample data warehouse

domain described below. Read and analyse the following specification of a data

warehouse domain.

A person is represented as either a patient or a medical worker or an administration

worker. Medical and administration workers work in medical facilities that have a name,

address, and possibly $($not obligatory$)$ specialization. Each medical worker is described

as a unique staff number at a facility, name, address, and phone number.

A patient visits a medical facility for service of a health problem. Each service involves a

patient, a medical worker, and an administration worker. The service can be a diagnosis,

treatment, or checkup. A description and date of each service are recorded. Time spent

on service and the costs are recorded as well.

A patient is eligible for his or her company health care benefits. Patient data includes

name, id number $($social security number$),$ address $($street$,$ city, state, zip$),$ and phone.

A medical worker must hold one or more credentials that are granted to work in a

particular medical facility. Doctors are allowed to deliver diagnosis and give treatment

based on their specialization Paramedics are allowed to deliver only emergency

diagnosis and treatment for any type of life$-$threatening problems. Nurses do not deliver

diagnosis, but they do participate in treatment, particularly if the patient must be

prepared for surgery or remain at the facility overnight.

The administration workers are concerned with personnel needs and assignments. Each

medical worker must have at most one assignment at a facility. Several administration

workers can be assigned to one assignment.

Medical facilities are located in different suburbs of different cities. A medical facility is

uniquely identified by an address.

A data warehouse must be designed such it should be possible to easily implement the

following classes of applications.

The management of the medical facilities would like to get from a data warehouse

information about

$-$ the total number of medical services performed per medical facility, per year, per

month per day, per city and per medical worker,

$-$ the total length of medical services per medical facility, per year, per month per day,

per city and per medical worker,

$-$ the average length of medical services per medical facility, per year, per month per

day, per city and per medical worker,

$-$ the total number of doctors$/$ paramedics$/$nurses involved in medical services, per year,

per month per day, per medical facility, per city,

$-$ the average time spent on medical services per year, month, day,

$-$ the total costs of medical services per year, month, day, medical facility, and city.

To draw a conceptual schema, use a graphical notation explained to you in lecture $11$

Conceptual Data Warehouse Design.

To create a conceptual schema of a sample data warehouse domain, follow the steps

listed below.

Step $1$ Find a fact entity, find the measures describing a fact entity.

Step $2$ Find the dimensions.

Step $3$ Find the hierarchies over the dimensions.

Step $4$ Find the descriptions $($attributes$)$ of all entity types.

Step $5$ Draw a conceptual schema.

To draw your diagram, you can use UMLet diagram drawing tool and apply a

"Conceptual modelling" notation, Selection of a drawing notation is available in the right

upper corner of the main menu of UMLet diagram drawing tool.

Deliverables

A file with a drawing of a conceptual schema of a sample data

warehouse domain.