CS 4250: Database Management Systems
Spring 2022 - Homework 1
Due 1 March 2022
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.
Turn in as hardcopy (in class time) or
upload to the CS Homework system
on or before midnight on 1 March 2022. Typed, wherever possible. Or photographs
of hand-drawn answers (dark writing, white paper, even lighting). Save in a widely
used file format, like JPG, PNG, etc.
When drawing ER diagrams, be obvious. Put a gap between double-lines.
Draw subset symbols nice and large.
Consider a database containing information about telescopes in space.
Create entities and relationships as appropriate for each (independent) situation described below.
Information that is true for all of the following ER diagrams:
Each space telescope has a unique name, a payload mass, a planned mission duration, and
For each of the following situations, draw an ER diagram that describes it.
Consider each situation independently. (Multiple situations means multiple, separate ER diagrams in a correct homework solution.)
Information in parentheses is simply information. Information in parentheses does
not affect the ER diagrams, but may help you understand the real world context of the
(Note: I may have "fudged" the truth a bit, on these questions, to make a good homework questions. Draw the ER diagram for the situations described.)
- Each space telescope is organizeded and run by one or more space agencies.
An organizing space agency is required for a space telescope to exist.
The database stores a unique identifying name for each agency, the date the agency was founded, the full and formal name of the agency (which might not be unique),
and the location of the agency headquarters. A space agency may organize
many space telescopes or none at all.
- Each space telescope, if it has been launched, is launched from one launch site.
The database stores the name, latitude, longitude, elevation, and surface area
of each launch site. The latitude and longitude, together, can uniquely identify
each launch site. Some launch sites have launches many telescopes, and some have
launched none. (Some telescopes in the database are still under construction
and have not been launched.)
- Some space telescopes are powered by solar panels, some are powered by
nuclear fuel. Some have both sources of power; all telescopes must have at
least one power source. For the telescopes with solar
panels, the database stores how many panels there are, and the total surface
area of the panels. For the telescopes powered by nuclear fuel, the database
stores how many grams of fuel, and what chemical isotope the fuel is.
(Commonly plutonium-238, but other sources are possible.)
- Space telescopes must have at least one instrument installed, but may have
more than one. Instruments may be installed on more than one telescope, or
on none (if still under construction). Each instrument has a name, a minimum
wavelength it can detect and a maximum wavelength it can detect.
Also, for each instrument installed on a particular telescope,
the database stores the date the instrument was installed and the date
the instrument finished being built.
(For example, the Fermi space telescope has two instruments. As a different example, a particular type of infrared light detector might be installed on two telescopes.)
- Some space telescopes have servicing missions. A telescope might be serviced
(fixed and upgraded) by zero servicing missions or many. Servicing missions
are identified by number (1, 2, 3...) for a particular telescope.
In addition to the mission number, each servicing mission has a start date,
an end date, a servicing vehicle, and a mission description. Servicing mission
numbers (1, 2, 3...) only identify a mission for a particular telescope.
- Translate the following image into relations. Write the SQL statements
to create those relations. Write English explanations of anything in the ER
diagram that is not captured in your SQL. Assume all keys are data type 'integer' and
all other fields are data type 'date'.
(Let me know if the diagram is too small and you want a bigger version of it.)
- Suppose a database has the following three relations.
Museum (mid: integer, name: string, address: string, website: string, foundingYear: integer, annualBudget: integer)
Displays (museumID: integer, artID: integer, startDate: date)
ArtItem (aid: integer, title: string, weightPnd: integer, type: string, pricePaid: integer)
ExhibitsIn (artID: integer, exhibitID: integer, insuranceCost: integer, deliveryDate: date, homeMuseum: integer)
Exhibition (eID: integer, title: string, startdate: date, enddate: date, specialFee: integer)
(The "bit" data type stores 0 or 1. False or true. Booleans.)
"museumID" in Displays is a foreign key referencing "mid" in Museum.
"exhibitID" in ExhibitsIn is a foreign key referencing "eid" in Exhibition.
"artID" in Displays and ExhibitsIn are foreign keys referencing "aid" in ArtItem. The
art item weights are measured in pounds.
You may assume simple boolean comparisons (<, >, ≠) work on dates.
If you are unfamiliar with art museums, they will frequently organize special
exhibitions. Museums will borrow art from other museums for a few months,
art that fits an exhibition theme, and charge a small extra fee for admission
to the exhibition. For examples, here
are some past Asian Art Museum of San Francisco exhibitions.
Art items must, of course, be insured appropriately before they leave their
home museum and are shipped
to the exhibition host museum, in case of loss or damage in transit.
Write the following queries in relational algebra.
- Find the titles and prices paid for art items that are of type "marble statue."
- Find the exhibition titles and start dates for exhibitions that include
art items that will be delivered to the exhibition later than February 2022.
- Find the museum names and addresses, and art item identifiers.
The art items are scheduled to be displayed in the museum starting after March 2022.
- Find the titles and types of art items that will be displayed in an
exhibition titled "Guo Pei: Couture Fantasy".
- Find the art item titles and art identifiers, and museum names, for art items that
have been displayed at a museum named "Legion of Honor Museum"
or at a museum named
Seattle Art Museum".
- Find the art item titles and art identifiers for art items that
have been displayed at a museum named "de Young Museum"
and at a museum named
"Art Institute Chicago".