Final Exam Fall 2005

Instruction

1. Answer all questions.  Optional questions can gain you additional points but wrong answers will not reduce your grade.

2. To answer, use any resource (e.g., notes, books, web-sites) EXCEPT another human being.

3. Check back this page to read the instructor's answers to questions asked by you or others.

4. Copy the questions and your answers into a word processing document.  Do not send just the answers.  Copy the full text of the questions into the document.  Please name your document <YourName-Final-HCSI-709.doc> and send it to hsci709@gmu.edu.  Keep records of your emails sent. 

5. If you have a question, you can ask it at the bottom of this exam.

Exam

Do not proceed without completing each question, including questions 1 through 6:

1. On what day and time you first viewed this page? 
    
2. What is your first and last name?
    
3. What is your email?
    
4. What is your telephone number?
    
5. Have you received help from any one else in responding to this exam?
    
6. To date, which of the weekly assignments have not been handed in (add an explanation for any item that is late)?

7. In response to the following questions, provide both the SQL code of the query and the image of the resulting data.  Import data from here and complete the following analysis on this data:
   1. Calculate similarity of diagnoses.  Calculate the frequency with which two diagnoses occur for the same patient within 30 days of each other.  Refer to this as "Similarity".  Please note that frequency should be a number between 0 and 1, you can obtain this by counting the number of times a pair of diagnoses co-occur within 30 days and dividing this by total number of diagnoses.
   2. Calculate time between diagnoses.  Calculate for each patient time between each diagnoses and subsequent diagnoses of the same patient, refer to this as "TimeBetween".
   3. Calculate Score.  Calculate for each pair of diagnoses the ratio of one plus Similarity by one plus TimeBetween, refer to this as "Score".
   4. Enter cutoff value.  Assume the cutoff value is 2 divided by 31 (obtained by assuming that a patient with two exactly same diagnosis but more than 30 days apart are no longer part of the same episode).
   5. Identify pairs of diagnoses that belong to the same episode.   Select any pair of diagnoses for the same patent.  Include only pairs of diagnoses where the first diagnosis occurs prior to the second diagnosis..  For each patient, assign any pair of diagnoses where the Score exceeds the cutoff value as belonging to the same episode.  Name this episode the same as the name of the first diagnosis.
   6. Combine linked episodes.  Compare any two pairs of diagnoses identified in previous step and if any one of the four diagnosis within the pairs are the same, then all four diagnoses belong to the same episode.  Name this episode the same name as the name of the episode for the first diagnosis (optional).   
   7. Report which client has the largest number of episodes (optional). 
8. In response to the following questions provide both the SQL code for the query and the image of the resulting data.  In the following questions, use the enclosed tables for frequency, importance and drug orientation of clients. 
   1. For each client, calculate a query that assigns to the people not listed in the frequency or importance tables but listed in the Orientation table, the last available rank for that client.  
   2. For each client, calculate a new field called FrequencyWeight equal to 1 divided by the rank for data in the table Frequency for this person.  Calculate a new field called ImportanceWeight equal to 1 divided by the rank for data in the table Importance.   
   3. For each client and for the tables Frequency and Importance, create a query that calculates a new field called Standardized weight.  For each client, standardized weight for frequency, SWF, is equal to the FrequencyWeight divided by the sum of the FrequencyWeights for same person.  For each person, standardized weight for importance, SWI, is equal to the ImportanceWeight divided by the sum of the ImportanceWeights for the same person.
   4. For each client, create a query that, for each initial, multiplies the standardized weight for frequency of contacts, SWF,  with standardized weight for importance of contact, SWI, and the orientation of contact (0 if negative and 1 otherwise).  Sum the resulting finding across initials.  Call this NetworkScore for the person.
   5. Find the client most likely to use drugs by finding the client with maximum NetworkScore.