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.