Performance Measurement Tool Box and Reporting System for Research Programs and Projects (2008)

Below is the uncorrected machine-read text of this chapter, intended to provide our own search engines and external engines with highly rich, chapter-representative searchable text of each book. Because it is UNCORRECTED material, please consider the following text as a useful but insufficient proxy for the authoritative book pages.

B- 1 APPENDIX B - Systems Requirements Document Excerpts INTRODUCTION The Texas Transportation Institute (TTI) has developed systems requirements for the Research Performance Measures (RPM) System to be provided under NCHRP Project 20-63. The RPM System is comprised of a CD-ROM, referred to herein as RPM-Tools, and a web site with a backend database, referred to herein as RPM-Web. The RPM System is designed to guide and assist research program managers in evaluating the performance of their research programs and individual projects. RPM-Tools is a standalone piece to assist both research program managers and contractors in understanding research performance measurement, selecting performance measures, and in calculating benefits from individual research products. RPM-Web also offers benefit calculation capability, plus a variety of customizable reporting options that include statewide and nationwide reports. Sponsoring agencies also have the option to delegate access to web input screens to their research contractors. A web browser is the only piece of software required. The RPM database on the web site will be populated with information from the RiP database on a routine basis. Any needed information that cannot be downloaded from RiP will require manual entry by the state agencies or their contractors. This can be accomplished by either manually entering the information using the web site or, in the case of project/product information, the upload from the RPM-Tools CD-ROM. Annual program budget information, state strategic objectives, goals for current fiscal year (FY) performance, and similar program information will require agency entry on an annual basis. The system includes an automated communication feature. Through the use of database triggers, the RPM System notifies sponsoring agencies via e-mail when an authorized contractor uploads new performance measurement data to the web site. The web site reporting tool can generate five standard format HTML/PDF reports plus a data file option allowing transfer of information to a user’s Excel file. The RPM reports have several customization features, including ability for the research program manager to add performance measures and results which are unique to their state. This systems requirements document describes in detail the web site, the design of the backend database, and a description of CD-ROM capabilities. Chapter 2 includes a glossary, descriptions of roles, and RPM reports available from the system. Chapter 3 describes tasks that can be accomplished through the CD-ROM. Chapter 4 describes what tasks can be accomplished through the web site, and by whom, and also lists the hierarchy of pages. Chapter 5 is comprised of the entity-relationship diagram as well as tables and field definitions for the database used by the web site.

B- 2 DEFINITIONS, REPORTS AND ROLES Glossary for Systems Requirements Document Data Entry Status Pending Review – Product or project information entered or uploaded into RPM-Web by the contracting agency and that has not yet been approved by the sponsoring agency and therefore will not yet be included in any RPM-Web report Deliverable Status Completed – A product or report that has been delivered by the contracting agency and accepted by the sponsoring agency as completing the contract requirements Deleted – The requirement for the product or report was removed from the project contract Implementing – A product that has been delivered and accepted and that will be or has been used outside of the research project which developed it Not Implementing – A product that has been delivered and accepted but that will not be implemented by the sponsoring agency Pending – A product or report which has not been entered into the system as either Completed, Implementing, Not Implementing, or Deleted. This is the default deliverable status. Project Status Closed Out – The contract end date has passed and implementation decisions have been made for all products General Codes Deliverable Types: Report Product Person Roles: Principal Investigator Project Manager RPM System Report Descriptions & Functionality Individual Project Report – The default format of this report contains all project and product information available in the database for a selected research project. See the sample report shown in Figure 4-2 – Individual Project Report. The primary purpose for this customizable report is to provide the Research Program Manager one or more report formats suitable either for internal research office use in

