Chapter 9, Problem 1P

The Mega Supply Corporation has three divisions: Commercial Products, Consumer Products, and Corporate Offices, which are located in Hatfield, South Carolina; Palo Alto, California; and Tulsa, Oklahoma, respectively. The Commercial Products division deals exclusively in sales of industrial products and supplies to business organizations. The Consumer Products division sells nonindustrial products to private consumers. Both divisions have dedicated inventory warehouses at their respective locations in Hatfield and Palo Alto. Because of the dissimilar nature of the commercial and consumer division product lines, they do not share customers or vendors.

Currently Mega Supply uses a centralized database, which is located at their Corporate Division in Tulsa. Some relevant database tables and attributes are presented in the figure designated Problem 1.

When customers contact their respective sales division, the sales clerk logs into the corporate database, checks credit, determines product availability, and creates a sales invoice. The corporate office typically bills the customer within 3 or 4 days and extends terms of net 30. Inventory control, AR processing, cash receipts, purchases from vendors and AP processing, and cash disbursements are performed by the corporate office.

Due to Mega’s rapid growth, the company has seen a significant increase in sales and purchase transactions, which has resulted in excessive delays in processing transactions from the central database. Since customer service, including rapid response to customer inquiries and sales order processing, is a cornerstone of Mega’s business model, these delays are unacceptable.

Required

Mega wants to improve response time by distributing some parts of the corporate database while keeping other parts of it centralized.

1. (A) Develop a schema for distributing Mega Supply Corporation’s database. Add new tables and attributes as needed but limit the schema to the tables needed to support sales, cash receipts, purchases/AP, and cash disbursements. In your schema, indicate whether tables are centralized, replicated, or partitioned.
2. (B) Explain how the new system will operate.