ADOPTIONS contains one row for each adoption of a chimpanzee. For the purposes of the Gombe-MI project an adoption occurs the first time one chimpanzee acts as the mother in a follow to a chimpanzee that is not their offspring.
There is no restriction on the sex of the adopting parent, nor is there a limit on the number of times a chimpanzee can be adopted.
The adopting individual must be older than the adopted individual[6] -- the AParent's BIOGRAPHY_DATA.Birthdate must be before that of the adopted individual.
The date of adoption must be during the time period in which both the adopted individual and the adopting individual are alive -- the ADOPTIONS.ADate value cannot be before the related BIOGRAPHY_DATA.Birthdate value of the ADOPTIONS.ChimpID; the ADOPTIONS.ADate value cannot be after the related BIOGRAPHY_DATA.Departdate value of the ADOPTIONS.ChimpID; the ADOPTIONS.ADate value cannot be after the related BIOGRAPHY_DATA.Departdate value of the ADOPTIONS.AParent.
The adoptive parent cannot be the biological mother of the adopted individual -- the individual's BIOGRAPHY_DATA.MomID value cannot be one of the related ADOPTIONS.AParent values.
An individual cannot be adopted twice by the same adoptive parent.
Either a permanent (ADate) or
a temporary (TmpDate) adoption date
must be present, both dates may not be NULL. When there is
both a permanent adoption date
(ADate) and a temporary adoption
date (TmpDate) the temporary date
must be before the permanent date.
Only permanently adopted individuals
(ADOPTIONS.ADate
is not NULL) generate warnings[7] when not with their adoptive parent in a group when
young, and vice-versa. No such constraints are placed on
group membership when the adoption is temporary. It is the
responsibility of the data entry team to ensure that the
presence of temporarily adopted individuals and their adoptive
parent are recorded correctly in the appropriate group.
Temporary adoptions alter the warnings regarding mother/infant pairing within group composition. For complete information on the group membership warnings surrounding adopted individuals and their adoptive parent see the GROUPMEMBERS table.
A unique integer identifying the adoption. This column is automatically maintained by the
database, cannot be changed, and must not be
NULL.
The virtual or real identifier assigned to the adopted
individual. The ChimpID code of
the adopted individual. The ChimpID value cannot be changed.
This column may not be NULL.
The identifier assigned to the adoptive parent. The
AnimID code of the adopting
individual. The AParent value cannot be changed.
This column may not be NULL.
BIOGRAPHY_DATA contains one row for every chimpanzee. It is a copy of the master file kept in MS Access and described the Gombe Chimp Database Handbook.
It is recommended, and generally more convenient, to use the BIOGRAPHY view. BIOGRAPHY has all the columns of BIOGRAPHY_DATA and a few more.
Two AnimID values have special meaning to Gombe-MI.
The MGE
AnimID value is assigned to
the unknown stranger. The UNK
AnimID value is assigned to
the generic unknown individual. The significance of these codes
is that they may have NULL values in all of their columns
excepting AnimID and
AnimName. This exception is
not mentioned in the column descriptions below.
An individual's Birthdate cannot be before the individual's BDMin. An individual's BDMax cannot be before the individual's Birthdate. An individual's Entrydate cannot be before the individual's Birthdate. An individual's Departdate cannot be before the individual's Entrydate. An individual's Import_Datetime cannot be before the individual's Departdate.
Individuals cannot be observed, neither their AnimID nor any of their related CHIMPIDS.ChimpIDs can appear in the Gombe-MI system, after the individual's BIOGRAPHY.Departdate when the individual's BIOGRAPHY.Departtype indicates final departure from the study population.
The Dad_Prelim and
Dad_Published columns must be
NULL when DadID is NULL and
must not be NULL when DadID is
not NULL.
None of the following columns may be NULL unless all are
NULL: BGCertainty,
FirstBorn,
Birthdate,
BDMin,
BDMax,
BDDist,
BirthGroup ,
Entrydate,
Entrytype,
Departdate,
DepartdateError,
Departtype, and all of the
following columns are null also:
AnimID_Num
MomID
DadID. This allows the creation
of individuals about whom nothing is known excepting their
parentage of offspring.
The unique identifier assigned to the individual. It is composed entirely of upper case letters and digits and is at most 5 characters long.
The AnimID value cannot be changed. This column may not be empty, it must contain characters,
and it must contain at least one non-space character.
This column may not be NULL.
Some AnimID values are reserved and cannot be used to identify individuals within the database. See the documentation on AnimID values with special meaning to the UPLOAD_BEHAVIORS view.
The unique alphanumeric identifier assigned to the
individual for the SIV study. This value may be up to
20 characters long. This column may not be empty, it must contain characters,
and it must contain at least one non-space character. This value
may be NULL when no such identifier exists.
The name of the individual. This name must be unique.
This column may not be empty, it must contain characters,
and it must contain at least one non-space character. This column may not be NULL.
Unvalidated code used to designate the community into which the individual was born. See the Gombe Chimp Database Handbook for further information.
This column may not be empty, it must contain characters,
and it must contain at least one non-space character. This column may be NULL when the birth
community is unknown.
Code used to indicate confidence in the recorded BirthGroup.
BGCertainty Acceptable Values
C
Certain
U
Uncertain
Code indicating the sex of the individual.
Sex Acceptable Values
M
Male
F
Female
U
Unknown
This column may not be NULL.
The AnimID code of the
individual's mother, when known. The mother must be
female.[8]This value may be NULL when the mother is not
known.
The AnimID code of the
individual's father, when known. The father must be
male.[9]This value may be NULL when the father is not
known.
A Boolean value indicating when paternity assignment, the
DadID column value, is
preliminary. This value may be NULL, see above.
Where and when paternity assignment has been published.
This value may be NULL, see above.
Whether or not the individual is the mother's firstborn.
FirstBorn Acceptable Values
Y
Firstborn
N
Not the firstborn
U
May or may not be the firstborn
Minimum birth date. See the Gombe Chimp Database Handbook for further information.
Maximum birth date. See the Gombe Chimp Database Handbook for further information.
Code indicating the birth date probability distribution. See the Gombe Chimp Database Handbook for further information. The legal values for this column are defined by the BDDISTS support table.
Code indicating how the individual entered the study population. See the Gombe Chimp Database Handbook for further information. The legal values for this column are defined by the ENTRYTYPES support table.
Time between Departdate and when the individual was confirmed missing. Units are fractions of a year. Precision is 2 decimal places. This number must be non-negative. For further information see the Gombe Chimp Database Handbook.
Code indicating how the individual left the study population. See the Gombe Chimp Database Handbook for further information. The legal values for this column are defined by the DEPARTTYPES support table.
The date and time of the data import -- the time the row was created. This column defaults to the date and time of the start of the transaction which inserted the row. Hence when all the rows are inserted in a single transaction they will all have the same timestamp. Further, the column will automatically be set to the correct value upon upload when this column is omitted from the uploaded data.
This column is not part of the master Access database.
This column is time zone aware; times are output in, and expected to be input in, your local time zone. This column may not be NULL.
CHIMPIDS contains one row for every chimpanzee, plus additional rows giving twins a “virtual” identifier. The “virtual” identifier is used when the individual's identity is unknown because the twins cannot be distinguished from each other. Use of the “virtual” identifiers indicates that either of the two twins may be the individual in question. This is true even though each twin should, in CHIMPIDS, have only a single virtual identifier (in addition to the regular AnimID). In other words, the association between the “virtual” identifiers and the individual twins is arbitrary.
There must always be a row on CHIMPIDS for each row on BIOGRAPHY_DATA. This required row has a ChimpID value that duplicates the row's AnimID value. These requirements are enforced on transaction commit. The required row ensures that each chimpanzee has an identifier, one equal to the chimp's AnimID, suitable for use throughout the Gombe-MI system.
The content of the CHIMPIDS table is automatically altered under the following circumstances: When a BIOGRAPHY_DATA row is inserted a row is inserted into CHIMPIDS which has a AnimID value and a ChimpID value of the inserted BIOGRAPHY_DATA.AnimID. When a BIOGRAPHY_DATA row is deleted the related rows are deleted from CHIMPIDS. These operations are performed at the time of transaction commit. This feature should eliminate any need for manual maintenance of the CHIMPIDS table, except in the case of twins.
Often the CHIMPIDS table can be ignored; when writing queries ChimpID values in database tables may be joined directly with BIOGRAPHY_DATA.AnimID. When this is done rows which contain the “virtual” twin identifiers will disappear from the query results. Whether this is desirable depends on the intended goal.
A second way to ignore the CHIMPIDS table is to use the CHIMPS view instead of joining to BIOGRAPHY_DATA or BIOGRAPHY. In this case, when counting, care must be taken to count distinct AnimID values to avoid double-counting twins.
The “virtual” identifiers must be unique -- each ChimpID value must be related to exactly one AnimID value. The twins alternate identifiers cannot be used as AnimID values on BIOGRAPHY_DATA -- ChimpID values that differ from their row's AnimID values cannot be BIOGRAPHY_DATA.AnimID values.
There is no rule requiring that twins (as defined within the TWINS table) have “virtual ids” defined on CHIMPIDS, nor is there a rule which requires that every “virtual id” be a twin, but violating these constraints may result in a system which behaves strangely.[10]
There are no rules in the system which require that twins must be referenced either by their “normal” AnimID values or by their “alias” ChimpID, used when the twins cannot be distinguished, but not by a mixture of both. It is allowed to reference one twin by their “normal” AnimID and the other by the twin's “alias” ChimpID, although this is almost surely a bad idea. Data hygiene in this regard is left to the system's users.
A 5 character identifier for
the “virtual” individual. This value cannot be
changed. This column may not be NULL.
Some ChimpID values are reserved and cannot be used to identify individuals within the database. See the documentation on AnimID values with special meaning to the UPLOAD_BEHAVIORS view.
The AnimID code of the
individual. This value cannot be changed. This column may not be NULL.
COMMUNITY_MEMBERSHIP contains one row for every contiguous period of time each individual has spent in each community. It is a copy of the master file kept in Access. Since record keeping involving transition between communities is non-obvious users are encouraged to read the description found in the Gombe Chimp Database Handbook.
The combination of AnimID and Start_date must be unique.
The data in this table, AnimID excepted, is entirely unvalidated.
The unique identifier assigned to each individual. The
AnimID code of the individual
who's community tenure the row represents. This column may not be NULL.
Unvalidated code identifying the community or pair of
communities of which the individual was a part.
This column may not be NULL.
Textual description of how the start date was determined.
This column may not be NULL.
Textual description of how the end date was determined.
This column may not be NULL.
The date and time of the data import -- the time the row was created. This column defaults to the date and time of the start of the transaction which inserted the row. Hence when all the rows are inserted in a single transaction they will all have the same timestamp. Further, the column will automatically be set to the correct value upon upload when this column is omitted from the uploaded data.
This column is not part of the master Access database.
This column is time zone aware; times are output in, and expected to be input in, your local time zone. This column may not be NULL.
TWINS contains one row for every individual who is a twin. Twins are required to occur in pairs, so for every AnimID/Twin pair of values there must also be a row containing a corresponding reversed pair of values.[11]This rule is enforced on transaction commit.
Insert rows into the TWINS table in pairs, in a single transaction
A unique integer identifying the twin. This column is automatically maintained by the
database, cannot be changed, and must not be
NULL.
The identifier assigned to the individual who is a twin.
The AnimID code of the
individual. This value must be unique, no other row in the
table may share an AnimID value. This value cannot be changed.
This column may not be NULL.
The identifier assigned to the twin sibling of the
individual. The AnimID code of
the individual's twin. This value must be unique, no other row
in the table may share a Twin value. This value cannot be
changed. This column may not be NULL.
[6] An additional age requirement may be worthwhile; the adoptive parent could be a mere 1 day older than the adopted youth.
[7] Generated by the warning system.
[8] There are a number of other checks which make sense, and are desirable; checking that the mother is old enough to have given birth, not yet dead, etc. However, we're leaving this to the upstream data maintainers.
[9] See the footnote above regarding MomID.
[10] Then again, it may not behave strangely. It is up to you to understand how the system works, the implications of your choices, and to determine how the system best meets your needs.
[11] To be through, there should also be rules which say twins must have identical birthdays, mothers, etc. There are no such rules.