Commonwealth of Pennsylvania

Department of Environmental Protection

Bureau of Information Technology

Project Management Office

Design Document

For

<Project Title>

Prepared by: <Name of Person Preparing Report>

Date: <January 1, 2005>

Project Manager: <Name of Project Manager>

This page is left intentionally blank.

Document Control

Author

Document Name:	Design Document
Version Number:
Version Date:
Project Manager:
Date Issued:

Approvals

The Commonwealth of Pennsylvania, Department of Environmental Protection (DEP) approves the Design Document within this document. Approval of this document signifies that this Design Document is a complete, quality implementation of the deliverable requirements as specified for the authorized work.

Roles	Name	Signature	Date
Quality Manager
Project Management Officer
Project Sponsor
Chief Information Officer

Change History

Version	Date	Revision Descriptions
V.01
V.02
V.03

TABLE OF CONTENTS

Document Control	ii

1.	System Architecture	2

1.2	Function To Module Matrix	2

1.3	Entity to Table Matrix	2

1.4	Module to Table Matrix	2

1.5	User to Table Matrix	2

1.6	User Group/Role to Module Matrix	2

1.7	User/Module Interfaces	2

1.7.1	User Class Access/Access Type	2

1.7.2	Menu Structure/Linkage	2

1.7.3	Manual Procedures/Data Entry Form Usage	2

1.7.4	Special Work Flow Procedures	2

1.7.5	Required Forms Modifications	2

1.8	Integration with Other Systems	2

1.9	Grants	2

2.	DBMS Object Definition/lmplementation	2

2.1	File Definitions and Placement	2

2.1.1	Database Definitions	2

2.2	Rollback Segment Definitions	2

2.3	Table Space Definitions	2

2.4	Table and View Definitions	2

2.5	Index and Cluster Definitions	2

2.6	Sequence Definitions	2

2.7	Constraints	2

2.7.1	Table Constraints	2

2.7.2	Column Constraints	2

2.8	Detail Sizing & Storage Definitions	2

2.9	DDL	2

3.	Module Design	2

3.1	Description	2

3.2	Specifications	2

3.2.1	User/Module Interfaces	2

3.2.2	Menu Structure/Linkage	2

3.2.3	Screen and Report Dialogues	2

3.2.4	Data Entry Forms	2

3.2.5	Special Work Flow Procedures	2

3.3	Field Specification/Validation	2

3.4	Draft Layouts	2

3.4.1	DETAIL OF PROCESSING	2

3.4.2	Special Processing Logic	2

3.5	Audit Trail	2

4.	Menus	2

5.	Database Tuning Considerations	2

5.1	Tuning At Installation Time	2

5.2	Optimizing I/O	2

5.3	Optimizing Memory Usage	2

6.	Application Tuning Considerations	2

6.1	Using the SQL Language Effectively	2

6.2	Using Indexes and Clusters Effectively	2

7.	Help System Design	2

7.1	Offline Help	2

7.2	Online/Context Sensitive Help	2

8.	Network and Communication Design	2

8.1	Network Architecture	2

8.2	Network Control Procedures	2

8.3	Role of Clients and Servers	2

9.	Transition Plan	2

9.1	Delivery and Acceptance Plan	2

9.2	Training Plan	2

9.3	Data Take-On Plan	2

9.4	Cut-Over Plan	2

9.5	Installation Plan	2

9.6	Operational Plan	2

9.7	Critical Success Factors	2

10.	Audit/Control	2

10.1	Financial and Statutory Auditing	2

10.2	Access Security Provisions	2

10.3	Control and Security Descriptions	2

10.4	Error Handling Descriptions	2

11.	BackuplRecovery Plan	2

11.1	Archiving Procedures	2

11.2	Recovery Procedures	2

12.	System Test Plan	2

12.1	Stand-Alone and Link Testing	2

12.2	Performance, Integrity, Recovery and Regression Testing	2

12.3	Environment Testing	2

12.4	Limit and Exception Testing	2

12.5	Integration with Existing Systems	2

12.6	Access Control	2

12.7	Conversion and Data Take-On	2

12.8	Others where required	2

13.	Special Considerations	2

13.1	Risks, Problems and Gaps	2

13.2	Prototype Documentation	2

14.	Forward System Development Plan	2

14.1	Build and User Documentation Stage	2

14.1.1	GANTT CHART	2

14.1.2	MANPOWER REQUIREMENTS	2

14.1.3	COST ESTIMATES	2

14.1.4	EQUIPMENT ESTIMATES	2

14.2	Subsequent Stages	2

14.2.1	GANTT CHART	2

14.2.2	Manpower Requirements	2

14.2.3	COST ESTIMATES	2

14.2.4	Equipment Estimates	2

Attachments	2

Glossary	2

Index	2

The Design Report includes system architecture, program module architecture, specification of manual procedures, system network definition overview, design overview, tables, indexes and space usage, sizing, and transition strategy. The report incorporates all the approaches, standards, guidelines, plans and assumptions that will be used to build the team.

Note the gray text indicated current developer software toolset names that may be changed over time.

Bureau of Applications Development is currently using Oracle Designer R6.0 as their primary CASE Designer tool.

1.	System Architecture

