November 2022 was the 100 year anniversary of the discovery of King Tutankhamun's tomb, so...
Consider a database containing information about the ancient Egyptian items found in Tutankhamun's tomb. Create entities and relationships as appropriate for each (independent) situation described below.Information that is true for ALL of the following ER diagrams: Each item has a "Carter No.", which can be used to identify it. Each item also has a description, and a current location and exhibit number. (Note that exhibit numbers do not identify items in this database. British Museum exhibit #4 will be a very different object from Modesto's McHenry Museum exhibit #4.)
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 good homework questions. Draw the ER diagrams for the situations as described.)
- Each item in Tutankhamun's tomb may have been studied by many scientists. The database will track which scientists have studied each item. An item might have been studied by many scientists, or none, and a scientist might study many items, or none. Each scientist is identified by their email address, and they also have first and last names and a scientific speciality.
- After the tomb had been emptied, a photographer named John Ross photographed many of the tomb items, after the items had been separated (and cleaned). The database stores information about each photograph. One tomb item may have many Ross photographs of it, or only one. Each Ross photograph is of exactly one tomb item. For each photograph, the database stores the name of the room (in the tomb) the item was found in, the Ross photo number of the photograph, and the file name of the file containing the photograph. The Ross photo number can identify each photo. (The tomb had multiple rooms -- the Burial Chamber, the Antechamber, etc.)
- The database stores some special photographs, taken by King Tutankhamun's
youngest fans. Each item might have no photographs from young fans, or many.
Each photograph will be of exactly one tomb item. The database stores
the nickname of the photographer, the day the photograph was taken, the photograph
itself and a brief comment by the photographer.
Since the photographs are by young people, they cannot be uniquely identified (to protect the privacy of the photographers) on their own. Photographs can be identified by using the photographer's nickname and the date of the photograph. (Each child photographer is only allowed to add one photograph of a particular item per day to the database.) - The discovery of King Tutankhamun's tomb was such a big event in archeology
that the
daily diaries and journals of people close to the excavation have been carefully
saved and preserved. This database stores information about each page of each journal,
and each
page can be identified using the number of the page and the last name of the person
who wrote the journal. The database also stores the text of each page, and a photograph
of the original page (handwritten pages).
The database stores information about which journal pages discuss which tomb items. A tomb item might be mentioned on many journal pages, or none. A journal page might discuss many tomb items, or none. For each mention of a particular tomb item, on a particular journal page, the database stores a short string describing where on the page the mention of the item is. (As examples, a third coffin mentioned at the top of page 88 of Carter's journal, and a second coffin mentioned in the middle of page 88. Second coffin mentioned on page 89, at the top. A floral collar also mentioned on page 89 at the top. And so on.)
- Some of the items in Tutankhamun's tomb were originally made for a different person. For those items, the database stores the name of the original owner. Some of the items in the tomb were images/statues of some Egyptian god. For those items, the database stores the name of the relevant god. (It is possible for an item to be both an image of a god and originally owned by someone who was not Tutankhamun.)
(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, artistID: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)
Artist (personID: integer, lastname: string, firstname: string, birthyear: integer, bio: 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.
"artistID" in Exhibition is a foreign key referencing "personID" in Artist.
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). Some exhibitions are organized around work by a specific artist. For example, in Spring 2023 the Asian Art Museum organized the exhibition: "Color Trip: Yoshida Hodaka’s Modern Prints".
Write the following queries in relational algebra.
- Find the titles and curator names for exhibitions where the special exhibition fee is more than $15.
- Find the exhibition identifiers and start dates for exhibitions that end after November 2023.
- Find the exhibition identifiers and start dates for exhibitions that visit locations in Brazil.
- Find the artist names, the exhibition titles, the art identifiers and the delivery dates for art items that will be exhibited in exhibitions and that have insurance costs of more than $100,000.
- Find the artist first and last names, and exhibition titles, for exhibitions that visit somewhere starting after January 2024 and before June 2024.
- Find the curator names, for curators who have organized exhibitions that were focussed on the work of artist "Leonardo DaVinci" or organized exhibitions on the work of artist "Hokusai" (no first name, or first name of "").
- Find the curator names, for curators who have organized exhibitions that were focussed on the work of artist "Leonardo DaVinci" and organized exhibitions on the work of artist "Hokusai" (no first name, or first name of "").