B- 3 research project management or to meet the information needs of individuals or organizations external to the research office. A unique feature of this report is that it includes documentation of the method of benefit calculation. A user belonging to the agency sponsoring the research project may customize the default report format by eliminating rows or columns of information. The sponsoring agency user may also add one or more unique performance measures and results concerning this project in free form text fields. The title of the report may also be customized. The Research Program Manager has the option to save this customized report configuration on RPM- Web to make it available to other system users within his or her own agency. Upon opening a saved report configuration, an authorized user obtains up to the minute information pertinent to that report. The Research Program Manager may also save a report to their computer for attaching to e-mails, preserving data reflecting that report date, or otherwise handling. Users from another agency will only be able to access the default format of the Individual Project Report. The default format of this report is the report available from RPM-Tools. Multiple Project Report – The default format of this report contains most project and product information available in the database for each research project sponsored by the requestor’s agency. See the sample report shown in Figure 4-3 – Multiple Project Report. The primary purpose for this customizable report is to provide the Research Program Manager one or more report formats suitable either for internal research office use in program management or for providing information to individuals or organizations external to the research office. An authorized user may customize this report by eliminating rows or columns of information. This report may also be customized to include only projects associated with one or more agency strategic objectives, agency-defined categories, funding type, or national standard categories from the fiscal year being reported. The title of this report may be customized. As with the Individual Project Report, the Research Program Manager may save a configuration of this report on RPM-Web, to make it available to other system users within his or her own agency, or he or she may save the specific report to their computer. State Research Program Performance Report – The default format of this report contains two tables of information describing the performance of the agency’s entire research program for the requested fiscal year. The first table includes program efficiency information; and the second table includes program effectiveness information. See the sample report shown in Figure 4-4 – State Research Program Performance Report. The primary purpose for this customizable report is to provide the Research Program Manager one or more report formats suitable for either internal research office use in monitoring program performance or for reporting program performance to agency administrators. The report for a given agency is available only to system users belonging to that agency. The prior fiscal year’s performance is displayed along with the requested fiscal year’s

B- 4 targeted and actual performance. A user may customize the default report format by eliminating rows or columns of information. The sponsoring agency user may add one or more unique performance measures and results in free form text fields. The title of the report may also be customized. The Research Program Manager has the option to save this customized report configuration on RPM-Web, to make it available to other system users within his or her own agency, or he or she may save the specific report to their computer. State Research Program Impact Report – The default format of this report provides information about an agency’s research program that is pertinent to three common and primary research program objectives: to save lives, to reduce costs of providing transportation, and to improve quality of life. See the sample report shown in Figure 4-5 – State Research Program Impact Report. The default report includes an indication of the amount of research being performed toward each objective, the outputs and outcomes which resulted, and indicators of the efficiency of the agency’s entire research program. This primary purpose for this report is to succinctly provide information needed by decision-makers responsible for requesting or approving an agency’s research program funding level. The report for a given agency is available only to system users belonging to that agency. A user may customize the default report format by eliminating rows or columns of information. The title of the report may also be customized. The Research Program Manager may save this customized report configuration on RPM-Web, to make it available to other system users within his or her own agency, or he or she may save the specific report to their computer. National Research Program Impact Report – The default format of this report provides information about the effect of the combined state research programs. See the sample report shown in Figure 4-6 – National Research Program Impact Report. Like the similar agency-level report, this report focuses on three common and primary objectives of every research program: to save lives, to reduce costs of providing transportation, and to improve quality of life. The default report includes an indication of the amount of research being performed nationally toward each objective, the outputs and outcomes which resulted, and indicators of the efficiency of the nationwide research program. The primary purpose for this report is to succinctly provide information needed by the Standing Committee on Research (SCOR) and other national decision-makers responsible for requesting or approving research program funding levels during federal transportation program reauthorization deliberations. A user may customize the default report format by eliminating rows or columns of information. The title of the report may also be customized. An authorized user may save the created report to their computer. The Research Program Manager may save a customized report configuration on RPM-Web to make it available to other system users within their agency authorized to view this report.