This section describes menu structures, screen dialogues, batch procedures, manual procedures, user classes, user interface, database objects, modules, network and communication, audit/control and backup/recovery of data, test plans and special considerations/assumptions involved in the system design.

1.2	Function To Module Matrix

Identifies the functions to be satisfied by each module. This matrix is necessary to ensure that the functions defined on the ‘Function Hierarchy Diagrammer’ tool of ORACLE Designer R60’ during Strategy and Analysis stages are implemented by respective modules during the Design stage.

The Module/Function Matrix information can be displayed by using the ‘Matrix Diagrammer’ tool of ORACLE Designer R60’.

This requirement can be met by running reports available in ‘Repository Reports: Entity/Relationship Modeling: Function to Entity Matrix’. This report shows all the functions in the specified application system, by name and hierarchy reference, together with the modules that implement them. Project monitoring information is also contained in the report, including the task code, whether the modules are complete, the programming language being used, the complexity of the module, and an estimate of the effort required.

1.3	Entity to Table Matrix

Identifies the relationship between entities and corresponding tables. This matrix is necessary to ensure that all the entities and their attributes created in the entity relationship diagram during the Strategy and Analysis stages are implemented by corresponding tables and their columns.

This information can be displayed by using ORACLE Designer R60’s ‘Matrix Diagrammer’.

This requirement can be met by running reports available in ‘Repository Reports: Server Model Definition: Entity to Table Implementation’.

1.4	Module to Table Matrix

Identifies the table(s) that support each module. This matrix is necessary for doing an impact analysis; i.e. All the modules that get affected by modifying a table structure, also, how many tables (and so entities) are used by a module, etc.

This information can be displayed by ORACLE Designer R60’s ‘Matrix Diagrammer’.

This requirement can be met by running reports available in ‘Repository Reports: Impact Analysis’. These reports shall all the tables and columns that are used, and how they are used, by a module in a specified application system.

1.5	User to Table Matrix

This matrix is very important to DBA for giving Grants to the users of the system. To define a specific user’s access to different database objects like tables, views, sequences, etc. use: ‘Repository Object Navigator: Database & Network Design’ folder.

1.6	User Group/Role to Module Matrix

While investigating requirements, different types of users will be identified, depending on their needs to execute certain function or being barred from such access. This matrix is of use to the designer where users will be given access to generalized function modules or end-user query language.

This requirement can be met by using the ‘Repository Object Navigator: Database & Design’ folder. This report contains a list of user groups defined within the specified application system, together with the comment, database user names and the modules that can be accessed by each user group.

1.7	User/Module Interfaces

Describes the users ability to perform specific functions given a user class; the relationship among menus within the application; the correlation between the application and the manual procedures or forms; any work flow procedures suggesting special attention; and any forms which need to be modified or created to fulfill the functionality of the application.

1.7.1	User Class Access/Access Type

Illustrates the type of manipulation a user class can perform to the information within the application. A USER CLASS is a group of individuals having similar needs, i.e. managers from the field offices comprise the Field Managerial user class. An ACCESS TYPE indicates the rights to perform a function on specific fields/records of information. Following are the available access types: READ, meaning an individual can read information on the screen in an inquiry manner; INSERT, pertaining to the ability to create a new record or occurrence; UPDATE, which allows updates or changes be made to the information; and DELETE, allowing the elimination of the occurrence. It also incorporates procedures for controlling the security provisions.

To make the above manipulations, use: ‘Repository Object Navigator (tool): Database & Network Design (folder). This area is used to define a group of ORACLE users within the application system.

1.7.2	Menu Structure/Linkage

Illustrates the functional structure and navigation steps of the application.

This section is also supported by Repository Reports: Module Design: ‘Menu and Screen Definition’.

1.7.3	Manual Procedures/Data Entry Form Usage

Designates input forms which will be required to input information onto the screen. Forms are typically those used as manual data collection tools.

1.7.4	Special Work Flow Procedures

Lists any special actions to be aware of when utilizing the screen as well as sequence of steps to be followed to complete the task.

1.7.5	Required Forms Modifications

Shows any Application Design Transformer generated forms that may need to be customized.

It must be noted that any customizations performed on the forms need to be recorded back into the Repository Reports using Application Design Transformer’s “Re-Generation” techniques.

1.8	Integration with Other Systems

Identifies systems which either perform discrete functions or carry out some of the functionality required for this application. Identify areas for modification or integration with other parts or other systems.

1.9	Grants

Provides restricted access to database and requires DBA privilege. The database privileges include DBA, CONNECT, RESOURCE, and Object Privileges. The DBA privilege allows user to bypass many standard privileges and also to perform certain data administration tasks.

The CONNECT privilege establishes a new username in database so that the user may connect to the database and operate on any objects to which he has been given privilege.

The RESOURCE privilege permits a user to create database objects including tables, indexes, clusters, and sequences. It also provides limited resource for specific tablespaces or unlimited resource on all tablespaces.

Object privileges include ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT or UPDATE for tables, views and sequences.

This section is supported by the ‘Repository Object Navigator tool’s Database and Network Design folder.

This requirement can be met by running the following in Repository Reports: Database and Network Design: ‘Role Definition’ report.

