CS 4250: Database Management Systems
Spring 2025 - Homework 2
Due 3/28/2025 Wednesday, 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 or email to the professor
on or before midnight on 28 Mar 2025. 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.)
Put your own name at the top of your own homework answers.
Consider the following relations:
SolarSystemObject(officialname: string, nickname: string, type: string, distanceMinKM: integer, distanceMaxKM: 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, birthdate: date, deathdate: date);
WorkedAt(workerID: integer, obserID: integer, startdate: date, stopdate: date, title: string)
Observatory(obserID: integer, name: string, buildDate: date, country: string, address: string, annualBudget: integer);
The relations capture some basic information about exploring the solar system,
including who was involved in discovering what objects.
SolarSystemObject 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.
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.
People who discover solar system objects frequently (but not always) work at
astronomical observatories, such as the Lowell Observatory where Clyde Tombaugh
worked at the time he found Pluto.
WorkedAt has a foreign key, workerID, referencing Person
personID, and a foreign key, obserID, referencing Observatory obserID.
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.
- Find the average budget of an astronomical observatory.
- Find the last and first names of people, and their birthdates,
for people who started work at an observatory before the year 1900, with the job title of
"founder".
(For example, Percival Lowell founded the Lowell Observatory in 1894.)
- Find the official names, nicknames, and discovery team names for solar system objects
of type "minor moon".
- Find the last and first names, and their titles at the observatory, of people
who worked at an observatory named "Harvard College Observatory".
(The Harvard Observatory was founded in 1839. I recommend a book about its
history, "The Glass Universe" by Dava Sobel.)
- Find the names of observatories, and dates they were build, for observatories
in either a country whose name starts with "South" and has one or more characters
after the "South", or a country whose name has " and " in the middle (space, and, space).
- Find the official names, nicknames and dates of discovery of solar system objects
discovered by a team named
"Harvard Computers".
(Harvard College Observatory's computers, in the late 1800's and early 1900's,
were not machines; they were people. The machines, later, were called
after people who did math professionally -- Harvard's computers did astronomical math.
And were women.)
- For each country, list the name of the country and the annual budget of
the astronomical observatory in that country with the biggest annual budget.
- Find the official names of solar system object's whose maximum
distance from Earth is bigger than the average minimum distances from Earth.
- Find the names of observatories and first and last names of people,
where the person worked at the observatory, and started work as member of
a team that discovered a solar system object forty or more years after they were
born. (The person started work on the team on or after age forty.)
You may assume that simple subtractions on dates will "magically work" to
get the number of years between those dates.
(Galileo was about 45 when he made his world-changing observations of Jupiter's moons.)
- For each title that employees at observatories have, how many observatories
have employees with that title?
Show the output sorted by title.
- Find and list pairs of person identifiers, last names and first names,
for pairs of people who both worked at the same observatory and one of them
started work a decade before the others.
Show the output sorted by the last name of the person who started work earlier.
As above, you may assume that simple subtractions on dates will
"magically work" to get the number of years between those dates.
- For each team that discovered solar system objects, print the name of the
team, how many people are on the team, and the earliest date anyone started on
the team. Only include teams that have more than two members in the output, and
alphabetize by team name.
- List the last and first names of people who worked at observatories, and who
were also members of teams
that discovered solar system objects. List the names of the teams they were members
of, the names of the observatories they worked at, and the last and first names of
the people. Restrict the list to only people born after 1920.
Sort the results by the last and first names of the people.