Exercise: library books

You are building a very simplified database for a library. The library, of course, owns (physical) books that are stored on shelves and checked out by customers. Each of these books is represented by a catalog entry (now in the computer, but think of an old-fashioned card file as a model of this). Assume that there is only one “title” card for each book in the catalog, but there can be many physical copies of that book on the shelves. Call the title card class a “CatalogEntry” and the physical book class a “BookOnShelf.” In addition to the typical information about a book, the library also needs to keep track information about book publishers.

Model the information provided by completing the following steps.

  1. You might think of the book's publisher as a simple attribute of the catalog entry—but in fact, the library will probably want to know more than just the publisher’s name (for example, the phone number where they can contact a sales representative).
  2. Follow the top-down database design by first identifying and describing in English the classes needed in modeling the information provided.
  3. Start drawing the UML class diagram by drawing the classes, but avoid filling in the attributes for now, just name the classes.
  4. Next, determine what relationships exist between the classes you identified. Draw each association in the diagram including the multiplicity constraints and describe in English (both directions) to make sure you've correctly modeled the information given.
  5. Now complete each class by filling in the attributes and their corresponding data type.
  6. Map the UML class diagram to the relational model by drawing the relation scheme diagram. Be sure to model all constraints such as any non-null attributes, primary key, and foreign key.

The solution to this exercise may be discussed in class by your instructor or posted online at a later date.