Improving Wait Times for Interlibrary Lending

A Database Solution for Decentralized Services

(5 minutes read)

Interlibrary loan services are crucial to libraries' capacities to meet the growing needs of their stakeholders. As members of interlibrary lending networks, libraries have access to collection pools made up of their combined resources and are therefore able to offer expanded range of materials to their patrons. This can be crucial in settings such as academia where access to large library collection can have important implications for teaching and learning outcomes

Yet, interlibrary loan systems face a lot of challenges, one of which is delay in delivery of requested items. At some post secondary institutions in BC, for instance, some patrons of faculty and department libraries sometimes experience delays in receiving items they request via interlibrary loan services. One possible reason for such delays could be that interlibrary loan transaction at most such institutions are handled by their main, central libraries. Delegating some services to library branches at department and faculty levels could speed up processing times for requests. My LIBR 554: Database Design final project therefore sought to explore a database solution for handling interlibrary loan services at the faculty and department levels. My project used the Allard Law School library at UBC as case study to examine how a database for managing interlibrary loan services at faculty and department levels might be designed and implemented.

In doing this, I followed a five (5) step design process described in Coronel and Morris (2016), which entailed carefully examining the existing status and environment of the problem or situation to be solved, and then defining the various constraints, objectives, scope and boundaries of the system to be developed. Using the information and insights gained from this process, I then implemented the logical and phyical designs for a faculty-level interlibrary loan database system. The process I used are described in the succeeding sections.

Abstract Design

moscow1

MoSCoW Prioritization Matrix for the abstract design

My project began with data collection - via personal interviews - to obtain up-to-date and accurate information on the perceptions, experiences and attitudes of student patrons and library staff regarding interlibrary loan services. Other sources of information were online resources such as websites detailing various interlibrary loan processes at the institution. On the basis of the information obtained, various needs and objectives of the proposed system were contextualized using the popular MoSCoW Prioritization technique.

Establishing Business Rules

Next, a series of Business Rules and an Entity Relations Model were created to capture the interactions, experiences and processes that would define the new system. This was intended to help provide a thorough understanding of user needs, and how to meet them via the new system's products and services (Coronel & Morris, 2016). The business rules for the new database were defined as follows:

A PATRON may make zero or many REQUEST, but each REQUEST must have one and only one PATRON.
A BOOK may be checked out in zero or many REQUEST, but each REQUEST must check out one and only one BOOK.
A JOURNAL_ITEM may be checked out in zero or many REQUEST, yet each REQUEST must check out one and only one JOURNAL_ITEM
A STAFF member may process zero or many REQUEST, but each REQUEST must be processed by one STAFF member.
A CATALOGUE may represent one or many BOOK, but each BOOK must be represented by one CATALOGUE.
A CATALOGUE may represent zero or many JOURNAL_ITEM but each JOURNAL_ITEM must be represented by one CATALOGUE.

Entity Relations & Normalization

ermodel

Entity Relations Model representing business rules for the new system

The interactions and processes defined in the business rules were used to develop an Entity Relations Model shown above. To eliminate any data duplication and redundancies in the tables created above, I implemented a series of normalization. Coronel & Morris (2016) posit that a Second Normal Form (2NF) occurs when a database table is already in a First Normal Form (1NF) and includes no partial dependencies. In other words, none of the table's attributes are dependent on a portion of the primary key. Similarly, a Third Normal Form (3NF) is reached when a table is already in Second Normal Form (2NF) and contains no transitive dependencies. Following these recommendations, I ensured that all non-key attributes of tables were dependent on their primary keys, and that all tables had single key values with none of them repeating or duplicated. This is depicted in the diagram below:

normalize

Diagram showing normalized tables

Developing & Testing the Database

Having completed the logical aspect of the design process, it was now time to bring the database application to life. To do this, I used Microsoft SQL Server version 12 for Windows Operating System. I used this particular tool/software primarily because it is free, has an intuitive interface and a relatively shallow learning curve . To input the necessary commands for creating the database, I wrote a script using Stuctured Query Language (SQL).

To ascertain how well the new database met the requirements and objectives outlined breviously, I wrote several SQL queries to test for robustness and capacity to handle complex tasks. A few of these queries are illustrated below:

Scenario 1:

A manager at the library at Allard Law School would like to know the status of interlibrary loan requests for various patrons. Ideally, any infomantion retrieved should include addresses and campuses of patrons and be arranged in descending order.

User: Library Manager at Allard Law School Library

Expected query results: PATRON FIRST NAME, PATRON lAST NAME, PATRON CAMPUS, PATRON EMAIL, REQUEST STATUS, and REQUEST DUE DATE

SQL syntax:

Results:

query2

#1: Results of SQL queary for testing database

As indicated in the above query, various columns were referenced in two tables: PATRON - given an alias p - and REQUEST - assigned an alias r. For each of the rows in a given table the INNER JOIN clause was used to march it with a row in the other table, thus producing the results above.

Scenario 2:

A Library Assistant at the Allard Law School library needs information on book and jounal article authorship and title. She would also like to know about their publication dates and book physical description.

User: Library Assistant at Allard Law School Library

Expected query results: JOURNAL ARTICLE TITLE, JOURNAL ARTICLE AUTHOR, JOURNAL DATE, JOURNAL TITLE, BOOK TITLE, BOOK AUTHOR, BOOK PUBLICATION DATE, BOOK PHYSICAL DECRIPTION

SQL syntax:

Results:

QUERY0

#2: Results of SQL queary for testing database

As shown in the results of the second test scenario, the SQL clause, UNION, was used to combine the results of two SELECT statements from two separate tables - BOOK and JOURNAL_ITEM - into a single result set made up of all the rows affected by the SELECT statements.

From the successful execution of the two test queries therefore, it was clear that the new database was well equipped to handle complex and advanced inter library lending query tasks.

Conclusion and Moving Forward

A relational database for handling interlibrary lending transactions may entail more complexity and detail than what is depicted in this project. The prudence of decentralizing interlending services, especially in large post secondary institutions, is also a subject of intense debate. Indeed, there are some who argue strongly for decentralizing such systems and feel that their costs can be lowered by automation (Vervliet, 1981). These are quite interesting and important debates, and given my enthusiasm about systems librarianship, I plan to continue developing more skills and knowledge about how database systems and technologies may be designed and implemented to help libraries pull resources together, save cost, and improve effieciency of service delivery.

I am currently developing a front-end web application to connect to this project and improve its accessibility. I'm also looking into building a viable database product, perhaps for a non-profit organization or a similar entity committed to improving literacy access for marginalized communities.

Works cited

Coronel, C., & Morris, S. (2016). Database systems: design, implementation, & management. Nelson Education.
Vervliet, H. (1981), "Speaking for a Cinderella: unplanned decentralized lending", Interlending Review, Vol. 9 No. 4, pp. 128-130. https://doi.org/10.1108/eb017691