2.	DBMS Object Definition/lmplementation

This section deals with implementing the entities defined in the Entity Relationship diagram during the Analysis stage into corresponding table(s). To achieve this, the database and its objects must be created.

2.1	File Definitions and Placement

This section is supported by ‘File Definition’ screen under ‘File / Record Definition’ of Repository Object Navigator. This screen is used to define files that are used by the application system. These may be files that are used to interface to other systems, or they may be old files that are to be converted to ORACLE tables during transition to new system. This screen is used to design a file and the records held by it, and to define the fields comprising each record. Any entities to be registered as sources of record, together with the application system in which they are included, must already exist.

This requirement can be met by running the following Repository Reports under the ‘File / Record Definition’ folder:

‘File and Field Definitions for a Given Record’

‘File and Field Definitions’

“File, Record and Field Definitions’

2.1.1	Database Definitions

An ORACLE database consists of one or more database files that contain all the database data.

This section is supported by ‘Database and Network Design’ of Repository Object Navigator. This area records information about database name, control file, archive log files, and data files.

This requirement can be met by running the following Repository Report: ‘Database Definition’ under the ‘Database and Network Design’ folder.

2.2	Rollback Segment Definitions

Each database contains one or more rollback segments. Rollback segments generally improve database performance by undoing changes in the database in the event of transaction rollbacks, media recovery, read consistency, etc.

This section is supported by the ‘Database and Network Design’ folder in the Repository Object navigator. You can find Rollback Segment information in the ‘Databases’ folder.

2.3	Table Space Definitions

The database is divided into logical sections called tablespaces. Each tablespace (logical) corresponds to one or more physical database files. Tablespaces are of primary concern to the Database Administrators who use it to control space allocation, space quotas for users, availability of data, backup/recovery, database tuning tool etc.

This section is supported in the ‘Database & Network Design’ folder of ‘Repository Object Navigator’. This area is used to define tablespace(s) that can be used by tables, indexes, sequences, clusters and rollback segments. Information contained in this screen is used by the DDL Command Generator Utility to generate a SQL statement.

This requirement can be met by running Repository Reports: Database & Network Design folder.

2.4	Table and View Definitions

This section deals with defining tables in the database. Tables are the basic unit of data storage. Table and View definitions are required to maintain a link and derivation from the entities in the E-R diagram. Table is defined (CREATE TABLE...) with a table name and a set of column names. Each column is given a column name, a datatype, and a width and optionally specifies whether it requires values, or whether it allows null values. Optional default values and referential constraints may be specified.

A View in an ORACLE database is a stored query that presents the data from one or more tables. Views are technically not tables but behave like one. Views require no physical storage but are created for convenience and security reasons.

The Table Definition section is supported by Repository Object navigator’s ‘Server Model Definition; Table Definitions’ folder of Repository Reports.

This requirement can be met by running the following Repository Reports; under ‘Server Model Definition’ folder:

‘Table Definition’

‘Tables and Primary Key Derivations’

‘Tables, Columns and Foreign Key Derivations’

The View Definition section is supported by the ‘Repository Object navigator: View Definitions’ folder.

This requirement can be met by running Repository Reports ‘Snapshot/View Definitions’ under ‘Server Model Definition’. This report gives a list of views that have been used to produce a view in the specified application system.

2.5	Index and Cluster Definitions

Indexes on ORACLE tables are used to increase the speed with which rows can be retrieved from the tables. They can also be used to guarantee the unique nature of a column or set of column values in the table and ordering of data on one or more columns. There may be a need to add some indexes to facilitate access to some data which may not be a key in the table.

The question of accessing data via an Index or table scan depends on the size of the table as well as the nature of index (unique vs. non-unique). If the table size is small it is worthwhile to scan it.

Clustering is a means of structuring the data in one or more tables so that the columns are physically close to one another in the ORACLE database.

The Index definition section is supported by the ‘Repository Object Navigator’ under ‘Database and Network Design’ folder.

This requirement can be met by running the following Repository Reports: ‘Index Storage and Partitioning’ under the ‘Database and Network Design’ folder.

The Cluster definition section is supported by ‘Repository Object Navigator’ under ‘Server Model Definition’ folder.

This requirement can be met by running the following Repository Reports: ‘Cluster Definition’ under ‘Server Model Definition’ folder.

2.6	Sequence Definitions

Sequences can be generated by the Design Editor tool. It will generate numbers for rows in tables; you can use sequence numbers to automatically generate unique primary keys for your data, and you can coordinate keys across multiple rows or tables.

This section is supported by the ‘Sequence Definitions’ folder under the Server Model Definition folder of the Repository Object Navigator’ tool. After defining the sequences, the Design Editor can be used to generate the SQL statements necessary to create the sequences in an ORACLE database.

To view Sequence data run the following Repository Report: ‘Sequence Definition’ under ‘Server Model Definition’.

2.7	Constraints

Constraints in ORACLE database serve the purpose of restricting/validating for a column or a group of columns. All INSERT, UPDATE and DELETE statements cause the evaluation of relevant constraints. The constraint must be satisfied for the statement to succeed. There are two types of constraints:

2.7.1	Table Constraints

These are part of the global table definition and they can reference one or more columns.

