CS 4250: Database Management Systems

Spring 2015 - Homework 2

Due 3/18/2015, at 4 pm

This is an individual assignment. All work must be your own. You should not look at any other student's work (in whole or in part, on paper or on screen), nor allow anyone else to look at yours, during the course of this assignment.

You may turn in hardcopy (typed) or email a plain text, MS Word or PDF document to the instructor with subject line "cs4250,hw2".

Consider the following relations:

Patient (pid: integer, lname: string, fname: string, primary_did: integer, age: integer)
Doctor (did: integer, lname: string, fname: string, deptid: integer, age: integer)
Nurse (nid: integer, lname: string, fname: string, deptid: integer, rank: string, age: integer)
Department (deptid: integer, name: string, budget: integer)
TestInfo (testid: integer, name: string, cost: integer, machine_needed: string)
MedTest (testid: integer, pid: integer, dateOrdered: date, whoOrdered: integer, dateGiven: date, timeGiven: time, nid: integer, techID: integer)

The meaning of the relations should be straightforward. Doctors order tests for patients. (MedTest:whoOrdered is a foreign key referencing Doctor:did.) Tests are given by particular nurses and processed by particular lab technicians. Patients have primary care physicians. Doctors work in departments (oncology, pediatrics, etc). Nurses have ranks, like "nurse practitioner", "registered nurse", "licensed practical nurse". (The tasks nurses may legally perform vary by rank.) Some medical tests may require the use of a certain kind of machine -- microscope, MRI machine, audiometer for hearing tests, etc.

The full table of technician information (names, etc) has not been presented here.

(did = doctor ID. lname = last name. pid = patient ID. nid = nurse ID. Etc.)

You may make the simplifying assumption that comparisons on "date" fields will work with basic =, <, >, etc, boolean operators. This is not the case in many DBMS products; for real work you would need to consult the documentation for your DBMS product.

Write the following queries in SQL. No duplicates should be printed in any of the answers.

  1. Find the last and first names of all nurses who are 60 years old.
  2. Find the cost of the least expensive medical test.
  3. Find the names and costs of all medical tests which were ordered on Feb. 28, 2015.
  4. Find the last and first names and ages of all doctors who work in the Pediatric department. The results should be alphabetized by last name.
  5. For each department identifier, print out the department identifier and the number of doctors who work in that department.
  6. Find the last and first names of all patients who have a medical test ordered by some doctor in the Oncology department. The results should be alphabetized by the last names of the patients.
  7. Find the patient IDs, last and first names and ages for patients whose age is oldest. (Hint: there may be more than one patient who is of the oldest age; imagine the hospital is near a facility for seniors. Your query should return all of them.)
  8. Find the last and first names and doctor IDs of the doctors who are younger than a doctor whose name is "Lister."
  9. List the first and last name of each doctor and the number (count) of patients who have that doctor as their primary care doctor. The results should be alphabetized by the last names of the doctors.
  10. Find the names of all departments whose names either start with a "P" or end with "ogy".
  11. List the last names and patient IDs for pairs of patients where the patients have the same primary care doctor and have the same last name (as each other, not as the doctor), but are not the same person (different IDs).
  12. List all patient IDs, patient names, doctor IDs, doctor names, medical test IDs, medical test names and the dates the tests were given to each patient for tests which require the use of a machine named "MRI".

Notes: Dr. Siddhartha Mukherjee is the author of "The Emperor of All Maladies: A Biography of Cancer". (He is, in fact, an oncologist, so he would not be anyone's primary care physician.) Florence Nightingale more or less created the field of modern nursing... and made interesting contributions to statistics and the visualization of scientific data, as well. Dr. Joseph Lister pioneered the use of antisceptics during surgery.