CS 4250: Database Management Systems

Spring 2017 - Homework 2

Due 3/14/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 3/14) 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. False or true. Booleans.) "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 polo club, and also pay monthly costs to feed and stable one's polo ponies.)

    Write the following queries in relational algebra.

    1. Find the hobby IDs and hobby names of hobbies whose monthlyCost is greater than $500.
    2. Find the first and last names of students whose GPA is greater than 3.0.
    3. Find the last and first names students that belong to a club named "Fred Astaire Fans" and also to a club named "Tchaikovsy Fans".
    4. Find the club IDs and club names for clubs devoted to the hobby named "tap dancing".
    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 more than ten members.

    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 average age of nurses.
    2. Find the first and last names of all patients who have ages between 21 and 40.
    3. Find the names of the departments that employ nurses of rank "registered nurse".
    4. Find the names and budgets of departments which have budgets greater than the average department budget.
    5. Find the first and last names of doctors who work in the Surgery department.
    6. Find the last and first names all nurses who work for the Oncology department. The results should be alphabetized by last name.
    7. Find the names of all departments whose names either start with a "S" or end with "ogy".
    8. Find the patient IDs, last and first names of patients who have had medical tests that required an "otoscope." Include the test identifying number, and results should be sorted by that number.
    9. Find the patient IDs, last and first names of all patients, where the database contains two patients with that same last name, but different patient IDs. (Possible siblings, spouses, etc.)
    10. For each department, print out the department name and the age of the youngest doctor who works for that department.
    11. For each department that has more than three nurses working for it, print out the department name and the total number of nurses that work for that department.
    12. Find the last and first names and doctor IDs of the doctors who are older than some doctor whose last name is "Lister".

    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.