Consider a database containing information about dinosaurs, specifically about the fossilized remains on display in museums around the world. Create entities and relationships as appropriate for each (independent) situation described below.
Information that is true for all of the following ER diagrams: Each dinosaur exhibit has a nickname, a current location ID number and a specimen ID number, a species name and a length. The location ID and specimen ID numbers, together, can uniquely identify a dinosaur exhibit.
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 dinosaur exhibit may have been purchased by one or more organizations.
The database stores a unique identifying number for each organization, plus a name,
date the organization was founded, and website URL. Dinosaur exhibits may or
may not have been purchased; organizations may or may not have participated
in a dinosaur exhibit purchase.
(As examples, consider Sue the dinosaur, purchased by multiple large organizations for display at the Field Museum in Chicago. On the other hand, the dinosaur identified as AMNH 5027 was found by Mr Brown, who worked for the museum, and that dinosaur has been owned by the museum ever since the time of discovery, never sold.) - Each dinosaur exhibit is displayed at exactly one museum and, to be in this database, must be on display. For each museum, the database stores the name, address, website URL, and founding date. Museums may display no dinosaurs, like the de Young Museum in San Francisco, or may display many dinsaurs, like the American Museum of Natural History in New York.
- The database stores information photographs of the dinosaur taken by
tourists on museum tours. Walking tours do not have unique identifiers,
and are simply recorded as tour 1 on a particular day, tour 2 on that date, tour 3, tour 4, etc.
So a photograph of a particular dinosaur exhibit can be identified by the date
of the tour, the number of the tour, and a number for the photograph. (For example,
photo #5 of Sue the T. Rex, taken by tour #3 on 10 Sept 2022.)
For each photograph, the database stores the photo number, tour number, date ouf the tour, and the photo itself.
(A museum tour is a walking tour around the museum, led by a museum staffmember several times a day.) - Some dinosaur exhibits are part fossilized bones, and some are entirely copies
of bones. For the exhibits that are part fossilized (and part copies), the database
stores the number of real bones in the exhibit and the weight of the heaviest fossil bone.
For the exhibits that are entirely copies, the database stores the cost of the exhibit
and the name of the organization that made the copy.
(An exhibit of 100% real bones is almost unheard of, due to how difficult it is for even a fossilized bone to survive more than 60 million years. "Understanding fossils and casts" from the Chicago Field Museum.) - Dinosaur exhibits can be made up of many fossilized bones. Each fossil bone is displayed in only one exibit, if it is on display. (The database does not store this fact, but some fossil bones may be in a back room, being cleaned or studied by a scientist.) For each bone, the database stores which dinosaur exhibit it is in, the weight of the fossil bone, the length of the bone, a specimen number and a museum number. Together, the museum number and the speciman number can uniquely identify any fossil bone.
(Let me know if the diagram is too small and you want a bigger version of it.)
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, curatorName: string, specialFee: integer)
Visits(exhibitionID: number, lat: string, long: string, startdate: date, enddate: date)
Location(latitude: string, longitude: string, name: string, address: string, country: string, phonenumber: string)
(The "bit" data type stores 0 or 1. False or true. Booleans.)
"lat, long" in Visits are a foreign key referencing "latitude, longitude" in Location.
"exhibitionID" in Visits are a foreign key referencing "eID" in Exhibition.
"exhibitID" in ExhibitsIn is a foreign key referencing "eid" in Exhibition.
"artID" in ExhibitsIn is a foreign key 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. Some exhibitions will tour art around the world, displaying it at one museum for a few months before moving on to a museum in a different city (or country).
Write the following queries in relational algebra.
- Find the names and phone numbers for exhibition locations in "Scotland".
- Find the titles and curator names for exhibitions with special fees greater than US$ 10.
- Find the exhibition titles and start dates for exhibitions that include art items that will be delivered to the exhibition later than September 2022.
- Find exhibition identifiers, titles and starting dates, for exibitions whose visits to particular locations will be after October 2022.
- Find the titles and curator names for exibitions that will be at the location named "de Young Museum".
- Find the exhibition titles and exhibition identifiers, and location names, for
exhibitions that have been displayed at location in the country "India" or
at a location in the country "United States".
- Find the exhibition titles and exhibition identifiers, and location names, for
exhibitions that have been displayed at location in the country "Japan" and
at a location in the country "United States".