Laserfiche WebLink
Diversion Records <br />Each incoming data set may contain much more than the actual data that are to refresh the database. <br />There may be substantial history data interwoven with the expected update year data. The primary <br />goal of this verification exercise is to end up with data files that contain only data elements that can <br />be applied against the database tables. <br />The incoming diversion/structure ?refresh? data set will be loaded into a temporary database table. If <br />a secondary ?verify? data set is present (applicable for State-supplied data), it will also be loaded into <br />a temporary database table. <br />The data administration staff will have prepared a set of SQL scripts that use the various rules and <br />constraints of the CRDSS database. The process of verifying the incoming data consists of running <br />these stored SQL scripts within the INFORMIX DB-Access environment against the contents of each <br />temporary table. It is important to note that the scripts will be structured to minimize the number of <br />records returned to the State for review, while at the same time ensuring the integrity of the CRDSS <br />database. <br />As the result of this script activity, the table containing the ?refresh? data will be partitioned in up to <br />four output data subsets (also temporary tables within the database): <br />Subset 1-New diversion records that can be inserted into the database. <br />These records would be for existing structures for dates (year/month) that are <br />not in the database. Generally, this data would be for the expected update <br />year, but could also be data to fill existing gaps in the historical range of data. <br />Subset 2-Change diversion records that may or may not be applied against previously <br />recorded production data. <br />These records would be for existing structures for dates that correspond to <br />rows already present in the production database. The contents of this table <br />will be compared to the contents of the accompanying ?verify? table to see if <br />these changes are valid. Two more data subset tables will be produced from <br />this matching exercise. The subset table that contains the matching records <br />can be applied against the database. The table that contains the non-matching <br />records will be exported to an ASCII data file and set aside for review by the <br />State?s management team. <br />Subset 3-Change diversion records that fail a dependency test. <br />These records would contain foreign keys that do not exist elsewhere in the <br />database but that specifically reference either the structure or the geoloc table. <br />To ensure the integrity of the production database, a diversion record must <br />associate to an existing structure and geoloc; therefore, this record should be <br />set aside for review by the State?s management team and for checking the <br />record counts. <br />6 <br />a320/taskmems/ 2-13-01.doc 01/03/97 <br />