All answers need to be printed and not handwritten.
This homework provides some SQL exercises on a small subset of the Children's Hospital database, from the mid-1990's. These data have been "scrubbed", so that specifically identifying information has been altered or removed. Nevertheless, please treat the data with sensitivity, because you will learn from our discussion of medical data confidentiality that even innocuous-seeming data can help re-identify. This CWS database can be downloaded in MS Access Version ,or MySQL version, together with a brief description of the database. According to the show of hands on the first day, everyone should be able to install these data on a Windows, Linux or Macintosh system. MySql is available for free non-commercial use. To use Access, you need to be running Windows and have installed Microsoft Office. As a last resort, Delin has created a Web-based interface to the database at http://disclose.lcs.mit.edu/cws/retrieve.jsp. This runs on an ancient (Pentium 133) server, so please don't overload it. You can also explore the database via http://disclose.lcs.mit.edu/cws/cwstables.jsp.
Additional information about the CWS database is in the following documents:
Tutorial information about SQL is widely available on the Web. Some sites are linked from the 6.872 Schedule page.
Problems marked as bonus question are not required and you donít have to answer them, but I do suggest you take a look and think about them.
Please hand in your homework in class on 2/19, Thursday.
1. SQL practice on CWS database: please use as few as possible queries.
a) Please list the query to retrieve all disease names (no duplicated ones) in the database. You may either use the table PROBLEMS and/or any other tables you think appropriate.
b) Please list the query or queries to retrieve the names of the doctors who have treated the disease DIABETES-INSIPIDUS. You may use any table/tables you think appropriate.
c) Give three different queries, each of which will estimate the total number of patients being tracked in the database. If they result in different numbers, discuss why. (Ignore the possibility that the same patient is entered several times but with different identifiers.)
3. We learned in class three different paper-based medical record systems: doctorís journal, patientís journal (e.g. Mayo), and problem oriented medical records (e.g. SOAP).
a) Please list the pros and cons of each system while comparing with the others.
b) Design a simple relational database table scheme of electronic medical record (EMR) system, from which the above three styles of medical records can be easily retrieved. You donít have to include all clinical information. Just notes would suffice.
c) Please list the queries to create the table, and the queries to retrieve the three types of medical records.
d) Mr. Murphy was sent to the hospital with a broken leg because of a car accident, completely healthy otherwise. He was treated and discharged by Dr. Jenkins and Dr. Gehrke. Please list the query or queries to insert this new data into your database.
4. Describe an ideal Computer-based Patient Record (CPR) system based on the functional components and fundamental issues discussed in Shortliffe by listing at least two properties of each functional component and the three most important aspects of each fundamental issue. What kind of impediment would you expect to face to develop and use such a CPR?
5. Shortliffe page 396, p4
Describe the key philosophies underlying the design of central, modular, and distributed HCISs. What are at least two advantages and two disadvantages of each architecture?
6. (Bonus question, no credit) Please list at least two possible explanations of the following fact: medicine spends 1-2% on IT, vs. 6-7% for business overall, vs.10-12% for banking. Do you think information technology should play a more important role in medicine? Why?