Data Model: We need to store information about different types of employees (Managers, Workers), Library Branches, different colleges, Students, Books, Publisher (Books details such as ISBN, author, published date, publisher name, and address), Borrow information (Books [SBM; students who borrow - borrow date and due date, employee who give the books to the borrowers, etc]). Other requirements are as follows:
- An employee can be either a worker or a manager.
- A library has different branches distributed across colleges (Computer School Library, Eng School libraries, etc). These branches must have only one manager and can have either one or multiple employees.
- Each branch contains books related to the school.
- Only IU students can borrow books and the borrow process must be approved by a worker.
- A student can borrow any books, but a book can only be borrowed if all copies of that book are reserved.
- A student can perform two actions: Reserve book (including borrow information such as ISBN, borrow date, due date, branch number, and the employee who approves it) and return book (including all the information related to the returning process).
- Students who did not return books should be listed in the blacklist and they cannot borrow any books within 30 days.
Below are some tables for the schema of the database:
Books(ISBN, Book_name, author_name, status, number of copies)
Publisher (Publisher id, publisher_name, address)
Employee(EID, eName, eFamily, JobTitle, startDate, lastDate, dob, phone, email)
Students(student id, firstName, lastName, dob, phone, address, college, department)
Branches(branch id, branch_name, college)
In each relation, the underlined attribute is the primary key of that relation.
Note that Manager and Workers are special kinds of employees for which we need to record their information. Also, the reserving of a book is important to record the reserve date, due date, assigned employee, and other necessary information. Information related to returning a book must be recorded as well. It is also highly recommended to visit one of the branches in IU (e.g. College of Hadith, etc.) to have a better understanding of how the library branches at IU are managed and controlled.