2.7.2	Column Constraints

They are local to a specific column. The Constraint Clause can have the following keywords and parameters:

NULL

The NULL constraint means that a column may be null for any row of the table. Conversely, every row must have a value for any column that is NOT NULL. This constraint may only be used in a column constraint.

UNIQUE

The Unique constraint:

requires that each row in the table have a distinct value for the column

each column be declared NOT NULL

the column not be a PRIMARY KEY

PRIMARY KEY

A Primary Key constraint:

is used to uniquely identify each row in a table

is a superset of the UNIQUE requirements and as such requires that a column be declared NOT NULL

requires that the column not have the UNIQUE constraint

may be specified only once per table. The primary key may be composed of multiple columns.

FOREIGN KEY/REFERENCES

The Foreign Key/Reference constraint:

must reference a PRIMARY KEY or UNIQUE column or group of columns in the primary key table

will reject an INSERT or UPDATE if value of a corresponding key does not currently exist in the primary key table

will reject a DELETE if it would invalidate a REFERENCES constraint

requires that the datatypes of foreign key column(s) and constraint column(s) match

CHECK

The Check constraint specified a condition that the column must satisfy for the row to exist in the table. This constraint may only refer to columns in the same table.

These Table constraints are supported by ‘Repository Object Navigator’ under Server Model Definition folder. The information entered in this screen is used by Application Design Transformer to validate column values and establishing relationships between table usage, and consequently the blocks on generated forms.

These Column constraints are supported by ‘Repository Object Navigator’ under Server Model Definition folder. The information entered in this screen is used by Application Design Transformer to determine access to, and the use of tables/views and their respective columns.

The Table constraint information can be viewed by Repository Report Constraint Definition’ under ‘Server Model Definition’.

The Column constraint information can be viewed by Repository Report ‘Column Definition’ under ‘Server Model Definition’.

2.8	Detail Sizing & Storage Definitions

This section deals with defining space to be used by database (datafiles and redo log files), tablespace, cluster & cluster index, tables, indexes, etc. It must be noted that except for database sizing, all other sizing must be done using Storage Definition Clauses.

Storage Definitions are supported by ‘Repository Object Navigator’, “Reference Data Definition’ folder. This screen is used to define a named set of storage parameters for use in the SQL STORAGE clause.

This requirement can be met by running the following Repository Report: ‘Storage Definition’ under ‘Database and Network Design’ folder.

Database sizing is supported by ‘Object Database Designer’ used by Database Administrators wherein datafile(s) and redo log file(s) are defined and their size(s) specified.

Tablespace sizing is supported by ‘Object Database Designer’ used by Database Administrators wherein a storage name and datafile(s) are specified.

Cluster and cluster index sizing is supported by ‘Object Database Designer’ used by Database Administrators wherein storage name, tablespace name, pctfree, pctused for cluster and storage name and tablespace name for cluster index are specified.

2.9	DDL

Generate command files to create databases, tablespaces, tables, views, indexes, clusters, rollback segments, grants, sequences, etc. These files must then be used on target database(s) to create the required object (s). Information about all production, development, test and training databases must be stored in these DDLs.

This section is supported by the ‘Generate DDL’ button under the ‘Design Editor’ tool. The commands are written to a user specified command file.

3.	Module Design

3.1	Description

Description of the function, screen, or report under consideration.

3.2	Specifications

Specifies the details to be used for screens and reports in the ORACLE environment. This section will identify the environment in which the program will operate, e.g. on-line versus batch, keyboard input versus tape input, etc., the parameters required, and its relationship to other jobs.

3.2.1	User/Module Interfaces

Describes the users ability to perform specific functions given a user class; the relationship among menus; the relationship and meaning of fields within a screen or report; and any workflow procedures suggesting Special attention.

Illustrates the type of manipulation a user class can perform to the information within the application. A USER CLASS is a group of individuals having similar needs, i.e. managers from the field offices comprise the Field Managerial user class. An ACCESS TYPE indicates the rights to perform a function on specific fields/records of information. Following are the available access types: READ, meaning an individual can read information on the screen in an inquiry manner; INSERT, pertaining to the ability to create a new record or occurrence; UPDATE, which allows updates or changes be made to the information; and DELETE, allowing the elimination of the occurrence. It also incorporates procedures for controlling the security provisions.

3.2.2	Menu Structure/Linkage

Illustrates the functional structure and navigation steps of the application. Within each system there are modules which may contain sub‑modules and may be a sub-module itself. This requirement can be met by ORACLE Designer R60’s Matrix Diagrammer.

This information can be viewed by Repository Reports report ‘Modules used by a Given Module’ under ‘Impact Analysis Reports Menu (Analysis)’.

3.2.3	Screen and Report Dialogues

Display the actual screen or report. The (________) symbol identifies the positions on the screen/report for which information will be displayed. Field prompts and any special formatting characters (slashes, parentheses, hyphens, etc.) are also displayed in the position they will appear.

(Include Screen Shots of Forms and Reports)

3.2.4	Data Entry Forms

Identifies the hardcopy input form*.

* if there are any hardcopy forms

3.2.5	Special Work Flow Procedures

Lists any special actions or procedures to be aware of when utilizing this screen.

