CONTENTS Title Page Copyright Page Preface 1 Introduction to the AVERTZ Sample 2 Functional Requirements Analysis 2.1 Collecting Functional Requirements 2.1.1 Site Management 2.1.2 Reservation Processing 2.1.3 Car Management 2.1.4 Customer Accounts 2.1.5 Determining Future Business Needs 2.2 Analyzing Functional Requirements 2.2.1 Identifying the Tasks Performed by Each Business Function 2.2.2 Identifying Entities 2.2.3 Identifying the Attributes of Each Entity 2.2.4 Identifying Relationships Among the Entities 2.3 Continuing the Design Process Part I Database Design and Development 3 Analyzing Data 3.1 Collecting Business Documents 3.2 Establishing Precise and Consistent Rules for Data Names 3.3 Ensuring that Identified Data Items Meet Future Requirements 3.4 Revising the Entity-Attribute List 4 Data Normalization 4.1 Selecting a Primary Key for Each Entity 4.2 Eliminating Repeating Attributes (First Normal Form) 4.3 Eliminating Partial Dependencies (Second Normal Form) 4.4 Eliminating Transitive Dependencies (Third Normal Form) 4.5 Revising the Entity-Relationship Map 5 Using Rdb/VMS to Assist Data Analysis 5.1 Understanding the Relational Model 5.2 Prototyping a Logical Design as an Rdb/VMS Database 5.3 Querying Rdb/VMS System Tables 5.4 Revising the Prototype Using System Table Information: A Scenario 5.4.1 Looking for Multiple Occurrences of Non-Key Attributes 5.4.2 Analyzing the Key Attributes 5.4.3 Examining the Relationships Between Entities 5.4.4 Summary of Using the LINK_BY_ * Displays to Analyze Relationships 5.4.5 Changing the Prototype Database 5.4.6 Querying the Revised Prototype Database 5.4.7 Summary: Querying the System Tables 5.5 Handling Special Types of Relationships 5.5.1 Many-to-Many Relationships 5.5.2 Multiple Relationships Between Two Entities 5.5.3 Reflexive Relationships 6 Analyzing Transactions to Check Database Design 6.1 Tracing Transaction Paths Through the Logical Model 6.1.1 Transaction Path: Entering a Reservation 6.1.2 Transaction Path: Checking Out a Car 6.1.3 Transaction Path: Checking In a Car 6.2 Prototyping a Transaction to Verify a Transaction Map 6.3 Consolidating the Transaction Maps 6.4 Incorporating Requests for Database Changes 6.5 Archiving Considerations 6.5.1 Remove Archiving Operations from Time-Critical Transactions 6.5.2 Classify Records According to Rate of Growth 6.5.3 Preserve Context Information in the Archive 6.5.4 Archive Records to Support Later Use 6.5.5 Tracing the Paths of the Archiving Transactions 6.6 Developing a Volume Table 7 Implementing a Database 7.1 Selecting a Database Management System 7.1.1 Stability and Complexity of Data Structures 7.1.2 Structure of the Transactions 7.1.3 Skills of Database Designer and Administrator 7.1.4 Skills of Programming Staff 7.1.5 Decision Support Considerations 7.1.6 Plans for Remote Access and Data Distribution 7.1.7 Why AVERTZ Chose VAX Rdb/VMS 7.2 Logical Design and Database Implementation 7.3 Converting the Logical Design to a Production Rdb/VMS Database 7.3.1 Defining Data Item Characteristics 7.3.1.1 Cost of Changing Data Types and Sizes After Implementation 7.3.1.2 Data Type and Size Decisions for Key Columns 7.3.2 Defining Constraints 7.3.2.1 Types of Constraints 7.3.2.2 Constraint Enforcement Decisions 7.3.3 Ensuring Acceptable Performance 7.3.3.1 Setting Performance Priorities for the Implementation 7.3.3.2 Changing the Logical Model to Improve Performance 7.3.3.3 Defining Indexes 7.3.3.4 Defining Storage Areas and Maps 7.3.3.5 Specifying Use of System Resources and Other Schema Characteristics 7.3.4 Protecting Data from Unauthorized Access 7.3.5 Working with an Rdb/VMS Database 8 Maintaining and Tuning a Database 8.1 Identifying Database Administration Requirements 8.2 Maintaining the Database 8.2.1 The AVERTZ Strategy for Database Maintenance 8.2.2 Using Volume Shadowing to Enable Continuous Disk Availability 8.3 Tuning the Database 8.3.1 Creating a Test Database 8.3.2 Monitoring the Database 8.4 Troubleshooting Database Problems 8.4.1 Solving Problems Related to Application Design 8.4.1.1 Testing Whether Indexes Are Used by DML Statements 8.4.1.2 Making Sure That Terminal I/O Does Not Prolong Database Transactions 8.4.2 Solving Problems Related to Disk I/O 8.4.2.1 Too Many User-Defined Files on the Same Disk 8.4.2.2 Too Many Tables or Indexes in the Same Storage Area 8.4.2.3 Too Many I/O Requests For One Table or Index 8.4.2.4 Heavy Access to Rdb/VMS Temporary Work Files 8.4.3 Solving Problems Related to Memory Consumption 8.4.4 Solving Problems Related to Locks on Database Resources 8.4.5 Solving Problems Related to Hardware Limitations 8.4.6 Summary of Troubleshooting Database Performance Problems 9 Balancing the Needs of Transaction Processing and Decision Support 9.1 Using Database Snapshots 9.2 Extracting Data 9.2.1 Storing Data Extracts 9.2.2 Designing the Query Database 9.2.3 Query Database Location 9.2.4 Implementing Query Databases with VAX Data Distributor 9.3 Replicating Data 9.4 Implementing the Archiving Strategy 9.5 Enabling Direct End User Access to Data Part II Application Design and Development 10 Designing an Application 10.1 The Application Development Cycle 10.2 Introduction to Application Design 10.2.1 The Goals of a Good Application Design 10.2.2 Understanding the Relationship Between Application Design and Database Design 10.2.3 Software Development as an Iterative Process 10.3 Analysis 10.3.1 Collecting and Analyzing the Application Requirements 10.3.2 Checking the Preliminary Application Design 10.4 Detailed Design 10.4.1 Identifying Separate Application Components 10.4.2 Identifying Program Modules 10.4.3 Choosing Which Software Products to Use 10.4.4 Final Design Considerations 10.5 Setting Up the Development Environment 10.5.1 VAX CDD/Plus Data Repository 10.5.1.1 Structure of the CDD/Plus 10.5.1.2 Creating and Managing Directories in the CDD/Plus 10.5.1.3 Dictionary Hierarchy for an Rdb/VMS Database 10.5.2 Managing Application Code with VAX DEC/CMS 10.5.2.1 Creating a CMS Library 10.5.2.2 Storing Files in a CMS Library 10.5.2.3 Which Files to Keep in CMS Libraries 10.5.2.4 Accessing CMS Libraries From Remote Nodes 10.5.3 Defining Symbols and Logical Names 11 Designing the Application Interface 11.1 A DECforms Application 11.1.1 Form Structure 11.1.2 Run-Time Processing 11.2 Form Development 11.2.1 Defining Layout Attributes 11.2.2 Defining Panel Attributes 11.2.3 Defining a Panel in the Panel Editor 11.2.3.1 Panel Editor Commands 11.2.3.2 Setting Display Attributes 11.2.3.3 Creating Fields on a Panel 11.2.4 Defining Form Data Items and Form Records 11.2.5 Exit Phase 11.3 Field Interaction and Validation 11.3.1 Field Description Entries 11.3.2 Help Messages 11.3.3 Built-in Form Data Items 11.4 Defining Responses 11.4.1 Accept Responses 11.4.2 Request Responses 11.4.3 Defining a Request 11.4.4 Defining a Request Response 11.4.5 Displaying Messages 11.5 DECforms Application Programming 12 Testing Database Access 12.1 Prototyping Database Access with the Interactive SQL Utility 12.2 Working with an Rdb/VMS Database 12.2.1 Relational Database Structure 12.2.2 Accessing the Rdb/VMS Database 12.2.3 Database Transactions 12.2.4 Manipulating Data Within the Database 13 Transaction Processing Against a Database 13.1 An ACMS Car Rental Application 13.1.1 Defining a Task 13.1.1.1 Exchange Steps 13.1.1.2 Error Handling in the Task Definition 13.1.1.3 Defining the Exchange Steps 13.1.1.4 Processing Steps 13.1.1.5 Error Handling in the Step Procedure 13.1.1.6 Defining the Processing Steps 13.1.1.7 Completing the Task Definition 13.1.1.8 Storing the Task Definition in CDD/Plus 13.1.2 Writing the DECforms Interface 13.1.3 Writing the Step Procedures 13.1.3.1 An AVERTZ Retrieval Procedure 13.1.3.2 An AVERTZ Update Procedure 13.2 Defining a Task Group 13.2.1 Writing Server Procedures 13.2.2 Using Message Files 13.3 Defining the Application Environment 13.3.1 Defining the Application 13.3.2 Defining Menus 14 Building and Testing the Application Code 14.1 Building the Application with VAX DEC/MMS 14.1.1 Using MMS Description Files 14.1.1.1 MMS Built-In Rules 14.1.1.2 MMS User-Defined Rules 14.1.2 Specifying CDD/Plus Dictionary Objects 14.1.3 Specifying CMS Library Objects 14.1.4 Techniques for Building ACMS Applications 14.2 Testing the Application with VAX DEC/Test Manager 14.2.1 Creating DEC/Test Manager Test Libraries 14.2.2 Creating a DEC/Test Manager Test Description 14.2.2.1 Setting Up the Process Environment 14.2.2.2 Setting Up a Test Database 14.2.3 Creating a Collection of Tests 14.2.4 Running a Collection of Tests 14.2.5 Reviewing Tests 14.2.5.1 Examining Unsuccessful Tests 14.2.5.2 Updating the Benchmark File 14.2.6 Making Sure You Are Testing the Entire Application 15 Application Maintenance and Tuning 15.1 Debugging Transaction Processing Applications 15.1.1 Debugging ACMS Task Groups 15.1.1.1 Building the Application for Debugging 15.1.1.2 Invoking the ACMS Task Debugger 15.1.2 Debugging ACMS Step Procedures 15.2 Tuning Transaction Processing Applications 15.2.1 Design Considerations 15.2.1.1 Modularity 15.2.1.2 Parameter Passing 15.2.1.3 Sharing Program Code 15.2.1.4 Specifying Different Servers for Different Types of Database Access 15.2.1.5 Record Locking 15.2.2 Tuning the Application Code 15.2.2.1 Monitoring the Application with VAX PCA 15.2.2.2 Analyzing VAX PCA Data 15.2.2.3 Using VAX PCA with VAX DEC/Test Manager 15.2.3 Tuning Process Quotas and System Parameters 15.3 Major Issues in ACMS Application Performance 15.3.1 Monitoring VMS and ACMS System Resources 15.3.2 Analyzing Memory Limitations 15.3.3 Analyzing CPU Limitations 15.3.4 Analyzing Disk I/O Limitations 15.4 ACMS Processes That Affect Performance 15.4.1 Configuring the Command Process 15.4.2 Configuring the Application Execution Controller 15.4.3 Configuring the Server Process 15.5 A Tuning Methodology Part III Sample Procedures and Source Files A Database Procedures and Definitions B Add Site Application Procedures and Definitions B.1 VR_ADD_SITE_FORM Form Definition B.2 VR_ADD_SITE_PROG Procedure Definition C Reservation Application Procedures and Definitions C.1 Workspace Definitions C.1.1 VR_CONTROL_WKSP Definition C.1.2 VR_CUSTOMERS_SHADOW_WKSP Definition C.1.3 VR_CUSTOMERS_WKSP Definition C.1.4 VR_CU_ARRAY_WKSP Definition C.1.5 VR_FIELDS Definition C.1.6 VR_MSG_WKSP Definition C.1.7 VR_RENTAL_CLASSES_WKSP Definition C.1.8 VR_RESERVATIONS_WKSP Definition C.1.9 VR_RE_ARRAY_WKSP Definition C.1.10 VR_SITES_WKSP Definition C.1.11 VR_SI_ARRAY_WKSP Definition C.1.12 VR_TRANS_SHADOW_WKSP Definition C.1.13 VR_TRANS_WKSP Definition C.2 Task Definitions C.2.1 VR_DISPLAY_CU_TASK Definition C.2.2 VR_DISPLAY_SITES_TASK Definition C.2.3 VR_RESERVE_CAR_TASK Definition C.3 Form Definitions C.3.1 VR_DISPLAY_CU_FORM Definition C.3.2 VR_DISPLAY_SITES_FORM Definition C.3.3 VR_RESERVE_CAR_FORM Definition C.4 Step Procedures C.4.1 VR_FIND_CU_PROC Procedure C.4.2 VR_FIND_SI_PROC Procedure C.4.3 VR_GET_CUSTOMERS_PROC Procedure C.4.4 VR_GET_RATES_PROC Procedure C.4.5 VR_GET_REGIONS_PROC Procedure C.4.6 VR_GET_SITES_PROC Procedure C.4.7 VR_MOVE_CU_PROC Procedure C.4.8 VR_MOVE_RE_PROC Procedure C.4.9 VR_MOVE_SI_PROC Procedure C.4.10 VR_STORE_CU_PROC Procedure C.4.11 VR_VERIFY_AVAILABILITY_PROC Procedure C.4.12 VR_WRITE_RS_PROC Procedure C.5 Server Procedures C.5.1 VR_INIT Procedure C.5.2 VR_TERM Procedure C.6 Task Group Definition C.7 Message File Definition C.8 Application Definition C.9 Menu Definition C.10 MMS Description File EXAMPLES 5-1 SQL Procedure to Build Prototype Database in First Normal Form 5-2 SQL Procedure to List Domains and Associated Columns 5-3 Displaying Non-Key Attributes 5-4 Displaying Key Attributes 5-5 Displaying 1-M and 1-1 Relationships 5-6 Diagram of Relationships in First Normal Form Prototype 5-7 SQL Statements to Change Definitions 5-8 Display of Relationships in the Third Normal Form Model 6-1 Prototyping the Reservation Transaction Using DATATRIEVE 6-2 Running the Prototype DATATRIEVE Procedure 9-1 View CURRENT_RESERVATIONS in VEHICLE_RENTALS Database 9-2 Extracting RESERVATIONS Data to a Corporate-Wide Query Database 9-3 An Epilogue Command Procedure Used by Data Distributor 9-4 Extracting RESERVATIONS Data to Site-Specific Query Databases 9-5 Replicating VEHICLES and VEHICLE_RENTAL_HISTORY Data at Local Sites 9-6 Summary Report on Reservations by Car Type and Site 11-1 IFDL Source File for the Add Site Layout and Panel Attributes 11-2 Preliminary IFDL for AVERTZ Site Form Data Items 11-3 IFDL for AVERTZ Form Records 11-4 Revised IFDL for AVERTZ Site Form Data Items 11-5 IFDL Source Code for Icons and Responses in MORE_SITES_PANEL 11-6 Update Program Using DECforms Request Calls 12-1 Testing DML Statements for the Reservation Task 13-1 Reservation Task Definition 13-2 Retrieval Step Procedure in COBOL 13-3 Update Step Procedure in COBOL 14-1 MMS Description File for a DECforms Application 14-2 MMS Description File for the Car Rental Application 14-3 DEC/Test Manager Prologue File for the Car Rental Application 14-4 DEC/Test Manager Epilogue File for the Car Rental Application 15-1 Defining Two Servers in a Task Group A-1 KEY_NAMES.SQL A-2 NON_KEY_NAMES.SQL A-3 LINK_BY_ATTRIBUTE.RDO A-4 LINK_BY_DOMAIN.RDO A-5 VR_SCHEMA.SQL A-6 VR_INDEXES.SQL B-1 VR_ADD_SITE_FORM.IFDL B-2 VR_ADD_SITE_PROC.SCO C-1 VR_CONTROL_WKSP.CDO C-2 VR_CUSTOMERS_SHADOW_WKSP.CDO C-3 VR_CUSTOMERS_WKSP.CDO C-4 VR_CU_ARRAY_WKSP.CDO C-5 VR_FIELDS.CDO C-6 VR_MSG_WKSP.CDO C-7 VR_RENTAL_CLASSES.CDO C-8 VR_RESERVATIONS_WKSP.CDO C-9 VR_RE_ARRAY_WKSP.CDO C-10 VR_SITES_WKSP.CDO C-11 VR_SI_ARRAY_WKSP.CDO C-12 VR_TRANS_SHADOW_WKSP.CDO C-13 VR_TRANS_WKSP.CDO C-14 VR_DISPLAY_CU_TASK.TDF C-15 VR_DISPLAY_SITES_TASK.TDF C-16 VR_RESERVE_CAR_TASK.TDF C-17 VR_DISPLAY_CU_FORM.IFDL C-18 VR_DISPLAY_SITES_FORM.IFDL C-19 VR_RESERVE_CAR_FORM.IFDL C-20 VR_FIND_CU_PROC.SCO C-21 VR_FIND_SI_PROC.SCO C-22 VR_GET_CUSTOMERS_PROC.SCO C-23 VR_GET_RATES_PROC.SCO C-24 VR_GET_REGIONS_PROC.SCO C-25 VR_GET_SITES_PROC.SCO C-26 VR_MOVE_CU_PROC.SCO C-27 VR_MOVE_RE_PROC.COB C-28 VR_MOVE_SI_PROC.COB C-29 VR_STORE_CU_PROC.SCO C-30 VR_VERIFY_AVAILABILITY_PROC.SCO C-31 VR_WRITE_RS_PROC.SCO C-32 VR_INIT.SCO C-33 VR_TERM.SCO C-34 VR_TASK_GROUP.GDF C-35 VRMSG.MSG C-36 AVERTZ_VR_APPLICATION.ADF C-37 ACMS_SAMPLE_MENU.MDF C-38 RESERVATION.MMS FIGURES 2-1 Preliminary Entity-Relationship Map (Car Rental) 3-1 Reservation Form: A Sample Document 4-1 Primary Keys and Matching Foreign Keys 4-2 Car Rental and Mileage Log with a Repeating Group 4-3 Entity-Relationship Map (Car Rental) 5-1 Rows in an Rdb/VMS Table 5-2 A Circular Relationship 5-3 Diagramming a Many-to-Many Relationship 5-4 Creating a Junction Entity 5-5 Diagramming a Reflexive Relationship 6-1 Path of the Reservation Transaction 6-2 Path of the Checkout Transaction 6-3 Path of the Checkin Transaction 6-4 Consolidated Transaction Map 6-5 Consolidated Transaction Map (Revision) 9-1 Central Node with Production and Query Databases 9-2 Distributing Query Data Across a Network 9-3 End User Access to Reservation Data Using DECdecision 10-1 The Car Rental System Divided into Application Groups 10-2 The Complete Reservation Application Design 10-3 Sample CDD/Plus Directory Hierarchy 10-4 CDD/Plus Hierarchy of VEHICLE_RENTALS Definitions Created by Rdb/VMS 11-1 Run-time Data Movement in the Add Site Application 11-2 Screen Image of ADD_SITE_PANEL 11-3 Screen Image of ADD_SITE_STATES_HELP 11-4 Screen Image of MORE_SITES_PANEL 11-5 Default Layout for a New Form 11-6 Main Menu Panel 11-7 Create Layout Panel 11-8 Layout for Adding New Sites 11-9 Panel Attributes for ADD_SITE_PANEL 11-10 Panel Attributes for ADD_SITE_STATES_HELP 11-11 Panel Attributes for MORE_SITES_PANEL 11-12 Panel Editor Status Line 11-13 Set Display Attributes Menu 11-14 Font Size Menu Window 11-15 Create Field Menu 11-16 Choose Data Type Menu 11-17 Fields on ADD_SITE_PANEL 11-18 Icons on MORE_SITES_PANEL 12-1 Illustration of the Car Rental Database 13-1 Car Rental Application Menu 13-2 DECforms Panel That Collects Site Information 13-3 DECforms Panel That Displays Sites Within a Region 13-4 DECforms Panel that Collects Reservation Information 14-1 MMS Dependencies in an ACMS Application 15-1 Grouping Modules in a Single ACMS Processing Step TABLES 2-1 Task-Step Model for the Car Rental Application 2-2 Car Rental Business Functions and Entities 2-3 Car Rental Entities and Attributes from Functional Requirements 3-1 Value Types for Data Names 3-2 Revised Entities, Attributes, and Relationships After Data Collection 4-1 First Normal Form: Move Repeating Group From VEHICLES to VEHICLE_RENTAL_HISTORY 4-2 Third Normal Form Database Model 5-1 Using System Relation Information 6-1 Volume Table for the VEHICLE_RENTALS Database 7-1 Storage Area Parameters 7-2 Schema Options 8-1 Troubleshooting an Rdb/VMS Database 10-1 Task Step Model for the Reservation Processing Business Function 10-2 Third Normal Form for the Car Rental Database 11-1 DECforms Request Calls and Default Responses C-1 Car Rental Task Sources