B- 5 Agency Comprehensive Database File – This option allows the Research Program Manager to download a file containing all research project information for a given fiscal year of their agency’s research program to an Excel file. The primary purpose for this option is to provide the Research Program Manager the capability of creating any report, chart, or graph that may be desired using database information. RPM-Web User Roles All users must “login” to the web site before being granted access to any area. The valid roles that can be granted to users are shown below. General User - this basic information access level is envisioned to be broadly granted within state transportation agencies, to FHWA, and other public and private organizations within the transportation research community SHA Administrator - primarily envisioned for SCOH Members and other state transportation agency administrators Research Program Manager - primarily envisioned for the state transportation agency RAC Member Research Project Manager - primarily envisioned for research office staff use or optionally to be granted to state transportation agency personnel located outside of the research office Principal Investigator – the lead researcher responsible for a specific project Contract Administrator – the research contract office of the Principal Investigator’s agency Webmaster – manages the database, creates new agencies, and assigns Research Program Manager system user roles PERFORMANCE MEASURES CD-ROM (RPM-Tools) The industry-standard multimedia authoring program Macromedia Director MX 2004 will be used to assemble and create the CD-ROM. This authoring tool will accommodate creation of all the features described in this document, including interactive formulas and saving data to the computer's hard drive. The CD will be a multi-platform "hybrid" CD-ROM. It will support versions of Microsoft Windows from Windows 98 to the present, and Macintosh operating systems. RPM-Tools provides three major components to the RPM System:

B- 6 PM 101: A self-paced introduction to research performance measurement, featuring an overview of the value of research performance measurement and providing the user with specific assistance in selecting appropriate performance measures. The material is targeted for those with no prior experience with research performance measurement. Benefit Calculation Tools: A set of benefit calculation tools, incorporating all of the standard performance measurement options, for determining the benefits resulting from specific research products. Approximately 25 benefit calculation examples, each with active formulas to allow users to modify the calculation to fit their specific need, are included in this section. Project/Product Data File Creation: A file of the information created during a work session can be saved for transmitting to the sponsoring agency or the data can be uploaded if the project file exists within RPM-Web. Access to the RPM-Tools CD-ROM will be unrestricted, although only Research Program Managers or others authorized within RPM-Web security may upload information from the CD-ROM to the web-based system. The CD-ROM does not include a database and will not be directly linked to the web-based system. The primary navigation bar will be anchored across the top of the screen, with section- specific secondary navigation down the side. Project identification information and benefit calculation data entered by users of the benefit calculation tools can be saved in three ways: a. Save data to the local hard drive. b. Create an individual project report for saving or printing which lists all work performed concerning the product currently being analyzed. c. Create a “data upload” file, which in turn may be either saved for transfer to the sponsoring agency or directly uploaded to the RPM web site if the user has authorized web site access for this capability. Users will be able to save a session of work for completion during a later session. Only an individual project report may be generated from the RPM-Tools CD-ROM. The format of the report is identical to that produced by the web-based component, as illustrated later in this document. The self-paced instructional section will use on-screen text, voice-over narration, and supplementary diagrams to introduce the concepts central to understanding the value of research performance measurement. A software “Wizard” approach will be used to lead users through a series of questions to aid in identifying the performance measures most applicable to specific research programs. Additionally, a resource section will provide all users with links to a variety of related web- based data sources. Use of this section will require the CD-ROM user to have an active web connection.

B- 7 PERFORMANCE MEASURES WEB SITE (RPM-Web) This chapter will discuss the roles, access, navigation, design, and functionality of the RPM web site. Roles and Access Table 4-1 describes access to reports and screens for data entry on the RPM web site. The roles are listed in the left-hand column; the types of access are indicated in the remaining columns. The Research Program Manager and the Webmaster have access to the greatest number of reports and input screens. Principal Investigators and Contract Administrators have access to fewer reports and screens. The General User has access only to Individual Project Reports. The Research Program Manager may specify one of two levels of access for Contractor employees (Principal Investigators and Contract Administrators). Level 1 access allows Contractor personnel to generate multiple project reports containing the projects they performed. Level 2 access allows the Contractor to also add/update/delete product and project data for the projects with which the Contractor is associated. Contractor access level is specified by each sponsoring agency. A Contractor that works for more than one agency could, for example, be assigned Level 1 access by one agency and Level 2 access by another. All Researchers and Contract Administrators inherit the access level provided to the Contractor for whom they work. Navigation Figure 4-1, a story board of web site navigation, shows authorized user access throughout RPM-Web navigation. Navigation within the web site will be controlled by a primary and secondary navigation. The primary navigation will be across the top which will contain the main sections of the site. The secondary navigation will be down the side and will change depending on what section the user has selected. The list below shows the main navigational elements (bold print), their purposes, and their secondary navigational elements. • Home: Welcome Message o FAQs: A helpful list of FAQs o About RPM: Description/Purpose of site o Site Map o Contact Us: Page for submitting e-mail to the webmaster • Search: A search page for finding projects o Basic Search: Keyword Search (default) o Advanced Search: Allow searches based on PI, PD, or Agency o My Agency’s Projects: A list of projects involving the user’s agency

