CS 4250: Database Management Systems

Fall 2023 - Homework 2

Due 10/16/2023 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. Nor may you submit the work of software programs [or hardware devices] as your own.

Upload to the CS Homework system or email to the professor on or before midnight on 16 Oct 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.)

Note that you will need to be on campus, or on the campus VPN, to access the CS Homework system. Instructions on how to get onto the campus VPN are on the main page, under 'Campus Resources'.

Consider the following relations:

Engineer (engineerID: integer, lname: string, fname: string, jobtitle: string, email: string, speciality: string)
MemberOf (teamID: integer, engineerID: integer, role: string, hoursAWeek: integer, startedOn: date, stoppedOn: date)
Team (teamID: integer, name: string, creationDate: date, projectID: integer)
SpaceProject(projectID: integer, name: string, destination: string, launchDate: date, costEstimated: integer, description: string, sponsorOrganization: string)
SolarSystemLocation(officialname: string, nickname: string, type: string, distanceMinKM: integer, distanceMaxKM: integer)

The relations capture some basic information about a engineers, who work on teams that build machines to travel to various destinations in our solar system.

Engineers have job titles and specialities.
Engineers join and leave Teams during their careers. engineerID in MemberOf is a foreign key referencing engineerID in Engineers. teamID in MemberOf is a foreign key referencing teamID in Teams.
Multiple Teams cooperate to build a SpaceProject. projectID in Teams is a foreign key referencing projectID in SpaceProject.
For example, there could be a Communications Team working on the system that would let a satellite communicate with Earth, a Navigation Team working on the correct route to get the satellite to a particular planet, and a Shields Team working on protective measures to keep satellite equipment safe from micrometeorites, solar flares, and other threats. And so on.
SolarSystemLocations are where SpaceProjects are sent. destination in SpaceProject is a foreign key referencing officialname in SolarSystemLocation. Locations might be the asteroid 101955 Bennu, the planet Saturn, the moon Titan, the Moon, etc. The database tracks the minimum and maximum distances, in kilometers (KM) from Earth, of solar system locations, over the course of orbits around the solar system.

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 biggest estimated cost for a space project.

  2. Find the names, destinations and launch dates of all space projects sponsored by JAXA.

  3. Find the team names, engineer last names, and engineer first names for all engineers who are members of teams, where the creation date of the team was before 2010.

  4. Find the engineer identifiers, last and first names of engineers with "nasa" anywhere inside their email address, or whose specialities start with "Electr". There must be one or more more characters in the speciality description, after the "Electr". (For example, "electronic", "electrical", "electro-mechanical", and so on.)

  5. Find the team names, names of space projects, and estimated costs of space projects, for teams that work on projects destined to travel to a location nicknamed Pluto.

  6. Find the space project names and sponsoring organizations for the space projects whose estimated costs are greater than the average estimated cost over all space projects.

  7. For each of the different engineers job titles, print out the job title and how many engineers have that job title.

  8. Find the engineer identifiers and roles, and team names, for engineers that are on some team, where the team works on a project sponsored by ISRO. The resulting names should be alphabetized by team names. (ISRO is the Indian Space Research Organization, sponsors of the recent Chandrayaan-3 mission to the Moon.)

  9. Find and list pairs of team identifiers, plus the team names, for pairs of teams who work on the same project (have the same project identifier). (Two teams, same project.)

  10. For each team, find the name of the team, a count of how many engineers are members of the team, and the sum of all the hours the engineers are assigned to work on the team. The resulting list should be sorted by team name.

  11. For each location in the solar system, find the official name of the location, a count of the number of space projects travelling to that location, and the minimum cost of a space project travelling to that location. Only locations that have more than two space projects traveling to them should be in the results.

  12. Find the engineer last name and first names, and their job titles, and how many hours a week they work on each team they are a member of, plus the name of the team and the name of the space project that team works on, and also the launch date of the space project. The results should only include engineers (and their teams) working on projects that will launch after October 2023. Present the results sorted by the names of the space projects.