Agritec Variable Dictionary

Introduction

Ovitec uses a standard database model called relational. A relational database system contains one or more objects called tables. The data or information for the database is stored in these tables. Tables are uniquely identified by their names and are comprised of columns and rows. Columns contain the column name, or variable, data type, and any other attributes for the column. Rows contain the records, or data, for the columns. Here is a sample table called "Customers".

Name, surname, address, city and phone are the variables. The rows contain the data for this table:

Female table

Each record is a female; by default it shows the current status of the female and refers to the current parity.

Parity table

Each record is a parity. The data of each record refers to a specific parity.

Service table

Each record is a service. The data of each record refers to a specific service.

PWDeath table

Each record is a preweaned death event.

Treatment table

Each record is a treatment event.

Mating table

Each record is a mating event.

Milktest table

Each record is a milk test.

Lactation table

Each record is a lactation.

Performance table

Each row is a period of time defined by the user. This table includes all statistical data incurred in the breeding herd.

Some of these primary tables are used to build more tables, facilitating access to information:

FemaleEx Table

FemaleEx table joins the columns of the Female+Parity+Service+Treatment tables. Each record is a female, including the current parity, the last service and the last treatment.

ParityEx Table

ParityEx table joins the columns of the Female+Parity+Service tables. Each record is a parity, including the female, the parity and the last service in the parity.

ServiceEx Table

ServiceEx table joins the columns of the Female+Parity+Service tables. Each record is a service, including the female, the parity and the service.

ServiceResult Table

ServiceResult table joins the the Female+Parity+Service tables. Each record is a service, and includes the female, the service and the next parity of the service row. Note that ServiceResult occur in the next parity, so if you need information pertaining to a service outcome use ServiceEx table.

PwDeathEx Table

PwDeathEx table joins the columns of the Female+Parity+PwDeath tables. Each record is a pre-weaned death event, and includes the female and the parity table information.

TreatmentEx Table

TreatmentEx table joins the columns of the Female+Parity+Treatment tables. Each record is a treatment, and includes the female and the parity table information.

For example, if you need the information about one female (ID, genetics, ...) to include all her parities (lambing date, liveborn, ...) you need to use the ParityEx table instead of FemaleEx table.

A female record from the Female table can have several parity records in the Parity table, and in turn a Parity record can have several service records in the Service table.

For example, the following SQL sentence:

SELECT Id, Location, Parity FROM ParityEx

This sentence selects a data set where each row is a single parity and there are 3 columns: Id, Location and Parity. It will show:

Location variable shows the current location because this field belongs to the Female table. Female table always refers to the current parity or status of the female. In the above example, the Location value is always 235 for the ID 45 because in all cases, it refers to the current value.

Now, we want to see all service dates. In this case Service table will be used.

SELECT Id, Location, Parity, ServDate FROM ServiceEx

Each row is a service

Note that.Parity variable is valued at 1 for the first 2 services: 9Jan03 and 21Jan03. These 2 services occurred in parity 1. Parity 2 includes only one service on 15Jun03, and finally parity 3 includes 2 services, 30Nov and 15Dec.

Introduction to relational databases:

http://www.computerbits.com/archive/2003/0400/introdatabases.html

Tutorials about SQL:

http://www.geocities.com/SiliconValley/Vista/2207/sql1.html

http://www.firstsql.com/tutor2.htm