Group Membership and Life Events

Group Membership and Life Events
Prev	Chapter 6. The Babase Views	Next

CENSUS_DEMOG (CENSUS extended with DEMOG information)

Contains one row for every row in CENSUS. Each row contains the CENSUS columns and the related DEMOG columns. In those cases where there is a CENSUS row but no related DEMOG row the DEMOG columns will be NULL. Because there is a one-to-one relationship between CENSUS and DEMOG, and a DEMOG row always has a related CENSUS row, there is little utility in maintaining the DEMOG row without maintaining the related CENSUS row. This view provides a convenient way to maintain the CENSUS/DEMOG combination.

Definition

Figure 6.1. Query Defining the CENSUS_DEMOG View

SELECT census.cenid AS cenid , census.sname AS sname , census.date AS date , census.grp AS grp , census.status AS status , census.cen AS cen , demog.reference AS reference , demog.comment AS comment FROM census LEFT OUTER JOIN demog ON (census.cenid = demog.cenid);

Figure 6.2. Entity Relationship Diagram of the CENSUS_DEMOG View

If we could we would display here the diagram showing how the CENSUS_DEMOG view is constructed.

Table 6.1. Columns in the CENSUS_DEMOG View

Column	From	Description
Cenid	CENSUS.Cenid	Unique identifier of the CENSUS row.
Sname	CENSUS.Sname	Individual who's location has been recorded.
Date	CENSUS.Date	Date of demography note.
Grp	CENSUS.Grp	Group where the individual was located.
Status	CENSUS.Status	Source of location information. When the source is both a demography note and another source, like a census, the other source is shown.
Cen	CENSUS.Cen	Whether or not there was an entry on the field census data sheet for the individual on the given date.
Reference	DEMOG.Reference	The group identifying the written field notebook where the demography note can be found.
Comment	DEMOG.Comment	The demography note text.

Operations Allowed

INSERT

Inserting a row into CENSUS_DEMOG inserts two rows, one into CENSUS and one into DEMOG, as expected. However, if the underlying DEMOG columns are NULL, no DEMOG row will be inserted.

Warning

The PostgreSQL nextval() function cannot be part of an INSERT expression which assigns a value to this view's Cenid column.

UPDATE

Updating a row in CENSUS_DEMOG updates the underlying columns in CENSUS and DEMOG, as expected. However, the relationship between CENSUS and DEMOG introduces some complications.

Caution

The CENSUS table is updated before the DEMOG table. Because updating the DEMOG table can change the CENSUS.Status column the resulting value may not be that specified by the update.

Updating the Cenid column updates^[249] the Cenid columns in both CENSUS and DEMOG. Setting all the DEMOG columns (Cenid excepted) to NULL causes the deletion of the DEMOG row. Setting DEMOG columns to a non-NULL value when all the DEMOG columns were NULL previously creates a new row in DEMOG.

DELETE

Caution

The CENSUS-DEMOG view cannot be used to delete arbitrary CENSUS rows.

Deleting rows from CENSUS_DEMOG updates the database in a fashion that removes the related demography note information from storage.

Deleting a row in CENSUS_DEMOG deletes the underlying row in CENSUS when appropriate; when the CENSUS row exists only because there is an underlying row in DEMOG. That is, the CENSUS row is deleted if and only if the CENSUS.Cen column is FALSE. If there is an underlying row in DEMOG it is always deleted.

CENSUS_DEMOG_SORTED (CENSUS_DEMOG, Sorted)

Contains one row for every row in the CENSUS_DEMOG view. The only difference between this view and the CENSUS_DEMOG view is that this view is sorted.

Definition

Figure 6.3. Query Defining the CENSUS_DEMOG_SORTED View

SELECT census.cenid AS cenid , census.sname AS sname , census.date AS date , census.grp AS grp , census.status AS status , census.cen AS cen , demog.reference AS reference , demog.comment AS comment FROM census LEFT OUTER JOIN demog ON (census.cenid = demog.cenid) ORDER BY census.sname, census.date;

Figure 6.4. Entity Relationship Diagram of the CENSUS_DEMOG_SORTED View

If we could we would display here the diagram showing how the CENSUS_DEMOG_SORTED view is constructed.

Table 6.2. Columns in the CENSUS_DEMOG_SORTED View

Column	From	Description
Cenid	CENSUS.Cenid	Unique identifier of the CENSUS row.
Sname	CENSUS.Sname	Individual who's location has been recorded.
Date	CENSUS.Date	Date of demography note.
Grp	CENSUS.Grp	Group where the individual was located.
Status	CENSUS.Status	Source of location information. When the source is both a demography note and another source, like a census, the other source is shown.
Cen	CENSUS.Cen	Whether or not there was an entry on the field census data sheet for the individual on the given date.
Reference	DEMOG.Reference	The group identifying the written field notebook where the demography note can be found.
Comment	DEMOG.Comment	The demography note text.

Operations Allowed

The operations allowed are as described in the CENSUS_DEMOG view.

CYCPOINTS_CYCLES (CYCPOINTS extended with CYCLES information)

Contains one row for every row in CYCPOINTS. Each row contains the CYCPOINTS columns and the related CYCLES columns. Because there is a many-to-one relationship between CYCPOINTS and CYCLES, the same CYCLES data will appear repeatedly, once for each related CYCPOINTS row. As a CYCPOINTS row always has a related CYCLES row, and the CYCLES row is what identifies the cycling female, when working with the CYCPOINTS table alone it is difficult to tell which dates belong to which females. This view provides a convenient way to create and maintain the CYCPOINTS/CYCLES combination.

Definition

Figure 6.5. Query Defining the CYCPOINTS_CYCLES View

SELECT cycles.cid AS cid , cycles.sname AS sname , cycles.seq AS seq , cycles.series AS series , cycpoints.cpid AS cpid , cycpoints.date AS date , cycpoints.edate AS edate , cycpoints.ldate AS ldate , cycpoints.code AS code , cycpoints.source AS source FROM cycles, cycpoints WHERE cycles.cid = cycpoints.cid;

