CS 4250: Database Management Systems

Spring 2021 - Homework 2

Due 3/25/2021 (Thursday), 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 25 March 2021. 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))
ProjectLocations (projectID: integer, locationID: integer, startDate: date, endDate: date, supervisor: integer)
Location (locationID: integer, name: string, address: string, latitudeDeg: float(10,6), longitudeDeg: float(10,6), acreage: float(5,3)

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.
One project may involve work at many job sites -- the upgrade of all stoplights in a town, for example, or the installation of sensors to monitor power distribution lines all over a city.
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.
ProjectLocations records which locations a project is working at, and which engineer is supervisor at that project site. supervisor is a foreign key referencing Engineer:engineerID.
Location stores information about project locations. The information includes latitude and longitude. (For example, Modesto's latitude and longitude are 37.661389, -120.994444. Santiago, Chile's latitude and longitude are -33.45,-70.666667.)

The database is using the geo URI scheme for latitude and longitude. For latitude, 0 is at the Equator, locations south of the Equator have negative latitude, and locations north of the Equator have positive values.

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 largest monthly budget for a project.

  2. Find name, address and phone number of companies where the company name has "charity" anywhere in it, or the name has "foundation" at the end of it, with at least one character before the 'f'.

  3. Find the project names and end dates for all projects contracted by the customer with customer identifier of 1234.

  4. Find the end date and supervisor identifier of project work at a particular location, where the name of the location is "Gallo Center for the Arts."

  5. Find the last names and job titles of engineers who work on some project, and the hours per week each engineer works on each project.

  6. Find the names and addresses of project locations, where the location is along the most southern latitude of any of the project locations. (The most southern latitude would be the smallest latitude in the database.)

  7. For each Professional Engineer Exam title, print out the title and the earliest and latest dates engineers passed that PE exam.

  8. Find the names of projects and the names of the customers paying for those projects, where the project start date is after "1 March 2020". The resulting names should be alphabetized by customer name.

  9. Find and list pairs of project identifiers, the location identifier, and the corresponding pairs location supervisors for each projects's work at each location. The paired projects both must have work at the same location. Print the list in sorted order, by location identifier. (For example, one project might be reinforcing building supports in the basement, while a different project is installing new elevators at the same location. Two projects, two supervisors, one location.)

  10. For each customer paying for projects, print out the customer identifier and how many projects that customer is paying for, and the largest total budget for a project that customer is paying for.

  11. Find the engineer last name and first names, for engineers who are supervisors on a project location. Also print the name and address of the project location the engineer supervises, and the name of the project. Only print data for projects where the work at the project location will end before 31 December 2021.

  12. For each customer paying for more than 5 projects, list the customer identifier and name, the count of how many projects that customer is paying for, and the sum of all the monthly budgets being paid for by that customer.