辅导案例-CSE 132A-Assignment 2
Programming Assignment 2
CSE 132A Fall 2019
Due: by 1:00pm on Friday, Nov 22 (electronically)
The goal of the second assignment is the development of a Java program that follows the money trail by
calculating the transitive closure of a database relation that shows fund transfers between accounts.
As discussed in class, the transitive closure of a relation cannot be expressed in SQL without extension
with recursion, and the latter is not efficiently implemented in existing SQL engines. For this reason you
need the added expressive power of a general-purpose programming language, like Java. The project will also
introduce you to the standard Java Database Connectivity (JDBC) interface for building Java applications
on top of databases. This is an individual assignment. The usual criteria of academic integrity apply.
Computing Environment
• Your application will be built using the Postgres db server.
• The programming language will be Java.
• You will use JDBC to connect to the database.
Find the JDBC driver for Postgres, as well as documentation, at https://jdbc.postgresql.org/download.html.
Use the latest version (42.2.8).
Database Schema Recall our schema of a bank:
customer (name: string, credit: integer)
account (no: string, balance: float)
depositor (cname: string, ano: string)
where depositor.cname and depositor.ano are foreign keys referencing customer, respectively account. As
usual, primary keys are underlined. Add to these a table
transfer (src: string, tgt: string, timestamp: date, amount: float)
where src and tgt are foreign keys referencing account. Rows in this table state that a fund transfer has
taken place at the given time, transfering the given amount from source account src to target account tgt.
The graph The transfer relation induces a relationship between customers, funds (A,B), according to
which customer A funds customer B if and only if A is the depositor of an account accA and B is the
depositor of an account accB , such that there is a tuple in relation transfer whose src attribute is accA and
whose target attribute is accB .
This connection between customers can be modeled as a directed graph in which customers are represented
by nodes, and a tuple (c1, c2) in funds is an edge from node c1 to node c2.
1
The transitive closure You will compute the table
influence(from, to)
which records that customer to can be reached from customer from in the graph along a path of one or more
funds edges. In other words, table influence is the transitive closure of relationship funds.
Requirements
• You will implement the computation of the transitive closure using semi-naive evaluation and logarith-
mic convergence.
• Your Java program must create and fill table influence using the information in table transfer.
• You may create any other auxiliary helper tables you might need. For example, as shown in class,
you will need a Delta table to detect termination of the computation and to use in the semi-naive
evaluation. Any extra tables must be created from your Java program. Please make sure to clean up
after your program, dropping at the end the Delta table as well as any other auxiliary tables you have
created.
• Make sure that your program can be run repeatedly without requiring manual dropping of tables. To
this end, start the program by dropping the influence table and end it by dropping all
auxiliary tables you may have created. This will help you in development and will allow us to
re-run your program on different inputs. We cannot intervene manually in 150 submissions and you
risk losing the points for the affected tests.
• You must avoid as much as possible transferring data between your Java program and the database
server. Recall that in real deployment scenarios, the Java client resides on a different machine than
the database server so communication is expensive. This means that data manipulation should be
done exclusively using SQL commands executed on the server, rather than by transferring data to the
client and processing there. In other words, the client side should be used for control only, not for
computation. Leave the heavy lifting to the SQL server, this is what it was designed for.
In particular, store the output of your SQL queries directly at the server, without first pulling their
tuples to the Java client. The sole exception is the query checking the emptiness of the Delta table;
this query is allowed to send back to the client a single tuple per iteration.
The result of your computation will therefore not be printed from your Java program, but must be
simply left in the influence table at the server.
What to turn in Turn in a single self-contained Java source file that we can compile without needing
any other file from you, and uses the JDBC driver. Assume that this driver is in the same directory as the
source file.
1. The file should be named ’FTM.java’.
2. Don’t put your class into any java package.
3. The test will run on the TAs database where the customer, account, depositor and transfer tables will
have been created for you.
4. We will not be editing your source file, so the username and password for the postgres account need
to be given as arguments to the program, not hardcoded into it!.
2
5. Before turning in, make sure your file compiles and runs properly on your machine using the following
commands (use your own user name and password for the arguments):
Test commands for Linux/Mac (In Windows, just change : to ;):
javac -cp ./postgresql-42.2.8.jar:. FTM.java
java -cp ./postgresql-42.2.8.jar:. FTM
3
51作业君 51作业君

扫码添加客服微信

添加客服微信: IT_51zuoyejun