CS680 hw4  Getting Started with Databases

Due Monday, Oct. 20,  files by midnight in UNIX cs680/hw4 directory.

1. Review SQL.  Do the b parts of the queries at sqlzoo.net, up to and including query 5, "more Join operations" . Use copy and paste into your memo.txt of your (final-version) queries, in order, using their numbers for identification. Check your answers using the Answers section on the linked web page.

2.  Start learning (or review) JDBC. Read the Sun tutorial on basic JDBC, at least up to the "Milestone".
a. What Java classes (include package name) support JDBC and are used in the Sun tutorial?  For each, briefly explain what it provides, in memo.txt.
b.  Find the JDBC API documentation among the usual JSE Javadocs at http://java.sun.com/javase/6/docs/api/.  In particular, read Connection.close() and explain what it means.  The "resources" here involve the TCP/IP connection to the database, an OS resource not directly managed the the Java vitual machine (JVM.)
c. Explain how to use the "getXXX" methods.  

3 a. After you have verified your Oracle account is working by logging in to system dbs2.cs.umb.edu and using sqlplus, try accessing it with the supplied program JdbcCheckup. Once that works, go back with sqlplus and record what tables have been created by the JDBC program by using the catalog query "select * from user_tables" or just "select table_name from user_tables" to get a less verbose report. Note that the jars supplied with JdbcCheckup work equally well on Windows as on UNIX.
b. Download  pizza1 (zip)  and get it working. Read the build.xml in the base directory and also in the database directory.You need to define environment variables ORACLE_USER and ORACLE_PW and then load the database into your account in Oracle by using the build.xml in the database directory. In memo.txt, report on success or any problems you had.

4 a. Similarly, try out the memory database HSQLDB, first with JdbcCheckup. Record your success or problems.  Note that you need to start HSQLDB before using it, see pizza1/database/build.xml for target "start-hsqldb".
b. Run pizza1 on HSQLDB and report success or problems.
c. Install the SQL Explorer plugin for eclipse. Make sure eclipse is not running. Download sqlexplorer_plugin-3.5.0.RC5.zip from http://sourceforge.net and unzip it in your eclipse directory--the one with plugins as a subdirectory. Then run eclipse and you should see a new SQL Explorer perspective. Note that SQL Explorer has a topic in eclipse Help: Navigate to Help>Help Contents>SQLExplorer. Add a new Connection to Oracle as explained in detail here.  See your welcome table there. Record your success or problems.

5. As a start on chat functionality for Pong, set up a Java project called "chat" and write a JDBC program Send.java that takes a screenname and a line of input (the message) on the command line, and inserts a new row in a table game_messages with screenname, timestamp, and message. Write Receive.java to read all the messages and display them on standard output, in time order. Both programs take arguents for database type, username, and password before their other arguments, like SystemTest in pizza1. Use the build.xml and database/build.xml from pizza1 (zip) modified so that "ant load-oradb" and "ant drop-oradb" (and load/drop hsqldb) work to load and drop the table, and "ant oraSysTest" (ant hsqlSysTest) sends two messages in for user "Bob" and one for "Sue", in between those for Bob, using Send, and then uses Receive to displays all messages. In all cases of Oracle use, the username and password are set up in environment variables ORACLE_USER and ORACLE_PW, and are accessed by the build.xml as in pizza1.

Deliverables

We will collect your work electronically from a hw4 subdirectory of the cs680 directory.

Your memo should be pure ascii text, with no markup (no html, no MS Word). When printed on the Department printers no text should be lost at the ends of lines. If you want to submit some hard copy of diagrams, you may bring them to class the day the assignment is due.

We will look in your cs680/hw4 directory. It should contain