Figure 6.6. Entity Relationship Diagram of the CYCPOINTS_CYCLES View

If we could we would display here the diagram showing how the CYCPOINTS_CYCLES view is constructed.

Table 6.3. Columns in the CYCPOINTS_CYCLES View

Column	From	Description
Cid	CYCLES.Cid	Arbitrary number uniquely identifying the CYCLES row.
Sname	CYCLES.Sname	Female that is cycling.
Seq	CYCLES.Seq (readonly)	Number indicating the cycle's position in time within the sequence of all the cycles of the female. Counts from 1 upwards.
Series	CYCLES.Series (readonly)	Number indicating with which CYCLES (Female Sexual Cycles) of continuous observation the cycle belongs.
Cpid	CYCPOINTS.Cpid	Number uniquely identifying the CYCPOINTS row.
Date	CYCPOINTS.Date	Date-of-record of the sexual cycle transition event.
Edate	CYCPOINTS.Edate	Earliest possible date for the sexual cycle transition event.
Ldate	CYCPOINTS.Ldate	Latest possible date for the sexual cycle transition event.
Code	CYCPOINTS.Code	The type of sexual cycle transition event. Mdate, Tdate, or Ddate.
Source	CYCPOINTS.Source	Code indicating from whence the data was derived. This has a bearing as to its accuracy.

Readonly Columns

Both the Seq and Series columns are read only.

Warning

Changes to the Seq and Series columns are silently ignored.

Operations Allowed

Tip

In most cases Cid, Cpid, Seq, and Series should be unspecified (or specified as NULL), in which case Babase will compute and assign the correct values.

INSERT

Inserting a row into CYCPOINTS_CYCLES inserts a row into CYCPOINTS, as expected. Whether or not a row is inserted into CYCLES depends on whether or not the new CYCPOINTS row should be associated with a new CYCLES row or an existing one. When a Cid is supplied and a CYCLES row already exists with the given Cid then the underlying CYCLES row is updated to conform with the inserted data. When a Cid is supplied and Babase finds that the underlying CYCPOINTS row should be related to a CYCLES row with a different Cid the system silently ignores the supplied Cid.

UPDATE

Updating a row in CYCPOINTS_CYCLES updates the underlying columns in CYCLES and CYCPOINTS, as expected. Note that updating the Cid or Sname columns will attempt to update the underlying CYCLES columns and this will immediately produce an error.

DELETE

Deleting a row in CYCPOINTS_CYCLES deletes the underlying row in CYCPOINTS. The underlying row in CYCLES is deleted only when the CYCLES last related CYCPOINTS row is deleted.

CYCPOINTS_CYCLES_SORTED (CYCPOINTS_CYCLES, Sorted)

Contains one row for every row in the CYCPOINTS_CYCLES view. This view is sorted for ease of maintenance.

Definition

Figure 6.7. Query Defining the CYCPOINTS_CYCLES_SORTED View

SELECT cycles.cid AS cid , cycles.sname AS sname , cycles.seq AS seq , cycles.series AS series , cycpoints.cpid AS cpid , cycpoints.date AS date , cycpoints.edate AS edate , cycpoints.ldate AS ldate , cycpoints.code AS code , cycpoints.source AS source FROM cycles, cycpoints WHERE cycles.cid = cycpoints.cid ORDER BY cycles.sname, cycpoints.date;

Figure 6.8. Entity Relationship Diagram of the CYCPOINTS_CYCLES_SORTED View

If we could we would display here the diagram showing how the CYCPOINTS_CYCLES_SORTED view is constructed.

Table 6.4. Columns in the CYCPOINTS_CYCLES_SORTED View

Column	From	Description
Cid	CYCLES.Cid	Arbitrary number uniquely identifying the CYCLES row.
Sname	CYCLES.Sname	Female that is cycling.
Seq	CYCLES.Seq (readonly)	Number indicating the cycle's position in time within the sequence of all the cycles of the female. Counts from 1 upwards.
Series	CYCLES.Series (readonly)	Number indicating with which CYCLES (Female Sexual Cycles) of continuous observation the cycle belongs.
Cpid	CYCPOINTS.Cpid	Number uniquely identifying the CYCPOINTS row.
Date	CYCPOINTS.Date	Date-of-record of the sexual cycle transition event.
Edate	CYCPOINTS.Edate	Earliest possible date for the sexual cycle transition event.
Ldate	CYCPOINTS.Ldate	Latest possible date for the sexual cycle transition event.
Code	CYCPOINTS.Code	The type of sexual cycle transition event. Mdate, Tdate, or Ddate.
Source	CYCPOINTS.Source	Code indicating from whence the data was derived. This has a bearing as to its accuracy.

Operations Allowed

The operations allowed are as described in the CYCPOINTS_CYCLES view.

DADS (DADS_CONSENSUS, extended)

Contains one row for every row in DADS_CONSENSUS. In addition to the columns from DADS_CONSENSUS, this view also includes columns from UNIQUE_INDIVS and BIOGRAPH to clarify the identity of the kid and father.

Tip

To see both of an individual's parents, use the PARENTS view.

Definition

Figure 6.9. Query Defining the DADS View

SELECT dads_consensus.kid AS kid_uiid , kid_ui.popid AS kid_popid , kid_ui.individ AS kid_individ , kid.sname AS kid_sname , dads_consensus.dad_consensus AS dad_uiid , dad_ui.popid AS dad_popid , dad_ui.individ AS dad_individ , dad.sname AS dad_sname , dads_consensus.mismatch AS mismatch , dads_consensus.completeness AS completeness , dads_consensus.notes AS notes FROM dads_consensus JOIN unique_indivs AS kid_ui ON kid_ui.uiid = dads_consensus.kid LEFT JOIN biograph AS kid ON kid.bioid::TEXT = kid_ui.individ AND kid_ui.popid = 1 LEFT JOIN unique_indivs AS dad_ui ON dad_ui.uiid = dads_consensus.dad_consensus LEFT JOIN biograph AS dad ON dad.bioid::TEXT = dad_ui.individ AND dad_ui.popid = 1;

