Enhanced Entity-Relationship Modeling

Question

i have to draw Enhanced Entity-Relationship Modeling from this case :

The Georgia Tech Library (GTL) has approximately 16,000 members, 100,000 titles, and 250,000 volumes (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 how 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 includes 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 return books before the grace period ends. About 5 percent of the members have to be sent reminders to return books. 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 books 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 who 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 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 their 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 (hardcover or softcover). 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.

Draw an EER diagram of the library database.

**My question: 1) the main entity are Books,Staff,Member,Borrows. what is the relationship between borrows and member ?

2) " active members of the library are defined as those who borrow books 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." Is it necessary to model the membership and & active member?

3) the relationship between staff and borrows is one to many right ?

4) who will send the notice for the member .Can we consider it the staff ?**

5)what is the relationship between borrows and staff ? i think staff need to check the rules .

Answer 1

Here are my answers to your questions.

1. Borrowed is a book status. A member can borrow many books. A book can be borrowed by zero or one members at a time.

2. Number of books borrowed by member is a count of each of the books borrowed by each member, implying that there's a BookBorrowed table with a member ID and a book ID combination key.

3. There's no relationship between Staff and BookBorrowed. Staff can change the status of a book from in-house to borrowed, or borrowed to in-house. Only Members can borrow books. A Staff person can also be a Member.

4. The computer system built to maintain the database. There need to be screens and reports so that the staff can do its job.

5. Same as 3.

You need to putt the entities out of your description and make them tables. The entities I see are:

• Book
• Member

Staff information determines who can access the various functions of the system.

• Buy a book
• Add a book to the library
• Check out a book
• Check in a book
• Remove a book from the library
• Add a member
• Remove a member
• Send out membership renewals

Different members of the staff can access different functions. A library assistant may not have access to any of these functions. The head librarian has access to all these functions. The last two functions are probably done by the computer system, rather than the librarians.

So, lets get back to the entity tables.

We know a Book has the following attributes

Book
----
Book ID
ISBN
Title
Status (in-house, borrowed, uncirculated, desired)
Publication Date
Publisher
...

We'll determine the Book ID later. Right now, think of it as a unique key to a book that has none of the characteristics of a book. That's why the ISBN is an attribute, rather than the key. The ISBN will probably be indexed as a search value.

We also know that a book can have more than one author.

Author
------
Author ID
Author Name

A book can have many authors and an author can have many books. This many to many relationship calls for a junction table

AuthorBook
----------
Author ID
Book ID

The primary key is (Author ID, Book ID). There is also a unique index on (Book ID, Author ID). This allows you to query on an author's books, or all the authors of a book.

We also need a Member table

Member
------
Member ID
Member Name
Member Address
Date Joined
Date Membership Expires
Faculty Flag
...

Since the relationship between Member and Book is many to many, we need (ta-da) another junction table.

MemberBook
----------
Member ID
Book ID
Checked Out Date
Return Date
...

This junction table has attributes that don't belong to the member and don't belong to the book. They belong to the member - book relationship.

Generally, table ID's can be UUIDs or incrementing integers or longs. The important thing about a table ID is that it doesn't have any meaning, other than to associate rows in different tables.

I think this is enough information to get you started. It took years for Georgia Tech to work out their library database schema. Expect to spend months getting it right.

Answer 2

1)One to many

2) " active members of the library are defined as those who borrow books 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." Is it necessary to model the membership and & active member? I think not need.

3) yes

4) I don't know.İf you find answer ,please write soon.