1. The University Library has approximately 60,000 members, 200,000
titles, and 500,000 volumes (an average of 2 coples per book). About
10% of the volumes are out on loan at any one time.
2. 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 coples of each book are in the library or out on
loan at any given time.
3. 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; the description 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.
4. Library staff includes chlef librarian, departmental associate librarians,
reference librarians, check-out staff, and library assistants.
5. 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.
6. 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% 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% of the overdue books are either kept or never
returned.
7. The most active members of the library are defined as those who
borrow books at least ten times during the year. The top 1% of
membership does 15% of the borrowing, and the top 10% of the
membership does 40% of the borrowing. About 20% of the members
are totally inactive in that they are members who never borrow.
8. 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.
9. 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 malled 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.
10.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.
11.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.
In first part of the assignment, you will first design an EER schema diagram
based upon the LIBRARY database requirements specified above and create an
EER schema diagram and documentation report describing your design
choices. As part of this assignment, you should identify any missing or
Incomplete requirements, and explicitly state them in your documentation.
You should also explicitly state any assumptions you made that were not part
of the requirements listed above.
The second part of the assignment will be to map the EER schema design to
a relational database schema, and create the tables corresponding to the
relational schema using the ORACLE DBMS (or MySQL). You will add to your
report a listing of the CREATE TABLE statements. Specify as many
constraints (key, referential integrity) as you can in the relational schema.
You should state the choices you made during the EER-to-relational mapping,
and the reasons for your choices. (40%)
The third part of the project is to load some data into the database, and
apply certain update transactions and retrieval queries. You will create your
own data. Include at least 10 members, 5 staffs, and at least 30 titles of
different types. (20%)
The following are the tasks for the third part of the project:
1. Load some initial data (as discussed above) into the database tables that
you created in Part 2 of the assignment. You can either write a loading
program, or use SQL/PLUS (insert command), or use SQL/FORMS. Your
data should be kept in files so that it can easily be reloaded during
debugging. The data format should be designed by you. (Note: You can
also use the transactions created by you in item 3 below to load some of
the data).
2. Write queries to retrieve and print all the data you entered. Try to print
the data so that it is easy to understand (for example, print appropriate
headings, such as: Authors, Book Title, Subject area etc.).
3. Write a query that will prepare a report for weekly Borrowing activity by
Subject area, by Author, number of copies and number of days loaned
out.
4. Write the following database update transactions using either PRO*C or
JAVA/JDBC or PHP or some other programming language or scripting
language.
4.1 The first transaction is to add information about a new MEMBER.
4.2 The second transaction is to add all the information about a new
BOOK.
4.3 The third transaction is to add all the information about a new
BORROW (LOAN) (this must find the book from the catalog).
4.4 The fourth transaction is to handle the return of a book. This
transaction should print a return receipt with the details of the book
and days when it was borrowed and returned etc.
4.5 The fifth transaction is to renew the membership.
5. Write following two triggers -
5.1 To notify a member about the outstanding overdue book.
5.2 To notify a member about his membership renewal.
6. Each transaction should have a user friendly interface to enter the
information needed by the transaction. This can either be a Web-based
interface, a command line interface, or a forms interface.