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
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
- 5-7 SELECT
- 1 INSERT
- 1 DELETE
- 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:
Part 3: Presentation Slide
Each team needs to prepare for a power-point slide presentation
for about 15 minutes (10-20 pages). The presentation should
All members need to participate in
- Brief description about projects #1-3 (e.g., ER, schema, normalization).
- On-line demonstration.
Part 4: Turn-In
- By due date, each team needs to turn in both the hard-copies
of project #3 report and the presentation slide.
- The project #3 report should include:
- Overall description of your web-interface and application.
- Data population SQL code.
- 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...).
- Show all interesting features that you want to show off.
- 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.
Last modified: Mon Nov 15 00:33:35 EST 2004