Ernst and Anderson is a manufacturer of power tools and other products used in the construction industry. The company was founded in the early 1900s as a manufacturer of quality hand tools such as hammers and screwdrivers, but such products have represented a decreasing percentage of total company sales for more than a decade. The company has been very successful in industrial power tools, which is its primary line of business. Several small companies were acquired in the late 1980s to broaden the company’s product line to include a variety of commercial products such as small appliances and household cleaning equipment such as vacuum cleaners. Because their products are consumer- oriented and thus do not compete with industrial power tools, these acquisitions generally have been allowed to operate as independent companies. The company operates several manufacturing facilities that produce power tools, but the largest and oldest plant, based in Pittsburgh, Pennsylvania, accounts for almost 80% of total production. The Pittsburgh plant employs more than 1,000 people and manufactures more than 250 different types of power tools. Much of the company’s success in maintaining its position in the marketplace in the face of intense international competition can be attributed to its long- standing policies concerning good employee relations and adequate plant maintenance. Although old in years, the Pittsburgh plant has been well maintained, and the company has continuously invested in new manufacturing equipment. A computer- based information system for production control has been used at the Pittsburgh plant since the early 1970s. Management of Ernst and Anderson was an early believer in the view that adequate information is an essential ingredient in the successful operation of a complex manufacturing operation. The production control system is organized into five major applications: production scheduling, materials management, labor cost reporting, work- in- process inventory, and finished goods inventory. These applications were developed separately and have been modified constantly over the years. The five different applications all use their own separate files and programs. Applications are linked together through separate batch processing runs. For example, periodically the work- in- process application is processed to generate a file of completed jobs. This transaction file of completed jobs is then reformatted as necessary and processed by the finished goods inventory application to update the finished goods inventory records. The reformatting is necessary because each application has been developed independently. This has resulted in some inconsistencies between identical data elements in the different applications. These inconsistencies have become more and more bothersome as the company has expanded the role of its computer- based production control system. Management is convinced that the effectiveness of the information system can be increased significantly if the five separate, stand- alone applications are integrated in a DBMS. To this end, the company has established a project team to study the feasibility of using a DBMS. The company hired a large public accounting firm to assist in this project. On the advice of the consultants, the project team has begun a study of the inputs, file structures, and outputs of the five separate applications to determine the data- definition ambiguities that exist in the present systems. These ambiguities will have to be eliminated if the production scheduling, materials management, labor cost reporting, work- in- process inventory, and finished goods inventory applications are to be integrated in a DBMS. To date, the project team has studied three of the five applications, and this has taken three times as long as the team had originally expected. More than 400 different data names were discovered, but analysis of these data names indicated that only about 150 different data variables really existed.
The difference is due to data redundancy. For example, the number of units being produced in a job is referred to by the data name “Units started” in the production scheduling application but by “Quantity” in the work- in- process application and by “Units” in the finished goods inventory application. These three different data names in the three different applications refer to the same real data element. As another example, the economic production quantity for a product is referred to by the data name “Lot size” in the production scheduling application but by “Minimum level” in the finished goods inventory application. These examples indicate one reason why data have to be reformatted when they are transferred from one application to another. The project team found many other similar instances of data redundancy in the three applications it has studied so far. The problem of data redundancy is also complicated by the fact that the different data names for the same data element usually have different physical representations in the different applications. For example, “Units started” in the production scheduling application has a length of 8 characters, but “Quantity” in the work- in- process application has a length of 10 characters.
Inconsistency is another type of data ambiguity the project team discovered. Inconsistency occurs when the same data name is used to mean different things in different applications. For example, the data name “Code” in the production scheduling application means department code, but “Code” means transaction code in the work- in- process application and product number in the finished goods inventory application. This is yet another reason why data have to be reformatted when they are transferred from one application to another. The project team found several other similar instances of data inconsistency in the three applications it studied. The five applications cannot be integrated while data ambiguities exist. Yet the project is taking considerably more time than expected. The status of the DBMS project is currently being reviewed by the management of Ernst and Anderson.

Required
a. If a user requested the data name “Lot size” in the production scheduling application, and the same user entered the data name “Minimum level” in the finished goods inventory application, what would be the result? If a user requested the data name “ Units started” in the production scheduling application, then requested the data name “ Quantity” in the work- in- process application, and then requested the data name “ Units” in the finished goods inventory application, what would be the result? What additional consideration compounds this type of problem?
b. If a user requested the data name “Code” in the production scheduling application, then requested “Code” in the work- in- process application, and then requested “Code” in the finished goods inventory application, what would be the result?
c. Discuss the role of a data dictionary in analyzing data ambiguity. Indicate how specific sections of a data dictionary can help resolve data ambiguity.
d. What action should the management of Ernst and Anderson take concerning the status of the DBMS project? Should the project team complete its study of data ambiguity even though the project has taken considerably more time than expected? Should the DBMS project proceed in view of the large amount of data ambiguity that has been discovered? If so, what is the first database that should be implemented?