CS 4250: Database Management Systems

Spring 2026 - Homework 2

Due 3/13/2026 Friday, 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)
ExperienceWith(astroID: integer, officialair: string, experiencetype: string, yearstart: integer);
Airplane(officialair: string, nickname: string, manufacturer: string, firstflight: date, numberBuilt: integer, planetype: string);

The relations capture some basic information about the USA space shuttle program and astronauts. 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.
astroID in ExperienceWith is a foreign key referencing Astronaut.astroID. officialair in ExperienceWith is a foreign key referencing Airplane.officialair. (Astronauts have had experience with airplanes as pilots, instructors, mechanics, etc.)

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.) officialair, in Airplane and elsewhere, is the full, official name of an airplane. 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 official names and nicknames of airplanes, and the type of plane, for airplanes that some astronaut has experience being a "test pilot" on.
2. Find the average mass of a space shuttle.
3. Find the official names of airplanes and the number built, where an astronaut has experience on that airplane as an "instructor pilot", and that astronaut flew for the country of Italy.
4. Find the last and first names of astronauts, where the astronaut flew on a shuttle flight in the role of "pilot" and also has experience as a pilot on an airplane with the official name of "F-14D Super Tomcat".
5. Find the airplanes whose official names include "Star" anywhere inside, or which start with "Grumman". (Wikipedia's fixed wing aircraft page... there are a lot!)
6. For each type of airplane, list the type of airplane and the earliest date a plane of that type achieved flight.
7. Find the official names and first flight date of the airplane(s) with the latest (last) first flight date. (Hint: There may be more than one airplane that first flew on that date.) Show the output alphabetized by official airplane name.
8. For each astronaut, list the astronaut identifier, how many flights that astronaut flew on, and the earliest actual launch date out of all their flights. Only list information for astronauts who flew on more than two flights.
9. For each space shuttle, list how many flights it flew on, including only space shuttles which had an original launch date before 1990. Only include shuttles that flew more than 20 flights.
10. Find the first and last names of astronauts who flew on a space shuttle flight that returned to Earth after 2005, and who had experience as "flight instructor" on some airplane. Show the output sorted by the last name of the astronaut.
11. List flight identifiers for shuttle flights, where some astronaut flew on the flight in the role of a "Mission Specialist 2 Flight Engineer". Also list the astronaut's last and first name, and the official name(s) and nicknames of the airplanes that astronaut has experience as "pilot" of. Output the information sorted by the flight identifiers of the shuttle flights.
12. Find and list pairs of official airplane names, where both airplanes have some astronaut who has experience flying the plane as a "test pilot". (Two airplanes, both with astronaut test pilots. It is likely the two test pilots are not the same person - they may or may not be and this will not affect the output of the query.)