Abc.Com Case

ABC.com

1. Business Background

2. ABC.com is a specialist on-line white goods. ABC leased a small warehouse where they stock a supply of kitchen white goods (ovens, stoves, range hoods, fridges and dishwashers). ABC has chosen to stock a limited range of manufacturers and sells them at discount prices. ABC has calculated different price discounts for each manufacturer based on its analysis of the difference between the cost price of items to him and the recommended retail price. Goods are stored in the warehouse, and delivered to customers via an external courier service. ABC uses a number of different couriers depending on the number and size of products to be delivered and the availability of couriers. The cost of delivery is dependent on the cost of the purchased item. ABC passes the delivery charges directly on to the customer.

3. Application Design

A) Tables

The database consists of 7 tables: Customer_Details, Delivery_Details, Driver, Order, Order_Item_Details, Stock_Items, and Transport_Company. Hereafter is the detail description of each table:

a. Table Customer_Details consists of the following fields:

* Customer_Id: Each customer has a unique Customer_Id (should be in text, size of the field is 10)

* Name: Name of customer (should be in text, size of the field is 30)

* Phone: (should be in text, size of the field is 30)

* Street Address: (should be in text, size of the field is 50)

* City: (should be in text, size of the field is 50)

* Payment Method: There are three possible payment methods COD, Credit, Account. The filed type should be text and use lookup

b. Table Order consists of the following fields:

* Order_Id: the order_id is unique (should be in text, size of the field is 10)

* Date: date of the purchase (Field type should be "date/time")

* Customer_Id: the Id of the customer who pass the order. Since Customer_Id comes from the Table Customer_Details, we should use Lookup

c. Table Order_Item_Details consists of the following fields:......

d. Table Stock_Items consists of the following fields:.......

e. Table Delivery_Details consists of the following fields:.......

f. Table Transport_Company consists of the following fields:.......

g. Table Driver consists of the following fields:.......

...