3.3	Field Specification/Validation

Shows how each field on the screen (and those that are ‘behind the scenes’ and not visible to the user) will look, act and feel. Each field should be identified by the logical name and the physical database name. If the field is a key field, identify the following: primary or foreign, uniqueness, default values, and determination of how the key is derived. All special validation logic should also be included here.

This section is supported by Repository Object Navigator.

The ‘Table Definition’ folder details column data i.e. Datatype, maximum length of data, number of decimal places, null or mandatory, uppercase only, default value, Auto‑generated flag, the name of the sequence which creates a unique value for the column, format mask, field prompt, Hint message, etc.

Report requirements about Columns can be met by Repository Reports report ‘Column Definition’ under ‘Server Model Definition’. This report shows all the column definition details that were recorded on the Column Definition Screen for the given table(s) or view(s) in the specified application system, together with display parameters.

3.4	Draft Layouts

Designs of each anticipated implementation module. This requirement can be met by Application Design Transformer’s generated SQL*Forms and SQL*ReportWriter/SQ1*Plus report layout for the prototype.

Remember: The layout design is the users’ interface with the system and will largely determine their perception of its usefulness. It is vital to understand the users’ pattern of working, so that functions are grouped appropriately for access. For each module include a matrix identifying the tables and columns to be accessed by the module, and the mode in which each column is accessed by this module.

3.4.1	DETAIL OF PROCESSING

Identify any complex functions defined by their detailed function logic will be used to perform each aspect of the function defined. It also includes source and runtime path, command line parameters, etc.

This section is supported by following screens under ‘Module Design Menu’ of Repository Reports:

‘Module Definition Screen’

3	‘Module Definition - Program Interface Screen’

4	‘Module Parameter Definition Screen’

This requirement can be met by Repository Reports report ‘Module Data Summary’ under ‘Module Design Reports Menu’.

3.4.2	Special Processing Logic

Identifies to document any ‘special’ or tricky areas that may not be apparent to other programmer/analysts. Include such things as data integrity constraints, i.e. when deleting a master record ensure that all detail records associated with the master are also deleted, etc. All block and form level logic should be specified in this section and special “where” clauses, special block coordination, special starting triggers, special keys and data integrity constraints.

3.5	Audit Trail

Document the methods for tracking the actions e.g. INSERT, UPDATE and DELETE performed by the users on data along with the date‑time of the action and if needed maintaining a before image of the changed record.

This requirement can be met by “Journaling” the Table. This feature can be invoked by setting the field “Journal Table” to “Y” on the ‘Table Definition Screen’ under ‘Database Design Menu’ of Repository Reports. A journal table keeps track of table’s history by recording all inserts, updates and deletes of the table it accompanies. Before implementing the journaling feature it is important to consider the following:

•	frequency of DML operations; On DML operations data row is written into journal table, which grows rather quickly if there are many DML operations.

•	performance of the forms; Each time a DML operation is performed both the original and the journal table have to be accessed which can impact the performance of forms.

•	use of non-updateable primary key; On each DML operation only the primary key value along with the journal information is recorded in the journal table. Since the primary key value is the only way to relate back to the original table, hence wherever possible journal‑ing table should have a non‑updateable primary key.

4.	Menus

The Menu structure must be defined to link all the modules in an application system. Menus permit the user to invoke the required software application by selecting the appropriate menu option. The procedures for selecting menu options, and returning to the previous menu should be specified. The menu styles i.e. pull‑down, bar, full screen, etc. must be considered.

This section is supported by the ‘Application Design Transformer’. When the required details have been recorded in Repository Reports, the user must select the Application Design Transformer for SQL*Menu option on the Application Design Transformer for SQL*Menu Menu. Application Design Transformer creates a menu for each module in the application system that has the following details:

•	Language - SQL*MENU

•	Type - MENU

There are several preference flags which allow control over different aspects of SQL*Menu i.e. Administration, SQL*Forms calling menus, End User Interface, Generator Runtime, Menu Layout, style, etc.

Each item on a menu is derived from a module called by a menu module. The groups of users assigned to the module determine the roles that they are assigned to the menu item(s) derived from it.

5.	Database Tuning Considerations

This section is important especially to the DBA to provide the best possible overall performance for all users of a database. Two basic concepts are associated with tuning a database system:

•	optimizing disk I/O

•	optimizing memory usage

In other words both processing and storage needs to be optimized. On an optimally tuned system, both the CPU and the disks operate at nearly 100 percent capacity.

Tuning a database for optimal performance is a complex task since many factors contribute to the process. Tuning performance occurs at many levels like:

5.1	Tuning At Installation Time

These steps can be taken only at installation time. They include:

•	Tuning 0/S by means of shared ORACLE code, fast response storage devices, direct memory access, process privileges and priorities, memory requirements, storage requirements, directory structures, etc.

•	Raising the priority of background processes doesn’t affect the ORACLE system performance but raising the priority of user processes benefits them.

•	The ORACLE RDBMS interacts best with the operating system if all database files are contiguous on disk.

•	Placing redo log files on a separate disk from database file(s).

•	Create new Rollback segment in addition to the SYSTEM rollback segment.

5.2	Optimizing I/O

