CS 4250: Database Management Systems

Fall 2017 - Mini-Homework 1

Due Thursday, 10/26/2017, at start of class

This is a GROUP assignment. Groups of 3 highly recommended. (Groups of 2 are acceptable, as well.)

You may email a plain text, MS Word or PDF document to the instructor with subject line "cs4250,mini1", or turn in a typed hardcopy. Put the names of all group members on what you submit.

  • Recall the database you used in Homework 2. Some minor additions have been included.

    Patient (pid: integer, lname: string, fname: string, primary_did: integer, age: integer, insuranceNum: string, insuranceComp: string)
    Doctor (did: integer, lname: string, fname: string, deptid: integer, age: integer, salary: integer, ssn: string)
    Nurse (nid: integer, lname: string, fname: string, deptid: integer, rank: string, age: integer, ssn: string)
    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.

    ssn is short for Social Security Number. insuranceNum is the number of a patient's medical insurance account, and insuranceComp is the company that provides that insurance.

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

    Your task: List all the functional dependencies that hold true over this database.

    Notes:
    You do not need to list trivial functional dependencies. (e.g, A -> A)
    You also do not need to list dependencies redundantly. (i.e., if you already listed A -> B, you do not need to also list AC -> B, AD -> B, AE -> B, and so on.)

    ABC -> DE is shorthand for {A, B, C} -> {D, E}