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.
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.)