As the number of users simultaneously accessing the database increases, the importance of balancing the I/O increases.

The I/O performance can be affected in the following ways:

•	Identify all data that will be accessed simultaneously in order to place that data on separate disk drives. This would allow maximum parallel I/O and minimum chance for I/O contention.

For example, separating table data from index data using tablespaces stored on separate disk drives.

•	Optimizing the SGA.

•	Spread database activity across tablespaces by:

•	Assigning a particular tablespace to be used primarily for temporary segments.

•	Assigning all users a tablespace other than SYSTEM tablespace for the creation of their database objects.

•	Grouping users of an application so that their data is either distributed across a few tablespaces or resides in one tablespace dedicated to that application.

•	Minimizing the number of extents so that the overhead required to access any given row is minimized. Full table scans perform slower against data in many segments. Occasionally it may be desirable to spread data across multiple extents to reduce block‑level contention and expedite many simultaneous updates. This is particularly true when a table is very heavily accessed and updated.

•	Reducing the frequency of Extent Allocations because these activities consume a significant amount of resource.

•	“Striping” large tables into sections and spreading the sections across different disks to allow for simultaneous access by many users.

5.3	Optimizing Memory Usage

Memory is a limited resource of the computer that runs the processes and also stores data. This resource should be managed properly to get good system performance. The following factors affect system memory and the performance:

•	Size of the buffer cache in the SGA.

•	Efficiency of the background database writer process DBWR.

Ideally, DBWR should work just hard enough to provide an adequate supply of free buffers so that database read operations are not constrained. If DBWR does not work hard enough, the system runs out of free buffers and users must wait for a buffer to be cleaned. If DBWR works too hard, it writes out buffers that may be changed again in the near future and will only have to be requested again.

In this case, user processes wait for DBWR to finish cleaning buffers.

Refer to ORACLE RDBMS Database Administrator’s Guide, chapter “Database Tuning” for detail information.

6.	Application Tuning Considerations

This section describes the performance implications of tuning of SQL statements and using indexes and clusters.

6.1	Using the SQL Language Effectively

The SQL data language is a rich, flexible, and powerful language. Some optimizations are performed automatically by rewording certain SQL statements using WHERE, DISTINCT, GROUP BY, NOT, ORDER BY clauses and also optimizing Sub‑queries, Joins, proper operator precedence, etc.

6.2	Using Indexes and Clusters Effectively

Using indexes is almost always preferable to using a full table scan. The indexes will be used only if:

•	there is a WHERE clause and it is referenced in a predicate.

•	the indexed columns is not modified by a function or arithmetic operation.

•	there is a WHERE clause and the search is not for records with NULL/NOT NULL values in the indexed columns.

The indexes should be created effectively i.e. concatenated indexes, indexes on NOT NULL columns, etc. The indexes should be used effectively by choosing among multiple indexes on one table, suppressing the use of “least selective” indexes, etc.

Refer to ORACLE RDBMS Database Administrator’s Guide, chapter: “Improving Performance of Applications” for detail information.

7.	Help System Design

Alongside the design of the application a user friendly help system is a must for the success of the project.

7.1	Offline Help

Offline help is required to conduct training classes, references, etc. for the users and for accessing help anytime and anywhere. This also serves as a help backup in case the computer system is shut-down.

7.2	Online/Context Sensitive Help

Context sensitive offers help on the exact field/block/form on which user needs help. It is invoked by pressing [Help]. The automatic display of hint text upon entry into a field is supported by AUTOHP preference which can be when the hint line is not displayed the user can press [Help] to show field-level hint text. If the hint line is already displayed, pressing [Help] will call the most detailed level of multi‑line help that is available for the form. There are two categories of context sensitive help text:

•	Hint text:

Hint text consists of a single row of text which appears on the hint line at the foot of the form when the user enters a particular field. If this is not present, the hint text becomes “Enter value for X”, where X is the field prompt or, if this is not available, the name of the field. This is always made available in the generated form. Hint text for a field is derived from the text entered in the hint column of the Column Usage block on the Repository Object Navigator.

•	Multi-line help text:

Consists of multiple lines of text that appear on a separate help screen. HLPFRM preference governs whether or not multi‑line help is made available in the generated form.

Multi-line help text is derived from help text recorded in the Repository Object Navigator, Multi‑line help text can be recorded against the following:

•	Module Definition

•	Table Definition

•	Detailed Table Usage

•	Column Definition

•	Detailed Column Usage

There are three levels of multi-line help text:

•	Field-level

Derived from help text recorded against the column usage on which the field is based. If this text has not been defined, the text recorded against the Repository Reports column definition for the underlying column of the field is used.

•	Block-level

Derived from help text recorded against the detailed table usage on which the block is based. If this text has not been defined, the text recorded against the Repository Reports table definition for the base table of the block is used.

•	Form-level

Derived from Module definition help text.

8.	Network and Communication Design

8.1	Network Architecture

Includes setting and type of terminals and processors, required protocols, system software, lines, devices. DEP utilizes an Extended Ethernet Local Area Network from Hewlett Packard (HP). This networking scheme basically permits two types of access, terminal and LAN.

•	Terminal access can be gained either by using a DEC, or DEC compatible terminal, or a PC running the appropriate terminal emulation software.

