CS 4250: Database Management Systems

Fall 2016 - Homework 2

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

(Added Oct. 5: Question A may be handwritten on a hardcopy.)


Question A

  • Suppose a database has the following three relations.
    Student (sid: integer, fname: string, lname: string, level: string)
    Like (studentId: integer, movieId: integer)
    Movie (mid: integer, title: string, director: string, releaseDate: date, genre: string)
    "studentID" in Like is a foreign key referencing "sid" in Student. "movieID" in Like is a foreign key referencing "mid" in Movie.

    Write the following queries in relational algebra.

    1. Find the student ids and last names of students that have the first name "Buttercup".
    2. Find the first and last names of students who like the movie with movie identifier equal to 42.
    3. Find the titles and directors for movies that are of the "comedy" genre.
    4. Find the first and last names of students who like movies in the "comedy" or "action" genres.
    5. Find the last names of students at the sophomore level that like a movie titled "Frozen".
    6. Find the last and first names students that like a movie directed by "Rob Reiner" and also like a movie directed by "Chris Buck".

    Question B

    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 containing lab 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 first and last names of all doctors who are older than 60.
    2. Find the average budget of hospital departments.
    3. Find the name (or names) of the oldest patients.
    4. Find the date and time every medical test was given, for the medical tests which require a fMRI machine.

    5. Find the last and first names all patients who have "Mukherjee" as their primary doctor. The results should be alphabetized by last name (of patient).
    6. Find the nurse IDs and last names of all nurses, where the database contains two nurses with that same last name, but different nurse IDs. (Possible siblings, spouses, etc.)
    7. Find the patient IDs, last and first names of patients whose primary doctors work in the "Pediatrics" department. Also include the doctor's last name. The results should be sorted by last name.
    8. For each medical machine needed for tests, print out the machine name and the average cost of tests given using that machine.
    9. Find the names and doctor IDs for doctors whose names have a "sh" sound in them, either at the beginning or the end of the name. (To help answer patient questions like, "I don't remember the name fo the doctor I talked to, but it started with a shhhhhh sound. Or maybe it ended with a shhhh sound. I don't remember which.")
    10. Find the doctor IDs, last and first names and ages for doctors whose age is oldest. (Hint: there may be more than one doctor of the oldest age. Your query should return all of them.)
    11. List the department IDs and department names of each department and the number (count) of doctors that are assigned to that department. The results should be alphabetized by the department names.
    12. Find the last and first names and nurse IDs of the nurses who are younger than a nurse whose last name is "Nightingale."

    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.