Problem

Your university hires you to implement a database system for the library network. You have...

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:

University book ID

Borrower phone number

Type of borrower (faculty or student)

Book publisher

Cash account number

 

Due date

Librarian name

Librarian college degree

Loan number

Book status (on the shelf or checked out)

Actual return date

Checkout date

 

Borrower ID

Borrower name

Borrower’s fine balance owed

Library borrowed from

Book title

 

Librarian number

Fine receipt number

ISBN number

Account balance

Amount received

Book return number

Total number of books in a specific library

Library name

Dewey decimal number

 

Amount of fine

Borrower address

Loan status (still outstanding, or returned)

Default library where book is shelved

Book copyright date

Borrower e-mail address

Author name

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 absolutely necessary.


(This problem is adapted from one developed by Dr. Julie Smith David for classroom use at Arizona State University.)

Step-by-Step Solution

Request Professional Solution

Request Solution!

We need at least 10 more requests to produce the solution.

0 / 10 have requested this problem solution

The more requests, the faster the answer.

Request! (Login Required)


All students who have requested the solution will be notified once they are available.
Add your Solution
Textbook Solutions and Answers Search
Solutions For Problems in Chapter 19