CS405G Fall 2009 Final Project

Assigned: Sep 15rd

Due:          Dec 7th     

 

Objectives

The aim of this project is to implement a client-side database e-commerce application. You may implement this in any language. You are also responsible for setting up the server-side database. You are required to team up with another student. Let’s assume that you have been hired by Nile Company (Amazon’s competitor) to keep track of their inventory. Nile sales two types of products: games and toys.

 

Requirements

Your goal is to create an online store for the Nile Company. There will be three types of users:

• customers

• staff

• manager

 

Customers In order to purchase from Nile, customers must first register. Once they are registered, they query and purchase books or toys. These items are first placed in a shopping basket, and then ordered. Customers can see the status of orders (i.e., pending or shipped).

 

Staff can check inventory, re-stock the online store with more components, view all customer orders, and ship orders to customers. A staff member has an on-line ID and a pass word that he/she can use to login into the company’s website to perform the previous listed tasks

 

Manager can do all tasks a staff member can do. In addition, manager can (1) view statistics about sale information (in the previous week, month, or year), and (2) decide sales promotions. Manager needs to login into the company’s website to perform the tasks.

 

Your project must include the following functionality:

 

Customer Forms

Register Allows a new customer to register with NILE.

Shopping Allows a registered customer to list books or toys. The purchased items may be stored in a shopping basket.

Purchase Allows a registered customer to view their shopping basket and click "Purchase". This creates an order for the items that can then be viewed (and filled) by the NILE staff. NILE staff cannot see shopping baskets.

Orders Allows a registered customer to view the orders they have places and see the status (either Pending or Shipped).

 

Staff Forms

Login Screen Staff must login in order to perform these functions. A single login for all staff is fine.

View Inventory See a list of all items and their quantity.

Update Inventory Same as above, but with editable text boxes to change the quantity of any component.

Ship Pending Orders View the list of pending orders (components, price, customer info).

The staff member can click a "Ship It" button and, if all the components are available, the status of the order changes from "Pending" to "Shipped" and the quantities in the inventory are decreased. If the components are not available, some error page listing the missing components is generated and the order remains "Pending".

 

Manager Forms

Login Screen may use the staff login form

View Inventory, Update Inventory, Ship Pending Orders: the same as those of staff

Sales Statistics View the list of all items and sales history in the previous (week, month, or year)

Sales Promotion View the list of all items and decide the promotion rate.

 

 

Submission

 

Due Sep 30th

1.      Group members and proposal about how work will be split between members.

2.       (description of problem, ER diagram)

3.      Initial Database Design (table names and uses, fields names, data types, constraints, is it 3NF? if not, why not)

 

 

Due Dec 7th 

Submit the code and the executables and a printed report in the following format for each group:

1.    Finalized database design

a.    Detailed ER diagram.

b.    Detailed database schema design.

c.    Explanation of functional dependencies and the highest degree of form.

2.    Description of programs (data structures, algorithms, and filenames)

a.    Program flow

b.    Data structures

c.    Algorithms if there is any

3.    Program functions:

a.    Sample input and output screens for each function

4.    Testing

a.    Explain what you have tested to make sure your software works correctly.

b.    Describe your project experience.

 

Grading

20 Database Design (ER correctness, 3NF?  Constraints listed? Domains used? Style)

40 Embedded SQL programs (5 styles, 25 correctness, 10 efficiency)

20 Interface (5 styles, 15 correctness)

10 Testing

10 Reports

Bonus:

 

(15 points) Add VIP customers. A VIP customer is the customer that has the privilege to open a mini-store of his/her own. He/she can list items for sale in the mini-store and can cancel them at any time before the items are sold. The sale can be in the format of regular sale and bidding.  If the item is for regular sale, registered customers can purchase it. If the item is for bidding, customers can bid on the item and bidding history should be kept for each item in the database. When the bidding finishes, customer offering the highest price wins the item. The item will be shipped to the winning customer by the original seller.

Write down your assumptions or constraints and draw a complete ER diagram including the VIP customers.

(5 points) Implement the function of regular sale for the VIP customers with SQL programs.

(10 points) Implement the function of bidding for the VIP customers with SQL programs.

 

(5 points) Add a search form for customer, staff, and manager to search for items. You may search by title of a book, the ISBN of a book and the subject of a book. For toys, you may search by name, age group, or theme (like Thomas the train, Bob the builder, etc.)

 

(5 points) Do a 5-10 minute demo of your project to the class.