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.

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.9. 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.10. 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.5. 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.11. 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.12. 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.6. 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.13. 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.14. 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.7. 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.

PARENTS

Contains one row for every BIOGRAPH row for which there is either a row in MATERNITIES with a record of the individual's mother or there is a row in DAD_DATA with a record of the individual's father -- where DAD_DATA.Dad_consensus is not NULL.

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 has a NULL BIOGRAPH.Pid) nor a Dad (i.e., the offspring has either a NULL.DAD_DATA.Dad_consensus or no related DAD_DATA data row for the father at all) the view has no row for the individual.

Definition

Figure 6.15. Query Defining the PARENTS View

SELECT biograph.sname AS kid , maternities.mom AS mom , dad_data.dad_consensus AS dad , maternities.zdate AS zdate , dad_data.dadid AS dadid , maternities.zdate_grp AS momgrp , members.grp AS dadgrp FROM biograph LEFT OUTER JOIN maternities ON (maternities.child = biograph.sname) LEFT OUTER JOIN dad_data ON (dad_data.kid = biograph.sname) LEFT OUTER JOIN members ON (members.sname = dad_data.dad_consensus AND members.date = maternities.zdate) WHERE maternities.mom IS NOT NULL OR dad_data.dad_consensus IS NOT NULL;

Figure 6.16. Entity Relationship Diagram of the PARENTS View

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

Table 6.8. Columns in the PARENTS View

Column	From	Description
Kid	BIOGRAPH.Sname	Identifier (Sname) of the offspring.
Mom	CYCLES.Sname	Identifier (Sname) of the mother, or `NULL` if the mother is not known.
Dad	DAD_DATA.Dad_consensus	Identifier (Sname) of the father -- the manually chosen father-of-choice --, or `NULL` if there is none.
Zdate	CYCPOINTS.Date	Conception date-of-record, or `NULL` if the mother is not known.
Dadid	DAD_DATA.Dadid	Identifier of the DAD_DATA row containing paternity information, or `NULL` if there is no such row.
Momgrp	MEMBERS.Grp	Mother's group as of the conception date-of-record, or `NULL` if the mother is not known.
Dadgrp	MEMBERS.Grp	The group of the father on the Zdate, or `NULL` if there is either no consensus dad or the Zdate is 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.17. 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.18. 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.19. 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.20. Entity Relationship Diagram of that portion of the POTENTIAL_DADS View having easily computed columns

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

Table 6.9. 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.19.	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.21.	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.21.	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.22. 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.23. 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.10. 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.