Technology Essays - Database, Pointer, Extensible Storage Engine

Technology Background The Technology Needs Survey (TNS) software program developed at HSC/XRE was originally intended to provide a convenient vehicle by which the office's engineers and scientists could enter and edit environmental, safety and occupational health (ESOH) needs data into a database. The program provided an interface that allowed the user to answer, or revise answers, to questions regarding the nature of the ESOH technology needs of the customer. The database was originally installed on a local area network (LAN) shared by the technical members of the HSC/XRE office. The data in the database was used, in part, to rank the severity, impact and importance of technology needs throughout the Air Force. HSC/XRE performed substantial analysis on the data prior to its presentation to the ESOH TPIPT, Air Staff and others. Consequently, the database itself became a dumping ground for temporary tables, queries and reports that were generated "on the fly" over time. The structure of the underlying database is simple, as shown in Figure 1. The numerous queries, reports and tables that are antidotal artifacts in the database are distractions to the underlying structure, and should be removed. Creation of the Tri-Service TNS Database for FY97 The source data for the FY97 Tri-Service TNS database came from four sources: ? US Navy, by way of four MS WORD documents (segmented by pillar) ? US Army, by way of a TNS database that had been exported from a version of the software modified by the US Army and/or their contractors ? USAF FY96 TNS database ? last year's USAF database, with needs updated as required ? USAF FY97 TNS database ? current year's new USAF technology, policy and training needs The tasking from the HSC/XRE office was to consolidate the four data sources into a single database and provide it to representatives of the Joint Engineering Management Panel (JEMP) on or before 31 Dec, 1996. Such a database would be known as the Tri-Service TNS Database for FY97. Technical Issues The USAF FY97 database was considered the target into which the other three data sets were to be consolidated. At this point in time (Dec, 1996), the FY97 database still contained "T-numbers", rather than Tag integers. It appeared that there were printing difficulties with T-numbers in the Tag field, so they were removed, and added as a prefix to the need Title. In their place, sequential integers, beginning with 3000, were placed in the Tag field. Next, the Navy needs, which consisted of 807 needs in four word documents, were manually added to the FY97 database using the TNS software. This was a two man-day effort by a program support individual. All attempts to successfully print all US Army needs failed. Most needs contained a data value that exceeded TNS's a single print page. A bug in TNS causes the first page to be printed OK; then, subsequent lines are printed, one per page. It was decided that the US Army database would be provided to the government as-is, with a suggestion that the government obtain the US Army's TNS version to see if the bug had been fixed by the Army. The final step was to export the USAF FY96 TNS database, and import it into the FY97 database. This presented something called the "Match Table Problem." The Match table in TNS contains three columns ? the need number, a category, and a pointer. Depending upon the category, the category would represent a unique primary POC, technical POC, potential user, regulation or contaminant. These pointers are not uniquely generated; therefore, a primary POC with a pointer of 4254 might point to Smith in the FY96 database, while a primary POC with a pointer of 4254 might point to Jones in the FY97 database. The same corruption was possible for regulations and contaminants, as well. To resolve this problem, it was determined that the set of pointers in the FY97 database did not exceed 5000 for POC, regulations and contaminants. Consequently, the pointers in the Match table of the FY96 database were incremented by 5000, as were their corresponding targets in the POC, Regulation and Contamination tables. This assured that there would be no overlap between the two databases. The FY96 database was imported successfully into the FY97 database.