Benedict G. Scott

Sample Project

Client:

A major international corporation with multiple regions in the United States, Canada, the British Isles, Europe, and Asia.

Duration of Assignment:

From June of 1991 through October of 1992 based in Nashville, TN, with some analysis and design sessions and occasional team meetings abroad

Organizational Problem:

1. Contract management: In an organization the size of the client, keeping track of what contracts are in place to purchase supplies from which suppliers and at what prices is a non-trivial task. Some of this information is available at the corporate level and some is found only at the regional level. How to capture, organize, and store this information so that costs can be managed most effectively is an extremely complex question.

2. Corporate purchasing: Both inventory items and expense items are purchased centrally under corporate contracts. A system to track purchase detail by supplier has not existed previously. Determine the type of system needed.

3. Regional purchasing: Most inventory items are purchased only at the regional level, either under a corporate contract, or under a local contract, or as needed, without benefit of contract. The same applies to many expense items. The new system must be capable of tracking this information.

4. Vendor tracking and performance: Previously, keeping track of the number of vendors supplying a given product or set of products has not been possible because of a lack of information. The same can be said for which products are supplied by a given set of vendors. Establish this type of tracking to accurately report items supplied by suppliers and suppliers supplying items. In this way, provide information on vendor-item purchasing patterns most advantageous to the client.

5. Sources of information: Much of the information can be obtained from Accounts Payable systems, but some can only be obtained from other regional level systems. Identify all the source systems and all the dataflows needed to supply the information.

Technical Problem

1. Contract negotiation and adherence: Team leaders previously had no way to independently validate the actual prices paid for inventory and expense items. The system must facilitate this validation to reduce blind dependence on vendor information.

2. Supporting corporate buyers: Corporate buyers need to know what to buy from whom to meet schedule and cost constraints.

3. Supporting regional buyers: Regional buyers need contract information to meet schedule and cost constraints and to adhere to corporate guidelines.

4. Maintaining vendor information: Without valid vendor information, it is not possible to arrive at the optimal vendor-item mix. Previously, most of the vendor information either belonged to Accounts Payable or was filtered out there. The system must capture and maintain this information.

5. Varying standards across the organization: Many client regions have their own Accounts Payable and Purchasing systems. The granularity of data in these systems is not uniform. The new system must provide a consistent, company-wide granularity.

Assignment

1. Review the quality and availability of information across the organization: Examine the content of current and proposed Accounts Payable systems, purchasing systems, and invoice control systems.

2. Identify the user community and its informational needs: Based on existing surveys and new interviews, find out what data this group needs to see, what they need to maintain, and how to best meet the needs.

3. Develop entity/relationship models that meet the needs: Because of inherent complexities, there is more than one way to organize and relate the data. Analyze the advantages and disadvantages of each possible approach.

4. Assist in selecting the best model for meeting the needs: Present the entity/relationship models to the project team and recommend those solutions which best meet the needs of the client.

5. Develop the physical database: Select the levels of normalization and de-normalization which ensure reasonable performance while minimizing overhead.

6. Implement the physical database: Generate the Oracle tables and load with test data. Develop maintenance forms for each table.

7. Develop the prototype system: Guide a three-person development team to produce a working prototype of the system. Develop on-line forms, reports, and extracts for presenting the data to the users. Design the data loader for the system and guide its implementation.

8. Review GUI specification conformance: Track the activities of the GUI development and evaluate how well the GUI matches the capabilities of the prototype.

9. Represent the informational interests of Corporate Purchasing: Since Purchasing does not have any on-board database expertise, serve as their advocate in all areas relating to information management.

Solution

1. Recommend prototype approach: Because there is no existing system and user surveys are out-of-date, use a prototype approach to the development cycle to ensure that all user needs are incorporated.

2. Analyze data sources and sinks: Taking into account wide variation in the granularity of the data available, develop policies and procedures which assure capture of data at a granularity fine enough to be useful to the user community during the prototype.

3. Verify user community needs for information: Re-survey the user community at critical points in the prototype development to ensure that the system will meet needs.

4. Synthesize and present alternative solutions: Report on the feedback from the users. Incorporate the new and modified needs into the prototype. Report the changes to the project team.

5. Enhance the prototype: Incorporate user responses and suggestions in the prototype. Record and recommend existing and modified system features which address the user input to the project team.

6. Capture the logical and physical database in the CASE Dictionary: The CASE Dictionary serves as the repository of information about the system's entities and relationships. It will be used first to document the logical database and then to generate the tables for the physical database.

7. Develop the prototype system: A working prototype in place in six-months with approximately 1.5 man-years of effort.

8. Specify the processing for the data feeds: By combining information from different sources, enhance the quality of the incoming data to improve its granularity during the prototype. This is accomplished by adding additional data feeds from the sources and massaging the data during the loading process.

9. Monitor and enhance prototype functionality: As new needs for information are identified, modify the model and the physical database to accommodate the necessary changes.

10. Develop drill-down philosophy for GUI: Ensure that the GUI developers incorporate the major features of the prototype in the final version of the system.

11. Demonstrate prototype to user community: To keep the user community involved in the development of the prototype, conduct periodic demonstrations. Elicit user reactions, responses, and suggestions.

Environment and Methodology

1. Sun SPARC workstation running UNIX with and without X-Windows
2. Various HP computers running UNIX with and without X-Windows
3. Compaq and IBM computers running MS/DOS
4. Banyan Vines as a local area network
5. Internet backbone for a wide area network
6. Structured Analysis
7. Relational Database Design and Normalization
8. Oracle RDBMS
9. SQL Plus
10. SQL Forms
11. SQL Reportwriter
12. PL/SQL
13. C language
14. CASE Dictionary
15. CASE Designer
16. UNIX System Administration