Read This! Information that is true for ALL of the following ER diagrams: Each power outage has a unique outage identifying number. Each outage also has a start time, a cause, and a status.
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 power outage may affect multiple streets, and streets can be affected by multiple power outages. For streets, the database stores an identifying street number. The database also stores the street name, the street city (many cities have a street named "Main"), and the latitude/longitude coordinates of each end of the street. (Two pairs of latitude/longitude numbers, one for each end.) It is possible for an outage to affect no streets, and for a street to have no outages on it.
- The database tracks repair teams. Each team has a team identifying number, a contact phone number, a repair truck number, and a description. Each team might be working on fixing one outage, or none. An outage might have no repairs working on it, or one, or many teams.
- The database can track notes about each outage. Each note has a timestamp, the text of the note, and the employee ID number of the person entering the note. (A sample note might record that, at 2pm, employee 33 wrote, "cutting up fallen tree branch". The notes help the power company track the progress of an outage repair.) Each note pertains to exactly one outage. There is no unique identifier for every note. A note belongs to only one outage. For a particular outage, each note for that outage will have a different timestamp.
- Some outages are brownouts; some outages are power station failures. For brownouts, the database stores the voltage of the power supply during the brownout. (In a brownout some power is flowing, but not the full and proper amount.) Some outages are caused by a power station going off-line. For those outages, the database stores the street address of the affected power station. A power station outage is never a brownout, and a brownout cannot be caused by a power station outage. (Power station outages cause blackouts, severe ones.)
- The database tracks the supplies used to repair an outage. For each supply item, the database stores a supply identifier (unique), a name, a price/cost, and a size. Many supply items might be used to fix an outage, and many outages might require the same supply item to fix them. (A replacement piece of power line wire, for example.) For any particular supply item and an outage it helps repair, the database tracks how many of the items were used. (For example, a particular outage repair might require 1 replacement power pole, 2 replacement chunks of power wire, and 35 new screws.)
(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, creator: integer)
ExhibitsIn (artID: integer, exhibitID: integer, insuranceCost: integer, deliveryDate: date, homeMuseum: integer)
Exhibition (eID: integer, title: string, curatorName: string, specialFee: integer)
Artist (personID: integer, lastname: string, firstname: string, birthyear: integer, birthregion: string, bio: string)
Regions (regionname: string, latitude: string, longitude: string, sizeSqMi: integer, climate: string)
(The "bit" data type stores 0 or 1. False or true. Booleans.)
"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.
"creator" in ArtItem is a foreign key referencing "personID" in Artist.
"birthregion" in Artist is a foreign key referencing "regionname" in Regions. A region where an artist was born might be a country, like Vietnam, or a particular area within a country, like Siberia, or a city, like New York City.
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 last names and first names of artists born before 1600.
- Find the climate, region names, last names and first names for artists born in regions that are "tropical" in climate.
- Find the titles of art items, and the last and first names of the artists who created them, for art items that weigh more than 100 pounds.
- Find the titles of art items, the last and first names of artists, and the artists' birth regions, for artists who come from a region less than 500 square miles in size.
- Find the artist first and last names, and exhibition titles, for exhibitions that include art items created by that artist, where the insurance cost for including that art item is greater than $1,000.
- Find the exhibition titles for exhibitions that include art items that cost either less than $100 to buy, or more than $1,000,000. (The price paid for the art item, the last time it was sold, was either very small or very large.)
- Find the exhibition titles for exhibitions that include art items that cost either less than $100 to buy, and also include art items that cost more than $1,000,000. (The price paid for the art item, the last time it was sold, was either very small or very large.)