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 an insignia.
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 question. (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.
(Let me know if the diagram is too small and you want a bigger version of it.)
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".