CS 4250: Database Management Systems

Spring 2016 - Homework 2

Due 3/24/2016, at 5 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, submitted during class time on 3/24) or email a plain text, MS Word or PDF document to the instructor with subject line "cs4250,hw2" (by 5pm).

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 relations capture some basic information about a hospital. 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 relation 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 particular DBMS software.

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

  1. Find the name of the department with the biggest budget.
  2. Find the names and costs of all tests that need an "X-ray" machine.
  3. Find the date and time every medical test was given, for the medical tests ordered by a doctor with last name "Lister".
  4. Find the last and first names and ranks of all nurses who work in the Oncology department. The results should be alphabetized by last name.
  5. Find the last and first names of all patients who have a medical test that requires a machine called an "audiometer". Also include in the results the dates the tests were given. The results should be sorted by the test dates.
  6. For each nursing rank, print out the rank and the average age of nurses who hold that rank.
  7. Find the last and first names and doctor IDs of the doctors who are older than a doctor whose last name is "Mukherjee."
  8. Find the nurse IDs, last and first names and ages for nurses whose age is youngest. (Hint: there may be more than one nurse of the youngest age; newly hired nurses may have similar ages. Your query should return all of them.)
  9. List the first and last name of each patient and the number (count) of medical tests that have been ordered for that patient. The results should be alphabetized by the last names of the patients.
  10. List all patient IDs, patient last and first names, doctor IDs, doctor last names, medical test IDs, medical test names and the dates the tests were given to each patient for tests ordered by a doctor named "Mukherjee" (last name).
  11. Find the names of all nurses who have first names that start with a "Fl" or who have last names that end in "gale".

Notes: Dr. Siddhartha Mukherjee is the author of "The Emperor of All Maladies: A Biography of Cancer". 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.