- Email: [email protected]
Computerized representation of feeding regimes in monitored dairy herds
Computers and electronics in agriculture ELS EV l ER
Computers and Electronics in Agriculture 10 ( 1994 ) 151-166
Computerized representation of feeding regimes in monitored dairy herds Fran~oise Lescourret *, P. Dunaud, J. Barnouin, Michelle Chassagne, B. Faye INRA, Centre de Clermont-Ferrand-Theix, Laboratoire d'Ecopathologie, 63122 Saint-Geni~s Champanelle, France
(Accepted 21 September 1993 )
Abstract
A computerized representation o f feeding regimes in dairy herds is based on intermittent information concerning individual calving supplementation, and data on the time distribution of various elements (diets, including nutritive values of supplied feedstuffs, vitamins and mineral elements) to groups o f cows according to their physiological status. A conceptual data model and derived relational database tables describe these basic data. The individual or group data are employed to generate a time distribution of supplies to individual animals, through a computer application, written in procedural language and linked to the database. The application is presented in algorithmic fashion. It runs for any set of animals and associated periods chosen by the user. It uses an input database table and generates output database tables. The application allows investigations of relationships between production or disease in the dairy cow and nutritional practices. Generalization of various features of the system to other fields of work is underlined.
I. Introduction Computerized information systems are increasingly used for storing and processing data collected in dairy herds for animal management, performance checking and research (e.g., Esslemont et al., 1981; Martin et al., 1982; Noordhuizen and Buurman, 1984). However, they rarely include a description of feeding regimes sufficiently accurate for some purposes, such as epidemiological or zootechnical observational studies (but see Faye et al., 1990), where feeding fea* Corresponding author. 0168-1699/94/$07.00 © 1994 Elsevier Science B.V. All rights reserved SSDI 0168-1699(93)E0034-9
152
F. Lescourret et al. / Computers and Electronics in Agriculture 10 (1994) 151-166
tures have been proved to be risk factors of common diseases (Barnouin, 1992 ) or crucial determinants of animal performance. Our paper presents such a description, elaborated in the framework of an epidemiological prospective survey aiming at analysing the occurrence of diseases in dairy cows, especially in the periparturient period (Faye et al., 1989). The study relates to various feeding features likely to be collected in monitored dairy herds, with emphasis on the reconstitution of individual supplies on the basis of supplies to groups of animals or intermittent information. Contrary to experimental situations, observational studies generally do not allow the collection of individual diets, merely that of groups defined by their physiological status (milking cows, dry cows or pregnant heifers ). In this paper, we present a conceptual data model and derived relational database tables, describing both characteristics of the feeding regimes, the physiological status of cows and their herd membership, the last two things being useful for relating individual cows to group feeding. This model is a part of the global model of a relational database intended for the survey, which contains many other data items. Then, we present a computer application written in a procedural language and linked to the database, which realizes the reconstitution of individual supplies evoked above. This problem would have been hard to solve with SQL (Structured Query Language ), the standard language of relational databases, because of the complexity of the queries and huge calculation times. The whole system (database+application) is not dairy herd-specific. It deals with types of problems which can be encountered in other fields of work.
2. Material and Methods
2.1. Data, designing and programming tools The basic information related to the feeding regimes concerns individual calving supplementation and various elements distributed to groups of cows according to their physiological status: diets (and nutritive values of supplied feedstuffs), mineral elements, vitamins and bicarbonate. The conceptual data model employs the entity-relationship approach (Chen, 1976) in the more general framework of a concept for information systems, MERISE (Tardieu et al., 1983 ) that we applied for designing and implementing the general database. MERISE has been widely used in France in various industrial or management companies, but its application to information systems for agricultural research is only recent. Relational database tables were derived from the model according to standard guidelines. They were filled with data collected in 47 dairy herds monitored during 4 years in Brittany, the primary French dairy region. Individual data concerned 4123 dairy cows, covering 8938 completed lactations. The relational database management system was ORACLE, with SQL as query language. We do not detail here either the principles of the entity-relationship approach, or the characteristics of the relational schema, which have been
F. Lescourret et al. / Computers and Electronics in Agriculture 10(1994) 151-166
153
extensively described in the computer science literature, including their application to information systems for monitored dairy herds (Lescourret et al., 1992 ). The model presented here is also described in a simplified way and in a general framework with explanation of modeling rules in Lescourret et al. ( 1993 ). The computer application linked to the database used the programming tool Pro*C, an ORACLE Precompiler allowing embedded SQL statements in a C source program (Portfolio, 1989). Thus, the C program could access and manipulate data in the ORACLE database. The application, which is presented here in algorithmic fashion, was implemented on a SUN 4/390 computer operating on the U N I X system.
2.2. Description of the feeding regimes in a relational database: conceptual data model (Fig. 1) andderived tables (Table 1) A part of the model concerns individual cows, with both feeding features and physiological status. "Physiological status" includes lactation numbers (calving ranks), which are important to herd managers and research workers. One basic entity is "cow", which is identified by a number. It contains attributes having a single value for the whole life history of any cow, among which are the dates at which the cow entered its farm (birth date, if born in it, or purchase date) and left it in the framework of the survey (culling date if before the end of the survey, or end of the survey). The entity "cow" is linked to an entity "lactation number" by three important relationships. The first one, named "lactation", details the start of lactations and contains especially the starting date and any available information on calving supplementation; duration until calving, total quantities of concentrate and cake given during this period and those given at calving. The second one, "maximal supplementation", details the end of the supplementation if it takes place after calving (date, supplied quantities of concentrate and cake); and the third one, "drying off", contains the drying off date. The tables "lactation", "max. suppl." and "drying off" are derived from these three relationships. Displaying lactation numbers in an entity and dates in relationships or vice-versa gives similar table structures. Another part of the model concerns farms to which cows belong. Here, the basic entity is "farm", which is also identified by a number. The entity "cow" is linked to "farm" by a "belongs to" relationship with a simple pattern (during our survey, any monitored cow has belonged to one monitored farm only). The table "cow" is derived from the entity "cow" and this relationship. In monitored dairy herds, a specific set of feedstuffs is generally supplied during a period to each group of animals according to their physiological status: pregnant heifers (within 3 months before first calving), milking cows, dry cows. We introduced here the notion of diet, which is represented by a farm number, a period and a group of animals. A diet is composed of a variable number of feedstuffs, with a mean quantity per day for each, so that the diet changes when either a feedstuff is added or removed, or a quantity is changed. We represent the diet as an entity "diet", linked to the entities "farm", "group" and "period" by binary
154
F. Lescourret et al. / Computers and Electronics in Agriculture 10(1994) 151-166
Table 1 Relational database tables derived from the conceptual data model of Fig. 1 (only columns useful to our description are mentioned) Database table name (number of rows)
Column name (primary key in italics)
cow (4123)
cow number birth date purchase date leaving date farm number
lacation (8938)
cow number+ lactation number starting date duration
Explanation
may be missing culling date or end of the survey
calving date ... of individual supplementation until calving
total quantity of concentrate total quantity of cake quantity of concentrate at calving quantity of cake at calving max.
suppl. (7770)
cow number+ lactation number date quantity of concentrate quantity of cake
drying off (8426)
cow number+ lactation number date
silage supply (3794)
farm number+ analysis date+ starting date + ending date + group code quant
mean quantity per cow and day
farm number+ feedstuff code + starting date+ ending date + group code quant
mean quantity per cow and day
feedstuff supply ( 10208 )
silage (574)
farm number+ analysis date energy content protein content
type
percentage percentage other nutritive values
F. Lescourret et al. / Computers and Electronics in Agriculture 10 (I 994) 151-166
Database table name ( number of rows
Column name (primary key in italics )
~edstuff (181)
feedstuff code energy content protein content
155
Explanation
percentage percentage other nutritive values
type ~edstuff type (75)
feedstuff type label
min. suppl. (1542)
farm number+ P+Ca+Mg+ starting date+ ending date+ group code quant
group measures
(1575)
character string describing the type
mean quantity per cow and day
farm number+ date vitamin supplied to milking cows vitamin supplied to dry cows vitamin supplied to pregnant heifers bicarbonate supplied to milking cows bicarbonate supplied to dry cows bicarbonate supplied to pregnant heifers
yes/no yes/no yes/no mean quantity per cow and day mean quantity per cow and day
mean quantity per cow and day
relationships, xn order to associate each diet to the feedstuffs of which it is composed. Observational studies may require information on nutritive values of supplied feedstuffs, but such an information may be available to different degrees of precision, according to feedstuffs. In our study, it was possible to analyse silages only, whereas the nutritive values of other feedstuffs had to be estimated from a reference book (Jarrige, 1989). For this reason we arranged feedstuffs in two distinct entities, "silage" and (other) "feedstuff", with similar attributes other than the identifiers (percentage of energy content, of protein content, ...), but different identifiers; i.e., the set farm number+ analysis date for "silage" and a code for "feedstuff", whose values are those of the reference book. The entity "diet" is linked to these two entities by relationships "is composed of", containing a mean quantity per cow and day. As data processing may use categories of
I ~ entpiy O rel nshi harabtiooul no inatgptribute ekatbioour nshi O fhar atintrgipbute idenlitiel 0,N cardlnaib/
~ i ~ +
period st~elinadate endinadmte 0,N
0 , N ~ 1,~N~ g~uP ~
......~rbe~
L
Fig. 1. Conceptual data model of feeding regimes in dairy herds.
e+mln:l~upp,t.i,.N
1N ~ 1N cow ':~::::::S?r ' 0,N~ O , N ' c........ ~
0,N
da~ date
date
farm analnumber vsm
O,N
o.N
1,N
~ode
feedstuff ~ t~dstuff
N
feedstufftype
1,1
c~
7"
',o
2
e~
,2
F. Lescourretetal./ComputersandElectronics inAgriculture 10(1994) 151-166
157
feedstuffs (e.g., clover, corn) instead of precise codes, it is useful to create an entity "feedstuff type" to which "silage" and "feedstuff" are linked. The tables "silage supply" and "feedstuff supply" are derived from the compositional relationships between "diet" and "silage" or "feedstuff". The tables "silage" and "feedstuff" are derived from the corresponding entities with their belonging relationship to "feedstuff type". Finally, the table "feedstuff type" is derived from the corresponding entity. In the same manner, mineral supplementation frequently is cow-group-specific. A variety of mineral supplements may be given during the same period. We model the supply by a quaternary relationship "supplies" between the entities "farm", "period", "group" and "mineral supplement", the last mentioned being defined by its chemical composition. The relationship contains the mean quantity per cow and per day. The table "min. suppl." is derived from this relationship. Finally, one may collect information on vitamin and other mineral supplementation. During our survey, data on presence-absence of vitamin supply and mean quantities of bicarbonate supplied to each group of animals (pregnant heifers, milking cows, dry cows) were collected at regular time intervals. We directly mo-delled these facts by a relationship "group measures" between "farm" and an entity "date", the generic name "group measures" meaning that other group in-formation was collected at the same time. The table "group measures" was de.rived from this relationship. The model contains many features commonly encountered in the semantic de.scription of information systems: representation of time (entities like "lactation number", "date", "period" and associated relationships); hierarchical relationships (three relationships "belongs to"); representation of some information by entities instead of relationships, in order to relate it to other information (case of the entity "diet", which seems at first sight to be a relationship); decomposition of entities or relationships for a reliable representation of reality (case of the entities "silage" and "feedstuff", of the same type but with different attributes ).
2.3. A computer application for the reconstitution of individual supplies The calculations solve a very common problem; i.e. (a) how to obtain timedependent information on basic entities (here cows) where information was not recorded on these basic entities but on higher-level entities (here groups of cows ) to which the basic entities are linked in a time-dependent way (here changes in physiological status) or (b) how to obtain continuous information where only intermittent information was recorded (case of the reconstitution of calving supplementation, see below). The computer application is linked to the database in a very general way. In a specific framework chosen by the user, it uses information available in the original database tables to calculate information that it stores in peculiar database tables. The last ones can be queried by the user together with other original tables.
Input and output. The application calculates, for any set of animals and associ-
158
F. Lescourret et al. / Computers and Electronics in Agriculture 10 (1994) 151-166
ated periods, the individual supply for elements chosen by the user among a list: ensiled feedstuffs, other feedstuffs, mineral supplements, vitamins plus bicarbonate, concentrates plus cakes. For the first four categories, the reconstitution is done on the basis of cow-group supplies, taking into account that two events (start of lactation and drying off) determine what group any cow belongs to. For the last one, it is done on the basis of intermittent information on calving supplementation, with the following simple hypotheses: calving supplementation is constant from its beginning until calving, then - if continued - increases or decreases (according to farmer choice) in a linear way until the end (Fig. 2 ). The application scans the whole periods or only parts of them relative to a particular physiological status (pregnant heifers, milking cows, dry cows), as defined by the user. The application uses/generates specific database tables apart from those already described: an "input" table with a cow number associated to a starting date and an ending date (primary key) and a blind numerical attribute "hum" which may be any useful attribute, e.g. a lactation number. The table is filled by the user before starting the application through SQL queries addressed to any part of the database; - "output" tables which can be queried by the user using SQL, together with other tables of the database if necessary. They have a common structure: a cow number, a starting date, an ending date, another attribute, X, representing the supplied element (silage, mineral supplement .... according to tables), a mean quantity supplied per day, "quant" and the blind numerical attribute "num". This structure means that the quantity "quant" of the element X has been supplied to a cow between a starting date and an ending date. One or more tables can be obtained per application use and they have a temporary life span: the -
supplied quantity
qmax (quantity at datmax)
qcalv (quantity at calving)
total quantity supplied during duration
datcalv-duration (calving date - duration of supplementation until calving)
datcalv (calving date)
datmax
time
(end of the supplementation after calving)
Fig. 2. Hypothetical pattern for individual calving supplementation (concentrate or cake).
F. Lescourret et al. / Computers and Electronics in Agriculture 10 (1994) 151-166
159
application has to be locked by the user during a working session and the output tables are removed when the application is unlocked. General algorithm. The general algorithm of the program is valid for any type of output table, except a few changes for the table concerning calving supplementation, the case of which will be explained further. In the course of the program, information is stored in the RAM in two tables, "A" and "B". If necessary, the period associated to each cow is limited at left by the date at which the cow entered its farm (birth date, if born in it, or purchase date ) and at right by the date at which it left it. Initialization of the output database table While lines are found in the input database table read cow number, starting date, ending date in the input database table read for the cow, in the database table COW: birth date, purchase date, leaving date, farm number entry date = birth date if purchase date exists then entry date = purchase date end if if starting date < entry date then starting date = entry date end if if ending date > leaving date then ending date = leaving date end if record cow number, farm number, starting date, ending date, num in the "A" RAM table End while Sort "A" by farm number For every cow n u m b e r + starting date-ending date in "A" calculation of the table " G r o u p " ( 1 ) if new farm number then load the lines of database table " Z Z " in the " B " RAM table sort the lines by chronological order end if calculation and filling up the database output table ( 2 ) End for
The table "Group" contains the physiological status of the cow during each day of the period. The database table " Z Z " is "silage supply", "feedstuff supply", ...according to the type of the output database table. The lines of " Z Z " corresponding to a farm are loaded in "A" when the first cow encountered belonging to this farm is processed. "A" does not change until the processed cow belongs to another farm. Storing information in the RAM ("A", " B " ) saves a lot of time since reading here is hugely faster than in the database. The following part details the calculation of the table "Group" ( l ) and the filling up of the database output table (2), which uses the tables " G r o u p " and " l '~" Reconstitution of the link between a cow and a group. The table "Group" concerns
160
F. Lescourret et aL / Computers and Electronics in Agriculture 10 (1994) 151-166
a set cow number+period; it contains as many cells as days in the period. At the beginning, each cell is filled up with the character "*" (unknown group). Then, the lactations of the cow are scanned (database table "lactation") and if calving days are encountered during the period, the character "M" (milking cow) is written in the corresponding cells. The minimum lactation number of the cow and the date of the last lactation started before the period are stored elsewhere ( 1 ). In a similar way, the drying offinformation of the cow is scanned (database table "drying off" ), characters " D " (dry cow) written in the corresponding cells and other information stored. The following table is obtained: ****M******D***M**** Then, the value of the first element of the table has to be estimated. It gets "M" or " D " depending on whether calving or drying off was the last event before the starting date, or " H " (pregnant heifer) if no such event occurred and if the minimum lactation number stored [see ( 1 ) above] was 1. The table becomes: H***M******D***M**** Finally, the remaining "*" characters are replaced in the following way: HHHHMMMMMMMDDDDMMMMM The detailed algorithm is provided in Appendix 1.
Calculation of individual supplies. The calculation consists in filling up a list of elements supplied to a cow during a period that we call "cow period". The list, previously empty, is composed of units containing the fields day, element identifier and quantity. The calculation uses the table "Group" and the table "B" of the general algorithm, e.g. a picture of the database table "feedstuffsupply" restricted to the farm of the processed cow. In this case, each line of "B" displays the distribution of a fixed quantity o f a feedstuffto a group of cows during a period that we call distribution period. For each day of the cow period, one looks for the lines of "B" corresponding to the group it belonged to that day and with a distribution period including that day. The selected items are then inserted in the list. Finally, for synthesizing this day-by-day information, consecutive days with similar feedstuffs and quantities are gathered. Peculiarities for the reconstitution of the individual calving supplementation. In this case, only the filling up of the list described above is peculiar. For this purpose, information on the lactations of the cow and on calving supplementation is searched (database tables "lactation" and "max. suppl."). For the days of calving supplementation before calving and included in the cow period, the supplied quantity of concentrate or cake is estimated by total quantity/duration (Table 1 ). For the days of calving supplementation after calving (if any), the supplied quantity of concentrate or cake is estimated by: QCALV'[- ( ( Q M A X - - QCALV) * (DATE -- DATCALV ) / (DATMAX -- DATCALV ) )
where QCALVis the quantity at calving, QMAX that at the end of the calving sup-
F. Lescourret et al. / Computers and Electronics in Agriculture 10 (1994) 151-166
161
plementation, DATE the date of the processed day, DATCALVthat of calving and DAa-MAXthat of the end of calving supplementation (Fig. 2 ).
3. Results
3. I. User interface
Any user has to lock the application before using it. It ensures that user's data will not be modified during the working session. Locking implies that an ORACLE identification is required. Connection errors occur when somebody else has already locked the application. Once the application is locked, the user chooses the cows and periods to be studied, by altering (updating, or emptying and filling) what we called before the input database table, through SQL queries. Then, he has to run the application, while choosing as arguments the supplied elements to be processed among a list ( 1 = ensiled feedstuffs, 2 = other feedstuffs, 3 = mineral supplements, 4 = vitamins and bicarbonate, 5 = concentrates and cakes) and the parts of the animals' periods relative to a particular status to be processed among a list ( H = p r e g n a n t heifer, M = milking cow, D = dry cow). Various combinations of choices are possible. The application is started by writing its name followed by the chosen arguments. For example (here let us call REGIMES the name of the executable program ): - REGIMES 12 calculates the time distribution of ensiled and other feedstuffs for the whole periods of the selected animals; - REGIMES D calculates the time distribution of all the types of elements for the dry parts of the periods of the selected animals; - REGIMES 3HM calculates the time distribution of mineral supplement for the heifer and milking parts of the periods of the selected animals. While choosing what is processed, the user has to be aware of disk space limits in the framework of the application (at most 5 Mbytes is planned for the output database tables, whereas 22 Mbytes are necessary to process all the supplied elements and all the cows during the entire survey). It is a frequent problem when several users share a computer resource. Then, the application calculates the time distribution of supplies to individual animals. It deals with abnormal data generated by errors in the query which filled the input database table, by removing the animals for which the end of the period is before the birth date, or the beginning of the period is after the leaving date, or the beginning of the period is after the end. It drops the output database tables resulting from a previous use of the application and generates new ones. At this
fills ...
l
J
I
.
.
select ..
.
• .
Query
.
.
.
.
.
.
.
j ~
.
'
11/29/88
11/27/87
ending date
table silage
10/30/88
2
table lactation
table lactation
10/28/87
starting date
1
;ow ~umber
Input database table: feed/
\
'.
2
1
.
.
$
~EGIMES 12
11111•87 11/27/87 11/27/87 11/29/88
61 457 074 601
code
feedstuffi
5 10 5
quant
.
.
.
10/30/88
11/11/87
.
.
.
.
.
11/29/88
11/27/87
ending date
.
5
4
.
.
farm number
.
.
.
11/29/88
02112187
.
7 7 7 2
num
.
.
400
300
2
7
analysis date quant hum
Output database tab/e feed02
10/28/87 11f11/87 11/11/87 10/30/88
ending date
starting date
Output database table f e e d 0 1
cow starting number date
' .
1 1 1 2
cow number
2
7
num
Running the application REGIMES
Fig. 3. An example of practical application of the system, dealing with the individual supplies (within 30 days before calving) of cows having started actations with placental retention; "num" is a lactation number, "feedI" is the input database table, "feedO 1" and "feedO2" are output database tables isting the individual supply of feedstuffs and silage (see also Appendix 2 ) and quant (supplied quantity ) is in hectograms ( 100 g).
number of cow-lactations having received corn silage within 30 clays before calving: 875 mean dry period: 67.12 days mean pregnancy length: 281.5 days 41 mean distribution time: 2038 days
Results
nsert into feedl eelect ...
2uery ...
T~
.-n
,O
T, ..)
F. Lescourret et al. / Computers and Electronics in Agriculture 10 (1994) 151-166
163
point, the user can query these tables, together with other tables of the database, through SQL queries. Once the required results have been obtained, the user can alter the input database table again and reuse the application, as many times as he wants. When he has finished, he must unlock the application.
3.2. Example of a practical application of the system We investigated the nutritional risk factors of placental retention in dairy cows. Thus, we were interested in individual diets, within 30 days before calving, of animals having started lactations with placental retention between two defined dates. Animals had to belong to two defined breeds; lactations should have lasted 30 days at least, with one calf born only. First, we filled the input database already table described with 300 records, which took 53 seconds thanks to an easy SQL query (Appendix 2). Then, we started the computer application and obtained within 5 minutes two tables listing the required supply of silage and other feedstuffs (Fig. 3). Finally, we could use the tables together with others from the general database. For example, querying the number of cow lactations (meeting the previous conditions) having received corn silage within 30 days before calving, together with the mean distribution time, the mean dry period of the previous lactation and the mean pregnancy length of the current lactation was done through a few SQL command lines (Fig. 3, Appendix 2 ). Results were obtained within 30 seconds.
4. Discussion and Conclusions
Although several information systems for dairy herds partly concern nutritional management, their objectives are different from those of our system. The DairyChamp system in New Zealand (Udomprasert and Williamson, 1990) or the Dairy Information System in Northern Ireland (Goodall and Agnew, 1988 ) display nutritional management in terms of economic flows, since they can monitor the quantity of feed inputs and outputs in farms. DAISY in the U K (Esslemont et al., 1981 ) displays nutritional management in terms of optimal allocation to yield, since it uses calculators to assist rationing, complete diet and concentrate feeding. Our system allows various investigations about relationships between real practices and production or diseases, on the basis of accurate recording in the field (Scholl et al., 1992 ). Moreover, although designed for dairy herd research, our system deals with universal problems. Database design is faced with common problems such as representation of time and the application also deals with time-dependent information at different degrees of precision. The user interface requires a minimal knowledge of database manipulation, but in return it offers various possibilities. Finally, our system takes place in the framework of flexible structures constituted of a database plus processing modules added according to specific requirements.
164
F. Lescourret et al. / Computers and Electronics in Agriculture 10 (1994) 151-166
References Barnouin, J. (1992) Approche dcopathologique de la composante nutritionnelledes troubles de santd chez la vache laiti~re: des facteurs de risque aux mrcanismes de la pathogdnicitr. Doctoral Thesis, Univ. Montpellier II, France, 175 pp. Chen, P.P. ( 1976 ) The entity-relationship model. Toward a unified view of data. ACM T. Databases Syst., 1: 9-36. Esslemont, R.J., Stephens, A.J. and Ellis, D.R. (1981) Dairy herd management. In: G.M. Hillyer, C.T. Whittemore and R.G. Gunn (Editors), Computers in Animal Production. OCcas. Publ., Br. Soc. Anim. Prod., 5: 21-31. Faye, B., Barnouin, J. and Lescourret, F. (1989) Objectifs principaux et stratrgie de l'enqu~te dcopathologique Bretagne sur la vache laitirre. Epiddmiol. Santd Anim., 15:23-31. Faye, B., Gourcy, E. and Barnouin, J. (1990) Mdthodologie de description des syst6mes alimentaires dans le cadre d'une enqu~te dcopathologique en dlevage bovin laitier. Epidrmiol. Sant6 Anim., 17: 117-134. Goodall, E.A. and Agnew, E. (1988) A computerized management information system to monitor annual milk production. Agric. Syst., 26:231-240. Jarrige, R. ( 1989 ) Ruminant Nutrition. Recommended alllowances and feed tables. INRA and John Libbey Eurotext, Paris-London, 389 pp. Lescourret, F., Pdrochon, L., Coulon, J.B., Faye, B. and Landais, E. (1992) Modelling and inforrnalion system using the MERISE method for agricultural research: the example of a database for a study on performances in dairy cows. Agric. Syst., 38:149-173. Lescourret, F., Genest, M., Barnouin, J., Chassagne, M. and Faye, B. (1993) Data modeling for database design in production and health monitoring systems for dairy herds. J. Dairy Sci., 76:10531062. Martin, B., Mainland, D.D. and Green, M.A. ( 1982 ) VIRUS: a computer program for herd health and productivity. Vet. Rec., 110: 446-448. Noordhuizen, J.P.T.M. and Buurman, J. (1984) VAMPP: a Veterinary Automated Management and Production control Programme for dairy farms (the application of MUMPS for data processing). Vet. Q., 6: 67-72. Portfolio, T. ( 1989 ) ORACLE Precompilers. Programmer's Guide, Version 1.3. Oracle Corporation, Belmont, CA. Scholl, D.T., Dobbelaar, P. and Brand, A. (1992) Standardized protocol to develop dairy farm management questionnaires for observational studies. J. Dairy Sci., 75:615-623. Tardieu, H., Rochfeld, A. and Coletti, R. (1983) La mdthode MERISE. Tome I. Principes et outils. Les Editions d'Organisation, Paris. Udomprasert, P. and Williamson, N.B. (1990) The Dairychamp program: a computerised recording system for dairy herds. Vet. Rec., 127: 256-262.
Appendix 1 Detailed algorithm o f the reconstitution o f the link between a cow and a group Variables used Group [ ]: table with as many cells as days in the period, to be filled with characters "M" (milking cow), "D" (dry cow) or "H" (pregnant heifer). At the beginning, each cell is filled up with the character "*" (unknown group); lactnum: lactation number; datcalv: calving date corresponding to lactnum; datdry: drying off date following datcalv;
F. Lescourretet al. / Computersand Electronics in Agriculture 10 (1994) I51-166 firstdatcalv: firstdatdry: prevdatcalv: prevdatdry: d a t e ); lactnum0:
165
first calving date in the period; first drying off date in the time period; first calving date before the beginning of the period ( = starting date); first drying off date before the beginning of the period (--starting
smallest lactation number
of the cow recorded in the database.
firstdatcalv = 999 lactnum0 = 999 prevdatcalv-- 0 prevdatdry = 0 For each lactation if starting date < = datcalv < = ending date then Group [ datcalv-starting date ] = " P " if datcalv < firstdatcalv then firstdatcalv = datcalv end if end if if lactnum < lactnum0 then lactnum0 = lactnum end if if datcalv < starting date and prevdatcalv < datcalv then prevdatcalv = datcalv end if End for For each drying off if starting date < = datdry < = ending date then Group [ datdry-starting date ] = " D " end if if datdry < starting date and prevdatdry < datdry then prevdatdry = datdry end if End for If prevdatcalv > prevdatdry then Group [ 0 ] = " P " end if If prevdatdry > prevdatcalv then Group [ 0 ] = " D " end if If prevdatcalv = 0 and prevdatdry = 0 then if Group [ 0 ] = ' .... and lactnum0 = 1 then Group [ 0 ] = " H " end if End if For i = 0 to ending d a t e - starting date - 1 if Group [ i ] = ' .... then Group [ i ] = Group [ i - 1 ] end if End for If Group [0 ] = " H " then i=0 while i < ending date-starting date and Group [i ] = " H " i = i + 1 end while if i > 90 then forj = 0 to i - 91 Group [j ] = ' .... end for end if End if
Appendix 2 Example o f SQL queries used before and after running the application ( 1 ) SQL queryforfilling the input database table I n t h e f o l l o w i n g q u e r y , a, b , c a n d d a r e s y n o n y m s
of the tables; "feedI"
is t h e
IThis is due to the definition of the pregnant heifer: female within 3 months before its first calving.
166
F. Lescourret et al. / Computers and Electronics in Agriculture 10 (1994) 151-166
input database table (I means "input"); "cow disease" is a database table harbouring the disease occurrences of cows, which contains the fields cow number, lactation number, date and disease code ("PR" is a code value meaning placental retention); breed is a field of the table "cow" and 66 and 83 two breed values; nucalv, milkdays and drydays are fields of "lactation" and relate to the number of born calves, the number of days in milk and the length of the dry period, respectively. The first three queried items correspond to the primary key of the input table and the last one to the blind numerical attribute "num" (see Section 2.3). insert into feedI select a.cow n u m b e r , a . d a t e - 30, a.date, a.lactation n u m b e r f r o m lactation a, cow b, cow disease c, lactation d where a.cow n u m b e r = b.cow n u m b e r a n d a.cow n u m b e r = c.cow n u m b e r a n d a.cow n u m b e r = d.cow n u m b e r a n d a.lactation n u m b e r = c.lactation n u m b e r a n d a.lactation n u m b e r = d.lactation n u m b e r + 1 a n d disease c o d e - - - " P R " a n d breed in (66, 83 ) and nucalv = 1 a n d a.dat b e t w e e n " 1 - a u g - 8 6 " a n d " 3 1 - d e c - 8 9 " a n d m i l k d a y s > = 30 a n d d r y d a y s > 30
(2) SQL query for using an output database table, together with other tables In the following, a, b, c, d and e are synonyms of tables as previously; "feedO2" is the second output database table, which concerns time distribution of ensiled feedstuffs (O means "output") and contains the fields cow number, starting date, ending date, farm number+analysis date (identifying the ensiled feedstuff, as in the "silage" table) and the blind numerical attribute "num" (here a lactation number); dry period and pregnancy length are attributes of the "lactation" table; "CS" is a code value meaning corn silage; avg is a SQL function meaning "average". select c o u n t (a.cow n u m b e r ) , avg (c.dry period ), avg (a.pregnancy length ), avg ( d . e n d i n g date - d.starting date ) f r o m lactation a, cow b, lactation c, f e e d 0 2 d, silage e where a.cow n u m b e r = b.cow n u m b e r a n d a.cow n u m b e r = c.cow n u m b e r a n d a.cow n u m b e r = d.cow n u m b e r and b.farm number = e.farm number a n d a.lactation n u m b e r = d . n u m a n d a.lactation n u m b e r = c.lactation n u m b e r + 1 a n d d.analysis date = e.analysis date a n d e.type = " C S "
Computers and Electronics in Agriculture 10 ( 1994 ) 151-166
Computerized representation of feeding regimes in monitored dairy herds Fran~oise Lescourret *, P. Dunaud, J. Barnouin, Michelle Chassagne, B. Faye INRA, Centre de Clermont-Ferrand-Theix, Laboratoire d'Ecopathologie, 63122 Saint-Geni~s Champanelle, France
(Accepted 21 September 1993 )
Abstract
A computerized representation o f feeding regimes in dairy herds is based on intermittent information concerning individual calving supplementation, and data on the time distribution of various elements (diets, including nutritive values of supplied feedstuffs, vitamins and mineral elements) to groups o f cows according to their physiological status. A conceptual data model and derived relational database tables describe these basic data. The individual or group data are employed to generate a time distribution of supplies to individual animals, through a computer application, written in procedural language and linked to the database. The application is presented in algorithmic fashion. It runs for any set of animals and associated periods chosen by the user. It uses an input database table and generates output database tables. The application allows investigations of relationships between production or disease in the dairy cow and nutritional practices. Generalization of various features of the system to other fields of work is underlined.
I. Introduction Computerized information systems are increasingly used for storing and processing data collected in dairy herds for animal management, performance checking and research (e.g., Esslemont et al., 1981; Martin et al., 1982; Noordhuizen and Buurman, 1984). However, they rarely include a description of feeding regimes sufficiently accurate for some purposes, such as epidemiological or zootechnical observational studies (but see Faye et al., 1990), where feeding fea* Corresponding author. 0168-1699/94/$07.00 © 1994 Elsevier Science B.V. All rights reserved SSDI 0168-1699(93)E0034-9
152
F. Lescourret et al. / Computers and Electronics in Agriculture 10 (1994) 151-166
tures have been proved to be risk factors of common diseases (Barnouin, 1992 ) or crucial determinants of animal performance. Our paper presents such a description, elaborated in the framework of an epidemiological prospective survey aiming at analysing the occurrence of diseases in dairy cows, especially in the periparturient period (Faye et al., 1989). The study relates to various feeding features likely to be collected in monitored dairy herds, with emphasis on the reconstitution of individual supplies on the basis of supplies to groups of animals or intermittent information. Contrary to experimental situations, observational studies generally do not allow the collection of individual diets, merely that of groups defined by their physiological status (milking cows, dry cows or pregnant heifers ). In this paper, we present a conceptual data model and derived relational database tables, describing both characteristics of the feeding regimes, the physiological status of cows and their herd membership, the last two things being useful for relating individual cows to group feeding. This model is a part of the global model of a relational database intended for the survey, which contains many other data items. Then, we present a computer application written in a procedural language and linked to the database, which realizes the reconstitution of individual supplies evoked above. This problem would have been hard to solve with SQL (Structured Query Language ), the standard language of relational databases, because of the complexity of the queries and huge calculation times. The whole system (database+application) is not dairy herd-specific. It deals with types of problems which can be encountered in other fields of work.
2. Material and Methods
2.1. Data, designing and programming tools The basic information related to the feeding regimes concerns individual calving supplementation and various elements distributed to groups of cows according to their physiological status: diets (and nutritive values of supplied feedstuffs), mineral elements, vitamins and bicarbonate. The conceptual data model employs the entity-relationship approach (Chen, 1976) in the more general framework of a concept for information systems, MERISE (Tardieu et al., 1983 ) that we applied for designing and implementing the general database. MERISE has been widely used in France in various industrial or management companies, but its application to information systems for agricultural research is only recent. Relational database tables were derived from the model according to standard guidelines. They were filled with data collected in 47 dairy herds monitored during 4 years in Brittany, the primary French dairy region. Individual data concerned 4123 dairy cows, covering 8938 completed lactations. The relational database management system was ORACLE, with SQL as query language. We do not detail here either the principles of the entity-relationship approach, or the characteristics of the relational schema, which have been
F. Lescourret et al. / Computers and Electronics in Agriculture 10(1994) 151-166
153
extensively described in the computer science literature, including their application to information systems for monitored dairy herds (Lescourret et al., 1992 ). The model presented here is also described in a simplified way and in a general framework with explanation of modeling rules in Lescourret et al. ( 1993 ). The computer application linked to the database used the programming tool Pro*C, an ORACLE Precompiler allowing embedded SQL statements in a C source program (Portfolio, 1989). Thus, the C program could access and manipulate data in the ORACLE database. The application, which is presented here in algorithmic fashion, was implemented on a SUN 4/390 computer operating on the U N I X system.
2.2. Description of the feeding regimes in a relational database: conceptual data model (Fig. 1) andderived tables (Table 1) A part of the model concerns individual cows, with both feeding features and physiological status. "Physiological status" includes lactation numbers (calving ranks), which are important to herd managers and research workers. One basic entity is "cow", which is identified by a number. It contains attributes having a single value for the whole life history of any cow, among which are the dates at which the cow entered its farm (birth date, if born in it, or purchase date) and left it in the framework of the survey (culling date if before the end of the survey, or end of the survey). The entity "cow" is linked to an entity "lactation number" by three important relationships. The first one, named "lactation", details the start of lactations and contains especially the starting date and any available information on calving supplementation; duration until calving, total quantities of concentrate and cake given during this period and those given at calving. The second one, "maximal supplementation", details the end of the supplementation if it takes place after calving (date, supplied quantities of concentrate and cake); and the third one, "drying off", contains the drying off date. The tables "lactation", "max. suppl." and "drying off" are derived from these three relationships. Displaying lactation numbers in an entity and dates in relationships or vice-versa gives similar table structures. Another part of the model concerns farms to which cows belong. Here, the basic entity is "farm", which is also identified by a number. The entity "cow" is linked to "farm" by a "belongs to" relationship with a simple pattern (during our survey, any monitored cow has belonged to one monitored farm only). The table "cow" is derived from the entity "cow" and this relationship. In monitored dairy herds, a specific set of feedstuffs is generally supplied during a period to each group of animals according to their physiological status: pregnant heifers (within 3 months before first calving), milking cows, dry cows. We introduced here the notion of diet, which is represented by a farm number, a period and a group of animals. A diet is composed of a variable number of feedstuffs, with a mean quantity per day for each, so that the diet changes when either a feedstuff is added or removed, or a quantity is changed. We represent the diet as an entity "diet", linked to the entities "farm", "group" and "period" by binary
154
F. Lescourret et al. / Computers and Electronics in Agriculture 10(1994) 151-166
Table 1 Relational database tables derived from the conceptual data model of Fig. 1 (only columns useful to our description are mentioned) Database table name (number of rows)
Column name (primary key in italics)
cow (4123)
cow number birth date purchase date leaving date farm number
lacation (8938)
cow number+ lactation number starting date duration
Explanation
may be missing culling date or end of the survey
calving date ... of individual supplementation until calving
total quantity of concentrate total quantity of cake quantity of concentrate at calving quantity of cake at calving max.
suppl. (7770)
cow number+ lactation number date quantity of concentrate quantity of cake
drying off (8426)
cow number+ lactation number date
silage supply (3794)
farm number+ analysis date+ starting date + ending date + group code quant
mean quantity per cow and day
farm number+ feedstuff code + starting date+ ending date + group code quant
mean quantity per cow and day
feedstuff supply ( 10208 )
silage (574)
farm number+ analysis date energy content protein content
type
percentage percentage other nutritive values
F. Lescourret et al. / Computers and Electronics in Agriculture 10 (I 994) 151-166
Database table name ( number of rows
Column name (primary key in italics )
~edstuff (181)
feedstuff code energy content protein content
155
Explanation
percentage percentage other nutritive values
type ~edstuff type (75)
feedstuff type label
min. suppl. (1542)
farm number+ P+Ca+Mg+ starting date+ ending date+ group code quant
group measures
(1575)
character string describing the type
mean quantity per cow and day
farm number+ date vitamin supplied to milking cows vitamin supplied to dry cows vitamin supplied to pregnant heifers bicarbonate supplied to milking cows bicarbonate supplied to dry cows bicarbonate supplied to pregnant heifers
yes/no yes/no yes/no mean quantity per cow and day mean quantity per cow and day
mean quantity per cow and day
relationships, xn order to associate each diet to the feedstuffs of which it is composed. Observational studies may require information on nutritive values of supplied feedstuffs, but such an information may be available to different degrees of precision, according to feedstuffs. In our study, it was possible to analyse silages only, whereas the nutritive values of other feedstuffs had to be estimated from a reference book (Jarrige, 1989). For this reason we arranged feedstuffs in two distinct entities, "silage" and (other) "feedstuff", with similar attributes other than the identifiers (percentage of energy content, of protein content, ...), but different identifiers; i.e., the set farm number+ analysis date for "silage" and a code for "feedstuff", whose values are those of the reference book. The entity "diet" is linked to these two entities by relationships "is composed of", containing a mean quantity per cow and day. As data processing may use categories of
I ~ entpiy O rel nshi harabtiooul no inatgptribute ekatbioour nshi O fhar atintrgipbute idenlitiel 0,N cardlnaib/
~ i ~ +
period st~elinadate endinadmte 0,N
0 , N ~ 1,~N~ g~uP ~
......~rbe~
L
Fig. 1. Conceptual data model of feeding regimes in dairy herds.
e+mln:l~upp,t.i,.N
1N ~ 1N cow ':~::::::S?r ' 0,N~ O , N ' c........ ~
0,N
da~ date
date
farm analnumber vsm
O,N
o.N
1,N
~ode
feedstuff ~ t~dstuff
N
feedstufftype
1,1
c~
7"
',o
2
e~
,2
F. Lescourretetal./ComputersandElectronics inAgriculture 10(1994) 151-166
157
feedstuffs (e.g., clover, corn) instead of precise codes, it is useful to create an entity "feedstuff type" to which "silage" and "feedstuff" are linked. The tables "silage supply" and "feedstuff supply" are derived from the compositional relationships between "diet" and "silage" or "feedstuff". The tables "silage" and "feedstuff" are derived from the corresponding entities with their belonging relationship to "feedstuff type". Finally, the table "feedstuff type" is derived from the corresponding entity. In the same manner, mineral supplementation frequently is cow-group-specific. A variety of mineral supplements may be given during the same period. We model the supply by a quaternary relationship "supplies" between the entities "farm", "period", "group" and "mineral supplement", the last mentioned being defined by its chemical composition. The relationship contains the mean quantity per cow and per day. The table "min. suppl." is derived from this relationship. Finally, one may collect information on vitamin and other mineral supplementation. During our survey, data on presence-absence of vitamin supply and mean quantities of bicarbonate supplied to each group of animals (pregnant heifers, milking cows, dry cows) were collected at regular time intervals. We directly mo-delled these facts by a relationship "group measures" between "farm" and an entity "date", the generic name "group measures" meaning that other group in-formation was collected at the same time. The table "group measures" was de.rived from this relationship. The model contains many features commonly encountered in the semantic de.scription of information systems: representation of time (entities like "lactation number", "date", "period" and associated relationships); hierarchical relationships (three relationships "belongs to"); representation of some information by entities instead of relationships, in order to relate it to other information (case of the entity "diet", which seems at first sight to be a relationship); decomposition of entities or relationships for a reliable representation of reality (case of the entities "silage" and "feedstuff", of the same type but with different attributes ).
2.3. A computer application for the reconstitution of individual supplies The calculations solve a very common problem; i.e. (a) how to obtain timedependent information on basic entities (here cows) where information was not recorded on these basic entities but on higher-level entities (here groups of cows ) to which the basic entities are linked in a time-dependent way (here changes in physiological status) or (b) how to obtain continuous information where only intermittent information was recorded (case of the reconstitution of calving supplementation, see below). The computer application is linked to the database in a very general way. In a specific framework chosen by the user, it uses information available in the original database tables to calculate information that it stores in peculiar database tables. The last ones can be queried by the user together with other original tables.
Input and output. The application calculates, for any set of animals and associ-
158
F. Lescourret et al. / Computers and Electronics in Agriculture 10 (1994) 151-166
ated periods, the individual supply for elements chosen by the user among a list: ensiled feedstuffs, other feedstuffs, mineral supplements, vitamins plus bicarbonate, concentrates plus cakes. For the first four categories, the reconstitution is done on the basis of cow-group supplies, taking into account that two events (start of lactation and drying off) determine what group any cow belongs to. For the last one, it is done on the basis of intermittent information on calving supplementation, with the following simple hypotheses: calving supplementation is constant from its beginning until calving, then - if continued - increases or decreases (according to farmer choice) in a linear way until the end (Fig. 2 ). The application scans the whole periods or only parts of them relative to a particular physiological status (pregnant heifers, milking cows, dry cows), as defined by the user. The application uses/generates specific database tables apart from those already described: an "input" table with a cow number associated to a starting date and an ending date (primary key) and a blind numerical attribute "hum" which may be any useful attribute, e.g. a lactation number. The table is filled by the user before starting the application through SQL queries addressed to any part of the database; - "output" tables which can be queried by the user using SQL, together with other tables of the database if necessary. They have a common structure: a cow number, a starting date, an ending date, another attribute, X, representing the supplied element (silage, mineral supplement .... according to tables), a mean quantity supplied per day, "quant" and the blind numerical attribute "num". This structure means that the quantity "quant" of the element X has been supplied to a cow between a starting date and an ending date. One or more tables can be obtained per application use and they have a temporary life span: the -
supplied quantity
qmax (quantity at datmax)
qcalv (quantity at calving)
total quantity supplied during duration
datcalv-duration (calving date - duration of supplementation until calving)
datcalv (calving date)
datmax
time
(end of the supplementation after calving)
Fig. 2. Hypothetical pattern for individual calving supplementation (concentrate or cake).
F. Lescourret et al. / Computers and Electronics in Agriculture 10 (1994) 151-166
159
application has to be locked by the user during a working session and the output tables are removed when the application is unlocked. General algorithm. The general algorithm of the program is valid for any type of output table, except a few changes for the table concerning calving supplementation, the case of which will be explained further. In the course of the program, information is stored in the RAM in two tables, "A" and "B". If necessary, the period associated to each cow is limited at left by the date at which the cow entered its farm (birth date, if born in it, or purchase date ) and at right by the date at which it left it. Initialization of the output database table While lines are found in the input database table read cow number, starting date, ending date in the input database table read for the cow, in the database table COW: birth date, purchase date, leaving date, farm number entry date = birth date if purchase date exists then entry date = purchase date end if if starting date < entry date then starting date = entry date end if if ending date > leaving date then ending date = leaving date end if record cow number, farm number, starting date, ending date, num in the "A" RAM table End while Sort "A" by farm number For every cow n u m b e r + starting date-ending date in "A" calculation of the table " G r o u p " ( 1 ) if new farm number then load the lines of database table " Z Z " in the " B " RAM table sort the lines by chronological order end if calculation and filling up the database output table ( 2 ) End for
The table "Group" contains the physiological status of the cow during each day of the period. The database table " Z Z " is "silage supply", "feedstuff supply", ...according to the type of the output database table. The lines of " Z Z " corresponding to a farm are loaded in "A" when the first cow encountered belonging to this farm is processed. "A" does not change until the processed cow belongs to another farm. Storing information in the RAM ("A", " B " ) saves a lot of time since reading here is hugely faster than in the database. The following part details the calculation of the table "Group" ( l ) and the filling up of the database output table (2), which uses the tables " G r o u p " and " l '~" Reconstitution of the link between a cow and a group. The table "Group" concerns
160
F. Lescourret et aL / Computers and Electronics in Agriculture 10 (1994) 151-166
a set cow number+period; it contains as many cells as days in the period. At the beginning, each cell is filled up with the character "*" (unknown group). Then, the lactations of the cow are scanned (database table "lactation") and if calving days are encountered during the period, the character "M" (milking cow) is written in the corresponding cells. The minimum lactation number of the cow and the date of the last lactation started before the period are stored elsewhere ( 1 ). In a similar way, the drying offinformation of the cow is scanned (database table "drying off" ), characters " D " (dry cow) written in the corresponding cells and other information stored. The following table is obtained: ****M******D***M**** Then, the value of the first element of the table has to be estimated. It gets "M" or " D " depending on whether calving or drying off was the last event before the starting date, or " H " (pregnant heifer) if no such event occurred and if the minimum lactation number stored [see ( 1 ) above] was 1. The table becomes: H***M******D***M**** Finally, the remaining "*" characters are replaced in the following way: HHHHMMMMMMMDDDDMMMMM The detailed algorithm is provided in Appendix 1.
Calculation of individual supplies. The calculation consists in filling up a list of elements supplied to a cow during a period that we call "cow period". The list, previously empty, is composed of units containing the fields day, element identifier and quantity. The calculation uses the table "Group" and the table "B" of the general algorithm, e.g. a picture of the database table "feedstuffsupply" restricted to the farm of the processed cow. In this case, each line of "B" displays the distribution of a fixed quantity o f a feedstuffto a group of cows during a period that we call distribution period. For each day of the cow period, one looks for the lines of "B" corresponding to the group it belonged to that day and with a distribution period including that day. The selected items are then inserted in the list. Finally, for synthesizing this day-by-day information, consecutive days with similar feedstuffs and quantities are gathered. Peculiarities for the reconstitution of the individual calving supplementation. In this case, only the filling up of the list described above is peculiar. For this purpose, information on the lactations of the cow and on calving supplementation is searched (database tables "lactation" and "max. suppl."). For the days of calving supplementation before calving and included in the cow period, the supplied quantity of concentrate or cake is estimated by total quantity/duration (Table 1 ). For the days of calving supplementation after calving (if any), the supplied quantity of concentrate or cake is estimated by: QCALV'[- ( ( Q M A X - - QCALV) * (DATE -- DATCALV ) / (DATMAX -- DATCALV ) )
where QCALVis the quantity at calving, QMAX that at the end of the calving sup-
F. Lescourret et al. / Computers and Electronics in Agriculture 10 (1994) 151-166
161
plementation, DATE the date of the processed day, DATCALVthat of calving and DAa-MAXthat of the end of calving supplementation (Fig. 2 ).
3. Results
3. I. User interface
Any user has to lock the application before using it. It ensures that user's data will not be modified during the working session. Locking implies that an ORACLE identification is required. Connection errors occur when somebody else has already locked the application. Once the application is locked, the user chooses the cows and periods to be studied, by altering (updating, or emptying and filling) what we called before the input database table, through SQL queries. Then, he has to run the application, while choosing as arguments the supplied elements to be processed among a list ( 1 = ensiled feedstuffs, 2 = other feedstuffs, 3 = mineral supplements, 4 = vitamins and bicarbonate, 5 = concentrates and cakes) and the parts of the animals' periods relative to a particular status to be processed among a list ( H = p r e g n a n t heifer, M = milking cow, D = dry cow). Various combinations of choices are possible. The application is started by writing its name followed by the chosen arguments. For example (here let us call REGIMES the name of the executable program ): - REGIMES 12 calculates the time distribution of ensiled and other feedstuffs for the whole periods of the selected animals; - REGIMES D calculates the time distribution of all the types of elements for the dry parts of the periods of the selected animals; - REGIMES 3HM calculates the time distribution of mineral supplement for the heifer and milking parts of the periods of the selected animals. While choosing what is processed, the user has to be aware of disk space limits in the framework of the application (at most 5 Mbytes is planned for the output database tables, whereas 22 Mbytes are necessary to process all the supplied elements and all the cows during the entire survey). It is a frequent problem when several users share a computer resource. Then, the application calculates the time distribution of supplies to individual animals. It deals with abnormal data generated by errors in the query which filled the input database table, by removing the animals for which the end of the period is before the birth date, or the beginning of the period is after the leaving date, or the beginning of the period is after the end. It drops the output database tables resulting from a previous use of the application and generates new ones. At this
fills ...
l
J
I
.
.
select ..
.
• .
Query
.
.
.
.
.
.
.
j ~
.
'
11/29/88
11/27/87
ending date
table silage
10/30/88
2
table lactation
table lactation
10/28/87
starting date
1
;ow ~umber
Input database table: feed/
\
'.
2
1
.
.
$
~EGIMES 12
11111•87 11/27/87 11/27/87 11/29/88
61 457 074 601
code
feedstuffi
5 10 5
quant
.
.
.
10/30/88
11/11/87
.
.
.
.
.
11/29/88
11/27/87
ending date
.
5
4
.
.
farm number
.
.
.
11/29/88
02112187
.
7 7 7 2
num
.
.
400
300
2
7
analysis date quant hum
Output database tab/e feed02
10/28/87 11f11/87 11/11/87 10/30/88
ending date
starting date
Output database table f e e d 0 1
cow starting number date
' .
1 1 1 2
cow number
2
7
num
Running the application REGIMES
Fig. 3. An example of practical application of the system, dealing with the individual supplies (within 30 days before calving) of cows having started actations with placental retention; "num" is a lactation number, "feedI" is the input database table, "feedO 1" and "feedO2" are output database tables isting the individual supply of feedstuffs and silage (see also Appendix 2 ) and quant (supplied quantity ) is in hectograms ( 100 g).
number of cow-lactations having received corn silage within 30 clays before calving: 875 mean dry period: 67.12 days mean pregnancy length: 281.5 days 41 mean distribution time: 2038 days
Results
nsert into feedl eelect ...
2uery ...
T~
.-n
,O
T, ..)
F. Lescourret et al. / Computers and Electronics in Agriculture 10 (1994) 151-166
163
point, the user can query these tables, together with other tables of the database, through SQL queries. Once the required results have been obtained, the user can alter the input database table again and reuse the application, as many times as he wants. When he has finished, he must unlock the application.
3.2. Example of a practical application of the system We investigated the nutritional risk factors of placental retention in dairy cows. Thus, we were interested in individual diets, within 30 days before calving, of animals having started lactations with placental retention between two defined dates. Animals had to belong to two defined breeds; lactations should have lasted 30 days at least, with one calf born only. First, we filled the input database already table described with 300 records, which took 53 seconds thanks to an easy SQL query (Appendix 2). Then, we started the computer application and obtained within 5 minutes two tables listing the required supply of silage and other feedstuffs (Fig. 3). Finally, we could use the tables together with others from the general database. For example, querying the number of cow lactations (meeting the previous conditions) having received corn silage within 30 days before calving, together with the mean distribution time, the mean dry period of the previous lactation and the mean pregnancy length of the current lactation was done through a few SQL command lines (Fig. 3, Appendix 2 ). Results were obtained within 30 seconds.
4. Discussion and Conclusions
Although several information systems for dairy herds partly concern nutritional management, their objectives are different from those of our system. The DairyChamp system in New Zealand (Udomprasert and Williamson, 1990) or the Dairy Information System in Northern Ireland (Goodall and Agnew, 1988 ) display nutritional management in terms of economic flows, since they can monitor the quantity of feed inputs and outputs in farms. DAISY in the U K (Esslemont et al., 1981 ) displays nutritional management in terms of optimal allocation to yield, since it uses calculators to assist rationing, complete diet and concentrate feeding. Our system allows various investigations about relationships between real practices and production or diseases, on the basis of accurate recording in the field (Scholl et al., 1992 ). Moreover, although designed for dairy herd research, our system deals with universal problems. Database design is faced with common problems such as representation of time and the application also deals with time-dependent information at different degrees of precision. The user interface requires a minimal knowledge of database manipulation, but in return it offers various possibilities. Finally, our system takes place in the framework of flexible structures constituted of a database plus processing modules added according to specific requirements.
164
F. Lescourret et al. / Computers and Electronics in Agriculture 10 (1994) 151-166
References Barnouin, J. (1992) Approche dcopathologique de la composante nutritionnelledes troubles de santd chez la vache laiti~re: des facteurs de risque aux mrcanismes de la pathogdnicitr. Doctoral Thesis, Univ. Montpellier II, France, 175 pp. Chen, P.P. ( 1976 ) The entity-relationship model. Toward a unified view of data. ACM T. Databases Syst., 1: 9-36. Esslemont, R.J., Stephens, A.J. and Ellis, D.R. (1981) Dairy herd management. In: G.M. Hillyer, C.T. Whittemore and R.G. Gunn (Editors), Computers in Animal Production. OCcas. Publ., Br. Soc. Anim. Prod., 5: 21-31. Faye, B., Barnouin, J. and Lescourret, F. (1989) Objectifs principaux et stratrgie de l'enqu~te dcopathologique Bretagne sur la vache laitirre. Epiddmiol. Santd Anim., 15:23-31. Faye, B., Gourcy, E. and Barnouin, J. (1990) Mdthodologie de description des syst6mes alimentaires dans le cadre d'une enqu~te dcopathologique en dlevage bovin laitier. Epidrmiol. Sant6 Anim., 17: 117-134. Goodall, E.A. and Agnew, E. (1988) A computerized management information system to monitor annual milk production. Agric. Syst., 26:231-240. Jarrige, R. ( 1989 ) Ruminant Nutrition. Recommended alllowances and feed tables. INRA and John Libbey Eurotext, Paris-London, 389 pp. Lescourret, F., Pdrochon, L., Coulon, J.B., Faye, B. and Landais, E. (1992) Modelling and inforrnalion system using the MERISE method for agricultural research: the example of a database for a study on performances in dairy cows. Agric. Syst., 38:149-173. Lescourret, F., Genest, M., Barnouin, J., Chassagne, M. and Faye, B. (1993) Data modeling for database design in production and health monitoring systems for dairy herds. J. Dairy Sci., 76:10531062. Martin, B., Mainland, D.D. and Green, M.A. ( 1982 ) VIRUS: a computer program for herd health and productivity. Vet. Rec., 110: 446-448. Noordhuizen, J.P.T.M. and Buurman, J. (1984) VAMPP: a Veterinary Automated Management and Production control Programme for dairy farms (the application of MUMPS for data processing). Vet. Q., 6: 67-72. Portfolio, T. ( 1989 ) ORACLE Precompilers. Programmer's Guide, Version 1.3. Oracle Corporation, Belmont, CA. Scholl, D.T., Dobbelaar, P. and Brand, A. (1992) Standardized protocol to develop dairy farm management questionnaires for observational studies. J. Dairy Sci., 75:615-623. Tardieu, H., Rochfeld, A. and Coletti, R. (1983) La mdthode MERISE. Tome I. Principes et outils. Les Editions d'Organisation, Paris. Udomprasert, P. and Williamson, N.B. (1990) The Dairychamp program: a computerised recording system for dairy herds. Vet. Rec., 127: 256-262.
Appendix 1 Detailed algorithm o f the reconstitution o f the link between a cow and a group Variables used Group [ ]: table with as many cells as days in the period, to be filled with characters "M" (milking cow), "D" (dry cow) or "H" (pregnant heifer). At the beginning, each cell is filled up with the character "*" (unknown group); lactnum: lactation number; datcalv: calving date corresponding to lactnum; datdry: drying off date following datcalv;
F. Lescourretet al. / Computersand Electronics in Agriculture 10 (1994) I51-166 firstdatcalv: firstdatdry: prevdatcalv: prevdatdry: d a t e ); lactnum0:
165
first calving date in the period; first drying off date in the time period; first calving date before the beginning of the period ( = starting date); first drying off date before the beginning of the period (--starting
smallest lactation number
of the cow recorded in the database.
firstdatcalv = 999 lactnum0 = 999 prevdatcalv-- 0 prevdatdry = 0 For each lactation if starting date < = datcalv < = ending date then Group [ datcalv-starting date ] = " P " if datcalv < firstdatcalv then firstdatcalv = datcalv end if end if if lactnum < lactnum0 then lactnum0 = lactnum end if if datcalv < starting date and prevdatcalv < datcalv then prevdatcalv = datcalv end if End for For each drying off if starting date < = datdry < = ending date then Group [ datdry-starting date ] = " D " end if if datdry < starting date and prevdatdry < datdry then prevdatdry = datdry end if End for If prevdatcalv > prevdatdry then Group [ 0 ] = " P " end if If prevdatdry > prevdatcalv then Group [ 0 ] = " D " end if If prevdatcalv = 0 and prevdatdry = 0 then if Group [ 0 ] = ' .... and lactnum0 = 1 then Group [ 0 ] = " H " end if End if For i = 0 to ending d a t e - starting date - 1 if Group [ i ] = ' .... then Group [ i ] = Group [ i - 1 ] end if End for If Group [0 ] = " H " then i=0 while i < ending date-starting date and Group [i ] = " H " i = i + 1 end while if i > 90 then forj = 0 to i - 91 Group [j ] = ' .... end for end if End if
Appendix 2 Example o f SQL queries used before and after running the application ( 1 ) SQL queryforfilling the input database table I n t h e f o l l o w i n g q u e r y , a, b , c a n d d a r e s y n o n y m s
of the tables; "feedI"
is t h e
IThis is due to the definition of the pregnant heifer: female within 3 months before its first calving.
166
F. Lescourret et al. / Computers and Electronics in Agriculture 10 (1994) 151-166
input database table (I means "input"); "cow disease" is a database table harbouring the disease occurrences of cows, which contains the fields cow number, lactation number, date and disease code ("PR" is a code value meaning placental retention); breed is a field of the table "cow" and 66 and 83 two breed values; nucalv, milkdays and drydays are fields of "lactation" and relate to the number of born calves, the number of days in milk and the length of the dry period, respectively. The first three queried items correspond to the primary key of the input table and the last one to the blind numerical attribute "num" (see Section 2.3). insert into feedI select a.cow n u m b e r , a . d a t e - 30, a.date, a.lactation n u m b e r f r o m lactation a, cow b, cow disease c, lactation d where a.cow n u m b e r = b.cow n u m b e r a n d a.cow n u m b e r = c.cow n u m b e r a n d a.cow n u m b e r = d.cow n u m b e r a n d a.lactation n u m b e r = c.lactation n u m b e r a n d a.lactation n u m b e r = d.lactation n u m b e r + 1 a n d disease c o d e - - - " P R " a n d breed in (66, 83 ) and nucalv = 1 a n d a.dat b e t w e e n " 1 - a u g - 8 6 " a n d " 3 1 - d e c - 8 9 " a n d m i l k d a y s > = 30 a n d d r y d a y s > 30
(2) SQL query for using an output database table, together with other tables In the following, a, b, c, d and e are synonyms of tables as previously; "feedO2" is the second output database table, which concerns time distribution of ensiled feedstuffs (O means "output") and contains the fields cow number, starting date, ending date, farm number+analysis date (identifying the ensiled feedstuff, as in the "silage" table) and the blind numerical attribute "num" (here a lactation number); dry period and pregnancy length are attributes of the "lactation" table; "CS" is a code value meaning corn silage; avg is a SQL function meaning "average". select c o u n t (a.cow n u m b e r ) , avg (c.dry period ), avg (a.pregnancy length ), avg ( d . e n d i n g date - d.starting date ) f r o m lactation a, cow b, lactation c, f e e d 0 2 d, silage e where a.cow n u m b e r = b.cow n u m b e r a n d a.cow n u m b e r = c.cow n u m b e r a n d a.cow n u m b e r = d.cow n u m b e r and b.farm number = e.farm number a n d a.lactation n u m b e r = d . n u m a n d a.lactation n u m b e r = c.lactation n u m b e r + 1 a n d d.analysis date = e.analysis date a n d e.type = " C S "