USAFA Base Library Intralibrary Loan Database Debbie Worthington

USAFA Base Library Intra-library Loan Database Debbie Worthington LI 844 Database Design

Introduction to the US Air Force Academy Base Library --The United States Air Force Academy (USAFA) Base library is a small military library supporting the occupants of the installation. --Our patrons range from elderly (retired military) to infants (children of military and civilian personnel). We serve active duty, civilians and their families; the Preparatory school (preparing possible Academy entrants); base residents and some cadets. --Our three main missions include supporting the military mission, voluntary education (supplying language materials, CLEP & Dantes study guides), and quality of life (programs and recreational material). --We own over 33, 000 items and circulated over 68, 000 in the last fiscal year, according to the Library Annual Report. We mainly exchange books with the large academic cadet, or Mc. Dermott, library, since we do not have the funds for a courier service to the public libraries or other bases. We share the same ILS, making these transactions “intralibrary loans”.

Interviews for USAFA Base Library Inter -Library Loan Database For our director: Of course, we have three-pronged mission stated in the AFI (supporting the mission, education, and quality of life). What do you feel is the single most important reason for the existence of the organization? We support the military. How would you describe what our organization does? We encourage the love of reading and life-long learning. Another focus is to be useful in any way possible. What information would you like to see included in this database? What (title), where, who (patron borrowing), when (when sent and when due), to see specific users (i. e. cadets) and notes. Are there any related areas you would like to see tracked? Time (such as due dates passed) and a note field for comments (i. e. book condition). Will there be any reports generated from this data? Monthly stats, which will make up the annual stats submitted in the LARP (Library Annual Report).

Self-Interview for Debbie Worthington, circulation technician responsible for ILL data: How would you describe the type of work you do? A large part of my job is ensuring our organization has access to correct information (patron databases, for instance), notifying patrons of over-due items (ensuring accountability for government property), and keeping correct records of the intra-library loan transactions. What kind of data do you work with? I work with names, dates, and titles of books. What type of reports do you generate? I only generate one report: monthly statistics. What types of things do you keep track of? I track ILLs coming from (due dates, who checked out to) and to (who checked out to) the cadet library. These records are kept 3 years, as per AFI (Air Force Instruction) regulation. What do you expect from this database? Currently, the ILL data is stored inefficiently in several spreadsheets and tables. The spreadsheet tracking patron (and my) due dates is growing too large to be efficient. There is a separate table to track the number of cadets borrowing from us, but I am dissatisfied with that. Additionally, I want to add information that would aid with continuity if necessary (i. e. names and phone numbers of the staff I interact with at the cadet library). It would also be great if the information entered were directly linked to my stats table (which I send to the director monthly); that would be much more efficient!

What was gleaned from the interview process: --As I am solely responsible for the ILL process, I was already intimately aware of the processes. As I had taken them over from predecessors, I inherited data structures and continued using them, although it would occasionally occur to me there were better ways to track the information. --It was interesting to interview my boss and pick out several aspects she felt would improve the process, such as creating a field to denote book condition, to prevent borrowers from being held responsible for existing damage. --It was also valuable for me to closely inspect the process and determine what changes would improve it.

Artifacts My active file cabinet

Screenshot of the ILL spreadsheet

