Project #3: Due Nov. 29 Dec. 1, 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 in 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.

Part 1: Data Population

• If TA pointed out any errors in your schema, fix them by normalizing the schema.
• For your BCNF-normalized schema, populate data accordingly. You may manually make up some plausible data according to your schema, or copy and paste from web sites, or even write a program that generates synthetic data.
• The data that you generate can be anything, but they should satisfy all constraints that you have found in the project #2 (e.g., Functional Dependencies, Keys, etc)
• Save your data population SQL code in a file like company_data.sql that you are given for the SQL Lab during class and turn it in. The file will have essentially lots of "INSERT INTO ..." statements that populate new data into your schema.

Part 2: SQL and Web Interface Implementation

• 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.
• For PHP parts, you can start from the examples from the lab -- form.php, db.php, and db2.php. Furthremore, if you want, you are free to use other tools that can generate PHP codes for you.
• 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
  

  or

     INPUT: Take some inputs from users (e.g., employee's old and new addresses)
     OUTPUT: Display a table of answers after the updates (from old to new address)
  

• 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 advanced SQL features such as sub-queries, aggregates, set operators, group-by, etc. To give you a ball park figure, let's say you need to have at least
  1. 5-7 SELECT
  2. 1 INSERT
  3. 1 DELETE
  4. 1 UPDATE
• 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 be able to prevent such an error, or present a nice and non-cryptic error message to users like

      You typed a wrong value in the field foo, you idiot.
  

• 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

Part 3: Presentation Slide

1. Brief description about projects #1-3 (e.g., ER, schema, normalization).
2. On-line demonstration.

Part 4: Turn-In

1. By due date, each team needs to turn in both the hard-copies of project #3 report and the presentation slide.
2. The project #3 report should include:
   1. Overall description of your web-interface and application.
   2. Data population SQL code.
   3. 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...).
   4. Show all interesting features that you want to show off.
3. By due date, a final project web site that has Web-based interface to DB2 must be ready and accessible to anyone on Web. Similarly, the presentation slide must be linked to the project web site. We will be running everyone's Web interface in order to grade this part of the PDA. You will lose substantial points if the web interface that you created does not run properly, or if the URL linked to your project web page is not valid. Do NOT add password to your project so that any one can try it out.