CS 4250: Database Management Systems

Fall 2019 - Homework 2

Due 10/21/2019, at midnight

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 the due date) or email a plain text, MS Word or PDF document to the instructor with subject line "cs4250,hw2" (by midnight).

(Typed homework, in all cases. Under no circumstances should you email me a photograph of handwritten scribbles.)

Consider the following relations:

Patient (pid: integer, lname: string, fname: string, primary_did: integer, weight: integer, height_in: integer, age: integer)
Doctor (did: integer, lname: string, fname: string, deptid: integer, age: integer, salary: integer)
Nurse (nid: integer, lname: string, fname: string, deptid: integer, rank: string, age: integer)
Department (deptid: integer, name: string, budget: integer)
Exam (examid: integer, pid: integer, did: integer, examday: date, purpose: string, heartrate_bpm: integer, bloodpress_sys: integer, bloodpress_dia: integer, notes: string)
Vaccination (vaccinationid: integer, pid: integer, vaccinationname: string, dategiven: date, dateexpires: date)
Prescription (presid: integer, pid:integer, medname: string, quantity: integer, timesPerDay: integer, withfood: bit, writtenby: integer)
MedsGiven(presid: integer, timegiven: timestamp, givenbynurse: integer, notes: string)

The relations capture some basic information about a hospital.
Patients have primary physicians (primary_did). (Other doctors may write prescriptions or give exams, for many reasons.)
Doctors work in departments (deptid) (oncology, pediatrics, etc).
Patients are examined by doctors. pid in Exam is a foreign key referencing Patient, and did references Doctor.
Patients also have vaccinations (pid in Vaccination references Patient).
Patients may have prescriptions and, of course, each prescription must be written by a doctor. writtenby in Prescription references did in the Doctor relation.
Nurses give medicines based on prescriptions, and the database records the time each medicine is given (timegiven) and which nurse gave it (givenbynurse).

Blood pressure is recorded as two numbers, the systolic and diastolic numbers (measured in mm Hg -- millimeters of mercury).

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

You may make the simplifying assumption that comparisons on "date" or "timestamp" 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.

  1. Find the average systolic blood pressure of patients during exams.

  2. Find the patient identifiers (IDs) and their systolic and diastolic blood pressures for all patient exams where the purpose of the exam is "check-up".

  3. Find the first and last names, and ages of patients that have had a vaccination, where the vaccination is named "rinderpest".4

  4. Find the prescription IDs and times that the medicine was given, for all times a medicine was given and the notes contain the word "nausea" anywhere, or the notes contain "headache" one character from the end of the notes. ("headache.", headaches", "headache!", and so on)

  5. Find the purposes of exams, the last and first names of doctors who give the exams, and the names of the departments those doctors belong to.

  6. Find the first and last names of patients and the first and last names of their primary doctors, where the age of the primary doctor is the oldest age among all the doctors.

  7. Find the first and last names of patients, and the first and last names of their primary doctors, for patients that are older than 70. The results should be alphabetized by doctor last name.

  8. For each day on which exams were given, print out the exam day, and the and the lowest systolic blood pressure and the lowest diastolic blood pressure out of the patients examined on that day.

  9. For each exam purpose in the database, list the purpose and the number of exams that have been given for that purpose to patients older than 50.

  10. Find nurse last and first names, and pairs of nurse IDs, where the nurses are different people but work in the same department and one of the nurse's is at least 5 years older than the other nurse.

  11. Find the prescription IDs, nurse IDs and patient IDs of prescriptions, where the patient last name is "Mukherjee" and the nurse last name is "Nightingale" and the nurse has given the prescription to the patient at least once. Results should be sorted by the medicine name of the prescription.2,3

  12. For each patient who has had more than ten exams, list the patient ID, the total number of exams that patient has had, and the lowest heartrate recorded for that patient during exams.

Notes:
1 Rinderpest is one of the very few diseases we have eradicated from the Earth, a triumph of 1900's vaccinations. It is also a disease of cattle, so this question is a little joke. (Humans didn't get vaccinated for rinderpest, since the rinderpest virus ignored us.)
2 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.
3 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.)
4 Dr. Jonas Salk led the team that developed the first effective polio vaccine. (At the time, polio was killing more than 3,000 and paralyzing more than 20,000 people each year. Mostly children.)