Database Software Creation Process

1. Database Software Creation Process Arvin Meyer, MCP, MVP arvinm@datastrat.com

2. Arvin Meyer, MCP, MVP arvinm@datastrat.com Chief Database Architect

3. Process • Identify Functional Requirements • Identify Performance Requirements • Define Measures of Performance • Define Personal Roles • Define Business Rules • Define the Structure of the Database • Identify the Required Infrastructure • Identify Other Systems • Define Manpower Requirements • Define Personnel Requirements • Define Support and Training Requirements

4. Process • Identify Functional Requirements • Identify Performance Requirements • Define Measures of Performance • Define Personal Roles • Define Business Rules • Define the Structure of the Database • Identify the Required Infrastructure • Identify Other Systems • Define Manpower Requirements • Define Personnel Requirements • Define Support and Training Requirements

5. Indentify Functional Requirements Identify the activities, tasks, and the actions required to achieve the stated system goals. To do that the goals of the system must be first defined or limited. This is also known as a definition of scope.

6. Identify Functional Requirements - Interview with management 1. Determine a single point of contact at the initial meeting. If there are multiple people, make sure that each is responsible for a single part of the project. Note the names and area of responsibility of each key person. Schedule specific interview time with each of them, separately. 2. Determine goal or focus of database. Determine the specific problem(s) that the database is supposed to address. Ask for samples of any specific report output that may be required. 3. Discover if there are any conflicting goals (i.e. keeping data secure and freely available). The way data is accessed may be specialized for different uses. 4. Determine key users to interview. Ask managers what each key user’s responsibility is. 5. When interviewing key users ask how they describe their responsibilities. Make note of any differences between user and manager descriptions.

7. Process • Identify Functional Requirements • Identify Performance Requirements • Define Measures of Performance • Define Personal Roles • Define Business Rules • Define the Structure of the Database • Identify the Required Infrastructure • Identify Other Systems • Define Manpower Requirements • Define Personnel Requirements • Define Support and Training Requirements

8. Indentify Performance Requirements Identify the performance capabilities required to successfully achieve the stated system goal.

9. Indentify Performance Requirements I. Expectations 1. What are the expectations of management? 2. What are the expectations of each of the users as to how the new system will perform? II. Limitations 1. Are there any hardware limitations that will prevent these expectations from fruition? 2. Are there any software limitations? 3. Who is responsible for addressing each of the identified limitations?

11. Indentify Performance Requirements I. Expectations 1. What are the expectations of management? 2. What are the expectations of each of the users as to how the new system will perform? II. Limitations 1. Are there any hardware limitations that will prevent these expectations from fruition? 2. Are there any software limitations? 3. Who is responsible for addressing each of the identified limitations?

12. Process • Identify Functional Requirements • Identify Performance Requirements • Define Measures of Performance • Define Personal Roles • Define Business Rules • Define the Structure of the Database • Identify the Required Infrastructure • Identify Other Systems • Define Manpower Requirements • Define Personnel Requirements • Define Support and Training Requirements

13. Define Measures of Performance Define the metrics by which the performance of the system will be assessed. In larger corporate systems, measures of performance are defined to a greater degree of specificity.

14. Define Measures of Performance Develop a set of standards 1. Determine if it is necessary to define metrics. (Small systems often won’t need them) 2. Can we measure whether or not this software is accomplishing its design goals? Example: If we build a mailing label system, what percentage of labels do we allow to not have zip codes? 3. Determine the requirement to alter business rules if the goals are not met. 4. An experienced developer must be involved at this point to provide a realistic assessment of cost and success factors. This will avoid making promises of the software which are not feasible or cost effective.

15. Process • Identify Functional Requirements • Identify Performance Requirements • Define Measures of Performance • Define Personal Roles • Define Business Rules • Define the Structure of the Database • Identify the Required Infrastructure • Identify Other Systems • Define Manpower Requirements • Define Personnel Requirements • Define Support and Training Requirements

16. Define Personal Roles and Business Rules Determine what decisions and activities within the operation of the database will be performed by employees and which decisions are not allowed to be performed by employees. This provides the balance between computer technology and the control that employees can exert on the system. In smaller sized companies, this control is normally wider than in larger companies. This includes leaving a decision to the user’s control, such as having input masks for dates, or perhaps requiring that specific labor-intensive actions be performed by the computer.

17. Define Personal Roles and Business Rules • Are business rules required? 1. To what extent do we allow a person to determine how data is entered and which business rules are to be enforced by the software? a. Smaller companies with a stable employee base generally require fewer business rules. 2. Make sure the corporate goals are met. 3. Make sure data integrity is not sacrificed if that data is critical to the company operation. 4. Users must be involved in all specification decisions or there is a 100% chance of error. • Identify which rules are necessities, and which are “bells and whistles” 1. Create a list of all identified business rules and prepare a checklist for management.

