Project #3: Due Apr. 23 Apr. 26, BEFORE Class Begins

PDA (Personal Database Application), Courtesy of J. Widom

In Project #3, each team needs to create a web-based interface to their DB2 database. As we discussed at the beginning of the course, a huge number of Web sites are based largely on a relational database system. The HTML pages a user browses are generated from the database, and user actions and inputs result in behind-the-scenes SQL queries and updates. Although the database-backed site you will create is not likely to be as snazzy as a typical Web shopping or auction site, the basic idea is the same. Here are some general ground rules:

• Your Web interface should offer the user several different types of interactions that involve the database. You may base your Web interface on the same SQL queries that you implemented in Project #2, or you may use new or additional SQL queries.

• Your interface should support operations that involve data retrieval as well as operations that involve data modification (i.e., INSERT/DELETE/UPDATE). That is, you have to implement, for instance:

  • INPUT: Take some inputs from users (e.g., Book title and year)
  • OUTPUT: Display a table of answers matching users' selection

• As in Project #2, please support (through your Web interface) a few number of "interesting" queries, i.e., operations that require some of the more complex SQL constructs such as sub-queries, aggregates, set operators, etc.

• The less your Web site looks like it is interacting with an DB2 database, the better it becomes. At the very least, the user should be completely shielded from anything resembling SQL. For instance, if you go to Amazon.com site, you do not type a single keyword of SQL command directly. Amazon.com provides various types of buttons and input box to get users' inputs and compose valid SQL queries behind the scene. Your web interface should provide a similar experience to users.

• Most interactions should involve some sort of input values in addition to the user pressing a Submit button. Whenever possible, input values should be specified using menus, radio buttons, checkboxes, scrollers, etc. Text input boxes may also be appropriate.

• Sophisticated error handling is not necessary, however your Web site definitely should not "lock up" regardless of how the user chooses to interact with it. For instance, even if users enters totally invalid input values (thus resulting in SQL error internally), never show the SQL error message returned from DB2 directly like:

      Warning: SQL error: [IBM][CLI Driver][DB2/6000] SQL1086C An
      operating system error "" occurred. , SQL state S1000 in SQLExecDirect in 
      /.../dce.psu.edu/fs/users/d/u/dul13/www/phpdul13/db.php on line 21
      Empty answers were returned from DB2
  

  Instead, your system must present a nice and non-cryptic error message to users like

      You typed a wrong value in the field foo, you idiot :-)
  

• Although we will try hard not to be affected by the factors such as nice images or well-designed frames other than DB-related features, since we are aiming at practical applications, a little of touch would not hurt. However, your focus must be on the aforementioned DB-related parts.

• Be C.R.E.A.T.I.V.E -- Project #3 is open-ended. Add whatever you can think of to make your applications more interesting and appealing.

• Here are a few web-based database interfaces that I found on Web:

  PSU	Amazon.com	Wine.com

• Finally, here is an example from the last year (that got A): Web-based University Management System by Jessica Scott, Jordan Steinberg (Lead), Ramon Thomas, Steve Dalonzo. (Note that they got A not because of their nice interfaces, but because they successfully demonstrated many complex SQL features including advanced ones like Integrity and Triggers).

PHP

Your Web front-end can interact with the DB2 database using many ways -- CGI, ColdFusion, PHP, ASP, JSP, JavaScript, Applet/Servlet, or ODBC/JDBC, just to name a few. Since this is not a Web course, we cannot possibly cover all these. Ideally, we should be able to cover at least ODBC/JDBC since they are the ones most related to Database course. However, most students in IST210 do not have adequate programming background, nor PSU ITS supports it.

Therefore, what we finally chose was to use PHP for this task. ITS officially supports DB2 along with PHP. They have already created a starting point for you to work as follows:

• In UNIX account, under "www" directory, each student will have a directory called "phpxxxx" where "xxxx" is students' PSU ID. For instance, mine is "phpdul13". If you do not see it under your directory, notify to TA immediately.

• A test PHP module was created there, called "test.php". If you don't see one, open your favorite editor (eg, emacs, vi, pico), save the following one line:

    <?phpinfo()?>
  

  And save it as 'test.php' and exit the editor.

• In a web browser, try the following URL:

      http://phpdb.aset.psu.edu/phpxxxx/test.php
  

  It may pop up a window with warning message about Security Certificate -- click "Yes" and proceed. Then, if all goes well, you should be able to see a web page that has lots of PHP information. Congratulations -- your PHP configuration works fine.

• Examples can be downloaded at Class Resources.

• PhP Resources:
  • PHP
  • PHPHelp.com
  • Connecting PHP Applications to IBM DB2 Universal Database
  • PHP ODBC Reference
  • HTML Form Tutorial

Submission

1. By due date, a final project web site that has Web-based interface to DB2 must be ready and accessible to anyone on Web.

2. A project 3 report (hardcopy) and a presentation (hardcopy) must be turned in to the Instructor at 313A.

3. In addition, the same report must be linked to the web site; it includes

   • Contribution distribution for Project #3

   • Overall description of your web-interface and application

   • Details of how each query feature is implemented behind (e.g., "Search by author name" feature was implemented by the following PHP code and internally it generates the following SQL queries and blah blah...)