Federal Aviation Administration (FAA): Database Development & Administration
The FAA Office of Aviation Medicine (AAM) needed to develop a Compliance and Enforcement Tracking System (CETS) to track and report on drug and alcohol testing, inspections, investigations, and voluntary disclosures among some 7,200 companies within the aviation community.
The CETS application needed to accommodate several conditions: extract and preserve data from the legacy database; pull data from other FAA databases on different platforms; integrate the new CETS application with a COTS-based document management solution; service an inspection workflow comprising inspectors and remote and local users gathering data from 7,200 sites across the United States.
First, NDi gathered requirements and analysis -- a critical step in the CETS software development process. This insight into the existing workflow and the ultimate needs of the end-user provided a blueprint for the final design of a working prototype. Given the nature of the data and the inspection workflow processes that needed to be captured, we very thoroughly reviewed every aspect of the requirement phase.
We analyzed the existing inspection workflow to determine what information to be carried over to the new system, to find out which data fields were no longer necessary, and to decide what code could be reused. Our analysis also included gathering and understanding the following: Web-workflow process; Business rules; Downloadable template requirements; Reporting requirements; Integration requirements; Security requirements; Document management requirements; External FAA system database requirements.
Our experts produced a final requirements document that contained all of the information listed above and a clear road map to produce a database design that meets the needs of end users and fulfills AAM strategic goals.
NDi proposed a 3-Tier Architecture System Application Design that satisfied the requirements of a large-scale intranet/laptop/server application, delivering an enterprise-class application for the FAA AAM. The CETS 3-Tier Architecture provides the physical partitioning of the application:
The Graphical User Interface Tier: Provided the client with a seamless interface with the system through the use of fields, drop-down menus, and pre-populated information
The Middle Tier: Implemented as a collection of components that were used in a variety of client-initiated business processes; Provided the ability to call on other components to help implement a request; Provided the method to send client requests using object name to server to obtain information from the database; Provided components to act as gateways with other mainframe databases; Provided the ability to incorporate Custom-Off-the-Shelf (COTS) applications; Provided the ability to add a new component and or new component capabilities without interrupting the client
The Server Tier: Provides storage where the database is maintained; Provides added security by not exposing the database schema to the client; Provides added security by ensuring authorized-only access to the server; Provides the ability to update, add to, and delete without interrupting the client
The 3-Tier Architecture allows the FAA CETS System Administration to be less complex. CETS can be centrally managed on the server using standard system management tools. The availability of the application is constant. The tiers can be restarted on another server in case of resident server failure. The end result is a secure, robust system, allowing the application to increase in scale without a complete recode.
The CETS design included the following documentation: integration configuration with other FAA systems; Database structuring/modeling; Integrated security system plan; System objects; Workflow and dataflow; Layout and design of reports; Mock-up, including WEB GUI and application flow; Overall system architecture.
Test Installation, Functional Testing, and Training Documentation
NDi produced a series of CETS testing and training documents to ensure that the CETS application met the expectations of the FAA AAM. We produced a comprehensive Test Plan to guide both our developers and FAA users through the application testing process. The process included a uniform testing procedure, the ability to capture the testing results, and the process to be carried out during lab testing and debugging.
Our experts created a document that explains how to install the CETS application on a server and individual computers to ensure a uniform and successful installation. We performed a full production testing of the CETS Application in a Lab environment. Using the CETS Test Plan, the CETS Application was tested by both CETS end-users and our developers in the real-world environment. Based on the Test Plan, we produced training materials in support of User Training.
CETS System Documentation / Implementation Plan to Production Environment
NDi produced all necessary documentation of the CETS system. The documentation included a Data Dictionary (database descriptions), coding script (with comments, i.e. history of changes), source code, test cases, installation script, updated component design and disaster recovery plan. The final document consolidated information from these specified documents along with additional operational and maintenance guidelines in the form of an Operations and Maintenance Plan to be used for on-site maintenance purposes.
We produced the CETS User Manual for end-users to successfully use all aspects of the CETS Application. After the final review of the User and User Training Manuals, we updated both manuals, as necessary, and produced final documents. Our staff traveled to Oklahoma City where the production server is located to perform deployment activities involved in the initial transition of system to production environment. Also, we provided on-site User Training at four sites: Atlanta, GA; Los Angeles, CA; Fort Worth, TX, and FAA Headquarters in Washington, DC.
Final Data Transfer and Deployment of New System
Data from the old CETS version (2.0) was successfully migrated into the new version (3.0). After creating and employing a utility to facilitate and ensure a comprehensive and secure transfer of data, we successfully brought CETS 3.0 online as the new Compliance and Enforcement Tracking System.
CETS has reduced the quarterly scheduling of inspections from a matter of weeks to less than an hour. Inspectors can now enter all enforcement activities data while in the field and upload that information to the FAA server upon return to the office.