The uog library

The UoG library

1. D1 - Assumption

The UoG library is a library which was serving 24,533 students studying in UK and 3,743 studying overseas in 2008. There are also 1,556 staff members who are entitled to borrow items from the library. It has approximately 150,000 book titles and 600,000 copies (i.e. an average of 4 copies per book). The library also has journals, videos and books, etc. It has a catalogue online. So that I suppose that there must have four entities. They are MEMBER, LOAN, CATALOGUE, and LIBRARIAN. There are two types of member, STAFF and STUDENT. In STUDENT, it has two student types, undergraduate and postgraduate student. So I think there should be UNDERGRADUATE and POSTGRADUATE entities. However, they are the members of the library. Therefore I think that their relations. I suppose that STAFF and STUDENT entities are the sub classes of MEMBER and I use disjoint optional mapping between the super and sub classes. For the STUDENT entity, UNDERGRADUATE and POSTGRADUATE entities are the sub classes. But they are not as important as STUDENT entity. So that I make overlapping optional mapping between super and sub classes. I think every member has the member start date, expire date and member type which are the detail information of member. I suppose that, there must be MEMBER DETAIL entity to record these detail information. But it depends on the MEMBER entity so that I make it as a weak entity.

For borrowing activity, undergraduate student can loan 10 books per one time and postgraduate student and staff can loan 15 books per one time. But I think that they are belonging to MEMBER entity and it is the parent for all child classes so that I make a relation between LOAN and MEMBER entities. For a member, he or she can loan one or more books at one time. So that I think I have to record the loan items. So I make LOAN DETAIL entity to record it.

Although the catalogue is mentioned in the scenario, the information for it is not sufficient. Moreover the scenario says that the library system is only focused on the books, journals, video items so that I make CATALOGUE, BOOK, JOURNAL, VIDEO entities. But catalogue is the super class of the other threes. So I make disjoint optional relation between the entities.

In scenario, there are average 4 copies per book and I have to differentiate between copies of books that can be lent on short or ordinary loan or those that cannot be lent. For short loan, the member can borrow the items for one week and or ordinary loan is for one academic semester. The reference items are the one that cannot be lent. For journal and video, there is no description. So I suppose that there should be at least one copy item for journal and video. For the most active items, there should be also four copies. To record the copies and loan type, I make ITEM COPY entity. But it depends on the CATALOGUE. So I make it as weak entity. In the entity, I suppose that at least one copy for each item. The original items become reference items. For videos and journals, I assume the member has to loan for short period. So I mark the copies of these items as short loan. For some rare books or most important books, I mark the copies of these books as short loan. For the books that are supporting for thesis and they can easily replace, I mark some of the copies of books as ordinary loan. I assume both original and copies items has ItemCopyCode bur for original items, ItemCopyCode is same as ItemCode because every item has detail for their loan type (reference, short loan, and ordinary loan). In the scenario, a member can reserve items. But it is an optional relationship. But it is important to record. So I make it RESERVE entity. According to scenario, a notice of renewal is sent to members 3 working days before the return date. But it is not mentioned in detail. So I neglect this process. In the scenario, academic year starts at the beginning of September and ends at the end of August. I assume first semester should starts at the beginning of September and ends at the end of Februray which is at the middle of academic year and second semester starts at the beginning of March and ends at the end of August.

2. D2 - Conceptual data model diagram

3. Relational Schema

Book (ItemCode,ISBN,Language, Edition, Date of Publication) Journal (ItemCode, Publish Date)

Video (ItemCode, Genus, Length in minutes, Date of production, Publisher Name)

Catalogue (ItemCode, Title, Season or Volume, Author, Coauthor, Keyword, Field,

Collection Category, Year of publication, Total no of copy item)

ReserveItem (*ReserveID, *ItemCode, CollectionCategory, Title, Author)

Librarian (LibrarianID, Librarian Name, Job, Address, Phone, Email)

Reserve (ReserveID, *MemberID, ReserveDate,* LibrarianID)

ItemCopy (ItemCopyCode, *ItemCode, Loan Type)

LoanItem (*LoanID, *ItemCopyCode, Due Date, Return Date, Fine)

Loan (LoanID,*MemberID, Loan Date, Academic Year)

Member (MemberID, Name, DOB, Occupation, Address, Email, Phone)

Member Detail (*MemberID,*Start Date, Expire Date, Member Type, Academic Year, Books Per One Time,* LibrarianID)

Student (MemberID, StudentNo, Study Program, Campus, Secondary School, Undergraduate Degree, Student Type)

Staff (MemberID, StaffNo, Post, Department, Campus)

Relational Schema Notation

  1. Table - TableName
  2. Primary Key(PK) - Underline , Bold
  3. Foreign Key(FK) - Contain *
  4. Candidate Key - Italic
  5. Relationship - PK FK

