Project Phase II
Warning: Start early. The work
load of this phase is huge.
Goal:
In this phase you form groups of 3 students, one from each domain of
Phase I. Together, each group forms an E-Business
company. Your company will provide a web shopping interface to
clients, and a decision support workbench to managers in the company.
Through this project, you will learn:
- How to implement applications that use several databases. The
operations on the databases may or may not be simultaneous
- How to handle queries that are across different databases
- How to coordinate with project group members and implement new
functions based on previous work
- Most importantly, you will face the same data management
problems often seen in the Real World.
About Grouping:
- You will form your own groups of 3, with one student from each
domain of phase1. Email us
your group name and members by Jan 28.
- If you still don't have a group by Jan 28, we will assign some
groups randomly.
- Your group project spaces will be up by Jan 29... if you have a
group.
- However since we got 20 inventories 19 billings and 17 shippings,
we will allow 1 group of inventory-inventory-billing and 1 group of
inventory-billing (2 students). These groups will have to implement the
missing database domain (which is shipping. and you need only the
shipping database, not the web UI in phase 1 part 2......). And they
will receive a small amount (<= 10%) of extra credits.
- A one-student-group is not allowed.
Milestones:
- By Jan 28, decide your group and email us your group
name.
- The phase is due Feb 16. You will email us the URLs of your
web-shopping webpage and your query webpage.
Details:
Web-Shopping
Most of you have web-shopping experiences, so it's easy to
understand what needs to be done. In this part, you'll
generate your own web-shopping site.
The details of web shopping includes:
- Browsing: A customer can browse books and music CDs in
store. The browsing methods are either (1) find by title, (2) find by
author or musician, (3) by keyword for books, and (4) view all
products. Some important
information that MUST be displayed (either in the browsing page or by
implementing another "view detailed information" page) include product ID, product name, author,
price, have discount or not and total quantity in stock (sum over all
warehouses). Your work in
Phase 1
should have prepared you well for this.
- Log-in: Customers can register for customer accounts.
During registration, the customer provides their basic information,
(such as name, SSN etc. depends on how you key your customers, and
login ID and pw too) and contact
details
(address etc.). Associated with each account, there is a shopping
cart. Afterwards, the customer needs to log in using that account for
any transaction. He can also view and update his account information.
- Shopping Cart Arrangement: A customer can add certain
products to the shopping cart, change the quantity of a chosen book or
music CD in the cart, and delete items in the shopping cart. A customer
should not be able to buy products that are out of stock. Once the
customer has chosen to buy (check out), the entire shopping cart (i.e.
everything in it) is purchased. You
don't have to maintain more than one active shopping cart for customer.
You're free to store the shopping cart anywhere you want.
- Transaction: A customer can make a transaction of all the
items in his/her shopping cart. There are several sub-tasks for each
transaction:
- If the goods are delivered to another customer, the
customer
needs to provide the delivery address and contact information about
that customer. You can assume that all the products in the cart are
shipped to the same location. Also, he fills in special instructions
(e.g. caution! this book explodes on impact!! handle with care!!!), if
there's any.
- A warehouse from which the prodcuts are shipped is assigned
automatically. Products are shipped from a warehouse which contains
enough quantities of bought items. If no single warehouse can provide
that quantity of items, it can be shipped from several warehouses (i.e.
customer receieves multiple packages). When
considering different warehouses, we give higher priority to the
warehouses that are in the same city as the delivery address (say if
you're shipping something to Seattle and you have a warehouse in
seattle, you ship from that warehouse. If you have no warehouse in
seattle or your warehouse in seattle does not supply enough goods, then
you can
pick a random warehouse).
- The customer chooses shipping options (overnight, next day,
standard, teleport, blah...).
When making his choices, he can browse information (vendor, price,
expected delivery time) on the cheapest
available shipping method (your program computes it) for each different
shipping options, as supported by Domain III,
Phase I.
- The price for purchased items, discounts, shipping and tax
are provided and the customer verifies them. We assume the same local
tax rate for different cities.
- The customer chooses a payment method. If she chooses to pay
by credit card, she needs to provide the credit card information. If
the customer has used the system before and entered credit card
information before, the system should display it and allow the customer
to choose that credit card. Here
we omit the authentication of credit cards.
- After the transaction, the shopping cart is reset to empty
- Make sure the corresponding tables in your database are
updated correctly: i.e. make sure you insert things into invoice and
packages, deduct the quantity purchased in the inventory... etc. The
management queries are designed to check these...
- You're not required to make your transaction atomic though.
And of coz you don't need to have a roll back when there's a crash....
- Delivery Status Tracing: After making a transaction, the
customer can trace the delivery status of each item in his invoice by
providing the invoice number. Besides, he can check his shopping
history.
- As for the "cheapest available shipping method" mentioned, you
can ignore the shipping methods' restrictions (this is saved for extra
credit).
- And you can ignore the association between warehouses and
shipping vendors, that's saved for extra credit as well.
Extra Credits
- Take the shipping restriction "maximum weight" and "maximum
items" into account when packing your packages. Take the number of
resulting packages into account when displaying the "cheapest shipping
methods"
- Do the above, and pack your packages optimally....
- Take the warehouse-shipping vendor relation, and the "shipping
detination" restriction into account when calculating available
shipping methods. Also implement some way (that's better than "let the
customer pick the shipping option for each package no matter what") to
handle the cases when no single shipping method can ship all the
packages in an invoice.
Management Information Query
The second part of Phase II is to built a tool that helps the
managers in the company get certain information
for further business analysis and decision making.
You should provide a simple front-end to answer the following 3
queries.
Even though you are asked to provide answers only to the three queries
below, you should try to figure out a general solution for the problem
of querying across multiple databases. So when we give
you some other queries at the end of Phase II (if that happens), you
should be able to
implement
the interfaces and answer the queries quickly.
- For each book or CD, give the sale amount (both in terms of
#items and $$) made so far. (That means return a table (or two if you
wanna separate book and cd) that lists everything your have in your
INVENTORY and their sale amount)
- Given a city, list all shipping methods that can ship stuff to
that
city, also display the number of times the shipping methods are used
and information such as vendor, pricing option, price per unit,
restrictions for each shipping method returned.
- Display the following information for all of your warehouses:
Address, shipping vendors associated with this warehouse, products in
stock (list each product separately), and number
of products shipped from that warehouse
Rules and Restrictions
You should implement web-shopping based on the 3 separate databases
for the
3 domains.
- You may need to generate several new tables in appropriate
databases.
- You may NOT create new
databases, but you can (actually must) create a database for the
missing domain if you're in a group of 2.
- You need to ensure the consistency of data in different databases.
- Considering the huge cost of schema structure change and table
duplication, you are strongly advised to work on current databases you
created for Phase I. If you feel that it's necessary to make
modifications to existing schemas, you need to write a petition to us, as you need to do in
real life. The petition needs to specify the change you want to make to
the schema, the drawbacks of the old design, the benefits of the new
version, the expected cost of the change, and the time needed for
making the change.
- You are required to check for errors in the following cases:
- When a user tries to create a login with a user name that
already exists in your database
Advices
- Start early
- In your Web.config file, set <customErrors mode ="Off">
(instead of "Remote Only"), so when an exception is thrown it displays
the details...
- No need to be fancy about your web design unless you have plenty
of time.
What and When Do I hand in?
- On or before Feb 16, throw allll your files into the
"turnin" folder under your project space. Make sure that your project can be viewed
through
http://iisqlsrv.cs.washington.edu/group/<your_group_folder>/turnin/<startingPage.aspx>.
- Also, it would be better if you name your starting page as
"index.aspx"...
- This time I will create and configrue the turnin subfolders for
you so don't worry about it not running in turnin....