Question

Prototyping an Event-Driven IT Application DC Medical Supply, located in Washington, DC is in the industry of medical device sales. Its market share is small compared to the industry leaders, but it hopes to increase its share by becoming more responsive to customer needs. DC Medical Supply would like your help documenting their sales process using an event driven ERP. The following is a description of the sales/collection process of DC Medical Supply. When customers need to order medical devices, they place an order online at DCMedicalSupply.com. When orders are received, a sales representative records the order to update the Sales Order Transaction file and prints two copies of a formal sales order and one copy of a stock release document. The sales representative staples one copy of the sales order to the stock release and sends it to the warehouse department. The other copy of the sales order is filed numerically in the sales department. If part of an order is out of stock, it will be picked when it is available. A warehouse employee receives the stock release and sales order and picks and scans the goods using a handheld scanner to update the Pick Transaction file. The warehouse employee forwards the goods, the stock release and order document to the shipping department. A shipping clerk proceeds to update the Shipment Transaction file and prints the packing list and three copies of the shipping notice. The shipping companies arrive every day at 4:30 PM, and a shipping clerk gives them the packing slip, the original shipping notice and the devices to be shipped. The stock release, a copy of the shipping notice, and the sales order are stapled together and sent to the billing department. The last copy of the shipping notice is filed numerically in Shipping. The Billing Department receives the order, stock release and shipping notice, prepares the invoice, emails it to the customer and saves an electronic copy of it in the department. Once the invoice has been sent, a billing clerk sends the sales order, stock release, and shipping notice to the accounts receivable department. At the end of each month, the accounts receivable department emails customers with balances owing. DC Medical Supply accepts installment payments in some cases. The cash receipts clerk receives the checks and updates the cash receipts transaction file. The controller prepares a deposit slip and deposits the checks at the local bank branch. Customers have 30 days to return devices to DC Medical Supply warehouse. Required: (Turn in two pieces of paper, unstapled, with your name on the top of each page.) 1. Conceptual model: Draw an REA model with cardinalities for the operating events in the sales/collection process described above (do not model the production of the baked items). Make sure you differentiate between the operating and the information events. The REA model can be hand-drawn, but neatness is required. 2. Logical model: List each database table required (master and event tables), complete with key attributes (primary keys), foreign keys, and non-key attributes. Use proper notation as described in class. This part of the requirement needs to be typewritten.

Answer 1

SALES PROCESS

Conceptual model

Logical model

Customer

Trigger

Trigger

Online order is placed at Dcmedicalsupply.com

Sales Representative

Operating event

Master Table

Update sales order transaction file (SOTF), print two copy of sales order and one copy of stock release document

Informational event

Event table

Sends one copy of Sales order and stock release doc to warehouse dept

Informational event

Event table

Another copy of Sales order is kept with Sales Dept for filing serially

Warehouse Dept

Informational event

Event table

Receives copy of Sales order and stock release doc

Operating event

Master Table

Stock is picked if available, else wait till stock is available. Pick transaction file (PTF) is updated once stock is picked

Informational event

Event table

Sends Sales order, stock release doc and stock to Shipping Dept

Shipping Dept

Informational event

Event table

Received Sales order, stock release doc and stock to Shipping Dept

Operating event

Master Table

Update shipment transaction file (STF), print packing list and three copy of shipping notice

Operating event

Event table

Packing list, original shipping notice and stock handed to Shipping companies at 4.30pm everyday

Informational event

Event table

Stock release doc, copy of shipping notice and sales order sent to billing department

Informational event

Event table

Third copy of shipping notice filed with Shipping department serially

Billing Department

Informational event

Event table

Stock release doc, copy of shipping notice and sales order received

Operating event

Event table

Invoice is prepared, emailed to customer and one soft copy saved

Informational event

Event table

Sends the sales order, stock release, and shipping notice to the accounts receivable department

Account Receivable Department

Informational event

Event table

Receives the sales order, stock release, and shipping notice

Operating event

Master Table

And returns from customers within 30 days of shipment are updated to arrive at customer outstanding balances

Informational event

Event table

At month end Emails customer who have outstanding balances

Operating event

Master Table

Payment received in full or in instalment, Cash receipt transaction file (CRTF) is updated based on checks received

Operating event

Event table

Deposit slip is prepared by controller and checks deposited with bank