Gombe Mother-Infant Database Technical Specifications

Gombe Mother-Infant Database Technical Specifications Karl O. Pinc kop@meme.com The Meme Factory, Inc. Copyright Notices Copyright (C) 2012-2015 The Meme Factory, Inc., Elizabeth V. Lonsdorf, PhD., Carson M. Murray, PhD.

Except as otherwise noted permission is granted to copy, distribute and/or modify this document under the terms of the GNU Free Documentation License, Version 1.3 or any later version published by the Free Software Foundation; with no Invariant Sections, no Front-Cover Texts, and no Back-Cover Texts. A copy of the license is included in . All of the code contained in this document, including but not limited to the code presented in examples and figures, is, at your option, dual licensed; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; either version 3 of the License, or (at your option) any later version. A copy of this license is include in .

The appendix titled &transaction_appendix; is Copyright (C) 1996-2011 by the PostgreSQL Global Development Group, distributed under the terms of the license of the University of California below. Permission to use, copy, modify, and distribute this software and its documentation for any purpose, without fee, and without a written agreement is hereby granted, provided that the above copyright notice and this paragraph and the following two paragraphs appear in all copies. IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY PARTY FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN "AS-IS" BASIS, AND THE UNIVERSITY OF CALIFORNIA HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.

is Copyright (C) 2000, 2001, 2002, 2007, 2008 Free Software Foundation, Inc.

is Copyright (C) 2007 Free Software Foundation, Inc.

All declarations of copyright within the files of this project may, in place of an explicit listing of all years for which copyright is claimed, instead list a range of years in the manner XXXX-YYYY to designate that copyright is claimed from year XXXX through year YYYY, inclusive of both endpoints. Acknowledgments The Gombe-MI Development Group Karl O. Pinc kop@meme.com The Meme Factory, Inc. Book Author, System Design Lead, Implementation Carson M. Murray, PhD. cmmurray@gwu.edu George Washington University http://departments.columbian.gwu.edu/anthropology/people/317 Project Co-Leader, System Design Core Member Elizabeth V. Lonsdorf, PhD. elizabeth.lonsdorf@fandm.edu Franklin & Marshall College http://www.fandm.edu/elizabeth-lonsdorf Project Co-Leader, System Design Core Member Karen Anderson System Design Core Member, Copy Review, System Testing Lead A. Catherine Markham, PhD. Stony Brook University http://www.stonybrook.edu/commcms/anthropology/faculty/cmarkham.html System Design Participant Margaret A. Stanton, PhD. George Washington University http://cashp.columbian.gwu.edu/margaret-stanton System Design Participant, System Testing Core Member Edward Wilkerson, Jr. Lincoln Park Zoo http://www.lpzoo.org/conservation-science/resources/staff-bios/edward-wilkerson System Design Participant Funding and Support We gratefully acknowledge the support of The National Institutes of Health grant R00HD057992 for the development of this system. We are also very grateful for the support given by The Leo S. Guthman Foundation, the Lincoln Park Zoo, Franklin & Marshall College, and The George Washington University. Other Thanks We would like to thank the myriad Free and Open Source communities, including those of PostgreSQL, the GNU Project, the Debian Project, Ubuntu, PhpPgAdmin, the Pyramid web framework, TeX and LaTeX, dblatex, DocBook, Babase, and many others unmentioned, for giving, gratis, billions of dollars See: Estimating the Total Development Cost of a Linux Distribution. of work to the world, without which the Gombe-MI software and this book would not exist. Any opinions, findings, conclusions or recommendations expressed in this material are those of the author(s) and do not necessarily reflect the views of the National Institutes of Health, The Leo S. Guthman Foundation, the Lincoln Park Zoo, Franklin & Marshall College, The George Washington University, or any other organization which has supplied support for this work. Gombe-MI System Architecture

Databases Databases are collections of information, all of which can be queried and otherwise manipulated alone or in aggregation with all other database content. As security restrictions permit, of course. Gombe-MI contains three databases.

The <literal>gombemi</literal> Database The gombemi database contains the real information. All research takes place in this database.

The <literal>gombemi_test</literal> Database The gombemi_test database contains a copy of the gombemi database. It is a place to try out dangerous things that might break the gombemi database.

The <literal>gombemi_copy</literal> Database The gombemi_copy database contains a few bits of made up information. It is a place to try out random things and a place where the gombemi developers can work on alterations and enhancements.

