UOG Library System
In this UOG library system, I assumed there are two types of members, student and staff member. I separate those two types as different entities because they have different roles and activities. Such as staff member is valid for four years while student members can renew their account at each academic year, academic staffs can borrow up to 15 books at any one time, and students may have some access to borrow course work related items. But every member must have unique member ID. So I decided to relate Member, Student and Staff entities with Optional Disjoint relationship.
For borrowing items in this course work will emphasize only on book, video item and journal. But the library also has 600,000 copies and those copies will allow borrowing. So, I create another copy table which have one-to-many relationship with Book because the book may have an average of 4 copies per book. There are four borrowing items type in UOG library.
There are also online catalogue which that lists book by Author, Title, Keyword, etc. In my opinion, catalogue will kept with collection name such as best seller of the year, popular book, the most borrowing books, etc. This catalogue will describe only about the book. So I assumed that a catalogue may have many books in one collection and a book may contain in many catalogues. There two entities are related as many-to-many relationship.
I add another entity such as acquire item because these entity will kept information about the items that librarian wanted but cannot obtain. The most important point is that those items in acquire table will not be allowed to borrow or it does not exist in library. The entity will exit only for the purpose of displaying or recording items that librarian wanted or just for the items which need to buy but have not buy yet.
The most important entity in this library is "Loan", which hold information about borrowing activities of all items in library. So it relate with all items which will allowed borrowing such as Book, Copy, Video Items, and Journal. There will be foreign key of Book, Copy, Video Items and Journal. But member may or may not borrow all kinds of items at one time. According to the foreign key constraint rule that the foreign key's value will not allow null, I combine those all foreign keys of borrowing items' ID (Book_ID, Copy_ID, VItem_ID, Journal_ID) as "Item_ID" and describe these ID with "Item_Type" which hold only four values of B as Book, C as Copy, V as VideoItem, and J as Journal in Loan table.
For the process of reserving items in library, I assumed that all the borrowing items can reserved. Reserve table have the same properties like "Loan" but have different operation. Reserve table is just for reserving the items that are out on loan or in the library. So I structure Reserve entity like Loan table which hold foreign keys of Book, Video Item, Journal and Copy as "Reserve_Item_ID".
Librarians have five kinds of staff, head librarian, school associate librarian, reference librarian, check-out staff and library assistants. I assumed that this entire staffs have different position but have a little operation process such as authority to access the system or role on the system. So I create "Librarian" entity to kept information about library staff but have different authority to access over the system.
SQL Statement (A1 to A5)
A1. A list showing the details of all items borrowed by a given specific borrower number. The user should be prompted to enter the borrower number at runtime. Produce the SQL code only (i.e. no form or report).
A2. For a given borrower, produce the total number of borrowed items in a specific academic year. Academic year starts from beginning of September to the end of August on the following year. The total number of items should include current borrowings, if the end date is less than the date of running the query (i.e. if the query runs in the current academic year, the total items should include all items that are currently on loan). The user should be prompted to enter the borrower number and the target academic year in the format YY-YY (e.g. 09-10). Produce the SQL code only (i.e. no form or report).
A3. For each borrowing categories (e.g. short loan, ordinary loan, reference item, etc.), produce a list of all items under that category. The output should include the name of the category and the total number of items in stock (borrowed or not) under that category. Produce the SQL code only (i.e. no form or report).
A4. Produce a list of all books having the same title. The list should include the title, ISBN, year of publication, type of binding and the language of the book. Produce the SQL code only (i.e. no form or report).
A5. For any given book, produce a list of all borrower numbers, date of each borrowing and the return date. The user should be prompted to enter the target ISBN that is captured and used by the code to produce the list. Produce the output on a report.
Database Design and Implementation Book