Task 

Conceptual modeling 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 analyze 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 the 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 diagnoses and give treatment based on their specialization Paramedics are allowed to deliver only emergency diagnoses and treatment for any type of life-threatening problem. Nurses do not deliver a 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. 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, – 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 the UMLet diagram drawing tool and apply a “Conceptual modeling” notation, Selection of a drawing notation is available in the right upper corner of the main menu of the 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.