Read This Line! Information that is true for ALL of the following ER diagrams: Each marathon has a unique identifying number. Each marathon also has a start date, a name, a start location and an end location.
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.)
- Some marathons are affiliated with one particular city. Given the lengths of ultramarathons, some marathons have no such affiliation. Cities may or may not have a marathon, or many marathons, affiliated with them. For cities, the database stores the name of the city, the name of the country it is in, the altitude of the city, and a brief description of the climate of the city. (Running a marathon near high Mexico City is, reportedly, very different from a marathon at ocean level.) The database also stores a unique, identifying geo-code for each city.
- Runners run in marathons. To be in this database, each runner must have run in at least one marathon. Each marathon must have at least one runner in it. Runners may run in many marathons and, hopefully, each marathon will have many runners in it. Runners have identifying runner numbers. The database also stores their first and last names and birth dates. For every combination of runner and marathon, the database will store how long it took that runner to complete that marathon.
- Some marathons are obstacle marathons, and some marathons are charity marathons. Marathons can be both. For charity marathons, the database stores the name of the charity the marathon benefits, and a "money-raising style". (For example, some marathons raise money by having sponsors donate to become sponsors, or, alternately, runners can get friends to promise $X to the charity for every mile the runner completes.) For obstacle marathons, the database stores the type of the obstacle and how many obstacles are part of the marathon course. (For example, one desert obstacle, or five mud ponds, or...)
- Some marathons offer special 'packages' people -- runners, spectators, etc -- can purchase. The 'packages' that might be for sale for a particular marathon are just for that marathon, and there is no world-wide way to identify those 'packages.' For any particular marathon, the organizers will give 'packages' for sale at their own marathon different names, unique only to their own marathon. For each type of package sold, the database stores the name of the package, the price, and a description. For any particular marathon, the name of the packages can be used to identify them. (For example, the Chicago marathon in Oct 2026 has these offers -- packs of photos taken by the official photographer, expedited access to skip lines, access to an extra-nice tent with snacks, and so on.)
- Sometimes new world records are set at marathons. Multiple new world records might be set at one marathon (or none). For each world record set at a marathon, the database stores a record identifier, brief description, the date, the completion time of the runner, the runner name, and the age of the runner at the time of the record. For example, Wikipedia reports that the oldest woman to complete a marathon was 92 year old Marriette Thompson, in 2015. (An accomplishment for you to try to beat, someday.)
(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 to 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 last names, first names and specialities of conservators.
- Find the start dates and costs of art restorations performed by a conservator named "Mathias Kauage".
- Find the titles and home museums of art items that are owned by people with last name "Huntington".
- Find the titles of art items with a home museum "Crocker Art Museum" that have been restored by a conservator whose specialty is "oil paintings".
- Find the titles of art items and start dates of restorations, where the art item has been restored by a conservator named either "Clark Kent" or "Reed Richards".
- Find the titles of art items and start dates of restorations, where the art item has been restored by conservators named "Clark Kent" AND "Reed Richards". (Old art items may be restored multiple times, by different conservators, over the centuries.)