Customer to the Lotus Tea Importer Company places an order with a sales representative by phone or fax. The sales department employee then transcribes the customer order into a standard sales order format and produces the following documents: three copies of sales orders, a stock release document, a shipping notice, and a packing slip. The accounting department receives a copy of the sales order, the warehouse receives the stock release and a copy of the sales order, and the shipping department receives a shipping notice and packing slip. The sales clerk files a copy of the sales order in the department.

Upon receipt of the sales order, the accounting department clerk prepares a customer invoice by adding prices to the sales order, which she obtains from the official price list. She then sends the invoice to the customer. Using data from the sales order the clerk then records the sale in the sales journal and in the AR subsidiary ledger. At the end of the day the clerk prepares a sales journal voucher, which she sends to the general ledger department for posting to the sales and AR control accounts.

The warehouse receives a copy of the sales order and stock release document. A warehouse employee picks the product and sends it to the shipping department along with the stock release document. A warehouse clerk then updates the inventory records to reflect the reduction of inventory on hand. At the end of the day the clerk prepares a hard-copy inventory account summary and sends it to the general ledger department for posting to the inventory control and cost of goods sold accounts.

Upon receipt of the stock release document from the warehouse, the shipping clerk prepares the two copies of a bill of lading. The BOLs and the packing slip are sent with the product to the carrier. The clerk then files the stock release in the department.

Cash Receipts Procedure

The mail room has five employees who open mail and sort the checks from the remittance advices. The remittance advices are sent to the accounting department where the accounting clerk updates the customer AR subsidiary ledger to reflect the reduction in accounts receivable. At the end of the day the clerk prepares an account summery and sends it to the general ledger department for posting.

The mail room clerk sends the checks to the cash receipts department, where a clerk endorses each check with the words “For Deposit Only.” Next, the clerk records the cash receipts in the cash receipts journal. Finally, the clerk prepares a deposit slip and sends it and the checks to the bank.

Required

Assume that the manual system described is to be automated using a relational database system. Perform the following tasks. You may need to make assumptions about how certain automated activities will be performed.

a. List all candidate entities in the procedures described.

b. Identify the valid entities and explain why the rejected entities should not be modeled.

c. Create a data model of the process showing entity associations.

d. Create a filly attributed model by adding primary keys, foreign keys, and data attributes. Normalize the model.