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.
You may turn in hardcopy (typed, submitted during class time on the day before the due date) or email a plain text, MS Word or PDF document to the instructor with subject line "cs4250,hw2" (by midnight).
(Typed homework, in all cases. Under no circumstances should you email me 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))
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 also go on Trips for
Engineers Without Borders USA activities,
as a way to give back to the world and their own communities.
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.
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.)
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.