Figure 6.10. Entity Relationship Diagram of the DADS View

If we could we would display here a diagram showing how the DADS view is constructed.

Table 6.5. Columns in the DADS View

Column	From	Description
Kid_UIId	DADS_CONSENSUS.Kid	The unique identifier for the Kid.
Kid_PopId	UNIQUE_INDIVS.PopId	The identifier for the Kid's population.
Kid_IndivId	UNIQUE_INDIVS.IndivId	The name/identifier for the Kid in this population.
Kid_Sname	BIOGRAPH.Sname	The Sname of the Kid, if the Kid has one.
Dad_UIId	DADS_CONSENSUS.Dad_Consensus	The unique identifier for the Dad.
Dad_PopId	UNIQUE_INDIVS.PopId	The identifier for the Dad's population.
Dad_IndivId	UNIQUE_INDIVS.IndivId	The name/identifier for the Dad in this population.
Dad_Sname	BIOGRAPH.Sname	The Sname of the Dad, if the Dad has one.
Mismatch	DADS_CONSENSUS.Mismatch	The category of genetic mismatching (or lack thereof) that occurs in this paternity assignment.
Completeness	DADS_CONSENSUS.Completeness	The apparent completeness of this paternity assignment.
Notes	DADS_CONSENSUS.Notes	Miscellaneous comments about this paternity assignment, or lack thereof.

Operations Allowed

In all operations, the identifying information (UIId, PopId, IndivId, and Sname) provided for each individual (Kid or Dad) must uniquely identify a single individual.

INSERT

Inserting a row into DADS inserts a row into DADS_CONSENSUS, as expected.

UPDATE

Updating a row in DADS updates the related DADS_CONSENSUS row, as expected.

DELETE

Deleting a row from DADS deletes the related DADS_CONSENSUS row, as expected.

DADS_ANALYSES_EXT (DADS_ANALYSES, EXTended)

Contains one row for every row in DADS_ANALYSES. In addition to the columns from DADS_ANALYSES, this view also includes columns from UNIQUE_INDIVS and BIOGRAPH to clarify the identity of the kid and putative father.

Definition

Figure 6.11. Query Defining the DADS_ANALYSES_EXT View

SELECT dads_analyses.daid AS daid , dads_analyses.kid AS kid_uiid , kid_ui.popid AS kid_popid , kid_ui.individ AS kid_individ , kid.sname AS kid_sname , dads_analyses.date AS date , dads_analyses.putative_dad AS putative_dad_uiid , dad_ui.popid AS putative_dad_popid , dad_ui.individ AS putative_dad_individ , dad.sname AS putative_dad_sname , dads_analyses.comments AS comments FROM dads_analyses JOIN unique_indivs AS kid_ui ON kid_ui.uiid = dads_analyses.kid LEFT JOIN biograph AS kid ON kid.bioid::TEXT = kid_ui.individ AND kid_ui.popid = 1 LEFT JOIN unique_indivs AS dad_ui ON dad_ui.uiid = dads_analyses.putative_dad LEFT JOIN biograph AS dad ON dad.bioid::TEXT = dad_ui.individ AND dad_ui.popid = 1;

Figure 6.12. Entity Relationship Diagram of the DADS_ANALYSES_EXT View

If we could we would display here a diagram showing how the DADS_ANALYSES_EXT view is constructed.

Table 6.6. Columns in the DADS_ANALYSES_EXT View

Column	From	Description
DAId	DADS_ANALYSES.DAId	The unique identifier for this analysis.
Kid_UIId	DADS_ANALYSES.Kid	The unique identifier for the Kid.
Kid_PopId	UNIQUE_INDIVS.PopId	The identifier for the Kid's population.
Kid_IndivId	UNIQUE_INDIVS.IndivId	The name/identifier for the Kid in this population.
Kid_Sname	BIOGRAPH.Sname	The Sname of the Kid, if the Kid has one.
Date	DADS_ANALYSES.Date	The date of this analysis.
Putative_Dad_UIId	DADS_ANALYSES.Putative_Dad	The unique identifier for the Putative_Dad.
Putative_Dad_PopId	UNIQUE_INDIVS.PopId	The identifier for the Putative_Dad's population.
Putative_Dad_IndivId	UNIQUE_INDIVS.IndivId	The name/identifier for the Putative_Dad in this population.
Putative_Dad_Sname	BIOGRAPH.Sname	The Sname of the Putative_Dad, if he has one.
Comments	DADS_ANALYSES.Comments	Miscellaneous notes regarding the analysis.

Operations Allowed

In all operations, the identifying information (UIId, PopId, IndivId, and Sname) provided for each individual (Kid or Putative_Dad) must uniquely identify a single individual.

INSERT

Inserting a row into DADS_ANALYSES_EXT inserts a row into DADS_ANALYSES, as expected.

UPDATE

Updating a row in DADS_ANALYSES_EXT updates the related DADS_ANALYSES row, as expected.

DELETE

Deleting a row from DADS_ANALYSES_EXT deletes the related DADS_ANALYSES row, as expected.

DEMOG_CENSUS (DEMOG, showing CENSUS information)

Contains one row for every row in DEMOG. Each row contains the DEMOG columns and the related CENSUS columns. In those cases where there is a CENSUS row but no related DEMOG row no row exists. Because there is a one-to-one relationship between CENSUS and DEMOG and a DEMOG row always has a related CENSUS row, and because without the CENSUS information it is difficult to tell to which individual the demography note refers , there is little utility in maintaining the DEMOG row without maintaining the related CENSUS row. This view provides a convenient way to maintain the CENSUS/DEMOG combination.

