LIBRARY MANAGEMENT SYSTEM:
DESIGN AND IMPLEMENTATION
Prepared for: Cynthia Xin Zhang, Instructor
ITCS 3160 - 001
Prepared by: Group 4
Darren Adams
Sergey Begun
Andrew Fail
Shawn Haigler
Franklin Lee
April 18, 2007
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
Guidelines and tips
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
Library Management System Design and Implementation: A Case Study in Database Design, Thesis of Project Management
for practice and project guideline
Typology: Thesis
2017/2018
1 / 25
This page cannot be seen from the preview
Don't miss anything!
On special offer
Related documents
Partial preview of the text
Download Library Management System Design and Implementation: A Case Study in Database Design and more Thesis Project Management in PDF only on Docsity!
LIBRARY MANAGEMENT SYSTEM:
DESIGN AND IMPLEMENTATION
Prepared for: Cynthia Xin Zhang, Instructor
ITCS 3160 - 001
Prepared by: Group 4
Darren Adams
Sergey Begun
Andrew Fail
Shawn Haigler
Franklin Lee
April 18, 2007
ii
ABSTRACT
This report describes our group's implementation of a library
management system. We used the Entity-Relationship model to design
a database that will store and organize the library's data. We have
created the database using SQL and populated it with some sample
data. The system can keep track of library cards, customers,
librarians, library locations, books, videos, and the relationships
between them. Using Java Server Pages, servlets, and JDBC, we have
created an Internet-based graphical user interface that allows
customers and librarians to access the system remotely.
TABLE OF CONTENTS
Abstract ................................................................................. ii
- Introduction List of Illustrations iii
- Requirement Analysis
- ER Design
- Relational Database Design
- Normalization
- Physical Database Design
- GUI Design
- Conclusions and Future Work
- Appendices
- A. ER Diagram
- B. Website Layout........................................................
INTRODUCTION
This report will provide a detailed account of the processes our group
used to design and implement a database that can be used to manage
a library system. Each subsection of the report corresponds to an
important feature of database design.
○ author or director
○ year
● Access their own account information:
○ Card number(s)
○ Fines
○ Media currently checked out
○ Media on hold
● Put media on hold
● Pay fines for lost or late items
● Update personal information:
○ Phone numbers
○ Addresses
○ Passwords
Functions for librarians are the same as the functions for customers
plus the following:
● Add customers
● Add library cards and assign them to customers
● Check out media
● Manage and transfer media that is currently on hold
● Handle returns
● Modify customers' fines
● Add media to the database
● Remove media from the database
● Receive payments from customers and update the customers'
fines
● View all customer information except passwords
ER DESIGN
It is clear that the physical objects from the previous section – the
customers, employees, cards, media, and library branches –
correspond to entities in the Entity-Relationship model, and the
operations to be done on those entities – holds, checkouts, and so on
- correspond to relationships. However, a good design will minimize
redundancy and attempt to store all the required information in as
small a space as possible. After some consideration, we have decided
on the following design:
Figure 1: ER Diagram
Refer to Appendix A for a zoomed-in view.
Notice that the information about books and videos has been
separated from the Media entity. This allows the database to store
multiple copies of the same item without redundancy. The Status
entity has also been separated from Media in order to save space. The
RELATIONAL DATABASE DESIGN
After coming up with an Entity-Relationship model to describe the
library system, we took advantage of the special relationships found in
our design, and were able to condense the information to 13 tables.
This new design is a database that combines some entities and
relationships into common tables.
Table 1: Relational Database Schema
Status code desctiption
Media media_id code
Book ISBN title author year dewey price
BookMedia media_id ISBN
Customer ID name addr DOB phone username password
Card num fines ID
Checkout media_id num since until
Location name addr phone
Hold media_id num name until queue
Stored_In media_id name
Librarian eid ID Pay name since
Video title year director rating price
VideoMedia media_id title year
Note: the arrows in the diagram represent foreign key constraints.
PHYSICAL DATABASE DESIGN
The next step was to create the physical database and input some
sample data. In order to turn the relational design into a database, we
ran the following script in UNCC's Oracle database:
CREATE TABLE Status ( code INTEGER, description CHAR(30), PRIMARY KEY (code) ); CREATE TABLE Media( media_id INTEGER, code INTEGER, PRIMARY KEY (media_id), FOREIGN KEY (code) REFERENCES Status ); CREATE TABLE Book(ISBNCHAR(14), title CHAR(128), author CHAR(64), year INTEGER, dewey INTEGER, price REAL, PRIMARY KEY (ISBN) ); CREATE TABLE BookMedia( media_id INTEGER, ISBN CHAR(14), PRIMARY KEY (media_id), FOREIGN KEY (media_id) REFERENCES Media, FOREIGN KEY (ISBN) REFERENCES Book); CREATE TABLE Customer( ID INTEGER, name CHAR(64), addr CHAR(256), DOB CHAR(10), phone CHAR(30), username CHAR(16), password CHAR(32), PRIMARY KEY (ID), UNIQUE (username) ); CREATE TABLE Card( num INTEGER, fines REAL, ID INTEGER, PRIMARY KEY (num), FOREIGN KEY (ID) REFERENCES Customer ); CREATE TABLE Checkout( media_id INTEGER, num INTEGER, since CHAR(10), until CHAR(10), PRIMARY KEY (media_id), FOREIGN KEY (media_id) REFERENCES Media, FOREIGN KEY (num) REFERENCES Card ); CREATE TABLE Location( name CHAR(64), addr CHAR(256), phone CHAR(30), PRIMARY KEY (name) ); CREATE TABLE Hold( media_id INTEGER, num INTEGER, name CHAR(64), until CHAR(10), queue INTEGER, PRIMARY KEY (media_id, num), FOREIGN KEY (name) REFERENCES Location, FOREIGN KEY (num) REFERENCES Card, FOREIGN KEY (media_id) REFERENCES Media ); CREATE TABLE Stored_In( media_id INTEGER, name char(64), PRIMARY KEY (media_id), FOREIGN KEY (media_id) REFERENCES Media ON DELETE CASCADE, FOREIGN KEY (name) REFERENCES Location ); CREATE TABLE Librarian( eid INTEGER, ID INTEGER NOT NULL, Pay REAL, Loc_name CHAR(64) NOT NULL, PRIMARY KEY (eid), FOREIGN KEY (ID) REFERENCES Customer ON DELETE CASCADE, FOREIGN KEY (Loc_name) REFERENCES Location(name) ); CREATE TABLE Video( title CHAR(128), year INTEGER, director CHAR(64), rating REAL, price REAL, PRIMARY KEY (title, year) ); CREATE TABLE VideoMedia( media_id INTEGER, title CHAR(128), year INTEGER, PRIMARY KEY (media_id), FOREIGN KEY (media_id) REFERENCES Media, FOREIGN KEY (title, year) REFERENCES Video );
The next script populated the database with sample data:
INSERT INTO Customer(ID, name, addr, DOB, phone, username, password) VALUES (60201, 'Jason L. Gray', '2087 Timberbrook Lane, Gypsum, CO 81637', '09/09/1958', '970-273-9237', 'jlgray', 'password1'); INSERT INTO Customer(ID, name, addr, DOB, phone, username, password) VALUES (89682, 'Mary L. Prieto', '1465 Marion Drive, Tampa, FL 33602', '11/20/1961', '813-487-4873', 'mlprieto', 'password2'); INSERT INTO Customer(ID, name, addr, DOB, phone, username, password) VALUES
(64937, 'Roger Hurst', '974 Bingamon Branch Rd, Bensenville, IL 60106', '08/22/1973', '847-221-4986', 'rhurst', 'password3'); INSERT INTO Customer(ID, name, addr, DOB, phone, username, password) VALUES (31430, 'Warren V. Woodson', '3022 Lords Way, Parsons, TN 38363', '03/07/1945', '731-845-0077', 'wvwoodson', 'password4'); INSERT INTO Customer(ID, name, addr, DOB, phone, username, password) VALUES (79916, 'Steven Jensen', '93 Sunny Glen Ln, Garfield Heights, OH 44125', '12/14/1968', '216-789-6442', 'sjensen', 'password5'); INSERT INTO Customer(ID, name, addr, DOB, phone, username, password) VALUES (93265, 'David Bain', '4356 Pooh Bear Lane, Travelers Rest, SC 29690', '08/10/1947', '864-610-9558', 'dbain', 'password6'); INSERT INTO Customer(ID, name, addr, DOB, phone, username, password) VALUES (58359, 'Ruth P. Alber', '3842 Willow Oaks Lane, Lafayette, LA 70507', '02/18/1976', '337-316-3161', 'rpalber', 'password7'); INSERT INTO Customer(ID, name, addr, DOB, phone, username, password) VALUES (88564, 'Sally J. Schilling', '1894 Wines Lane, Houston, TX 77002', '07/02/1954', '832-366-9035', 'sjschilling', 'password8'); INSERT INTO Customer(ID, name, addr, DOB, phone, username, password) VALUES (57054, 'John M. Byler', '279 Raver Croft Drive, La Follette, TN 37766', '11/27/1954', '423-592-8630', 'jmbyler', 'password9'); INSERT INTO Customer(ID, name, addr, DOB, phone, username, password) VALUES (49312, 'Kevin Spruell', '1124 Broadcast Drive, Beltsville, VA 20705', '03/04/1984', '703-953-1216', 'kspruell', 'password10'); INSERT INTO Card(num, fines, ID) VALUES ( 5767052, 0.0, 60201); INSERT INTO Card(num, fines, ID) VALUES ( 5532681, 0.0, 60201); INSERT INTO Card(num, fines, ID) VALUES ( 2197620, 10.0, 89682); INSERT INTO Card(num, fines, ID) VALUES ( 9780749, 0.0, 64937); INSERT INTO Card(num, fines, ID) VALUES ( 1521412, 0.0, 31430); INSERT INTO Card(num, fines, ID) VALUES ( 3920486, 0.0, 79916); INSERT INTO Card(num, fines, ID) VALUES ( 2323953, 0.0, 93265); INSERT INTO Card(num, fines, ID) VALUES ( 4387969, 0.0, 58359); INSERT INTO Card(num, fines, ID) VALUES ( 4444172, 0.0, 88564); INSERT INTO Card(num, fines, ID) VALUES ( 2645634, 0.0, 57054); INSERT INTO Card(num, fines, ID) VALUES ( 3688632, 0.0, 49312); INSERT INTO Location(name, addr, phone) VALUES ('Texas Branch', '4832 Deercove Drive, Dallas, TX 75208', '214-948-7102'); INSERT INTO Location(name, addr, phone) VALUES ('Illinois Branch', '2888 Oak Avenue, Des Plaines, IL 60016', '847-953-8130'); INSERT INTO Location(name, addr, phone) VALUES ('Louisiana Branch', '2063 Washburn Street, Baton Rouge, LA 70802', '225-346-0068'); INSERT INTO Status(code, description) VALUES (1, 'Available'); INSERT INTO Status(code, description) VALUES (2, 'In Transit'); INSERT INTO Status(code, description) VALUES (3, 'Checked Out'); INSERT INTO Status(code, description) VALUES (4, 'On Hold'); INSERT INTO Media( media_id, code) VALUES (8733, 1); INSERT INTO Media( media_id, code) VALUES (9982, 1); INSERT INTO Media( media_id, code) VALUES (3725, 1); INSERT INTO Media( media_id, code) VALUES (2150, 1); INSERT INTO Media( media_id, code) VALUES (4188, 1); INSERT INTO Media( media_id, code) VALUES (5271, 2); INSERT INTO Media( media_id, code) VALUES (2220, 3); INSERT INTO Media( media_id, code) VALUES (7757, 1); INSERT INTO Media( media_id, code) VALUES (4589, 1); INSERT INTO Media( media_id, code) VALUES (5748, 1);
INSERT INTO VideoMedia(media_id, title, year) VALUES ( 5725, 'Raiders of the Lost Ark', 1981); INSERT INTO VideoMedia(media_id, title, year) VALUES ( 1716, 'Aliens', 1986); INSERT INTO VideoMedia(media_id, title, year) VALUES ( 8388, 'Aliens', 1986); INSERT INTO VideoMedia(media_id, title, year) VALUES ( 8714, 'Die Hard', 1988); INSERT INTO Hold(media_id, num, name, until, queue) VALUES (1716, 4444172, 'Texas Branch', '02/20/2008', 1); INSERT INTO Librarian(eid, ID, pay, Loc_name) Values (2591051, 88564, 30000.00, 'Texas Branch'); INSERT INTO Librarian(eid, ID, pay, Loc_name) Values (6190164, 64937, 30000.00, 'Illinois Branch'); INSERT INTO Librarian(eid, ID, pay, Loc_name) Values (1810386, 58359, 30000.00, 'Louisiana Branch'); INSERT INTO Stored_In(media_id, name) VALUES(8733, 'Texas Branch'); INSERT INTO Stored_In(media_id, name) VALUES(9982, 'Texas Branch'); INSERT INTO Stored_In(media_id, name) VALUES(1716, 'Texas Branch'); INSERT INTO Stored_In(media_id, name) VALUES(1734, 'Texas Branch'); INSERT INTO Stored_In(media_id, name) VALUES(4589, 'Texas Branch'); INSERT INTO Stored_In(media_id, name) VALUES(4188, 'Illinois Branch'); INSERT INTO Stored_In(media_id, name) VALUES(5271, 'Illinois Branch'); INSERT INTO Stored_In(media_id, name) VALUES(3725, 'Illinois Branch'); INSERT INTO Stored_In(media_id, name) VALUES(8388, 'Illinois Branch'); INSERT INTO Stored_In(media_id, name) VALUES(5748, 'Illinois Branch'); INSERT INTO Stored_In(media_id, name) VALUES(2150, 'Louisiana Branch'); INSERT INTO Stored_In(media_id, name) VALUES(8714, 'Louisiana Branch'); INSERT INTO Stored_In(media_id, name) VALUES(7757, 'Louisiana Branch'); INSERT INTO Stored_In(media_id, name) VALUES(5725, 'Louisiana Branch');
The database was created and filled with 10 Customers, 11 Cards, 3
Locations and 3 Employees, and 15 media items. A Hold relationship
and a Checkout relationship were also created.
GUI DESIGN
The first step in designing the GUI was to choose a means of accessing
the database. After evaluating various options, we settled on using the
JDBC API. The availability of JavaServer Pages on UNCC's servers was
an important factor, as it allowed us to develop our application using a
three-tier architecture. By using JDBC we could separate the
application logic from the DBMS as well as from clients. In addition to
simplifying operations on the database, this architecture makes
extending the functionality of our system easier. When adding a new
feature or improving an existing one, we will not need to change the
database; it will only be necessary to modify the Java portion of the
code.
Before beginning Java development, however, we needed to define a
set of queries that our application would use to communicate with the
Oracle database. The queries are presented below. Note that the terms
labeled are to be filled in by the application after it
receives input from the user and validates it. Note also that complex
procedures that require several steps and modify more than one table
- such as operations to check out media or put media on hold - will
combine several queries into a single transaction, eliminating the
possibility of corrupting the database. Finally, some searches (i.e.
searches for Book or Video entries) may have a variable number of
search parameters, determined at run-time. For example, users will
have the option to search for a book by title only, by author only, by
year only, by all three fields, or by any combination of two fields. For
simplicity's sake, the search queries listed below contain all possible
search parameters, but not their possible combinations.
/* *\
Functions available to customers * / / User login and authentication */ SELECT C.ID, C.name, C.addr, C.DOB, C.phone, C.username, nvl((SELECT 'Librarian' FROM Librarian L WHERE L.ID = C.ID), 'Customer') AS role
SET queue = queue- WHERE media_id =
Functions reserved for librarians * / / Add new customer / INSERT INTO Customer(ID, name, addr, DOB, phone, username, password) VALUES (
(