The terminal should be compatible with DEC’s VT220, VT320 or VT420 models. The Department’s standard terminal emulation software for MS‑DOS based PCs is SETHOST, a DEC proprietary product.

•	LAN access to the network is through PCs that have Ethernet Controller boards installed. This permits the PCs to operate as nodes on the network and grants them “peer‑to‑peer” communications with other nodes, such as DEC VAX processors Terminal. Access is generally at speeds of 19.2 KBS (kilobits per second) for terminals in the Harrisburg area and the Regional Offices, and 9.6 KBS for those located in other sites.

•	LAN access is at speeds of 10 MBS (megabytes per second) in those areas local to a LAN server and 56 KBS (kilobytes per second) in those places served remotely.

•	A connection into the network is required in order for any device to gain access. There are a limited number of dial up ports available for terminal access that will operate at 2.4 KBS. However, dial-up is not the preferred method of access and should be used only in those cases where no other type of service is available. Wherever an HP connection exists, terminal access is always available.

•	Utilizing LAN access will depend on the type of connection installed. Regional Offices and certain buildings in the Harrisburg area have the necessary accommodations for LAN access.

8.2	Network Control Procedures

Applications developers should consult with Bureau of Networking Operations, Network Administration personnel to see what network accommodations are present at the sites where access is desired. Once a device is connected into the network it is physically able to access any service offered, security restrictions being the only limiting factor.

If a connection is not available, developers should consult with Communications personnel to see if arrangements can be made to provide the necessary access points.

8.3	Role of Clients and Servers

This section is useful if the data in the system is distributed across several computers that need to communicate for accessing each other’s data.

Role of Clients and Servers are of prime importance when the user:

•	Wants to reference data on nodes other than where they are currently logged on.

•	Issue queries referencing data on single or multiple nodes.

•	Perform joins and nested queries on tables on different nodes.

•	Define views using tables on different nodes.

•	Perform updates, inserts, deletes and other DDL commands on tables residing on different nodes.

A distributed database consists of clients and servers. A client is an application requesting data from another node. Clients can be PCs, minicomputers, or mainframes.

A server is any node with a database, of which data may be requested. Servers must be multi‑user operating systems, running multi‑user ORACLE.

A distributed database system requires that predefined pathways between participating databases are set up. These links are called database links (DBLINKS). The client is responsible for executing the application. It sends SQL statements through the network to the server. The server parses and executes the SQL statements in order to send data and status values back to client.

ORACLE configured with SQL*Net supports distributed queries. When multiple CPUs are connected by a network with the intention of allowing users and applications access to multiple nodes, several USA concerns arise:

•	Installation decisions (where to install which software and where to locate which data).

•	Guaranteeing data security and integrity across the network.

•	Guaranteeing good system performance on all nodes and across the network.

Rather than simple connect previously independent databases and not relocate any data, it is almost always advantageous to move or merge some data. The benefits include reduced network traffic, better response time, less data redundancy, local control over data used locally, etc.

9.	Transition Plan

9.1	Delivery and Acceptance Plan

Contains phased delivery plan method of acceptance, acceptance criteria, and acceptance test plan. Also, required and allocated resources, tasks and time frames.

9.2	Training Plan

Contains details on users to be trained and scope of training for each, training method, required sessions, new staff requirements, organization changes, and related resources, tasks, and time frames.

9.3	Data Take-On Plan

Details source of data, state of data requiring clean‑up, method for conversion, data take‑on method, complexity, related resources, and time frames. Include specific ownership of data, the state it is in and any required actions requiring clean‑up for conversion, available take‑on budget and estimates of work required. Also include how much of the available data can be taken over to the new system and in what form, timing considerations for conversion, and dates or times which should be avoided. Part of this plan can evolve into the Conversion Package that is required for user walkthroughs. For larger projects, the Data Take‑On Plan can be broken out into a separate document. Included in this document should be:

•	Extract Requirements

•	Extract Summary (Location of Data, Extract Program Name, Output File Name)

•	Extract Source Code

•	Load Procedures

•	Conversion Package

•	Cross Reference

•	Originating File Descriptions

•	Sample printout of data extract output file

Reference “The Data Conversion Strategy” of Project Management Guidelines for assistance developing conversion strategy.

Reference “Data Conversion Implementation” Section of the Transition Phase, page 6.2-1, for additional information.

9.4	Cut-Over Plan

Details conversion “window” in operational timetable, parallel running, dependencies, fall‑back options, checkpoints, tests, responsibilities and related resources tasks and time frames.

9.5	Installation Plan

Details building modifications, installation of hardware, software, network/communications, control of third parties, and related resources, tasks, and time frames.

9.6	Operational Plan

Details expected load on computers, storage requirements, and service, response and support from operations staff.

9.7	Critical Success Factors

The key factor is to produce a design appropriate to the business needs. This involves:

•	Creating template forms and reports, verifying them with user, and taking their sign‑off.

•	Identifying capabilities/limitations of Hardware, software and resource availability.

•	Making informed trade‑off decisions so that the system design integrates well with other systems and the interdependence is preserved.

•	Creating user-friendly help system and documentation.

10.	Audit/Control