B- 8 • Browse: A page that allows for browsing of projects based on several various categories. o My Agency’s Projects: A list of projects associated with the user’s “Home Agency” is displayed. o Browse by Organization: Display an alphabetical list of agencies. When the user selects one, a list of projects for that agency is displayed. o Browse by Topic: Display an alphabetical list of topics. When the user selects one, a list of projects associated with that topic is displayed. • Add/Update Records: A page that allows the user to add or update various types of records. o Add/Update Project Records: A new project link and a list of projects. ƒ Add/Update Product is embedded in the project screen. o Add/Update Person: A new person link and a list of people in the system. o Update Agency Record: A page that contains a list of various aspects of agency information that can be edited. o Add/Update Contractor Records: A new contractor link and a list of contractors the user has permission to update. o Upload from CD-ROM: A page that lists projects the user has permission to update. When the user selects one they are taken to a page where they can tie a file on their hard drive to a project.

Table 4-1: Roles and Capabilities of Web Site Users Access to Reports Access to Add/Update Screens Role or Access Level I n d i v i d u a l P r o j e c t R e p o r t M u l t i p l e P r o j e c t R e p o r t S t a t e R e s e a r c h P r o g r a m P e r f o r m a n c e R e p o r t S t a t e R e s e a r c h P r o g r a m I m p a c t R e p o r t N a t i o n a l R e s e a r c h P r o g r a m I m p a c t R e p o r t C o m p r e h e n s i v e D a t a b a s e F i l e ( D a t a E x p o r t ) P r o j e c t a n d P r o d u c t I n f o r m a t i o n C o n t r a c t o r I n f o r m a t i o n U p l o a d f r o m R P M - T o o l s P e o p l e I n f o r m a t i o n P r o d u c t a n d P r o j e c t C l o s e o u t A p p r o v e C o n t r a c t o r L e v e l 2 I n p u t S e l e c t P e r f o r m a n c e M e a s u r e s t o S h o w o n R e p o r t s F r e e F o r m T e x t B o x E n t r y & S a v e R e p o r t O p t i o n P r o g r a m D a t a ( F Y b u d g e t o v e r h e a d d a t a , P M T a r g e t s , e t c . ) A s s i g n R o l e s E x c e p t R e s e a r c h P r o g r a m M a n a g e r A d d A g e n c y a n d A s s i g n R e s e a r c h P r o g r a m M a n a g e r R o l e General User Nation -wide Acces s Research Program Manager Nation -wide Acces s All of Their State’s Projects Their State Their State Yes Their State’s Data Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Research Project Manager Nation -wide Acces s Their Projects Only Their State Their State Their State’s Data Yes Yes Yes Yes Yes Yes Yes SHA Administrator Nation -wide Acces s All of Their State’s Projects Their State Their State Yes Principal Investigator (Contractor is Level 1) Nation -wide Acces s Their Projects Only Principal Investigator (Contractor is Level 2) Nation -wide Acces s Their Projects Only Yes Yes Yes Contract Administrator (Contractor is Level 1) Nation -wide Acces s All of Their Organization’ s Projects Only Contract Administrator (Contractor is Level 2) Nation -wide Acces s All of Their Organization’ s Projects Only Their Organization’ s Projects Only Yes Yes Yes Webmaster Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes

Figure 4-1: Web Site Navigation Story Board