Note

The DEMOG_CENSUS view is very similar to the CENSUS_DEMOG view. It is unclear which is more useful so both exist.

Definition

Figure 6.13. Query Defining the DEMOG_CENSUS View

SELECT census.cenid AS cenid , census.sname AS sname , census.date AS date , census.grp AS grp , census.status AS status , census.cen AS cen , demog.reference AS reference , demog.comment AS comment FROM census, demog WHERE census.cenid = demog.cenid;

Figure 6.14. Entity Relationship Diagram of the DEMOG_CENSUS View

If we could we would display here the diagram showing how the DEMOG_CENSUS view is constructed.

Table 6.7. Columns in the DEMOG_CENSUS View

Column	From	Description
Cenid	CENSUS.Cenid	Unique identifier of the CENSUS row.
Sname	CENSUS.Sname	Individual who's location has been recorded.
Date	CENSUS.Date	Date of demography note.
Grp	CENSUS.Grp	Group where the individual was located.
Status	CENSUS.Status	Source of location information. When the source is both a demography note and another source, like a census, the other source is shown.
Cen	CENSUS.Cen	Whether or not there was an entry on the field census data sheet for the individual on the given date.
Reference	DEMOG.Reference	The group identifying the written field notebook where the demography note can be found.
Comment	DEMOG.Comment	The demography note text.

Operations Allowed

INSERT

Inserting a row into DEMOG_CENSUS inserts a row, into DEMOG, as expected, but only when the underlying DEMOG columns are not NULL. If the values of the columns belonging to the DEMOG table are null then the insert raises an error.

A new CENSUS row is inserted if there is not already an existing CENSUS row, otherwise the existing CENSUS row is updated. To leave the value of an existing CENSUS row untouched either omit the column from the insert or specify the NULL value.

Warning

Values must be supplied for either the Cenid or all of the Sname, Date, and Grp columns or else the system will be unable to identify pre-existing CENSUS rows updated by inserts to DEMOG_CENSUS. The system may silently ignore insert operations when too few column values are supplied.

When a new CENSUS row is created the Cen column defaults to false.

Warning

The PostgreSQL nextval() function cannot be part of an INSERT expression which assigns a value to this view's Cenid column.

UPDATE

Updating a row in DEMOG_CENSUS updates the underlying columns in CENSUS and DEMOG, as expected. However, the relationship between CENSUS and DEMOG introduces some complications.

Caution

The CENSUS table is updated before the DEMOG table. Because updating the DEMOG table can change the CENSUS.Status column the resulting value may not be that specified by the update.

Updating the Cenid column updates^[250] the Cenid columns in both CENSUS and DEMOG.

DELETE

Deleting rows from DEMOG_CENSUS updates the database in a fashion that removes the related demography note information from storage.^[251]

Deleting a row in DEMOG_CENSUS deletes the underlying row in CENSUS when appropriate; when the CENSUS row exists only because there is an underlying row in DEMOG. That is, the CENSUS row is deleted if and only if the CENSUS.Cen column is FALSE. If there is an underlying row in DEMOG it is always deleted.

Note

Regardless of whether the underlying CENSUS row is deleted a delete operation will always cause the deleted row to disappear from the DEMOG_CENSUS view.

DEMOG_CENSUS_SORTED (DEMOG_CENSUS, Sorted)

Contains one row for every row in the DEMOG_CENSUS view. The only difference between this view and the DEMOG_CENSUS view is that this view is sorted.

Definition

Figure 6.15. Query Defining the DEMOG_CENSUS_SORTED View

SELECT census.cenid AS cenid , census.sname AS sname , census.date AS date , census.grp AS grp , census.status AS status , census.cen AS cen , demog.reference AS reference , demog.comment AS comment FROM census, demog WHERE census.cenid = demog.cenid ORDER BY census.sname, census.date;

Figure 6.16. Entity Relationship Diagram of the DEMOG_CENSUS_SORTED View

If we could we would display here the diagram showing how the DEMOG_CENSUS_SORTED view is constructed.

Table 6.8. Columns in the DEMOG_CENSUS_SORTED View

Column	From	Description
Cenid	CENSUS.Cenid	Unique identifier of the CENSUS row.
Sname	CENSUS.Sname	Individual who's location has been recorded.
Date	CENSUS.Date	Date of demography note.
Grp	CENSUS.Grp	Group where the individual was located.
Status	CENSUS.Status	Source of location information. When the source is both a demography note and another source, like a census, the other source is shown.
Cen	CENSUS.Cen	Whether or not there was an entry on the field census data sheet for the individual on the given date.
Reference	DEMOG.Reference	The group identifying the written field notebook where the demography note can be found.
Comment	DEMOG.Comment	The demography note text.

Operations Allowed

The operations allowed are as described in the DEMOG_CENSUS view.

GROUPS_HISTORY

Contains one row for every row in the GROUPS table. This view portrays a group's history in a more-accessible format than the GROUPS table. It collects into one row all the dates that are relevant to a particular group, including (in the case of groups that fissioned) the date they became "impermanent" by starting to fission.

This view is similar to GROUPS, but omits columns used predominantly for data entry and validation. This view also renames some columns for clarity, and adds three calculated columns.

Definition

Figure 6.17. Query Defining the GROUPS_HISTORY View

