Identify all the important concepts represented in the library database case
study described here. In particular, identify the abstractions of
classification (entity types and relationship types), aggregation,
identification, and specialization/generalization. Specify \((\min , \max )\)
cardinality constraints whenever possible. List details that will affect the
eventual design but have no bearing on the conceptual design. List the
semantic constraints separately. Draw an EER diagram of the library database.
Case Study: The Georgia Tech Library (GTL) has approximately 16,000 members,
100,000 titles, and 250,000 volumes (or an average of 2.5 copies per book).
About 10 percent of the volumes are out on loan at any one time. The
librarians ensure that the books that members want to borrow are available
when the members want to borrow them. Also, the librarians must know liow many
copies of each book are in the library or out on loan at any given time. A
catalog of books is available online that lists books by author, title, and
subject area. For each title in the library, a book description is kept in the
catalog that ranges from one sentence to several pages. The reference
librarians want to be able to access this description when members request
information about a book. Library staff is divided into chief librarian,
departmental associate librarians, reference librarians, check-out staff, and
library assistants.
Books can be checked out for 21 days. Members are allowed to have only five
books out at a time. Members usually return books within three to four weeks.
Most members know that they have one week of grace before a notice is sent to
them, so they try to get the book returned before the grace period ends. About
5 percent of the members have to be sent reminders to return a book. Most
overdue books are returned within a month of the due date. Approximately 5
percent of the overdue books are either kept or never returned. The most
active members of the library are defined as those who borrow at least ten
times during the year. The top 1 percent of membership does 15 percent of the
borrowing, and the top 10 percent of the membership does 40 percent of the
borrowing. About 20 percent of the members are totally inactive in that they
are members but never borrow. To become a member of the library, applicants
fill out a form including their SSN, campus and home mailing addresses, and
phone numbers. The librarians then issue a numbered, machine-readable card
with the member's photo on it. This card is good for four years. A month
before a card expires, a notice is sent to a member for renewal. Professors at
the institute are considered automatic members. When a new faculty member
joins the institute, his or her information is pulled from the employee
records and a library card is mailed to his or her campus address. Professors
are allowed to check out books for three-month intervals and have a two-week
grace period. Renewal notices to professors are sent to the campus address.
The library does not lend some books, such as reference books, rare books, and
maps. The librarians must differentiate between books that can be lent and
those that cannot be lent. In addition, the librarians have a list of some
books they are interested in acquiring but cannot obtain, such as rare or out-
of-print books and books that were lost or destroyed but have not been
replaced. The librarians must have a system that keeps track of books that
cannot be lent as well as books that they are interested in acquiring. Some
books may have the same title; therefore, the title cannot be used as a means
of identification. Every book is identified by its International Standard Book
Number (ISBN), a unique international code assigned to all books. Two books
with the same title can have different ISBNs if they are in different
languages or have different bindings (hard cover or soft cover). Editions of
the same book have different ISBNs.
The proposed database system must be designed to keep track of the members,
the books, the catalog, and the borrowing activity.
