We finished talking about SQL and started on ER-modeling. Our book describes a different type of ER-diagramming. For an overview of several ER-modeling conventions, see the ER-model page at wikipedia, for a discussion of ER modeling close to how we are doing it see ER modeling.
1. [The art museum's miniworld]
We want to design a database for the inventory of an art museum. After interviewing the curator of the museum you have found out that they need the following information:
"We have objects of different types by many artists. Each object was created by one artist only, of course. For every object we need to know what type it is (painting, sculpture, etc.), and we keep a short description of the object on file. Each object also gets a unique inventory number. Objects are not always on display, and sometimes loaned to other museums. To keep track of this information, we keep---for every object---a complete list of times, when the object is not in regular storage (that is, on display, or on loan), and where it is. For the artist we have a unique ID, name, real name (if the name is a pseudonym), dates (born/died), and a biographical sketch. We obtain objects from either of two sources: by buying them from a supplier (such as auction houses, or other museums), or through donations. If we acquire an object from a supplier we need to know the supplier's name, address, email address(es), and type (e.g. "auction house"), and when the object was acquired, at what price. If the object was a donation, we store the donor's name, address and any phone numbers they have and whether they want to remain anonymous. We also need to store when the donation was made, and whether it is temporary or permanent. Many donors make several donations to the museum.
Based on this description, design an ER diagram for the art museum. Think carefully about what objects should become entities and which relations you want. Add all the attributes you need, and finally include key and maximum cardinality constraints.
Note 1: you can do the diagram by hand, but in that case, please sketch if first and then create a "clean" version for submission. You can also use software (some of it listed at the ER-model page at wikipedia); for example, Microsoft Visio is available in the labs. When you open Visio, go to New -> General -> Basic Block Diagram; that'll give you the right elements for creating an ER model.
Note 2: the description contains a lot of information. Some of it cannot be modeled by an ER diagram (as we have seen them), in which case you can ignore it (in particular this goes for the values of attributes mentioned, such as "painting"; values don't play a role in the ER model) . However, anything you can model, you should. Also, carefully design the attributes. Some of them will be composite, some multivalued, even complex.