1

CSCI3170 Introduction to Database Systems

Tutorial 3 – Project SpecificationBy Kester Lee

GENERAL INFORMATIONAn overview of the course project

Introduction

• Library Inquiry SystemCommand line interface (in JAVA)Database Management System (Oracle)

• PlatformCSE Linux machine• With JAVA JDK 7.0

Oracle Database• For storing the data

Tutorials will be given on Linux, JAVA and Oracle

Accounts

• The following accounts will be providedAccount Usage

CSE Unix account(For non-CSE students)

• Access the Linux machines• Connect to the CSE VPN

Oracle account(For each student)

• Connect to the database

Grouping

• Grouping– Each group should have three members– If you want to form a one-person group or a two-

person-group, please send an email to the tutor– You will be randomly assigned to a group if you

are not in a group after the registration period

Milestones

Phase Deadline TasksGroup

Registration6/2/2015 • Form a group

Phase 1 13/3/2015 • Draw an ER-diagram• Translate into a relation schema

Phase 2 10/4/2015 • Write a JAVA application• Write a read me file

DEMO TBA • Demonstrate your works

* The deadlines are subjected to change

7

Data Specification

• The data are in four categoriesCategoriesLibrary usersBooksChecked-out records

• They are the format of the data files only– Not meant to be the tables in the final schema

8

Data Specification

• User categories– Category ID: A unique identifier for a category–Max Books: The maximum number of books that can

be borrowed by the library user in the corresponding category. – Loan Period: The number of days that a user can

borrow a book in the corresponding category.

Data Specification

• Library users– User ID: A unique identifier of a user (like student

id in CUHK library system)– Name: The name of a user– Address: The address of a user– Category ID: It indicates which category the library

user belongs to.

Data Specification

• Books– Call number: It is used for the library users to

search for the book. – Number of copies: The number of identical copies of

a book– Title: The title of a book– Author(s): Author name(s) of the book

concatenated as a string with comma character as the delimiter. – Date of publication: The date that a book is published

Data Specification

• System– All numerical values are less the 232 – The system is case sensitive– All dates is expressed as DD/MM/YYYY and have the same

time zone as Hong Kong.– Your program may assume that any value entered into any

input field is correct in format only.– Your program may assume that any data file inputted into it

doesn’t have duplicated row and is correct in format and content.

12

Assumptions and Regulations

• Categories– Each category has a unique category id and it can be used to

identify a category.– Some library users may have the same maximum loan

period or number of book that can be borrowed.

• 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 the same

address. – A user can only belong to one user category

Assumptions and Regulations

• Books– Each book has a unique call number and it can be used to

identify a book. – Some books may have the same title. – A book copy refers to a physically existing book in the library.– Each book copy has a unique pair of call number & copy

number and they can be used jointly to identify a book copy. – The book copy has a copy number begins with 1. – Each book must have at least one author. – An author can be uniquely identified by his/her name. – An author may write more than one book.

14

Assumptions and Regulations

• 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 never check out any book copies. – Some book copies may have never been checked out. – A user may borrow same book copy for more than 1 time. – The return date of a book copy is NULL if the book copy is

not returned.

PROJECT PHASE 1ER-diagram and relation schema

Workflow

STEP 1: Read Sections 3 and 4 of the specification

STEP 2: Draw the ER-diagram accordingly

STEP 3: Transform into a relation schema

STEP 4: Remove any redundancy in the schema

Remarks

• Four input files does not mean that there are four entities in the ER-diagram

• You may need to add extra attributes or transform the existing attributes

• The relation schema should clearly show the key relationships

• The relation schema should not consist of redundant information

PROJECT PHASE 2: JAVA APPLICATION

Requirements for the JAVA application

Workflow

STEP 1: Read Section 5 of the specification

STEP 2: Write the JAVA application

STEP 3: Test with the provided data set

STEP 4: Test with other data sets

20

System Functions

• You have to implement three set of functionsFunctions for administratorFunctions for librarian Functions for library director

• You need to implement all functions in a command line program

21

System Functions

• Functions for administrator– Create table schemas in the database– Delete table schemas in the database– Load data into the database

• Your system should read data files from a user defined directory and insert data in those data files into the database

• Please make sure that it can read the sample data provided

– Show the information of the database• List the existing tables and the numbers of records

System Functions

• Functions for librarian– Search for books (only one method per query)

• By call number (exact matching)• By title (partial matching)• By author (partial matching)

– Output should include• Call number• Title• Author(s)• Number of available copies

– The results of the query should be sorted in ascending order of call number

23

System Functions

• Functions for librarian– Show all check-out records of a library user

• By user ID (exact matching)

– Output should include• Call number• Copy number• Title• Author(s)• Check out date• The book copy of the corresponding check-out record is returned

or not.

– The check-out records should be sorted in descending order of check-out date

24

System Functions

• Functions for library director– Show total number of book checked-out within a period:

• The library director enters the start date and end date• The program will perform the query and return the total number

of book checked-out within the inputted period inclusively.

– Show the N books that are most often to be overdue:• The library director enters a number N• The program return the N books that are most often to be over-

due in terms of call number, title and total number of over-due. • The books should be sorted in descending order of number of

over-due as the table below. • For the sake of simplicity, the check-out record with a null return

date is ignored

25

Q&A