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.