B - 11 • Reports: A page that allows the user to select and generate various reports, depending on their roles within the system. (see Table 4-1) o Individual Project Report: The user can search for projects based on any combination of National Technical Area, Title, Start Date, End Date, Project Number, Sponsor or Person. The user then can select a single project and generate this report. Report customization options are offered as described in Chapter 2, RPM Report Descriptions & Functionality. o Multiple Project Report: The user selects a FY for the report to be generated. The default is to the current FY. After selecting the FY, a search screen based on National Category, Research Committee, State Subject Area, Funding Type, and Strategic Objective is displayed. The user can further limit the projects to be included in the report by selecting one or more of these categories. Additional report customization options are offered as described in Chapter 2, RPM Report Descriptions & Functionality. o State Research Program Performance Report: The user selects a FY for the report to be generated. The current FY is the default. After selecting the FY, a search screen based on National Category, Research Committee, State Subject Area, Funding Type, and Strategic Objective is displayed where the user can further limit the projects to have information included on the requested report. Additional report customization options are offered as described in Chapter 2, RPM Report Descriptions & Functionality. o State Research Program Impact Report: The user selects a FY for the report to be generated. The default is the current FY. After selecting the FY, a search screen based on National Category, Research Committee, State Subject Area, Funding Type, and Strategic Objective is displayed where the user can further limit the projects to have information included on the requested report. Additional report customization options are offered as described in Chapter 2, RPM Report Descriptions & Functionality. o National Research Program Impact Report: The user selects a FY for the report to be generated. The default is the current FY. Report customization options are offered as described in Chapter 2, RPM Report Descriptions & Functionality. • About RPM: Description/Purpose of Site o FAQs: Helpful list of FAQs o Site Map o Contact Us: Page for submitting e-mail to the webmaster. • Logout: A page that logs the user out of the system.

B - 12 Add/Update Records Due to the complexities of the Add/Update Records section, the following is a more detailed discussion of what occurs in this section. Add/Update Project When the user clicks the “New Project” link on the “Add/Update Project” page, the system prompts the user with four check boxes before creating the project to determine if the project impacts any of the following: • Safety-crashes, injuries, fatalities, work zones, etc • Operational Costs-construction, maintenance, engineering, etc. • Environmental-recycling, air quality, etc. • Traveler Comfort-security, traffic congestion, etc. Based on the user’s response, the appropriate indicator variables in the RPM_PROJECT_SPONSOR_PM table will be set. The system then displays the Project Edit page which contains • Sponsoring agency (If user is a contractor, the system limits the selection of Sponsoring Agencies to those for agencies that have assigned Level 2 security to this contractor. If the user is not a contractor, then the sponsoring agency field will default to the user’s agency.) • Project title • Abstract • Start/end date • Contract number • RAC national categories • State specific categories • Project completed on time • Project completed in budget • Paper submitted for publication • Number of grad students • Project cost • Contractors • Assign people to roles for this project • Product link • Objectives link Based on the Sponsoring Agency selected by the user, the system will update the list of available Study Codes for this project based on the Sponsoring Agency’s study codes. If the user is a contractor the Pending Indicator check box is disabled and automatically set to “Y”. Once the contractor finishes with the record an e-mail is sent to the Project Director notifying him/her of the pending status.

B - 13 When the user clicks the Objectives link, the system allows the user to map this project to the Strategic Objectives of the Sponsoring Agency. When the user clicks the Product link, the system displays the list of Products defined for that project and a “New Product” link. If the user selects an existing Product, the system displays the Product Edit page. If the user clicks the “New Product” link, the system prompts the user with seven check boxes before creating the product to get information about the product and determine its expected impacts. • Type (Report or Product) • Does this product effect Technical aspects for the agency • Does this product effect Management aspects for the agency • Does this product effect Basic Knowledge of the agency • Is the product a Report Only • Does implementing this product contribute to State’s environmental initiatives • Does implementing this product enhance safety Based on the user’s response, the appropriate indicator variables in the RPM_PRODUCT and RPM PRODUCT SPONSOR_PM tables will be set. The system then displays the Product Edit page which contains • Implemented indicator (Y/N) • Date implemented • Implementation cost • Description • Title • Reason not implemented (If not implemented) • Completed indicator (Y/N) • Product due date • Number of crashes reduced • Number of fatalities reduced • Reduce operating costs indicator • Operating cost reduction • Enhance traveler comfort indicator (Y/N) • Traveler comfort comments • Environment comments • Pending indicator (If contractor this is set to Y until the agency set’s it to N) • Benefit formula link If the user is a contractor, the Pending Indicator check box is disabled and automatically set to “Y”. Once the contractor finishes with the record, an e-mail is sent to the Research Program Manager and Research Project Manager to notify them of a pending status. When the user selects the benefit formula link, the system displays the benefit formula calculation page. The page contains the following: • Total benefit • Benefit type

