CSCI- Project Library Inquiry System Solution

Description

1. Introduction

You are required to implement a Library Inquiry System for a library so that all information about books in the library, library users, and loans are stored. The system shall support interactive operations from operators. You are required to use Java JDBC API to access the database. Our tutors will give a tutorial on how to use the JDBC API. You are required to implement a Java command line application to realize all system functions stated in this specification.

There are two phases:

In phase 1, you are required to design the database for the system (including an ER-diagram and a relational schema). After the deadline of Phase 1, a suggested solution will be provided. You are required to use the suggested solution to complete Phase 2.

In Phase 2, you are required to implement the Library Inquiry System as a Java command-line program. Our tutors will give tutorials on how to connect to a MySQL database system with JDBC API and deploy your work on the required platform.

This is a group project and each group should have at most 3 members. ONLY one copy of solution is required for each group. Please fill out the group registration form on the blackboard system before the group registration deadline.

2. Milestones

Preparation

• Read the document thoroughly and make sure you understand all the assumptions and regulations stated in Section 4.

Phase 1 (20 %)

• According to the data specifications in Section 3, design an ER-diagram and transform it into a

relational schema without any redundant fields and tables.

Phase 2 (80 %)

• According to the suggested solution of Phase 1, implement a Java application that fulfills all requirements stated in Section 5.

• Debug your system with different datasets and user inputs.

• Write a readme file to describe the compilation and deployment of your system.

CSCI3170 Project (Spring 2020) Library Inquiry System

3. Data Specification

All data files of the system are in Linux text file format (i.e. Newline character is \n) encoded in ASCII. Your Java command line application is required to read records stored in the files and inserts them into appropriate tables of the provided MySQL DBMS via JDBC API. There are four input files, a list of categories, a list of library users, a list of books and a list of check-out records. Each line of each input file is a sequence of attributes delimited by a tab (\t) character. The definition of each attribute in each input file is defined in the corresponding subsections. The order of the attributes within a line of each input file follows that of the attribute in the corresponding subsection. A sample data set will be provided after the deadline of Phase 1.

3.1. Category – category.txt

Library users are divided into different categories. The loan period and the maximum number of books that can be borrowed by a library user are determined by his or her category. Each category has a unique category ID.

Attribute Name	Format	Description
Category ID	Non-empty positive	A unique identifier for a category.
	integer with 1 digit
Max books	Non-empty positive	The maximum number of books that can be borrowed by
	integer with at most 2	the library user in the corresponding category.
	digits
Loan period	Non-empty positive	The maximum number of days for the library user in the
	integer with at most 2	corresponding category to borrow a book.
	digits

3.2. Library Users – user.txt

Each library user has a unique user ID and belongs to exactly one category.

Attribute Name	Format	Description
User ID	Non-empty string with	A unique identifier of the library user.
	10 characters
Name	Non-empty string with	The name of the library user.
	at most 25 characters
Address	Non-empty string with	The address of the library user.
	at most 100
	characters
Category ID	Non-empty positive	It indicates to which category the library user belongs.
	integer with 1 digit

CSCI3170 Project (Spring 2020) Library Inquiry System

3.3. Books – book.txt

Each book is a literary composition written by one or more authors and a library stores a number of physical copies for each book. Each book has a unique call number

Attribute Name	Format	Description
Call number	Non-empty string with 8	It is used for the library users to search for the book.
	characters
Number of	Non-empty positive	The number of identical copies of the book.
copies	integer with 1 digit
Title	Non-empty string with	The title of the book.
	at most 30 characters
Author(s)	Non-empty string with	Author name(s) of the book concatenated as a string with
	at most 25 characters	comma character as the delimiter.
	for each author
Date of	Date format see 4.1	The date that the book is published
publication

3.4. Checked-Out Records – check_out.txt

Each check-out record shows a borrow history of a library user.

Attribute Name	Format	Description
Call number	Non-empty 8	The call number of the checked-out book copy.
	characters
Copy number	Non-empty 1 digit	The copy number of the checked-out book copy.
	Positive integer
User ID	Non-empty 10	The user ID of the borrower.
	characters
Check-out date	Date format see 4.1	The date that the book is checked-out.
Return date	Date format see 4.1 &	The date that the book is returned.
	4.7

CSCI3170 Project (Spring 2020) Library Inquiry System

4. Assumption and Regulations

4.1. System

• All numerical values will not be larger than the maximum integer value that can be handled by Java.

• The system is case sensitive.

• All dates follow the format “DD/MM/YYYY”, e.g. “16/09/2019”.

• There is no duplicate row in any input files.

• You may assume that any user inputs to the system are correct in format only.

• You may assume that all data files are correct in format and content.

4.2. Categories

• Each category has a unique category id and it can be used to identify a category.

• Some categories may have the same max books or loan period.

4.3. Library Users

• Each library user has a unique user ID and it can be used to identify a library user.

• Some library users may have the same name or address.

• A user can only belong to one category.

4.4. Books

• Each book has a unique call number and it can be used to identify a book.

• Each book must have at least one author; Some books may have the same title or author.

4.5. Book Copies

• A book copy refers to a physically existing book copy that can be borrowed by a library user in the library.

• Each book copy of a book has a copy number. If a book has n book copies, the copy number of those n book copies ranges from 1 to n. For example, if a book has four copies, these four book copies have the same call number, and their copy numbers are 1, 2, 3 and 4.

