A Book Club Library Information System
For the term project, we will design and develop an information system for a Book Club. The Book Club runs a small library to serve the members. The members can borrow as many books as desired, and keep them as long as they want. However, the information – such as who has borrowed which books since when – will be public, available to all other club members using our information system. The information system therefore must keep track of club membership, book catalog, as well as circulation information. Also the system must maintain circulation history – so that we can do analysis such as who has read most books, and which book is most popular. We have the following requirements.
• Each club member is identified by a unique member ID number, and we also keep the member’s name, gender, address, and the starting date of membership, and the date the membership terminated (when the person is no longer a club member).
• Note that a member may also change address because of moving, or change name (for reasons such as marriage), the system keeps only updated information, but not the history.
• The club keeps a complete catalog of the books belonging to the library. For each book, the system maintains these information: ISBN, book title, first author, publisher, year of publication, and number of copies. The ISBN uniquely identifies the book as published.
• Optional for your design of the database: keep track of the date when a book copy first becomes available in the library for borrowing, and the date when it may be lost or too worn out for use – the date when it is removed from the library. (While a book is still in service, this date may be left as null.)
• The library must also keep track of circulation information: that is, who has borrowed what books, and keep circulation history. That is, the system must have a record each time a member borrows a book and returns a book (or reports it to be lost), along with the dates of these events.
• Describe in detailed steps the process to do either one of the following:
o for a club member to borrow a book o for a club member to return a book
(Optional: for MS Access, we can build an entry form for this.) Indicate which tables need to be updated with what data or record(s).
Choose one of the following and define what you want it to mean, and write the SQL statement to give the answer from your database design.
o list the most popular book(s) – read by most members.
o list the most active member(s) – who has read most books.
(Optional: for MS Access or Oracle, we can build in report generation for this.)
Some sample data for book catalog, and membership are available on-line as Excel data sheets. Download the spread sheet file project_data.xls from our course web site. You can generate some of your own circulation data by exercising your Borrow Book and/or Return Book procedures, using the database you have implemented.
Design a conceptual schema for the database, and illustrate your design with ER Diagram, and then implement the system in a set of tables in a relational DBMS (MS Access or Oracle). Prepare a report in an MS Word document to document your design and development, with illustration of the features you have implemented. You may download a report framework in the Word document file report.doc from the course web site. You also need to submit the database software you have designed and implemented: for Oracle, it is the SQL script file (-.sql) ; for MS Access, it is your database file (-.accdb).
