Read This Line! Information that is true for ALL of the following ER diagrams: Each fair has a unique identifying code. Each fair also has a name, an address, a start and an end date, and a ticket price.
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.)
- Art and craft fairs are held at locations. Each particular fair must be at only one location. Each location hosts many fairs over the course of time. (For example, Stanislaus County fairgrounds hosts events throughout the year.) Locations have unique location identifiers, names, numbers of parking spots, and latitude and longitude values.
- Art and craft fairs have schedules of events. Each art and craft fair may have many events. Each event has a start time, a stop time, a title and a description. There is no way to uniquely identify a particular fair event over all fairs. However, for the one fair that event is scheduled at, the start time and title, together, will identify the event. (For example, here is the schedule for an asian arts fair on September 13.)
- Live performances are a feature of many art and craft fairs. An art and craft fair may have many performances, or none. A performer can perform at many fairs. Performers have unique email addresses, names, websites, and performance types (such as country music, or taiko drumming, or Tex Mex music, or folk dancing).
- Some art and craft fairs also have a food theme. (Mendocino County Fair and Apple Show, Lodi Grape Festival, San Joaquin Asparagus Festival, and so on.) For those fairs with a food theme, the database should store the name of the food, and the category of the food (for example, fruit, vegetable, beverage...) Some art and craft fairs also include a 'vintage market' (flea market or antiques fair). For the fairs with an additional market, the database stores the number of market sellers. Some fairs go overboard and have a vintage market and a food theme!
- Art and craft fairs have vendors, people who make items and sell them at the fair. A fair must have many vendors to succeed. Vendors have vendor identifying numbers, names, a category of items they sell (for example, jewelry or glassware or woodwork). Vendors may participate in many fairs. For each fair that a vendor sells their work at, they will have a booth number and aisle number. Vendors may have different booth locations at each fair.
(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)
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)
Conservator (restorerID: integer, lastname: string, firstname: string, speciality: string, employer: integer)
Restoration (artID: integer, restID: integer, startdate: date, stopdate: date, cost: integer)
(The "bit" data type stores 0 or 1. False or true. Booleans.)
"homeMuseum" in ArtItem and employer in Conservator are foreign keys referencing "mid" in Museum.
"artID" in Restoration 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.
"restID" in Restoration is a foreign key referencing "restorerID" in Conservator.
If you're curious, Wikipedia has a page devoted entirely the the multiple restoration projects on the Sistine Chapel frescoes. You may assume simple boolean comparisons (<, >, ≠) work on dates. Write the following queries in relational algebra.
- Find the costs and end dates of art restorations that started after January 2025.
- Find the start and stop dates of art restorations performed on an item of art titled "The Great Wave off Kanagawa".
- Find the first and last names of owners of art items that weigh more than 5000 pounds.
- Find the last names of conservators, the last names of artists, and titles of art items, where the conservator restored and the artist created the art item, and the artist was born in the region named "Thailand".
- Find the last and first names of the artists who created art items with home museums located in either the country of Argentina or the country of Nepal.
- Find the last and first names of the artists who created art items with home museums located in either the country of Argentina and the country of Nepal. (One artist, probably two art items, each art item in a museum in two different countries.)