Designing a database library system


We have to undertake in designing a Database Library System for UoG. In 2008, UoG library was serving 24,533 students studying in the UK and 3,743 studying overseas. It has 8,979 (in 2008) who are the main users of the library on the campus. There are also 1,556 staff members who are entitled to borrow items from the library. UoG has approximately 150,000 books titles and 600,000 copies. It has journals, dissertations, video collection, audio collection and electronic books.

Library staff is divided into head librarian, school associate librarian, reference librarians, check-out staff and library assistants. Library has two types of categories on check out for one week on short loan or one academic semester for ordinary loan. And undergraduate students are allowed to have 10 books and academic staff members and postgraduate students can have 15 books out at any one time. There is a fine calculated on daily basis on overdue books. The library does not lend some books, such as reference books, rare book or out-of-print books and maps.

Conceptual Model

Relational Schema

Book (Book ID, Item No, Book Title, Author, ISBN, Year of publication, Type of binding, Language)

Journal (Journal ID, Item No, Journal Title, Street Date, Editor, Publisher)

Video (Video ID, Item No, Video Title, Author, Length, Publisher, Year of Publish, Genus)

Code of item (Item No, Item Name, Status)

Borrow Detail (Borrow No, item No, Member ID, Borrow Date, Categories, Librarian ID, Academic Year)

Borrow (Borrow No, Member ID)

Return Detail (Return No, Item No, Return Date, Borrow No, Member ID)

Return (Return No)

Reserved (Reserved No, Member ID, Item No, Reserved Date)

Reserved Detail (Reserved No, Item No)

Member Card (Member Card No, Member ID, Renew Date, Expire Date)

Member (Member ID, Member Card No, Member Name)

Student (Student ID, Member ID, Student Name, Date of Birth, Programme, Academic Year, Email, Phone, Address)

Staff (Staff ID, Member ID, Staff Name, Date of Birth, Post No, Department No, Email, Phone, Address)

Librarian (Librarian ID, Librarian Name, Post No)


SQL code

D6. - A1.

SELECT BD.BorrowNo, M.MemberID, I.ItemName, BD.BorrowDate, RD.ReturnDate, BD.Category

FROM Borrow AS B, BorrowDetail AS BD, CodeOfItem AS I, Member AS M, Return AS R, ReturnDetail AS RD

WHERE BD.MemberID=M.MemberID AND B.BorrowNo=BD.BorrowNo AND I.ItemNo=BD.ItemNo AND R.ReturnNo=RD.ReturnNo AND RD.MemberID=B.MemberID AND RD.ItemNo=BD.ItemNo AND B.BorrowNo=[Enter Borrow No];


SELECT M.MemberID, M.MemberName, COUNT(BD.MemberID) AS Total, BD.AcademicYear

FROM Member AS M, BorrowDetail AS BD

WHERE M.MemberID=BD.MemberID AND M.MemberID=[Enter Member ID] AND BD.AcademicYear=[Enter Academic Year]

GROUP BY M.MemberID, M.MemberName, BD.AcademicYear;


SELECT BD.Category, I.ItemNo, COUNT(BD.Category) AS Total_Borrow

FROM Borrow AS B, CodeOfItem AS I, BorrowDetail AS BD

WHERE BD.Category=[Enter Loan Category] AND B.BorrowNo=BD.BorrowNo AND BD.ItemNo=I.ItemNo

GROUP BY BD.Category, I.ItemNo;


SELECT BookTitle, Author, ISBN, YearOfPublication, TypeOfBinding, Language, COUNT(BookTitle) AS Count_BookTitle


WHERE BookTitle=[Enter Book Title]

GROUP BY BookTitle, Author, ISBN, YearOfPublication, TypeOfBinding, Language


ORDER BY BookTitle;

Please be aware that the free essay that you were just reading was not written by us. This essay, and all of the others available to view on the website, were provided to us by students in exchange for services that we offer. This relationship helps our students to get an even better deal while also contributing to the biggest free essay resource in the UK!