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/18/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 (Specify what your plan is)
-
Log showing the contributions of each team member
PHASE 2:
By 12/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.
http://www.gl.umbc.edu/oracle_class.shtml (for
information on how to connect to OIT's Oracle server
)