Laserfiche WebLink
A set of SQL scripts that use the various rules and constraints of the CRDSS database will have been <br />prepared beforehand by the data administration staff. The process of verifying the incoming data <br />consists of running these stored SQL scripts within the INFORMIX DB-Access environment against <br />the contents of each temporary table. It is important to note that the scripts will be structured to <br />minimize the number of records returned to the State for review, while simultaneously ensuring the <br />integrity of the CRDSS database. <br />As a 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 irrigated acreage records that can be inserted into the database. <br />These records would be for existing polygons for the expected update year and <br />for geolocs that did not previously have any polygon data in the database. <br />Subset 2-Change irrigated acreage records that may be applied against previously <br />recorded production data. <br />These records would be for polygons and time series data already present in <br />the production database. The key values for each record in this data set will <br />have been matched successfully to a record in the ?verify? table to confirm the <br />validity of the change. <br />Subset 3-Irrigated acreage records that fail a dependency or constraint test. <br />These records would contain foreign keys that do not exist elsewhere in the <br />databasebut that specifically reference the geoloc table and/or contain values <br />that do not satisfy the requirements for the specific columns. To ensure the <br />integrity of the production database, an irrigated acreage record must be <br />associated with an existing geoloc; therefore, this record should be set aside <br />for review by the State?s management team and for checking the record <br />counts. <br />Subset 4-Change irrigated acreage records that are not present in the ?verify? file. <br />The records in this table would be intended to change existing irrigated <br />acreage data in the database. However, there would be no matching record <br />present in the ?verify? file to confirm this change record. This table would be <br />exported to an ASCII data file and set aside for review by the State?s <br />management team. <br />Preparations for the Database Refresh Process <br />There are approximately 60 tables in the CRDSS production database. Fifty-two of these tables are <br />potentially eligible to be refreshed at some point(s) during the database life cycle with new data sets. <br />The remaining eight tables are derived from other tables and would only need to be refreshed after <br />the related tables are involved in a refresh. <br />9 <br />a320/taskmems/ 2-13-01.doc 01/03/97 <br />