Your university hires you to implement a database system for the library network. You have interviewed several librarians, and the following summarizes these discussions:
€¢ The library€™s main goal is to provide students and professors with access to books and other publications. The library, therefore, maintains an extensive collection of materials that are available to anyone with a valid university identification card.
€¢ The standard procedure for lending materials is that the student or faculty member comes to one of the three campus libraries and locates the book or journal on the shelves.
€¢ Each book is assigned three unique numbers. First, the book is assigned a number by the publisher, called the International Standard Book Number (ISBN). This number allows the publishers to track each title and the number changes with each new edition. The second number is the Dewey decimal number, which is assigned to the title and written on the outside spine of the book. This number is used to organize the library shelves and is thus helpful to the students and faculty. It is therefore critical that this number be available to users on the online inquiry screens. The last number is a university book ID number. A different number is assigned to every book that is received so the library can track all copies of each book. This number is different from the other two numbers such that if the library has three copies of one book, each will have a unique university book ID number.
€¢ When students or faculty check out books, the system must be able to track the specific copy that is being borrowed. Each book has a magnetic strip inserted in its spine, which is used as a security measure. If someone tries to take a book without checking it out, an alarm sounds.
€¢ In general, students and faculty have equal clout in the library. Both are able to check
out most books and to check out several books at one time. No one is allowed to remove periodicals from any library. The length of time that the book may be borrowed varies, however, depending on who checks it out. Students are allowed to check out a book for several weeks; faculty may borrow books for several months.
€¢ When patrons check out books, they take their materials to the circulation desk. At that time, the librarian scans in each item€™s university book ID number and the borrower€™s ID number. The system records a separate loan event for each book being checked out, assigning each a separate loan number. At this time, each book€™s due date is calculated and marked on a slip located inside each book€™s front cover. Simultaneously, the magnetic strip is deactivated so the book may be removed from the library.
€¢ After borrowers check out a book, they are expected to return it by its due date. In reality, everyone is allowed 30 days after the due date recorded on the checkout slip before the book is officially overdue. At that point, the book must be returned, and the borrower is assessed a $10 fine. If the book is permanently lost, then the borrower is fined $75 for the book€™s replacement. All fines must be paid in cash, in full. Students are not allowed to enroll for subsequent semesters until all library fines are paid; they also do not receive a diploma until all library fines are paid. Faculty must pay all outstanding fines by June 30 of each year.
€¢ When a book is returned, the return must be entered into the system, and a unique return number is used to log the transaction. At that time, the loan record is updated to show that the book has been returned.
The following attributes have been identified as critical for the new system:

REQUIRED
a. Draw an REA diagram for the library system. Remember to include cardinalities.
b. As directed by your instructor, either create the tables on paper that would be required to implement your REA diagram or actually build those tables in a relational DBMS to which you have access. Only use the attributes listed, unless others are absolutelynecessary.

Transcribed Image Text:

University book ID Book publisher Due date Loan number Borrower phone number Cash account number ubrarian name Book status (on the shelf or Actual return date Type of borrower (faculty or student) Librarian college degree checked out) Checkout date Borrower name Borrower ID Library borrowed from Borrower's fine balance owed Book title Fine receipt numberISBN number Amount received Book return number Library name Amount of fine Default library where Book copyright date book is shelved Boower e-mail address Author name Librarian number Account balance Total number of books in a specific library Dewey decimal number Borrower address Loan status (still outstanding, or returned)