18. Process • Identify Functional Requirements • Identify Performance Requirements • Define Measures of Performance • Define Personal Roles • Define Business Rules • Define the Structure of the Database • Identify the Required Infrastructure • Identify Other Systems • Define Manpower Requirements • Define Personnel Requirements • Define Support and Training Requirements

19. Define the Structure of the Database Which tables will be required. Which fields will be required to achieve the necessary output. How will those tables be accessed (queries, forms, reports, web, etc.)

20. Define the Structure of the Database Method A Define table structures. 1. Outline and diagram all table structures. 2. Define all the data fields and datatypes for those fields. 3. Define and diagram all relationships and rules for enforcing Referential Integrity. Define Form and Report objects. 1. Count and name forms necessary to achieve desired result. 2. Count and name reports necessary to achieve desired result. Method B 1. Review existing paper forms/reports to determine data that must be captured. 2. Review existing legacy data to ascertain datatypes and structures. 3. Define fields and tables that will allow building a structure to construct forms/reports. 4. Define entity relationship diagrams and rules for enforcing Referential Integrity. Define Form and Report objects as above.

21. Process • Identify Functional Requirements • Identify Performance Requirements • Define Measures of Performance • Define Personal Roles • Define Business Rules • Define the Structure of the Database • Identify the Required Infrastructure • Identify Other Systems • Define Manpower Requirements • Define Personnel Requirements • Define Support and Training Requirements

22. Identify the Required Infrastructure Which parts of the system will require outside maintenance, identify the infrastructure that must be created or maintained to support the system.

23. Identify the Required Infrastructure Infrastructure includes items that may not typically be considered to be part of the database, such as networks, backup devices, etc. An example for a web based application would be making sure that the intended users have adequate bandwidth, or that the network is adequate to carry the traffic.

24. Process • Identify Functional Requirements • Identify Performance Requirements • Define Measures of Performance • Define Personal Roles • Define Business Rules • Define the Structure of the Database • Identify the Required Infrastructure • Identify Other Systems • Define Manpower Requirements • Define Personnel Requirements • Define Support and Training Requirements

25. Identify Other Systems Based on the definition of the scope of work, identify other systems, current or anticipated, with which the database design will be required to interact. Differences between legacy systems, future systems, and the database under design need to be identified.

26. Process • Identify Functional Requirements • Identify Performance Requirements • Define Measures of Performance • Define Personal Roles • Define Business Rules • Define the Structure of the Database • Identify the Required Infrastructure • Identify Other Systems • Define Manpower Requirements • Define Personnel Requirements • Define Support and Training Requirements

27. Define Manpower Requirements On systems which may require 24/7 access or have critical missions, how many people will be necessary to ensure that the database remains on line.

28. Process • Identify Functional Requirements • Identify Performance Requirements • Define Measures of Performance • Define Personal Roles • Define Business Rules • Define the Structure of the Database • Identify the Required Infrastructure • Identify Other Systems • Define Manpower Requirements • Define Personnel Requirements • Define Support and Training Requirements

29. Define Personnel Requirements Define the specific attributes of the manpower requirements. Personnel factors may include abilities, skill levels, experience, training, security clearances, and physical constraints.

30. Process • Identify Functional Requirements • Identify Performance Requirements • Define Measures of Performance • Define Personal Roles • Define Business Rules • Define the Structure of the Database • Identify the Required Infrastructure • Identify Other Systems • Define Manpower Requirements • Define Personnel Requirements • Define Support and Training Requirements

31. Define Training Requirements Define the amount and level of instruction necessary to use the system. Help systems can easily account for 40% of the cost of the system.

32. Define Training Requirements Determine the amount and level of instruction necessary to use the system: 1. While interviewing each of the users, get a “feel” for the comfort level that each has for computers and computer applications. Some users feel threatened by new applications and they will require more attention and training than others. Those users are also not ideal candidates for on-line training. 2. How much external documentation (manuals, slide or video training, etc.) will be required? Written documentation often takes 40% as long to produce as the software itself.

33. Other considerations include adequate resources (budget and manpower) required to complete the design and implementation of the project. A complete Requirements Analysis is necessary for a new system, but only parts of it may be necessary for existing systems. A complete Requirements Analysis should always be a billable item as it produces a finished product, the design of the database. There are 2 methods of achieving the final design: A. Following the steps outlined above – Generally necessary for a new system. B. Working in reverse – If there is an existing set of forms/reports that the customer is now using, or existing software that may be a legacy system but has salvageable data, that output can be used as a schema to design the framework of the new system.

34. Set Client Expectations • Pay on time. • Avoid micromanaging - look for results not process. • Be specific, know what you want. • Unless you have a very large budget, you can't have it all, you can't have endless flexibility for the one time situations. Be focused in your goals. • Be available for questions and testing. • Be congenial. Ensure that employees are also. • Pay on time.

35. Database Software Creation Process Arvin Meyer, MCP, MVP arvinm@datastrat.com http://www.datastrat.com/Presentations/DatabaseCreationProcess/