SELECT groups.gid AS gid , groups.name AS name , groups.from_group AS from_group , groups.to_group AS to_group , CASE WHEN groups.from_group IS NULL AND NOT EXISTS (SELECT 1 FROM groups AS from_groups WHERE from_groups.to_group = groups.gid) THEN groups.permanent ELSE groups.start END AS first_observed , CASE WHEN groups.study_grp IS NULL THEN NULL WHEN groups.from_group IS NULL AND NOT EXISTS (SELECT 1 FROM groups AS from_groups WHERE from_groups.to_group = groups.gid) THEN groups.permanent ELSE (SELECT date FROM census WHERE census.grp = groups.gid AND census.cen ORDER BY date LIMIT 1) END AS first_study_grp_census , groups.permanent AS permanent , (SELECT descgroups_start.start FROM babase.groups AS descgroups_start WHERE descgroups_start.from_group = groups.gid OR descgroups_start.gid = groups.to_group ORDER BY descgroups_start.start LIMIT 1 ) AS impermanent , groups.cease_to_exist AS cease_to_exist , groups.last_reg_census AS last_reg_census , groups.study_grp FROM babase.groups;

Figure 6.18. Entity Relationship Diagram of the GROUPS_HISTORY View

If we could we would display here the diagram showing how the GROUPS_HISTORY view is constructed.

Table 6.9. Columns in the GROUPS_HISTORY View

Column	From	Description
Gid	GROUPS.Gid	Unique identifier of the GROUPS row.
Name	GROUPS.Name	Name of this group.
From_Group	GROUPS.From_group	The Gid of the group from which this group was created.
First_Observed	GROUPS.Permanent, GROUPS.Start	The first date the group was observed. For groups that are fission or fusion products of other known groups, this is the group's Start. Otherwise, this is the group's Permanent.
First_Study_Grp_Census	GROUPS.Permanent, CENSUS.Date	The first date that a study group was observed as its own group and not a subgroup from its parent group (in the case of fissions) or as a temporary multi-group encounter (in the case of fusions). For non-study groups (Study_Grp is `NULL`), this is `NULL`. For groups of unknown lineage (groups whose From_group is `NULL` and whose Gid does not exist as a To_group)^[a], this is the group's Permanent date. Otherwise, this is the group's earliest CENSUS.Date where Cen is `TRUE`.
Permanent	GROUPS.Permanent	The first date on which the group was recognized as its own distinct group.
Impermanent	GROUPS.Start	The earliest Start date of this group's fission or fusion products.
Cease_To_Exist	GROUPS.Cease_To_Exist	The last date of this group's existence, and the day before fission or fusion products of this group became permanent.
Last_Reg_Census	GROUPS.Last_Reg_Census	The date of the last regular census done on the group.
Study_Grp	GROUPS.Study_Grp	The date the group became an "official" study group, or `NULL` if the group was never a study group.
^[a]Which is expected to only occur when a previously unseen group is first seen and becomes a known group.

Operations Allowed

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

MOMS (MOMS_CONSENSUS, extended)

Contains one row for every row in MOMS_CONSENSUS. In addition to the columns from MOMS_CONSENSUS, this view also includes columns from UNIQUE_INDIVS and BIOGRAPH to clarify the identity of the kid and mother.

Caution

Do not assume that this view contains all known mother-offspring pairs. Maternities that are known because of field observation — in the MATERNITIES view — are not included in this view.

Tip

To see both of an individual's parents, including all known mother-offspring pairs, use the PARENTS view.

Definition

Figure 6.19. Query Defining the MOMS View

SELECT moms_consensus.kid AS kid_uiid , kid_ui.popid AS kid_popid , kid_ui.individ AS kid_individ , kid.sname AS kid_sname , moms_consensus.mom_consensus AS mom_uiid , mom_ui.popid AS mom_popid , mom_ui.individ AS mom_individ , mom.sname AS mom_sname , moms_consensus.mismatch AS mismatch , moms_consensus.completeness AS completeness , moms_consensus.notes AS notes FROM moms_consensus JOIN unique_indivs AS kid_ui ON kid_ui.uiid = moms_consensus.kid LEFT JOIN biograph AS kid ON kid.bioid::TEXT = kid_ui.individ AND kid_ui.popid = 1 LEFT JOIN unique_indivs AS mom_ui ON mom_ui.uiid = moms_consensus.mom_consensus LEFT JOIN biograph AS mom ON mom.bioid::TEXT = mom_ui.individ AND mom_ui.popid = 1;

Figure 6.20. Entity Relationship Diagram of the MOMS View

If we could we would display here a diagram showing how the MOMS view is constructed.

Table 6.10. Columns in the MOMS View

Column	From	Description
Kid_UIId	MOMS_CONSENSUS.Kid	The unique identifier for the Kid.
Kid_PopId	UNIQUE_INDIVS.PopId	The identifier for the Kid's population.
Kid_IndivId	UNIQUE_INDIVS.IndivId	The name/identifier for the Kid in this population.
Kid_Sname	BIOGRAPH.Sname	The Sname of the Kid, if the Kid has one.
Mom_UIId	MOMS_CONSENSUS.Mom_Consensus	The unique identifier for the Mom.
Mom_PopId	UNIQUE_INDIVS.PopId	The identifier for the Mom's population.
Mom_IndivId	UNIQUE_INDIVS.IndivId	The name/identifier for the Mom in this population.
Mom_Sname	BIOGRAPH.Sname	The Sname of the Mom, if the Mom has one.
Mismatch	MOMS_CONSENSUS.Mismatch	The category of genetic mismatching (or lack thereof) that occurs in this maternity assignment.
Completeness	MOMS_CONSENSUS.Completeness	The apparent completeness of this maternity assignment.
Notes	MOMS_CONSENSUS.Notes	Miscellaneous comments about this maternity assignment, or lack thereof.

Operations Allowed

In all operations, the identifying information (UIId, PopId, IndivId, and Sname) provided for each individual (Kid or Mom) must uniquely identify a single individual.

INSERT

Inserting a row into MOMS inserts a row into MOMS_CONSENSUS, as expected.

UPDATE

Updating a row in MOMS updates the related MOMS_CONSENSUS row, as expected.

DELETE

Deleting a row from MOMS deletes the related MOMS_CONSENSUS row, as expected.

