CS 4250: Database Management Systems

Fall 2024 - Homework 2

Due 10/14/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 14 Oct 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:

SpaceProject(projectID: integer, name: string, destination: string, launchDate: date, costEstimated: integer, description: string, sponsorOrganization: string)
SolarSystemObject(officialname: string, nickname: string, type: string, distanceMinKM: integer, distanceMaxKM: integer)
OrbitsAround(isInOrbit: string, isOrbited: string, orbitInDays: integer, distanceAvgKM: integer)
DiscoveredBy(object: string, teamID: string, discoverydate: date, maintool: string)
Team (teamID: integer, name: string)
MemberOf (teamID: integer, personID: integer, role: string, startedOn: date, stoppedOn: date)
Person (personID: integer, lname: string, fname: string, jobtitle: string, birthdate: date, deathdate: date);

The relations capture some basic information about exploring the solar system, including who was involved in discovering what objects, and projects that build machines to travel to various destinations in our solar system.

SolarSystemObjects are where SpaceProjects are sent. destination in SpaceProject is a foreign key referencing officialname in SolarSystemObject. 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 objects, over the course of orbits around the solar system.
OrbitsAround records the facts that the solar system object isInOrbit circles around isOrbited every orbitInDays days. Both isOrbited and isInOrbit are foreign keys referencing officialname in SolarSystemObject.
SolarSystemObjects were discovered by teams of people. (Some teams are just one person, like Galileo discovering the moons of Jupiter.) personID in MemberOf is a foreign key referencing personID in Person. teamID in MemberOf is a foreign key referencing teamID in Team.
DiscoveredBy has a foreign key, object, referencing SolarSystemObject officialname, and a foreign key, teamID, referencing Team teamID.

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 smallest minimum distance from Earth to a object in the solar system.

  2. Find the last and first names of people involved in the discovery of solar system objects and born before the year 1800. (For example, William Herschel discovered Uranus -- working from his back yard! His sister Caroline Herschel helped him build his homemade telescopes.)

  3. Find the official names, nicknames, and discovery dates for solar system objects discovered after 1950.

  4. Find the last and first names, and their roles on the team, of people who are members of a Team named the "James Webb Telescope Team."

  5. Find the official names, nicknames and types of objects in the solar system where the types end in "oid" (meteoroid, asteroid, etc.), or where the type has " planet" (space-letters-planet) in the middle and one or more characters before the space (minor planet, dwarf planet, etc).

  6. Find the nicknames and dates of discovery for objects in the solar system that are in orbit around an object whose official name is "Saturn".

  7. For each tool used in the discovery of objects in the solar system, list the name of that tool and how many solar system objects were discovered using it.

  8. Find the official names of solar system object's that are in orbit and orbited by one another, where the length of the orbit, in days, is bigger than the average length of an orbit.

  9. Find the space project identifiers and names for projects, plus the official name of the object the project is traveling to, plus the date the object was discovered, where the project is traveling to a solar system object that orbits around "Jupiter". (Jupiter is the official name of the object.) Present the results sorted by the official names of the object the project is travelling to. (For example, Europa Clipper is travelling to the moon Europa, which orbits around Jupiter. Europa was discovered by Galileo Galilei in 1610.)

  10. For each team identifier, find the team identifier, how many people are members of that team, and the earliest birthdate of anyone on that team. Show the output sorted by team identifier.

  11. Find and list pairs of solar system object official identifiers, plus the identifier for the team that found them, where the objects are different objects but were discovered by the same team. Two objects, same team found them.

  12. For each solar system object that has 3 or more objects orbiting around it, print the official name of that object (the big object being orbited), how many objects are orbiting that object (the big one), and the average number of days in the orbits of the orbiting objects. (So if B, C, and D are orbiting A, print A's official name, 3, and the average days in the orbits of B, C, and D.)

  13. List the last and first names of people and the roles they held in teams, plus the names of the teams and the official names of objects those teams discovered, for objects that were discovered before 1950. Sort the results by the dates the objects were discovered.