Library system of university

Library System of University

D1:Assumption

This system is the library system of University of Greenwich. The students and staffs can borrow items included books, videos and journals from the library. In the library, 150,000 book titles and 600,000 copies that is average of four copies per book. The librarians ensure that the books that members want to borrow them and they must know how many copies of each book are in the library. In this system, there have five types of librarians with different responsibilities. For example, check out process is performed by check out staff (librarian), etc.

Books have two types of loaning method. They are short loan and ordinary loan. Short loan is for one week and ordinary loan for one academic semester. The library system is divided loaning method for students. If the students are undergraduate student, they are allowed to have 10 books out at any one time. But, for the postgraduate student, there can have up to 15 books out any one time. Like, the academic staff members can also have up to 15 books out at any one time. A notice of renewal is sent to members 3 working days before the return date.

And then, in this system, I identified the overdue fees is $100 for books per day, $50 for videos and journals per day. The duration of the items is one week. If the members exceed the return date, they pay the overdue fees to the library. In this library, the reference book cannot be loan.

In this system, there have two types of member cards: student card and staff card. Staff cards are valid for four years while student cards are renewable at the beginning of each academic year during the registration week. All records of the library system are recorded in the database.

D2: Conceptual Enhanced Entity Relationship Diagram

Database Design and Implementation KMD Computer Center

Relational Schema

Member(MemberID,MemberType,MemberName,Address,PhoneNo,Email,CardType)

Student(MemberID,StudentType,RegisterSchool,ProgrammeStudy)

Staff(MemberID,Post,MajoritySubject)

Borrow(BorrowID,MemberID,LibraryStaffID,BorrowDate,DueDate,AcademicYear)

BorrowBookDetail(BorrowBookDetailNo,BorrowID,CopiesBookID,ReturnDate,Fine,OverdueDay)

BorrowJournalVideoDetail(BorrowJournalVideoDetailNo,BorrowID,ItemNo,ReturnDate,Fine,Overdueday)

Item(ItemNo,ItemType,Title,BarcodeNo,ArrivalDate,LoanType,Status,OverdueFee)

Book(ItemNo,ISBN,Author,Keyword,YearPublication,Language,TypeBinding,CategoryName)

Journal(ItemNo,JournalName,CurrentQty)

Video(ItemNo,Author,Genus,LengthMinutes,DateProduction,PublisherName,CurrentQty)

CopiesBook(CopiesBookID,ItemNo,BookComment,CopiesBookQty)

LibraryStaff(LibraryStaffID,LibrianType,Name,Address,Phone,Email)

Reservation(ReservationNo,ReservationDate,MemberID)

ReservationDetail(ReservationNo,ItemNo,AvailiableDate)

The Relationship of Relational Schema

D4:Normalization

Third Normalization

D6 :(SQL Statement)(A1 to A4)

A1: A1 is shown the detail of all items borrowed by a given specific borrower number.

SELECT i.ItemType, b.BorrowDate, i.Title

FROM Item AS i, Borrow as b, BorrowJournalVideoDetail as bjv

WHERE b.BorrowID = bjv.BorrowID and i.ItemNo = bjv.ItemNo and b.BorrowID =[EnterBorrowID];

UNION SELECT i.ItemType, b.BorrowDate, i.Title

FROM Item AS i, Borrow AS b, BorrowBookDetail AS bb, CopiesBook AS cb

WHERE b.BorrowID=bb.BorrowID And cb.CopiesBookID=bb.CopiesBookID And i.ItemNo=cb.ItemNo And b.BorrowID=[EnterBorrowID];

D7:Screen Design

Member register for student form

Member register for staff form

In this Student Register form

  • If you want to added new record of student information ,you first click student tab in the Member Register Form.
  • After that If you added new record of student information and fill these information then, you click Save button.
  • In the new record information, you must type MemberID, MemberType, MemberName, Address, PhoneNo, Email, Card Type, StudentType, Registering School, ProgammeStudy.
  • Next, you click save button and the information is save in the database.
  • If you cancel any information, then click cancel.
  • If you close from this form, click Close and the form is closed.

After filling the student information , can be seen the following.

Result table for student

Member register for staff form

In this Staff Register form

  • If you want to added new record of staff information ,you first click staff tab in the Member Register Form.
  • After that If you added new record of staff information and fill these information then, you click Save button.
  • In the new record information, you must type MemberID, MemberType, MemberName, Address, PhoneNo, Email, Card Type, Post, MajoritySubject.
  • Next, you click save button and the information is save in the database.
  • If you cancel any information, then click cancel.
  • If you close from this form, click Close and the form is closed.

After filling the staff information , can be seen the following.

Result table for staff

D8 Search By ISBN

SQL statemet for A5

SELECT bk.ISBN, b.BorrowID, b.BorrowDate, bb.ReturnDate

FROM Borrow AS b, BorrowBookDetail AS bb, Book AS bk, CopiesBook AS cb

WHERE (((b.BorrowID)=bb.BorrowID) And ((bb.CopiesBookID)=cb.CopiesBookID) And ((bk.ItemNo)=cb.ItemNo));

D9: Search By Author

SQL Statement for A6

SELECT v.Author, v.Genus, v.LengthMinutes, v.DateProduction, v.PublisherName, i.Title

D10: Testing

This is the borrow list Form

If you would like to see report of borrow list information, first type the ISBN and then click Report button and the SearchbyISBNReport is formed. See figure 1

If you would like to see according to Author Name, you choose Author Name in the combo box and the information concerned with that Author is formed

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!