Spring 2002
Stony Brook
Principles of Database Systems
Annie Liu
Project 4
Handout P4
Apr. 16, 2002
Due Apr. 30

Building Application Programs, Testing, and Improvements.

In this project assignment, we will build our application program, and also test and improve the application. We have given sample solutions to all project assignments before; you may update your own solutions based on ours if you wish.

What to do? Build an application program in Java that contains a GUI and uses JDBC to connect to the Sybase SQL server; test and improve your application in terms of both correctness and efficiency. More specifically, your task includes the following items:

  1. Build a GUI that allows a user to
  2. Use JDBC to connect to the SQL server to perform the above queries and updates to the database based on the SQL programs from Projects 2 and 3.
  3. Write Java code that can generate complete data for n users, n commuting requests, and n other trip requests.
  4. Test your application in terms of both correctness and efficiency and report clearly what you did and what results you obtained. For correctness, run your application on some specific data sets (which could also be generated, but this is not required) that you can check correctness of different aspects carefully. For efficiency, run your application on some data sets of increasing size n, where n could be 500, 1000, 1500, 2000, for example, or 2000, 4000, 8000, 10000, for another example, depending on where you hit a resource (time or space) limitation.

You may choose to use either Java application or applet, but application is faster and more flexible.

You could use JBuilder's JPanel or JTabbedPane in Swing Containers to layout the background of your screen and then put different controls such as JButton and JTextField on the background.

General description of JDBC is as given in the textbook and lectures. Specific instructions in using it in the Translab can be found following links to Translab on the course homepage. You might also find the tutorial slides by Bin Tang (a TA of 305 for Fall 2001) helpful.

If you choose to use our solutions to P2 and P3 for data definitions and queries in SQL, you must say explicitly and exactly how you used them, in comments of your code. Indeed, you should include comments for anything else your code uses or depends on.

Now that you have the full power of Java, plus that you have seen uses of triggers and stored procedures, you should complete all missing aspects of the application. In particular, if there is any integrity constraint that was not handled before, then handle them now. The data you generate and put into your database should satisfy all integrity constraints.

Note that you will get points off if your application produces wrong answers. However, you may be given full points even if your database has bad performance, so long as you identified the source of problems; in fact, you may be given extra points if the source of problems is unexpectedly interesting.

What to deliver? Hand in a printout of (1) sample screen dumps of your GUI, (2) complete code for your application (including code you used for testing) (with comments for anything not obvious), and (3) report about your testing. Preferably, they are printed 2-per-page and double-sided.

You will also be asked to give a demo of your application during the last week of class, so we can see your GUI in action and check the correctness and efficiency of your system. For checking correctness, you will be asked to prepare your database with the data from Project 3, and perform some queries and updates including those in Project 3. For checking efficiency, you will be asked to load the database with a large data set generated by your test program and perform some queries and updates; we would prefer to see those at the performance boundary.

Team work: Continue working with the partner you had for Project 3; if you did not try to look for a partner for Project 3, you should do it now, for this final project.

Each team should hand in only one copy of the assignment solution; include identification information for both team members.

Bonus problems:

(15% extra credit, for understanding and improving design) Specify all functional dependencies in your database schema. For each table, state what normal form it is in and justify your answer. Do this also for an improved design (for example our design :-)) and then compare the tradeoffs.

(15+% extra credit, for comparing alternative implementations) You may implement alternative ways of enforcing constraints, e.g., using Java code and using SQL triggers, then compare them (in terms of code size and clarity, efficiency, ease of change, etc), and report what you did and what you found.

(15+% extra credit, for understanding and improving efficiency) If after you identified sources of performance problems in your application, you addressed them by rewriting your queries, redesigning your schemas, switching to stored procedures, etc., compare the performance and other trade-offs, if any, and report what you did and what you found.

(somenumber+% extra credit) If you find anything else interesting to explore, you may come to talk to me, and we will evaluate how much it is worth. The +'s mean that you can get more than specified points if you obtained unexpectedly interesting results.