Lab 2. Database Access
CS320, Fall 2007

Please upload your files to CSNS. The files should include all the source code, documentation (optional), and an HTML file lab2.html, which contains a link to your JSP page on the CS3 server. Note that file uploading will be disabled automatically after 10:00PM, so please turn in your work on time.


Company X is conducting a promotion, which works as follows:

In this exercise, you are going to help Company X to implement the promotion website.

[SQL] (5pt) Create the following two tables, Rewards and Customers, in your database, using proper attribute type for each attribute:

Rewards:
Code Reward ClaimedBy
2576 a TV
john@hotmail.com
2886 null null
2567 an iPod
null
2625 null null
Customers:
Email Name
john@hotmail.com John

Note that the Rewards table contains a list of codes and the rewards associated with them. The Reward field could be null, which means there is no reward. The Customers table contains a list of customers and their email addresses. Note that an email address uniquely identifies a customer, or in other words, you should not have two rows in the Customers table that have the same email address.

For this problem, turn in a file lab2.sql which includes the SQL statements to create and populate the tables.

[JSTL SQL] (5pt) Create a JSP page GetReward.jsp. When GetReward.jsp is requested without any parameter, it displays the the following form:

Code:
Name:
Email:

Once a customer fills out the form and clicks the Get Reward! button, the application checks the user input and the content of the database, updates the database if necessary, and displays one of the following messages:

For this problem, you must use the JSTL SQL tags to access the database.

[JDBC] (+5pt) For extra credit, create another JSP page GetReward2.jsp, which provides the same functionality as GetReward.jsp, except that GetReward2.jsp uses Java beans and JDBC instead of the JSTL SQL tags to access the database.