Project Milestone 1

Hongwei Liu, Lei Yang, Lisa Whitney-Kooker, Rahul Parelkar

INFO 605, Fall 2015

Title: Design and Implementation of an Online Retail Shopping Database System

  1. The Problem Statement

“Things-R-US” is an up-and-coming store which plans to break into the online shopping experience. This move will require a database that will store information regarding customers, purchases, payment details and suppliers.

a.Overall Goals of the System

i. Store Customer profiles (eg. Account information, Name, email)

ii. Store Supplier profiles (eg. Name, Address, Sales Contact)

iii. Store Product details (eg. Name, Description, Cost)

iv. Track Shipment details (eg. Carrier, Delivery Status)

vi. Billing information (eg. Info for various payment methods)

b.Context and Importance of the System

The “Things-R-US” online shopping outlet plans to sell a variety of items to customers all over the country. Each customer will be required to create an account prior to making a purchase. Information about the customer and their profile will be stored as well as payment account information.

Things-R-US purchases merchandise from a number of different suppliers worldwide. Information needs to be stored regarding each supplier.

Each product is given a unique product ID code as it is placed in stock. Prior to shipment each product is inspected to ensure it is good condition. A separate inventory system is used to ensure products for sale are appropriately kept in stock.

While shopping online, customers have the ability to add items to their cart. This information will remain in the “cart” until the customer decides to purchase, or manually removes the information from the cart.

Shipping is included in all purchases greater than $100. “Things-R-Us” does not need to charge customers sales tax on these items. For orders less than $100, customers are asked to pay a shipping fee.

All shipments are tracked with regular updates to the customer regarding the arrival of their purchase.

Our database design helps to automate the entire process of online shopping experience and makes it more convenient for the customer and the retailer both.

The customer benefits from the system as follows -

  1. The customer does not have to repetitively add payment information.
  2. The customer gets to choose from a wide variety of products available, compare the products by viewing the images, reviews and so on.
  3. The customer can save the products for later in cart and buy it later so he/she does not have to go through the pain of searching for the same products again.
  4. The customer gets to know when his product will be delivered.

The retailer benefits the system as follows -

  1. Allowing the company to offer products to a larger number of customers than a traditional brick and mortar store.
  2. It increases the efficiency of the system by decreasing time and resources required to manually take orders and process billing information

c.Scope of the Project
IN-Scope:

The online tracking database will include the following:

●Information about the Customers (Name, CustomerID, Address, Phone, Email)

●Information about the Suppliers (Name, SupplierID, Address, Email, Phone, Website, Rating)

●Information about the Products (Name, Description, ID, SupplierID, Price, Weight, Images, Quantity, Rating)

●Information about Shipments (Tracking ID, PurchaseID, CustomerID, Delivery Address, Delivery Status, Carrier)

●Information about purchases (CustomerID, ProductID, PurchaseID, Quantity, Shipment Details, Purchase Date, Estimated Delivery, TotalPurchaseCost)

●Information about the billing and payment (paymentMethod, billingAddress)[1]

OUT-Scope:

●The database will not include information regarding merchandise returns.

●The database will not include a section for recommendations for products that are purchased.

●The database will not include promotional offers or sale.

  1. Requirements
  2. Data Requirements

In the “Things-R-US” online shopping outlet, the system will store information regarding the company’s customer database, products, suppliers, payments and shipments.

Customer first need to register their profile on ORS database. Registration involves creating a user name and a password. The customer profile will include following detail, customID, customName(first name, last name), customer phone, customer email.

The customer saves some products in the cart, the product contain productID, productName, description, unit price, weight, customer rating. The cart will contain cartID, created date, quantity of productID. Once the customer finalize the order, it will go to the shipment entity. Before the shipment, the billing will happen, the billing is separated into three parts, credit card, paypal and checking account. Once the payment is successfully processed, we go to shipment entity.

Its database will mainly track three action behavior information:

  1. Update Behavior - Relevant information include SupplierID, ProductID, Quantity.
  2. Purchase Behavior - Customers purchase product on the website. Relevant information include ProductID, Quantity, SupplierID, CustomerID, PurchaseDate, PurchaseID, paymentMethod, billingAddress.
  3. Shipment Behavior - Merchandise ship product according to customer’s former purchase decision. Relevant information include SupplierID, TrackingID, PurchaseID, CustomerID, Quantity.

The database will also verify and store suppliers’ name, address, email, phone number, official website, customer rating and connect all these information to supplier ID.

  1. Business Rules and Logic
  2. The same product can be supplied by more than one supplier.
  3. Delivery logistics will be updated from the carrier service at a daily basis and updated on the ORS website.
  4. If delivery date exceeds expected delivery, the customer will be notified.
  5. The product sale report will be shared with the supply vendors and a threshold for every product will be decided and if the trigger/alert for the product is set then a product invoice will be sent to the supply vendor to replenish the product in the ORS warehouse.
  6. The database should allow for simple queries and reports.
  7. When the order is placed, the customer will be sent a receipt in email with the tracking details and expected time of delivery. If total cost of the products purchased is greater than $100 then shipping would be free.
  8. Before the packaging process, the product will be checked and inspected for Quality Control and stamped ‘Tested Ok’ before dispatching.
  1. Sample Output
  2. Report of transaction activity for specific product sample.
  3. Report of online shopping activity for specific customer.
  4. Report of tracking information of specific product shipment.
  5. Report of transaction of specific purchase.

d. Other Assumptions

  1. All products to be stored in our own warehouse.
  2. All products in our warehouse have already been examined for quality and quantity.
  3. All orders will be sent in individual shipments.
  4. Cart history is not retained.
  1. ERD (post your ERD and explanation of the ERD here)

Explanation of ERD -

  1. Every user account can belongs to one and only one customer profile and one customer can have one user account.

2. One user can place many orders, each order can belong to only one customer.

3. Each customer can save products into a cart. Customers have only one cart. Carts are empty once a customer has purchased or removed all products from their cart. Cart history is not retained.

4. Each customer can have multiple orders but each order can be linked to only one customer.

5. Each customer can have multiple billing accounts but each order will be billed to only one payment account.

6. For orders less than $100, customers will be asked to add an additional shipping fee to their order

7. Each product can come from one or more suppliers and each supplier can supply multiple products. In some cases, the supplier may not supply the product due to reasons like the product being out of stock with the supplier.

8. Each order can have one and only one shipment, and each shipment can only belongs to one and only one order.

9. Each order may contain many products, and many products can be processed within one order.

10. After ordering a product, customers can provide a rating for that product. The average rating by all customers will be calculated for each product.

Known References (so far)

Elmasri, Ramez and Sham Navathe. Fundamentals of Database Systems. 6th ed., Boston: Addison-Wesley, 2010.

1 of

[1]verify