4. Normalization

5. D6 - SQL Code & D10 - Screen Dump and Test

A1.A list of showing the details of all items borrowed by a given specific borrower number.

SQL Code

SELECT DISTINCT m.MemberID, m.Name, c.[Collection Category], ic.ItemCopyCode, c.Title, c.Author, c.Keyword, c.Field

FROM Member AS m, Cataloge AS c, ItemCopy AS ic, Loan AS l, LoanItem AS li

WHERE m.MemberID=Enter_BorrowerID

AND m.MemberID=l.MemberID

AND l.LoanID=li.LoanID

AND li.ItemCopyCode=ic.ItemCopyCode

AND ic.ItemCode=c.ItemCode;

Test Result

  1. Request the BorrowerID as parameter.
  2. Enter BorrowerID = M-00001
  3. Show the result table.

6. D10 - Screen Dump and Test

6.1. Screen Dump and Test for D7

6.2. Screen Dump and Test for D8

6.3. Screen Dump and Test for D9

Reference

Book Reference

ISBN : 978-1-4302-0550-0

Title : Beginning SQL Queries from Novice to Professional

Author : Clare Churcher

ISBN : 978-1-59059-769-9

Title : Beginning Database Design from Novice to Professional

Author : Clare Churcher

ISBN : 978-0-470-11928-0

Title : SQL All-in-One Desk Reference for Dummies

Author : Allen G.Taylor

Other Reference

Author: Microsoft

System: Microsoft Access Help

4. Normalization

3NF

MEMBER

Member ID

Name

DOB

Phone

Address

Email

STUDENT

Member ID

Student No

Studying Program

Campus

Secondary School

Undergraduate degree

MEMBER DETAIL

*Member ID

Start Date

Expire Date

Member Type

*Librarian ID

LIBRARIAN

Librarian ID

Librarian Name

3NF

MEMBER

Member ID

Name

DOB

Phone

Address

Email

STAFF

Member ID

Staff No

Post

Department

Campus

MEMBER DETAIL

*Member ID

Start Date

Expire Date

Member Type

*Librarian ID

LIBRARIAN

Librarian ID

Librarian Name

. D6 - SQL Code & D10 - Screen Dump and Test

A2.Total number of borrowed items in a specific academic year for a given borrower

SQL Code

SELECT m.MemberID, m.Name,COUNT(li.ItemCopyCode) AS [Total Number Of Borrow Item]

FROM Member AS m, Loan AS l, LoanItem AS li, Cataloge AS c, ItemCopy AS ic

WHERE m.MemberID=Enter_BorrowerID

AND l.[Academic Year]=Enter_AcademicYear

AND m.MemberID=l.MemberID

AND l.LoanID=li.LoanID

AND li.ItemCopyCode=ic.ItemCopyCode

AND ic.ItemCode=c.ItemCode

GROUP BY m.MemberID,m.Name;

Test Result

  1. Enter BorrowerID as parameter.
  2. Enter BorrowerID = M - 00001.
  3. Enter AcademicYear as parameter.
  4. Enter AcademicYear = 09-10
  5. Show the result table.

Screen Dump for A2

A3. A list of all items under the category (e.g. short loan, ordinary loan, reference item) and total no of items in stock (borrowed or not) under each categories

SQL Code for a list of all items under the category

SELECT ic.[Loan Type], c.ItemCode, ic.ItemCopyCode, c.Title

FROM Cataloge AS c, ItemCopy AS ic

WHERE ic.ItemCode=c.ItemCode

ORDER BY ic.[Loan Type], c.ItemCode, ic.ItemCopyCode;

Test Result

Screen Dump for Result Table

SQL Code for a list of total no of items under each category

Main Query - A3 - total no of items

SELECT Total.[Loan Type], InStock.[Total No of Books in Stock], OnLoan.[Total No of Books on Loan], Total.[Total No of Books]

FROM ([View Total for A3] AS Total LEFT JOIN [View OnLoan for A3] AS OnLoan ON Total.[Loan Type] = OnLoan.[Loan Type]) LEFT JOIN [View InStock for A3] AS InStock ON Total.[Loan Type] = InStock.[Loan Type]

ORDER BY Total.[Loan Type];

Sub Query 1 - View InStock for A3

SELECT ItemCopy.[Loan Type], Count(ItemCopy.[ItemCopyCode]) AS [Total No of Books in Stock]

FROM ItemCopy

WHERE (((ItemCopy.[ItemCopyCode]) Not In (SELECT li.[ItemCopyCode]

FROM LoanItem AS li, Loan AS l, ItemCopy AS ic, Cataloge AS c

WHERE l.LoanID=li.LoanID

And li.ItemCopyCode=ic.ItemCopyCode

And ic.ItemCode=c.ItemCode

AND li.[Return Date] IS NULL)))

