CS 4250: Database Management Systems

Fall 2021 - Homework 2

Due 10/11/2021 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 11 October 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)
BordersTrip (tripID: integer, engineerID: integer, country: string, town: string, startDate: date, stopDate: date, charityName: string, donation: integer, description: string)

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.)
BordersTrip records which engineers (engineerID is a foreign key referencing Engineer) are scheduled to go on trips for Engineers without Borders USA, when and where. The table also records how much money the company might have donated to a charity involved in the trip. (EWB frequently partners with charities when developing and deploying charitable projects.)

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 smallest salary for an engineer.
  2. Find the project identifiers and engineer identifiers for all projects where that engineer is assigned to work more than 30 hours per week on that project.
  3. Find the location names and addresses, for locations that have projects with starting dates after "20 September 2021" at that location.
  4. Find name, email address and job title of engineers where the first name starts with "Pe" and has at least one other letter in it (Pea, Peter, Pearl, Percy) or the last name has a "ark" anywhere in it. (Added "email" to "address" on 4 Oct.)

  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 first and last names and job titles of engineers, where the engineers' salaries are smaller than the average engineer salary.
  7. For each country that engineers take Engineers Without Borders trips to, print out the country and the earliest start date and latest stopping date of EWB projects in that country.
  8. Find the names of projects and the names of the customers paying for those projects, where the project total budget is bigger than $1,000,000. The resulting names should be alphabetized by project name.
  9. Find and list pairs of project identifiers, the location identifier, and the corresponding pairs of location supervisors for each projects's work at each location. The paired projects both must have work at the same location. (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 location where projects are happening, print out the location name and address, and how many projects are happening at that location.
  11. Find the engineer last name and first names, and the project identifiers and hours per week that engineer works on that project, for projects where the customer for that project is named "Defense Advanced Research Projects Agency." Present the results sorted by engineer's last name.
  12. For each project location with more than three projects at that location, list the location identifier and name of the location, the number of projects at that location, and the earliest start date for any project at that location.