home

// " Appointment Database" //
 * ITEC 442 Section 1**:

**Objective**
We will implement a database that records doctors appointments and allows the monitoring of diagnoses associated with an city, state, or region. Dimensions included in the database will consist of Patient, Doctor, Hospital, Day, Symptoms, and Diagnosis. All dimensions will be referenced within a central fact table "Appointments" along with measures such as blood work, lab tests, temperature, and blood pressure.

Grain
The grain for our database is an individual appointment set at a given day, involving a patient who is visiting a hospital to see their doctor. The patient has specific symptoms that a doctor uses to assign a diagnosis. We chose this grain because it deals with a specific appointment, and uses all the attributes in the 6 dimensions to describe that appointment. The dimensions provide atomic data that is necessary to describe an appointment.

Appointments Star Schema
 The information being retained in our data warehouse will generate a report, in which doctors, and or administration can use to analyze potential outbreaks of various illnesses within a city, state, or region. This is important because it allows users, based on the reports generated, to further analyze outbreaks based on months, year, or quarter. If an outbreak occurred, administration would be able to allocate doctors to an area in which the outbreak has taken place.

Dimensions
.
 * **Day Dimension** - Assists our data warehouse by providing various day attributes that will allow the user to analyze periods (weeks, months etc.) in conjunction with an appointment. (e.g. The amount of flu diagnoses during the month of December)
 * **Hospital Dimension** - Assists our data warehouse by providing hospital information that allows our system to analyze where an event (e.g. outbreak, appointment, etc.) has taken place.
 * **Patient Dimension** - Provides the patient's personal information, physical status, and their insurance provider. This dimension is helpful because the information can be used for more detailed appointment analysis. (e.g. is there a correlation between age and the volume of flu diagnosis.)
 * [[image:PatientDataPic.PNG width="776" height="137"]]


 * **Doctor Dimension** - Provides attributes about the doctors specialization (e.g. pediatrics), and if they have any records of malpractices. This dimension can be useful to our data warehouse in terms of being able to better allocate doctors to hospitals because of there specialization. (e.g. Administration can allocate more pediatricians to a hospital that deals with more children)


 * **Symptoms Dimension** - Used to list the description of patient symptoms. (e.g. Cough, Sneezing, etc.) This dimension is unique because it is the only table that plays multiple roles in our appointments star schema. The symptoms table is referenced to the appointments fact table to specify patients symptoms. The symptoms dimension is also referenced to the diagnosis dimension to generate with a diagnosis based upon the various symptoms the patient is experiencing.(e.g. if a patient has a head ache, experiencing fatigue, and has body aches, that patient would be diagnoses with the seasonal flu.
 * [[image:SymptomsDataPic.PNG width="800" height="309"]]


 * **Diagnosis Dimension** - Provides a diagnosis based on the patients symptoms. This dimension references the symptoms dimension to provide a diagnosis based on the symptoms that the patient is experiencing (e.g. Seasonal Flu (Fever, Fatigue, and Aches)).
 * [[image:DiagnosisDataPic.PNG]]


 * **Appointment Fact Table** - Describes the bases of our entire schema with all the dimensions Doctor, Patient, Hospital, Symptom, Diagnosis, and Day. This fact table provides analysis for all of the various patient vitals including, temperature and blood pressure, as well as basic lab tests and blood work.
 * [[image:AppointmentDataPic.PNG width="800" height="119"]]

We believe that an aggregate view of a Diagnosis by city by month could be useful because it shows an overall grouping of diagnosis for a city. This could be used by administration to identify drastic changes in the fluctuation of illnesses by diagnosis. For example, if we were to look at data over a month using our aggregate table, we could compare months to identify possible outbreaks or an increasing number of flu cases. However, we are not limited to just analyzing outbreaks by month but also by city, state, or region through attributes defined within our city dimension. Using this data you could further drill down to figure out which hospital has the most flu cases, or during what quarter there tends to be a higher volume of cases. We constructed this view, which resulted in the shrinking of our hospital dimension and day dimension; All other tables were lost except for the diagnosis dimension which remains unaffected. Thus, our aggregate fact table is significantly smaller due to the loss of salient attributes, making the aggregate table less atomic and more generalized.
 * Aggregate Summary**

Reports and Dashboard
The report and dashboard in the file below were created by taking the amount of H1N1 diagnosis, and dividing that number by Virginia's total population to calculate what percentage of the population has H1N1 virus. The given SQL statement would pull all the records in the Appointments database to generate the report and dashboard.



SELECT * FROM Appointments.table INNER JOIN Hospital.table ON Hospital.HospitalID = Appointments.HospitalID WHERE Diagnosis ID = 2 && State = "Virginia" GROUP BY region;


 * Data Mining Application**

Classfication:
 * Normal flu average 0% - 10%
 * Flu Outbreak 11% - 20%
 * Flu Epidemic 21% - 100%

Using the three classifications above we could create a mining application that could group cities, regions, or states based on the number of diagnosis in a given location. We could use this application along with the dashboard to monitor the different levels. The training set for the application would be historical hospital records from previous years. The historical data would describe to the application wether a current outbreak percentage is average, and outbreak, or a full epidemic. If the application were to classify a state or region as an outbreak or epidemic, hospitals and health care business could take action by producing more flu vaccines and any other means that could reduce the number of cases.