Project Phase 2
In this phase you will be in groups of 3 students, one from each domain of Phase 1. Together, each group forms an ecommerce company. Your company will provide a web shopping interface to clients, and a decision support workbench to managers in the company.
Through this phase of the 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.
Project Groups
- Each group will be one student from each domain in phase 1. This is essential because you will be using all three database schemas.
- If you want, you can form your own groups. If you do, please email Nathan with your group members by 5:00PM Monday October 21st. Include the names of all your group members and a name for your group! =)
- If you don't choose your own group by 5:00 PM on Monday, you will be randomly assigned to a group at that time. Have your emails in on time please.
- Your group project spaces will be up by October 22nd.
- After the domains were assigned, there were a few people who dropped the class and a few who joined. Unfortunately, this means that there are 13 people in inventory, 15 in billing, and 12 in shipping. There will be twelve groups with one person from each domain, and four persons left over. The remaining four will be in groups of two and I will provide them my own implementations of the missing databases. (Plus a little extra grading leniency.)
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 need to ensure the consistency of data in different databases.
- In the real world, changing the schema of an existing database has a huge price tag. You are strongly advised to work on current databases you created for Phase 1. If you feel that it's necessary to make modifications to existing schemas, you need to write a petition to Nathan, 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
- When a user inputs data thats going to the database it must be checked for validity first.
- Yes, Management Studio has great graphical query tools. Don't use them, write your own queries.
Tips
By tips I mean advice. If you give me the other type of tips, it will not effect your grade. =)
- Don't wait until the last minute (So cliché but true!)
- In your Web.config file, set <customErrors mode ="Off"> (instead of "Remote Only"), so when an exception is thrown it displays the details.
- Make sure you have functionality working before you start making it pretty.
Part A
Due: Monday November 7th 2005 @ 11:59PM
Working Example
Starter Code
CheckOut Starter
Tasks for Phase 2A are:
- Choose a group (By 5:00PM Oct 24th)
- Fix data inconsistencies between databases.
- Build Web Store Front End
- Implement Login/Shopping Cart/Checkout
- Consume a Simple Web Service (zip code distance)
You will be expected to turn in:
- Your finished e-store published on IISQLSRV.
(I should be able to access it via http://iisqlsrv.cs.washington.edu/phase2/[YOUR GROUP NAME]/default.aspx) - Also include in your web root a zip archive with all your source code (.aspx / .cs)
named source2a.zip.
(I should be able to download it via http://iisqlsrv.cs.washington.edu/phase2/[YOUR GROUP NAME]/source2a.zip)
Details
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. First, however, you need to make your data consistent between your databases.
Data Cleanup
Because each member of your group added arbitrary example data to his or her database, it is inevitable that some things in your databases don't match up. For example there may be a package in the shipping database that shipped from warehouse 12 when the inventory database only has 8 warehouses! You must find a way to eliminate the inconsistencies in your data. I'm not going to put any restrictions on how you accomplish this. You could remove all data and insert new consistent data, fix the existing data, or, for an additional challenge, add tables that map between the different data.
Store Components
-
Browsing
A customer can browse books and music in store. The browsing methods are either (1) search by title (or part of it), (2) find by author or musician (or part of the name), and (3) view all books/view all music. 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, authors/musicians, price, discount if it has one, 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, email, password ect) and at least one set of contact details (address etc.). Associated with each account, there is a shopping cart. (The shopping cart should persist even between sessions!) Afterwards, the customer needs to log in using that account for any transaction. He can also view and update his account information. Note: You can store the password in clear text if you want, but if you can figure out how to encrypt it, more power to you. =)
-
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.
-
Check Out (Lots of work!)
A customer can check out (buy) all the items in his/her shopping cart. There are several sub-tasks for each check out:
- If the order is to be shipped or billed to an address other than the one that the user input at registration time, then the customer can choose from addresses he/she has on file, or can input a new address. You can assume that all the products in the cart are shipped to one address, and billed to one address (The shipping and billing address may or may not be the same). Also, he fills in special shipping instructions, if there's any.
-
It may or may not be possible to ship all products from one warehouse. If it is, then ship all items from one warehouse. If not, then split the order up into multiple packages from different warehouses.
When more than one warehouse in the database could potentially ship a package, the warehouse that is closest to the shipping address is given priority. How, you may ask, are you supposed to determine distance between two addresses? You should use a webservice to obtain the distance between the two zip codes. I have created such a web service for you, you can find it here .
- The customer chooses shipping options from the vendors and methods in the shipping database. If the order is split into multiple packages, then the customer needs to select a shipping option for each package. When the customer chooses a shipping option, he/she can see the details on the different options (vendor, price, delivery time, etc).
- The price for purchased items, discounts, shipping and tax are provided and the customer verifies them. We assume a universal 8% local tax rate (For simplicities sake).
- The customer chooses a payment method. If she chooses to pay by credit card, he/she needs to either select a credit card they already have on file, or enter new information. Since this is not a real store, and these are fake credit card numbers, we will omit the authentication process.
- After check out, 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. To insure that your tables are consistent, use an atomic transaction. See this quickstart tutorial for information on using atomic transactions in C#.
-
Delivery Status Tracing
After placing an order, the customer can visit a page which displacs a list of all his/her invoices. For each invoice there is a details page which shows each package related to the invoice and the shipping details/tracking information for that package. Invoices remain in the system so the customer can check his/her shopping history.
Part B
Due: Tuesday November 15th 2005 @ 11:59PM
Tasks for Phase 2B are:
- Import Amazon book and music data from XML
- Modify storefront to reflect XML attributes or elements NOT in your inventory schema.
- Add store management pages
You will be expected to turn in:
- Your modified e-store published on IISQLSRV.
(I should be able to access it via http://iisqlsrv.cs.washington.edu/phase2/[YOUR GROUP NAME]/default.aspx) - Also include in your web root a zip archive with all your source code (.aspx / .cs / .sql)
named source2b.zip.
(I should be able to download it via http://iisqlsrv.cs.washington.edu/phase2/[YOUR GROUP NAME]/source2b.zip)
Details
Data Import
In addition, you are expected to import some data that we will provide. This data is data on real books and music. There will be few hundred books and several thousand pieces of music.
The data was extracted from an amazon.com web service. It is in pages, ten products per page, in a database on iisqlsrv. Each record in the database is a page of ten products. The database is nbales_xmldata and each group has permission on a view which exposes a subset of the data. To access the data, you can select from this view. (Look for the view in Management Studio.)
Your task is to use XPath, XQuery and the built in function for XML in SQL Server 2005 to masauge the data from your view into your relational schema. <clairfied>There will be some attributes in the XML that do not have corrisponding columns in your relational schema. Import all elements/attributes that make since. (See the next paragraph for details on how wer are dealing with mainting the other elements.)</clairfied> Do this using only the database tools to parse the XML. Do not use C# to parse any of the data. Make sure you save your SQL and XQuery code because you will need to turn this in! There is a good overview of the XML specific features HERE.
You will also add a single column to your product table(s). This new column will be XML type, and it will hold the raw XML fragment from the amazon web service data. As part of this phase you need to modify your product description page to display at least two of the attributes found in the XML fragment that are not represented in your relational schema. To do this, you are going to have to use the XML in SQL tools yet again.
I recomend starting out by figgureing out what each of "CROSS APPLY" and .nodes() do.
Management Information Querys
You will also build some pages that help the manager get certian information for further business analysis and decision making. I am not going to make any starter code or working example for these pages, you are on your own this time. (It's only three pages) You must include the following queries:
- For each book or CD, give the sale amount made so far. (Both in terms of number of items sold and dollars made.)
- Given a city, the number of times each shipping method is used to ship to that city and give 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.