Spring 2012 Assignment 3 – Data Visualization

Spring 2012 Assignment 3 – Data Visualization

Objectives

1. Use the query and visualization tools

2. Practice with real data sets

Assignment

Will use the data set  http://data.medicare.gov/dataset/Survey-of-Patients-Hospital-Experiences-HCAHPS-/rj76-22dk  – this site has the ability to export overall data as a spreadsheet and other formats.

1. Design a simple database to import (at a minimum) these fields

 Hospital Name, City, State, Zip Code, and the results of these question

Percent of Patients who reported their nurses (1) Sometimes or Never (2) Usually, or (3) Always communicated well (3 different questions)

Percent of Patients who reported their doctors(1) Sometimes or Never (2) Usually, or (3) Always communicated well (3 different questions)

Percent of Patients who reported that staff (1) Sometimes or Never (2) Usually, or (3) Always explained about medications before giving it to them (3 different questions)

note: You have 9 questions with results. These results also need to be in the database as a numeric field because you will be doing a bit of calculation with them.

2. Import the data into your database – you only need to import data for California and Florida – but you can easily import it all at the same time with the same amount of work.

3. Here is the real (new) work – you will write 3 very simple stored procedures with one input – the state code (like ‘CA’ or ‘FL’) and will produce a score for communications. Here is how the score will be calculated.

Score = (Percent of Sometimes or Never Answers * 0 + Percent of Usually Answers * 5 + Percent of Always Answers * 10) / Total Percent

note: Percent should be used as a number between 0 and 1, the range of the score will be from 0-10. Total Percent is always 1 – so is really not needed explicitly in the calculation.

The reported score from the stored procedure will be the AVERAGE for the state scores. You may also do this as a Stored Function. (This would be a scalar valued function).

The stored procedures will be called

  sp_comScoreDoctors (StateCode Varchar2)

  sp_comScoreNurses (StateCode Varchar2)

  sp_comScoreMedications (StateCode Varchar2)

4. Using screen captures, write up the progress of this assignment and upload to D2L drop box.

Resources

There is a lot in this assignment, data import, equations, stored procedures.