ICDB User's Guide

In 1988 Department of Agriculture insect collection curator Kevin Richards and entomologist Rob Emery saw a need to database the insect reference collection. As there was nothing available for the PC at the time Rob and Kevin built a database specifically for entomologists and to add features as required. The database management system used initially was Borland® Paradox. By his retirement in 1992, Kevin Richards had databased some 13,000 specimens.

In 1993, Rob Emery created an entirely new database in Microsoft® Access 2.0 with the help of a professional database engineer, Jon Wardrop. Rob decided to convert from Paradox to Access given the growth and expected domination of Microsoft products. Also the cost per Access licence (around $300) was minimal compared with more "industrial" databases like Oracle®.

To ensure compatibility with the Australian National Insect Collection database, the new database structure was based on the ANIC data dictionary provided by Tracy Harwood.

ICDb has been optimised for data input because of the requirement to database 340,000 existing specimens. To this end we have used drop-down list boxes extensively so that only a few characters are needed to obtain a match and to minimise typographical errors (Figure 1). This has allowed a single person to input up to 600 specimen details in one day although 2-300 is a more typical figure. ICDb is shared over the Agency network so more than one person can enter data at a time. As of July, 1999, over 85,000 specimens (25% of the collection) had been databased, mostly by contract staff Natarsha Zilm, Romolo Tassone and volunteers.

Context sensitive help was added in 1995 using Helpmaker Pro along with built-in full and incremental backups strategies. ICDb will prompt for a backup to be run when the database is closed on Fridays.

In 1996 ICDb was converted to Microsoft Access 97 and the Australian Land Information Group gazetteer of Australian placenames purchased and linked to the database. This provided automatic lookups of digital latitude and longitude data for Australian placenames as soon as the name is completed. ICDb replaces the placename with Auslig's so that consistent spelling of nearest named place is achieved. ICDb also includes a spherical navigation calculation to correct lat/longs based on an offset in either kilometres or miles. AGWEST are now licensed as Value Added Resellers for the Auslig database so we can now distribute it along with ICDb provided we ensure that their data is secure and only accessible via our database.

All new species (not specimen) details are held in a separate table along with bibliographic information (unlimited synonyms), common names (unlimited), and image files. The common names information became so extensive that we decided to pre-populate the tables with common names taken from a range of national and international sources. ICDb currently holds 4,000 common names and over 10,000 individual species along with 800 images. ICDb holds only the filename of images; pictures are linked as required. The images themselves are stored as JPG files outside the database to keep its size to a minimum (ICDb is currently around 28 megabytes).

A loans database has also been incorporated which links to a Microsoft Word document template generating loan issue and overdue loan form letters populated with address and specimen information from the database. ICDb checks for overdue loans every time it is opened and will prompt if loans are more than 3 months late.

ICDb has a built in report that will print specimen labels in a 5 point font for all recently entered specimens. We use a laser printer and acid-free paper guaranteed to last 300 years! It will also export collection locality information along with lat/long data in a format that can be used by mapping programs.

When it was time to provide query interfaces for ICDb, we decided that there was little point in building software dependent query forms in Access given widespread use of World Wide Web browsers not only for only for the Internet but stand-alone programs as well. All ICDb queries run in version three and above web browsers. The query forms were built using Microsoft Frontpage and connected to the ICDb Access database using Microsoft Internet Database Connector. The master ICDb database is uploaded to the Agency web server daily along with any new image files. The ICDb web pages can be found at http://www.agric.wa.gov.au:7000/ento/icdb/icdb1.idc

The Internet Database Connector approach for web page development meant that no specialised programming ability was required and web pages could be developed and adapted by entomologists for entomologists. All queries use drop-down list boxes for setting criteria and refining searches, for example choosing Coleoptera from a drop-down box and refining will return a family drop-down box populated with only Coleoptera families (Figure 2). Specific records can be prohibited from display by unchecking a box in ICDb if the data is considered sensitive.

The same approach is used for view ICDb images except that the drop-down boxes are only populated with entries for which there are images. The links to images are not limited to pictures but can include sound files, videos etc. A random image web page is also available at the ICDb home page with images selected from the database using a random number with time as the seed.

As our scientific and common name lists expanded we built a web page to look up scientific name given the common name and vice versa. This provides a useful service to students and amateur entomologists.

Even the acknowledgments page is database driven, viewing this web page runs an ICDb query which lists contributors, their background and a count of the number of specimens entered by them and the families to which they (the specimens) belong. This encourages volunteers by giving them some international exposure.

ICDb is currently in use at WA Museum, DPI Tasmania, AGWEST Plant Pathology. Copies are under evaluation at DPI Queensland, WA Department of Conservation and Land Management and museums in Belgium, South Africa and New Zealand.

We are currently working on a number of databases that use elements of ICDb.

Pest Host Database - holds taxonomic and distribution details for agricultural pests; taxonomic detail for agricultural hosts and a table which links the pests and hosts. This database has over 600 pests (mostly bruchids), 600 hosts and about 1,600 pest/host interactions. It can be found at http://www.agric.wa.gov.au:7000/ento/pesthost/

Pest Threat Database - asks the user 15 questions about agricultural pests. The questions and responses are weighted to give a ranked semi-quantitative risk assessment for exotic pests. The database averages the score if more that one expert responds to a particular pest. http://www.agric.wa.gov.au:7000/ento/threat.htm)

A Pest Identification Database - holds name, description, life cycle, damage and control information as well as images for over 100 pests. The query forms run over the web and allow the user to describe the insect using a graphical interface or search for a particular insect by name. Matching bugs are returned to a selection page to allow the user to find their insect and request more detail. This approach allows experts to spend a few minutes entering details into the database, Farmnote-like web pages are generated each time the user provides selection criteria.
http://www.agric.wa.gov.au:7000/ento/pestweb/default.idc

Grain Insect Resistance Database - for some time FAO have been recommending that resistance researchers develop national databases of resistance. AGWEST are the first to accept the challenge and have developed the Australian Grain Insect Resistance Database. This database is accessible by collaborators over the Internet or by stand-alone distributed replica databases. AGIRD currently holds data from AGWEST, NSW Ag. and QDPI for over 15,000 assays on 11,000 strains from 4,500 sites. http://www.agric.wa.gov.au:7000/ento/agird1.htm.

Authors: Natarsha Zilm, Rob Emery & Romolo Tassone