Table: biography

Comment: One row per individual studied. These individuals are those which are the subject of observation, such as the animals in an animal life history study. At present, animals can be the subject of only one study; in the future this restriction may need to be lifted. Also, birth groups are recorded directly as an attribute, and hence are denormalized, but at present it is unclear which attributes other than a name a birth group would need to have. Access to rows in BIOGRAPHY is controlled, on a per-study basis, by the PERMISSION table. AnimId must be unique per StudyId. AnimName must either be NULL or unique per StudyId. BirthDate must be on or precede EntryDate. BirthDate may be NULL only when MomOnly is TRUE. BDMin must be NULL or on or precede BirthDate. BDMin may be NULL only when MomOnly is TRUE. BDMax must be NULL or on or follow BirthDate. BDMax may be NULL only when MomOnly is TRUE. BDMax may not follow the sum DepartDate plus DepartDateError number of years. BDDist may be NULL only when MomOnly is TRUE. The MomBId value cannot be the Bid value. Sex must be 'F' when MomOnly is TRUE. Entrydate must be on or precede DepartDate. EntryDate may be NULL only when MomOnly is TRUE. EntryDate may be NULL only when MomOnly is TRUE. EntryType can be NULL only when EntryDate is also NULL. DepartDate may be NULL only when MomOnly is TRUE. DepartType can be NULL only when DepartDate is also NULL. DepartDateError must be NULL if DepartDate is NULL. DepartDateError must not be NULL if DepartDate is not NULL. The combination of StudyId and AnimId must be unique. Because neither of these columns may be NULL this combination can, instead of the Bid value, be used as a unique row identifier. The combination of StudyId and AnimName must be unique. Because AnimName may be NULL this combination cannot be used as a unique row identifier. Individuals identified as mothers (rows that have a BId value in the MomBId column of another BIOGRAPHY row) must have a 'F' value for Sex. Mothers and their offspring must have the same StudyId value. These conditions are checked upon transaction commit. TIP: Use the BIOGRAPHIES view to get the mother's AnimId. BIOGRAPHIES is identical to this table but for the additional column.

ColumnTypeComment
bidinteger Auto Increment [nextval('biography_bid_seq')]Unique row identifier, and hence a unique identifier of the individual regardless of study. The value of this column is automatically assigned by the system; the normal practice, which results in a system generated id, is to omit this column when inserting new rows or to supply a NULL value. The value of this column cannot be NULL. The value of this column cannot be changed. TIP: Can be used to JOIN with FERTILITY.BId.
studyidcharacter varying(12)Identifier of the study for which the individual is observed. This value may not be NULL. TIP: Can be used to JOIN with STUDY.Id.
animidcharacter varying(16)The identifier used by the study to denote the individual. This value may not be NULL.
animnamecharacter varying(128) NULLThe (long) name of the individual. This value may be NULL when the individual has no long name.
momonlybooleanWhether or not the biography row records an individual who exists in the database only because they are known to be a mother of another individual in the database. A Boolean value. Individuals who are "only mothers" (MomOnly = TRUE) have different requirements from typical study subjects as to what data must or must not be recorded in the database. This value may not be NULL.
birthdatedate NULLBirth date. Animal's birthdate. The birthdate is either the exactly known date of birth or it is with a range of possible birthdates. The BirthDate must be on or follow 1910-01-01. Caution: The automatic data integrity check for early BirthDates does not provide adequate per-study data integrity.
bdmindate NULLEstimated earliest birth date. Must differ from Birthdate whenever earliest possible birth date is >7 days before Birthdate.
bdmaxdate NULLEstimated latest birth date. Must differ from Birthdate whenever latest possible birth date is >7 days after Birthdate.
bddistcharacter(1) NULLProbability distribution of the estimated birth date given BDMin, Birthdate, and BDMax. The vocabulary for this column is defined by the PROBABILITY_TYPE table, which expected to define only normal (N) and uniform (U).
birthgroupcharacter varying(32) NULLThe name or code or ID of the group within which the individual was born. This value may be NULL to indicate that the group at birth is unknown or the concept is not applicable. Caution: This value is not validated. As with all strings, it is case-sensitive.
bgqualcharacter(1) NULLQuality of the estimate of the group of birth. The degree of certainty about which group this animal was born into. Must be one of 'C' or 'U' for certain or uncertain. This value may be NULL to indicate quality estimate is unknown.
firstborncharacter(1) NULLWhether the individual is the first born, meaning the first offspring from the maternal parent. Values are 'Y', 'N' and 'U', for Yes, No, and Unknown, respectively. This value may be NULL when the study does not track first born.
mombidinteger NULLThe row identifier of the individual's mother. This is a BIOGRAPHY.BId value. This condition is checked on transaction commit. This value may be NULL when the mother is unknown. TIP: The study's code for the mother may be found in the MomId column of the BIOGRAPHIES view.
sexcharacter(1)The gender of the individual. Allowed values are 'M', 'F', and 'U', for male, female, and unknown, respectively. This value may not be NULL.
entrydatedate NULLDate the animal was first seen. Date on which the animal is first sighted in the study population, either because the animal is recognized and ID'd as of that date or because strong inference indicates group membership from that date. Study population is the studied population at the time of the animal's entry into it. EntryDate must be on or follow 1960-01-01. EntryDate must be on or precede today's date. Caution: The automatic data integrity check for early EntryDates does not provide adequate per-study data integrity.
entrytypecharacter varying(8) NULLType of entry into population. Birth, immigration, start of confirmed ID, Initiation of close observation for any other reason, etc. The vocabulary for this column is defined by the START_EVENT table.
departdatedate NULLDate on which the animal was last seen alive in the population. DepartDate must be on or precede today's date.
departtypecharacter varying(8) NULLType of departure. Death, permanent disappearance, emigration out of the study population, end of close observation for any other reason, or end of currently entered data period. Death may be assigned only in cases where the evidence is very strong: body found, or circumstantial evidence indicates poor health or other risks contributing to mortality and/or violations of population-specific behavior patterns. Otherwise assign permanent disappearance. Do not assign mortality based solely on inferred risks associated with age. The vocabulary for this column is defined by the END_EVENT table.
departdateerrordouble precision NULLTime between departdate and the first time that the animal was confirmed missing. Expressed as fraction of a year (number of days divided by 365.25). Assign a zero to DepartdateError only if the number of day between departdate and the first time that the animal was confirmed missing was < 15.

Indexes

PRIMARYbid
INDEXbdmin
INDEXbdmax
INDEXbirthgroup
INDEXmombid
INDEXentrydate
INDEXdepartdate
UNIQUEstudyid, animid
UNIQUEstudyid, animname
INDEXstudyid
INDEXanimid
INDEXanimname
INDEXbirthdate

Foreign keys

SourceTargetON DELETEON UPDATE
studyidstudy(sid)NO ACTION NO ACTION Alter
bddistprobability_type(code)NO ACTION NO ACTION Alter
mombidbiography(bid)NO ACTION NO ACTION Alter
entrytypestart_event(code)NO ACTION NO ACTION Alter
departtypeend_event(code)NO ACTION NO ACTION Alter

Triggers

Language: