CMSC661 Project Description

Grocery Order Processing and Delivery

You are to implement an RDBMS-based web application to receive and process orders for groceries over the Internet and deliver the groceries to the customer address.

The project will consist of

•    developing a schema on paper,
•    creating the actual database using the Oracle database system from OIT,
•    populating the database and testing with interesting queries,
•    modifying the database and creating programs to manipulate the database,
•    implementing constraints and triggers,
•    developing a Web front-end for the database application.

Customers must be able to view the items in the store on their browser. Your system must offer categories of items for browsing, such as produce, dairy, cereal, and bread, and items under each. For each item customers must be able to view the size options and price. Pictures of items would be nice. Customers must then be able to select items to buy. Your system must also offer suggestions on other items for customers to consider for buying, based on buying patterns of other customers (for instance, customers who place orders for gourmet foods could be offered mineral water products).

Your system must then offer delivery choices. You must design a system to compute delivery charges based on the distance to the customer address from the store. You may pick real or fictitious addresses. Approximate distances and ranges are acceptable. You must design an appropriate delivery policy. For instance, you may charge special rates for immediate delivery, offer discounts for delivery during non-peak hours, and discounts for long lead times times. You may use distance computing facilities such as Mapquest. However, the system must be fully automated and must not require human intervention. State your assumptions clearly.

Your system must compute the final amount due, both for the products selected for purchase, and for the delivery, and record the final placement of the order (for instance, accept credit card information).

Administrative queries: Your system must process administrative queries, accessible only to the system administrators, including queries for the most and the least popular n items per category. Also required is a query showing the average and standard deviation of the distance of the customers from the store. Your system must also generate a financial summary report of items sold for a given day. Design at least five interesting admin queries including the ones above.

User interface: Your system must include a suitable user interface. Web interface using Java is preferred, although well-designed GUIs that are not Java based are acceptable.

Other Requirements

•  At least two PL/SQL packages are required.
•  You are to include at least five constraints in your design.

Implementation

The final phase of the project requires a working implementation of the system. A large part of the final grade for the project depends on this implementation. You will use the Oracle 9 on the UMBC Oracle server. Accounts will be given out. PL/SQL and/or embedded SQL should be used to write stored procedures and functions and Java and JDBC for application
programming.

Teams: You will work in teams of at least three and no more than four persons. Choose your own team. If you cannot join a team by 10/6/04 contact Dr. Mundur. You must maintain a log of tasks performed by each team member.

Required submissions

PHASE 1:

By 10/11/04 you must submit a complete design document for your system. The design document must include the following:

• A suitable name for your grocery store
• A general system description
• Description of the methodology for computing the delivery charges
• Database schema design showing tables, views, constraints, indexes, entity-relationship diagrams, ?
• Design (pseudo-code) of compiled objects such as triggers and stored procedures
• GUI design
• Log showing the contributions of each team member

PHASE 2:

By 10/13/04 you must complete the coding and testing of your system. Dr. Mundur will schedule a demo for each team. Following are required at the time of the demo:

• A 15-minute demo of your system showcasing its features including order placement and special admin queries
• Listing of objects in your schema (printouts)
• Listing of all code including database compiled objects and the GUI
• Listing of other tools and scripts (SQL script for loading data, for instance)
• Log showing the contributions of each team member

Admin details

You will be assigned a user ID on the Oracle system. Talk to the TA if you have problems accessing the system. If you have teaming issues let Dr. Mundur know as soon as possible; do not wait until the due date.