CS 4250: Database Management Systems

Spring 2023 - Homework 2

Due 3/16/2023 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. Nor may you submit the work of software programs as your own.

Upload to the CS Homework system or email to the professor on or before midnight on 16 March 2023. Typed, in a plain text, PDF or MS Word document. (Subject line: "cs4250, hwk 2")

(Typed homework, in all cases. DO NOT submit a photograph of handwritten scribbles.)

Consider the following relations:

Engineer (engineerID: integer, lname: string, fname: string, jobtitle: string, email: string, proExamID: integer, proEngineerExamPassDate: date)
Project (projectID: integer, name: string, hoursAWeek: integer, numVolunteersNeeded: integer, blurb: string)
VolunteerEngineers (projectID: integer, engineerID: integer, hoursPerWeek: integer, startDate: date, endDate: date)
ProjectTravel (projectID: integer, engineerID: integer, tripname: string, country: string, city: string, startDate: date, endDate: date )
EngineerExam (examID: integer, proEngineerExamTitle: string, passRate: numeric, takersLastYear: integer)
EngineerExamBook (bookID: integer, examID: integer, title: string, author: string, yearpublished: integer)

The relations capture some basic information about a engineers, who volunteer on projects, and who are studying for the professional licensing exams.

Engineers have job titles, and some engineers have passed Professional Engineers exams; proExamID is a foreign key referencing examID in the EngineerExam table.
Projects are projects engineers can work on, such as the volunteer opportunties available through Engineers Without Borders USA.
VolunteerEngineers keeps track of which engineers work on which volunteerprojects. Both key fields are foreign keys to the appropriate tables. hoursPerWeek keeps track of the hours per week the engineer is volunteering on this project, and start and end date track when the engineer began and ended work on the project.
EngineerExam stores data about Professional Engineers exams in specific fields of engineering (civil, chemical, fire protection, etc).
EngineerExamBook tracks books that young engineers may study from, while trying to pass their professional engineer exam, and also building experience via volunteer work. examID is a foreign key referencing the primary key of EngineerExam.

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 volunteer project that asks for the fewest number of hours a week.

  2. Find the title and year published of books to help study for the engineer exam, where the exam is titled "PE Fire Protection".

  3. Find the names of projects and the number of volunteers they need, for projects that include the word "water" anywhere inside the blurb, or that include the word "power" anywhere inside the blurb except just at the start. There must be at least one character before "power" in the blurb.

  4. Find the trip names, city names and start dates for for traveling trips that go to a country named "Uganda."

  5. Find the last and first names of engineers, and trip names, for engineers who have taken trips as part of work on projects, where the project needs more than 100 volunteers.

  6. Find the professional engineer exam titles and exam pass rates for PE exams who had the smallest numbers of people taking the exam last year. (Keep in mind that more than one PE exam might have the same, small number of takers in a year.)

  7. For each project that involves travel / trips, print out the project identifier and how many engineers have gone on trips for that project.

  8. Find the engineers' last and first names, project identifiers, project names, and dates the engineer started work for that volunteer project. We are only interested in engineers whose job title is "Supply Chain Engineer." The resulting names should be alphabetized by project identifier.

  9. For each professional engineers exam, print out the exam identifier and name, plus a count of the number of books available to study for that exam, and the earliest year a book that could be used to study for that exam was published. The resulting list should be sorted by exam name.

  10. Find and list pairs of engineer identifiers, plus their first and last names, for engineers who have the same job title and have passed the same professional engineer exam. (Two engineers, same exam and job titles.)

  11. Find the engineer last name and first names, and the titles of the professional engineers exam the engineers passed, and the names of the projects those engineers volunteer on, plus the dates the engineers started volunteering on the projects. The results should only include engineers who passed their professional engineers exam earlier than 2010. Present the results sorted by the titles of the professional engineers exams.

  12. For each volunteering project, find the project identifier and the number of engineers volunteering on that project, along with average number of hours per week those engineers are volunteering on the project. Only projects that have more than 10 volunteers working on them should be in the results.