B - 14 • Begin year of benefit • Year full benefit realized • Max benefit • Number of years of max benefit • Compute total benefit link • Example calculation links When the user calculates the benefit for a quantitative performance measure, the methodology for determining the benefits is stored in the database in extensible markup language (XML). This is done so that when an Individual Project Report is generated, the report can display how the benefit calculation was done. When the user clicks the compute total benefit link, the system makes sure begin year of benefit, year full benefit realized, max benefit, and number of years of max benefit fields have been completed and then computes the total benefit based on the formula Max Benefit * ((Year Full Benefit Realized – Begin Year of Benefit)/2 + Number of Years of Max Benefit) and updates the total benefit and the appropriate field in the RPM_PRODUCT_SPONSOR_PM table based on the benefit type. The user can also put their own formula into the system and calculate the benefit their own way, which in turn will be stored in XML format, for reporting at a later date. Add/Update Person The Edit Person Page contains the following: • Last name • First name • Middle name • Prefix • Suffix • Home agency • Home contractor • Phone • E-mail • Role security The Role security shows which roles the Person has been assigned. Add/Update Contractor The Edit Contractor page contains the following • Name • Minority indicator • Contractor web site universal resource locator (URL) • Role security

B - 15 The Role security shows which roles the Contractor has been assigned. These roles can only be assigned by the research program manager or research project manager only. Add/Update Agency Records This page contains the following • Name • Agency web site URL • Year/strategic objectives matrix • Subject category link • Annual link An authorized user can add or update any agency record, with one exception. Only the Webmaster can add a new agency or update the agency name. When the user clicks the subject category link, the user is given the option to edit or delete existing categories or add new ones. When the user clicks the annual link, the system displays the Agency Annual Edit page with the following: • Fiscal Year • FY Start Date • FY End Date • Agency Cost • Overhead Cost • Number of Problem Statements Submitted • Number of Problem Statements Funded • Percent Customer Satisfaction • Number of Agency Personnel involved • Target Benefit/Cost Ratio • Target Percent Projects Implemented • Target Percent Projects Completed • Target Percent Customers Satisfied • Target Percent Projects on Time • Target Percent Projects in Budget • Target Number of Graduate Students • Target Number of Active Contractors Upload from CD-ROM Uploading from the CD-ROM toolbox is a multiple step process. The CD-ROM toolbox will produce an XML file on the user’s local computer. This is a special type of

B - 16 text file which the user can either upload to the web site, if they have access to do so, or e-mail the file to someone else in order for them to upload the file to the web site. If this is a new Project, for the upload process to proceed, the Research Program Manager must first have created the project record in the web site. This feature will help eliminate the possibility of duplicate names/projects that could otherwise be caused from loading directly from the CD-ROM. To upload the information, the user will select the correct Project from a web site list. The user then selects the file containing the output from the CD-ROM application for upload. He/she then matches products in the existing project with those from the CD-ROM upload. For products that do not match up, the user can choose to add these remaining products to the web site or ignore the remaining products. If the user’s agency has been assigned the contractor Level 2 role by the sponsoring agency, then the pending indicator is set to “Y” for all data uploaded in that project, and an e-mail is sent to the research manager notifying him/her of the uploaded data. The research project manager can then choose to accept/modify or reject the data. Technologies Used (Web Site) The technologies being used by the web site portion of the project include hosting to be provided by a web server powered by RedHat Advanced Server 3.0 running the Apache 2 web server. PHP will be utilized as the programming language and XML/XSLT will be used for displaying the web site as well as for transforming the uploaded data. DATABASE TABLES AND FIELD DEFINITONS RPM-Web stores information about programs, projects, products, and the related performance measure information in an Oracle database. RPM-Web provides access to the database, and all interactions with the database are performed through this web interface. When the RPM web site becomes available on the Internet, the RPM database will already contain information about a large number of current and past research projects extracted from RiP. Most of the RiP records include at least a title, a list of subjects, and information about the contractor and sponsor agency; many records include more details. To develop an initial list of sponsoring agency contacts, TTI will load information from the table of authorized users of the Pooled Fund system. Eventually, the RPM database will contain a centralized repository of information about performance measurement as applied to research projects and research programs nationwide. It is anticipated that when the RPM web site first becomes available for use, many records of existing studies will contain only partial information. TTI will work to add all additional information provided by organizations participating in the rpm process. As new records of studies are created through the RPM web site, a higher proportion of records will contain complete information.

