1. Assumptions and Business Rules
In order to design a Database Library System for UoG, some assumptions on the based specification of this assignment has been made before undertaking any range of tasks.
According to the specification, the UoG library has different kinds of items like journals, dissertations, video collection, audio collection and electronic books. However, in this coursework, only books, journals and video items are focused on. Thus, only the transactions of these three items may be chiefly concerned with the Database of the library system that I designed. In addition, as every kind of book title has four copies of it, I assumed that every item may have its copies so that not only books but also videos and journals may have four copies of its original in this library system.
Moreover, in the specification of the UoG library, borrowers can borrow the items in a couple of loan type; short loan or ordinary loan. Short loan allows checking out for one week and ordinary loan allows one academic semester. Therefore, I assumed that the exact kind of loan types may need to be recorded every time an item is borrowed. Besides, according to the specification, the types of the borrowers are based on the number of books allowed to borrow so that I assumed that there may be two main types of borrowers such as staff and student in the library system. In student type, postgraduate student and undergraduate student are included. Furthermore, for renewing items, I assumed that the library staff may be able check the return date of the loan in order to send the notice of renewal and the reservations of items to know whether the item is reserved by another member. Although the specification focuses on the reservation and renewal of books in this coursework, I assumed that other items like videos and journals may have reservations and renewals in the UoG library system.
Furthermore, according to the specification, although fines can be calculated on daily basis only on overdue books, I assumed that journals and video items may be borrowed and there may be fines on the overdue of these items so that fines may be calculated for overdue of these items on the daily basis. Additionally, I assumed that there may have various kinds of fine types based on the types of borrowed items and the amount of fines may be different according to the kinds of fine types. Also, for renewal and fining process, I assumed that return information of the items may also be important for the Database of the UoG library system.
In addition, as there are some books, such as reference books, rare books and maps, that the library does not lend, I assumed that the librarian may keep tracks of such kinds of books by differentiating them into different groups like borrowable and un-borrowable items. Moreover, in order to know whether the items are available to be lent or are reserved or are missing or disposed, I assumed that the librarian may secernate them by the status of the items. Furthermore, I assumed that the location of the items that they are placed in the library may require knowing for keeping them in safety.
2. Conceptual Model
3. Relational Scheme
4. Normalisation (Third Normal Form)
See appendix for Normalization Form
5. SQL Code
A1. Search with Borrower number
SELECT BO.BorrowID, BO.BorrowerID, B.BorrowerName, B.BorrowerType, I.Title, I.ItemType, BD.LoanType, BO.BorrowDate, BD.DueDate
FROM Borrow AS BO, Borrower AS B, Item AS I, BorrowDetail AS BD, CopyItem AS CI
WHERE BO.BorrowerID=[Enter Borrower ID] And BO.BorrowerID=B.BorrowerID And BO.BorrowID=BD.BorrowID And BD.CopyItemID=CI.CopyItemID And CI.ItemID=I.ItemID;
A2. Produce the total number of borrowed items in a specific academic year
SELECT BO.BorrowerID, ST.CurrentAcademicYear, Count(BD.BorrowID) AS CountofBorrow
FROM Borrower AS BO, Borrow AS B, BorrowDetail AS BD, Student AS ST
WHERE BO.BorrowerID=[Enter Borrower ID] And ST.CurrentAcademicYear=[Enter Academic Year] And BD.BorrowID=B.BorrowID And B.BorrowerID=BO.BorrowerID And BO.BorrowerID=ST.BorrowerID
GROUP BY BO.BorrowerID, ST.CurrentAcademicYear;
A3. Search with Loan Type
SELECT BD.LoanType, Count(BD.LoanType) AS CountOfLoanType, I.Title, B.BookCategory
FROM BorrowDetail AS BD, CopyItem AS CI, Item AS I, Book AS B
WHERE (((BD.LoanType)=[Enter Loan Type]) And ((BD.CopyItemID)=CI.CopyItemID) And ((CI.ItemID)=I.ItemID) And ((I.ItemID)=B.ItemID))
GROUP BY BD.LoanType, I.Title, B.BookCategory;
A4. Produce a list of all books having the same title
SELECT I.Title, I.LanguageName, B.ISBN, I.YearofPublication, Bi.BindingType, Count(I.Title) AS CountOfTitle
FROM Item AS I, Book AS B, Binding AS Bi
WHERE (((I.Title)=[Enter Title]) And ((I.ItemID)=B.ItemID) And ((B.BindingID)=Bi.BindingID))
GROUP BY I.Title, B.ISBN, I.YearofPublication, Bi.BindingType, I.LanguageName
ORDER BY I.Title;
6. Screen Dumps
Screen Dump for D7 (Student Registration)
Screen Dump for D7 (Staff Registration)
Screen Dump for D8
Screen Dump for D9