10.1	Financial and Statutory Auditing

Describes the business and legal requirements of the system. Identify auditing requirements as they apply to both the system‑wide architecture and specific modules, where applicable.

Describe tests to validate that business and legal requirements have been met.

10.2	Access Security Provisions

Described by user classification or user to be applied at program, record, or field level. Identify provisions such as password allocation, system software protection, tape encoders, off‑site backup, use of safes, secure zones in buildings, security guards, etc.

10.3	Control and Security Descriptions

Described per module and add any system wide control and any additional procedures required to ensure referential integrity; for example, date/version checks on programs and files, reconciliation totals checked between programs, etc.

10.4	Error Handling Descriptions

Describes criteria for acceptance/rejection of transactions, and subsequently handling of them.

11.	BackuplRecovery Plan

Describes installation standards which need to be applied for back‑up/recovery including any special needs of the system. Fall‑back options are also provided.

11.1	Archiving Procedures

Describes archiving and retention of software and data. Provide available storage and additional requirements due to sensitivity of data and location of storage center. This section should describe the following topics:

•	Features of the ORACLE RDBMS designed for data backup i.e. redo log files, consistent/cold/offline backup, image/hot/online backup, etc.

•	Preparing for backup by taking various types of backup.

•	Role of Export Utility in a backup strategy.

•	Using the database in ARCHIVELOG and NOARCHIVELOG mode.

•	Using SQL*DBA command ARCHIVE.

11.2	Recovery Procedures

This section should describe the following topics:

•	Types of failures requiring recovery i.e. User Error, Program failure, Process failure, Instance failure, Media failure, etc.

•	Recovering from various failures by means of rolling forward with redo log, rolling backward with rollback segments, etc.

•	Using SQL*DEA command RECOVER.

•	Role of Import Utility in a recovering strategy.

12.	System Test Plan

This plan derives test conditions for data validation, missing/empty files, performance criteria, etc. and reviews against business and legal requirements.

12.1	Stand-Alone and Link Testing

Plans of standalone testing of interactive/non-interactive programs, manual procedures and other modules and link testing of dependent modules.

12.2	Performance, Integrity, Recovery and Regression Testing

Plans to ensure that system performance is up to the expectation, the business rules are not violated under any circumstance, in an event of system or media failure the data is recovered.

12.3	Environment Testing

Plans to ensure predictable results in all environments, especially those which are non‑traditional.

12.4	Limit and Exception Testing

Plans include provisions for testing boundaries and exceptions.

12.5	Integration with Existing Systems

Plans to verify the interfaces are working properly and the data is being received in the proper form.

12.6	Access Control

Plans to verify data is secure and is not accessible to unauthorized individuals or groups.

12.7	Conversion and Data Take-On

Plans to verify that the data transferred from existing systems is correct and inserted into the new system properly.

12.8	Others where required

Contains plans for any other tests deemed necessary to successfully and completely test the system to ensure all business and legal requirements are satisfied as well as physical implementation expectations.

Reference “The Testing Strategy” of Project Management Guidelines for more information on developing a test strategy.

13.	Special Considerations

13.1	Risks, Problems and Gaps

Describes those areas that should be further considered or at least brought to the attention of the appropriate party. Reference “Risk Management” section of Project Management Guidelines for assistance identifying potential risk areas.

13.2	Prototype Documentation

(See http://intraoit.pader.gov/ad/HtmlFiles/SDM/StyleGuide/RapidApplicationDevelopment.htm )

14.	Forward System Development Plan

14.1	Build and User Documentation Stage

This Section describes what is involved in the Build and User Documentation Stage, and what the scope of the project entails.

14.1.1	GANTT CHART

Used to provide a visual outline of the timeframe estimated to complete the Production Stage of the project.

14.1.2	MANPOWER REQUIREMENTS

Identifies the personnel required to support the time estimate displayed in the Gantt Chart.

14.1.3	COST ESTIMATES

Identifies the Production Stage’s estimated cost of the project.

14.1.4	EQUIPMENT ESTIMATES

Identifies the estimated cost of purchasing the equipment or leasing equipment necessary to complete the Production Stage.

14.2	Subsequent Stages

Subsequent stages describe what is involved in the remaining stages of the development life cycle, and what the scope of the project entails. This section should be updated with more accurate figures as the project progresses and more detailed information is discovered.

14.2.1	GANTT CHART

A Gantt chart is used to provide a visual outline of the time frame estimated to complete the subsequent stages.

14.2.2	Manpower Requirements

Manpower requirements identify the personnel required to support the time estimate displayed in the Gantt Chart.

14.2.3	COST ESTIMATES

Cost estimates identify the estimated cost of the subsequent stages of the project.

14.2.4	Equipment Estimates

Equipment estimates identify the estimated cost of purchasing the equipment or leasing equipment necessary to complete the subsequent stages.

Attachments

(Optional)

<Insert>

Glossary

(Optional)

<Insert>

Index

(Optional)

<Insert>

Commonwealth of Pennsylvania	3.L_DD_Final

Department of Environmental Protection

Design Document

<Enter Project Name Here>

29	0/0/0000

<Location of Project "file path">

Commonwealth of Pennsylvania

lhayes

2007-02-06