TEXTUAL DATABASE

Oracle schema

The textual component of the EpiMAN database is constructed under Oracle and installed on one of the UNIX workstations. The schema of this database comprises a number of tables. The first of these is the FARMS table, which contains farm profile information such as names, addresses, livestock numbers etc. The second table is the IPS table, which contains infection details on each SP and IP, such as whether confirmed or not, infection date, diagnosis date, clinical details etc. The EPISODES table contains records of each episode or situation that places a farm at-risk of contracting FMD. There are two tables dealing with conveyors and encounters, which are used during the tracing process. A meteorological table records weather data used for simulation of wind-borne spread of FMD. A number of other tables are used for a variety of purposes, for example to hold the outputs of the on- farm simulation model. For a list of the fields in each table, please refer to Appendix 2.

Farm-based database

It was recognised early in the development of EpiMAN that to extend the power of the system to enable the assessment of risk factors and the prediction of disease spread required that the database contain information not just on infected or directly exposed properties, but on all livestock units within the affected zones. To facilitate this, MAF Quality Management is in the process of setting up a national agricultural index system (Agribase) that will contain farm profile information on every commercial farm in the country.

Each farm will have a unique farm identification number, the FarmID. This is based on a 2- character code for the territorial local authority (district) followed by 4 digits. The profile information will include name, address, telephone number of the person in charge of the animals, the type of farm and numbers of the principal farm species, and geographical information including an x,y location in New Zealand map grid coordinates, the list of valuation roll numbers, and the list of legal descriptions describing the parcels of land.

Agribase is being implemented in Oracle, and will reside on UNIX servers at various MAF locations throughout the country. Access to Agribase will be through terminals connected to MAF's computer network (MAFNet). At the start of an epidemic, a copy of Agribase records for the part of the country where the outbreak has occurred will be downloaded to the EpiMAN system. It is this

demographic data that will form the basis of the analytical and predictive capabilities of the decision support system (DSS).

Agribase will have an annual maintenance cycle. The fact that it will not be 100% up-to-date should not detract significantly from its ability to be used in an analytical and predictive capacity. Farm types do not change particularly rapidly, and both change of ownership and especially updates to the register of the numbers of animals can be made on the record of at-risk farms as they are investigated. Thus records will be completely up to date for all important farms and adequate for the rest of the farm population. The FarmID is the key field throughout the EpiMAN system, and provides the main link between the GIS and the DBMS.

Standard reports

The use of a relational DBMS such as Oracle, which uses the Structured Query Language (SQL), means that a tremendous variety of ad hoc reports can be easily produced. For routine reports, whose contents can be pre-defined, sophisticated reports can be designed, which can be generated by selection from a menu. Several of these reports have been designed to aid the members of the various EHQ sections in their tasks. Some of them are in fact activity forms, used to collect data during the control endeavours (such as the Patrol Form), or to deliver certain control directives, such as the C&D Order (see Appendix 3). Following are a list of the major reports:

1. Tracing Group

• Tracing Form;

• Encounter Form;

• Summary statistics showing time from diagnosis of farm to completion of trace, by risk rating;

• Numbers of uncompleted traces by risk rating. 2. Disease Investigation Group

• Patrol Form;

• List of all at-risk farms requiring visits on given dates; • List of outstanding at-risk farms still requiring investigation;

• C&D Order;

• Movement Permit;

• List of outstanding vehicles still requiring C&D;

• Free movement list - on-screen view plus ability to print out; • Ability to check whether farm is on standstill or not.

4. IP Support Group

• IP and PESP notification form;

• List of IPs showing stages of depopulation and C&D;

• List of times from diagnosis to slaughter to disposal of carcases to C&D complete - statistics - means/standard deviations/ranges.

5. EHQ Controller

• List of IPs by date and decontamination stage; • Numbers of current and projected at-risk farms; • Free-movement list - input form.