Homework 1c (Initial Portion)

 

Background:

A group of doctors at Sunnyvale Medical Practice care for a group of patients, and need an electronic medical record system.  These doctors see some patients with Alzheimers disease, which causes their brains to lose volume over time (i.e., their brains shrink).  To track brain volume, Sunnyvale has a MRI (magnetic resonance imaging) machine. The 3-dimensional images (also called MRIs) it produces can be viewed by a doctor and analyzed by computer applications (e.g., to help compute total brain volume).

 

Information Requirements:

                  A patient can visit multiple doctors, and a doctor can be visited by multiple patients; each visit has a date and appointment time.  However, each a patient has exactly one primary doctor.  Doctors are identified by their doctor number and have a name and specialty.  Patients are identified by a 6-digit medical record number and have a name, gender, birthdate, and zipcode.  Names are additional unique identifiers for both doctors and patients.

                  The medical record of a patient is composed of a set of Visit_Records, which are uniquely identified for each patient by the date of that visit and the appointment time.  Each Visit_Record records which doctor was visited, and the height, weight, pulse, blood pressure, and current_diagnosis of the patient.

                  A patient may have one or more MRIs made in a visit, which become a part of the resulting Visit_Record.  An MRI has a date and time it was made, a pulse sequence (one of T1, T2, or PD), and a brain_volume.  An MRI may be processed to reveal more information, resulting in a new MRI derived from the first one.  Each processing has a type (e.g., register, tissue_classify).  Processed MRIs are part of the visit record of the MRI they are derived from.

 

Assignment Part 1:

  1. Translate the information requirements into an ER diagram, using the techniques we learned in class.  Record information which cannot be modeled, and any assumptions you make, at the bottom of your diagram.  We will discuss the diagram in class on 3/26, and choose a single class ER diagram.
  2. Derive a relational schema from the class ER diagram.
  3. Implement a relational database in Oracle using your schema.
  4. (You will add tuples to your database, as described later).
  5. (You will write and execute a set of queries, as described later).

 

Due date: Thursday 4/17 (April 17th)

Turn in paper copies of a) your ER diagram, b) your relational schema, c) your Oracle script including CREATE TABLE statements, INSERTs for tuples, queries (SELECT statements), and query results.  Electronically submit your script to the GTA as well.

 

Extra Credit (Due April 17th as well)

Note that you may choose this extra credit assignment, or the redoing of your midterm, but only one.  This is worth a maximum of 7 midterm points.

Turn in a short (e.g., 2-3 page) paper discussing the ambiguities in the Information Requirements section above, and possible variants of your ER diagram based on theses ambiguities.  Discuss how these might (or might not) impact the final relational tables.  In your opinion, how forgiving is the ER methodology?  That is, if the textual description is somewhat ambiguous, how likely are we to get the final database ÒrightÓ?

A reasonable effort will receive at least 4 points.  Excellence in coverage, insight, clarity will earn more points.