Your task is to design a system that allows smooth operation of a library system. That is, create stored procedures that will fulfill the user requirements given below.
Please provide test case(s) for each of the stored procedures that you create. The library has several locations and there are two main user groups, Patrons and Library Employees.
User Requirements:
1. A patron wants to know if copies of a specific book are available in the library system. The patron provides book name or part of the book name to search for the book. The system should provide the total number of available book copies to the user along with the book title.
2. A patron wants to know if copies of a specific book are available in the library system. The patron provides book name or part of the book name to search for the book. The system should list the names and the location (libraryID) of the copies that are available.
3. The library employee would like to know when a certain book copy is due. The employee provides the book copy id to retrieve the due date for the book. If the employee does not provide a book copy id, due dates for all books are retrieved. The due date for only books that haven’t been returned should be retrieved.
4. The employee would like to find all books that are above a certain price. The price is provided by the employee which is used by the system to retrieve all books above the provided price.
5. A patron can search for books in the library using zero, one, or all of the following criteria: library, category, or author’s last name. The patron should get a list of all the book copies (isbn, copy number, title, category name, library location, book status) that meet the requested criteria.
6. Patrons would like to look at books that fall under a category. When the patron provides a categoryid, all books listed under that category as well as all its sub-categories should be displayed. For example, if a patron provides the category ID of 100 (database) for searching, all books listed in the Database category as well as the sub-categories of the Database category should be displayed.
7. The system should allow patrons to borrow or check out a book. The patron will swipe his / her ID card and scan the book’s barcode.
8. The system should allow patrons to return a book. The patron will swipe his / her ID card and scan the book’s barcode.
9. The library staff need a list of patrons (ID, name, email address, number of books returned late) who are late returning books during a user-specified period (the user specifies start and end dates). The results should show patronid, patronlastname, patronemail and the count of books that were returned late.
10. The library employee would need a mechanism to enter a new patron into the system. All required columns will be provided to create the new record. The newly generated patronID will be returned.
11. The library employee would need a mechanism to update the price of a book copy. All required columns will be provided to achieve this functionality.
12. The library employee would need a mechanism to add a new book to the library system. All required columns will be provided to create the new record.
13. The library employee would need a mechanism to add a new book copy for a particular book. All required columns will be provided to create the new record.
Relevant Business Rules:
1. A book is identified by its ISBN (a 13-character code).
2. A book can have several book copies (physical copies).
3. Each book copy is identified by the combination of ISBN and copy number
4. Each book copy belongs to one library location.
5. Only a patron is allowed to borrow a book copy.
6. Also, a book copy can be borrowed by only one patron at any given time.
7. A book belongs to one category.
8. Category refers to the category of a book – examples, Database, Programming, History, etc.
9. Each Category can have several sub-categories (for example, Database category has sub-categories such as Relational Database, Normalization, SQL).
10. Each sub-category can belong to several parent categories (for example, SQL can have Database and Programming as parent categories).
11. A book can have several authors.
12. Each author can write several books.
13. A book can have three possible status values: “Available”, “Borrowed”, or “Lost.”
14. A book’s price varies by copy (depending on binding, original copy, rarity)
15. Library staff can suspend the borrowing privileges of a patron if he / she returns books late on more than the allowed number of times (suspended field for Patron is set to true).
