Homework is due by classtime on Thursday. Submit either a hardcopy (in class, or drop it off at the 4th floor front desk), or email it to me.

We finished talking about SQL and started on ER-modeling (see chapter 3 of the book).

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, 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: 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.

Marcus Schaefer
Last updated: October 18th, 2007.