MOMS_ANALYSES_EXT (MOMS_ANALYSES, EXTended)

Contains one row for every row in MOMS_ANALYSES. In addition to the columns from MOMS_ANALYSES, this view also includes columns from UNIQUE_INDIVS and BIOGRAPH to clarify the identity of the kid and putative mother.

Definition

Figure 6.21. Query Defining the MOMS_ANALYSES_EXT View

SELECT moms_analyses.maid AS maid , moms_analyses.kid AS kid_uiid , kid_ui.popid AS kid_popid , kid_ui.individ AS kid_individ , kid.sname AS kid_sname , moms_analyses.date AS date , moms_analyses.putative_mom AS putative_mom_uiid , mom_ui.popid AS putative_mom_popid , mom_ui.individ AS putative_mom_individ , mom.sname AS putative_mom_sname , moms_analyses.comments AS comments FROM moms_analyses JOIN unique_indivs AS kid_ui ON kid_ui.uiid = moms_analyses.kid LEFT JOIN biograph AS kid ON kid.bioid::TEXT = kid_ui.individ AND kid_ui.popid = 1 LEFT JOIN unique_indivs AS mom_ui ON mom_ui.uiid = moms_analyses.putative_mom LEFT JOIN biograph AS mom ON mom.bioid::TEXT = mom_ui.individ AND mom_ui.popid = 1;

Figure 6.22. Entity Relationship Diagram of the MOMS_ANALYSES_EXT View

If we could we would display here a diagram showing how the MOMS_ANALYSES_EXT view is constructed.

Table 6.11. Columns in the MOMS_ANALYSES_EXT View

Column	From	Description
MAId	MOMS_ANALYSES.MAId	The unique identifier for this analysis.
Kid_UIId	MOMS_ANALYSES.Kid	The unique identifier for the Kid.
Kid_PopId	UNIQUE_INDIVS.PopId	The identifier for the Kid's population.
Kid_IndivId	UNIQUE_INDIVS.IndivId	The name/identifier for the Kid in this population.
Kid_Sname	BIOGRAPH.Sname	The Sname of the Kid, if the Kid has one.
Date	MOMS_ANALYSES.Date	The date of this analysis.
Putative_Mom_UIId	MOMS_ANALYSES.Putative_Mom	The unique identifier for the Putative_Mom.
Putative_Mom_PopId	UNIQUE_INDIVS.PopId	The identifier for the Putative_Mom's population.
Putative_Mom_IndivId	UNIQUE_INDIVS.IndivId	The name/identifier for the Putative_Mom in this population.
Putative_Mom_Sname	BIOGRAPH.Sname	The Sname of the Putative_Mom, if she has one.
Comments	MOMS_ANALYSES.Comments	Miscellaneous notes regarding the analysis.

Operations Allowed

In all operations, the identifying information (UIId, PopId, IndivId, and Sname) provided for each individual (Kid or Putative_Mom) must uniquely identify a single individual.

INSERT

Inserting a row into MOMS_ANALYSES_EXT inserts a row into MOMS_ANALYSES, as expected.

UPDATE

Updating a row in MOMS_ANALYSES_EXT updates the related MOMS_ANALYSES row, as expected.

DELETE

Deleting a row from MOMS_ANALYSES_EXT deletes the related MOMS_ANALYSES row, as expected.

PARENTS

This view shows every individual's known parents. It contains one row for every individual who has a known mother in MATERNITIES and/or MOMS_CONSENSUS, or who has a known father in DADS_CONSENSUS.

Note

A row in this view can have a NULL Mom or a NULL Dad, but not both. When there is neither a Mom (i.e., the offspring does not appear as a Child in MATERNITIES and has either a NULL MOMS_CONSENSUS.Mom_Consensus or no related MOMS_CONSENSUS data row at all) nor a Dad (i.e., the offspring has either a NULL DADS_CONSENSUS.Dad_Consensus or no related DADS_CONSENSUS data row at all), the view has no row for that individual.

This view includes parents and offspring that are unknown"placeholder" individuals, as described earlier. Because of this, each individual (Kid, Mom, Dad) has four columns to identify it: its UIId, PopId, IndivId, and its Sname (if any). This proliferation of ID columns is unfortunate but necessary.

Tip

If a simple set of "Kid, Mom, Dad" columns is desired, consider using COALESCE(), e.g. "SELECT COALESCE(kid_sname, kid_individ) AS kid, ...".

Definition

Figure 6.23. Query Defining the PARENTS View

WITH cycling_moms AS (SELECT kid_ui.uiid AS kid_uiid , kid_ui.popid AS kid_popid , kid_ui.individ AS kid_individ , maternities.child AS kid_sname , mom_ui.uiid AS mom_uiid , mom_ui.popid AS mom_popid , mom_ui.individ AS mom_individ , mom.sname AS mom_sname , maternities.zdate AS zdate , maternities.zdate_grp AS momgrp FROM maternities JOIN unique_indivs AS kid_ui ON kid_ui.individ = maternities.child_bioid::TEXT AND kid_ui.popid = 1 JOIN biograph AS mom ON mom.sname = maternities.mom JOIN unique_indivs AS mom_ui ON mom_ui.individ = mom.bioid::TEXT AND mom_ui.popid = 1) , all_moms AS (SELECT DISTINCT ON (kid_uiid, mom_uiid) kid_uiid , kid_popid , kid_individ , kid_sname , mom_uiid , mom_popid , mom_individ , mom_sname , zdate , momgrp FROM (SELECT * FROM cycling_moms UNION ALL SELECT kid_uiid AS kid_uiid , kid_popid AS kid_popid , kid_individ AS kid_individ , kid_sname AS kid_sname , mom_uiid AS mom_uiid , mom_popid AS mom_popid , mom_individ AS mom_individ , mom_sname AS mom_sname , NULL::date AS zdate , NULL::numeric AS momgrp FROM moms ) AS combined ORDER BY kid_uiid, mom_uiid, zdate) SELECT COALESCE(all_moms.kid_uiid, dads.kid_uiid) AS kid_uiid , COALESCE(all_moms.kid_popid, dads.kid_popid) AS kid_popid , COALESCE(all_moms.kid_individ, dads.kid_individ) AS kid_individ , COALESCE(all_moms.kid_sname, dads.kid_sname) AS kid_sname , all_moms.mom_uiid AS mom_uiid , all_moms.mom_popid AS mom_popid , all_moms.mom_individ AS mom_individ , all_moms.mom_sname AS mom_sname , dads.dad_uiid AS dad_uiid , dads.dad_popid AS dad_popid , dads.dad_individ AS dad_individ , dads.dad_sname AS dad_sname , all_moms.zdate AS zdate , all_moms.momgrp AS momgrp , members.grp AS dadgrp FROM all_moms FULL OUTER JOIN dads ON dads.kid_uiid = all_moms.kid_uiid LEFT JOIN members ON members.sname = dads.dad_sname AND members.date = all_moms.zdate WHERE all_moms.mom_uiid IS NOT NULL OR dads.dad_uiid IS NOT NULL;

