Read This Line! Information that is true for ALL of the following ER diagrams: Each garden has a unique identifying code. Each garden also has a name, an address, and a founding date.
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.)
- Big gardens have designers. A designer may design many gardens, or none. A garden will have at least one designer, but may have many. Each designer has a name, a birthdate, a home country, and a death date. (For example, Kew Gardens was founded in 1759, and many people have designed and redesigned parts of it over time. You may also enjoy looking at photos of gardens designed by Gertrude Jekyll.)
- Big, famous gardens may have named sub-gardens within them. A famous garden may have many sub-gardens, or none. Each sub-garden must be in exactly one big garden. Sub-gardens cannot be uniquely identified in the database as a whole, but can be identified within a particular famous garden by the sub-garden name. Each sub-garden has a name, a theme, a starting date, and a photograph. (Huntington Gardens, in Pasadena has a Children's Garden, a Desert Garden, a California Garden, a Herb Garden, etc. Many famous gardens have a rose garden, or an herb garden.)
- Big gardens may have dining establishments inside them. A big garden may have no dining establishments, one, or many. Each dining establishment can be identified by its business license number, and the database also stores a name, alcohol license number, and the date of the most recent food inspection.
- Some big gardens are gardens with gift shops. For gardens with gift shops, the database stores the gift shop name, phone number, and web site URL. Some big gardens are gardens with playgrouns. For gardens with playgrounds, the database stores the minimum and maximum child ages the playground was designed for, plus the number of swings the playground has. Some gardens have both gift shops and playgrounds.
- Big gardens must have a head gardener. The head gardener has a name, a phone number, an official title, and a publicity photograph. Over the course of time, a garden will have many head gardeners, and skilled gardeners may lead the staff of multiple gardens in the course of their lives. The database also stores, for each garden and head gardener, the date that gardener started working at that garden. (Recall, Kew Gardens in the UK was founded in 1840. The Master of the Nets Garden in Suzhou, China, was founded around 1140.)
(Let me know if the diagram is too small and you want a bigger version of it.)
Owner (oid: integer, firstname: string, lastname: string, birthyear: integer, bio: string)
OwnedBy (ownerID: integer, artID: integer, purchasePrice: integer, currency: string, purchaseDate: date)
ArtItem (aid: integer, title: string, weightPnd: integer, type: string, creator: integer, homeMuseum: integer)
ExhibitsIn (artID: integer, exhibitID: integer, insuranceCost: integer, deliveryDate: date)
Exhibition (eID: integer, title: string, curatorName: string, specialFee: integer, startdate: date, stopdate: date, museum: integer)
Museum (mid: integer, name: string, street: string, state: string, country: string, postalcode: string, website: string)
Artist (personID: integer, lastname: string, firstname: string, birthyear: integer, birthregion: string, bio: string)
(The "bit" data type stores 0 or 1. False or true. Booleans.)
"exhibitID" in ExhibitsIn is a foreign key referencing "eid" in Exhibition.
"homeMuseum" in ArtItem and "museum" in Exhibition are foreign keys referencing "mid" in Museum.
"artID" in ExhibitsIn is a foreign key referencing "aid" in ArtItem. The art item weights are measured in pounds.
"creator" in ArtItem is a foreign key referencing "personID" in Artist.
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).
Sometimes art acquires value because it belonged to someone famous. For example, in 2023 the Legion of Honor museum held an exhibition devoted to objects the Tudor rulers of England had owned. You may assume simple boolean comparisons (<, >, ≠) work on dates. Write the following queries in relational algebra.- Find the names and web sites of museums in a state named "California".
- Find the titles and special fees of exhibitions held at museums in a country named "Peru".
- Find the titles and art types of art items that were purchased at prices bigger than five million USA dollars.
- Find the titles of exhibitions, and the names of museums, for exhibitions held at the museum and featuring art that will be delivered to the exhibition after February 2025.
- Find the exhibition titles, art item titles, and museum names for exhibitions that are held in that museum and include that art item, and the exhibition starts after February 2025.
- Find exhibition titles and curator names for exhibitions that exhibit art items titled "Queen Elizabeth I" and "Henry VIII armor". (Two art items, in the same exhibition.)