The International Widget Corporation is a smallsized manufac

The International Widget Corporation is a small-sized manufacturer and importer of hardware parts (widgets, flanges, thingamabobs, and so forth). In its present manual tracking system, orders received are recorded in a ledger (the \"Record of Orders\"), with information about the specific items to be delivered.

(a) How efficient would the present format of the data be for storing the orders when the system is automated?

(b) What kind of data structure would be necessary for holding this order data in memory in its present form? Explain the data structure clearly, using diagrams if needed.

(c) Identify at least five (5) problems or issues in the data instances shown. Why would each be a problem for the business users of the data?

(d) If you could reorganize the data to make its structure more effective, what would you change? Why? (Explain each change you would make.)

INTERNATIONAL WIDGET CORPORATION RECORD OF ORDERS (excerpt CUST CUST ORDER CUST ITEM DATE ID NAME STATE ID DESCRIPTION QTY 9046 022899 2001 Acme Mfg. OH 137 Model A Widget 5700 A92 Flange Assembly 225 263 Assembly Manual 1900 9047 030199 0376 Boston Bldrs. MA 421 Model C Widget 027 d Hd. Screw 4000 9049 030199 1198 F&H; Builders WT 137 Widget type A. 1200 A93 Type 2 Flange 225 A94 Flange Bracket 200 2710 9050-1 030299 2001 Acme Mfg OH 421 Model C Widget 924 Flange Assembly 100 76F Gasket Clamp 91 76E Left Gasket 131 UNIT SALES PRICE PERSON 4.957 Brooks 109.4 15.95 2.847 Thomas 0.040 5.950 Willis 109.400 6.003 2.847 Smith 109.40 0.31 9.21 Ohio New Engl. South Ohio

Solution

(a) The present format of data for storing the orders when the system is automated is highly inefficient. On the face of it, it may seem that the company is just keeping a ledger where it has only Order information but this is misleading. The company actually keeps track of the Order, Items in the order, Customer to which the order was deliverd and Sales Person who did the sale.

(b) When the system is automated the information in \"Record of Orders\" can be broken down into the following tables so that it can be persisted in any relational database:

ITEM TABLE

Description: This table has information about the item that have been ordered, such as description of item and its unit price.

Item ID(Primary Key)

Description

Unit Price

ORDER TABLE

Description: This table has information about the order that are placed, it has order Id, order date

And the customer who has placed the order. It has a foreign key Customer table.

Order Id (Primary Key)

Date

CustomerID

ORDER_ITEM TABLE

Description: An order can contain many items and one item can be associated with many orders. This table represents the many to many mapping between order and item. This also shows the quantity of each item in each table.

Combination of OrderId and Item ID is Primary Key. This is called composite primary key.

Order ID (Foreign Key)

Item ID (Foreign Key)

Quantity

CUSTOMER TABLE

Description: This table has information about the customer who has placed the order, information includes customer Id, name, state and sales person who made the sales.

Customer ID(Primary Key)

Customer Name

Customer State

Sales Person ID

SALESPERSON TABLE

Description: This table has information about the sales person and the region in which he serves.

Sales Person ID(Primary Key)

Name

Territory

(c)
1. The data instance shown is not normalized. This means all the information that our system will ever recieve will be populated into one table. This will lead to performance and scalability issues when large number of trnsactions happen and we have to query the database, as the table size will grow exponentially.

2. The table has multivalued attributes such as item ID, description, quantity and unit price. This make it difficult to query the table for a specific item id, its description and the quantity in which it was sold to a particular order or group of orders.

3. This table design has a lot of redundant data. For e.g. if a sales man X, makes 10 sales in his territory, the information about the sales man and his/her service territory will be saved 10 times.

4. The data is not logically stored, all information about different entities which have absolutely different characteristic from each other have been stored in one place. For e.g. Item, Order, Customer, Sales Person have nothing in common. Althogh they are associated with each other.

5. Placing all data at one places leads to updation anomalies. If we have to update the sales man information, then we have to update all the rows in the table which hold this information. Any row that does not get updated leads to anomaly and this is difficult to track.

6. If we have to delete a customer from the table then the corresponding sales man and item information is also lost as we will have to delete the entire row. This is called delete anomaly.

(d) Put some dummy values in the table design mentioned above.

The new schema is normalized, dependency preserving and lossless. This ensures that all the information that was available from the previous design can be obtained by joining the tables with each other in appropriate fashion. Also, this schema ensures a much clear, distributed, scalable and performance efficient data model.

Item ID(Primary Key)

Description

Unit Price