A web interface for the beef CRC database.

Abstract

Proc. Assoc. Advmt. Anim. Breed. Genet. Vol 14 A WEB INTERFACE FOR THE BEEF CRC DATABASE A. Reverter1, E. Farrell1 and C. Hutchinson 2 The Cooperative Research Centre for the Cattle and Beef Quality 1 Animal Genetics and Breeding Unit University of New England, Armidale, NSW 2351 2 Hutchinson Software, PO Box U302, Armidale, NSW, 2351 SUMMARY In order to facilitate the tasks of monitoring and retrieving data from the Cooperative Research Centre for the Cattle and Beef Quality, a web interface system has been developed. This presentation illustrates the peculiars of the database including data size and data structure as well as the functionality of the web interface system such as data inquiries, data retrievals, data annotations and data management. Keywords: Beef cattle, Internet, MySQL. INTRODUCTION Up until June 2000, The Cooperative Research Centre for the Cattle and Beef Quality (Beef CRC) database, containing approximately 250 Megabytes of data on 9,677 steers and heifers, resided in two independent PROGRESS databases, one for purebred and one for crossbred individuals. Users login to access the host computer and run PROGRESS scripts. These databases as implemented provided tools to access data in two forms, as inquiries and as retrievals of data subsets, together with a simple data annotation feature and data dictionary access. This system, although functional, was limited by user-interface constraints of the database engine, by network traffic constraints causing erratic response times, and by the performance of the host computer. The decision was made to join both databases in one, to transport it to the AGBU facilities and to take advantage of developments in web-based, secure, database search and access technologies by implementing a dynamic web interface system for the Beef CRC database. The objective of this vignette is to describe the peculiars of the database including data size and data structure as well as the functionality of the new web interface system such as data inquiries, data retrievals, data annotations, data management. MATERIALS AND METHODS Database size, design and structure. The Beef CRC database contains pedigree, management and performance information on 9,677 animals including 7,781 purebreds (PBRED) and 1,896 crossbred (XBRED). Robinson (1995) describes details of the design of the breeding program. In short, breeds included in PBRED were Angus (AA), Hereford (HH), Shorthorn (SS), Murray Grey (MG), Brahman (BB), Belmont Red (BR), and Santa Gertrudis (SG). From 1994 to 1998, cooperating herds (N = 36) provided the Beef CRC on average with 25 fully pedigreed progeny from each of four homebred sires AGBU is a joint institute of NSW Agriculture and The University of New England 485 Proc. Assoc. Advmt. Anim. Breed. Genet. Vol 14 per year, as well as generating linkages across herds by the use of two additional link sires per year (about 12 progeny per link sire). Calves were purchased by the Beef CRC at weaning. The Beef CRC crossbreeding project was based on 1,000 BB females mated to sires of nine breeds representing an array of performance and adaptation potential. Breeds utilised in XBRED included AA, HH, SS, BB, BR, and SG from PBRED. In addition, Charolais, Limousin and Charbray sires were also used. Progeny from XBRED were produced on two different properties. All temperate breeds were finished in the southern, more temperate environments of Australia. BR, SG, and BB cattle were finished in both the northern and southern environments in proportion of 60% to 40%, respectively. Also, around 40% of XBRED cattle of all sire breeds were finished in the southern locations. For both PBRED and XBRED projects, cattle were managed under two finishing regimes (pasture and feedlot) to representative market weights of 400 (domestic), 520 (Korean) and 600 kg (Japanese). The Beef CRC relational database accommodates this structure within 35 tables of which 6 contain the core of the data and the remaining 29 tables provide the interface with the linked lists necessary for the optimal functionality of the system. All possible relationships among tables (ie. one-to-one, one-to-many, many-to-one, many-to-many) are present in the Beef CRC database. Details of the 6 core tables are described next: 1) CRCAnimals: Contains 44 fields and 9,677 records, one for each animal. Information on breed, herd of origin, pedigree, date and type of birth, and allocation regime is stored here. 2) Records: Contains 7 fields and 579,262 records on live measurements including animal and trait identifications, date of measurement and management group. A total of 39 traits are recorded: 26 haematology (blood) measurements, live weight, condition score, flight score, flight speed, hip height, maturity score, muscle score, sheath/navel score, scanned fat depths and eye muscle area. 3) MSRecords: Contains 7 fields and 956,881 records on abattoir and meat science measurements. With similar structure to that of Records table, a total of 222 traits are recorded here including chiller, bone-out, meat quality, and Meat Standard Australia data. 4) FeedData: 9 fields and 125,730 records on feed intake measurements including animal identification, date of measurement, feeder number, total intake, total time and total sessions for the entire period an animal stayed at the automatic feeders of Tullimba feedlot. 5) GroupTraits: with 7 fields and 6,046 records, it defines the various stages in the live of the animals at which several traits were recorded. Allows for quick identification of traits at particular points in time: arrival, end of backgrounding, feedlot entry, pre-slaughter, etc. 6) Codes: with 3 fields and 443 records, it defines each and every one of the codes used throughout the entire database. Database architectural design. The Beef CRC database web interface system has been implemented as a set of related modules involving CGI (Common Gateway Interface) programming, relational database development, and web page design including HTML page templates. The system 486 Proc. Assoc. Advmt. Anim. Breed. Genet. Vol 14 eliminates the problem of slow response due to network congestion by moving all user interactions into the web browser clients. Further, the server database has been build using a high-performance database engine providing excellent query response time and allowing more sophisticated analysis to be made available at a future data. The main components of the architectural strategy include: 1) The system is designed to run under the Apache web server (http://www.apache.com). 2) The database back-end is provided by the MySQL relational database, a high-performance freeware relational database widely used for the web systems. Visit MySQL home page (http://www.mysql.com) for the latest information about MySQL. 3) The system runs under the Red-Hat Linux operating system (http://www.redhat.com) on a Pentium III 550 Mhz computer with 128 Mb of RAM and 13Gb hard disk. 4) Perl is used as scripting language. Perl is freely available (http://www.perl.com), has numerous functional modules, excellent text manipulation features, and can be compiled to run with high efficiency under the Apache web server. 5) The site was developed using the HTML::Mason Perl module (http://www.masonhq.com), a web application framework running under mod_perl (http:// mod_perl.apache.org). This framework combines the best of Perl rapid application development, modular development, and high performance CGI code. Database access and web users. Users are required themselves by logging into have an account maintained host web server computer. level: interface. Access to the Beef CRC database is restricted to authorised to point their browser to http://agbucrc.une.edu.au and authenticate the system through a web form using a username and password. Users as part of the database system and user accounts are not required on the The system provides the following functionality at the user interface 1) Data Inquiries: information on a given animal, progeny information for a given sire, fixed effects, live measurements and abattoir measurements. Inquiry tools for animals, sires, and cohorts are inter-connected allowing for a fast, appealing navigation through the entire database. 2) Data Retrievals: by selected effects, by selected live and abattoir measurements. Prior to downloading from the host computer, the user is able to preview the selected data, making changes as required including user-defined formats for dates and missing values. Downloading is provided in three formats: as a ZIP archive, as a GZIPed TAR archive, and as uncompressed ASCII text. The download starts by the data file being pushed to the user's browser where they can save the file directly to their local filesystem. Upon downloading, the system adds the data set specifications to the user's saved set of specifications, so they can run this retrieval at a later time as a `packaged retrieval'. 487 Proc. Assoc. Advmt. Anim. Breed. Genet. Vol 14 3) Data Annotations: allow users to read and add `one-liner' comments. Automatic notification by email to users when new entries are added. List of codes and database schema can also be accessed from the documentation tools. 4) Data _Dictionary Functions: list animal comments, list database codes, list stage codes, list management regimes for selected cohorts. 5) Accounts Management: an on-line application to database administrator to maintain the integrity of the accounts through adding, deleting, and modifying user accounts as well as adding and deleting data comments. REFERENCES Robinson, D.L. (1995) Proc. Assoc. Advmt. Anim. Breed. Genet. 11: 541. 488