CS 4250: Database Management Systems

Spring 2024 - Homework 2

Due 3/25/2024 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.

You may turn in hardcopy (typed, submitted during class time on the day before the due date) or upload to Canvas or email to the professor on or before midnight on 25 Mar 2024. 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, 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 average number of hours a week engineers work for teams.
2. Find the official and nicknames of locations in the solar system that are further away, at their maximum distance, than 225,000,000 kilometers. (225 million km is, very approximately, the average distance of Mars from Earth.)
3. Find the team names, project names, and project sponsor organizations for teams that work on projects where the estimated cost of the project is more than $100,000,000.
4. Find the team names, engineer identifiers, the dates engineers started work on the team, space project names and the launch date for the space project, for engineers working on teams, where the team is working on the space project and the sponsor organization for the project is JAXA. (Japan Aerospace Exploration Agency is nicknamed JAXA. In simple terms, Japan's version of NASA.)
5. Find the space projects -- name and project identifier -- with "robot" anywhere inside the description of the project, or projects that have "Apollo" somewhere in the description, but with at least one character after and before "Apollo". (For example, "The Apollo Program...", "On the Apollo Soyuz...", etc.) (Added "name and project identifier" on 3/25, after an observant student asked about it in class.)
6. Find the solar system location's official name and nickname, for the solar system location that has the smallest of all minimum distances to Earth. (Bear in mind that more than one small object -- like an asteroid -- might have the smallest minimum distance to Earth. The database does not track if the objects are at that minimum distance at the same time, or centuries apart.)
7. For each organization that sponsors space projects, print out the name of that sponsoring organization and the estimated cost of the most expensive space project that organization is sponsoring.
8. Find the last and first names of engineers, and the names of the teams the engineers work on, where the engineer has a job title of "Aerospace Engineer". Present the results sorted by the engineer last names.
9. Find and list pairs of engineer identifiers, where the engineers are both members of the same team and have the same role on that team. (Both are "junior software developers" or both are "quality assurance engineers", and so on.) Two enginers, same team.
10. Find the engineer identifiers and how many hours a week each engineer works on a team, and the name of the team, for teams working on space projects where the destination of the project is nicknamed "Titan". (Interesting trivia -- the official designation of Saturn's moon, commonly called Titan, is "Saturn VI". It is larger and heavier than Earth's own moon, and is the only moon in our solar system with a dense atmosphere.)
11. For each solar system location that is more than 1,200,000,000 km from Earth at its minimum distance, report the official name of the solar system location, how many space projects are will be sent to that destination and the total estimated cost of all those projects together. The resulting list should be sorted by official names of the locations. (Some web sites say Saturn's distance to Earth is, at closest, about 1.2 billion km.)
12. For each team, report the team identifier and a count of how many engineers are on the team and the earliest data any engineer started work on the team. Only teams with more than 4 members should be in the results.