Roles and Database Permissions Each user is given a role for their own personal use (commonly called a login or username) and a password they must use to gain access to the database. It is good form to change your password occasionally. That way if you unwittingly revealed your password last weekend, after that fourth bottle of wine you just knew was a mistake, by the time everybody sobers up the password will have been changed and the amount of damage done is limited. The database can grant specific users (via each user's personal role, aka login) various levels of access to specific tables, although such access is not common as it is difficult to administer and maintain such a fine grained degree of control. For further information see the &postgresql; documentation on Database Roles. Rather than maintain database access privileges on a per-user basis it is more convenient to grant users additional roles, roles shared by many users, and then grant these roles different levels of database access. Gombe-MI contains the following (shared) roles:

The gombemi_readers role The members of this shared role have read access to Gombe-MI data but cannot add, delete, or otherwise alter any of the data.

The gombemi_editors role The members of this shared role have unlimited rights to the Gombe-MI data. They may add data, delete data, or alter existing data. They may not, however, alter the structure of the gombemi database or change the rules to which the data are required to conform. Thus, they may not add or delete tables, alter triggers, or write or replace stored procedures.

Administrative (Superuser) Users An administrative user has no restrictions; these users can do things like re-assign ownership of all database tables, all schemas, and all databases; assign to or remove roles from all usernames; create and destroy usernames, including other administrative users; and create and destroy all tables, all schemas, and all databases. In short, administrative users are all-powerful. Needless to say, administrative usernames should be meted out sparingly. They should also be used sparingly. Every person who has an administrative username should also have an ordinary username. The ordinary username carries much less risk of inadvertent, subtle or not-so-subtle, destruction. Login as an administrative user only when absolutely necessary.

Schemas Schemas partition databases. Tables, procedures, triggers, and so forth are all kept in schemas. Schemas are like sub-databases within a database. The salient difference between schemas and databases is that a single SQL statement can refer to objects in the different schemas of the parent database, but cannot refer to objects in other databases -- tables within a database can be related, but tables in different databases cannot. Gombe-MI uses schemas to partition each database into areas where users have a greater or lesser degree of freedom to make changes. For further information on schemas see the schema documentation for &postgresql;. Each database is divided into the same schemas. That is, each schema described below exists within each of the databases described here. The system looks at the different schemas for objects, for example table names appearing in SQL queries, in the order in which the schemas are listed below. If the table does not appear in the first schema it looks in the second, and so forth. As soon as a table is found with the name given, that table is used and the search stops. To explicitly reference an object in a specific schema, place the name of the schema in front of the object, separating the two with a period (e.g. schemaname.tablename).

The gombemi schema The gombemi schema holds the official Gombe-MI tables. Everything in the gombemi schema is documented and supported. In this schema the gombemi_readers and gombemi_editors have the access described above.

The gombemi_<wordasword>something</wordasword>_views schemas Gombe-MI contains a number of schemas that exist to simplify things for those interested only in particular portions of Gombe-MI. These schemas contain nothing but views that reference other parts of Gombe-MI (both tables and views), the parts that are especially relevant and useful to those interested only in one of the broad categories of Gombe-MI data. These schemas and their corresponding categories are: The categories of Gombe-MI data and their schemas Schema Category gombemi_support_views Support Table Related gombemi_demography_views &demography_section; gombemi_follow_views &follow_section; gombemi_event_views &event_section; gombemi_distance_views &distance_section; gombemi_group_views &group_section; These schemas provide an overview of the major areas of Gombe-MI. They should be especially useful to those starting out with Gombe-MI or those interested only in particular portions of Gombe-MI data. The views in these schemas may only be queried. Any updating of Gombe-MI data must be done in the gombemi schema. Some of Gombe-MI's tables and views appear in more than one of these schemas, some in none. Do not create any views that reference the views in these schemas. Reference the gombemi schema instead. Any views created that reference anything in these category schemas will be destroyed on occasion as Gombe-MI is modified.

The sandbox schema The sandbox schema holds tables that are used together with the official Gombe-MI tables but have not yet made it into the Gombe-MI project. They will not be documented in the Gombe-MI documentation. The shared roles have the following permissions:

gombemi_readers permissions in the sandbox schema The gombemi_readers have all the permissions in the sandbox schema that the gombemi_editors have in the gombemi schema. They may add, delete, or modify any information in the schema but may not alter the structure of the schema by adding or removing tables, procedures, triggers, or anything else.

gombemi_editors permissions in the sandbox schema The gombemi_editors have all the permissions of the gombemi_readers, plus they may add or delete tables, stored procedures, or any other sort of object necessary to control the structure of the data. Because of the schema search order the schema name must be used to qualify anything created in the sandbox schema. E.g. Creating table <code>foo</code> in the sandbox schema CREATE TABLE sandbox.foo (somecolumn INTEGER); &postgresql;, the database underlying Gombe-MI, is secure by default. This means that any tables or other database objects cannot be accessed by anyone but their creator without permission of the creator. gombemi_editors who create tables in the sandbox schema should use the GRANT statement to grant access to Gombe-MI's other users. This is done as follows: Granting permission to table <code>foo</code> in the sandbox schema

GRANT ALL ON sandbox.foo TO ROLE gombemi_editors;
GRANT SELECT ON sandbox.foo TO ROLE gombemi_readers;

There is one other issue. Only the creator of a table can change its structure -- to add another column, change the table name, etc. And only the creator can destroy (DROP) the table.

The devel schema The devel schema holds tables undergoing integration into Gombe-MI. Normally it is empty, but during the design and development of new tables it may contain the tables being developed. The tables in this schema do not necessarily contain valid or finalized data and so are not expected to be used for other than developmental purposes. Permissions are granted in the devel schema on the same basis as the granting of permissions in the gombemi schema. The difference between this schema and the sandbox schema is that the development tools support the creation and modification of the tables in the devel schema, which facilitates the movement of tables from the devel schema into the gombemi schema.

The per-user schemas Each user has her own schema, a schema named with the user's login. Users have permissions to do anything they want in their own schemas, and no permissions whatsoever to anybody else's schema. A user's schema is private. Users are not encouraged to grant others permissions to the tables in their schema, as shown in the above. A user's schema is deleted when she leaves Gombe-MI. All shared tables belong in the sandbox schema where they can be maintained without regard to personnel changes. Because of the schema search order the schema name must be used to qualify anything created in the user's schema. E.g. Creating table <code>foo</code> in user <code>mylogin</code>'s schema CREATE TABLE mylogin.foo (somecolumn INTEGER);

Tables, A Definition The data in Gombe-MI are stored in tables. Tables can be visualized as grids, with rows and columns. Each row represents a single real-world thing or event, an entity, e.g. a chimpanzee. Each cell in the row contains a single unit of information, e.g. a birth date, a name, and a sex. The row holds the entirety of the information belonging to the entity as an isolated thing, e.g. chimpanzee database entities consist of a birth date, a name, and a sex. Each column contains one and only one kind of information, e.g. birth date. is an example of a database table that might be used to represent chimpanzees, one chimpanzee per row. Notice that each cell contains one and exactly one unit of information. A Simple Database Table Birth Name Sex May 23, 1707 Alice Female February 12, 1809 Bob Male July 22, 1822 Carol Female

Anyone working with Gombe-MI will require a familiarity with the database's tables. An understanding of the entity each row represents is critical when working with a table. The remainder of this section provides short definitions of the entities each table holds in its rows. Some of the tables in Gombe-MI exist to define a vocabulary. These are the support tables. For lack of a better term, the remainder of the tables are labeled main tables in the .

Queries Querying is done using a language called SQL. A description of SQL is beyond the scope of this document but one, somewhat more advanced, SQL feature is worth mention. Because the Gombe-MI system stores all data of a like kind together, in one table, it is often desirable to reference the same table (or table-like structure) more than once in a single query. The SQL AS syntax can be used to give a table an alternate name used elsewhere within the query, allowing a single table to be referenced multiple times within a single query. For example, self-grooming data can be queried from the view. To count the number of times both the mother and the infant simultaneously self groom the following query uses AS so that can be used twice: Using <literal>AS</literal> in an SQL query: Reference ASOCIAL_EVENTS twice to count the number of times a mother and infant simultaneously self-groom Using AS in an SQL query: Counting simultaneous self-grooming

SELECT COUNT(*)
  FROM asocial_events AS mom_events
     , asocial_events AS infant_events
  WHERE mom_events.intid = infant_events.intid      -- Same interval
        AND mom_events.function = '&gmi_mom;'       -- Mom is mom
        AND mom_events.behavior = 'G'
        AND infant_events.function = '&gmi_infant;' -- Infant is infant
        AND infant_events.behavior = 'G';

The use of AS in queries, or other, more cumbersome techniques such as the creation of temporary tables, reduces the number of views which must be created and maintained.

An Overview Of The Gombe-MI Data The tables in Gombe-MI are divided into two sorts, those that hold data of primary interest, the Main Tables, and those who's primary purpose is to provide a controlled vocabulary for use throughout the rest of the system, the Support Tables. Support tables are also often called lookup tables. The relationships between the main tables are diagrammed in entity relationship diagrams. These diagrams provide a concise overview of the Gombe-MI system and are an essential aid to complete understanding. Because Gombe-MI is as data-driven as possible, wherever possible the system relies on the use of user-supplied vocabulary for it's semantics, an understanding of the codes defined by the support tables is essential to the use of the system In addition to the underlying data tables Gombe-MI also contains views, a database feature which allows a query to masquerade as a table. Views can present a simple interface to a complex data structure, one which is tailored to a particular purpose. This allows the underlying data structure to be as general-purpose as possible and so able to accommodate complex relationships and efficiently process arbitrary queries. At the same time the views allow straightforward access for the novice. Because view are often composed of a query into multiple underlying data tables each view has an entity relationship diagram which describes the relationships between the tables used to construct the view. Gombe-MI also consists of a number of programs. Some of these are generic, available from third parties for use on any system, and some which have been developed specifically for the Gombe-MI project. The latter are kept to a minimum to reduce cost and leverage the use of pre-existing software whenever possible.

ER Diagrams ER diagrams, Entity-Relationship diagrams, graphically show the relationships between a database's tables. ER diagrams contain a lot of information and so provide a useful high-level overview of the data contained within the database. They are particularly useful when writing queries, if there is already a general understanding of what the rows in each table represent.

ER Diagrams Explained Most tables have have an id, or key, column that contains a number unique to that row within its table. The id can be used, in perpetuity, to refer to its related row and distinguish it from all the other rows of the table. Ids are arbitrary, although for convenience they are often sequentially generated integers. A relationship is established between the rows of two tables when an id value from one table appears as data in the other. The relationship notion is made most clear by way of diagrams and examples. If the next paragraph is unclear, don't worry. Have a look at the Gombe-MI diagrams below by way of example and see if that does not clear things up. The relationship concept is at the heart of relational databases and, while the underlying idea is rather simple, it took many years to develop relational database concepts so don't expect a full understanding immediately. When an id value of a row in one table appears as data in a second table, the data in the second table can be used to retrieve the identified row from the first table. And the reverse is true. The id of a row in the first table can be used to find the row in the second table that holds it. When an id value of a row in the first table appears as data only once in the second table, the two tables are said to have a one-to-one relationship. One row in the first table relates to one (or possibly zero) row(s) in the second table. When a row's id value can appear in more than one row of a second table, the two tables are said to have a one-to-many relationship. One row of the first table can be related to many rows in the second table. One-to-many relationships are more common than one-to-one relationships. The relationship between the various Gombe-MI tables can be visualized in entity relationship diagrams, as shown here. In this diagram each table (entity) is a box, and each box contains a list of the table's columns. The lines between the boxes represent the relationships between the tables. If you have trouble viewing the diagrams in your browser, you may wish to view them in PDF format. The diagrams are available in The Gombe-MI Pocket Reference in PDF form.

Indexes Indexes are a feature of databases, a feature which greatly speeds data retrieval. In return there is a small cost in the time it takes to change table content, and cost in disk space used. Databases generally require indexes to perform efficiently. It is a good idea to index the tables each user has in their personal schema. There is no documentation on the indexes used in Gombe-MI. In general, there is an index for each way the tables are commonly referenced. For example, if records are often looked up on the basis of date, there will be an index on the date. As a practical guide, there is an index on each of the columns at the endpoint of a relational line in the above entity-relationship diagrams, as well as an index on every date column. All indexes are b-tree indexes.

Main Tables Unless otherwise mentioned, all time values in Gombe-MI are stored in a database data type which ignores the time zone of the database user in Nairobi local time.

&demography_section; Tables

ADOPTIONS 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 An additional age requirement may be worthwhile; the adoptive parent could be a mere 1 day older than the adopted youth. -- the 's . 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. value cannot be before the related . value of the ADOPTIONS.; the ADOPTIONS. value cannot be after the related . value of the ADOPTIONS.; the ADOPTIONS. value cannot be after the related . value of the ADOPTIONS.. The adoptive parent cannot be the biological mother of the adopted individual -- the individual's . value cannot be one of the related . values. An individual cannot be adopted twice by the same adoptive parent. Either a permanent () or a temporary () adoption date must be present, both dates may not be &null;. When there is both a permanent adoption date () and a temporary adoption date () the temporary date must be before the permanent date. Only permanently adopted individuals (. is not &null;) generate warnings Generated by the warning system. 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 table.

AdoptID (Adoption Identifier) A unique integer identifying the adoption. &autoid;

ChimpID (Adopted Individual's Chimp Identifier) The virtual or real identifier assigned to the adopted individual. The code of the adopted individual. The ChimpID value cannot be changed. ¬null;

AParent (Adopting Parent) The identifier assigned to the adoptive parent. The code of the adopting individual. The AParent value cannot be changed. ¬null;

ADate (Date of Permanent Adoption) The date of the adoption, when the adoption is permanent.

TmpDate (Date of Temporary Adoption) The date of the adoption, when the adoption is temporary.

BIOGRAPHY_DATA (Copy of the Master Biography Access Table) BIOGRAPHY_DATA contains one row for every chimpanzee. It is a copy of the master file kept in MS Access and described the &gcdbh;. It is recommended, and generally more convenient, to use the view. has all the columns of BIOGRAPHY_DATA and a few more. Two AnimID values have special meaning to Gombe-MI. The &gmi_stranger; value is assigned to the unknown stranger. The &gmi_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 and . This exception is not mentioned in the column descriptions below. An individual's cannot be before the individual's . An individual's cannot be before the individual's . An individual's cannot be before the individual's . An individual's cannot be before the individual's . An individual's cannot be before the individual's . Individuals cannot be observed, neither their nor any of their related .s can appear in the Gombe-MI system, after the individual's BIOGRAPHY. when the individual's BIOGRAPHY. indicates final departure from the study population. The and columns must be &null; when is &null; and must not be &null; when is not &null;. None of the following columns may be &null; unless all are &null;: , , , , , , , , , , , , and all of the following columns are null also: . This allows the creation of individuals about whom nothing is known excepting their parentage of offspring.

AnimID (Animal Id) The unique identifier assigned to the individual. It is composed entirely of upper case letters and digits and is at most &gmi_animid_len; characters long. The AnimID value cannot be changed. &emptytext; ¬null; Some values are reserved and cannot be used to identify individuals within the database. See the documentation on AnimID values with special meaning to the view.

AnimID_Num (Animal Id Number) The unique alphanumeric identifier assigned to the individual for the SIV study. This value may be up to &gmi_animid_num_len; characters long. &emptytext; This value may be &null; when no such identifier exists.

AnimName (Animal Name) The name of the individual. This name must be unique. &emptytext; ¬null;

BirthGroup (Birth Group) Unvalidated code used to designate the community into which the individual was born. See the &gcdbh; for further information. &emptytext; This column may be &null; when the birth community is unknown.

BGCertainty (Birth Group Certainty) Code used to indicate confidence in the recorded . BGCertainty Acceptable Values &gmi_bgcertain; Certain &gmi_bguncertain; Uncertain

Sex (Sex) Code indicating the sex of the individual. Sex Acceptable Values &gmi_male; Male &gmi_female; Female &gmi_unk_sex; Unknown ¬null;

MomID (AnimID of Mother) The code of the individual's mother, when known. The mother must be female. 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. This value may be &null; when the mother is not known.

DadID (AnimID of Father) The code of the individual's father, when known. The father must be male. See the footnote above regarding . This value may be &null; when the father is not known.

Dad_Prelim (Paternity is Preliminary) A Boolean value indicating when paternity assignment, the column value, is preliminary. This value may be &null;, see above.

Dad_Published (Paternity Publication Information) Where and when paternity assignment has been published. This value may be &null;, see above.

FirstBorn (Firstborn) Whether or not the individual is the mother's firstborn. FirstBorn Acceptable Values &gmi_fb; Firstborn &gmi_not_fb; Not the firstborn &gmi_unk_fb; May or may not be the firstborn

Birthdate (Birthdate) The individual's birth date. When not known this is estimated.

BDMin (Minimum Birth Date) Minimum birth date. See the &gcdbh; for further information.

BDMax (Maximum Birth Date) Maximum birth date. See the &gcdbh; for further information.

BDDist (Birth Date Distribution Code indicating the birth date probability distribution. See the &gcdbh; for further information. The legal values for this column are defined by the support table.

Entrydate (Entry Date) Date of entry into the study.

Entrytype (Entry Type) Code indicating how the individual entered the study population. See the &gcdbh; for further information. The legal values for this column are defined by the support table.

Departdate (Depart Date) Date last seen in the study population.

DepartdateError (Departure Date Error Factor) Time between 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 &gcdbh;.

Departtype (Depart Type) Code indicating how the individual left the study population. See the &gcdbh; for further information. The legal values for this column are defined by the support table.

Import_Datetime (Date and Time of Data Import) 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. &time_zoned; ¬null;

CHIMPIDS (Virtual and Real Chimp Identifiers) 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 ). 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 . This required row has a value that duplicates the row's value. These requirements are enforced on &transaction; commit. The required row ensures that each chimpanzee has an identifier, one equal to the chimp's , suitable for use throughout the Gombe-MI system. The content of the CHIMPIDS table is automatically altered under the following circumstances: When a row is inserted a row is inserted into CHIMPIDS which has a value and a value of the inserted .. When a 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 values in database tables may be joined directly with .. 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 view instead of joining to or . In this case, when counting, care must be taken to count distinct values to avoid double-counting twins. The virtual identifiers must be unique -- each value must be related to exactly one value. The twins alternate identifiers cannot be used as values on -- values that differ from their row's values cannot be . values. There is no rule requiring that twins (as defined within the 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. 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. There are no rules in the system which require that twins must be referenced either by their normal values or by their alias , used when the twins cannot be distinguished, but not by a mixture of both. It is allowed to reference one twin by their normal and the other by the twin's alias , although this is almost surely a bad idea. Data hygiene in this regard is left to the system's users.

ChimpID (Chimp Real or Virtual Identifier) A &gmi_animid_len; character identifier for the virtual individual. This value cannot be changed. ¬null; Some values are reserved and cannot be used to identify individuals within the database. See the documentation on AnimID values with special meaning to the view.

AnimID (Animal Identifier) The code of the individual. This value cannot be changed. ¬null;

COMMUNITY_MEMBERSHIP (Copy of the Master Community_Membership Access Table) COMMUNITY_MEMBERSHIP (Copy of the Master Community_Membership Table) 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 &gcdbh;. The combination of and must be unique. The data in this table, excepted, is entirely unvalidated.

AnimID (Animal Identifier) The unique identifier assigned to each individual. The code of the individual who's community tenure the row represents. ¬null;

Start_date (Start Date) Date of entry into community (inclusive). ¬null;

End_Date (End Date) Date of exit from the community (inclusive). ¬null;

Community_Id (Community Identifier) Unvalidated code identifying the community or pair of communities of which the individual was a part. ¬null;

Start_source (Source of Start Date) Textual description of how the start date was determined. ¬null;

End_source (Source of End Date) Textual description of how the end date was determined. ¬null;

TWINS TWINS contains one row for every individual who is a twin. Twins are required to occur in pairs, so for every / pair of values there must also be a row containing a corresponding reversed pair of values. To be through, there should also be rules which say twins must have identical birthdays, mothers, etc. There are no such rules. This rule is enforced on &transaction; commit. Insert rows into the TWINS table in pairs, in a single &transaction;

TwinID (Twin Identifier) A unique integer identifying the twin. &autoid;

AnimID (Animal ID) The identifier assigned to the individual who is a twin. The 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. ¬null;

Twin The identifier assigned to the twin sibling of the individual. The 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. ¬null;

&follow_section; Tables

FIELDFOLLOWS FIELDFOLLOWS contains one row for each package of Mother-Infant follow data put together by any one field researcher during the course of a day. There is one field follow for every researcher for every mother for each day for which the researcher collected data on the mother (and her infant). The mother (or infant) need not be under continuous observation for the interrupted observations to be part of a single field follow, in fact each row in FIELDFOLLOWS represents an entire day's worth of a researcher's observations of a mother (and infant). All the field follows, irrespective of the researcher in the field, which record data on any one mother (and her offspring) in any one day are combined for analysis into an analytic follow, usually referred to herein just as a follow, and are represented in the database as rows in the table. Each row in FIELDFOLLOWS must have at least one related row on . An observer cannot be in two places at the same time -- the combination of , ., and . must be unique. The date of the follow, the . value, cannot be after the date of data entry, the value. Likewise, the date of data entry, the value, cannot be after the date the data is uploaded into the Gombe-MI system, the value. The date of data entry, , may not be &null; when the . is after &gmi_max_null_entrydate;. The column must be non-&null; when there is any group composition information recorded in the database which is related to the field follow, otherwise it must be &null;. This is tested for by checking for the existence of rows in the table related by to the table, related by to the FIELDFOLLOWS table. This check is performed upon &transaction; commit.

FFID (Fieldfollow Identifier) A unique integer identifying the field follow. &autoid;

Collector (Field Observer) Code identifying the person who observed the follow and recorded it's information on the template sheets. The legal values for this column are defined by the support table. ¬null;

Entrydate (Date of Data Entry) The date on which the field follow's data entry was finished. This column may sometimes be &null;, see above.

Enterer (Data Entry Person) Code identifying the person who entered the field follow information recorded on the template sheets into Gombe-MI. The legal values for this column are defined by the support table. ¬null;

UploadDate (Date of Data Upload) The date on which the field follow's data was uploaded into the Gombe-MI system. When values for this column are not supplied as part of the upload process, or a &null; is supplied as a data value, the UploadDate value defaults to the current date. ¬null;

GCStatus (Group Composition Status) Code indicating the status of all of the the group composition surveys (if any) related to the field follow. This column may be &null; when there is no related group composition information.

FOLLOWPARTS (Follow Participants) FOLLOWPARTS contains one row for every Mother-Infant follow for every individual in the family that is followed. There are at least 2 participants in every follow: there must be exactly one row for the mother and at least one row for an infant. These rules are enforced on &transaction; commit. Each participant in a follow must be a member of the study population on the date of the follow. The . cannot be after the follow's . value. The . cannot be before the follow's .. The mother in a follow must be the mother of the infant (either biological or an adoptive mother) and, similarly the mother of, if any, the sibling -- the . of the offspring, or one of the offspring's related . values, must be the . of the mother, where the role of mother and infant or sibling is determined based on . value and the value is determined using the table. When the mother is an an adoptive parent then the date of the follow cannot be before the adoption date -- the . value cannot be before the . value. The sibling, if any, in a follow must be older than the infant. Two participants in a follow may have the same role Technically, may have the same . value. (e.g. twin) if and only if the two participants have the same birth date. Or if any of the birth dates involved are &null;. When twins are both participants in one follow the two twins must share a role in the follow -- must have identical values. The individual(s) in the follow with the role of infant or sibling must be less than &gmi_notmom_maxage; old on the date of the follow. There are no checks to ensure that the infant in the follow is the youngest living offspring, or that the sibling is the next youngest. An individual cannot participate twice in a single follow -- there cannot be 2 FOLLOWPARTS rows with the same and related values.

FPID (Followparts Identifier) A unique integer identifying the participant in the follow. &autoid;

FollowID (Follow Identifier) The integer identifying the follow of the participant. The FollowID value cannot be changed, the row must be deleted and a new row inserted in it's place. ¬null;

ChimpID (Chimp Virtual Identifier) The identifier assigned to the follow participant. The code of the individual. ¬null;

Role Code indicating the individual's role in the follow. The legal values for this column are defined by the support table. ¬null;

FOLLOWS FOLLOWS contains one row for every Mother-Infant follow. There is one follow for every mother for each day for which there is a record; a chimpanzee cannot be the focal in more than one follow on any given day. The mother (or infant) need not be under continuous observation for the interrupted observations to be part of a single follow, nor do all observations need to be made by the same person. Each follow must have at least one time interval during which observation was made -- each FOLLOWS row must be at least one related row on . Note however that nothing need be observed during a follow. Although the row must exist there is no requirement that an row have any related data, so the system does not require any behavioral or inter-focal distance data. This check is performed upon &transaction; commit. Deleting a row from the FOLLOWS table will delete the entire follow -- the system will first delete all related and rows, which will in turn delete dependent rows from other tables.

FollowID (Follow Identifier) A unique integer identifying the follow. &autoid;

Date The date of the follow. This date cannot be before &gmi_first_date;. ¬null;

INTERVALS (Follow Observation Time Intervals) INTERVALS contains one row for each interval of time during which observations were recorded as part of a follow. At the time of this writing all follow observation intervals are of 1 minute duration. The Gombe-MI system contains elements which rely on per-minute granularity. See for a list of the assumptions regarding interval duration. The time intervals recorded on any given template sheet must be part of a single follow -- all of the . values must be identical for those rows having a given value. A follow cannot have overlapping intervals of observation -- the combination of and must be unique. All of the time intervals related to a given group composition survey must be part of a single follow -- all of the . values must be identical for those rows having a given value. Deleting a row from the INTERVALS table will delete entire interval -- the system will first delete all related , , and rows, which will in turn delete dependent rows from other tables.

IntID (Interval Identifier) A unique integer identifying an interval of observation taken during a particular follow. &autoid;

FollowID (Follow Identifier) The integer identifying the follow to which the observation interval belongs. ¬null;

SheetID (Sheet Identifier) The integer identifying the template sheet on which the interval observation was recorded. The SheetID value cannot be changed. ¬null;

Time (Start Time of the Interval) The time at the start of the interval of observation. The stored value has a one minute precision; the seconds portion of the Time value must be 0. The Time value cannot be before &gmi_day_start; and cannot be after &gmi_day_end;. ¬null;

GID (Group Composition Identifier) The unique identifier assigned to each record of group composition. The . code of the group composition time period into which the observation interval falls. Specifying a value of &null; causes the system to automatically compute this value. The GID value may be &null; if the observation interval is not associated with a period of time during which group composition was recorded.

Comment Textual comments concerning the entire observational interval. These comments are those of the data collector in the field. &emptytext; However, the Comment value may be &null; when there are no comments concerning the entire observation interval.

SHEETS (Follow Template Sheets) SHEETS contains one row for every template sheet used in the field when recording follows, or in those cases where there is no longer an electronic record of exactly which intervals of a follow were written on which physical piece of paper, one row per follow per field observer involved in recording the follow (i.e. per FIELDFOLLOWS row. Every template sheet must have a record of at least one interval -- each SHEETS row must have at least one related INTERVALS row. On the SHEETS table this condition is checked at &transaction; commit time, although on the table the check is done immediately in the case of UPDATE and at transaction commit time in the case of DELETE. Use &transaction;s when making changes to both SHEETS and to produce bundles that ensure that at &transaction; commit each row on SHEETS has at least one related row on . Insert rows first into SHEETS and then into , and reverse this order when deleting rows. Deleting a last related row from the SHEETS table will delete the row. The value must be unique per field follow (per row) and there may be at most one &null; value per field follow.

SheetID (Sheet Identifier) A unique integer identifying the sheet. &autoid;

FFID (Field Follow Identifier) The integer identifying the field follow, the set of sheets collected by a researcher in the field comprising a day's mother-infant follow, in which the sheet belongs. The SheetID value cannot be changed. ¬null;

Followtype (Follow Type) Code identifying the type of the follow; the type of data entry template used for data collection that determined what was on the printed page. Presumably, the meaning of the data collected as determined by the data collection protocol in use did not change without a change in the print format of the data collection sheet. The legal values for this column are defined by the support table. ¬null;

Page (Sheet Page Number) The page number written on the sheet. This number must be an integer larger than zero. Note that the system does not require the page number to be contiguous. This value may be &null; when the page number is not known or not recorded in electronic form.

Location (Valley or Other Location of Data Collection) The name of the valley or other physical location where the data written on the sheet was recorded. &emptytext; However, this column may be &null; when there is no recorded location.

&event_section; Tables

EATS (Food Eaten) EATS contains one row for every different part of every kind of food eaten during a eating event. Each eating event, each row having a value that designates eating, must have at least one related row on EATS If the food eaten is unknown this should be indicated with a code which designates an unknown food. Likewise, if the part of the food eaten is unknown indicate this with a code which designates that the part is unknown. , and may have more if different kinds of food or parts of food are eaten by the chimpanzee. The combination of the kind of food eaten and the food part eaten must be unique per event -- the combination of , , and must be unique. Aside from the . column there is no way to record the amount of any one of the different kinds of food or parts thereof eaten by the individual. The system does not presently validate that the part eaten is appropriate for the kind of food eaten. While this is not hard to do this kind of validation is not a priority. To the system, it is perfectly acceptable to eat the kidney of a tree. It may be prudent to manually validate the existing combinations before performing analysis.

EatID (Eating Identifier) A unique identifier assigned to the combination of kind of food and part of food eaten during the given behavioral event. &autoid;

EID (Event Identifier) The identifier assigned to the behavioral event, the ., value, related to the kind of food eaten and part of food eaten. ¬null;

Foodkind (Kind of Food Eaten) Code indicating a kind of food eaten during the event. The legal values for this column are defined by the support table. ¬null;

Foodpart (Part of Food Eaten) Code indicating the food part eaten during the event. The legal values for this column are defined by the support table. ¬null;

EVENTS (Behavioral Events) EVENTS contains one row for every recorded non-social behavior exhibited by each focal as well as one row for every recorded social behavior exhibited by each focal for every social partner, for every follow interval. Each of the focal's non-social behaviors for the interval are represented by a single row per behavior in EVENTS. Each of the focal's social behaviors for the interval are represented by a single row in EVENTS per behavior per social partner. These rules are enforced on &transaction; commit by ensuring that the combination of EVENTS., and all related . values, related via ., and the roles the participating individuals play in the interaction, the . values are unique per EVENTS.. The exception is that anonymous individuals are allowed duplicates; duplicates are allowed when EVENTS. is &true; or when one of the individuals involved has a value which is either &gmi_unk_animid; or &gmi_stranger;. Social behaviors are recorded as dyadic interactions between 2 individuals. Record multiple interactions as pairs of dyadic interactions. When directionality is involved, as in mutual grooming between 2 individuals, 2 grooming events are recorded, identical except for reversed directionality. There must always be a chimpanzee involved in the behavior -- each EVENTS row must have at least one row on , related by . This rule is enforced on &transaction; commit. At least one of the individuals involved in the behavior must be a focal of the follow -- at least one of the related rows must have a value which identifies an animal also identified in one of the related . values. This rule is enforced on &transaction; commit on , , and , and immediately on . The . value identifying the kind of behavior controls quite a lot regarding the event including: whether the behavior can occur, whether a value is required, the identity of the individuals allowed to be involved in the behavioral event, the fashion in which individuals are allowed to participate, and whether the behavior is a social one -- whether more than one individual is involved -- and whether a baboon can be involved. For further information see the table. When the behavior is social, recording a dyadic interaction between two individuals, and one of the two individuals involved plays the active part, has a . value of &gmi_actor; (Actor), then the other individual must have the passive role, their . value must be &gmi_actee; (Actee); and the reverse is true as well. Social interactions may be non-directional as well as directional. When there is no directionality the individuals involved in the interaction are both labeled participant -- when the behavior is social and one of the individuals has a . value of &gmi_participant; (Participant) then the other individual must also have a . value of &gmi_participant;. These rules are check upon &transaction; commit. EVENTS rows recording behaviors that are social in nature are always dyadic, there must always be two related rows to record the two participants involved. The exception to this is when one of the participants is a baboon. In this case there is a single related row recording which focal is involved in the interaction and the EVENTS. column is set to indicate the baboon's involvement. This rule is enforced on &transaction; commit. The individual involved in those EVENTS rows recording behaviors which are not social, those where there is a single individual involved in the behavior, those having a single related row, cannot be directional, their related related row must have a . value of &gmi_self; (Self). Further, a baboon may not be involved, EVENTS. must be &false;. These rules are checked upon &transaction; commit. Deleting a row from the EVENTS table will delete the entire event -- the system will first delete all related , , , , , , and rows.

EID (Event Identifier) A unique identifier assigned to each behavioral event. &autoid;

IntID (Interval Identifier) The identifier assigned to the follow interval, the ., value, related to the occurrence of the recorded behavior. ¬null;

Behavior (Behavior Engaged In) Code indicating the behavior engaged in. The legal values for this column are defined by the support table. ¬null;

Position (Position Within the Canopy) Code indicating the position within the forest canopy where the event occurred. The legal values for this column are defined by the support table. ¬null;

Baboon (Is a Baboon a Participant?) A Boolean value. When &true; a baboon is a participant in the behavior. When &false; a baboon is not a participant. ¬null;

Uncertain (The Behavior May or May Not Have Occurred) A Boolean value. When &true; the recorded behavior is in doubt. Note that the system contains no way to designate that the identity of a participant in the behavior is uncertain. When &false; the recorded behavior was clearly observed and is not in doubt. See the &gcdbh; for further information. ¬null;

Comment (Notes Regarding the Event) Textual comments concerning the specific behavioral event. These comments are those of the data collector in the field. &emptytext; However, the Comment value may be &null; when there are no comments concerning the event.

FISHED (Food Fished For) FISHED contains one row to record the part and the kind of food fished for during each fishing event. Each fishing event, each row having a value that designates fishing, must have exactly one related row on FISHED If the food fished for is unknown this should be indicated with a code which designates an unknown food. Likewise, if the part of the food fished for is unknown indicate this with a code which designates that the part is unknown. , and may have more if different kinds of food are fished for by the chimpanzee. Only those kinds of food designated as fishable may be fished for -- FISHED. may contain only those . values where the related . value is &true;. Only those parts of food designated as fishable may be fished for -- FISHED. may contain only those . values where the related . value is &true;. The system does not presently validate that the part fished for is appropriate for the kind of food fished for. It may be prudent to manually validate the existing combinations before performing analysis.

FishedID (Fishing Identifier) A unique identifier assigned to the kind of food fished for during the given behavioral event. &autoid;

EID (Event Identifier) The identifier assigned to the behavioral event, the ., value, related to the kind of food fished for. ¬null;

Foodkind (Kind of Food Fished For) Code indicating a kind of food fished for during the event. The legal values for this column are defined by the support table. ¬null;

Foodpart (Part of Food Fished For) Code indicating the food part fished for during the event. The legal values for this column are defined by the support table. ¬null;

Result (Result of the Fishing Attempt) Code indicating the result of the fishing attempt. The legal values for this column are defined by the FISHING_RESULTS support table. ¬null;

PARTS (Behavioral Participants) PARTS contains one row for every individual, whether a focal in the follow or not An improved design would have an additional column, automatically maintained by the system, on to record the role the participant plays in the follow, i.e., mother, infant, sib, or non-focal. This would be analogous to the . column. Such a column would simplify both the data validation coding and database querying. See also the footnote regards improving the design. , involved in each recorded behavior (each row). Therefore there are always either 1 or 2 PARTS rows per row. Individuals which are unrecognized or otherwise anonymous, the individuals with values of &gmi_unk_animid; or &gmi_stranger;, have rows in PARTS when they are involved in the behavior, but baboons do not. An individual can be involved in a given behavioral event only once -- the related . must be unique per . Each participant in an event must be a member of the study population on the date of the follow. The . cannot be after the follow's . value. The . cannot be before the follow's ..

PID (Parts Identifier) A unique identifier assigned to the individual involved in the given behavioral event. &autoid;

EID (Event Identifier) The identifier assigned to the behavioral event, the ., value, related to the recorded participant. ¬null;

Part (Participant Role) One of the following codes indicating the role the designated individual plays in the behavior: &gmi_actor; (Actor) The individual has the active role in the behavior, initiating or sustaining it. &gmi_actee; (Actee) The individual has the passive role in the behavior, receiving attention or contact. &gmi_participant; (Participant) The individual is neither active nor passive, such concepts do not apply or are not appropriate to the behavior or the data collection protocol. &gmi_self; (Self) The individual is the sole participant in the behavior. ¬null;

Participant (Chimp Virtual Identifier) The identifier assigned to the individual involved in the behavior. The code of the individual. ¬null;

PLAYS (Playing Detail) PLAYS contains one row for every different play activity engaged in during a play event. Each play event, each row having a value that designates a play type event, must have at least one related row on PLAYS If the play activity occurring is unknown this should be indicated with a code which designates an unknown play activity. , and may have more if the playing chimpanzee engages in different play activities during the play event. The play activity must be unique per event -- the combination of and must be unique. Aside from the . column there is no way to record the number or duration of any one of the different play activities engaged in by the individual during the play event.

PlayID (Playing Identifier) A unique identifier assigned to the play activity engaged in during the given behavioral event. &autoid;

EID (Event Identifier) The identifier assigned to the behavioral event, the ., value, related to the play activity. ¬null;

Play (Play Activity Engaged In) Code indicating a play activity engaged in during the event. The legal values for this column are defined by the support table. ¬null;

RESTS (Resting Detail) RESTS contains one row to record the resting posture assumed during each resting event. Each resting event, each row having a value that designates a resting type event, must have exactly one related row on RESTS. If the resting posture assumed is unknown this should be indicated with a code which designates an unknown resting posture. Aside from the . column there is no way to record the duration of the resting posture assumed by the individual during the resting event.

RestID (Resting Identifier) A unique identifier assigned to the resting posture assumed during the given behavioral event. &autoid;

EID (Event Identifier) The identifier assigned to the behavioral event, the ., value, related to the resting posture assumed. ¬null;

Rest (Resting Posture Assumed) Code indicating a resting posture assumed during the event. The legal values for this column are defined by the support table. ¬null;

RIDES (Riding Detail) RIDES contains one row which records the riding posture assumed during each riding event. Each riding event, each row having a value that designates a riding type event, must have exactly one related row on RIDES. If the riding posture assumed is unknown this should be indicated with a code which designates an unknown riding posture. Aside from the . column there is no way to record duration of the riding posture assumed by the individual during the riding event.

RideID (Riding Identifier) A unique identifier assigned to the riding posture assumed during the given behavioral event. &autoid;

EID (Event Identifier) The identifier assigned to the behavioral event, the ., value, related to the riding posture assumed. ¬null;

Ride (Riding Posture Assumed) Code indicating a riding posture assumed during the event. The legal values for this column are defined by the support table. ¬null;

VOCS (Vocalization Detail) VOCS contains one row for every different kind of vocalization made during a vocalization event. Each vocalization event, each row having a value that designates a vocalization, must have at least one related row on VOCS If the vocalization made is unknown this should be indicated with a code which designates an unknown vocalization. , and may have more if different kinds of vocalizations are made by the vocalizing chimpanzee. The kind of vocalization must be unique per event -- the combination of and must be unique. Aside from the . column there is no way to record the number or volume of any one of the different kinds of vocalizations made by the individual during the vocalization event.

VocID (Vocalization Identifier) A unique identifier assigned to the kind of vocalization made in the given behavioral event. &autoid;

EID (Event Identifier) The identifier assigned to the behavioral event, the ., value, related to the kind of vocalization made. ¬null;

Vocalization (Kind of Vocalization Made) Code indicating a kind of vocalization made as the event occurred. The legal values for this column are defined by the support table. ¬null;

&distance_section; Tables

DISTANCEPARTS (Follow Participants Between Whom Distance Was Measured) DISTANCEPARTS contains exactly 2 rows for each recorded distance, one row for each of the individuals between whom the distance was measured. To restate, there are always exactly 2 rows on DISTANCEPARTS for every row. This check is performed on &transaction; commit. The individuals between whom the distance is measured can only be those individuals who are participants in the follow -- each value in DISTANCEPARTS has a related . value, this value must be that of a . related to one of the follows' . values. This check is performed on &transaction; commit on update to and insert or update of DISTANCEPARTS, and otherwise immediately. The individual must have the same role (i.e., mother, infant, twin1, etc.) on DISTANCEPARTS as the individual does in the rest of the follow -- the DISTANCEPARTS. value for each individual must match the value found in the related .. This check is performed on &transaction; commit on changes to DISTANCEPARTS and and immediately upon changes to and . An improved design would have the trigger automatically set the value of . based on the individual's .. Among other advantages, the columns on the view that expose the role could be removed. Distance must be measured between two different individuals -- the combination of and the related . must be unique. Distance can be measured between two individuals at most once per interval. If there is a group composition survey for the related interval then the survey must include the individuals between whom the distance was measured -- when there is a related row then the related rows must include rows for the individuals identified in the DISTANCEPARTS rows. This check is performed upon &transaction; commit on changes to and delete of rows, and immediately otherwise.

DPID (Distance Participant Identifier) A unique integer identifying the row recording one participant in the inter-individual distance measurement. &autoid;

DistID (Distance Identifier) The identifier assigned to the distance measurement, the . value, of which the individual was a participant. ¬null;

Role Code indicating the individual's role in the follow. The legal values for this column are defined by the support table. ¬null;

Participant (Chimp Virtual Identifier) The identifier assigned to the individual. The code of the individual. ¬null;

DISTANCES (Distances Between Follow Participants) DISTANCES contains one row for each recorded distance between follow participants. Note that codes are used to represent various distance ranges, and that codes have varied over time. For more information see the &gcdbh;. When the value is &gmi_distunk;, observed but unknown, the value must be &true;. Deleting a row from the DISTANCES table will delete the entire record of the distance -- the system will first delete all related rows.

DistID (Distance Identifier) A unique integer identifying the row recording the distance. &autoid;

IntID (Interval Identifier) The identifier assigned to the follow interval, the ., value, during which the distance measurement was taken. ¬null;

Uncertain (Distance is Uncertain) A Boolean flag indicating whether the observer was certain of the recorded distance measurement. &true; when the distance measurement is not certain, &false; when certain. ¬null;

Distance (Distance Code) Code indicating the distance between two followed individuals. The legal values for this column are defined by the support table. ¬null;

&group_section; Tables The data validation rules discussed in this section are subject to change as the system continues to be developed.

GROUPMEMBERS (Individuals Found in Group Composition Survey Analysis) The data in GROUPMEMBERS is the result of an automated analysis called smearing which spreads the group composition survey results over larger time intervals. GROUPMEMBERS contains one row for each individual which analysis determines is a member of the surveyed group for each analyzed group composition time interval. At present there is extremely limited automated group composition analysis and the GROUPMEMBERS table contains one row per row. An individual can be recorded present in a group at most once -- the combination of and the related . must be unique. A warning is generated when an individual is in a group comp, when that individual is is less than &gmi_min_alone_age; old, and their current mother is not in the group comp. The current mother is the biological mother (the mother indicated when the young individual has a . which is not &null;) if the young individual is never adopted or adopted after the date of the group comp. The current mother is the adoptive mother if the date of the group comp is after the date of permanent adoption (after the, non-&null;, .). In the case of multiple permanent adoptions the current mother is the most recent adoptive mother as of the date of the group comp. When the biological mother is unknown and there are no known adoptive parents then there are no requirements regarding presence of a parent. These checks are performed by the warning system and so do not occur until the warning system is run. The reverse is also true; a warning is generated when the most current mother is present in the group composition and any offspring, biological or permanently adopted on or before the date of the follow, in the study as of the date of the follow, less than &gmi_min_alone_age; old are not present. As above, in the case of adoption, only the current mother/offspring relationship is considered. As above, these checks are performed by the warning system and so do not occur until the warning system is run. Temporary adoptions do not generate warnings, with the exception that a warning is generated if the focals of a follow are involved in a temporary adoption and either the mother or the infant appears in the group comp without the other. When an adoption is temporary the infant need not be with the temporary adoptive parent. Significantly, the infant need not be with any parent. The presumption is that temporary adoption may be due to infant abandonment, and the establishment of a temporary adoption is what the system could (but does not) use to flag this situation. But unless a temporary adoption changes at a later date into a permanent adoption, temporary adoptions have no end date. Rather than have temporary adoptions create situations where the temporarily adopted individual thenceforth is not required to be with any parent, the system uses the existence of a mother/infant follow involving a temporary parent as the flag which triggers suspension of young-infants-must-be-with-parents warnings. At the various time interval endpoints, the date of permanent adoption, the date of entry into the study, and the date of departure from the study, the enforced rules regarding chimps less than &gmi_min_alone_age; and their real or permanently adoptive mothers are interpreted in such a way as to loosen the requirements and exclude the date of the interval endpoint. At any rate this is the intended design. These sorts of corner cases are always tricky and there may be bugs. This is because individuals may be born, die, be adopted, etc., at any point during the day. Because the system sometimes but not always expects offspring less than &gmi_min_alone_age; to be part of their mother's group composition care must be taken during analysis to ensure that results are not skewed based on whether or not the offspring's presence is forced. Presence in the study population is determined by whether the date of the follow is after an individual's . and before the individual's .. Because the individual need not be in the study population for the entire day on the date of entry into the study population or the date of departure from the study population the above rules regarding offspring less than &gmi_min_alone_age; old do not require the mother to be with the offspring on these two days. Unless care is taken during analysis this censoring could lead to biased results. The perineal swelling () value must be &null; unless the individual is female. The perineal swelling may not be more than &gmi_0; unless the individual is at least &gmi_swelling_min_age; old.

GMID (Group Composition Identifier) A unique integer identifying the row recording an individual's presence in the group composition survey. &autoid;

GID (Group Survey Identifier) A unique integer identifying the group composition survey during which the individual was found. ¬null;

ChimpID (Chimp Virtual Identifier) The identifier assigned to the group member. The code of the individual. ¬null;

Uncertain (Identity Uncertain) A Boolean flag indicating whether the identity of the individual is tentative. &true; when the individual's identity is not certain, &false; when certain. ¬null;

Swelling (Perineal Swelling) One of the following values to indicate the degree of perineal swelling: &gmi_0;, &gmi_1_4;, &gmi_1_2;, &gmi_3_4;, &gmi_1;; &gmi_U; to indicate adolescent swelling; or &null; to indicate that perineal swelling is not applicable or not known. For further information see the &gcdbh;.

GROUPS (Group Composition Surveys) The data in GROUPS is the result of an automated analysis called smearing which spreads the group composition survey results over larger time intervals. GROUPMEMBERS contains one row for every time interval over which is automatically analyzed. At present there is extremely limited automated group composition analysis and the GROUPS table contains one row per row. The group composition membership (a GROUPS row) is related to behavioral intervals ( rows) based on the group composition time (the . value). -- All intervals with times starting with (inclusive of) the -- group comp time through the interval (exclusive of) the -- next group comp time are connected to the group comp. Each group composition survey must record the presence of at least one individual, the individual who has the role of mother in the follow -- there must be related row on the table with the mother's related to the the value. This check is performed upon &transaction; commit when rows are added to the GROUPS table or when rows are deleted from the table or when rows are inserted or updated on the table or when rows are updated on the table, and performed immediately when a row is updated on the table. The infant and the sibling need not be in the group. Each group composition survey must be related to at least one follow interval -- there must be at least one related row on the table. This check is performed upon &transaction; commit. Note that there are some periods of time when group composition was not collected. There are no rules regarding when this occurred; the system permits group composition to be missing no matter the date. The system takes advantage of this; because group composition is not required the group composition can be uploaded independently, after upload of the follow related data. Given the data integrity rules and their manner of enforcement group composition survey data must be added to the database within a single transaction is as follows: Create a GROUPS row and then those related rows required to satisfy the data integrity requirements, update the related rows with the new value, then relate the group composition survey data to a follow either by updating (a) previously created row(s) inserted with a &null; . value or create new row(s) with the value of the new GROUPS row.

GID (Group Survey Identifier) A unique integer identifying the group composition survey. &autoid;

RAW_GROUPMEMBERS (Raw Data on Individuals Found in Group Composition Surveys) RAW_GROUPMEMBERS (Raw Data on Individuals in Group Composition Surveys) RAW_GROUPMEMBERS contains one row for each individual found to be a member of the surveyed group for each group composition survey. The data in RAW_GROUPMEMBERS is interpreted and smeared into the table, which provides a more direct link between the follow intervals and the group composition data. However, although the RAW_GROUPMEMBERS data is copied into the table, any sort of intelligent smearing is not yet implemented. Almost none of the data validation checks described in this section are implemented. They are all subject to change. An individual can be recorded present in a group at most once -- the combination of and the related . must be unique. See the for information regarding when mothers are expected to be present in a group composition with their young offspring. Because offspring less than &gmi_min_alone_age; are sometimes but not always forced to be part of their mother's group composition care must be taken during analysis to ensure that results are not skewed based on whether or not the offspring's presence is forced. Presence in the study population is determined by whether the date of the follow is after an individual's . and before the individual's .. Because the individual need not be in the study population for the entire day on the date of entry into the study population or the date of departure from the study population the above rules regarding offspring less than &gmi_min_alone_age; old do not require the mother to be with the offspring on these two days. Unless care is taken during analysis this censoring could lead to biased results. The perineal swelling () value must be &null; unless the individual is female. The perineal swelling may not be more than &gmi_0; unless the individual is at least &gmi_swelling_min_age; old.

RGMID (Raw Group Composition Identifier) A unique integer identifying the row recording an individual's presence in the group composition survey. &autoid;

RGID (Raw Group Survey Identifier) A unique integer identifying the group composition survey during which the individual was found. ¬null;

ChimpID (Chimp Virtual Identifier) The identifier assigned to the group member. The code of the individual. ¬null;

Origin (Originating Data Source) Code indicating the source that places the individual in the group. The legal values for this column are defined by the support table. ¬null;

Uncertain (Identity Uncertain) A Boolean flag indicating whether the identity of the individual is tentative. &true; when the individual's identity is not certain, &false; when certain. ¬null;

RAW_GROUPS (Raw Data on Group Composition Surveys) RAW_GROUPS contains one row for every survey of group composition. The data in RAW_GROUPS is interpreted and smeared into the GROUPS table, which provides a more direct link between the follow intervals and the group composition data. However, this is not yet implemented. Almost none of the data validation checks described in this section are implemented. They are all subject to change. Each group composition survey must record the presence of at least one individual, the individual who has the role of mother in the follow -- there must be related row on the table with the mother's related to the the value. This check is performed upon &transaction; commit when rows are added to the RAW_GROUPS table or when rows are deleted from the table or when rows are inserted or updated on the table or when rows are updated on the table, and performed immediately when a row is updated on the table. The infant and the sibling need not be in the group. Each group composition survey must be related to at least one follow sheet -- there must be at least one related row on the table. This check is performed upon &transaction; commit. The time of the group composition must be unique per follow; the combination of and the related . must be unique. Note that there are some periods of time when group composition was not collected. There are no rules regarding when this occurred; the system permits group composition to be missing no matter the date. The system takes advantage of this; because group composition is not required the group composition can be uploaded independently, after upload of the follow related data. Given the data integrity rules and their manner of enforcement group composition survey data must be added to the database within a single transaction is as follows: Create a RAW_GROUPS row and then those related rows required to satisfy the data integrity requirements, update the related rows with the new value, then relate the group composition survey data to a follow either by updating (a) previously created row(s) inserted with a &null; . value or create new row(s) with the value of the new RAW_GROUPS row.

RGID (Raw Group Survey Identifier) A unique integer identifying the group composition survey. &autoid;

Time The time the group composition was taken ¬null;

Baboons A Boolean flag indicating whether one or more baboons are present. This column may be &null; when it is not known whether a baboon is present. In the data converted from MS Access this column is set to &false; whenever there is no data. This may or may not indicate that baboons are absent; analysis may show that this simply means that the data was not collected. At the time of this writing the database content has not been updated to reflect when group composition surveys flagged the presence of baboons and when they did not.

SheetID (Sheet Identifier) The integer identifying the template sheet on which the group composition was recorded. ¬null;

GID (Group Identifier) Integer identifying the process (smeared) group survey analysis row into which the raw group survey data was incorporated. When group survey analysis is automated this column will not allowed to be &null;. In the meantime this column may be &null; when there is no automated analysis.

Support Tables The system's support tables, sometimes called lookup tables, describe the vocabulary of the system's coded data. As such, the content of these tables defines how the system is used and so it is important to understand the content of these tables. Each of the support tables has a column used as a key, which contains the validated vocabulary, and a column which describes the key, the Descr column. The name of the key column is chosen to describe it's content. The Descr column always has the same name. It contains a short textual description of the vocabulary item represented by the row. The Gombe-MI system strives, as much as possible, to have the semantics of the data be encoded in support tables instead of in logic embedded within the system. In those cases where certain values have meaning that is encoded in the system's logic this is noted in each support table's Special Values section. Special values are of two kinds: values that have meaning to the system as a whole and values that are coded within the data upload process which transforms the notation used in the field into the codes and structure of the Gombe-MI system.

&demography_section; Support Tables

BDDISTS (Birth Date Probability Distributions) BDDISTS contains one row for every code representing a probability distribution pertinent to birth date.

Key: BDDist The BDDISTS table is keyed by the BDDist column.

Special Values None.

ENTRYTYPES (Entrytype codes) ENTRYTYPES contains one row per chimpanzee entrytype.

Key: Entrytype The ENTRYTYPES table is keyed by the Entrytype column.

Special Values None.

DEPARTTYPES (Departtype codes) DEPARTTYPES contains one row per chimpanzee departtype.

Key: Departtype The DEPARTTYPES table is keyed by the Departtype column.

Final (Departure is Final) A Boolean value indicating whether or not there is a possibility for further observation of the individual after departure. ¬null;

Special Values None.

&follow_section; Support Tables

BEHAVIOR_TRANS (Behavior Translation Table) BEHAVIOR_TRANS contains one row for every behavior code as written in the field, plus one row for every column representing an observed behavior. It is used by the view to translate the codes and column markings made in the field to the behavior codes used within the Gombe-MI system. This provides a degree of flexibility when it comes to adding to the behaviors observed in the field. In cases where the field data collector writes behavior codes onto the data collection sheet there (may) need to be no changes made to the Gombe-MI system's code to accommodate the new data. In cases where the field data collector puts check-marks (or similar) into columns the view must have the names of these kinds of columns written into it's source code and so program changes are required to accommodate additional columns of this sort. The BEHAVIOR_TRANS table is different from other Gombe-MI support tables in that its values are used only to support the behavioral data upload process, the values do not define a vocabulary used for Gombe-MI database content. The must be &null; when is &true; and otherwise must be non-&null;. The column is only meaningful when and are &null; and and are &false;. Further, it is meaningful only for those rows representing behavioral codes which occur as values in columns, not for those rows representing uploaded columns, and has no effect on the mother's behavioral data.

Key: Old_Behavior The BEHAVIOR_TRANS table is keyed by the Old_Behavior column. This column indicates how the behavior is denoted in the field on the the data collection sheet. The values in this column are unusual in that they are of one of two different sorts. When the field data collector writes one or more behavior codes onto the data collection sheet each of the codes written should appear as a Old_Behavior value in a BEHAVIOR_TRANS row. When the field data collector makes check-marks or puts similar marks on the data collection sheet to represent the observation of a behavior then there should be a row in BEHAVIOR_TRANS with a Old_Behavior value which is the name of the column into which the check-mark is made. In this case the column name value in the Old_Behavior column should be one of the column names of the view, written in lower case. The decision to use the names of the columns of the view, in lower case, rather than an exact match of the names of the columns that are written on the field data collection sheets is somewhat arbitrary. The system as written does make it simpler to maintain the view since the programmer can be less aware of the details of data entry as it occurs in the field.

New_Behavior (Behavior Code) Behavior code to be used within Gombe-MI. The legal values for this column are defined by the support table. ¬null;

Position_Val (Default Position Value) Position code to be used when the uploaded row contains no indication of what position within the canopy is associated with the behavior. The legal values for this column are defined by the support table. This value must be &null; when a position value is not required by the behavior.

Uncertain A Boolean value indicating whether behavioral events deriving from the given old behavior code should always be denoted uncertain (.). ¬null;

Grooms_Other A Boolean value indicating whether another uploaded column contains the chimpids (animids) of individuals groomed by the focal. The actual referenced chimpid containing column and the treatment of its content is determined by the view.

GO_Self (Grooms Other indicates Self-grooming) A Boolean value indicating whether self-grooming is denoted by having the focal's own chimpid listed along with the other individuals the focal is grooming. A value of &true; indicates that self-grooming is denoted by listing the focal's chimpid along with the other groomed individuals. A value of &false; indicates that self-grooming is not denoted in this fashion. This value may be &null;. See above.

Grooms_By A Boolean value indicating whether another uploaded column contains the chimpids (animids) of individuals who groomed the focal. The actual referenced chimpid containing column and the treatment of its content is determined by the view.

Play_Val (Default Play Value) Play code to be used when the uploaded row contains no indication of what play code is associated with the behavior. The legal values for this column are defined by the support table. When this value is not &null; another uploaded column contains the chimpids (animids) of individuals who played with the focal. The actual referenced chimpid containing column and the treatment of its content is determined by the view. This value must be &null; when a play value is not required/when the behavior is not a play behavior.

Rest_Val (Default Resting Posture Value) Resting posture code to be used when the uploaded row contains no indication of what resting posture code is associated with the behavior. The legal values for this column are defined by the support table. This value must be &null; when a resting value is not required/when the behavior is not a resting behavior.

Ride_Val (Default Riding Posture Value) Riding posture code to be used when the uploaded row contains no indication of what riding posture code is associated with the behavior. The legal values for this column are defined by the support table. This value must be &null; when a riding value is not required/when the behavior is not a riding behavior.

Vocalization_Val (Default Vocalization Value) Vocalization code to be used when the uploaded row contains no indication of what vocalization code is associated with the behavior. The legal values for this column are defined by the support table. This value must be &null; when a vocalization value is not required/when the behavior is not a vocalization behavior.

Eats A Boolean value indicating whether another pair of uploaded columns contains the food parts and food kinds detailing the food eaten by the focal. The actual referenced columns containing food detail and the treatment of its content is determined by the view.

Fished A Boolean value indicating whether another pair of uploaded columns contains the food parts and food kinds detailing the food fished for by the focal. The actual referenced columns containing food detail and the treatment of its content is determined by the view.

Fishpart_Val (Default Fishpart Value) Default Foodpart code to be used when the uploaded row contains no indication of what foodpart code is associated with the fishing behavior. The legal values for this column are defined by the support table. This value must be &null; when a foodpart is not required/when the behavior is not a fishing behavior.

Fishkind_Val (Default Fishkind Value) Default Foodkind code to be used when the uploaded row contains no indication of what foodkind code is associated with the fishing behavior. The legal values for this column are defined by the support table. This value must be &null; when a foodpart is not required/when the behavior is not a fishing behavior.

Inf_Allowed (Is the Infant Allowed to have this behavior code) A Boolean value indicating whether or not the infant is allowed to have this behavior code written into the column. A value of &true; indicates the infant is allowed to have the behavior code. A value of &false; indicates the infant is not allowed to have the behavior code. ¬null; Do not rely on this column as a means of preventing the database from recording certain infant behaviors. The table should be used for that purpose. This column serves only to help ensure that uploaded data is not misinterpreted when transformed into database content.

With_Ma A Boolean value indicating whether or not individuals exhibiting this behavior are doing so in a directed fashion with the mother. &true; means the mother is a social partner, the actee, even though there is no other indication of this. &false; means the mother is not automatically made a social partner. ¬null;

Dups A Boolean value indicating whether the behavior is recorded twice on the data collection sheet, once for each focal. For further information see above.

Special Values In order to process the indicated columns the view requires that BEHAVIOR_TRANS contain rows for the following values: &gmi_ma_foodpart; &gmi_ma_fishpart; &gmi_ma_grooming; &gmi_ma_groomed_by; &gmi_ma_play; &gmi_ma_voc; &gmi_inf_foodpart; &gmi_inf_fishpart; &gmi_inf_nurse; &gmi_inf_travel; &gmi_inf_eat; &gmi_inf_solitary_play; &gmi_inf_rest; &gmi_inf_ride_on_belly; &gmi_inf_ride_on_back; &gmi_inf_ride_on_unk; &gmi_inf_dangle; &gmi_inf_rejection; &gmi_inf_touching_another; &gmi_inf_grooming; &gmi_inf_being_groomed; &gmi_inf_social_play; &gmi_inf_display; &gmi_inf_cry; &gmi_inf_voc; &gmi_inf_beg; &gmi_inf_assr; &gmi_inf_sex_related; &gmi_inf_tooluse; &gmi_inf_directaggression; &gmi_devbeh_col; &gmi_sib_foodpart; &gmi_sib_fishpart; &gmi_sib_grooming; &gmi_sib_groomed_by; &gmi_sib_play; &gmi_sib_voc;

DISTANCE_TRANS (Distance Translation Table) DISTANCE_TRANS contains one row for every distance code as written in the field, plus one row for every column representing an observed inter-focal distance. It is used by the view to translate the codes and markings in columns which are made in the field to the distance codes used within the Gombe-MI system. See the above footnote regarding the flexibility provided by the table. The DISTANCE_TRANS table is different from other Gombe-MI support tables in that its values are used only to support the behavioral data upload process, the values do not define a vocabulary used for Gombe-MI database content.

Key: Old_Distance The DISTANCE_TRANS table is keyed by the Old_Distance column. This column indicates how the distance is denoted in the field on the the data collection sheet. The values in this column are unusual in that they are of one of two different sorts. When the field data collector writes one or more distance codes onto the data collection sheet each of the codes written should appear as a Old_Distance value in a DISTANCE_TRANS row. When the field data collector makes check-marks or puts similar marks on the data collection sheet to represent the observation of a distance then there should be a row in DISTANCE_TRANS with a Old_Distance value which is the name of the column into which the check-mark is made. In this case the column name value in the Old_Distance column should be one of the column names of the view, written in lower case. See the above footnote in the documentation regarding the design decisions surrounding the use of column names.

New_Distance (Distance Code) Distance code to be used within Gombe-MI. The legal values for this column are defined by the support table. ¬null;

Uncertain A Boolean value indicating whether inter-focal distance measurements deriving from the given old distance code should always be denoted uncertain (.). ¬null;

Special Values In order to process the indicated columns the view requires that DISTANCE_TRANS contain rows for the following values: &gmi_inf_distance_1; &gmi_inf_distance_2; &gmi_inf_distance_3; &gmi_inf_distance_4; &gmi_inf_distance_5; &gmi_inf_distance_over_5m;

FOLLOWROLES (Roles of Follow Participants) FOLLOWROLES contains one row for every possible role of the individuals which are followed. E.g. Mother, Infant, Sibling, Twin1 Note that the system does not require a separate row for, e.g., twins since when there are multiple infants or siblings they must have the same birth date. , etc.

Key: Role The FOLLOWROLES table is keyed by the Role column.

Function (Biological Function) The biological relationship of the role. Must be one of: &gmi_mom; Mother &gmi_infant; Infant &gmi_sib; Sibling The Function value cannot be changed. ¬null;

Special Values The Role may not have a value of &gmi_nonfocal;. The &gmi_nonfocal; code has the reserved meaning Not a focal in the follow. The Role values &gmi_mom_role;, &gmi_infant_role;, and &gmi_sib_role; have special meaning to the view. See the documentation of the view for more information.

FOLLOWTYPES (Follow Data Entry Sheet Types) FOLLOWTYPES contains one row for every type of data entry sheet (template) used in the field. For further information see the templates.xlsx spreadsheet and related documentation. The Descr column is the appropriate place to put notes on the data collection protocol(s) or template sheets used with the given type of follow, as well as the list of templates which make up the type. It might or might not make sense to have a separate textual column which lists the templates that make up the type.

Key: Type The FOLLOWTYPES table is keyed by the Type column.

Implied_Behavior A Boolean value indicating whether a behavior code is always recorded on the sheet, for particular mother/infant/sibling behaviors, or whether the behavior code is implied by the presence of data in another column. A value of &true; means that the behavior code is implied by the presence of data in certain columns. A value of &false; means that behavior codes are always present in the data for these columns. For further information on which behaviors and which columns are affected by the Implied_Behavior value see the documentation of the view. ¬null;

The Boolean Columns of FOLLOWTYPES All columns but , , and Descr are Boolean. These Boolean columns are documented in a table, differently from the documentation of other columns in this document, due to their substantial similarity. True indicates that the data represented by the column name was collected using the data collection protocol associated with the data entry sheets of the given follow type. Note that the data may have been collected either as a column or in notes. Column Name Column Heading on Template Researcher Mchunguzi (Researcher) Date Tarehe (Date) Page Na. Ya Karatasi (Page) InOutOfCamp Kambini/Porini (In Camp/Out of Camp) Focals Sokwe (Focals) Time Muda/Saa (Time) Tape Tepu (Tape) BadObs Uchunguzi Mbaya (Bad obs) Rain Mvua (Rain) MomBehavior Vitendo vya Mama (behavior) MomGroomingPlay Grooming/Play MomGroomed Groomed MomVocalization Vocalization BadObsInfant Uchunguzi Mbaya (Bad Obs - Infant) Nurse Nyonya (Nurse) Travel Tembea (Travel) Eat Kula (Eat) SolitaryPlay Cheza Mwenyewe (Solitary play) RestWatch Pumzika/Tazama (Rest/Watch) MomDistance Gusana na Mama (Distance Touching (1)) MomWithinArmsReach Distance w/in arms reach (2) Mom0to5 Mita 0-5 (Distance 0-5m (3)) Mom5plus Zaida ya m 5 (Distance >5m) Mom5to15 Mita 5-15 (Distance 5-15m (4)) Mom15plus Mita 15 na zaidi (Distance >15m (5)) RideVentral Bebwa Tumboni (Ride Ventral) RideDorsal Bebwa Mgongoni (Ride Dorsal) Dangle Dangle RejectTravel Kataliwa (Reject [travel]) TouchingAnother Gusana na mwengine (Touching Another) InfantGrooming Kwatua (Grooming) InfantGroomed Kwatulia na (Groomed) InfantPlay Cheza (Social Play) InfantDisplay Jijenga (Display) InfantCry Lia lia (Cry) InfantVocal Sauti nyingine (Inf Voc/Other vocal) InfantBeg Omba (Beg) InfantASSR UUJB (ASSR) InfantTwinDistance Umbali (Distance between infants) SiblingPresent Uchunguzi mbaya SiblingBehavior Behavior SiblingMotherDistance Distance to Mother SiblingVocalization Vocalization SiblingPlay Play SiblingGrooming Grooming SiblingGroomed Groomed SiblingInfantDistance Distance to Infant SiblingInfant2Distance Distance to Second Infant SiblingSiblingDistance Umbali (Distance between siblings) Comments Comments GroupComp Group Comp

Special Values None.

FOODPART_TRANS (Food Part Translation Table) FOODPART_TRANS contains one row for every food part code as written in the field. It is used by the program to translate the foodpart codes as written in the field to the food part codes used within the Gombe-MI system. See the above footnote regarding the flexibility provided by the table. The FOODPART_TRANS table is different from other Gombe-MI support tables in that its values are used only to support the behavioral data upload process, the values do not define a vocabulary used for Gombe-MI database content.

Key: Old_Foodpart The FOODPART_TRANS table is keyed by the Old_Foodpart column. This column contains the foodpart codes written onto the data collection sheet in the field.

New_Foodpart Foodpart code to be used within Gombe-MI. The legal values for this column are defined by the support table. ¬null;

Special Values None

GC_TRANS (Group Composition Translation Table) GC_TRANS contains one row for every mark written in the field onto the group composition portion of the data collection sheets, in the cells of the columns which record chimpanzee or baboon presence. It is used by the program to translate the codes and markings in columns as made in the field to determine how the information is represented within the Gombe-MI system. See the above footnote regarding the flexibility provided by the table. The GC_TRANS table is different from other Gombe-MI support tables in that its values are used only to support the group composition data upload process, the values do not define a vocabulary used for Gombe-MI database content. There can only be a swelling when the individual is in the group composition -- the value must be &null; when is &false;. There must be a code indicating where the data came from when the individual is present, and otherwise there must not -- the column cannot be &null; when is &true;, and the column must be &null; when is &false;.

Key: Mark The GC_TRANS table is keyed by the Mark column. This column indicates what marks are made in the cells of the columns which correspond to chimpanzees. The Mark column may not be &null;, but may be the empty string.

Present A Boolean value indicating whether the designated mark means the individual should be included in the group composition -- whether there should be a row on . ¬null;

Uncertain A Boolean value indicating the . value to use when this mark appears on the group comp sheet. ¬null;

Swelling (Swelling Code) Swelling value to be stored in the . when the designated mark is used. This value may be &null; when the . value is to be NULL.

Origin (Origin Code) Origin value to be stored in the . when the designated mark is used. See the description above for when this value may and may not be &null;.

Notes Notes on the GC_TRANS row and the value. Because it can be hard to distinguish &null; from the empty string, no characters, the Notes column is provided. Any notes on the row's value or anything else related to the row may be be stored in this column. This value may be &null; when notes are not needed.

Special Values None

PEOPLE PEOPLE contains one row for every person involved in data collection or data entry.

Key: Person The PEOPLE table is keyed by the Person column.

Special Values None.

PLAY_TRANS (Play Translation Table) PLAY_TRANS contains one row for every play code as written in the field. It is used by the view to translate the codes written in the field to the play codes used within the Gombe-MI system. The PLAY_TRANS table is different from other Gombe-MI support tables in that it's values are used only to support the behavioral data upload process, the values do not define a vocabulary used for Gombe-MI database content.

Key: Old_Play The PLAY_TRANS table is keyed by the Old_Play column.

New_Play (Play Code) Play code to be used within Gombe-MI. The legal values for this column are defined by the support table. ¬null;

Uncertain A Boolean value indicating whether play events deriving from the given old play code should always be denoted uncertain (.). ¬null;

Special Values None.

VOC_TRANS (Vocalization Translation Table) VOC_TRANS contains one row for every vocalization code as written in the field. It is used by the view to translate the codes written in the field to the vocalization codes used within the Gombe-MI system. The VOC_TRANS table is different from other Gombe-MI support tables in that it's values are used only to support the behavioral data upload process, the values do not define a vocabulary used for Gombe-MI database content.

Key: Old_Voc The VOC_TRANS table is keyed by the Old_Voc column.

New_Voc (Vocalization Code) Vocalization code to be used within Gombe-MI. The legal values for this column are defined by the support table. ¬null;

Uncertain A Boolean value indicating whether vocalization events deriving from the given old vocalization code should always be denoted uncertain (.). When multiple vocalizations are uploaded if any of them are uncertain then all vocalizations are uncertain. The database contains no provision for recording the certainty of individual vocalizations. The column is related to the entire row. ¬null;

Special Values None.

&event_section; Support Tables

BEHAVIORS BEHAVIORS contains one row for every category of behavior defined (past or present) in the data collection protocol. The BEHAVIORS table controls many aspects of the validation that occurs involving behavioral events, it is central to the system. Behavior codes are classified into particular types, depending on what sort of supplemental information on the behavior is recorded. The classification is driven by code value in the BEHAVIORS. column. The type codes are as follows: The BEHAVIORS.Type values &gmi_eating; Eating. There must be one or more related rows on the table with further information on what was eaten. There can be no related rows on the , , , , or tables. &gmi_fish; Fishing. There must be exactly one related rows on the table with further information on what was eaten. There can be no related rows on the , , , , or tables. &gmi_other; Other. There can be no related rows on the , , , , , or tables. &gmi_play; Playing. There must be one or more related rows on the table with further information regarding the play. There can be no related rows on the , , , , or tables. &gmi_ride; Riding. There must be exactly one related rows on the table with further information on riding posture. There can be no related rows on the , , , , or tables. &gmi_rest; Resting. There must be exactly one related rows on the table with further information on resting posture. There can be no related rows on the , , , , or tables. &gmi_vocal; Vocalization. There must be one or more related rows on the table with further information on the vocalization. There can be no related rows on the , , , , or tables. The value determines whether a position within the forest canopy is required to be part of the record of the event. When &true; rows having this value must have a non-&null; . value. When &false; the row must have a &null; . value. This check is performed immediately on insert or update of and upon &transaction; commit on update of BEHAVIORS. The control over which behaviors can occur when others occur is supplied by the 4 Boolean columns , . and, . The and columns apply to the infant and the sibling. The and columns apply to the mother, otherwise the functionality supplied is identical. For this reason only the and columns are described in the following few paragraphs. When is &true; the behavior cannot be related to an row also related to another behavior involving the same focal designated unless the focal is the actee (. = '&gmi_actee;'), or unless all such behavioral events are uncertain To be specific: unless the . values of all such behaviors are &true;. . When &false; the behavior can occur with any other. This check is performed immediately, except when data in the is changed table in which case the check takes place upon &transaction; commit. It is expected that many point sample behaviors will have a &true; Exclusive_Behavior value to ensure that they do not happen when, e.g, resting or dangling is occurring, although this does not have to be the case. Behaviors that exclude some others, those with a &true; value, must also be behaviors that are excluded, they must have a &true; value. Social behaviors, behaviors involving 2 individuals Non-social behaviors, the only other sort, involve a single individual. , are said to be either directional or non-directional. Directional behaviors are those where one of the individuals involved initiates or sustains the behavior (the actor), the code for which is &gmi_actor; (actoR), and the other individual is acted upon (the "actee"), the code for which is &gmi_actee; (acteE). In non-directional social behavior either the 2 individuals involved play an equal part or directionality is simply unknown. In either case the 2 individuals involved in social non-directed behavior are both labeled participant, the code for which is &gmi_participant; (Participant). The individual involved in non-social behaviors, those that involve only a single individual, are designated as self -- the single . value is always &gmi_self; (Self). The BEHAVIORS columns , , and control whether, respectively, the mother in the follow, the sibling in the follow, or the infant in the follow are allowed to be actors -- whether their respective . value is allowed to be &gmi_actor;. Should the mother, sibling, or infant be allowed to be an actor then the respective , , and column must contain a value, otherwise the respective column must be &null;. The , , and columns determine whether an individual who is not a focal in the follow is allowed to be involved in the behavioral event as an actee. The column controls whether or not reciprocal directed behaviors are allowed. I.e., whether or not behaviors which are directed, have an actor and an actee, such as grooming, are allowed to be mutual; e.g. whether simultaneous mutual grooming is allowed between any given pair of individuals. &true; means that reciprocal directed behaviors are allowed. &false; means they are not. The column must be non-&null; when there is a possibility of directed interaction -- when any of the BEHAVIORS columns , , , , , , , , , , , or are &true; and must be &null; otherwise. This check is performed upon &transaction; commit, except that when the table is updated the check is performed immediately. The BEHAVIORS columns , , and their respective , , and columns behave as the columns described just above only with respect to the participant role. The BEHAVIORS columns , , control whether, respectively, the mother in the follow, the sibling in the follow, or the infant in the follow are allowed to be involved in the behavior without another individual -- whether their respective . value is allowed to be &gmi_self;. If there is a record of a non-social interaction for an individual then the individual may not also be involved in a non-directed social interaction of this kind during the interval -- if an row exists with a single related row Necessarily having a . value of &gmi_self;. then there may not be other rows related to the row with 2 related rows where either of the 2 rows has a value identifying the individual in question. So, for example, an individual may not self-play while playing with another individual. The general notion of non-directed social interactions is such that, we hope, this rule excludes dis-allowed behavioral combinations but does not interfere with the storage of legitimate behaviors in the database. This check occurs upon &transaction; commit. The column must be non-&null; when the other BEHAVIORS columns allow a non-focal to be involved in the behavior, otherwise must be &null;.

Key: Behavior The BEHAVIORS table is keyed by the Behavior column.

Swahili (Swahili Name) The Swahili name of the behavior. &emptytext; This column may be &null;, which is usually an indication that no Swahili name was used on the data collection templates. And is not, of course, any reflection on the richness of the Swahili language vocabulary, which, based on this author's brief encounter with Gombe-MI project's data collection templates, was thought be so rich as to have a single word for vocalizations-uttered-while-engaged-in-copulation. As it turns out, the word translates as romance -- which was a little disappointing.

Type (Behavior Type) A code designating the behavior as one of a certain type. See the overall description of above for further information. ¬null; The value of this column cannot be changed.

Position (Position Required) A Boolean value; whether or not the event must have a value. ¬null;

Both_Directions (Mutual Directed Behavior is Allowed) A Boolean value. When &true; pairs of individuals may exhibit the behavior twice, each with the directionality of the other reversed. See the overall description of above for further information. This column must be &null; when directed behavior is allowed and otherwise must not be &null;. See the overall description of above for further information.

Restricted (Infant or Sib's Behavior is Exclusive of Some Others) A Boolean value. When &true; the behavior is designated as one that excludes the possibility of the infant or sibling simultaneously having one of the behaviors marked on the minute. When &false; the behavior does not exclude any others. See the overall description of above for further information. ¬null;

Exclusive_Behavior (Behavior is Excluded by Restricted Behaviors) A Boolean value. When &true; the behavior is designated as an infant or sibling behavior that cannot occur with behaviors designated as occurring restricted. When &false; the behavior can occur with any other. See the overall description of above for further information. ¬null;

Mom_Restricted (Mom's Behavior is Exclusive of Some Others) A Boolean value. When &true; the behavior is designated as one that excludes the possibility of the mother simultaneously having one of the behaviors marked on the minute for the mom. When &false; the behavior does not exclude any others. See the overall description of above for further information. ¬null;

Mom_Exclusive_Behavior (Behavior is Excluded by Mom_Restricted Behaviors) A Boolean value. When &true; the behavior is designated as one that cannot involve the mother at the same time as those behaviors designated as mom restricted. When &false; the behavior can occur with any other. See the overall description of above for further information. ¬null;

Mom_Actor (Mother May be Actor) A Boolean value. Whether or not the focal that is the mother may be an actor in this kind of behavioral event. See the overall description of above for further information. ¬null;

Mom_Actee (Mother May be Actee) A Boolean value. Whether or not the focal that is the mother may be an actee in this kind of behavioral event. See the overall description of above for further information. ¬null;

Mom_Participant (Mother May be Participant) A Boolean value. Whether or not the focal that is the mother may be a participant in this kind of behavior in a non-directed but social way, a way involving 2 individuals. See the overall description of above for further information. ¬null;

Mom_Self (Mother May Engage in the Behavior Solo) A Boolean value. Whether or not the focal that is the mother may be involved this kind of behavior in a non-social way, as the sole individual involved. See the overall description of above for further information. ¬null;

Mom_W_NF_Actor (Mom With Non-Focal Actor) A Boolean value. Whether or not an individual who is not a focal may be involved in the behavioral event as an actor when the focal mother is also involved in the behavioral event. See the overall description of above for further information. The value of this column may be &null;; see the overall description of above for further information.

Mom_W_NF_Actee (Mom With Non-Focal Actee) A Boolean value. Whether or not an individual who is not a focal may be involved in the behavioral event as an actee when the focal mother is also involved in the behavioral event. See the overall description of above for further information. The value of this column may be &null;; see the overall description of above for further information.

Mom_W_NF_Participant (Mom With Non-Focal Participant) A Boolean value. Whether or not an individual who is not a focal may be involved in the behavioral event as a non-directional participant when the focal mother is also involved in the behavioral event. See the overall description of above for further information. The value of this column may be &null;; see the overall description of above for further information.

Sib_Actor (Sibling May be Actor) A Boolean value. Whether or not the focal that is the sibling may be an actor in this kind of behavioral event. See the overall description of above for further information. ¬null;

Sib_Actee (Sibling May be Actee) A Boolean value. Whether or not the focal that is the sibling may be an actee in this kind of behavioral event. See the overall description of above for further information. ¬null;

Sib_Participant (Sibling May be Participant) A Boolean value. Whether or not the focal that is the sibling may be a participant in this kind of behavior in a non-directed but social way, a way involving 2 individuals. See the overall description of above for further information. ¬null;

Sib_Self (Sibling May Engage in the Behavior Solo) A Boolean value. Whether or not the focal that is the sibling may be involved this kind of behavior in a non-social way, as the sole individual involved. See the overall description of above for further information. ¬null;

Sib_W_NF_Actor (Sib With Non-Focal Actor) A Boolean value. Whether or not an individual who is not a focal may be involved in the behavioral event as an actor when the focal sibling is also involved in the behavioral event. See the overall description of above for further information. The value of this column may be &null;; see the overall description of above for further information.

Sib_W_NF_Actee (Sib With Non-Focal Actee) A Boolean value. Whether or not an individual who is not a focal may be involved in the behavioral event as an actee when the focal sibling is also involved in the behavioral event. See the overall description of above for further information. The value of this column may be &null;; see the overall description of above for further information.

Sib_W_NF_Participant (Sib With Non-Focal Participant) A Boolean value. Whether or not an individual who is not a focal may be involved in the behavioral event as a non-directional participant when the focal sibling is also involved in the behavioral event. See the overall description of above for further information. The value of this column may be &null;; see the overall description of above for further information.

Infant_Actor (Infant May be Actor) A Boolean value. Whether or not the focal that is the infant may be an actor in this kind of behavioral event. See the overall description of above for further information. ¬null;

Infant_Actee (Infant May be Actee) A Boolean value. Whether or not the focal that is the infant may be an actee in this kind of behavioral event. See the overall description of above for further information. ¬null;

Infant_Participant (Infant May be Participant) A Boolean value. Whether or not the focal that is the infant may be a participant in this kind of behavior in a non-directed but social way, a way involving 2 individuals. See the overall description of above for further information. ¬null;

Infant_Self (Infant May Engage in the Behavior Solo) A Boolean value. Whether or not the focal that is the infant may be involved this kind of behavior in a non-social way, as the sole individual involved. See the overall description of above for further information. ¬null;

Infant_W_NF_Actor (Infant With Non-Focal Actor) A Boolean value. Whether or not an individual who is not a focal may be involved in the behavioral event as an actor when the focal infant is also involved in the behavioral event. See the overall description of above for further information. The value of this column may be &null;; see the overall description of above for further information.

Infant_W_NF_Actee (Infant With Non-Focal Actee) A Boolean value. Whether or not an individual who is not a focal may be involved in the behavioral event as an actee when the focal infant is also involved in the behavioral event. See the overall description of above for further information. The value of this column may be &null;; see the overall description of above for further information.

Infant_W_NF_Participant (Infant With Non-Focal Participant) A Boolean value. Whether or not an individual who is not a focal may be involved in the behavioral event as a non-directional participant when the focal infant is also involved in the behavioral event. See the overall description of above for further information. The value of this column may be &null;; see the overall description of above for further information.

Baboon (Is a Baboon Allowed to Interact?) A Boolean value. When &true; baboons are allowed to be involved in this kind of behavioral event in place of a non-focal chimpanzee -- the related . column may be &true;. When &false; baboons may not be involved in this kind of behavioral event -- the related . column must be &false;. The value of this column may be &null;; see the overall description of above for further information.

Special Values None.

FISHING_RESULTS FISHING_RESULTS contains one row for every code designating a fishing attempt result.

Key: Result The FISHING_RESULTS table is keyed by the Result column.

Special Values None.

FOODKINDS FOODKINDS contains one row for every code designating a kind of food eaten by chimpanzees. The value (when non-&null;) must be unique.

Key: Foodkind The FOODKINDS table is keyed by the Foodkind column.

Scientific (Scientific Name) The scientific name for the kind of food. &emptytext; ¬null;

Fishable (Can the Food be Fished For?) A Boolean value indicating whether the food can be fished for. &true; indicates that the food can be fished for.

Special Values None.

FOODPARTS FOODPARTS contains one row for every code designating a kind of food eaten by chimpanzees. The value (when non-&null;) must be unique.

Key: Foodpart The FOODPARTS table is keyed by the Foodpart column.

Swahili (Swahili Name) The Swahili name for the kind of food. &emptytext; &null;

Fishable (Can the Food be Fished For?) A Boolean value indicating whether the food can be fished for. &true; indicates that the food can be fished for.

Special Values None.

POSITIONS POSITIONS contains one row for every code designating position within the forest canopy defined (past or present) in the data collection protocol.

Key: Position The POSITIONS table is keyed by the Position column.

Special Values None.

PLAY_TYPES PLAY_TYPES contains one row for every designated play activity defined (past or present) in the data collection protocol.

Key: Play The PLAY_TYPES table is keyed by the Play column.

Special Values None.

RESTING_POSTURES RESTING_POSTURES contains one row for every designated resting posture defined (past or present) in the data collection protocol.

Key: Rest The RESTING_POSTURES table is keyed by the Rest column.

Special Values None.

RIDING_POSTURES RIDING_POSTURES contains one row for every designated riding posture defined (past or present) in the data collection protocol.

Key: Ride The RIDING_POSTURES table is keyed by the Ride column.

Special Values None.

VOCALIZATIONS VOCALIZATIONS contains one row for every code designating a kind of vocalization made by chimpanzees as defined (past or present) in the data collection protocol.

Key: Vocalization The VOCALIZATIONS table is keyed by the Vocalization column.

Special Values None.

&distance_section; Support Tables

DISTANCECODES DISTANCECODES contains one row for every distance category defined (past or present) in the data collection protocol.

Key: Distance The DISTANCECODES table is keyed by the Distance column.

Special Values The value &gmi_distunk; is used to specify that a distance was observed but the true value cannot now be determined.

&group_section; Support Tables

GCSTATUSES GCSTATUSES contains one row for every group composition status. These values indicate the state or source of all the group composition information related to any one follow.

Key: GCStatus The GCSTATUSES table is keyed by the GCStatus column.

Special Values The value &gmi_automatic_gcstatus; indicates that the row has been automatically created by the system. By the function.

GM_ORIGINS GM_ORIGINS contains one row for every group membership origin code. These codes indicate the origin of the group composition data for the particular group member.

Key: Origin The GM_ORIGINS table is keyed by the Origin column.

Special Values The value &gmi_automatic_origin; indicates that the row has been automatically created by the system.

The Gombe-MI Views The database structures holding the Gombe-MI data are constructed in a fashion which enables arbitrary and efficient data retrieval. Because these tables and their relationships are not necessarily convenient or understandable to the average user of the Gombe-MI system, the system uses the view database feature to provide simplified pictures of the data. Views are nothing more than queries, queries which appear to be tables. Views differ from tables only in that some views may not be used to change (insert, update, or delete) the underlying data.

&demography_section; Views

BIOGRAPHY BIOGRAPHY contains one row per row. It provides a stable interface into the imported Access table , allowing the underlying table to change without affecting Gombe-MI. This view should be used instead of . The view contains all of 's columns, plus the of the individual's twin, from ., if a twin. Of course in most cases there is no twin and the column will be &null;.

BIOGRAPHY Definition

BIOGRAPHY Columns Column From Description AnimID . Identifier of the individual. AnimID_Num . Identifier for the individual in the SIV study. AnimName . Name of the individual. BirthGroup . Birth community of the individual. BGCertainty . Certainty of the individual's birth community. Sex . Sex of the individual. MomID . Identifier of the individual's mother. DadID . Identifier of the individual's father. Dad_Prelim . Whether paternity is preliminary. Dad_Published . Whether paternity has been published. FirstBorn . Whether the individual is the mother's firstborn. Birthdate . Birth date of the individual. BDMin . Minimum possible birth date. BDMax . Maximum possible birth date. BDDist . Probability distribution of the individual's birth date estimate. Entrydate . Individual's date of entry into the study. Entrytype . Individual's method of entry into the study. Departdate . Individual's date of departure from the study. DepartdateError . Degree of error in departure date. Departtype . Method of individual's departure from the study. ImportDatetime . Timestamp of import from the master Access database. Twin . Identifier of the individual's twin.

BIOGRAPHY Usage Guidelines

INSERTing Into BIOGRAPHY INSERTing into BIOGRAPHY inserts rows into as expected. A row is inserted into the table if the value is non-&null;, otherwise a row is not inserted.

UPDATEing BIOGRAPHY Updating BIOGRAPHY updates the rows of as expected. Setting the Twin column to &null; results in deletion of a related row, if any exists. Setting the Twin column to a non-&null; value either updates the existing . value or creates a new row, as necessary.

DELETEing from BIOGRAPHY Deleting rows from BIOGRAPHY deletes rows from and , as expected.

BIOGRAPHY_UPLOAD (Upload into BIOGRAPHY_DATA) The purpose of the BIOGRAPHY_UPLOAD view is to provide a convenient interface for uploading the master Access biography table into the Gombe-MI table. It contains columns for each of the columns in the master Access biography table, named as they are named in the master table. Character case is significant in BIOGRAPHY_UPLOAD's column names. The column names must be written exactly as shown (and enclosed in double quote characters (") when used in SQL). This is to match Duke's column names and avoid having to change the column headings when uploading Duke's data. BIOGRAPHY_UPLOAD provides features upon data INSERT to ease the upload process. It serves to translate the column names in the master Access biography table into the column names in . It removes trailing spaces from all textual columns. The . column is set to &true; when the BIOGRAPHY_UPLOAD.B_DadID column ends in &gmi_dadid_prelim; and &false; otherwise. The suffix &gmi_dadid_prelim; is removed from the BIOGRAPHY_UPLOAD.B_DadID value when it is stored in the . column.

BIOGRAPHY_UPLOAD Definition Because there is nearly The exception being the column, which is not included in the view. a one-to-one match between the columns in this view and the master Access table there is no additional documentation of this view's columns. See the &gcdbh; and the table for further information.

BIOGRAPHY_UPLOAD Usage Guidelines INSERTing into this view trims trailing spaces from all inserted textual data. Non-textual data is inserted unaltered. Although it is not recommended to do anything other than INSERT into this view as part of a bulk upload of the master Access biography table, there are no special restrictions on the use of this view. SELECT, INSERT, UPDATE, and DELETE are all allowed and perform the expected operations on the underlying table.

CHIMPS (Real and Virtual Chimpanzees) CHIMPS contains one row per row plus one extra row for each twin. The additional rows are duplicates of each twin, and are used only when the twins are were indistinguishable in the field (e.g., observers used TW1 or TW2 in place of the twin's name). This view should be used for querying instead of , so long as it is safe to consider the temporary identifiers used when the twins cannot be distinguished as representative of individuals that are identical to, but distinct from, the twins themselves. This might be never. Which is why the table exists; so that use of the temporary twin identifiers does not imply existence of additional individuals. Twins will always cause headaches, but at least the database can recognize the temporary identifiers as representing virtual rather than real individuals. Using the view should be safe enough when simply retrieving demographic data from , but care must be taken when using and doing any sort of counting of individuals. One way to take care is to pay attention to values. The CHIMPS view contains all of 's columns, plus the column, plus the of the individual's twin, from ., if a twin. Of course in most cases there is no twin and the column will be &null;. Note that while and have the view there is no corresponding view for , although such a view could be created.

CHIMPS Definition

CHIMPS Columns Column From Description ChimpID . Identifier or alternate identifier of the individual. AnimID . Identifier of the individual. AnimID_Num . Identifier of the individual used in SIV study. AnimName . Name of the individual. BirthGroup . Birth community of the individual. BGCertainty . Certainty of the individual's birth community. Sex . Sex of the individual. MomID . Identifier of the individual's mother. DadID . Identifier of the individual's father. Dad_Prelim . Whether paternity is preliminary. Dad_Published . Whether paternity has been published. FirstBorn . Whether the individual is the mother's firstborn. Birthdate . Birth date of the individual. BDMin . Minimum possible birth date. BDMax . Maximum possible birth date. BDDist . Probability distribution of the individual's birth date estimate. Entrydate . Individual's date of entry into the study. Entrytype . Individual's method of entry into the study. Departdate . Individual's date of departure from the study. DepartdateError . Degree of error in departure date. Departtype . Method of individual's departure from the study. ImportDatetime . Timestamp of import from the master Access database. Twin . Identifier of the individual's twin.

CHIMPS Usage Guidelines The CHIMPS view cannot be INSERTed into, UPDATEed, or DELETEd from. Use the view instead.

COMMUNITY_MEMBERSHIP_UPLOAD (Upload into COMMUNITY_MEMBERSHIP) COMMUNITY_MEMBERSHIP_UPLOAD (Upload COMMUNITY_MEMBERSHIP) The purpose of the COMMUNITY_MEMBERSHIP_UPLOAD view is to provide a convenient interface for uploading the master Access community_membership table into the Gombe-MI table. It contains columns for each of the columns in the master Access community_membership table, named as they are named in the master table. COMMUNITY_MEMBERSHIP_UPLOAD provides two features to ease the upload process. It serves to translate the column names in the master Access community_membership table into the column names in . And it removes trailing spaces from all textual columns.

COMMUNITY_MEMBERSHIP_UPLOAD Definition Because there is a one-to-one match between the columns in this view and the master Access table there is no additional documentation of this view's columns. See the &gcdbh; and the table for further information.

COMMUNITY_MEMBERSHIP_UPLOAD Usage Guidelines INSERTing into this view trims trailing spaces from all inserted textual data. Non-textual data is inserted unaltered. Although it is not recommended to do anything other than INSERT into this view as part of a bulk upload of the master Access community_membership table, there are no special restrictions on the use of this view. SELECT, INSERT, UPDATE, and DELETE are all allowed and perform the expected operations on the underlying table.

&follow_section; Views

FOCAL_FOLLOWS (Follows and their Focals) FOCAL_FOLLOWS contains one row per row. The purpose of the FOCAL_FOLLOWS view is to provide a convenient interface for deleting and creating follows. In the case of twins, the column suffixed with the number 2 always contains the twin with the which is alphabetically last.

FOCAL_FOLLOWS Definition

FOCAL_FOLLOWS Columns Column From Description FollowID . Unique integer identifying the follow. Date . The date of the follow. Mom . The of the mother in the follow. Infant . The of an infant in the follow. Infant2 . The of a second infant in the follow, or &null; if the infant is not a twin. Sib . The of a sibling in the follow, or &null; if there is no sibling focal. Sib2 . The of a second sibling in the follow, or &null; if the sibling is not a twin.

FOCAL_FOLLOWS Usage Guidelines Altering the FOCAL_FOLLOWS view alters the content of the underlying tables in the fashion described below.

INSERT Inserting a row into the FOCAL_FOLLOWS view inserts a row into the table, as expected. A row is also inserted into the table for each non-&null; , , , , and value. The inserted . value is the , , , , or value and the . value is set to the corresponding &gmi_mom_role;, &gmi_infant_role;, or &gmi_sib_role; value. On insert there is no distinction between and , nor a distinction between and . Either may contain the alphabetically larger , and when not a twin, either may be &null;. When inserting into FOCAL_FOLLOWS it is not recommended to set the value. Omitting this column or supplying a &null; value will allow the system to automatically generate an id.

UPDATE Updating the columns of the FOCAL_FOLLOWS view updates the underlying tables as expected with the following exceptions: Setting any one of the , , or values to &null; deletes the corresponding row. Setting any one of the , , or values to non-&null;, where the original value was &null; inserts a corresponding row into the table as described above regards insertion into FOCAL_FOLLOWS. Updating the columns of FOCAL_FOLLOWS, excepted, will probably result in data integrity violations. Deleting and re-uploading the follow may be a better option. On update there is no distinction between and , nor a distinction between and . Either may contain the alphabetically larger , and when not a twin, either may be &null;. However, it is an error to attempt to swap twins -- e.g., by setting the value to the value or vice-versa.

DELETE Deleting rows from FOCAL_FOLLOWS deletes rows from the underlying tables as expected. Because deleting a row from automatically deletes all information related to the follow this is a convenient way to remove follows from the database.

DATASHEETS (A Field Datasheet-Like Report) DATASHEETS contains one row per row plus one row per row. It's purpose is to produce a report resembling While it is possible to use the window function &postgresql; feature to produce an improved report-like format by eliminating rectangular areas of empty space in the query output, this was not deemed important enough to justify the time required. The reader is left to to complete this as an exercise in query construction. the data entry sheets. The columns of the DATASHEETS view resemble the columns on the field data entry collection sheets. Due to the large number of these columns and the degree to which data is transformed when uploaded into GOMBE-MI the details regarding individual columns are not documented.

DATASHEETS Definition The DATASHEETS view is a combination of the and views, organized and sorted in a fashion such that the rows interleave. See the documentation on these views for further detail regards the content of the DATASHEETS view.

DATASHEETS Usage Guidelines Only SELECT is allowed on DATASHEETS. INSERT, UPDATE, and DELETE are not allowed. Related views which may be used to modify the database are: , , , and .

DATASHEET_EVENTS (A Field Datasheet-Like Report Of Behavioral Events) DATASHEET_EVENTS contains one row per row. It's primary purpose is to produce a report resembling the behavioral event portion of the data entry sheets. It is also possible to use the DATASHEET_EVENTS view to alter database content. For more on this see below. The columns of the DATASHEET_EVENTS view resemble the behavior related columns on the field data entry collection sheets. Due to the large number of these columns and the degree to which data is transformed when uploaded into GOMBE-MI the details regarding individual columns are not documented. The overall structure of the DATASHEET_EVENTS view is as follows: The view is extended, per possible participant, with all of the columns that appear in the related sub-tables. So, for example, there are vocalization columns containing . values for each of: mother, infant, sib, and non-focal. If the behavioral event is not a vocalization or respective individual is not involved in the behavioral event then the value of the individual's vocalization column is &null;. If the behavioral event is a vocalization then the column(s) corresponding to the individual(s) involved in the event contain the vocalization made by the respective individual. This pattern is repeated for all of the table's sub-tables.

DATASHEET_EVENTS Definition The DATASHEET_EVENTS view contains all the columns in the view. In the interest of brevity these columns are not documented here. Please refer to the table describing the columns of FAMILY_EVENTS for information on these columns. The columns on DATASHEET_EVENTS that are unique to the view are all from the sub-tables: , , , , , and . Each column in these sub-tables occurs three times in the DATASHEET_EVENTS view, once for the mother, once for the infant and once for the sibling. Rather than document each column 3 times, once for each of the focals, the columns are documented only once in the table below. The columns documented below have either Mom, Infant, or Sib in place of <focal> in their column names. The exception is the E_<focal>_ride column. There is no such column for the mother, although there is for the infant and the sibling.

DATASHEET_EVENTS Columns, not in the FAMILY_EVENTS View Column From Description E_<focal>_Foodpart . Code indicating the food part eaten by the focal during the event, or &null; if the focal was not involved in a eating event. E_<focal>_Foodkind . Code indicating (one of) the food kind(s) eaten by the focal during the event, or &null; if the focal was not involved in a eating event. E_<focal>_Fishpart . Code indicating (one of) the food part(s) fished for by the focal during the event, or &null; if the focal was not involved in a fishing event. E_<focal>_Fishkind . Code indicating the food kind fished for by the focal during the event, or &null; if the focal was not involved in a fishing event. E_<focal>_Fishresult . Code indicating the result of the focal's fishing attempt, or &null; if the focal was not involved in a fishing event. E_<focal>_Voc . Code indicating (one of) the focal's vocalizations made during the vocalization event, or &null; if the focal was not involved in a vocalization event. E_<focal>_Play . Code indicating (one of) the focal's play activities conducted during the playing event, or &null; if the focal was not involved in a playing event. E_<focal>_Ride . Code indicating the focal's riding posture during the riding event, or &null; if the focal was not involved in a riding event. E_<focal>_Rest . Code indicating the focal's resting posture during the resting event, or &null; if the focal was not involved in a resting event.

DATASHEET_EVENTS Usage Guidelines Altering the DATASHEET_EVENTS view alters the content of the underlying tables in the fashion described below. Related views which also may be used to modify the database are: , , and .

INSERT Inserting into DATASHEET_EVENTS first tests to see if the inserted row already exists in . Unlike usual, a &null; inserted value, or omitting the column from the insert statement, matches any existent data value. This allows, e.g., id columns to be omitted or specified as &null;. It is an error if more than one row in matches the inserted row. Of course more than one row can be inserted into DATASHEET events in a single INSERT statement. The requirement is that each inserted row be sufficiently specified. If the inserted row does not already exist in , inserting a row into the DATASHEET_EVENTS view inserts one row into either the view, for insertion into the underlying and tables. For further information see the FAMILY_EVENTS insert documentation. Inserting into DATASHEET_EVENTS can also insert a row into the sub-tables: , , , , , and . If the inserted row already exists on then inserting into DATASHEET_EVENTS can add additional rows to the sub-tables related to the existing row; as might be required, for example, when a single vocalization event is comprised of are multiple vocalizations . It is an error to attempt to insert data into an sub-table using a column pertaining to a focal when that focal is not involved in the given behavioral event. For example, it is an error to attempt to use the E_Mom_Voc column to associate a vocalization with a behavioral event involving only the infant. As can be seen from the documentation on updating FAMILY_EVENTS, the system does not really care about whether the column name pertains to a particular focal or not. It's possible for the system to ignore the focal associated with an sub-table and simply insert data into the appropriate table, relating it to the event at hand. However at this point it seems preferable to have DATASHEET_EVENTS perform this check.

UPDATE Updating a row in DATASHEET_EVENTS updates a row in the view, updating the underlying and tables. For further information see the FAMILY_EVENTS update documentation. Updating DATASHEET_EVENTS can update or delete rows from , , , , , and . The examples below explicitly specify an . value. This is solely for the purpose of brevity. Any valid WHERE clause could be used instead to designate the desired row in . If the old values for a mom, infant, or sib sub-table row are not &null; and the new values are all &null; then the old row is deleted. The following example removes the mother's HOO vocalization from the event with an . of 1234: Deleting The Mother's VOCS Row

UPDATE datasheet_events
       SET e_mom_voc = NULL
       WHERE eid = 1234
             AND e_mom_voc = 'HOO';

If the old vocalization value is not specified then all rows related to the event will be deleted. If old values exist, they are replaced by new values. Assuming the mother is involved in a vocalization the event with an . of 1234, the following example changes the UNK vocalization to HOO: Changing A Vocalization

UPDATE datasheet_events
       SET e_mom_voc = 'HOO'
       WHERE eid = 1234
             AND e_mom_voc = 'UNK';

The values need not correspond to the roles the individuals play in the follow because data is associated with chimp id values, not focal roles. The presence of focal roles in the column names of the view is, in some sense, artificial. For example, the following changes the focal involved with a vocalization event from mother to sibling, and changes the vocalization made from UNK to HOO: Changing a Vocalization Event to Involve the Sibling Instead of the Mom and Changing the Vocalization Made Changing a Vocalization: Changing focal and vocalization

UPDATE datsheet_events
       SET e_mom_voc = 'HOO'
         , e_mom = 'SB'  -- SB is the sibling's ChimpID
       WHERE eid = 1234
             AND e_mom_voc = 'UNK';

The above is sufficient to re-use the exiting row, changing the mother's value to the siblings, and reuse the existing row, changing the vocalization. When the result is queried SB will show up as a sibling with a sibling vocalization of HOO. Updating &null; values, when all related values are &null;, in the columns of the , , , , , or tables, inserts a new row into one of these tables. To insert a new row in for the mother, when all of the mom's data is &null; (E_Mom_Fishpart, E_Mom_Fishkind, E_Mom_Fishresult), update the mom's data. The following example shows how to do this, changing an other behavior into a fishing behavior. Changing a Mother's Behavior into a Fishing Behavior and Inserting a FISHED Row For the Mother Changing a Behavior to a Fishing Behavior

UPDATE datasheet_events
       SET behavior = 'F'  --  F is fishing
         , e_mom_fishpart = 'A'
         , e_mom_fishkind = 'B'
         , e_mom_fishresult = 'C'
       WHERE eid = 1234;

As with , , and the focal associated with the name of the column is not relevant when inserting new rows via an UPDATE statement. If the DATASHEET_EVENTS row represents an observation of the mother vocalizing, setting the E_Infant_Voc value will give the mother an additional vocalization row on the table. It is easy to unintentionally add additional rows to , , , , , or . To avoid this do not attempt to update an existing &null; value in , , , , , or . Instead update the non-&null; value, no matter if the focal is changing and the name of the column containing the old value does not pertain to the new focal. There is an example of this above. To insert a new row in for the mother, regardless of whether the mother has existing related rows on for the given behavioral event, update the infant or sibling's data. The odd nature of this procedure is due to the lack of , , , , , or id columns on DATASHEET_EVENTS. Id values could be used to signal whether an existing row should be altered or a new row inserted. However, additional id columns would clutter the view, which already has over-many columns. The following example shows how to do this: This works due to the lack of association on update between the column names and the focal, and because the infant's column values will always be &null; so changing them to a non-&null; value causes a row to be inserted. Unconditionally Inserting a FISHED Row For the Mother

UPDATE datasheet_events
       SET e_infant_fishpart = 'A'  -- note change of "infant's" data
         , e_infant_fishkind = 'B'
         , e_infant_fishresult = 'C'
       WHERE eid = 1234;

It is an error to attempt to update the values of any columns not derived from the , , , , , , , or tables. It is also an error to attempt to change the value. When updated, DATASHEET_EVENTS raises a notice containing the number of rows altered. A row is counted only once, when updating the row changes the database content in any way.

Delete Deleting rows from the DATASHEET_EVENTS view deletes rows from , , , , , , , and tables as expected.

FOLLOW_DURATIONS (Duration of the Follow) FOLLOW_DURATION contains one row per follow. It's purpose is to compute the various totals regards observation time per follow.

FOLLOW_DURATIONS Definition

FOLLOW_DURATIONS Columns Column From Description FollowID . Unique integer identifying the follow. FollowMins (Attempted Observational Minutes) Computed The number of minutes during which observations where attempted. EmptyMins (Bad Observational Minutes) Computed The number of minutes during which observations where attempted but no data was collected. No data is collected if there are no rows related to the interval and no rows related to the interval. SpanMins (Span of Minutes in Follow) Computed. The number of minutes, including endpoints, between the first observation of the follow and the last observation of the follow.

Operations allowed Only SELECT is allowed on FOLLOW_DURATION. INSERT, UPDATE, and DELETE are not allowed.

FOLLOW_INTERVALS (Follows and their Intervals) FOLLOW_INTERVALS contains one row per row per row. So, one row per minute of each follow. The purpose of the FOLLOW_INTERVALS view is to provide a convenient interface for deleting, creating, and altering rows and for creating and rows. See the description of the view for more information regarding the handling of twins.

FOLLOW_INTERVALS Definition

FOLLOW_INTERVALS Columns Column From Description FollowID . Unique integer identifying the follow. IntID . Unique integer identifying the interval. SheetID . The integer identifying the data collection sheet on which the interval was recorded. FFID . The integer identifying the field follow, the set of sheets collected by a researcher in the field comprising a day's mother-infant follow, in which the sheet belongs. Date . The date of the follow. Mom . The of the mother in the follow. Infant . The of an infant in the follow. Infant2 . The of a twin infant in the follow, or &null; when the infant is not a twin. Sib . The of a sibling in the follow, or &null; when there is not sibling focal in the follow. Sib2 . The of a twin sibling in the follow, or &null; when there is no sibling or the sibling is not a twin. Time . The time of the interval. GID . The identifier of the group composition related to the interval. The system will automatically compute this when a &null; value is supplied upon insert or update. Comment . The comment on the interval. Followtype . Code indicating the data collected on the data collection sheet. Page . The page number on the field data collection sheet. Location . The valley location information on the field data collection sheet. Collector . The field researcher who collected the information on the field data collection sheet. Entrydate . The date the data entry technician converted the information on the field data collection sheet into electronic form. Enterer . The data entry technician who converted the information on the field data collection sheet into electronic form. UploadDate . The date and time the information on the field data collection sheet was uploaded into into the database. GCStatus . The code indicating the status of the group composition information, or &null; if there is no group composition information.

FOLLOW_INTERVALS Usage Guidelines Altering the FOLLOW_INTERVALS view alters the content of the underlying table in the fashion described below. Inserting into FOLLOW_INTERVALS can also insert into the and table. The and related rows must exist; the content of these tables is never altered.

INSERT Inserting a row into the FOLLOW_INTERVALS view inserts a row into the table, as expected. When inserting rows using FOLLOW_INTERVALS the follow must be specified. The follow may be specified in either or both of the following 2 ways: A may be supplied. , and, values all may be supplied. In the case of twins, either twin may be supplied as the , or the (or likewise with the sibling columns). The correct follow will be identified so long as the indicated twin is a focal in the follow. However, if a RETURNING clause is used the twins will be returned in column names as if queried, irrespective of how specified in the inserted values. FOLLOW_INTERVALS inserts only when there is a row that matches the above selection criteria. If no row matches the database content is not altered. Note that no row will match if conflicting information is supplied. When inserting into FOLLOW_INTERVALS it is not recommended to set the value. Omitting this column or supplying a &null; value will allow the system to automatically generate an id. Inserting a row into FOLLOW_INTERVALS will also insert a row into if the specified related row does not already exist. The related row may be specified in either or both of the following 2 ways: A value may be supplied. A value may be supplied. Additional column values may be supplied. (The , , and values.) As usual, omitting any column is the same as specifying a &null; value. It is an error for any of the supplied, non-&null;, values to conflict with the content of an existing row when the row is related to the newly inserted row. If there is no existing row then the supplied values are stored in a newly created row. When inserting into FOLLOW_INTERVALS and creating a new row it is not recommended to set the value. Omitting this column or supplying a &null; value will allow the system to automatically generate an id. Inserting a row into FOLLOW_INTERVALS will also insert a row into if the specified related row does not already exist. The related row may be specified in either or both of the following 2 ways: A value may be supplied. A value may be supplied. Supplying a value serves to identify a row only when there is already a relationship between the follow and a field follow conducted by the designated collector. If the field data collector is not already in the database as having collected data for the follow, (or has, for some reason, performed more than one bout of data collection regards the follow) then the system cannot use the collector to identify a single, existent, field follow to relate to the new interval. Additional column values may be supplied. (The , , , values.) As usual, omitting any column is the same as specifying a &null; value. It is an error for any of the supplied, non-&null;, values to conflict with the content of an existing row when the row is related to the newly inserted row. If there is no existing row then the supplied values are stored in a newly created row. When inserting into FOLLOW_INTERVALS and creating a new row it is not recommended to set the value. Omitting this column or supplying a &null; value will allow the system to automatically generate an id. While the system does not dis-allow multiple row per per row, just in case a single researcher's daily follow sheets of a single mother/infant pair are somehow separated into two packets that are subsequently entered into the system at different times, inserting into FOLLOW_INTERVALS ensures that there is always a single row per field data collector per date per mother/infant pair.

UPDATE Updating the columns of the FOLLOW_INTERVALS view updates the underlying table as expected. It is an error to attempt to update the values of any columns not derived from the table.

DELETE Deleting rows from FOLLOW_INTERVALS deletes rows from the underlying table as expected. Because deleting a row from automatically deletes all information related to the interval this is a convenient way to remove intervals from the database.

UPLOAD_BEHAVIORS (Facility for uploading follow data) This view returns no rows, it is used only to upload follow data. This view exists instead of a custom upload program. The columns of the UPLOAD_BEHAVIORS view resemble the columns on the field data entry collection sheets. Only some of these columns are required, others may be present or omitted depending on the template type of the sheet used in the field. Omitting a column is equivalent to including the column and specifying a &null; value for all the column's rows. Required columns are either documented as such below or are required to have non-&null; values in the database. The UPLOAD_BEHAVIORS view does not validate the existence or absence of specific columns based on the template type. It is up to the data entry staff to use the correct columns. Depending on the program used to upload data into this view, the case of the column names can be significant. All column names, in this view and everywhere else in Gombe-MI, are in lower case. Column names are written here in mixed case as a lexical convention so that column names can be easily discerned from regular text. The use of the underscore character in place of spaces in the column names is always significant. It is recommended to always use lower case column names when uploading data. values of the focals are not required to be consistent throughout the follow. The first row containing data for any given follow will have it's ChimpID values used as . values. The ChimpID values supplied in each line uploaded are used as . values for the behavioral interactions the line records and as . values for the inter-focal distances the line records. The values of the focals must remain consistent from line to line of any given focal, but the used may vary from line to line. If any of a follow's uploaded lines contains a value then all the uploaded lines for the follow must contain a value. This is true not only for the lines uploaded in any given file but is also must be the case if a follow's lines are uploaded in multiple files. If is &null; then it is an error for any of the following columns to contain data: The time values, , in the uploaded lines cannot decrease when examined in line-number order on a per-fieldfollow basis (i.e. per date/mom/field-data-collector). However, subsequent uploads may add new intervals to an existing follow irrespective of the times of pre-existent behavioral intervals. The page number values, , in the uploaded lines cannot decrease when examined in line-number order on a per-fieldfollow basis. However, a &null; page number can occur on any line. Further, this restriction is per file uploaded. Subsequent uploads may add additional behavioral information to any existing or newly uploaded page regardless of page number. The column affects the certitude of all data recorded on the line. It may contain the following values: No characters or &null; All the data recorded on the line is certain, unless another column's value indicates otherwise. &gmi_badobs_y; or &gmi_badobs_w; All the data recorded on the line is uncertain. &gmi_badobs_m; The data related to the mother is uncertain. &gmi_badobs_i; or &gmi_badobs_t; The data related to the infant is uncertain. &gmi_badobs_s; or &gmi_badobs_k; The data related to the sibling is uncertain. &gmi_badobs_mi; or &gmi_badobs_mt; The data related to the mother and the data related to the infant is uncertain. &gmi_badobs_ms; or &gmi_badobs_mk; The data related to the mother and the data related to the sibling is uncertain. &gmi_badobs_is; or &gmi_badobs_kt; The data related to the infant and the data related to the sibling is uncertain. The column is used to denote uncertainty regarding data contained in the sibling related columns -- those columns beginning with sib_. is used in conjunction with . Any given behavioral or distance observation is denoted uncertain when uncertainty is indicated either by the or columns or by the data value of the given observation. The uploaded behavior code &gmi_ignore_mark; is ignored. Behavior codes are translated from the code or column written on paper in the field into the codes used within Gombe-MI by reference to the table. This provides a degree of flexibility; adding new rows to (and ) may allow new behavioral observations to be collected and uploaded into Gombe-MI without having to modify the system's code. The behavior code to use for the eating behavior associated with the , , , , , and columns and the fishing behavior associated with the , , , , , and columns is determined by looking up &gmi_ma_foodpart;, &gmi_inf_foodpart;, &gmi_sib_foodpart;, &gmi_ma_fishpart;, &gmi_inf_fishpart;, and &gmi_sib_fishpart; on the table. (The foodkind and fishkind columns have no row on . When there are multiple food part codes in the , , , , , or columns a separate row on or is created for each of them. The , , , , , or value is used as the or value for each such created row. When the uploaded followtype value identifies a row on with a . value of &true; UPLOAD_BEHAVIORS looks up the following column names in to determine the implied behavior code: When . is &true; the implied behavior code is only used if the data in the above columns has not been processed due to the presence of an uploaded behavior code. The implied behavior code is therefore the default behavior when no behavior code is listed. When . is &false; the data in these columns is processed only when an appropriate Based on a behavior code written into the data and according to the corresponding row. behavior value is encountered. Should there be no such behavior code UPLOAD_BEHAVIORS will raise an error if the above columns contain data. The following columns always imply a behavior That is, they are processed as if . is &true; regardless of it's actual value. : Controls other aspects of the upload process. When . is &true;, and the behavior is between focals, and the behavior is uploaded as a behavior code and not a column dedicated to a specific behavior Duplicate detection is hardcoded into UPLOAD_BEHAVIORS for those columns that are dedicated to specific behaviors. , UPLOAD_BEHAVIORS expects that the behavior to be recorded twice on the uploaded line, once for each focal. If this is not the case an error is reported. If either (or both) of the observations is uncertain then the observation is recorded in the database (once) as uncertain. If the . values do not match an error is reported. ., , as well as the name of the column, controls whether the behavior is considered a social play behavior. When . is non-&null;, or the column data is under consideration and the infant is not the individual listed in the column, and the behavior is between focals, UPLOAD_BEHAVIORS considers the play to be a directed (actor/actee) behavior. The program also checks for reciprocal actor/actee play relationships between focals, having identical . codes, and, if found, ensures that the 2 observations recorded in the field are uploaded into the database as a single mutual behavioral interaction, as a single row having . values of &gmi_participant;. So, for example, when an uploaded line records the mother playing with the infant (in a directed fashion) and the infant playing with the mother (in a directed fashion, both events having the same code for play) a single mutual (undirected) play event is recorded in the database. If either (or both) of the observations is uncertain then the observation is recorded in the database (once) as uncertain. If the . values do not match an error is reported. When . is non-&null;, or the column data is under consideration, and the behavior is not between focals, UPLOAD_BEHAVIORS considers the play to be mutual -- the . values are set to &gmi_participant;. When the column data lists the focal infant the behavior is considered to be self-play. The old Excel data format does not designate self-play by listing the focal infant in the column. Instead self-play is designated by use of the column. In order to accommodate the new Excel data format the rules for were altered to allow the coding for self-play regardless of whether old or new data is uploaded. Consequently, should the old data need to be re-uploaded, improper data may be silently ignored and incorrect self-play behaviors may be inadvertently uploaded into the system. ., as well as the data in the column, controls whether the behavior is considered one where the focal grooms another individual; ., as well as the data in the column, controls whether the behavior is considered to be one where the focal is groomed by another individual; and a non-&null; . column indicates that the behavior is considered to be one where the focal rides on another individual. When the grooming behavior is identified by a code rather than by ChimpIDs entered into a column it is an error to upload a behavior code with a ., or value of &true;, or a or value which is non-&null; without at least one ChimpID value in a corresponding column to provide a social partner for the focal. Unless UPLOAD_BEHAVIORS is to take the mother's grooming and play partners from separate columns on any one uploaded line the individuals with whom the mother groomed and played with must either both be recorded in a single column or be recorded in separate columns -- unless is &false; there cannot be data in both and , nor may there be data in both and . Yes, it is allowed to have data in the column even though the data is ignored. It is an error to upload a row where the mother's grooming and play partners are combined into a single column and there is more than one grooming and play partner; UPLOAD_BEHAVIORS is then unable to determine which partners were play partners and which were grooming partners. The &gcdbh; reserves some AnimID values to carry special meaning. These may not be used as . values or as . values. These special values have the following effect when uploaded into UPLOAD_BEHAVIORS: Reserved AnimID Values &gmi_animid_qmark; In most cases the focal is recorded as interacting with the &gmi_unk_animid; ChimpID (in .) and the . value is set to &true;. In the case of the column the focal is recorded as interacting with the mother and the . value is set to &true;. In the case of the column the focal (the infant) is recorded as interacting with herself and the . value is set to &true;. &gmi_animid_y; In most cases the focal is recorded as interacting with the &gmi_unk_animid; ChimpID (in .). In the case of the , , , , and columns the focal is recorded as interacting with the mother. In the case of the column the focal (the infant) is recorded as interacting with herself. &gmi_animid_baboon; The focal is recorded as interacting with a baboon -- . is set to &true; and there is not a row recording the focal's behavioral partner. UPLOAD_BEHAVIORS does not vary the . value when recording a fishing event. The value used is always &gmi_fishing_success; to indicate successful fishing. The and data are added to the database as a single vocalization event (a single row) in which, possibly, more than one sound is made (possibly having more than one related rows). The column is examined on upload and sometimes altered and a new behavioral event created. When the column contains the character sequence &gmi_devbeh; (case sensitive) and the columns , , , , , , , , , , , and are all empty then the comment text is examined to see if the &gmi_devbeh; characters are immediately followed by at least one character surrounded by square braces, []. If not an error is raised. If so then the a behavioral event is recorded in the database, the behavior code being obtained from the . value where the . is devbeh, the &gmi_devbeh; character sequence, the square braces with follow it and the text enclosed by the square braces are recorded in the . value of the newly created behavioral event. Note that the text of the . is unchanged; it retains the text copied into the . column.

UPLOAD_BEHAVIORS Definition

UPLOAD_BEHAVIORS Columns Column Uploads into Description Followtype . Code indicating the set of data collected. ¬null; &remove_spaces; Entrydate . Date the data collection sheet was entered into an electronic form. This value may be &null; under certain conditions, see .. Entered_By . Code for the person who entered the data into electronic form. ¬null; &remove_spaces; &case_sensitive; Researcher . Written out name of the person who collected the data in the field. ¬null; &remove_spaces_case; Date . Date of the follow. ¬null; Page . Page number of the data collection sheet on which the data is written. All of any one follow's data rows having &null; page numbers are considered to have been written on a single sheet (a sheet having a &null; page number). Location . Location of follow. &remove_spaces; Mother ., ., ., . ChimpID of the mother in the follow. The related . value is always set to &gmi_mom_role; Irrespective of the content of the table. when the follow is created. &remove_spaces; Infant ., ., ., . ChimpID of the infant in the follow. The related . value is always set to &gmi_infant_role; Irrespective of the content of the table. when the follow is created. &remove_spaces; Sibling ., ., ., . ChimpID of the sibling in the follow. The related . value is always set to &gmi_sib_role; Irrespective of the content of the table. when the follow is created. &remove_spaces; This column may be &null; to indicate that there is no sibling. Time_On_Sheet &ignored_column; Time value written on data collection sheet. Time_Converted_Mil . Time of the 1-minute interval in 24-hour clock format. Note that the format of the time is not validated beyond ensuring that it conforms to some legal &postgresql; time syntax. Time_Type &ignored_column; Code indicating time system used in time written on data entry sheet. Bad_Observation ., . This column is used in conjunction with other columns to indicate observation certainty. See the description of how certainty is determined above. Rain &ignored_column; Indication of whether or not it was raining. Ma_Behavior . Mother's behavior codes. This is translated into the codes used within Gombe-MI by reference to the table. Multiple behaviors are separated from each other by the &gmi_behavior_delim; character. Whitespace is ignored. Ma_Foodpart . Mother's food part codes of food eaten. This is translated into the codes used within Gombe-MI by reference to the table. Multiple food part codes are separated from each other by the &gmi_behavior_delim; character. Whitespace is ignored. Ma_Foodkind . Mother's food kind code of food eaten. Ma_Fishpart . Mother's food part codes of foods fished for. This is translated into the codes used within Gombe-MI by reference to the table. Multiple food part codes are separated from each other by the &gmi_behavior_delim; character. Whitespace is ignored. Ma_Fishkind . Mother's food kind code of food fished for. Ma_GP_Combined (Mother's Grooming and Play Partners are Combined) &control_column; When this value is &true; or &null; the individuals the mother groomed or played with are recorded on the uploaded line in a single column, the column. When the value of this column is &false; the line records the mothers grooming and play partners separately, using the column for individuals groomed by the mother and the column for the individuals the mother plays with. Note that this column only affects observations of the mother doing the grooming or play. Observations of others grooming or playing with the mother are not influenced. Ma_Groomingplay . Chimpids &chimpid-like; identifying the individuals whom the mother either groomed or played with. &comma_delim_elim_whitespace; &special_chimpids; &play_role_determination; Ma_Grooming . Chimpids &chimpid-like; identifying the individuals whom the mother groomed. &comma_delim_elim_whitespace; Ma_Play . Chimpids &chimpid-like; identifying the individuals whom the mother played with. &comma_delim_elim_whitespace; &special_chimpids; &play_role_determination; Ma_Groomed_By . Chimpids &chimpid-like; identifying the individuals whom the mother was groomed by. &comma_delim_elim_whitespace; &special_chimpids; Ma_Voc (Mother's Vocalization Codes . and others Mother's vocalization codes, separated by the comma (&gmi_chimpid_delim;) character. This is translated into the codes used within Gombe-MI by reference to the table. Whitespace is ignored. Inf_Behavior . Infant's behavior codes. This is translated into the codes used within Gombe-MI by reference to the table. Multiple behaviors are separated from each other by the &gmi_behavior_delim; character. Whitespace is ignored. Inf_Foodpart . Infant's food part codes of food eaten. This is translated into the codes used within Gombe-MI by reference to the table. Multiple food part codes are separated from each other by the &gmi_behavior_delim; character. Whitespace is ignored. Inf_Foodkind . Infant's food kind code of food eaten. Inf_Fishpart . Infant's food part codes of foods fished for. This is translated into the codes used within Gombe-MI by reference to the table. Multiple food part codes are separated from each other by the &gmi_behavior_delim; character. Whitespace is ignored. Inf_Fishkind . Infant's food kind code of food fished for. Inf_Distance_To_Ma (Code for Infant's Distance To Mother) . and others A . value indicating the distance between mother and infant. ¬null; The related . values are always set to &gmi_mom_role; and &gmi_infant_role;. Irrespective of the content of the table. Inf_Nurse (Infant Nurses on Mother) . and others Whether the infant is nursing on the mother. &semi_boolean; &bt_driven; Inf_Travel . and others Whether the infant is traveling. &semi_boolean; &bt_driven; Inf_Eat . and others Whether the infant is eating. &semi_boolean; &bt_driven; Inf_Solitary_Play . and others Infant's solitary play code. This is translated into the codes used within Gombe-MI by reference to the table. Whitespace is ignored. Inf_Rest . and others Whether the infant is resting. &semi_boolean; &bt_driven; Inf_Distance_1 . and others An indication of distance between mother and infant. &dt_driven; &semi_boolean; The related . values are always set to &gmi_mom_role; and &gmi_infant_role;. Irrespective of the content of the table. Inf_Distance_2 . and others An indication of distance between mother and infant. &dt_driven; &semi_boolean; The related . values are always set to &gmi_mom_role; and &gmi_infant_role;. Irrespective of the content of the table. Inf_Distance_3 . and others An indication of distance between mother and infant. &dt_driven; &semi_boolean; The related . values are always set to &gmi_mom_role; and &gmi_infant_role;. Irrespective of the content of the table. Inf_Distance_4 . and others An indication of distance between mother and infant. &dt_driven; &semi_boolean; The related . values are always set to &gmi_mom_role; and &gmi_infant_role;. Irrespective of the content of the table. Inf_Distance_5 . and others An indication of distance between mother and infant. &dt_driven; &semi_boolean; The related . values are always set to &gmi_mom_role; and &gmi_infant_role;. Irrespective of the content of the table. Inf_Distance_Over_5m . and others An indication of distance between mother and infant. &dt_driven; &semi_boolean; The related . values are always set to &gmi_mom_role; and &gmi_infant_role;. Irrespective of the content of the table. Inf_Ride_On_Belly . and others Chimpid &chimpid-like; identifying the individual on whom the infant is riding dorsally. Whitespace is ignored. &special_chimpids; &bt_driven; Inf_Ride_On_Back . and others Chimpid &chimpid-like; identifying the individual on whom the infant is riding ventrally. Whitespace is ignored. &special_chimpids; &bt_driven; Inf_Ride_On_Unk . and others Chimpid &chimpid-like; identifying the individual on whom the infant is riding when the riding posture is unknown. Whitespace is ignored. &special_chimpids; &bt_driven; Inf_Dangle . and others Chimpid &chimpid-like; identifying the individual from whom the infant is dangling. Whitespace is ignored. &special_chimpids; &bt_driven; Inf_Rejection . and others Chimpid &chimpid-like; identifying the individual rejecting the infant. Whitespace is ignored. &special_chimpids; &bt_driven; Inf_Touching_Another . and others Chimpids &chimpid-like; identifying the individuals involved (in a non-directed fashion) in touch with the infant. Whitespace is ignored. &special_chimpids; &bt_driven; Inf_Grooming . and others Chimpids &chimpid-like; identifying the individuals whom the infant is grooming. Whitespace is ignored. &special_chimpids; &bt_driven; Inf_Being_Groomed . and others Chimpids &chimpid-like; identifying the individuals who are grooming the infant. Whitespace is ignored. &special_chimpids; &bt_driven; Inf_Social_Play . and others Chimpids &chimpid-like; identifying the individuals participating in play with the infant. Whitespace is ignored. &special_chimpids; &bt_driven; Inf_Display . and others Chimpids &chimpid-like; indicator of whether the infant is displaying and to which individuals. Whitespace is ignored. &special_chimpids; &bt_driven; Inf_Cry . and others Whether the infant is crying. &semi_boolean; &bt_driven; Inf_Voc (Infant's Vocalization Codes . and others Infant's vocalization codes, separated by the comma (&gmi_chimpid_delim;) character. This is translated into the codes used within Gombe-MI by reference to the table. Whitespace is ignored. Inf_Beg . and others Chimpids &chimpid-like; identifying the individuals from which the infant is begging. Whitespace is ignored. &special_chimpids; &bt_driven; Inf_ASSR . and others Chimpids &chimpid-like; identifying the individuals against whom the infant is ASSR-ing. Whitespace is ignored. &special_chimpids; &bt_driven; Inf_Sex_Related . and others Chimpids &chimpid-like; identifying the individuals with whom the infant is engaging in sex related behavior. The behavior is directed and the infant is the actor. Whitespace is ignored. &special_chimpids; &bt_driven; Inf_Tooluse . and others Whether the infant is using a tool. &semi_boolean; &bt_driven; Inf_Directaggression . and others Chimpids &chimpid-like; identifying the individuals against whom the infant is engaging in direct aggression. The behavior is directed and the infant is the actor. Whitespace is ignored. &special_chimpids; &bt_driven; Sib_Bad_Observation ., . This column is used in conjunction with other columns to indicate observation certainty of sibling related data. The legal values for this column are: &gmi_badobs_y;, meaning the sibling data is uncertain; &gmi_ignore_mark; or &null; meaning the Sib_Bad_Observation column does not influence whether sibling related data is uncertain. See the description of how certainty is determined above. Sib_Behavior . Sibling's behavior codes. This is translated into the codes used within Gombe-MI by reference to the table. Multiple behaviors are separated from each other by the &gmi_behavior_delim; character. Whitespace is ignored. Sib_Foodpart . Sibling's food part codes of food eaten. This is translated into the codes used within Gombe-MI by reference to the table. Multiple food part codes are separated from each other by the &gmi_behavior_delim; character. Whitespace is ignored. Sib_Foodkind . Sibling's food kind code of food eaten. Sib_Fishpart . Sibling's food part codes of foods fished for. This is translated into the codes used within Gombe-MI by reference to the table. Multiple food part codes are separated from each other by the &gmi_behavior_delim; character. Whitespace is ignored. Sib_Fishkind . Sibling's food kind code of food fished for. Sib_Play . Chimpids &chimpid-like; identifying the individuals whom the sibling played with. &comma_delim_elim_whitespace; &special_chimpids; &play_role_determination; Sib_Grooming . Chimpids &chimpid-like; identifying the individuals whom the sibling groomed. &comma_delim_elim_whitespace; Sib_Groomed_By . Chimpids &chimpid-like; identifying the individuals whom the sibling was groomed by. &comma_delim_elim_whitespace; &special_chimpids; Sib_Ride . Chimpid &chimpid-like; identifying the individual on whom the sibling rode. &comma_delim_elim_whitespace; &special_chimpids; Sib_Voc (Sibling's Vocalization Codes . and others Sibling's vocalization codes, separated by the comma (&gmi_chimpid_delim;) character. This is translated into the codes used within Gombe-MI by reference to the table. Whitespace is ignored. Sib_Distance_To_Ma (Sibling Distance To Mother Code) . and others A code indicating the distance between mother and sibling. &dt_driven; The uploaded distance code &gmi_ignore_mark; is ignored. The related . values are always set to &gmi_mom_role; and &gmi_sib_role;. Irrespective of the content of the table. Sib_Distance_To_Infant (Sibling Distance To Infant Code) . and others A code indicating the distance between infant and sibling. &dt_driven; The uploaded distance code &gmi_ignore_mark; is ignored. The related . values are always set to &gmi_infant_role; and &gmi_sib_role;. Irrespective of the content of the table. Comments . Comments on the behaviors occurring during the minute. Leading and trailing whitespace is removed. This value may be &null; when there are no comments. The Comments column is processed as described and sometimes behaviors are created as a result.

UPLOAD_BEHAVIORS Usage Guidelines Only INSERT is allowed on UPLOAD_BEHAVIORS. SELECT, UPDATE, and DELETE are not allowed. Inserting a row into UPLOAD_BEHAVIORS inserts rows into the follow related tables as described above.

&event_section; Views The most general views are the and the views. Taken together these 2 views return (1 or more) rows for all behavioral events. They are suitable for use in arbitrary queries and are probably the best choice in most cases. For the most part, the view should be used in place of the view since it does not contain rows for social interactions with baboons. The , , and views, have columns for each animal involved to encode the following properties: the animal's chimpid, the animal's role in the follow (mom, infant, sib, nonfocal), and the animal's role (part) in the behavioral event (self, actor, actee, participant). When using the , , and views these 3 columns must often be tested in tandem to obtain the desired results. The other views in this section also often have these columns, or a subset of them, but are designed to reduce the need to test all 3 of these values together. The and views can be simpler to use than the and views when interested in directed behaviors. The and views contain at most a single row per behavioral event and classify the individuals involved into separate columns for actor and actee. The and views classify the involved individuals based on whether or not they are focals in the follow; there is a set of focal columns to describe (one of) the focals involved in the interaction and a set of other columns to describe the 2nd individual involved in the interaction. This may be less useful than it first appears. Because many interactions are between focals a focal often shows up in the other columns and those interactions that are between focals have two rows in these views. There are 2 rows per event so that each focal can show up once in the focal category. The view is different in that it contains at least one row for every recorded behavioral interaction. The view contains exactly one row for each recorded behavioral interaction, with columns structured in a fashion somewhat like the way behavioral data is recorded. This is helpful when checking data in the system against the data as it comes from the field. The family_events view is also useful for changing database content, an operation usually based on data recorded in the field. But the correspondence between the columns of the view and the paper records is likely to be less useful than it seems when it comes to analysis because queries must be written with foreknowledge of who plays what function (mom, infant, sib) in the follow rather than relying on the system to inform. The view cannot distinguish between twins and singleton offspring whereas the other views can. Assuming that the table contains rows to distinguish twins. Further, the view is by far the least efficient in its execution. Data analysis will probably be better served by spending the time to learn to use the other views. All these views have slightly different implementations and some may perform better than others depending on the circumstances of use. While it is certainly possible to create more specialized behavioral views than those described here to encompass all the relevant combinatorial parings of mom, infant, sib, and nonfocal, as well as the orthogonal categories of directed and non-directed social interactions and asocial interactions it is hoped that enough skill writing queries in SQL will be developed that the existing views will suffice. One useful SQL technique can be found in the section above.

ACTOR_ACTEES (Dyadic Directed Behavioral Events) ACTOR_ACTEES contains one row per row where there are 2 chimpanzees involved in an actor/actee behavioral even. Events involving baboons are excluded. ACTOR_ACTEES provides a straightforward way to analyze social behaviors from the perspective of who is initiating and who receiving the behavior.

ACTOR_ACTEES Definition

ACTOR_ACTEES Columns Column From Description EID . Identifier of the behavioral event row. IntID . Identifier of the observation interval during which the event was recorded. ActorPID . Identifier of the behavioral participant row which records behavioral data regarding the chimpanzee who is the actor in the behavior. ActeePID . Identifier of the behavioral participant row which records behavioral data regarding the chimpanzee who is the actee in the behavior. Behavior . Code indicating the behavior observed. Position . Position within the canopy where the event occurred. Uncertain . Whether or not the behavior was observed with certainty. ActorRole (Role in Follow of Actor) . Code for the role, i.e. mom, sib, infant, played by the animal who is the actor in the behavior. Actor . The identifier or alternate identifier of the actor. ActeeRole (Role in Follow of Actee) . Code for the role, i.e. mom, sib, infant, played by the chimpanzee who is the actee in the behavior. Actee . The identifier or alternate identifier of the actee. Comment . Textual remarks regarding the behavioral event.

ACTOR_ACTEES Usage Guidelines Only SELECT is allowed on ACTOR_ACTEES. INSERT, UPDATE, and DELETE are not allowed.

ACTOR_ACTEES_W_BABOONS (Dyadic Directed Behavioral Events Including Baboons) ACTOR_ACTEES_W_BABOONS (Dyadic Directed Behavioral Events w. Baboons) ACTOR_ACTEES_W_BABOONS contains one row per row where there are 2 animals involved in an actor/actee behavioral event, including events involving baboons. ACTOR_ACTEES_W_BABOONS provides a straightforward way to analyze social behaviors from the perspective of who is initiating and who receiving the behavior.

ACTOR_ACTEES_W_BABOONS Definition

ACTOR_ACTEES_W_BABOONS Columns Column From Description EID . Identifier of the behavioral event row. IntID . Identifier of the observation interval during which the event was recorded. ActorPID . Identifier of the behavioral participant row which records behavioral data regarding the animal who is the actor in the behavior. ActeePID . Identifier of the behavioral participant row which records behavioral data regarding the animal who is the actee in the behavior. Behavior . Code indicating the behavior observed. Position . Position within the canopy where the event occurred. Uncertain . Whether or not the behavior was observed with certainty. ActorRole (Role in Follow of Actor) . or NF in the case of baboons or nonfocal chimpanzees Code for the role, i.e. mom, sib, infant, played by the animal who is the actor in the behavior. BaboonActor Computed A Boolean value, true when the actor is a baboon. Actor . The identifier or alternate identifier of the actor, or &null; if the actor is a baboon. ActeeRole (Role in Follow of Actee) . or NF in the case of baboons or nonfocal chimpanzees Code for the role, i.e. mom, sib, infant, played by the animal who is the actee in the behavior. BaboonActee Computed A Boolean value, true when the actee is a baboon. Actee . The identifier or alternate identifier of the actee, or &null; if the actee is a baboon. Comment . Textual remarks regarding the behavioral event.

ACTOR_ACTEES_W_BABOONS Usage Guidelines Only SELECT is allowed on ACTOR_ACTEES_W_BABOONS. INSERT, UPDATE, and DELETE are not allowed.

ASOCIAL_EVENTS (Non-Social Behavioral Events) ASOCIAL_EVENTS contains one row per row per when there is a related row having a value of &gmi_self;. ASOCIAL_EVENTS provides a direct way to analyze non-social behaviors, those behaviors involving a single chimp and no other animal.

ASOCIAL_EVENTS Definition

ASOCIAL_EVENTS Columns Column From Description EID . Identifier of the behavioral event row. IntID . Identifier of the observation interval during which the event was recorded. PID . Identifier of the behavioral participant row which records behavioral data regarding the individual exhibiting the behavior. Behavior . Code indicating the behavior observed. Position . Position within the canopy where the event occurred. Uncertain . Whether or not the behavior was observed with certainty. Role (Role in Follow of Chimp) . Code for the role, i.e. mom, sib, infant, played by the individual involved in the behavioral event. Participant . The identifier or alternate identifier of the individual exhibiting the behavior. Comment . Textual remarks regarding the behavioral event.

ASOCIAL_EVENTS Usage Guidelines Only SELECT is allowed on ASOCIAL_EVENTS. INSERT, UPDATE, and DELETE are not allowed.

EVENT_BOUTS (Analysis of the focals' Behavioral Bouts, per Minute) An event bout is defined as a contiguous series of minutes during which a focal is involved in a behavior. However, behavioral events involving baboons are excluded when considering contiguity. Behaviors are distinguished by the codes used to represent them. An individual can be involved in more than one event bout at a time, if involved in more than one behavior at a time. EVENT_BOUTS contains one row per row per row, per . value having a related row that shows the given focal involved in the given behavior -- one row per follow, per focal, per interval in which the focal is involved in a behavior (excluding behavioral events which involve a baboon), per code for the behavior involved in. The number of rows returned is not affected by the number of times the focal is involved in the behavior during any given interval, nor is it affected by the behavioral role the focal plays regards the behavior. There is a row per focal per behavior the focal is involved in per interval, excluding cases where a baboon is involved. Event bouts are numbered sequentially, in a time-wise order, per follow, per focal, per behavior, starting with 1. Bouts are said to be censored, either on the left (earlier) or on the right (later) if the edge of the bout exists because there is a lack of behavioral data on the given focal. Censoring can occur because of badobs, because observation has not started or because observation finishes, or any other time there is no behavioral data what so ever on the focal. Note that censoring or lack thereof of any one focal is not affected by data or lack thereof on any other focal. Note also that observations involving baboons are not excluded when it comes to considering whether a bout is censored. Baboon related observations are like any other behavioral events involving the focal when it comes to considering whether behavioral data exists. Likewise, the columns that indicate censoring due to the start of observation at the beginning of the day and end of observation at the end of the day are per-focal and not affected by observations that may or may not be made on the other focals. Note that uncertain behavioral events are not distinguished from certain behavioral events in event bout analysis, although the EVENT_BOUTS view does provide some information regarding uncertainty. Since the EVENT_BOUTS view can take a number of minutes to return results it may be desirable to put the results of interest into a table for repeated analysis. If this is done it is very likely a good idea to add indexes to most of the new table's columns. This will greatly improve performance. The sum of and is the total number of minutes in the event bout.

EVENT_BOUTS Definition

EVENT_BOUTS Columns Column From Description FollowID . Identifier of the follow. IntID . Identifier of the observation interval comprising one of the bout's minutes. Function (Function in Follow of Focal) . Code indicating the biological role the focal plays in the follow: Mother, Infant, or Sibling. AnimID . Canonical identifier of the focal, the . value. This can differ from the in the case of twins or other circumstances where individuals have multiple identifiers. Role (Role in Follow of Focal) . Code indicating the family role the focal plays in the follow. This can differ from the in the case of twins. ChimpID . Identifier of the focal as recorded in the row for the follow. This can differ from the focal's in the case of twins or other circumstances where individuals have multiple identifiers. In such cases this may also differ from the stored in the . rows which designate the focal as being involved in a behavioral event. Bout Computed Integer indicating the bout number. Bout numbers start with 1 and are per follow, per focal, per behavior code. Behavior . Code indicating the behavior in which the focal is involved. Uncertain . &true; if all of the (non-baboon related) behavioral events in which the focal is involved during the given minute for the given behavior are uncertain, &false; otherwise. Time . The time of the observation. Mins_In (Minutes Into the bout) Computed Number of minutes into the current bout, including the current minute. Therefore, the first minute of the bout has a value of 1, the next minute in the bout has a value of 2, etc. Mins_Out (Minutes remaining in the bout) Computed Number of minutes remaining in the current bout, excluding the current minute. Therefore, the last minute of the bout has a value of 0, the prior minute in the bout has a value of 1, etc. Prior_Gap (Minutes between the current and Prior bout) Computed The number of minutes between the current bout and the previous bout (of the same follow, focal, behavior). &null; if there is no prior bout. Gap_NoObs (Minutes without Observation of the focal between the current and prior bout) Computed The number of minutes between the current bout and the previous bout (of the same follow, focal, behavior) during which there is no behavioral data what so ever on the focal. &null; if there is no prior bout. Left_Censored Computed. &true; when the bout is left censored, &false; otherwise. See above for more information regards censoring. Right_Censored Computed. &true; when the bout is right censored, &false; otherwise. See above for more information regards censoring. Day_Start (Bout is censored due to Start of Day) Computed &true; when the bout is censored because it occurs at the beginning of the day's observation of the follow, &false; otherwise. Day_End (Bout is censored due to End of Day) Computed &true; when the bout is censored because it occurs at the end of the day's observation of the follow, &false; otherwise.

EVENT_BOUTS Usage Guidelines Only SELECT is allowed on EVENT_BOUTS. INSERT, UPDATE, and DELETE are not allowed.

EVENT_BOUT_AGGS (Analysis of the focals' Behavioral Bouts, per Bout) EVENT_BOUT_AGGS contains one row per row per . value having a related row that shows the given focal involved in the given behavior -- one row per follow, per focal in which the focal is involved in a behavior (excluding behavioral events which involve a baboon), per code for the behavior involved in. Each row represents an entire event bout. See for more information on event bout analysis.

EVENT_BOUT_AGGS Definition

EVENT_BOUT_AGGS Columns Column From Description FollowID . Identifier of the follow. Function (Function in Follow of Focal) . Code indicating the biological role the focal plays in the follow: Mother, Infant, or Sibling. AnimID . Canonical identifier of the focal, the . value. This can differ from the in the case of twins or other circumstances where individuals have multiple identifiers. Role (Role in Follow of Focal) . Code indicating the family role the focal plays in the follow. This can differ from the in the case of twins. ChimpID . Identifier of the focal as recorded in the row for the follow. This can differ from the focal's in the case of twins or other circumstances where individuals have multiple identifiers. In such cases this may also differ from the stored in the . rows which designate the focal as being involved in a behavioral event. Bout Computed Integer indicating the bout number. Bout numbers start with 1 and are per follow, per focal, per behavior code. Behavior . Code indicating the behavior in which the focal is involved. Uncertain . &true; if any of the underlying rows which comprise the bout are uncertain, &false; otherwise. Start_Time . The time the event bout started. The time of the first observation of the bout. Stop_Time . The time the event bout stopped. The time of the last observation of the bout. Prior_Gap (Minutes between the current and Prior bout) Computed The number of minutes between the current bout and the previous bout (of the same follow, focal, behavior). &null; if there is no prior bout. Gap_NoObs (Minutes without Observation of the focal between the current and prior bout) Computed The number of minutes between the current bout and the previous bout (of the same follow, focal, behavior) during which there is no behavioral data what so ever on the focal. &null; if there is no prior bout. Left_Censored Computed. &true; when the bout is left censored, &false; otherwise. See above for more information regards censoring. Right_Censored Computed. &true; when the bout is right censored, &false; otherwise. See above for more information regards censoring. Day_Start (Bout is censored due to Start of Day) Computed &true; when the bout is censored because it occurs at the beginning of the day's observation of the follow, &false; otherwise. Day_End (Bout is censored due to End of Day) Computed &true; when the bout is censored because it occurs at the end of the day's observation of the follow, &false; otherwise.

EVENT_BOUT_AGGS Usage Guidelines Only SELECT is allowed on EVENT_BOUT_AGGS. INSERT, UPDATE, and DELETE are not allowed.

FAMILY_EVENTS (Behavioral Events With the Focals Identified by Column) FAMILY_EVENTS contains one row per row. FAMILY_EVENTS provides a way to review recorded behaviors in a way similar to the way data is recorded in the field. It is also useful for changing database content, since this is often done based on data recorded in the field. The FAMILY_EVENTS view is neither particularly efficient nor particularly well suited to data analysis. Other views will very likely be much faster and more appropriate when analyzing data. The FAMILY_EVENTS view is limited in that it will not change database content in the tables which extend the table, tables such as , , and the like. For this the view must be used.

FAMILY_EVENTS Definition

FAMILY_EVENTS Columns Column From Description FollowID . Identifier of the follow. IntID . Identifier of the observation interval during which the event was recorded. EID . Identifier of the behavioral event row. MomPID (Event Mom PID) . Identifier of the behavioral participant row which records behavioral data regarding the mother in the follow, or &null; when the mother is not a participant in the behavior. E_InfantPID (Event Infant PID) . Identifier of the behavioral participant row which records behavioral data regarding the infant in the follow, or &null; when the infant is not a participant in the behavior. E_SibPID (Event Sib PID) . Identifier of the behavioral participant row which records behavioral data regarding the sibling in the follow, or &null; when the sibling is not a participant in the behavior. E_NonfocalPID (Event Non-Focal PID) . Identifier of the behavioral participant row which records behavioral data regarding any nonfocal individual involved in the behavior, or &null; when only focals are participants in the behavior. Date . The date of the follow. Time . The time of the interval. Mom . The of the mother in the follow. Infant . The of an infant in the follow. Infant2 . The of a second infant in the follow, or &null; if the infant is not a twin. Sib . The of a sibling in the follow, or &null; if there is no sibling focal. Sib2 . The of a second sibling in the follow, or &null; if the sibling is not a twin. Behavior . Code indicating the behavior observed. Position . Position within the canopy where the event occurred. Uncertain . Whether or not the behavior was observed with certainty. E_Mom (Event Mom) . The identifier or alternate identifier of the mom, or &null; if the mother is not a participant in the behavior. E_MomPart (Event Mom Participant code) . The role the mom plays in the behavior, or &null; if the mother is not a participant in the behavior. E_Infant (Event Infant) . The identifier or alternate identifier of the infant, or &null; when the infant is not a participant in the behavior. E_InfantPart (Event Infant Participant code) . The role the infant plays in the behavior, or &null; when the infant is not a participant in the behavior. E_Sib (Events Sibling) . The identifier or alternate identifier of the sibling, or &null; when the sibling is not a participant in the behavior. E_SibPart (Event Sibling Participant code) . The role the sibling plays in the behavior, or &null; when the sibling is not a participant in the behavior. E_Nonfocal . The identifier or alternate identifier of any nonfocal individual involved in the behavior, or &null; when only focals participate in the behavior. E_NonfocalPart (Event Non-Focal Participant code) . The role a nonfocal individual plays in the behavior, or &null; when only focals participate in the behavior. E_Baboon (Event Baboon) . Whether or not a baboon is the second participant in the behavioral interaction. E_BaboonPart (Event Baboon Participant code) A simulated . value The role the baboon plays in the behavior, or &null; when a baboon is not a participant in the behavior. This value is computed from the . value of the chimpanzee participant. E_Comment (Event Comment) . Textual remarks regarding the behavioral event. I_Comment (Interval Comment) . Textual remarks regarding the interval.

FAMILY_EVENTS Usage Guidelines Altering the FAMILY_EVENTS view alters the content of the underlying and tables in the fashion described below. The and related and rows must exist; the content of these tables is never altered.

INSERT Inserting a row into the FAMILY_EVENTS view inserts a row into the table, as expected. When inserting into FAMILY_EVENTS the interval of the follow must be specified. The interval may be specified in either or both of the following 2 ways: A may be supplied. A may be supplied and the follow specified. The follow may be specified in either or both of the following 2 ways: A may be supplied. , and, values all may be supplied. See the note regards twins when inserting into FOLLOW_INTERVALS. FAMILY_EVENTS inserts rows into the database only when there is a row that matches the above selection criteria. If no row matches the database content is not altered. Note that no row will match if conflicting information is supplied. When inserting into FAMILY_EVENTS it is not recommended to set any of the , , , , or values. Omitting these columns or supplying &null; values will allow the system to automatically generate ids. When inserting into FAMILY_EVENTS the user-supplied value for is ignored.

Update Updating the FAMILY_EVENTS view updates , as expected. Updating FAMILY_EVENTS can update, insert into, or delete rows from . The examples below explicitly specify an . value. This is solely for the purpose of brevity. Any valid WHERE clause could be used instead to designate the desired row in . If the old values for a mom, infant, sib or nonfocal row are not &null; and the new values are all &null; then the old row is deleted. The following example removes the mother as a participant in the event with an . of 1234: Deleting The Mother's PARTS Row

UPDATE family_events
       SET e_mom = NULL
         , e_mompid = NULL
         , e_mompart = NULL
       WHERE eid = 1234;

If the old values for a mom, infant, sib, or nonfocal are &null;, i.e. if the mom, infant, sib, or nonfocal has no row then a new row can be inserted by supplying values. The following example adds, to the event with an . of 1234, assuming she is not already one of the individuals involved in the event, the mother as an actor (. = '&gmi_actor;'): Inserting A PARTS Row For The Mother

UPDATE family_events
       SET e_mom = 'GA'
         , e_mompart = '&gmi_actor;'
       WHERE eid = 1234;

If old values exist, they are replaced by new values. Assuming the non-focal JOE is involved in the event with an . of 1234, the following example changes the non-focal to JAK: Changing Non-Focal Participant In A PARTS Row

UPDATE family_events
       SET e_nonfocal = 'JAK'
       WHERE eid = 1234;

The values need not correspond to the roles the individuals play in the follow because data is associated with chimp id values, not focal roles. The presence of focal roles in the column names is, in some sense, artificial. For example, there are two ways to change the infant (INF) to a sibling (SB) in the event with an . of 1234. The first is to delete the row for the infant and insert a new row for the sibling: Deleting the Infant's PARTS row and Inserting a New PARTS Row for the Sibling

UPDATE family_events
       SET e_infant = NULL
         , e_infantpid = NULL
         , e_infantpart = NULL
         , e_sib = 'SB'
         , e_sibpart = '&gmi_actor;'
       WHERE eid = 1234;

Deleting and re-adding rows is somewhat inefficient and error prone. It is probably better to simply replace the infant's value with the siblings. Simply changing the . value is enough; being the sibling is a property of a ChimpID with respect to a follow and, again, when updating the database there is no correspondence between the column names and the roles played by the individual chimps. The following is sufficient to re-use the exiting row and change the infant's value to the siblings: Changing an Infant's PARTS row Into A PARTS Row for the Sibling

UPDATE family_events
       SET e_infant = 'SB'  -- SB is the sibling
       WHERE eid = 1234;

When the result is queried SB will show up as a sibling, not an infant. It is an error to attempt to update the values of any columns not derived from the or tables. It is also an error to attempt to change the value. When updated, FAMILY_EVENTS raises a notice containing the number of rows altered. A row is counted only once, when updating the row changes the database content in any way.

Delete Deleting rows from the FAMILY_EVENTS view deletes rows from and , as expected. Because deleting a row from also deletes related rows from the tables which extend the table, tables such as , , and the like, this is a convenient way to delete behavioral events.

FOCAL_EVENTS (All Behavioral Events With the Focals Distinguished) FOCAL_EVENTS contains one row per row per focal individual involved in the behavioral event. FOCAL_EVENTS provides a direct way to analyze behaviors when interested in the role (mom, infant, sib, non-focal) of the individuals involved in the behavior. FOCAL_EVENTS has 1 or 2 rows per behavioral event -- per row -- depending on whether the the behavioral interaction is social and how many of the individuals involved are focals in the follow. FOCAL_EVENTS includes rows for behavioral interactions with baboons.

FOCAL_EVENTS Definition

FOCAL_EVENTS Columns Column From Description EID . Identifier of the behavioral event row. IntID . Identifier of the observation interval during which the event was recorded. FocalPID . Identifier of the behavioral participant row which records behavioral data regarding one of the focals in the follow. OtherPID . Identifier of the behavioral participant row which records behavioral data regarding the other individual (the individual involved in the behavior who is not designated as the focal in the row). This value is &null; when a single chimpanzee is involved in the behavior. Behavior . Code indicating the behavior observed. Position . Position within the canopy where the event occurred. Uncertain . Whether or not the behavior was observed with certainty. FocalRole (Role in Follow of Focal Chimp) . Code for the role, i.e. mom, sib, infant, played by the individual designated as focal in the behavioral event. Focal . The identifier or alternate identifier of the focal. FocalPart . The role the focal plays in the behavior. OtherRole (Role in Follow of Other Chimp) . or computed in the case of interaction with a baboon Code for the role played by the other individual in the behavioral event, or &gmi_nonfocal; if the other individual is not a focal in the follow. This value is &null; when the behavior is non-social and &gmi_nonfocal; when the interaction is with a baboon. Other . The identifier or alternate identifier of the other individual (the the individual not designated as the focal in the row). This value is &null; when a single chimpanzee is involved in the behavior. OtherPart . or computed in the case of interaction with a baboon The role the other individual (the individual involved in the behavior who is not designated as the focal in the row) plays in the behavior. This value is &null; when the behavior is non-social. The value is appropriate when the interaction is with a baboon. Baboon . Whether or not a baboon is the second participant in the behavioral interaction. Comment . Textual remarks regarding the behavioral event.

FOCAL_EVENTS Usage Guidelines Only SELECT is allowed on FOCAL_EVENTS. INSERT, UPDATE, and DELETE are not allowed.

FOCAL_SOCIAL_EVENTS (Dyadic Behavioral Events Involving a Focal and Some Other Chimp) FOCAL_SOCIAL_EVENTS (Dyadic Behavioral Events Involving a Focal) FOCAL_SOCIAL_EVENTS contains one row per row per individual involved in the behavioral event when the individual is a focal in the follow where a focal in the follow is one of 2 chimpanzees involved in the exhibited behavior. FOCAL_SOCIAL_EVENTS provides a direct way to analyze social behaviors when interested in the role (mom, infant, sib, non-focal) of the chimpanzees involved in the behavior. FOCAL_SOCIAL_EVENTS has 0, 1 or 2 rows per behavioral event -- per row -- depending on whether there are 2 chimpanzees involved in the event and how many of the chimpanzees are focals in the follow.

FOCAL_SOCIAL_EVENTS Definition

FOCAL_SOCIAL_EVENTS Columns Column From Description EID . Identifier of the behavioral event row. IntID . Identifier of the observation interval during which the event was recorded. FocalPID . Identifier of the behavioral participant row which records behavioral data regarding one of the focals in the follow. OtherPID . Identifier of the behavioral participant row which records behavioral data regarding the other individual (the individual involved in the behavior who is not designated as the focal in the row). Behavior . Code indicating the behavior observed. Position . Position within the canopy where the event occurred. Uncertain . Whether or not the behavior was observed with certainty. FocalRole (Role in Follow of Focal Chimp) . Code for the role, i.e. mom, sib, infant, played by the individual designated as focal in the behavioral event. Focal . The identifier or alternate identifier of the focal. FocalPart . The role the focal plays in the behavior. OtherRole (Role in Follow of Other Chimp) . Code for the role played by the other individual in the behavioral event, or &gmi_nonfocal; if the other individual is not a focal in the follow. Other . The identifier or alternate identifier of the other individual (the the individual not designated as the focal in the row). OtherPart . The role the other individual (the individual involved in the behavior who is not designated as the focal in the row) plays in the behavior. Comment . Textual remarks regarding the behavioral event.

FOCAL_SOCIAL_EVENTS Usage Guidelines Only SELECT is allowed on FOCAL_SOCIAL_EVENTS. INSERT, UPDATE, and DELETE are not allowed.

NOOBS (Times During Which a Focal has no Behavioral Observations) NOOBS contains one row per row, per row, per minute from &gmi_day_start; through &gmi_day_end;, inclusive, but only when there is no behavioral data on the focal. NOOBS provides a direct way to tell when there is no behavioral data on a focal, regardless of reason and, so long as the individual is a focal in a follow on given day, irrespective of whether an observer is present. Note that the should be the same as the , unless twins are involved.

NOOBS Definition

NOOBS Columns Column From Description FollowID . Identifier of the follow. ChimpID . The of the focal. Useful when twins are involved. Role . The role the focal plays in the follow. AnimID . The canonical id, the . of the focal. Time Computed The time.

NOOBS Usage Guidelines Only SELECT is allowed on NOOBS. INSERT, UPDATE, and DELETE are not allowed.

SOCIAL_BOUTS (Analysis of the focals' Behavioral Bouts, per Social Partner, per Minute) SOCIAL_BOUTS (Analysis of the focals' Behavioral Bouts, per Partner, per Minute) A social bout is defined as a contiguous series of minutes during which a focal is involved in a behavior with a given social partner. However, behavioral events involving baboons are excluded when considering contiguity. Behaviors are distinguished by the codes used to represent them. An individual can be involved in more than one social bout at a time, if involved in more than one social behavior at a time. SOCIAL_BOUTS contains one row per row per row, per . value having a related row that shows the given focal involved in the given behavior with a social partner, per of the social partner -- one row per follow, per focal, per social partner, per interval in which the focal is involved in a social behavior (excluding behavioral events which involve a baboon), per code for the behavior involved in. The number of rows returned is not affected by the behavioral role the focal plays regards the social behavior or whether the focal is involved with the same behavior with the same social partner in a number of ways. There is a row per focal per social partner per social behavior the focal is involved in per interval, excluding cases where a baboon is involved. Social bouts are numbered sequentially, in a time-wise order, per follow, per focal, per social partner, per social behavior, starting with 1. Note that uncertain behavioral events are not distinguished from certain behavioral events in event bout analysis, although the SOCIAL_BOUTS view does provide some information regarding uncertainty. See for more information on bout analysis.

SOCIAL_BOUTS Definition

SOCIAL_BOUTS Columns Column From Description FollowID . Identifier of the follow. IntID . Identifier of the observation interval comprising one of the bout's minutes. Function (Function in Follow of Focal) . Code indicating the biological role the focal plays in the follow: Mother, Infant, or Sibling. AnimID . Canonical identifier of the focal, the . value. This can differ from the in the case of twins or other circumstances where individuals have multiple identifiers. Role (Role in Follow of Focal) . Code indicating the family role the focal plays in the follow. This can differ from the in the case of twins. ChimpID . Identifier of the focal as recorded in the row for the follow. This can differ from the focal's in the case of twins or other circumstances where individuals have multiple identifiers. In such cases this may also differ from the stored in the . rows which designate the focal as being involved in a behavioral event. Other_Function (Function in Follow of focal's social partner) . Code indicating the biological role the focal's social partner plays in the follow: Mother, Infant, Sibling, or &null; when the social partner is not also a focal. Other_AnimID (AnimID of the focal's social partner) . Canonical identifier of the focal's social partner, the . value. This can differ from the in the case of twins or other circumstances where individuals have multiple identifiers. Other_Role (Role in Follow of focal's social partner) . Code indicating the family role the focal's social partner plays in the follow. As with the value is &null; when the focal's social partner is not a focal. The value in this column can differ from the in the case of twins. Other_ChimpID (ChimpID of the focal's social partner when the partner is a focal) . Identifier of the focal's social partner as recorded in the row for the follow. This can differ from the focal's in the case of twins or other circumstances where individuals have multiple identifiers. In such cases this may also differ from the stored in the . rows which designate the social partner as being involved in a behavioral event. &null; when the focal's social partner is not themselves a focal. Bout Computed Integer indicating the bout number. Bout numbers start with 1 and are per follow, per focal, per social partner, per behavior code. Behavior . Code indicating the behavior in which the focal is involved. Uncertain . &true; if all of the (non-baboon related) behavioral events in which the focal is involved with the social partner during the given minute for the given behavior are uncertain, &false; otherwise. Time . The time of the observation. Mins_In (Minutes Into the bout) Computed Number of minutes into the current bout, including the current minute. Therefore, the first minute of the bout has a value of 1, the next minute in the bout has a value of 2, etc. Mins_Out (Minutes remaining in the bout) Computed Number of minutes remaining in the current bout, excluding the current minute. Therefore, the last minute of the bout has a value of 0, the prior minute in the bout has a value of 1, etc. Prior_Gap (Minutes between the current and Prior bout) Computed The number of minutes between the current bout and the previous bout (of the same follow, focal, social partner, and behavior). &null; if there is no prior bout. Gap_NoObs (Minutes without Observation of the focal between the current and prior bout) Computed The number of minutes between the current bout and the previous bout (of the same follow, focal, social partner, and behavior) during which there is no behavioral data what so ever on the focal. &null; if there is no prior bout. Left_Censored Computed. &true; when the bout is left censored, &false; otherwise. See above for more information regards censoring. Right_Censored Computed. &true; when the bout is right censored, &false; otherwise. See above for more information regards censoring. Day_Start (Bout is censored due to Start of Day) Computed &true; when the bout is censored because it occurs at the beginning of the day's observation of the follow, &false; otherwise. Day_End (Bout is censored due to End of Day) Computed &true; when the bout is censored because it occurs at the end of the day's observation of the follow, &false; otherwise.

SOCIAL_BOUTS Usage Guidelines Only SELECT is allowed on SOCIAL_BOUTS. INSERT, UPDATE, and DELETE are not allowed.

SOCIAL_BOUT_AGGS (Analysis of the focals' Social behavioral Bouts, per Social Partner per Bout) SOCIAL_BOUT_AGGS (Analysis of Social Bouts, per Social Partner per Bout) SOCIAL_BOUT_AGGS contains one row per row per . value having a related row that shows the given focal involved in the given behavior with a social partner, per of the social partner -- one row per follow, per focal, per social partner (excluding behavioral events which involve a baboon), per code for the behavior involved in. Each row represents an entire event bout. See for more information on event bout analysis.

SOCIAL_BOUT_AGGS Definition

SOCIAL_BOUT_AGGS Columns Column From Description FollowID . Identifier of the follow. Function (Function in Follow of Focal) . Code indicating the biological role the focal plays in the follow: Mother, Infant, or Sibling. AnimID . Canonical identifier of the focal, the . value. This can differ from the in the case of twins or other circumstances where individuals have multiple identifiers. Role (Role in Follow of Focal) . Code indicating the family role the focal plays in the follow. This can differ from the in the case of twins. ChimpID . Identifier of the focal as recorded in the row for the follow. This can differ from the focal's in the case of twins or other circumstances where individuals have multiple identifiers. In such cases this may also differ from the stored in the . rows which designate the focal as being involved in a behavioral event. Other_Function (Function in Follow of focal's social partner) . Code indicating the biological role the focal's social partner plays in the follow: Mother, Infant, Sibling, or &null; when the social partner is not also a focal. Other_AnimID (AnimID of the focal's social partner) . Canonical identifier of the focal's social partner, the . value. This can differ from the in the case of twins or other circumstances where individuals have multiple identifiers. Other_Role (Role in Follow of focal's social partner) . Code indicating the family role the focal's social partner plays in the follow. As with the value is &null; when the focal's social partner is not a focal. The value in this column can differ from the in the case of twins. Other_ChimpID (ChimpID of the focal's social partner when the partner is a focal) . Identifier of the focal's social partner as recorded in the row for the follow. This can differ from the focal's in the case of twins or other circumstances where individuals have multiple identifiers. In such cases this may also differ from the stored in the . rows which designate the social partner as being involved in a behavioral event. &null; when the focal's social partner is not themselves a focal. Bout Computed Integer indicating the bout number. Bout numbers start with 1 and are per follow, per focal, per behavior code. Behavior . Code indicating the behavior in which the focal is involved. Uncertain . &true; if any of the underlying rows which comprise the bout are uncertain, &false; otherwise. Start_Time . The time the event bout started. The time of the first observation of the bout. Stop_Time . The time the event bout stopped. The time of the last observation of the bout. Prior_Gap (Minutes between the current and Prior bout) Computed The number of minutes between the current bout and the previous bout (of the same follow, focal, behavior). &null; if there is no prior bout. Gap_NoObs (Minutes without Observation of the focal between the current and prior bout) Computed The number of minutes between the current bout and the previous bout (of the same follow, focal, behavior) during which there is no behavioral data what so ever on the focal. &null; if there is no prior bout. Left_Censored Computed. &true; when the bout is left censored, &false; otherwise. See above for more information regards censoring. Right_Censored Computed. &true; when the bout is right censored, &false; otherwise. See above for more information regards censoring. Day_Start (Bout is censored due to Start of Day) Computed &true; when the bout is censored because it occurs at the beginning of the day's observation of the follow, &false; otherwise. Day_End (Bout is censored due to End of Day) Computed &true; when the bout is censored because it occurs at the end of the day's observation of the follow, &false; otherwise.

SOCIAL_BOUT_AGGS Usage Guidelines Only SELECT is allowed on SOCIAL_BOUT_AGGS. INSERT, UPDATE, and DELETE are not allowed.

SOCIAL_EVENTS (Dyadic Behavioral Events Involving Chimpanzees) SOCIAL_EVENTS contains one row per row per individual involved in the behavioral event per chimpanzee when there are 2 chimpanzees (no baboons) involved in the exhibited behavior. SOCIAL_EVENTS provides a direct way to analyze chimp social behaviors regardless of whether a chimpanzee is a focal in the follow. SOCIAL_EVENTS has 0 or 2 rows per behavioral event -- per row -- depending on whether there are 2 chimpanzees involved in the event. The two SOCIAL_EVENTS rows for each row are reciprocal, the individuals arbitrarily designated first and second are reversed. So, either querying for the first individual as the mother and the 2nd as the infant or querying for the second individual as the mother and the 1st as the infant will eliminate double counting of events; each query returning one side of the duplicated pair. The mutual placement of each interaction participant in both the first and second columns can be convenient when querying; all individuals involved in a behavior can be tested against, joined with, etc., regardless of whether the first or second column content is examined. The and columns significantly slow the query. Performance is much improved if, when possible, these columns are not displayed or queried against.

SOCIAL_EVENTS Definition

SOCIAL_EVENTS Columns Column From Description EID . Identifier of the behavioral event row. IntID . Identifier of the observation interval during which the event was recorded. Part1PID . Identifier of the behavioral participant row which records behavioral data regarding the first of the individuals in the behavioral event. Part2PID . Identifier of the behavioral participant row which records behavioral data regarding the second of the individuals in the behavioral event. Behavior . Code indicating the behavior observed. Position . Position within the canopy where the event occurred. Uncertain . Whether or not the behavior was observed with certainty. Part1Role (Role in Follow of First Chimp) . Code for the role, i.e. mom, sib, infant, played by the individual designated as the first in the behavioral event. Participant1 . The identifier or alternate identifier of the first individual. Part1 . The role the first individual plays in the behavior. Part2Role (Role in Follow of Second Chimp) . Code for the role, i.e. mom, sib, infant played by the individual designated as the second in the behavioral event. Participant2 . The identifier or alternate identifier of the second individual. Part2 . The role the second individual plays in the behavior. Comment . Textual remarks regarding the behavioral event.

SOCIAL_EVENTS Usage Guidelines Only SELECT is allowed on SOCIAL_EVENTS. INSERT, UPDATE, and DELETE are not allowed.

SOCIAL_EVENTS_W_BABOONS (Dyadic Behavioral Events Including Baboons) SOCIAL_EVENTS_W_BABOONS contains one row per row per animal involved in the behavioral event per individual involved in the exhibited behavior, including baboons. SOCIAL_EVENTS_W_BABOONS provides a direct way to analyze social behaviors regardless of whether a chimpanzee is a focal in the follow. SOCIAL_EVENTS_W_BABOONS has 0 or 2 rows per behavioral event -- per row -- depending on whether there are 2 animals involved in the event. The two SOCIAL_EVENTS rows for each row are reciprocal, the individuals arbitrarily designated first and second are reversed. This can be convenient when querying; all individuals involved in a behavior can be tested against, joined with, etc., regardless of whether the first or second column content is examined.

SOCIAL_EVENTS_W_BABOONS Definition

SOCIAL_EVENTS_W_BABOONS Columns Column From Description EID . Identifier of the behavioral event row. IntID . Identifier of the observation interval during which the event was recorded. Part1PID . Identifier of the behavioral participant row which records behavioral data regarding the first of the animals in the behavioral event. Part2PID . Identifier of the behavioral participant row which records behavioral data regarding the second of the animals in the behavioral event. Behavior . Code indicating the behavior observed. Position . Position within the canopy where the event occurred. Uncertain . Whether or not the behavior was observed with certainty. Part1Role (Role in Follow of First Animal) . or NF in the case of baboons or nonfocal chimpanzees Code for the role, i.e. mom, sib, infant, played by the animal designated as the first in the behavioral event. Part1Baboon Computed A Boolean value, true when the first animal is a baboon. Participant1 . The identifier or alternate identifier of the first animal, or &null; if the individual is a baboon. Part1 . or computed based on the 2nd individual's role if the animal is a baboon The role the first animal plays in the behavior. Part2Role (Role in Follow of Second Animal) . or NF in the case of baboons or nonfocal chimpanzees Code for the role, i.e. mom, sib, infant, played by the animal designated as the second in the behavioral event. Part2Baboon Computed A Boolean value, true when the second animal is a baboon. Participant2 . The identifier or alternate identifier of the second animal, or &null; if the individual is a baboon. Part2 . or computed based on the 2nd individual's role if the animal is a baboon The role the second animal plays in the behavior. Comment . Textual remarks regarding the behavioral event.

SOCIAL_EVENTS_W_BABOONS Usage Guidelines Only SELECT is allowed on SOCIAL_EVENTS_W_BABOONS. INSERT, UPDATE, and DELETE are not allowed.

&distance_section; Views

DISTS (Distance Measurements Between Chimpanzees) DISTS contains one row per row per related row. DISTS provides a general way to analyze chimp inter-familial distances -- based on either or role played in the follow. DISTS has 2 rows per inter-familial distance measurement -- per row. The two DISTS rows for each row are reciprocal, the individuals arbitrarily designated first and second are reversed. So, either querying for the first individual as the mother and the 2nd as the infant or querying for the second individual as the mother and the 1st as the infant will eliminate double counting of events; each query returning one side of the duplicated pair. The mutual placement of each participant in the distance measurements in both the first and second columns can be convenient when querying; all individuals involved in a measurement can be tested against, joined with, etc., regardless of whether the first or second column content is examined.

DISTS Definition

DISTS Columns Column From Description DistID . Identifier of the distance measurement row. IntID . Identifier of the observation interval during which the distance measurement was recorded. Part1DPID . Identifier of the distance measurement participant row which records data regarding the first of the individuals between which distance was measured. Part2DPID . Identifier of the distance measurement participant row which records data regarding the second of the individuals between which distance was measured. Uncertain . Whether or not the inter-familial distance was observed with certainty. DPart1Role (Role in Follow of First Chimp) . Code for the role, i.e. mom, sib, infant, played by the individual designated as the first individual involved in the measurement. Participant1 . The identifier or alternate identifier of the first individual. DPart2Role (Role in Follow of Second Chimp) . Code for the role, i.e. mom, sib, infant, played by the individual designated as the second in the measurement. Participant2 . The identifier or alternate identifier of the second individual. Distance . Code designating the distance between the 2 individuals.

DISTS Usage Guidelines Only SELECT is allowed on DISTS. INSERT, UPDATE, and DELETE are not allowed.

FAMILY_DISTS (Inter-Chimp Distance Measurements With the Focals Identified by Column) FAMILY_DISTS (Inter-Chimp Distance Measurements, Focals Identified by Column) FAMILY_DISTS contains one row per row. FAMILY_DISTS provides a way to review recorded inter-familial distances in a way similar to the way data is recorded in the field. It is also useful for changing database content, since this is often done based on data recorded in the field. The FAMILY_DISTS view is neither particularly efficient nor always well suited to data analysis. Other views will very likely be faster and more appropriate when analyzing data. The , , and columns are not useful from a query standpoint but are, at present, needed when changing some database content; notably, but not exclusively, when inserting new rows.

FAMILY_DISTS Definition

FAMILY_DISTS Columns Column From Description FollowID . Identifier of the follow. IntID . Identifier of the observation interval during which the inter-familial distance was recorded. DistID . Identifier of the row recording inter-familial distance. D_MomDPID . Identifier of the distance participant row which records distance data regarding the mother in the follow, or &null;. D_InfantDPID . Identifier of the distance participant row which records distance data regarding the infant in the follow, or &null;. D_SibDPID . Identifier of the distance participant row which records distance data regarding the sibling in the follow, or &null;. Date . The date of the follow. Time . The time of the interval. Mom . The of the mother in the follow. Infant . The of an infant in the follow. Infant2 . The of a second infant in the follow, or &null; if the infant is not a twin. Sib . The of a sibling in the follow, or &null; if there is no sibling focal. Sib2 . The of a second sibling in the follow, or &null; if the sibling is not a twin. Uncertain . Whether or not the distance was observed with certainty. D_Mom . The identifier or alternate identifier of the mom, or &null; if the mother is not involved in the distance measurement. D_Momrole . The of the mother in the inter-familial distance measurement. See above regards inter-familial distance measurement roles. D_Infant . The identifier or alternate identifier of the infant, or &null; when the infant is not involved in the distance measurement. D_Infantrole . The of the infant in the inter-familial distance measurement. See above regards inter-familial distance measurement roles. D_Sib . The identifier or alternate identifier of the sibling, or &null; when the sibling is not involved in the distance measurement. D_Sibrole . The of the sibling in the inter-familial distance measurement. See above regards inter-familial distance measurement roles. Distance . Code designating the distance between the individuals. I_Comment (Interval Comment) . Textual remarks regarding the interval.

FAMILY_DISTS Usage Guidelines Altering the FAMILY_DISTS view alters the content of the underlying and tables in the fashion described below. The and related and rows must exist; the content of these tables is never altered.

INSERT Inserting a row into the FAMILY_DISTS view inserts a row into the table, as expected. When inserting into FAMILY_DISTS the interval of the follow must be specified. The interval may be specified in either or both of the following 2 ways: A may be supplied. A may be supplied and the follow specified. The follow may be specified in either or both of the following 2 ways: A may be supplied. , and, values all may be supplied. See the note regards twins when inserting into FOLLOW_INTERVALS. FAMILY_DISTS inserts rows into the database only when there is a row that matches the above selection criteria. If no row matches the database content is not altered. Note that no row will match if conflicting information is supplied. When inserting into FAMILY_DISTS it is not recommended to set any of the , , or, values. Omitting these columns or supplying &null; values will allow the system to automatically generate ids.

Update Updating the FAMILY_DISTS view updates , as expected. Updating FAMILY_DISTS can update, insert into, or delete rows from . The examples below explicitly specify an . value. This is solely for the purpose of brevity. Any valid WHERE clause could be used instead to designate the desired row in . Although the initial examples show how to insert and delete individual rows this is never done, at least not without other changes, because rows always come in pairs. An example combining insertion and deletion follows. If the old values for a mom, infant, sib or nonfocal row are not &null; and the new values are all &null; then the old row is deleted. Since every distance measurement is between two focals there is not a lot of point in deleting a focal's row unless another focal's row is inserted -- at which point you may as well have changed the chimpid of the focal and re-used the existing row. The following example removes the mother as a participant in the inter-familial distance measurement with a . of 1234: Deleting The Mother's DISTANCEPARTS Row

UPDATE family_dists
       SET d_mom = NULL
         , d_momrole = NULL
         , d_momdpid = NULL
       WHERE distid = 1234;

If the old values for a mom, infant, sib, or nonfocal are &null;, i.e. if the mom, infant, sib, or nonfocal has no row then a new row can be inserted by supplying values. The following example adds, to the inter-familial distance measurement with a . of 1234, assuming she is not already one of the individuals involved in the inter-familial distance measurement, the mother. Inserting A DISTANCEPARTS Row For The Mother

UPDATE family_dists
       SET d_mom = 'GA'
         , d_momrole = 'M'  -- M is the mother role 
       WHERE distid = 1234;

If old values exist, they are replaced by new values. Assuming the infant JOE is involved in the inter-familial distance measurement with an . of 1234, the following example changes the infant to JAK: Changing A Inter-Familial Distance Measurement Participant In A DISTANCEPARTS Row

UPDATE family_dists
       SET d_infant = 'JAK'
       WHERE distid = 1234;

The values need not correspond to the roles the individuals play in the follow because data is associated with chimp id values, not focal roles. The presence of focal roles in the column names is, in some sense, artificial. For example, there are two ways to change the infant (INF) to a sibling (SB) in the inter-familial distance measurement with an . of 1234. The first is to delete the row for the infant and insert a new row for the sibling: Deleting the Infant's DISTANCEPARTS row and Inserting a New DISTANCEPARTS Row for the Sibling Deleting Infant's DISTANCEPARTS row and Inserting a New Row for the Sibling

UPDATE family_dists
       SET d_infant = NULL
         , d_infantrole = NULL
         , d_infantdpid = NULL
         , d_sib = 'SB'
         , d_sibrole = 'S'  -- S is the sibling role 
       WHERE dpid = 1234;

UPDATE family_dists
       SET d_infant = 'SB'     -- SB is the sibling
         , d_infantrole = 'S'  -- S is the sibling role 
       WHERE distid = 1234;

When the result is queried SB will show up as a sibling, not an infant. It is an error to attempt to update the values of any columns not derived from the or tables. When updated, FAMILY_DISTS raises a notice containing the number of rows altered. A row is counted only once, when updating the row changes the database content in any way.

Delete Deleting rows from the FAMILY_DISTS view deletes rows from and , as expected.

&group_section; Views To be filled in.

The Gombe-MI Functions

Query Functions The functions in this section are suitable for use in SQL queries. followfunc followfunc return the function (role) of a chimp in a follow VARCHAR followfunc INT followid VARCHAR(&gmi_animid_len;) chimpid Input followid A . value. chimpid A . value. Description A function which returns the function (role, i.e. &gmi_mom;, &gmi_infant;, or &gmi_sib;) of a chimpid in a follow, or &gmi_nonfocal; if the chimp is not a focal in the follow. A &null; is returned if any of the input arguments are &null;. followrole followrole return the role of a chimp in a follow VARCHAR followrole INT followid VARCHAR(&gmi_animid_len;) chimpid Input followid A . value. chimpid A . value. Description A function which returns the role, i.e. &gmi_mom;, &gmi_infant;, or &gmi_sib; of a chimpid in a follow, or &gmi_nonfocal; if the chimp is not a focal in the follow. Note that the role, returned by followrole(), is able to distinguish twins from other infants or siblings whereas the function, returned by followfunc(), does not make this distinction. A &null; is returned if any of the input arguments are &null;.

Utility Functions The functions in this section are intended for use within programs or other code. The typical user will not have need of these functions. add_social_partners add_social_partners Ensure that all social interaction partners are in the group composition data INT add_social_partners VARCHAR(&gmi_animid_len;) chimpid BIGINT rgid Input chimpid A . value of a chimp in a group composition who may have social partners. rgid A . value identifying the group composition. Description add_social_partners adds all non-&gmi_unk_animid; social partners, and the mother's of young infant social partners and the young infants of mother social partners, of the specified chimp to the group composition data identified by the rgid. Social partners are those individuals (or baboons While baboons are not individually identified anywhere the table does have a flag indicating whether baboons are present. ) which interact with the given individual in any way during the time period starting with the time of the group composition (inclusive of endpoint) and ending with the time of the follow's next group composition (exclusive of endpoint). The group composition is unchanged if all social partners are already in the group composition. calls and on the added social partners to ensure that young infants are in the group composition with their mothers and vice versa. This function does not check it's arguments. It assumes that the supplied chimpid is in the supplied group composition. When inserting a row the value used is the . of a social partner This matters in the case of twins. , the value used is always &gmi_automatic_origin;, and the value always &false; This function always returns 0. delete_gc delete_gc Delete a follow's group composition data BOOLEAN delete_gc DATE followdate VARCHAR(&gmi_animid_len;) chimpid BOOLEAN delete_gc INT followid Input The follow for which group composition should be deleted may be designated either by supplying the date and mother of the follow or by supplying the id of the follow. followdate The date of the follow for which group composition should be deleted. chimpid The . of the mother in the follow for which group composition should be deleted. followid The . of the follow for which group composition should be deleted. Description delete_gc deletes all of a follow's related group composition information -- the related rows in , , , and are deleted. This function orders the deletes in such a way that performance may be improved by orders of magnitude. It is therefore the preferred way to delete group composition. This function returns &true; if the supplied follow exists and group comp rows are deleted and &false; otherwise. The following example demonstrates how to delete all of the automatically generated group compositions. Deleting Automatically Generated Group Compositions

SELECT delete_gc(follows.followid)
  FROM follows
  WHERE EXISTS (SELECT 1
                  FROM intervals
                         JOIN sheets
                              ON (sheets.sheetid = intervals.sheetid)
                         JOIN fieldfollows
                              ON (fieldfollows.ffid = sheets.ffid)
                      WHERE intervals.followid = follows.followid
                            AND fieldfollows.gcstatus = '&gmi_automatic_gcstatus;');

infants_in_gc infants_in_gc Ensure that the (biological or adopted) young infants of a mom are the group composition data BOOLEAN infants_in_gc VARCHAR(&gmi_animid_len;) chimpid BIGINT rgid DATE date Input chimpid A . value of a possible (biological or adoptive) mother. There are no restrictions regards sex or age of the given chimp. In theory, any individual can adopt. Regardless, the caller should not need to worry about such details. rgid A . value identifying a group composition. date The date of the group composition. Description infants_in_gc adds the (biological or adopted) infants of a possible mother identified by chimpid to the group composition data identified by the rgid when the infant is less than &gmi_min_alone_age; old as of the date supplied in the date argument and in the study population and the possible mom is either the biological mother or an adoptive mother and the adoption occurred on or before the given date and the infant has not been adopted subsequently (but before the given date). The group composition is unchanged when the infant is already in the group composition. Because adoptions can happen at any time during a day, when the given group composition date is the date of adoption the infant is added to all group compositions where either mother is found. This function does not check it's arguments. It assumes that the supplied date is the date of the supplied group composition and that the supplied chimpid is in the supplied group composition. When inserting a row the value used is the . of the (biological or adopted) infant This matters in the case of twins. , the value used is always &gmi_automatic_origin;, and the value always &false; A &true; value is returned if a row for any infant is added to , &false; is returned if not. infer_gc infer_gc Infer group composition from social relationships BOOLEAN infer_gc DATE followdate VARCHAR(&gmi_animid_len;) chimpid BOOLEAN infer_gc INT followid Input The follow for which group composition should be inferred may be designated either by supplying the date and mother of the follow or by supplying the id of the follow. followdate The date of the follow for which group composition should be inferred. chimpid The . of the mother in the follow for which group composition should be inferred. followid The . of the follow for which group composition should be inferred. Description infer_gc adds all non-&gmi_unk_animid; social partners, including baboons, and the mother's of young infant social partners and the young infants of mother social partners, of the focals of the specified follow to the group composition tables. It does so by first ensuring that the focals are in the group composition and then calling and on all of the focals. Social interaction with baboons is also checked for. See the documentation of for further information. This function does not remove any data from the group composition tables. If an existing group composition is to be re-inferred based on changes to database content the old group composition data must first be deleted. It is an error to attempt to infer group composition where group composition already exists -- as determined by whether . is &null; The group compositions, the rows, are created at intervals of &gmi_gc_interval;, starting at the time of the first behavioral observation, the first . value. Periods of no observation, periods during which there are no rows, lasting &gmi_gc_reset_interval; or longer cause a new set group compositions to start beginning at the time of the next observation, at the time of the next . value. This function returns &true; if the supplied follow exists and group comp rows were created and &false; otherwise. The following example demonstrates how to infer group composition of all the follows which have none. All of the rows of any follow having a group composition have a value. Those with no group composition have a &null; value, and this is used to identify all the follows with no group composition. Inferring the group composition of follows that have none

SELECT infer_gc(follows.followid)
  FROM follows
  WHERE NOT EXISTS (SELECT 1
                      FROM intervals
                      WHERE intervals.followid = follows.followid
                            AND intervals.gid IS NOT NULL);

mom_in_gc mom_in_gc Ensure that the (biological or adoptive) mother of a young chimp, and all the mom's young infants, are in the group composition data BOOLEAN mom_in_gc VARCHAR(&gmi_animid_len;) chimpid BIGINT rgid DATE date Input chimpid A . value of a possible infant. Any in-group may be supplied. The function performs all necessary filtering regards age, existence of mother, and so forth. rgid A . value identifying a group composition. date The date of the group composition. Description mom_in_gc adds the (biological or adoptive) mother, as well as all of the mother's young infants, of the chimp identified by chimpid to the group composition data identified by the rgid when the chimp is less than &gmi_min_alone_age; old as of the date supplied in the date argument. The group composition is unchanged when the chimp is older than &gmi_min_alone_age; old, and also unchanged if the mother is already in the group composition. The mother added must be in the study population as of the supplied date. When there are multiple (in-study) adoptive parents the most recent adopter, on or prior to the supplied date, is added to the group composition, regardless of the presence or absence of other parents in the group composition. mom_in_gc calls on the added mother to ensure that young infants are in the group composition with their mothers. This function does not check it's arguments. It assumes that the supplied date is the date of the supplied group composition and that the supplied chimpid is in the supplied group composition. When inserting a row the value used is the . of the (biological or adoptive) mother, the value used is always &gmi_automatic_origin;, and the value always &false; A &true; value is returned if a row for the mother is added to , &false; is returned if not.

Data Entry

Data Entry Overview The prototypical way to import data into Gombe-MI is in bulk, via a plain text file having columns delimited by the tab character. These are easily produced by almost any spreadsheet program; it is expected that most data imported into Gombe-MI will be typed into a spreadsheet and then exported to tab-delimited text for upload. Most data are uploaded into Gombe-MI via the program, most often directly into . The &ppa; program's import function can also be used, although it aborts as soon as it encounters an error. Data may also be entered row by row directly into the database, either via the &ppa; web interface, or by entering SQL into either &ppa; or any other &postgresql; front-end. Typically when uploading data into &postgresql; encountering an error causes the upload to abort. This can be a problem when uploading large amounts of data since it results in a repeated cycle of upload-fix-re-upload. Gombe-MI contains some bespoke programs that address this issue by attempting to continue uploading after an error is encountered in order to deliver back to the user as many errors as possible from a single upload. The nature of the data validation built into Gombe-MI has an impact on the efficient use of these sorts of upload programs. Data validation occurs in two stages. First, as much validation as possible takes place as each row is inserted into each table. This phase of the data validation process is called here, for lack of a better term, before commit data validation. The second phase of data validation occurs once all the data has been inserted into the database. In this phase additional validation occurs to check for consistency throughout the data as a whole. This second phase of data validation is called here the on commit data validation phase. The two phases of data validation can be distinguished by the errors messages they produce. Before commit data validation error messages do not contain the phrase on commit in their text, on commit error messages do contain this phrase. The upload programs can recover and continue to upload additional rows if a data validation error is found during the before commit phase of data validation. In this case it can be beneficial to upload large files in order to produce large numbers of error messages and minimize the number of upload-fix-re-upload cycles. However once the data is clean enough that the upload program ceases to produce before commit error messages, once the on commit validation phase has been entered, only a single error message at a time can be returned. Encountering an error during the on commit validation phase always immediately aborts the upload. When the data is clean enough that only on commit errors are returned then it can be beneficial to upload small files in order to reduce the amount of time it takes to produce an error and increase the speed of the upload-fix-re-upload cycle. Gombe-MI contains a minimal number of bespoke programs. Some few of these programs are utility in nature: a program to logout, a program to automate the steps involved in the creation of a new database user, and so forth. Most of the data entered into Gombe-MI is collected in tabular, row-and-column, format suitable for entry into a relational database. As mentioned previously, this data can be imported directly. Most errors in computer data entry can be caught by the wwwdiff program. This program compares files. Typos are detected by entering the data twice, preferably by 2 different people, and comparing the result. Errors made in the field are more likely to be detected by manual checks of the data, or by the data validation built into Gombe-MI.

Automatically Generated IDs The system will automatically generate id columns whenever a new row is inserted and an id column is not supplied. When an id value is supplied the system does not check to see that it is indeed the next id in sequence, nor does it update the sequence number to be automatically supplied the next time a row is inserted without an id column. Should an id value be manually supplied, it may be necessary to update the internal id counter so that future system generated ids will not conflict with an id already used. See the PostgreSQL documentation section on sequence functions for reference material on the requisite &postgresql; functions. Don't supply id values manually unless you know what your doing. Automatically generated ids are not guaranteed to be contiguous.

The Gombemi-MI Custom Programs To reduce cost the number of custom programs are minimal.

Data Maintenance Programs These are the programs that are used in the entry and maintenance of the Gombe-MI Master tables. Their use should be fully documented elsewhere in manuals describing how the system is used when performing specific tasks. The summary written here provides a statement of purpose and a mention of all updated data. The operation and behavior of the programs supports the table and program characteristics documented in this manual. For more information on the actual capabilities of the programs see the documentation in the programs' source code. Some programs are designed to upload data in batch -- each run of a program uploads a single file containing multiple lines of data, each of which is then inserted into the database as a row of data in one (or more Uploading into a single row of a view can update multiple tables, and programs designed to handle specialized input data formats may update arbitrary portions of the database as needed. ) database tables. Other programs provide utility functions. The upload programs take as input a file of data arranged in tabular format. The file is expected to contain plain text, with rows on separate lines and columns separated by a single tab character. This data structure can be produced by exporting data from a spreadsheet as tab delimited text. The programs upload the data into the database in an all-or-nothing fashion. Ether all the data in a uploaded file is inserted into the database or, should any error occur, none of it is. After an error each program continues to process the uploaded file so as to catch additional errors. However the input line containing the erroneous data is ignored so the trial Once an error occurs no changes will be committed to the database. insertion into the database of the subsequent lines in the uploaded file may result in spurious errors due to the missing data. It is left to the operator to distinguish the real errors from the false positives. When in doubt simply correct the errors that are clearly problems, notably the first error reported, and re-run the program. For reasons of security most browsers will remove pathnames from forms. Should a program which imports data into Gombe-MI from a file find an error in the data, rather than re-enter the pathname of the file to be uploaded simply press the browser's "reload" button. This (usually, depending on the browser) redoes the upload using the previously entered file name -- but with the new, now-corrected, data content. Each time any of the Gombe-MI web programs successfully uploads a file into a database Gombe-MI remembers the name of the file and the database. The Gombe-MI programs raise an error to warn a user who has upload a file of the same name into the same database twice in a row. Re-uploading a third time overrides the warning, allowing the re-upload to complete. For more information on whether data is required to present, as well as other required characteristics of the data values, see the documentation of the specific column into which the data is stored.

UPLOAD_BEHAVIORS: View to upload follow data The view takes the place of a custom web-based upload program. The program can be used to insert data into this view and thence into the various follow related tables.

Upload: Upload Into Any Table or View Upload uploads data into any table or view. Its primary purpose is to report as many data errors as possible during a single upload. Many other upload facilities will abort on the first error. Upload also provides an assured way to upload into views. At the time of this writing most &postgresql; front ends are unable to import data into views, although this is in the process of being addressed.

Table and Column Names The name of the table uploaded into may be qualified with a schema name to upload data into tables or views that are not in the gombemi schema. Both the table name, entered into the web browser, and the column names, appearing as column headings in the uploaded file, are taken exactly as written. Capital characters, spaces, or other unusual characters are taken exactly as written. This is in contrast to typical occurrences of table and column names within &postgresql; SQL statements, where such identifiers are normalized and automatically converted to lower case. The Upload program can therefore insert into tables with unusual names or columns, at the cost of being persnickety about table and column names. The one exception to the above rule is that when the Upload program is told to trim leading and trailing spaces from the data in each uploaded column. In this case leading and trailing spaces are also trimmed from the column names appearing in the first line of the uploaded file.

Trim Leading and Trailing Spaces The upload program has a checkbox which determines whether or not it removes (trims) leading and trailing spaces from each uploaded datum -- from each column of each row in the uploaded file.

&null; Values There are 2 ways to upload &null; data values. The easiest is to omit the column. Columns without some other default value will be given &null; values. The second is to check the checkbox labeled "Upload NULL Values" and supply a input value for &null;. Data values that match the given &null; representation will then be given a &null; value in the database. The default &null; representation is the empty string, no data at all. When this representation is used (and the "Upload NULL Values" checkbox is checked) data that are omitted in the input file becomes &null; when uploaded into the database. \N is sometimes used to represent the &null; value. A space (or multiple spaces) may be chosen as the &null; representation. This can be difficult to discern while operating the program.

Upload Data Input Format The uploaded data must be in either CSV or tab delimited format. When in tab delimited format the first line of the input file must contain the column names, each separated by a tab. The remaining lines of the file contain the data to be uploaded. Each line is a row of data, each column is separated from its neighbor with a tab character. &csv_description; A line need not contain as many data elements as there are column names given in the first line. All unspecified data elements will be given a blank value, the empty string, just as if, with tab delimited data, the tabs occurred but no data were specified. This may or may not result in a &null; value in the database, depending on how the program is invoked. A line must not have more data elements than there are column names given in the first line.

Upload_GC: Upload Group Composition Data The Upload_GC program uploads group composition data into the database. The program creates rows in the and tables and the database itself then creates the necessary rows in and . Upload_GC calls mom_in_gc(), infants_in_gc(), and add_social_partners() on the uploaded follows. Use of any other means to add group composition data to the database or modify group composition data can result in failure to meet the assurances provided by the aforementioned functions. One way to correctly approach such manual group composition alterations is to alter the group composition in the database, delete all the rows having an value of &gmi_automatic_origin;, and then re-run mom_in_gc(), infants_in_gc(), and add_social_partners() on the altered follows. There must be no existent information on an uploaded follow's group composition in the database. This is due to the need to call the aforementioned functions. Uploading a single follow's group composition in multiple uploads would result in incorrect group composition inference.

Upload_GC Data Input Format The uploaded data must be in either CSV or Tab delimited format (each column separated from the next by a tab character). Leading and trailing spaces are removed from the data contained in each column before any other processing is performed. &csv_description; The overall layout of the uploaded data is similar to that of the data sheet collected in the field, described in the &gcdbh;. The first line of the file contains column headings and each of the remaining lines contains the group composition recorded for a given time interval of a specified follow. All of the group composition information related to a follow must be in contiguous lines of the uploaded file. There must be no group composition information of a different follow interspersed among the lines which comprise any follow's group composition data. The first line of the file, as mentioned, contains the column headings. Column headings may appear in any order. There are 2 kinds of column headings, those to which the program assigns specific uses and those which represent s. All non- columns, unless otherwise noted, are required. Character case is significant, unless otherwise noted, both in column headings and elsewhere. Although the non- columns listed below are shown in mixed-case For legibility and for consistency in style with the manner in which other column names are written in this document. in the uploaded file all of these column headings must be lower case.

Upload_GC non-ChimpID Columns Column Description Purpose Researcher The .Descr value identifying the field researcher who collected the data. The comparison is done in a case-insensitive fashion. One of the columns which must match (the . value) when determining the follow to which the group composition belongs. Date The date of the follow. The date may be formatted in any manner acceptable to &postgresql;. One of the columns which must match (the . value) when determining the follow to which the group composition belongs. Mother A of the mother in the follow. One of the columns which must match (the . value in the row identifying the chimp who is the mother in the follow) when determining the follow to which the group composition belongs. The supplied must match exactly the value recorded in the . of the row identifying the chimp who is the mother in the follow. Other s which the mother may have are not allowed. Page The page number written on the field data collection sheet recording the group composition. One of the columns which must match (.) when determining the follow to which the group composition belongs. The uploaded value must be blank when the page numbers on the sheets were not entered into the database -- when the . value of the follow is &null;. Time_On_Sheet The time of the group comp as it was written on the data entry sheet. The value entered into this column is ignored. The presence of this column in the uploaded file is optional. Time_Converted_Mil The time of the group composition, written in military time form. The time value used in the . column, and consequently the value used to relate the group composition to the intervals during which behavioral data was collected. Although defined by protocol to be military time the program will accept a time value formatted in any manner acceptable to &postgresql;. Time_Type The time encoding scheme used when the data was collected in the field. The value entered into this column is ignored. Entered Whether or not the group comp information was entered into the MS Access version of the database. The value entered into this column is ignored. The presence of this column in the uploaded file is optional. GCStatus The status code assigned to the group composition information collected on the follow, per field researcher who collected the information. The value to record in the . column. All of the uploaded GCStatus values for a given follow for a given field researcher must match. &gmi_animid_baboon; A column for recording baboon presence. Unlike regular columns this column may never contain information on paracallosal swelling. An entirely empty column. For purposes of visual formatting the uploaded file may, but is not required to, contain (at most) a single column that contains no data what so ever. All column headings not listed above are expected to be valid values. The entries in the rows for the columns must contain values found in .. When the mark indicates that the individual identified in the column heading is present a row is created in the table. The database then, in turn, creates a row. The table should contain a row having a value which is the empty string (a blank value) because the group composition sheet collected in the field uses a blank to indicate absence. A column heading is not tested for validity unless at least one of marks contained in the column indicates presence in the group. At least one column must exist in the uploaded file.

Logout: Logout From Gombe-MI Custom Programs The logout program logs the user out of the Gombe-MI web based collection of programs. Gombe-MI consists of many programs, only some of which are web based programs written specifically for Gombe-MI. The logout program only controls access to those programs written specifically for Gombe-MI, other off the shelf programs (notably &ppa;) have their own logout mechanisms. Logout from Gombe-MI is automatic after a period of inactivity. Currently 1 hour.

Wwwdiff: World Wide Web based Difference program The wwwdiff program compares two text files. It can be found on the &gombemiwebsite;. Among other uses, this program provides a useful data validation mechanism. To validate data, have two different individuals enter the data and compare the results with the wwwdiff program. It is unlikely that both individuals will make identical errors and so almost all data transcription errors should be caught using this method. The program uploads the two files to be compared. For security reasons most web browsers will always clear the names of the uploaded files once they have been uploaded. This makes it difficult to repeatedly upload the same or similar files, as when re-comparing two files after correcting errors. The situation is not as bad as it might sound because browsers will often provide a browse button and keep track of the directory last accessed, removing the need to re-navigate to the location of the data files. But it is still awkward to have to repeatedly point and click. One solution is to use the browser's reload button. This will repeat the upload and comparison of the two files, but using the new, corrected, file content. A second, less desirable possibility is to have the have the pathnames of the files handy in a text document and cut and paste them as needed. A third possibility might be to use the browser's back button, but browsers will often clear the file upload information in this case in the same fashion they would with password information. The wwwdiff program provides 4 comparison methods: Tabular by Word Useful where the data consists of individual words aligned as rows and columns of data. Compares the files contents on a word-by-word basis and displays the entire content of both files as a table with differences marked. When one file contains whitespace Spaces, tabs, and whatever other characters that can't been seen. that is not in the other, this comparison method shows extra cells in the output. Words are separated by whitespace but because there is only extra whitespace the cells are empty. Thus, when one file contains more whitespace than the other those rows will contain more columns. Normally the data in the extra cells would be color coded to inform as to whence they came, but because the cells are empty there is nothing to color code. The operator must compare the files by hand to determine which file contains the extra whitespace. By Word Useful where the data consists of words and there are relatively few changes or where paragraphs have been refilled and words have moved from line to line. Compares the file contents on a word-by-word basis displaying the entire content of both files as plain text. By Line Useful when there is a large amount of textual data. Compares the file contents on a line-by-line basis and displays only a small amount of context surrounding those portions of the text which differ between the files. By File Useful when comparing non-text files. Reports the location (by line and byte offset from the beginning of the files) of the first difference found.

System Management Programs The programs in this section are of use to the administrators that manage the system. These programs require login be done using a username having administrative authority. These programs automatically logout upon completion; usernames and passwords must be re-entered each invocation.

Add_User: Create a database user The Add_User program provides a convenient way to add database users, users who have access to the database. This program is something of a non-essential convenience, as it's possible to do everything the Add_User program does with SQL statements. Add_User provides a convenient web interface which adds a user, constructs an initial password, creates a private schema for the user in both the gombemi and the gombemi_test databases, and gives the new user appropriate permissions to database content. Every person should have their own username. Usernames (and passwords) should never be shared. Either ordinary database usernames or administrative usernames may be created. Ordinary usernames have either read-only access to the gombemi schema or read/write access to the gombemi schema. Ordinary users always have read/write access to their own private schemas. For further information see: Only administrative logins (administrative users) can create new users. Passwords should not be known to anyone but the person to whom the username belongs. Once given the initial password by the administrator who created the username, the person receiving the new username/password should immediately replace the password by one of their own choosing. This can be done with &ppa; or manually via the ALTER ROLE SQL statement. People who have administrative privileges to the database should have separate usernames for ordinary and administrative use. Ordinary use of the database should be done with a non-administrative login to avoid the risk of accidental database destruction. You know the rule: With great power comes great responsibility. Administrative users have the permissions necessary to do great damage. Enable those permissions only at need. All usernames are created in lower case. Leading and trailing spaces are removed. When a new username is created the program generates a password and reports the generated password. If the generated password is lost a new password must be manually assigned to the user in &ppa;. Reassignment of a username's database role (whether the username is a member of or ) can be done either with the SQL GRANT ROLE and DROP ROLE statements or via the administrative facilities of &ppa;. Either way the operation must be performed by an Administrative user.

Copy_Schema: Copy Schemas Between Databases The Copy_Schema program copies schemas between databases. The schema and all it's content is copied. Permissions are preserved. An administrative login must be used to copy a schema between databases. The Copy_Schema interface has 2 checkboxs that control it's behavior: Copy_Schema will not copy a schema on top of an existing schema of the same name, destroying the original schema in the target database, unless explicitly asked to do so. Copy_Schema will not alter the gombemi database, potentially destroying production data, unless explicitly asked to do so. It can take some time, depending on the size of the schema to copy and the size of the target database, to copy a schema. Expect execution times that may be in the 10s of minutes. The Copy_Schema program performs a VACUUM ANALYZE on the target database, which contributes to the amount of time it takes to perform the copy.

Restrictions: Things Not To Do Using the SET CONSTRAINTS statement to change the timing of constraints can reduce Gombe-MI's functionality. Constraints must be checked when expected for the proper operation of the system. Many data integrity checks are not performed on truncate. Do not truncate the tables. One Minute Interval Assumptions There are some elements of the Gombe-MI system which assume that the follows have a 1 minute interval duration. This appendix documents these system elements. The test for non-overlapping follow intervals. The test which requires that a mother's offspring who are less than &gmi_min_alone_age; old be with the mother in the group composition survey relies on all intervals being the same duration when checking whether the offspring is with a different (adopted) mother.