GROUP BY ItemCopy.[Loan Type];

Sub Query 2 - View OnLoan for A3

SELECT ic.[Loan Type], Count(li.[ItemCopyCode]) AS [Total No of Books on Loan]

FROM LoanItem AS li, Loan AS l, ItemCopy AS ic, Cataloge AS c

WHERE l.LoanID=li.LoanID

And li.ItemCopyCode=ic.ItemCopyCode

And ic.ItemCode=c.ItemCode

And li.[Return Date] Is Null

GROUP BY ic.[Loan Type];

Sub Query 3 - View Total for A3

SELECT ic.[Loan Type], Count(ic.ItemCopyCode) AS [Total No of Books]

FROM ItemCopy AS ic, Cataloge AS c

WHERE ic.ItemCode=c.ItemCode

GROUP BY ic.[Loan Type];

Test Result

Screen Dump for Result Table of Main Query

Screen Dump for Result Table of Sub Query 1

Screen Dump for Result Table of Sub Query 2

Screen Dump for Result Table of Sub Query 3

A4. A list of all books having the same title

SQL Code

SELECT c.ItemCode, c.Title, b.ISBN, c.[Year of publication], c.Field, b.Language

FROM Cataloge AS c, Book AS b

WHERE c.Title In (SELECT ct.Title

FROM Cataloge as ct

GROUP BY ct.Title

HAVING count(ct.Title)>1) AND c.ItemCode=b.ItemCode

ORDER BY c.Title;

Test Result

Screen Dump for result table

6. D10 - Screen Dump and Test

6.1 Screen Dump and Test for D7

Testing

Test Objectives : To test the saving data for staff member registration

Test Data : Write data in appropriate text boxes and choose member type

Test Procedure : Fill data in personal information field and contact information field. Choose staff in occupation combo box and fill data in staff tab. Choose member type in member detail and click save button.

Expected Result : Display successfully saved message and save personal and contact information in Member table, Member Detail information in Member Detail table and Staff information in staff table.

Actual Result : See the following figures

Before Action

After Action

In Registration Form

In database

Member Table

Member Detail Table

Staff Table

Test Objectives : To test the saving data for student member registration

Test Data : Write data in appropriate text boxes and choose member type

Test Procedure : Fill data in personal information field and contact information field. Choose staff in occupation combo box and fill data in student tab. Choose member type in member detail and click save button.

Expected Result : Display successfully saved message and save personal and contact information in Member table, Member Detail information in Member Detail table and Student information in student table.

Actual Result : See the following figures

Before Action

After Action

In Registration Form

In Member Table

In Member Detail Table

In Student Table

6.2. Screen Dump and Test for D8

SQL Code for D8

SELECT b.ISBN, li.ItemCopyCode, c.Title, m.MemberID, l.[Loan Date], li.[Due Date], li.[Return Date]

FROM Book AS b, Cataloge AS c, ItemCopy AS ic, LoanItem AS li, Loan AS l, Member AS M

WHERE b.ItemCode=c.ItemCode

And m.MemberID=l.MemberID

And c.ItemCode=ic.ItemCode

And li.ItemCopyCode=ic.ItemCopyCode

And l.LoanID=li.LoanID

And b.ISBN=ISBN;

Testing

Test objectives : To test search function

Test Data : Type in ISBN = 0-05-332312-9

Test Procedure : Type ISBN in text box and click Search button

Expected Result : Display searched information

Actual Result : See the following figures

Before Action

After Action

Test objectives : To test report function

Test Data : Type in ISBN = 0-05-332312-9

Test Procedure : Type ISBN in text box , click Search button and click Report button.

Expected Result : Display report form

Actual Result : See the following figures

Before Action

After Action

6.2. Screen Dump and Test for D9

SQL Code for D9

SELECT c.Title, c.Author, c.[Co-author], c.Keyword, c.Field, v.Genus, v.[Length in minutes], v.[Date of production], v.[Publisher Name]

FROM Cataloge AS c, Video AS v

WHERE (((c.ItemCode)=v.ItemCode)

And ((c.[Collection Category])="Video"));

Testing

Test objectives : To test search function

Test Data : Choose author=Gibbs from author combo box.

Test Procedure : Choose author from author combo box.

Expected Result : Display searched information

Actual Result : See the following figures

Before Action

After Action

Test objectives : To test report function

Test Data : Choose author=Gibbs from author combo box.

Test Procedure : Choose author from author combo box and click Report button.

Expected Result : Display report form

Actual Result : See the following figures

Before Action

After Action

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!