CS 4250: Database Management Systems

Fall 2022 - Homework 2

Due 10/10/2022 Monday, 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 10 (new!) October 2022. (old: 7 Oct) Typed, in a plain text, PDF or MS Word document.

(Typed homework, in all cases. DO NOT upload a photograph of handwritten scribbles.)

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 may be 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 biggest salary for an engineer.
  2. Find the title and status of all standards that being developed by a working group whose sponsoring organization is named "IEEE".
  3. Find the names, start and end dates of projects, for projects with total budgets bigger than $1,000,000.
  4. Find the names of customer companies, and their address and phone numbers, where the name of the contact person at that company ends with "gil", or the name of the contact person contains "del" anywhere inside the name, except at the end. There must be at least one letter in the name after the "del".

  5. Find the last and first names, and professional engineer exam titles of engineers who work on a project supervised by the person with a manager ID number equal to 123.
  6. Find the working group identifier and title, and annual budget, for working groups where the annual budget of the working group is bigger than the average annual budget for working groups.
  7. For each engineer who is a member of working groups, print out their engineer ID and the earliest date they started work in a working group.
  8. Find the engineer last and first names, project identifiers and hours per week the engineer works on that project, and working group identifier, for engineers who work on both projects and working groups, where their role in the working group is "Chair". The resulting names should be alphabetized by engineer last name.
  9. Find and list pairs of project identifiers and project names, plus the customer identifier for the customer paying for the project, where both projects are sponsored by the same customer. (Two projects, one customer.)
  10. For each engineer, print out the engineers identifier and last name, plus a count of the number of projects that engineer works on, and the total hours per week the engineer is assigned to project work. The resulting names should be sorted by engineer identifier.
  11. Find the standards identifiers, and number of working groups that have worked on that standard, and latest/last start date for any of those working groups. Only standards that have two or more working groups working on that standard should be in the query results.
  12. Find the engineer last name and first names, and the working group identifiers and working group titles, and the project identifiers where the engineer is a member of the working group and engineer also works on the project. The engineer's role in the working group should be "Treasurer". Present the results sorted by working group title.