SWEN304-Project 2 Solved

To do the project you will use  

•        PostgreSQL Database Management System to build a database,

•        Java programming language to write a transaction program, and  

•        Java Database Connectivity (JDBC) library to exchange data between your database and your transaction program.

 

To familiarise yourself with JDBC use the lecture and review notes on JDBC published on the course web page as an introductory resource for learning how to use JDBC in your programs.

You can find out more about JDBC from PostgreSQL and Java Tutorial

(http://ecs.victoria.ac.nz/technical/java/tutorial/index.html). You will find both manuals in the Technical web pages that are referenced from the SWEN 304 website. Do not print out all of the tutorials.

Short Description of the Problem 
In this project, you will implement a simple library system. The system keeps track of books, authors, library customers, and their relationships.

Using your system, a library clerk should be able to retrieve information about books, authors, and customers. Also, the clerk should be able to update the appropriate records when a customer borrows or returns a book. Your program should send polite and understandable messages in a plain language to users.

Your program should be written in such a way that allow multiple instances of same program to access and update a common database without introducing any inconsistencies. Therefore, you should make a real transaction program.

Setting Up the PostgreSQL Database 
Your transaction program will need to use Java libraries, PostgreSQL, and the PostgreSQL JDBC driver. To use all of these, you should insert the following command in your .cshrc file:

need comp302tools   or  need postgresql 
 

It should be inserted between the lines

need SYSfirst ... need SYSlast

The comp302tools package sets up the CLASSPATH to include "postgresql94.jar" (which contains a JDBC driver manager), and also does a "need java2 " and a "need postgresql". So if you already have any of these other “need” lines you can delete them (although it won't harm to keep them).  

 

Create your database, named userid_jdbc by typing

> createdb userid_jdbc 
Note, this is the only database that should be set up to allow access from a Java program. Please use your ECS username as your userid.

 

To set up the password for your new database <userid>_jdbc, open an existing database (say with the name <userid>) by typing:

> psql userid

and then proceed with

userid =>ALTER USER ECS username WITH PASSWORD
’newpassword’;

This will set your password for the database <userid>_jdbc. Note, this password should NOT be the same as your system password. It should protect your database from possible misuse, but need not to be as secure as your system password. Type

userid=>\q

to close your old database.

 

To load your database with schema and data, you should use the file  

Project2_19.data

which contains CREATE TABLE and INSERT commands and data to populate your database. You will find the file on the Assignments&Projects course web page. Store it in your private directory. The command

> psql –d db_name -f ~/Project2_19.data

executes SQL CREATE TABLE and INSERT commands in the file /Project2_19.data and populates your database. Note, db_name will be userid_jdbc.  

 

For this project, you are not asked to use your database in an interactive mode, but if you need to (to try out a query, for example), do it in the same way that you did in Project 1.

 

 

Eclipse 
 

If you are using Eclipse you need to: 

 

1)  Select the project you are working on. 

2)  Select "Project" -> "Properties" from the Menu 

3)  Select "Java Build Path" from the window that appears 

4)  Select "Add External Jars" 

5)  Navigate to "/usr/pkg/lib/java/postgresql94.jar" 

 

The Structure of the Database Transaction Application 
The database transaction application has the following components:

 

•      A library database (in the file Project2_19.data)

•      A Java program named LibraryUI.java that contains the code for the GUI and the main method.

•      A Java file named LibraryModel.java that should implement methods that provide functionality to the options offered by the GUI in LibraryUI.java.

 

A dump of the schema and data for the database, and the two java files are in the Assignment web page. You will need to complete the LibraryModel.java file, but you should not need to modify LibraryUI.java at all. The Library Database 

Table 1 contains descriptions of the library database tables, and Table 2 contains description of the referential integrity constraints.

 

In the book table, the attribute NumOfCop is the number of copies of a book that the library owns, and the attribute NumLeft is the number of copies of a book that are currently available in the library. The difference between NumOfCop and NumLeft indicates how many copies of a book are currently out on loan. A book can be simultaneously loaned up to NumOfCop customers.

 

Attribute
Data Type
 Length
Null
Def
Condition
Table name: Customer, Primary Key: CustomerId
CustomerID
Integer  
4  
N
0
≥ 0  
L_Name  
Char  
15  
N
  
 
F_Name  
Char  
15  
Y
  
 
City  
Char  
15  
Y
  
(Wellington, Upper Hutt, Lower Hutt)
Table name: Cust_Book, Primary Key: CustomerId + ISBN
CustomerId  
Integer  
4  
N
0
≥ 0
DueDate  
Date  
  
Y
  
 
ISBN  
Integer  
4  
N
0
≥ 0
Table name: Book, Primary Key: ISBN
ISBN  
Integer  
4  
N
 0
≥ 0
Title  
Char  
60  
N
  
 
Edition_No  
SmallInt  
2  
Y
1  
> 0
NumOfCop
SmallInt
2
N
1
 
NumLeft
SmallInt
2
N
1
 
