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
