FINAL
CS320, Fall 2007


Please upload your files to CSNS. The files should include all the source code, documentation, and an HTML file final.html which contains a link to your application on the CS3 server. Note that file uploading will be disabled automatically after 10:00PM, and late submission will not be accepted.

In this exam you are going to develop an web application that helps a retailer to sell products online, using, of course, JSP without scripting elements.

[Database]

The application stores product information in a database. For each product, the ID, description, price, and quantity of the product are stored. The application also keeps track of coupons. The information about each coupon, which includes the coupon code, discount percentage, expiration date, and the product(s) to which the coupon can be applied, is also stored in the database.

For this part of the problem, you need to turn in a final.sql file which include the SQL statements to create the table(s) in the database, and the statements to populate the table(s) so that there are at least three products (which we will refer to as product #1, #2, and #3), and at least the following three coupons:

Note that we will assume that multiple coupons can be used together. For example, if a customer buys product #2 and enters both coupon codes "WEEKEND11" and "HAPPYXMAS", the customer should receive 30% off the price of product #2.

[Products]

The default page of the application is Products.jsp. When this page is requested without any parameters, it displays the products currently stored in the database as shown below:

Description Price In Stock
SANDISK 1GB Sansa M240 MP3 Player $35.97 Yes Add to Cart
APPLE iPod Nano 4GB (silver) $185.00 No
MICROSOFT Zune 30GB mp3/video/photo/fm Tuner $183.00 Yes Add to Cart

When a customer clicks on Add to Cart, the corresponding product is placed in a shopping cart, and the customer will be directed to the Shopping Cart page. Note that a customer cannot buy a product that is out of stock (i.e. the quantity of the product is 0).

[Shopping Cart]

The shopping cart page displays the content of a customer's shopping cart, as well as the coupon code(s) the customer enters, as shown below:

Product Description Quantity Unit Price Discount Total
SANDISK 1GB Sansa M240 MP3 Player 2 $35.97 10% $64.75
MICROSOFT Zune 30GB mp3/video/photo/fm Tuner 1 $183.00
$183.00
Total: $247.75
Coupons:
  • WEEKEND11
Coupon Code:
Check Out

When a customer enters a coupon code, the application checks if the code is valid, and if so, the Discount column is updated to reflect the price discount.

When the customer clicks Check Out, the application updates the database (i.e. the quantity of a product should be reduced by the number of that product sold), clears the shopping cart, and then redirects the customer back to the Products page.

[Grading Criteria]