CS 4250: Database Management Systems

Spring 2022 - Homework 2

Due 3/24/2022 Tuesday), 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.

Upload to the CS Homework system on or before midnight on 24 March 2022. Typed, in a plain text, PDF or MS Word document.

Consider the following relations:

Engineer (engineerID: integer, lname: string, fname: string, jobtitle: string, email: string, proEngineerExamTitle: string, proEngineerExamPassDate: date, salary: integer, vacationDaysAccumulated: integer)
Project (projectID: integer, name: string, startDate: date, endDate: date, customerID: integer, monthlyBudget: integer, totalBudget: integer, managerID: integer)
ProjectEngineers (projectID: integer, engineerID: integer, hoursPerWeek: integer)
Customer (customerID: integer, name: string, address: string, phone: char(10), contactName: string, contactPhone: char(10))
WorkingGroup(workgroupID: integer, workGroupTitle: string, workingOnStandard: integer, sponsorOrgName: string, startDate: date, endDate: date, annualBudget: integer)
WorkingGroupMembers(workgroupID: integer, engineerID: integer, startDate: date, endDate: date, role: string)
Standards(standardsID: integer, standardTitle: string, status: string, approvalDate: date, replacesOldStandard: integer, price: integer)

The relations capture some basic information about an engineering consultant company. Engineers work on Projects for Customers.

Engineers have job titles, and some engineers have passed Professional Engineers exams in some specific field of engineering (civil, chemical, fire protection, etc).
Projects are performed for and paid by Customers, and customerID in Project is a foreign key referencing Customer. Projects are managed by Engineers, and managerID is a foreign key referencing Engineer:engineerID.
ProjectEngineers keeps track of which engineers work on which projects. Both key fields are foreign keys to the appropriate tables. hoursPerWeek keeps track of how many of the engineer's weekly hours are assigned to this project.
Customers are companies. Some employee at each customer company will be the official contact for that company.
WorkingGroups are groups of people who draft new technical standards and steer them through the standards approval process. A working group works on a standard (workingOnStandard is a foreign key referencing standardID in Standards). And a working group has a sponsoring organization. For example, in computing and electrical engineering, the IEEE sponsors a lot of standards development work, including the WiFi standards you are probably familiar with.
WorkingGroupMembers stores which Engineers are working on what WorkingGroups. workgroupID is a foreign key referencing WorkingGroup. engineerID is a foreign key referencing Engineer. Engineers start and stop work on a working group on particular days. (Some may serve during all the years the working group is active; some engineers might only serve for a few months.) Working groups members may serve with particular roles -- "Chair", "Vice Chair" and so on.
Standards stores data about standards. Standards might have different status values -- "draft", "approved" and so on. (If the standard is a draft, and not yet approved, its approvalDate will be null.) Some new standards are designed to replace older standards, and replacesOldStandard is a foreign key referencing standardID (in the same table) when one standard is intended to replace or has replaced another. For example, the "IEEE Health informatics- Personal health device communication- Part 10419: Device Specialization- Insulin Pump" standard IEEE 11073-10419-2015 has been superseded (replaced by) IEEE standard IEEE 11073-10419-2017.

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.

  1. Find the average annual budget for a working group.
  2. Find the last names and first names of engineers, and the dates they start working on a working group, when the engineers have the role "Treasurer" in the working group.
  3. Find the title and status of all standards approved before 1 January 1960.
  4. Find titles, standards identifiers, and prices of standards where the title contains "802.11a" anywhere inside the title, or the title starts with "ANSI" and contains at least one more letter in it after the "ANSI".

  5. Find the names and addresses of customers for projects where the monthly budget of the project is over $50,000.
  6. For each organization that sponsors standards working groups, print out the name of the sponsoring organization and the number of working groups they are sponsoring.
  7. Find the titles and standards identifiers, and price, for standards where the price of the standard is the maximum price for a standard.
  8. Find the engineer identifiers and engineer last names, working group identifiers and working group titles, where the engineer is a member of the working group and the engineer has taken the "PE Mechanical" professional engineers exam. The resulting names should be alphabetized by working group titles.
  9. Find and list pairs of working group identifiers and an engineer identifier where the engineer identified has worked on both working groups, in different roles. ("Chair" on one group and "Treasurer" on the other, and so on.) (Two working groups, two roles, one engineer.)
  10. For each project, print out the project identifier and project name, how many engineers work on that project, and the average hours per week the engineers work on that project.
  11. For each working group with more than five members, list the working group identifier and working group title, the number of members of the working group, and the latest (last) date that a member joined the working group.
  12. Find the engineer last name and first names, and the working group identifiers and working group titles, and the standards identifier and standards title, where the engineer is a member of the working group and the working group is working on the standard. Also, the working group should be sponsored by an organization named IEEE. Present the results sorted by standards title.