CS 4250: Database Management Systems

Fall 2017 - Homework 2

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

(Question A may be handwritten, if submitted on a hardcopy.)


Question A

  • Suppose a database has the following relations.
    Students (sid: integer, fname: string, lname: string, gpa: real)
    StudentsInClubs (studentId: integer, clubId: integer)
    Hobbies (hobbyid: integer, name: string, monthlyCost: integer, team: bit)
    Clubs (cid: integer, clubName: string, hobby: integer, numClubMembers: integer, started: date, dues: integer)

    (The "bit" data type stores 0 or 1, corresponding to false -- not a team hobby -- or true.) "hobby" in Clubs is a foreign key referencing "hobbyid" in Hobbies. "clubId" in StudentsInClubs is a foreign key referencing the primary key of Clubs; "studentId" in StudentsInClubs is a foreign key referencing the primary key of Students. The monthly cost of a hobby is not the same as the club dues. (For example, one might pay dues to belong to a cooking club, and also pay monthly costs to buy sugar and flour.)

    Write the following queries in relational algebra.

    1. Find the club IDs and club names of clubs that have more than 50 members.
    2. Find the names of hobbies for teams.
    3. Find the last and first names students that belong to a club named "Gregory Hines Fans" and also to a club named "B-Boy / B-Girl".
    4. Find the club IDs and club names for clubs devoted to the hobby named "tap dancing" or to the hobby named "ballet".
    5. Find the student IDs of students who belong to some club devoted to a team hobby (team = 1).
    6. Find the first and last names of students who belong to some club with club dues less than $20.

    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, salary: 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)
    MedTech (techid: integer, lname: string, fname: string, salary: 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 medical 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.

    1. Find the maximum salary of a medical technician.
    2. Find the first and last names of all nurses who have ages between 40 and 60.
    3. Find the names of the departments that employ doctors with salaries greater than $100,000.
    4. Find the first and last names of nurses who work in the Optometry department.
    5. Find the first and last names and salaries of doctors which are paid more than the average doctor salary.
    6. Find the names of all nurses whose names either start with a "McD" or end with "y".
    7. Find the last names and ages of all doctors who work for the Surgery department. The results should be alphabetized by last name.
    8. For each machine used to give medical tests, print out the name of the machine and the average cost of medical tests that use that machine.
    9. Find the patient IDs, last and first names of patients who have primary doctors who work in the Oncology department. Include the doctor's last name, and results should be sorted by the doctor's last name.
    10. Find the doctor IDs, last and first names of all pairs of doctors, where the doctors have the exact same salary. (But are not the same doctor, of course.)
    11. For each medical technician that has processed more than ten medical tests, print out the medical technician's ID number and the total number of tests that technician has processed.
    12. Find the last and first names and doctor IDs of the doctors who are younger than some doctor whose last name is "Mukherjee".

    Notes: Dr. Siddhartha Mukherjee is the author of "The Emperor of All Maladies: A Biography of Cancer".