• Each book copy has a unique pair of call number and copy number and they can be used jointly to identify a book copy.

4.6. Authors

• An author can be uniquely identified by his/her name.

• An author may write more than one book.

4.7. Checked-Out Records

• Each check-out record has a unique set of {Call number, Copy number, User ID and Check-out date} and this set of attributes can be used to identify a check-out record.

• Some library users may have never checked out any book copies.

• Some book copies may have never been checked out.

• A user may borrow the same book copy for more than one time.

• The return date of a book copy is NULL if the book copy is not returned.

• For each book copy, there is only one check-out record with a NULL return date.

CSCI3170 Project (Spring 2020) Library Inquiry System

5. System Function Requirements

You are required to write a simple command line application in Java. After performing a function, the program should display the last appeared menu. The Java program should provide the following functions:

5.1. Administrator

The system should let administrators to perform the following operations:

• Create table schemas in the database: This function creates all the tables for this system based on the relational schema given.

Figure 1: Example interactive input and output while creating table schemas.

• Delete table schemas in the database: This function deletes all existing tables in the system.

Figure 2: Example interactive input and output while deleting table schemas.

• Load data from a dataset: After a user enters the path of the folder that contains the data files, the system reads all data files from the user-specified folder and inserts the records into the appropriate table in the database. (Your program can assume that the user-specified folder must contain all 4 data files. These 4 input files are named category.txt, user.txt, book.txt and check_out.txt. Each data file stores the data corresponding to its filename.)

CSCI3170 Project (Spring 2020) Library Inquiry System

CSCI3170 Project (Spring 2020) Library Inquiry System

5.2. Library User

• Search for books: The system has to provide an interface to allow a library user to search for the books in the library in three different ways

o By call number (exact matching) o By title (partial matching)

o By author (partial matching)

You can assume that only one searching method can be selected by the library user for each query and the whole string entered by the library user is considered as one search word (e.g. When a user entered “Database Concept”, The system will consider “Database Concept” as one and only one search keyword instead of two search keywords “Database” and “Concept”). After the library user entered the search keyword, the program should perform the query and return all matching books in terms of their call number, title, authors and number of available copies. The results of the query should be sorted in ascending order of call number and outputted as a table as follows:

Figure 5: Example input and output while searching for books

• Show all check-out records of a library user: The system has to provide an interface to allow a library user to show all his/her check-out records of with a given user ID. After the library user enters his/her user ID, the program will perform the query and return all the matching check-out records in terms of call number, copy number, title, authors, check out date and whether the book copy of the corresponding check-out record is returned. The check-out records should be sorted in descending order of check-out date and outputted as a table as follows:

CSCI3170 Project (Spring 2020) Library Inquiry System

Figure 6: Example input and output while showing all check-out records of a library user

5.3. Librarian

• Borrow a book copy: A librarian can perform the book borrowing procedure through the Library Inquiry System. First, he/she needs to input call number and copy number of the book copy being borrowed and the user ID of the library user. Then the system should check whether that book copy is available to be borrowed (i.e. There is no check out record of the specified book copy with NULL return date). If the book copy is available, it is then borrowed and a new check-out record of the specified book copy and user with NULL return date should be added to the database accordingly. Finally, there should be an informative message whether the book copy can be lent successfully in layman terms.

Figure 7: Example input and output while a librarian processes a book borrowing request

• Return a book copy: A librarian can perform the book returning procedure through the Library Inquiry System. First, he/she needs to input call number and copy number of the book copy being borrowed and the user ID of the library user. Then the system should check if a check-out record corresponding to the specified user ID, call number and copy number exists. If such record is found, the book copy can be returned, and the return date of the check-out record found is updated to be the current date of

CSCI3170 Project (Spring 2020) Library Inquiry System

CSCI3170 Project (Spring 2020) Library Inquiry System

CSCI3170 Project (Spring 2020)					Library Inquiry System
6. Grading Policy
The marks are distributed as follows:
	Phase	Content		Mark
				Distribution
1		ER-diagram		10%
		Relational schema	10%
		(based on your ER-diagram)
	2	Java application	80%

6. • There will be a mark deduction if your application is terminated unexpectedly during the demonstration.

6. • You are not allowed to modify any source code during the demonstration.

6. • All members in the same group will receive the same marks for the project. In order to encourage every student to participate in the project, a question about this project may be asked in the final examination.

6. Demonstration

6. • All groups need to sign up for a demonstration on their works for phase 2, the registration page would be posted on the course website later.

6. • All group members should attend the demonstration.

   • The duration for the demonstration for each group is about 20 minutes.

   • The Java application will be complied and tested in a Linux 64bit machine in the CSE department.

6. • The dataset used in the demonstration may be different from the dataset provided for testing.

6. Submission Methods

8.1. Phase 1

• Submit a PDF file (one copy for each group) to the collection box at Blackboard platform.

• The PDF file should consist of your group’s ER diagram, relational schema, the group number, the names and the student IDs of all group members of your group.

8.2. Phase 2

• Submit a ZIP file (one copy for each group) to the collection box at Blackboard platform. The ZIP file should consist of all your source codes and a README file (README.txt), which contains:

o The group number of your group

o The name and the student ID of each group members of your group o Methods of compilation and execution

P a g e 11 | 11