CS 4250: Database Management Systems

Fall 2025 - Homework 2

Due 10/14/2025 Tuesday, at midnight

I strongly recommend starting work on this well before the midterm, to prepare you for SQL mini-quizzes.

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. Typed, in a plain text, PDF or MS Word document.

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

Put your own name at the top of your own homework answers.

Consider the following relations:

Shuttle(shuttleID: integer, shuttlename: string, launchdate: date, enddate: date, currentlocation: string, massKg: integer)
Flight(flightID: integer, shuttleID: integer, plannedlaunchdate: date, actuallaunchdate: date, returndate: date, landingsite: string, commanderID: integer)
FlewOn(flightID: integer, astroID: integer, role: string);
Astronaut(astroID: integer, lastname: string, firstname: string, birthdate: date, deathdate: date, country: string)

The relations capture some basic information about exploring the USA space shuttle program. For more information about this, the "13 Minutes" podcast series by the BBC World Service, could be a place to start. (Be aware that the episodes about the Challenger are sad. But engineers need to face our failures, not ignoring them in favor of our successes.)

shuttleID in Flight is a foreign key referencing Shuttle.shuttleID.
commanderID in Flight is a foreign key referencing Astronaut.astroID.
flightID in FlewOn is a foreign key referencing Flight.flightID; astroID in FlewOn is a foreign key referencing Astronaut.astroID.

Country, in Astronaut, is the name of the country the astronaut flew for. (A German astronaut might fly for ESA, an astronaut might be born elsewhere and fly for the USA, etc.)

The mass of space shuttles is stored in kilograms (kg).

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 last and first names of astronauts who flew in space flights in the role of "Mission Specialist 2 Flight Engineer".
2. Find the earliest birthdate of an astronaut.
3. Find the flight identifiers, and the last and first names of the astronauts that flew on each flight, for flights that actually launched after January 1, 2000.
4. Find the shuttle identifier, shuttle name, flight identifier and planned launch date for each shuttle flight that has some astronaut flying on the flight in the role of "Payload Specialist 1".
5. For each country, list the name of the country and the number of astronauts who flew for that country.
6. Find the first and last names of astronauts, and their countries, for astronauts whose first name starts with "Ulf", or who have "th" anywhere in their first name. (Samantha, Timothy, Catherine, ...)
7. Find the names and masses of all space shuttles whose mass is larger than the average space shuttle mass. Show the output alphabetized by shuttle name.
8. Find the names of shuttles and the first and last names of astronauts who flew on them, for shuttles (and astronauts) where a flight of that shuttle landed at Edwards. (Edwards Air Force Base, mostly located in Kern County, CA.)
9. For each shuttle identifier, list the shuttle identifier, how many flights that shuttle flew, and the date the last flight returned to Earth. Only list information for shuttles that flew more than 30 flights.
10. List flight identifiers and actual launch dates for the flight, plus the name of the shuttle, plus the last and first names, and roles, of all the astronauts on the flight. Include only flights that returned to Earth on or before December 31, 1999. Output the information sorted by the actual launch dates for the flights.
11. Find and list pairs of astronaut identifiers, last and first names, where the two astronauts flew on a flight together, and one of them flew for the country named "Canada". (Like Marc Garneau, first Canadian in space.) Show the output sorted by the last name of the person who flew for Canada.
12. For each astronaut, list their astronaut identifier and how many flights they flew on, for astronauts who where born before 1965. Only include astronauts who flew on more than one space shuttle flight.