B - 17 Table 5-1 presents a simplified description of the database, listing the primary entities, their attributes, and their relationships to each other. The persons and contractors are separate from the projects, making it possible to update in one place. Agency subject categories, RAC national categories, and agency annual strategic objectives can be assigned to projects so that they can be matched to user interests. Sponsor performance measure on project-level, or product-level are separate from the projects, or products making it possible for the pooled-fund projects with multiple sponsor agencies. Entity Attribute Related to Agency [RPM_AGENCY] Name, web site URL. Agency subject category, Agency annual performance measure, Agency annual strategic objectives, Sponsored-related project performance measure (Project-level, and product-level) Contractor security Project [RPM_PROJECT] Title, Abstract, Start date, End date, Contract/project number, Sponsor- unrelated project-level performance measure. Person (Project Manager, Principal Investigator), Agency (Sponsor agency), Agency annual strategic objectives, Contractor, RAC national subject category, Agency subject category, Sponsor-related project-level performance measure, Product. Product [RPM_PRODUCT] Description, Product number, Type, Product due date, Sponsor- unrelated product-level performance measure. Project, Sponsor- related product-level performance measure. Contractor [RPM_CONTRACTOR] Contractor Name, Web site URL, Minority contractor indicator. Contractor security, Project. Person [RPM_PERSON] Last name, First name, Middle name, Prefix (Dr. Mr., etc.). Suffix (Jr. III etc), Phone, e-mail address Agency, Contractor, Project, User Account RAC National Subject [RPM_SUBJECT] Subject code, Subject description. Project Agency Subject Category [RPM_AGENCY_SUBJECT_CATEGORY] Subject category, subject description Agency Project Agency Annual Strategic Objectives [RPM_AGENCY_ANNUAL_STR_OBJ] Year, Strategic objectives Agency, Project Agency Annual Performance Measure Fiscal year, fiscal year Agency

B - 18 Entity Attribute Related to [RPM_AGENCY_ANNUAL_PM] start date, fiscal year end date, agency-level performance measure data. Sponsor-Related Project-Level Performance Measure [RPM_PROJECT_SPONSOR_PM] Performance measure data Agency, Project Sponsor-Related Product-Level Performance Measure [RPM_PRODUCT_SPONSOR_PM] Performance measure data Agency, Product User Account [RPM_USER_ACCOUNT] Role assigned to the account, Contact e-mail, contact phone number Person Table 5-1: Entities, Attributes and Relationships An Entity Relationship (ER) Diagram as shown Figure 5-1 is a graphic interpretation of RPM database design describing the relationships among the tables. The definition of each table and field in the ER diagram is listed within Figure 5-2.

ER Diagram Figure 5-1: Entity Relationship Diagram

B - 20 Figure 5-2: Definitions of ER Diagram Tables and Fields

B - 21 Figure 5-2: Definitions of ER Diagram Tables and Fields (Continued)

B - 22 Figure 5-2: Definitions of ER Diagram Tables and Fields (Continued)

B - 23 Figure 5-2: Definitions of ER Diagram Tables and Fields (Continued)

B - 24 Figure 5-2: Definitions of ER Diagram Tables and Fields (Continued)

B - 25 Figure 5-2: Definitions of ER Diagram Tables and Fields (Continued)

B - 26 Figure 5-2: Definitions of ER Diagram Tables and Fields (Continued)

B - 27 Figure 5-2: Definitions of ER Diagram Tables and Fields (Continued)

B - 28 Figure 5-2: Definitions of ER Diagram Tables and Fields (Continued)

B - 29 Figure 5-2: Definitions of ER Diagram Tables and Fields (Continued)

B - 30 Figure 5-2: Definitions of ER Diagram Tables and Fields (Continued)