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:
|
||||||||||||||||||||
Coupons:
|
||||||||||||||||||||
| 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]