Figure 6.24. Entity Relationship Diagram of the PARENTS View, showing how moms are gathered

If we could we would display here a diagram showing how moms in the PARENTS view are gathered.

Figure 6.25. Entity Relationship Diagram of the PARENTS View

If we could we would display here the main diagram showing how the PARENTS view is constructed.

Table 6.12. Columns in the PARENTS View

Column	From	Description
Kid_UIId	UNIQUE_INDIVS.UIId	The unique identifier of the Kid.
Kid_PopId	UNIQUE_INDIVS.PopId	The identifer of the Kid's population.
Kid_IndivId	UNIQUE_INDIVS.IndivId	The name/identifier of the Kid in this population.
Kid_Sname	BIOGRAPH.Sname	The Sname of the Kid, if any.
Mom_UIId	UNIQUE_INDIVS.UIId	The unique identifier of the mother.
Mom_PopId	UNIQUE_INDIVS.PopId	The identifer of the mother's population.
Mom_IndivId	UNIQUE_INDIVS.IndivId	The name/identifier of the mother in this population.
Mom_Sname	BIOGRAPH.Sname	The Sname of the mother, if any.
Dad_UIId	UNIQUE_INDIVS.UIId	The unique identifier of the father.
Dad_PopId	UNIQUE_INDIVS.PopId	The identifer of the father's population.
Dad_IndivId	UNIQUE_INDIVS.IndivId	The name/identifier of the father in this population.
Dad_Sname	BIOGRAPH.Sname	The Sname of the father, if any.
Zdate	CYCPOINTS.Date	Conception date-of-record, or `NULL` if not known.
Momgrp	MEMBERS.Grp	Mother's group as of the conception date-of-record (the Zdate), or `NULL` if not known.
Dadgrp	MEMBERS.Grp	The group of the father on the Zdate, or `NULL` if not known.

Operations Allowed

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

POTENTIAL_DADS (Potential Dads)

Contains one row for every (completed) female reproductive event for every male more than 2192 days old (approximately 6 years) present in the mother's supergroup during her fertile period. So, one row for every potential dad of every birth and fetal loss. The Potential_Dads-Status column can be used to distinguish males that are adult on the Zdate from subadults from males that have no record of testicular enlargement -- the males having no MATUREDATES.Matured.

Definition

Figure 6.26. Query Defining the POTENTIAL_DADS View

SELECT maternities.child_bioid AS bioid , maternities.child AS kid , maternities.mom AS mom , maternities.zdate AS zdate , maternities.zdate_grp AS grp , pdads.sname AS pdad , CASE WHEN rankdates.ranked <= maternities.zdate THEN 'A' WHEN maturedates.matured <= maternities.zdate THEN 'S' ELSE 'O' END AS status , maternities.zdate - pdads.birth AS pdad_age_days , trunc((maternities.zdate - pdads.birth) / 365.25, 1) AS pdad_age_years , (SELECT count(*) FROM members as dadmembers JOIN members AS mommembers ON (mommembers.date = dadmembers.date AND mommembers.supergroup = dadmembers.supergroup) WHERE dadmembers.sname = pdads.sname AND dadmembers.date < maternities.zdate AND dadmembers.date >= maternities.zdate - 5 AND mommembers.sname = maternities.mom AND mommembers.date < maternities.zdate AND mommembers.date >= maternities.zdate - 5) AS estrous_presence , (SELECT count(*) FROM actor_actees WHERE actor_actees.date < maternities.zdate AND actor_actees.date >= maternities.zdate - 5 AND (actor_actees.act = 'M' OR actor_actees.act = 'E') AND actor_actees.actor = pdads.sname AND actor_actees.actee = maternities.mom) AS estrous_me , (SELECT count(*) FROM actor_actees WHERE actor_actees.date < maternities.zdate AND actor_actees.date >= maternities.zdate - 5 AND actor_actees.act = 'C' AND actor_actees.actor = pdads.sname AND actor_actees.actee = maternities.mom) AS estrous_c

FROM maternities JOIN biograph AS pdads ON (pdads.sname IN (SELECT dadmembers.sname FROM members AS dadmembers JOIN members AS mommembers ON (mommembers.date = dadmembers.date AND mommembers.supergroup = dadmembers.supergroup) WHERE dadmembers.sname = pdads.sname AND dadmembers.date < maternities.zdate AND dadmembers.date >= maternities.zdate - 5 AND mommembers.sname = maternities.mom AND mommembers.date < maternities.zdate AND mommembers.date >= maternities.zdate - 5)) LEFT OUTER JOIN rankdates ON (rankdates.sname = pdads.sname) LEFT OUTER JOIN maturedates ON (maturedates.sname = pdads.sname) WHERE pdads.sex = 'M' -- Speed things up by eliminating potential dads -- who could not possibly interpolate into the mom's group -- during the fertile period. AND pdads.statdate >= maternities.zdate - 5 - 14 -- Potential dad must be at least 2192 days old -- (approximately 6 years) on the zdate. AND maternities.zdate - pdads.birth >= 2192 ;

