Question

The Case

Express Delivery (ED) is a Morgantown based home goods company. Customers can call in orders and ED mails the items to the customer. They also email the customers a bill, as seen below. Customers can mail in a check, once they receive the order. ED needs to track whether the order has been paid or not, but the payment information is handled by a third-party vendor.

ED needs to create a database to track their customers. Each customer receives a permanent ID when they call in. While on the phone, they also gather information regarding the customer's name, address, and email. A customer can have multiple emails, but we need only one email to be on the order. Customers can be of two types – individuals and corporate. Corporate customers have a tax id number that has to be stored by the company.

ED’s products are stored in a back room. The employee sends the products to the address on file for each specific order. ED wants to track which employee packed the products for each order (one employee is assigned to each order). Employee’s first and last name, address, phone numbers, and how long the employee has worked for the company needs to be tracked.

Draw an object model that shows the necessary data components for the database in ST's new system. If you make assumptions (which you should keep to an absolute minimum!), make sure to list them in detail. Include all calculated fields needed to create the order shown above!

Answer 1

Step 1: Customer [Either Individual or Corporate make a call to Express Delivery for ordering home goods]

Step 2: Express Delivery [Soon after receiving a call Permanent ID or Tax ID is created based on customer profile (Individual or Corporate) further, details of the customer such as Name, Address, Email and Product ID s noted and confirmed]

Step 3: Employee Back Room [The product ID & details of the customer is sent to the back room, where one of the employees would process and mail to the product to the required customer. Herein each employee would record his own details, customer details and corresponding Product ID mailed and send a copy of it to the third party vendor for payment collection and another copy to Express delivery company to maintain the everyday database.

Step 4: Third Party Vendor [On receipt of employee details, customer details and Product ID, the third party vendor would initiate payment collection]