Task 2 (7 marks) Conceptual modelling of a data warehouse An objective of this task is to create a conceptual schema of

[answerhappygod]

Task 2 (7 marks) Conceptual modelling of a data warehouse 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
the medical facilities that have a name, address, possibly (not
obligatory) specialization. Each medical worker is described 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
administration worker. The service can be diagnosis, treatment, or
checkup. A description and date of each service is 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.
A 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, - total length of medical
services per medical facility, per year, per month per day, per
city and per medical worker, - average length of medical services
per medical facility, per year, per month per day, per city and per
medical worker, - total number of doctors/ paramedics/nurses
involved in medical services, per year, per month per day, per
medical facility, per city, - average time spend on medical
services per year, month, day, - total costs of medical services
per year, month, day, per medical facility, per city. To draw a
conceptual schema, use a graphical notation explained to you in a
presentation 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 a conceptual schema, you must use a graphical notation
explained to you in a presentation 11 Conceptual Data Warehouse
Design. 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. UMLet 14.3 software is can be
downloaded from the subject's Moodle Web site in a section WEB
LINKS. A neat hand drawing is still all right. Deliverables A file
solution2.pdf with a drawing of a conceptual schema of a sample
data warehouse domain.