Figure 6.27. Entity Relationship Diagram of the foundation of the POTENTIAL_DADS View

If we could we would display here a diagram showing a portion of how the POTENTIAL_DADS view is constructed.

Figure 6.28. Entity Relationship Diagram of that portion of the POTENTIAL_DADS View which places the mother and potential father in the same group during the fertile period

Figure 6.29. Entity Relationship Diagram of that portion of the POTENTIAL_DADS View having easily computed columns

Figure 6.30. Entity Relationship Diagram of that portion of the POTENTIAL_DADS View involving social interactions

Table 6.13. Columns in the POTENTIAL_DADS View

Column	From	Description
Bioid	BIOGRAPH.Bioid	Numeric identifier (Bioid) of the offspring.
Kid	BIOGRAPH.Sname	Identifier (Sname) of the offspring.
Mom	CYCLES.Sname	Identifier (Sname) of the mother.
Zdate	CYCPOINTS.Date	Conception date-of-record.
Zdate_Grp	MEMBERS.Grp	Mother's group as of the conception date-of-record.
Pdad	BIOGRAPH.Sname	Identifier (Sname) of the potential father.
Status	`CASE WHEN rankdates.ranked <= maternities.zdate THEN 'A' WHEN maturedates.matured <= maternities.zdate THEN 'S' ELSE 'O' END`	The maturity of the potential dad as of the Zdate, as follows: POTENTIAL_DADS.Status values `A` Adult -- RANKDATES.Ranked is on or before the Zdate. `S` Subadult -- Does not meet the criteria for adult but MATUREDATES.Matured is on or before the Zdate. `O` Other -- Does not meet the criteria for Subadult, either because the MATUREDATES.Matured is too late or because there is none.
Pdad_age_days	Zdate - BIOGRAPH.Birth	The age, in days, of the potential dad as of the Zdate -- the Zdate minus the potential dad's BIOGRAPH.Birth.
Pdad_age_years	Pdad_age_days / 365.25	The age, in years, of the potential dad as of the Zdate.
Estrous_presence	Subquery on MEMBERS -- see Figure 6.28.	Count of the number of days the potential dad is in the same supergroup as the supergroup of the mother during the mother's fertile period -- the 5 days prior to the Zdate.
Estrous_me	Subquery on ACTOR_ACTEES -- see Figure 6.30.	Sum of the number of mounts and ejaculation interactions between the Mom and the Pdad during the fertile period -- the 5 days prior to the Zdate.
Estrous_c	Subquery on ACTOR_ACTEES -- see Figure 6.30.	Sum of the number of consortship interactions between the Mom and the Pdad during the fertile period -- the 5 days prior to the Zdate.

Operations Allowed

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

PROPORTIONAL_RANKS (RANKS extended with calculated PROPORTIONAL ranks)

Contains one row for every row in RANKS. Each row contains all the columns from RANKS and an additional column with the calculated proportional rank.

Proportional rank is a method of ranking that accounts for the size of the group^[252]. Its values should extend between 0 (low rank) and 1 (high rank), and can be interpreted as "the percent of the group over which this individual is dominant".

Caution

Be careful when comparing ordinal and proportional rank values to each other. Ordinal ranks (from RANKS) begin at 1 (high rank), with ascending values indicating lower rank. Proportional ranks go in the reverse direction: as proportional rank values ascend, these values indicate higher ranks.

Definition

Figure 6.31. Query Defining the PROPORTIONAL_RANKS View

WITH num_indivs AS ( SELECT ranks.rnkdate , ranks.grp , ranks.rnktype , count(*) AS num_members FROM ranks GROUP BY ranks.rnkdate, ranks.grp, ranks.rnktype) SELECT ranks.rnkid AS rnkid , ranks.sname AS sname , ranks.rnkdate AS rnkdate , ranks.grp AS grp , ranks.rnktype AS rnktype , ranks.rank AS ordrank , ranks.ags_density AS ags_density , ranks.ags_reversals AS ags_reversals , ranks.ags_expected AS ags_expected , CASE WHEN num_indivs.num_members = 1 THEN 1::numeric ELSE 1 - ((ranks.rank - 1)::numeric / (num_indivs.num_members - 1)::numeric) END::numeric(5,4) AS proprank FROM ranks JOIN num_indivs ON (num_indivs.rnkdate = ranks.rnkdate AND num_indivs.grp = ranks.grp AND num_indivs.rnktype = ranks.rnktype);

Figure 6.32. Entity Relationship Diagram of the PROPORTIONAL_RANKS View

If we could we would display here the diagram showing how the PROPORTIONAL_RANKS view is constructed.

Table 6.14. Columns in the PROPORTIONAL_RANKS View

Column	From	Description
RnkId	RANKS.Rnkid	Unique identifier of the RANKS row.
Sname	RANKS.Sname	Sname of the ranked individual.
Rnkdate	RANKS.Rnkdate	The date indicating the year and month of this ranking.
Grp	RANKS.Grp	The group in which this individual is ranked.
Rnktype	RANKS.Rnktype	The kind of rank assigned to this individual.
OrdRank	RANKS.Rank	The ordinal rank assigned to this individual.
PropRank	`CASE WHEN num_indivs.num_members = 1 THEN 1::numeric ELSE 1 - ((ranks.rank - 1)::numeric / (num_indivs.num_members - 1)::numeric) END::numeric(5,4)`	The calculated proportional rank for this individual. Expressed as a value between 0 (low rank) and 1 (high rank), inclusive.

Operations Allowed

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