Appendix A:
Library Database Case Study
Introduction
The Library Database Case Study provides an overview of the operations at the
West Municipal Library, describes the daily library functions, and presents the
database that was designed for the library.
Overview of Library Operations
Before a database for librarians and members was implemented, an interview
was conducted with the librarians at the West Municipal Library to assess the
library’s business needs. The decisions that the database designer made during
the design process are explained in the following sections. The following figure
shows the overall schema of the database that was designed for the library.
member
PK member_no
lastname
firstname
middleinitial
photograph
item
PK isbn
FK1 title_no
translation
cover
loanable
reservation
PK,FK1 isbn
PK,FK2 member_no
log_date
remarks
juvenile

city
state
zip
phone_no
expr_date
copy
PK,FK1 isbn
PK copy_no
FK2 title_no
on_loan
Library
Database Diagram

2 Appendix A: Library Database Case Study Daily Library Functions
Many daily library functions exist. The following are some of the
most important.
Uniquely Identifying Books
Some books may have the same title; therefore, titles cannot be used as a means
of identification. Librarians call books items. Items are identified by the
International Standard Book Number (ISBN). Books with the same title can
have different ISBN numbers if they are in different languages and have
different bindings (hard cover or soft cover).
Reserving Books
If a member wants a book that is out on loan, the book is placed on reserve for
them. When the book arrives, a librarian must notify the member who has been
waiting the longest. Members can have as many as four books on reserve at
one time.


The screen also displays information about a member’s outstanding loans,
including title, checkout date, and due date. This information is useful because
it is presented in a chronological sequence, with the most overdue loan
appearing first and the most recent loan appearing last. Highlighting also
indicates loans that are overdue or are about to become overdue.
If a member’s account is in order, a librarian checks out the books. Librarians
check out books by running a scanner down the book spines (the ISBN and the
copy number are encoded on the spines). The ISBN, title, and author
information then appear on the computer screen. If the books are not loanable, a
warning message appears.
Checking In Books
When books are returned, librarians check them in by running a scanner down
the book spines. The ISBN, title, and author information then appear on the
computer screen, as well as the member number and name and the book’s
due date.
Occasionally, books are accidentally reshelved before librarians check them in.
If a member tries to check out a book that the database lists as checked out,
librarians need to be able to access the checkout information, including the
member’s name, check out date, and due date. If a member presents a book to
check out that is still officially checked out to another member, a message
appears that alerts librarians that the book is already checked out. Then
librarians can update their records immediately by being forced to clear the
previous loan before they continue with the checkout.
Generating Usage Reports
Occasionally, librarians must compile usage information, mostly for the Town
Council or the Planning Commission. These groups usually want to know
information, such as the volume of circulation, the popularity of various books,
the reliability of return, and the average length of a borrowing term. Therefore,
the librarians need to be able to prepare quick summaries of this information.

1
NN
Jun
Mar
Nov
01
01
05
1980
1978
1982
PK, FK NN NN
member_no street city state zip phone_no expr_date
1
2
6
Elm St
Bowery Ave
Bowery Ave
Seattle
Seattle
Kent
WA
WA
WA
98022
98022
98206
NULL
(206)555-1212

Sally
Jack
A
R
NULL
B
A
NULL
~~~
~~~
~~~
~~~
~~~
PK NN NN
member_no lastname firstname middle_i photo
member
member

The member table is the master table, while adult and juvenile are subtables.
All three tables use the member_no column as a primary key. Since the values
in this column are different for each member and uniquely identify each row of
information, the member_no column is a good choice for a primary key.
These entities could have been modeled in several different ways: as a single
table or as member and juvenile tables. If a single table had been used for all
members, many addresses would have been duplicated because juveniles in this
model have the same address as their parents.
Librarians need to be able to track birth dates of juveniles only, so splitting the
membership information into several tables eliminates the null column values
that would have resulted for the birth dates of adults.
Dividing the tables in this fashion also models the scenario in a way that

1
2
1
1
2
3
4
4
FK, NN NNPK, FK PK
copy
copy
PK FK, NN
isbn title_no language cover loanable
1
2
3
4
5
1
2
3
4
2
English
French
French
NULL
English
softback
NULL

~~~
~~~
~~~

The item table has a loanable column. Rather than including information from
this column in the copy table, the database designer assumes that all copies of a
particular item are either loanable or not loanable.
Notice that the copy table has a primary key made up of two columns. This type
of primary key is called a composite key. The combination of isbn and
copy_no uniquely identifies each row in the table.
The copy table contains a duplicate title_no column. This group of tables
has been denormalized to reduce the number of joins that are needed to
retrieve information.
The on_loan column in the copy table is derived data—data that could be
generated with a query each time that the information is needed. But the
information is kept in the table to make it readily available and to reduce the
number of calculations that must be performed. The on_loan column is
populated by using information from the loan table (shown below). Because the
loan table changes frequently, locks could prevent a user from obtaining this
information. The copy table is more likely to be used in a read-only fashion, so
it would not be necessary to prevent users from accessing information that is
stored there.