Table name: Author, Primary Key: AuthorId
AuthorId
Integer  
4  
N
0
≥ 0
Name
Char  
15  
Y
 
 
Surname  
Char  
15  
N
 
 
Table name: Book_Author, Primary Key: (ISBN + AuthorId)
ISBN
Integer  
4  
N
0
≥ 0
AuthorId  
Integer  
4  
N
0
≥ 0
AuthorSeqNo  
Integer  
2  
Y
1  
> 0
Table 1.

 

 
 
Referencing Relation Schemas
 
Customer
Cust_Book
Book
Author
Book_Author
Customer  
 
(d, r), (m, r)
 
 
 
Cust_Book
 
 
 
 
 
Book
 
(d, r), (m, r)
 
 
(d, d), (m, c)
Author
 
 
 
 
(d, d), (m, c)
Book_Author
 
 
 
 
 
Table 2.

 

The referential integrity constraints are specified in Table 2. The relation schemas in the column headers are referencing the relation schemas in the row headers. If the corresponding table cell is empty, there is no referential integrity constraint defined between the two relation schemas. If the cell is not empty, then there is a referential integrity constraint defined between the two relation schemas, and the cell contains a pair (operation, action). Operation is either d (delete), or m (modify), and action can be r (restrict), c (cascade), n (set to null), or d (set default).

 

The LibraryUI.java and LibraryModel.java Files 
You should copy LibraryUI.java and LibraryModel.java into your own directory, compile them both using

>javac LibraryUI.java and run using  

> java LibraryUI

You will see that the program runs, setting up a GUI, and asking for a username and database password. However, at present none of the buttons on the GUI do much. They only display some messages in the GUI result area. LibraryUI.java, which contains the code for the GUI, is complete and you do not need to change it. LibraryModel.java contains a skeleton consisting of stubs of all the methods that provide the functionality for the buttons of the GUI. You need to implement all these methods.

 

The GUI contains an area for displaying results of queries, and a menu (tabs) with the following options:

•   Book o Book Lookup o Show Catalogue o Show Loaned Books

•   Author o Show Author o Show All Authors

•   Customer o Show Customer o Show All Customers

•   Borrow Book

•   Return Book

•   File  o Exit

 

When the GUI is constructed, it will create an instance of the LibraryModel class, passing it a username and a database password. Each option on the GUI will then call a method of the LibraryModel object, and display the string returned by the method in the result area.

 

Note, you can change the given GUI, but you should not expect to receive extra marks for it.

Demo Program 
There is a demo version of the program in the ~comp302/ directory that you can run on your own database. The demo program was initially made by Pavle, but was corrected and decorated by Jerome Dolman. To run the demo version, you need to:

•        Create your own <userid>_jdbc database,

•        Register your new password with PostgreSQL,

•        Populate your own <userid>_jdbc database using Project2_19.data file  Type:

> demo comp302 project2 from Unix shell prompt, and

•        Enter your <user_name> and the new password in the Authentication Pane that will appear on the screen. Note:  

•        This is just a demo, you may want to make a much better LibraryModel.java program.  

What should you do 
1.     Load your database from the file Project2_19.data.

2.     Complete the implementation of the LibraryModel class:

a)     Define the constructor to open a connection to the database

b)     Define each of the methods to perform an appropriate SQL query (or queries) and return the result of the query as a string to be displayed.

 

When implementing methods of the LibraryModel class, pay attention to commands for handling transactions correctly (for starting and ending a transaction, and locking), and to JDBC commands (for acquiring a driver object, establishing a connection to the database, controlling the transaction environment, and executing SQL statements using Statement and PreparedStatement objects). For the sake of performance, allow read only queries to read all database items, regardless whether they are locked or not. Most of your methods will need to handle exceptions raised by the JDBC commands. The most important goal of your transaction program is to leave the database in a consistent state after executing each of the requested database operations.

 

An important part of the database application you are going to implement is the method for the

“BorrowBook” transaction. After receiving ISBN, CustomerId, and DueDate values from the GUI that method needs to perform the following actions (assuming that none of the actions fails):

 

1.     Check whether the customer exists (and lock him/her as if the delete option were available).

2.     Lock the book (if it exists, and if a copy is available).

3.     Insert an appropriate tuple in the Cust_Book table.  

4.     Update the Book table, and

5.     Commit the transaction (if actions were all successful, otherwise rollback)

 

This transaction is intended to work in a multi-user transaction-processing environment where there are multiple copies of your program running at the same time. To allow you (and the marker) to test whether your program works correctly, insert an interaction command between steps 3 and 4 above. This interaction command should put up a dialog box and ask the user to click YES/OK to continue. The effect of this is to stall the processing of the program while you run another copy of the program and invoke a transaction that should interfere with the transaction that is part way through. This way, you can simulate contention for the same database items, and check whether your program:  

 Avoids lost update, dirty and unrepeatable read for database update transactions and  Allows reading all database items for read only transactions.

 

Apply the steps, similar to steps 1 to 5 above also when implementing the Return Book function. Of course, this time you need to delete an appropriate tuple from the Cust_Book table.