ILL Request Form (Reproduction) � INTERLIBRARY LOAN REQUEST FORM � BASE/COMMUNITY CTR LIBRARY � Only 1 item per form � Name on Account: -----------� Book Title: ____________ � Author: ______ Item barcode: __________ � ILL Librarian Use ONLY: ---------- (call #) � I • • • • • • • • • • • • • ~ • • • • • • • � Date: Patron Needs By____ or Anytime � Contact Info: Phone: ______ Email: _______ � I understand that I will have to pay the full price of this item if it is lost or damaged. � Signature of Person Requesting: --------------Staff Initials: ---� ILL LIBRARIAN USE (f)NLY � Date received: ----~~-------- Date of request: _ � Date returned to CADETLib_----

Mission Statement & Objectives Mission statement for the USAFA Base Library ILL database is: to accurately track intra-library loan activity between the USAFA Community (COMLib) library and the Mc. Dermott, or Dean of Faculty (DFLib), library.

Mission Objectives of the USAFA Base Library ILL Database --Supply data for the monthly statistics report, which combines for the annual statistic in the LARP (Library Annual Report). --Track ILLs coming to our patrons from the cadet library --Track ILLs going to the patrons of the cadet library --Track the number of times specific titles are being requested (to protect copyright) --Track the number of times cadets borrow from us (as opposed as staff/faculty) --Track due dates for patrons --Track due dates for the libraries --Keep notes of book condition, so patrons aren’t blamed for any pre-existing damage --Store contact info of DFLib circulation staff --Improve efficiency of ILL transactions

Analysis of the Current Database --There is no coherent database for the USAFA base library ILLs. --We have a spreadsheet containing patrons, titles of the books they request (only for titles requested from DFLib), their due dates and my due date. --There is a separate table for monthly statistics (i. e. , the number of books we borrow from DFLib which I must fill in manually (for instance, count the number of books we borrowed and enter the number). It would be great if the table could be linked to the new database and be populated automatically as titles are input into the system. --We also keep a separate table for the number of cadets who request titles from us, for statistics, and I would like that table linked also. � --Additionally, the whole point of keeping our ILL records for 3 years has to do with copyright law, so I would need to be notified when a title is being repeatedly requested—not just relying on my memory. � -- While I need to keep track of patron names while books are checked out, I need some way of tracking due dates, because I send a “gentle reminder” within 3 days of the due date. � --I really shouldn’t keep patron names on a long term list—for privacy reason—when it’s really titles I need to track. � --I may need to track “repeat offenders”—those who lose or damage books repeatedly

Preliminary Fields & Tables Patrons Table Intralibrary Loan Item Table Patron ID Item. Title Patron. First. Name Item. ID Patron. Last. Name Item. Call# Patron. Type Library. Due. Date Notes Intralibrary Loans Table Patron. ID Item. ID Date. Requested Contacts Table Contact. Position Date. Received Contact. First. Name Patron. Due. Date Contact. Last. Name Date. Returned Contact. Phone Contact. Email Monthly Statistics Table Month/Year COMLib. ILLs DFLib. ILLs Total. ILLs Cadets Preps

Business Rules for USAFA Base Library ILL Database: --Date Format (dd mm year, which is standard in the military) --"Return date" must occur same as or later than "Receive date" (for books we borrow from DFLib) --"Return date" must occur same as or later than "Send date" (for books borrowed by DFLib) --We need to know if borrowers from DFLib are actually cadets (as opposed to staff/faculty) for our statistics; also, we need to know which COMLib patrons are preparatory school students. --A view table will be helpful for tracking the number of ILLs of both types (to DFLib from us and to us from them), for monthly stats --Patron types will be limited to four choices: COMLib. Patron, Prep, Cadet, DFLIBPatron --Patron. ID and Item. ID will be limited to 14 -digit numerical codes

Proposed Database Structure Intralibrary Loan Item Table Key Type Patrons Table Key Type Item. Title CAK Patron ID PK Item. ID PK Patron. First. Name CAK Item. Call# Patron. Last. Name CAK Library. Due. Date Patron. Type CAK Notes Intralibrary Loans Table Key Type Patron Type Stats (Linking) Key Type Patron. ID PK/FK Patron. Type CPK/FK Item. ID FK COMLib. ILLs (Prep + COMLib. Patrons) CPK/FK DFLib. ILLs (Cadet + DFLib. Patrons) CPK/FK Date. Requested Date. Received Patron. Due. Date Monthly Statistics Table Key Type Date. Returned Month/Year PK COMLib. ILLs Contacts Table Key Type Contact. Position PK Contact. First. Name CAK Contact. Last. Name CAK Contact. Phone Contact. Email DFLib. ILLs Total. ILLs Cadets Preps

Summary of Design Process I found this to be an exciting process! While creating the Subject List & Table/Field Lists, I started seeing more possibilities for dealing with our ILL data. I thought I had the main Tables in the list, but as the tables were created, alternate routes became apparent. For instance, I had toyed with having separate tables for COMLib and DFLib ILLs, but realized the field names would get too long and complicated (e. g. COMLib. Patron. Last. Name), so I created a “Patron. Type” field in the table instead. That created more issues, such as how to create a table to track statistics from one field (“Patron Type”). As I thought, more business rules presented themselves also as I created the tables, and I’m sure even more will pop up as I create the database.