The Babase Pocket Reference Guide

A Technical Specification Summary

Jeanne Altmann, PhD.

Susan C. Alberts, PhD.

Leah Gerber

ER Diagram layout and conversion to Dia 

Anne Ndeti Hubbard

DocBook formatting 

Karl O. Pinc

DocBook formatting 

This material is based upon work supported by the National Science Foundation under Grant Nos. 0323553 and 0323596.

March 25, 2009


Table of Contents

Babase Summarized
The Babase ER Diagrams
The Babase Views
The ACTOR_ACTEES View
The ANESTH_STATS View
The BODYTEMP_STATS View
The CENSUS_DEMOG and CENSUS_DEMOG_SORTED Views
The CHEST_STATS View
The CROWNRUMP_STATS View
The CYCLES_SEXSKINS and CYCLES_SEXSKINS_SORTED Views
The CYCPOINTS_CYCLES and CYCPOINTS_CYCLES_SORTED Views
The DSAMPLES View
The DEMOG_CENSUS and DEMOG_CENSUS_SORTED Views
The DENT_CODES View
The DENT_SITES View
The INTERACT and INTERACT_SORTED Views
The MATERNITIES View
The MIN_MAXS View
The MIN_MAXS_SORTED View
The MPI_EVENTS View
The MTD_CYCLES View
The PARENTS View
The PCSKINS_SORTED View
The PCV_STATS View
The POINTS and POINTS_SORTED Views
The POTENTIAL_DADS View
The QUADS View
The SEXSKINS_CYCLES and SEXSKINS_CYCLES_SORTED Views
The SWERB view
The SWERB_DEPARTS view
The SWERB_LOC_GPS_XY view
The SWERB_LOCS view
The SWERB_UPLOAD view
The ULNA_STATS View
The TESTES_ARC_STATS View
The TESTES_DIAM_STATS View
Views Which Add Gid To Tables
The BIRTH_GRP View
The ENTRYDATE_GRP View
The STATDATE_GRP View
The CONSORTDATES_GRP View
The CYCGAPDAYS_GRP View
The CYCGAPS_GRP View
The CYCSTATS_GRP View
The DARTINGS_GRP View
The DISPERSEDATES_GRP View
The MATUREDATES_GRP View
The MDINTERVALS_GRP View
The MMINTERVALS_GRP View
The PCSKINS_GRP View
The RANKDATES_GRP View
The REPSTATS_GRP View

List of Figures

1. Key to the Babase Entity Relationship Diagrams
2. Babase Group Membership Entity Relationship Diagram
3. Babase Life Events Entity Relationship Diagram
4. Babase Sexual Cycle Entity Relationship Diagram
5. Babase Sexual Cycle Day-To-Day Tables Entity Relationship Diagram
6. Babase Social Interactions Entity Relationship Diagram
7. Babase Multiparty Interactions Entity Relationship Diagram
8. Babase Darting Logistics and Morphology Entity and Relationship Diagram
9. Babase Darting Physiology Entity and Relationship Diagram
10. Babase Darting Samples Entity and Relationship Diagram
11. Babase Darting Teeth and Ticks Entity and Relationship Diagram
12. Babase Hybrid Scores Data Entity Relationship Diagram
13. Babase SWERB Core Tables Entity Relationship Diagram
14. Babase SWERB Grove/Waterhole Location Tables Entity Relationship Diagram
15. Babase Manual Weather Data Entity Relationship Diagram
16. Babase WeatherHawk Data Entity Relationship Diagram
17. Warning Sub-System Entity Relationship Diagram
18. Query Defining the ACTOR_ACTEES View
19. Entity Relationship Diagram of the ACTOR_ACTEES View
20. Query Defining the ANESTH_STATS View
21. Entity Relationship Diagram of the ANESTH_STATS View
22. Query Defining the BODYTEMP_STATS View
23. Entity Relationship Diagram of the BODYTEMP_STATS View
24. Query Defining the CENSUS_DEMOG View
25. Entity Relationship Diagram of the CENSUS_DEMOG View
26. Query Defining the CHEST_STATS View
27. Entity Relationship Diagram of the CHEST_STATS View
28. Query Defining the CROWNRUMP_STATS View
29. Entity Relationship Diagram of the CROWNRUMP_STATS View
30. Query Defining the CYCLES_SEXSKINS View
31. Entity Relationship Diagram of the CYCLES_SEXSKINS View
32. Query Defining the CYCPOINTS_CYCLES View
33. Entity Relationship Diagram of the CYCPOINTS_CYCLES View
34. Query Defining the DSAMPLES View
35. Query Defining the DEMOG_CENSUS View
36. Entity Relationship Diagram of the DEMOG_CENSUS View
37. Query Defining the DENT_CODES View
38. Entity Relationship Diagram of the DENT_CODES View
39. Query Defining the DENT_SITES View
40. Entity Relationship Diagram of the DENT_SITES View
41. Query Defining the INTERACT View
42. Entity Relationship Diagram of the INTERACT View
43. Query Defining the MATERNITIES View
44. Entity Relationship Diagram of the MATERNITIES View
45. Query Defining the MIN_MAXS View
46. Entity Relationship Diagram of the MIN_MAXS View
47. Query Defining the MIN_MAXS_SORTED View
48. Entity Relationship Diagram of the MIN_MAXS_SORTED View
49. Query Defining the MPI_EVENTS View
50. Entity Relationship Diagram of the MPI_EVENTS View
51. Query Defining the MTD_CYCLES View
52. Entity Relationship Diagram of the MTD_CYCLES View
53. Query Defining the PARENTS View
54. Entity Relationship Diagram of the PARENTS View
55. Query Defining the PCSKINS_SORTED View
56. Entity Relationship Diagram of the PCSKINS_SORTED View
57. Query Defining the PCV_STATS View
58. Entity Relationship Diagram of the PCV_STATS View
59. Query Defining the POINTS View
60. Entity Relationship Diagram of the POINTS View
61. Query Defining the POTENTIAL_DADS View
62. Entity Relationship Diagram of the foundation of the POTENTIAL_DADS View
63. 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
64. Entity Relationship Diagram of that portion of the POTENTIAL_DADS View having easily computed columns
65. Entity Relationship Diagram of that portion of the POTENTIAL_DADS View involving social interactions
66. Query Defining the QUADS View
67. Entity Relationship Diagram of the QUADS View
68. Query Defining the SEXSKINS_CYCLES View
69. Entity Relationship Diagram of the SEXSKINS_CYCLES View
70. Query Defining the SWERB View
71. Entity Relationship Diagram of the SWERB View
72. Query Defining the SWERB_DEPARTS View
73. Entity Relationship Diagram of the SWERB_DEPARTS View
74. Query Defining the SWERB_LOC_GPS_XY View
75. Entity Relationship Diagram of the SWERB_LOC_GPS_XY View
76. Query Defining the SWERB_LOCS View
77. Entity Relationship Diagram of the SWERB_LOCS View
78. Query Defining the SWERB_UPLOAD View
79. Entity Relationship Diagram of the SWERB_UPLOAD View
80. Query Defining the ULNA_STATS View
81. Entity Relationship Diagram of the ULNA_STATS View
82. Query Defining the TESTES_ARC_STATS View
83. Entity Relationship Diagram of the TESTES_ARC_STATS View
84. Query Defining the TESTES_DIAM_STATS View
85. Entity Relationship Diagram of the TESTES_DIAM_STATS View
86. Query Defining the BIRTH_GRP View
87. Entity Relationship Diagram of the BIRTH_GRP View
88. Query Defining the ENTRYDATE_GRP View
89. Entity Relationship Diagram of the ENTRYDATE_GRP View
90. Query Defining the STATDATE_GRP View
91. Entity Relationship Diagram of the STATDATE_GRP View
92. Query Defining the CONSORTDATES_GRP View
93. Entity Relationship Diagram of the CONSORTDATES_GRP View
94. Query Defining the CYCGAPDAYS_GRP View
95. Entity Relationship Diagram of the CYCGAPDAYS_GRP View
96. Query Defining the CYCGAPS_GRP View
97. Entity Relationship Diagram of the CYCGAPS_GRP View
98. Query Defining the CYCSTATS_GRP View
99. Entity Relationship Diagram of the CYCSTATS_GRP View
100. Query Defining the DARTINGS_GRP View
101. Entity Relationship Diagram of the DARTINGS_GRP View
102. Query Defining the DISPERSEDATES_GRP View
103. Entity Relationship Diagram of the DISPERSEDATES_GRP View
104. Query Defining the MATUREDATES_GRP View
105. Entity Relationship Diagram of the MATUREDATES_GRP View
106. Query Defining the MDINTERVALS_GRP View
107. Entity Relationship Diagram of the MDINTERVALS_GRP View
108. Query Defining the MMINTERVALS_GRP View
109. Entity Relationship Diagram of the MMINTERVALS_GRP View
110. Query Defining the PCSKINS_GRP View
111. Entity Relationship Diagram of the PCSKINS_GRP View
112. Query Defining the RANKDATES_GRP View
113. Entity Relationship Diagram of the RANKDATES_GRP View
114. Query Defining the REPSTATS_GRP View
115. Entity Relationship Diagram of the REPSTATS_GRP View

List of Tables

1. The Main Babase Tables
2. The Warning Sub-System Tables
3. The Babase Support Tables
4. The Warning Sub-System Support Tables
5. The Babase Views
6. The table_GRP Views

Babase Summarized

Warning

Tables which have names ending in _DATA should not be used, there is always a view of the data in these tables that may be used in their place. Tables ending in _DATA may change in future Babase minor releases, breaking queries and programs which use the table. Use of the corresponding views will ensure compatibility with future Babase releases.

Table 1. The Main Babase Tables

Group Membership and Life Events
TableOne row for each
ALTERNATE_SNAMESrescinded sname
BIOGRAPHanimal, including fetuses
CENSUSday each individual is (or is not) observed in a group
CONSORTDATESmale who has a known first consortship
DEMOGmention of an individual's presence in a group within a field textual note
DISPERSEDATESmale who has left his maternal study group
GROUPSgroup (including solitary males)
MATUREDATESindividual who is sexually mature
RANKDATESindividual[a] who has attained adult rank
 
Analyzed: Group Membership and Life Events
TableOne row for each
DAD_DATAoffspring having a paternity analysis
MEMBERSday each individual is alive
RANKSmonth each individual is ranked in each group
 
Sexual Cycles
TableOne row for each
CYCGAPSfemale for each initiation or cessation of a continuous period of observation
CYCLESfemale's cycle (complete or not)
CYCPOINTSMdate (menses), Tdate (turgesence onset), or Ddate (deturgesence onset) date of each female
PCSKINSPCS color of each female
PREGStime a female becomes pregnant
SEXSKINSsexskin measurement of each female
 
The Sexual Cycle Day-By-Day Tables
TableOne row for each
CYCGAPDAYSfemale for each day within a period during which there is not continuous observation
CYCSTATSday each female is cycling -- by M, T and Ddates
MDINTERVALSday each female is cycling and is between M and Ddates
MMINTERVALSday each female is cycling -- by Mdates
REPSTATSday each female has a known reproductive state
 
Social and Multiparty Interactions
TableOne row for each
ALLMISCSfree form all-occurrences datum
CONSORTSmultiparty dispute over a consortship
FPOINTSpoint observation of a mature female
INTERACT_DATAinteraction between individuals
MPIScollection of multiparty interactions
MPI_DATAsingle dyadic interaction of a multiparty interaction collection
MPI_PARTSparticipant in a dyadic interaction of a multiparty interaction collection
PARTSparticipant in each interaction
POINT_DATAindividual point observation
NEIGHBORSneighbor recorded in each point sample
SAMPLESall-occurrences sample
 
Darting
TableOne row for each
ANESTHStime additional sedation is administered to a darted individual
BODYTEMPSbody temperature measurement taken of a darted individual
CHESTSchest circumference measurement made of a darted individual
CROWNRUMPScrown to rump measurement made of a darted individual
DART_SAMPLESsample type collected at each darting
DARTINGSdarting of an animal when data was collected
DPHYSdarting event during which physiological measurements were taken
HUMERUSEShumerous length measurement made of a darted individual
PCVSpacked cell volume measurement taken from a darted individual
TEETHpossible tooth site within the mouth on which data was collected for every darting event during which dentition data was collected
TESTES_ARCevery testicle width/length measurement recorded, as measured along a portion of the circumference
TESTES_DIAMevery testicle width/length measurement recorded, as measured along the diameter
TICKSdarting event during which data on ticks and other parasites were recorded
ULNASulna length measurement made of a darted individual
 
Analyzed: Darting
TableOne row for each
WBC_COUNTScount from a blood smear collected during a darting
 
SWERB Data (Group-level Geolocation Data)
TableOne row for each
AERIALSaerial photo used for map quadrant specification
GPS_UNITSGPS device
QUAD_DATASWERB map quadrant
SWERB_BESuninterrupted bout of group-level observation
SWERB_DATAevent related to group-level geolocation
SWERB_DEPARTS_DATAdeparture from camp of a observation team which collected SWERB data
SWERB_GWSgeolocated physical object (grove or waterhole)
SWERB_GW_LOC_DATArecorded location of a geolocated physical object (grove or waterhole)
SWERB_LOC_DATAobservation of a group at a time at a geolocated physical object
SWERB_LOC_GPSobservation of a group at a time at a geolocated physical object made using gps units and a protocol that requires 2 waypoint readings
SWERB_OBSERVERSdeparture from camp of an observer who drove or collected SWERB data
 
Weather Data
TableOne row for each
RAINGAUGESrain gauge reading
RGSETUPSrain gauge installation
TEMPMAXSmaximum temperature reading
TEMPMINSminimum temperature reading
WEATHERHAWKweather reading reported by the WeatherHawk instruments
WREADINGSmanually collected meteorological data collection event
 

[a] At this time of this writing only males have data entered into RANKDATES.


Table 2. The Warning Sub-System Tables

TableOne row for each
INTEGRITY_QUERIESquery used to discover data integrity problems
INTEGRITY_WARNINGSdata integrity problem discovered by the warning sub-system


Table 3. The Babase Support Tables

General Support Tables
TableId ColumnRelated Column(s) One entry for every possible choice of...
OBSERVERSInitialsSAMPLES.Observer, WREADINGS.WRperson, RGSETUPS.RGSPerson, CROWNRUMPS.CRobserver, CHESTS.Chobserver, ULNAS.Ulobserver, HUMERUSES.Huobserver, SWERB_OBSERVERS.Observerperson who records information
OBSERVER_ROLESInitialsOBSERVERS.Role, OBSERVERS.SWERB_Observer_Role, OBSERVERS.SWERB_Driver_Role, SWERB_OBSERVERS.Roleway in which a person can be involved in the data collection process
UNKSNAMESUnksnameNEIGHBORS.Unksname and the SWERB_UPLOAD viewproblem in identifying neighbor of focal during point sampling or in identifying a lone male in a SWERB other group observation
 
Group Membership and Life Events
TableId ColumnRelated Column(s) One entry for every possible choice of...
BSTATUSESBstatusBIOGRAPH.Bstatusbirthday estimation accuracy
CONFIDENCESConfidenceBIOGRAPH.DcauseNatureConfidence, BIOGRAPH.DcauseAgentConfidence, DISPERSEDATES.Dispconfidence, BIOGRAPH.Matgrpconfidencedegree of certitude in nature of death, agent of death, disperse date assignment, or maternal group assignment
DAD_SOFTWARESoftwareDAD_DATA.Softwaresoftware package used to perform genetic paternity analysis
DCAUSESDcauseBIOGRAPH.Dcausecause of death
DEATHNATURESNatureDCAUSES.Naturereason for death
DEMOG_REFERENCESReferenceDEMOG.Referencedata source for demography notes
MSTATUSESMstatusMATUREDATES.Matured, RANKDATES.Rankedmaturity marker date estimation process
PATERNITY_COMPLETENESSCompletenessDAD_DATA.Completenesscategory of analysis completeness
PATERNITY_MISMATCHESMismatchDAD_DATA.Consensus_Mismatchcategory of genetic mismatch
RNKTYPESRnktypeRANKS.Rnktyperank ordering assigned to subject and month
STATUSESStatusBIOGRAPH.Statusbaboon alive at last observation
 
Social and Multiparty Interactions
TableId ColumnRelated Column(s) One entry for every possible choice of...
ACTIVITIESActivityPOINT_DATA.Activityactivity classification
ACTSActINTERACT_DATA.Actinteraction classification
DATA_STRUCTURESData_StructureSETUPIDS.Data_Structureversion of data structure produced by the palmtops which collect data
CONTEXT_TYPESContext_typeMPIS.Context_typecontext in which a multiparty interaction occurs
FOODCODESFoodcodePOINT_DATA.Foodcodename of a food item
FOODTYPESFtypeFOODCODES.Ftypefood category
KIDCONTACTSKidcontactFPOINTS.Kidcontactspatial relationship between mother and infant
MPIACTSMpiactMPI_DATA.MPIActmultiparty interaction classification
NCODESNcodeNEIGHBORS.Ncodeneighbor classification
PALMTOPSPalmtopSAMPLES.Palmtophand-held computer used in the field
PARTUNKSUnksnameMPI_PARTS.Unksnameproblem in identifying participant in a multiparty interaction
POSTURESPosturePOINT_DATA.Posturedesignated posture
PROGRAMIDSProgramidSAMPLES.Programidversion of program used on the palmtops to collect data
SETUPIDSSetupidSAMPLES.Setupidsetupfile used on the palmtops to collect data
SUCKLESSuckleFPOINTS.Kidsuckleinfant suckling activity
 
Sexual Cycles and The Sexual Cycle Day-By-Day Tables
TableId ColumnRelated Column(s) One entry for every possible choice of...
PCSCOLORSColorPCSKINS.Colorparacallosal skin coloration
 
Darting
TableId ColumnRelated Column(s) One entry for every possible choice of...
BODYPARTSBodypartTICKS.Bodypart, BODYPARTS.Bodyregionpart of the body examined for parasites when darting
DART_SAMPLE_CATSDs_catDART_SAMPLE_CATS.DS_Catcategory of darting sample type
DART_SAMPLE_TYPESDS_TypeDART_SAMPLE_TYPES.DS_Typetype of sample collected during dartings
DRUGSDrugDRUGS.Druganesthetic drug
LYMPHSTATESLymphstateDPHYS.Ringnode, DPHYS.Lingnode, DPHYS.Raxnode, DPHYS.Laxnode, DPHYS.Lsubmandnode, DPHYS.Rsubmandnodelymph node condition
PARASITESPARASITETICKS.Tickkindparasite species, species developmental stage, or kind of parasite sign counted
TCONDITIONSTconditionTEETH.Tconditionphysical condition of a tooth
TICKSTATUSESTickstatusTICKS.Tickstatusparasite count outcome category
TOOTHCODESToothTEETH.Toothadult or deciduous tooth
TOOTHSITESToothsiteTOOTHCODES.Toothsitedental site within the mouth
TSTATESTstateTEETH.Tstatetooth presence
 
SWERB Data (Group-level Geolocation Data)
TableId ColumnRelated Column(s) One entry for every possible ...
ADCODESADCodeSWERB_LOC_DATA.ADcoderelationship between baboon groups and sleeping groves.
SWERB_LOC_CONFS (SWERB sleeping grove Confidences)ConfSWERB_LOC_DATA.Conflevel of confidence in sleeping grove on record.
SWERB_TIME_SOURCESSourceSWERB_BES.Bsource, SWERB_BES.Esourcedata source used to estimate beginning and ending of observation bouts
SWERB_XYSOURCES (SWERB Time Sources)SourceSWERB_GW_LOC_DATA.XYSource data source used to obtain XY coordinates
 
Weather Data
TableId ColumnRelated Column(s) One entry for every possible choice of...
WEATHERHAWK_SOFTWARESWSoftwareWEATHERHAWK.WSoftwaresoftware used to retrieve data from a WeatherHawk instrument
WSTATIONSWstationWREADINGS.Wstationmeteorological data collection location or device

Table 4. The Warning Sub-System Support Tables

TableId Column Related Column(s) One entry for every possible choice of...
IQTYPESIQTypeINTEGRITY_QUERIES.Typekind of problem with data integrity
WARNING_REMARKSWRIDINTEGRITY_WARNINGS.Categoryremark which might apply to more than one instance of questionable database integrity


Table 5. The Babase Views

Group Membership and Life Events
ViewOne row for eachPurposeTables/Views used
CENSUS_DEMOGCENSUS rowMaintenance of CENSUS rows that are extended with DEMOG information.CENSUS, DEMOG
CENSUS_DEMOG_SORTEDCENSUS rowMaintenance of CENSUS_DEMOG rows in a pre-sorted fashion.CENSUS, DEMOG
CYCPOINTS_CYCLESCYCPOINTS rowMaintenance of CYCPOINTS rows that are extended with CYCLES information.CYCLES, CYCPOINTS
CYCPOINTS_CYCLES_SORTEDCYCPOINTS rowThe CYCPOINTS_CYCLES view sorted by CYCLES.Sname, by CYCPOINTS.Date.CYCLES, CYCPOINTS
DEMOG_CENSUSDEMOG rowMaintenance of DEMOG rows.CENSUS, DEMOG
DEMOG_CENSUS_SORTEDCENSUS rowMaintenance of DEMOG_CENSUS rows in a pre-sorted fashion.CENSUS, DEMOG
GROUPS_HISTORYGROUPS rowDepiction of GROUPS rows in a more human-readable format.GROUPS
PARENTSBIOGRAPH 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 -- with a non-NULLDad_consensus.Easy access to parental information.BIOGRAPH, MATERNITIES, DAD_DATA, MEMBERS
POTENTIAL_DADS(completed) female reproductive event for every male more than 2192 days old (approximately 6 years) present in the mother's group during her fertile periodResearch into paternity, especially the selection of potential fathers for further genetic testing.MATERNITIES, MEMBERS (multiple times), ACTOR_ACTEES (multiple times), BIOGRAPH, RANKDATES, MATUREDATES
 
Sexual Cycles
ViewOne row for eachPurposeTables/Views used
CYCLES_SEXSKINSCYCLES rowMaintenance of SEXSKINS rows.CYCLES, SEXSKINS
CYCLES_SEXSKINS_SORTEDCYCLES rowThe CYCLES_SEXSKINS view sorted by CYCLES.Sname, by SEXSKINS.Date.CYCLES, SEXSKINS
MATERNITIESbirth or fetal lossSummarizes (completed) reproductive events.BIOGRAPH, PREGS, CYCPOINTS, CYCLES
MTD_CYCLESCYCLES rowPresents CYCLES together with Mdate, Tdate, and Ddate CYCPOINTS information for a view of an "entire" sexual cycle as a single row.CYCLES, CYCPOINTS
PCSKINS_SORTEDPCSKINS rowSorts PCSKINS by Sname for ease of maintenance.PCSKINS
SEXSKINS_CYCLESSEXSKINS rowMaintenance of SEXSKINS rows.CYCLES, SEXSKINS
SEXSKINS_CYCLES_SORTEDSEXSKINS rowThe SEXSKINS_CYCLES view sorted by CYCLES.Sname, by SEXSKINS.Date.CYCLES, SEXSKINS
 
Social and Multiparty Interactions
ViewOne row for eachPurposeTables/Views used
ACTOR_ACTEESINTERACT rowMaintenance of social interaction data, INTERACT rows and POINTS. A view optimized for highest performance when working with these tables. Analysis of social interaction data.INTERACT, PARTS
INTERACTINTERACT_DATA rowPresents INTERACT_DATA with additional date and time columns that transform the underlying date and time columns in useful and interesting ways.INTERACT_DATA
INTERACT_SORTEDINTERACT_DATA rowPresents the INTERACT view sorted in a fashion expected to ease maintenance.INTERACT_DATA
MPI_EVENTSMPI_DATA rowAnalysis and correction of multiparty interaction data.MPI_DATA, MPI_PARTS, MPIACTS
POINTSPOINT_DATA rowPresents POINT_DATA with the Ptime column transformed into a column that may be useful and interesting.POINT_DATA
POINTS_SORTEDPOINTS rowPresents POINTS sorted by Sid, and within that by Ptime.POINTS
SAMPLES_GOFFSAMPLES rowPresents SAMPLES with an additional column Grp_of_focal, which has the group of the focal at the time of sampling.SAMPLES
 
Darting
ViewOne row for eachPurposeTables/Views used
ANESTH_STATSunique ANESTHS.Dartid value -- for each darting during which additional anesthetic was administeredAnalysis and eyeballing of data involving additional administration of anesthetic when darting.ANESTHS
BODYTEMP_STATSunique BODYTEMPS.Dartid value -- for each darting having body temperature measurementsAnalysis and eyeballing of darting body temperature measurements.BODYTEMPS
CHEST_STATSunique CHESTS.Dartid value -- for each darting having chest circumference measurementsAnalysis and eyeballing of darting chest circumference measurements.CHESTS
CROWNRUMP_STATSunique CROWNRUMPS.Dartid value -- for each darting having crown-to-rump measurementsAnalysis and eyeballing of darting crown-to-rump measurements.CROWNRUMPS
DSAMPLESunique DARTINGS.Dartid value -- for each dartingVisualization of all samples collected per darting.DARTINGS, MEMBERS, DART_SAMPLES
DENT_CODESunique TEETH.Dartid value -- for each darting with recorded tooth informationPerusal and maintenance of TEETH rows by kind of tooth.TEETH
DENT_SITESunique TEETH.Dartid value -- for each darting with recorded tooth informationPerusal of TEETH rows by position in the mouth.TEETH, TOOTHCODES
HUMERUS_STATSunique HUMERUSES.Dartid value -- for each darting having humerus length measurementsAnalysis and eyeballing of darting humerus length measurements.HUMERUSES
PCV_STATSunique PCVS.Dartid value -- for each darting having PCV measurementsAnalysis and eyeballing of darting PCV measurements.PCVS
TESTES_ARC_STATSunique TESTES_ARC.Dartid value -- for each darting having at least one measurement of testes length or width circumferenceAnalysis of testes length and width measurements taken during darting.TESTES_ARC
TESTES_DIAM_STATSunique TESTES_DIAM.Dartid value -- for each darting having at least one measurement of testes length or width diameterAnalysis of testes length and width measurements taken during darting.TESTES_DIAM
ULNA_STATSunique ULNAS.Dartid value -- for each darting having ulna length measurementsAnalysis and eyeballing of darting ulna length measurements.ULNAS
 
SWERB Data (Group-level Geolocation Data)
ViewOne row for eachPurposeTables/Views used
QUADSQUAD_DATA rowQuerying of X, Y coodinates from and maintenance of QUAD_DATA rows.QUAD_DATA
SWERBSWERB_DATA row -- for every SWERB event, departure from camp excludedCollects SWERB related information spread among several tables and separates geolocation points into X and Y coordinates.SWERB_DATA, QUADS, SWERB_BES, SWERB_DEPARTS_DATA, SWERB_DEPARTS_GPS
SWERB_DATA_XYSWERB_DATA row -- for every SWERB event, departure from camp excludedSeparates SWERB_DATA geolocation points into X and Y coordinates for ease of maintenance.SWERB_DATA
SWERB_DEPARTSSWERB_DEPARTS_DATArow -- for every departure from camp of every observation team, for those observation teams which have collected SWERB dataCollects departure related information spread among several tables and separates geolocation points into X and Y coordinates.SWERB_DEPARTS_DATA, SWERB_DEPARTS_GPS
SWERB_GW_LOCSSWERB_GW_LOC_DATA row -- for every geolocation of an object, of a grove or waterholeCollects SWERB grove and waterhole location information spread between tables and separates geolocation points into X and Y coordinates.SWERB_GW_LOC_DATA, QUADS
SWERB_GW_LOC_DATA_XYSWERB_GW_LOC_DATA row -- for every geolocation of an object, of a grove or waterholeSeparates SWERB_GW_LOC_DATA geolocation points into X and Y coordinates for ease of maintenance.SWERB_GW_LOC_DATA
SWERB_LOC_GPS_XYSWERB_LOC_GPS row -- for every time a group is observed at a geolocated physical object, usually a grove or waterhole, and 2 GPS waypoints are required to by the protocol to collect the dataSeparates SWERB_LOC_GPS geolocation points into X and Y coordinates for ease of maintenance.SWERB_LOC_DATA, ADCODES
SWERB_LOCSSWERB_LOC_DATA row -- for every time a group is observed at a geolocated physical object, usually a grove or waterholePresents the relationship between the groups and physical features of the landscape in a more comprehensive manner for simpler querying.SWERB_LOC_DATA, ADCODES
SWERB_UPLOADrow uploaded into SWERBThis view returns no rows, it is used only to upload data into the swerb portion of Babase.SWERB_DEPARTS_DATA, SWERB_DEPARTS_GPS, SWERB_BES, SWERB_DATA, SWERB_LOC_DATA
 
Weather Data
ViewOne row for eachPurposeTables/Views used
MIN_MAXSWREADINGS rowAnalysis and correlation of manually collected weather data.WREADINGS TEMPMINS TEMPMAXS RAINGAUGES
MIN_MAXS_SORTEDWREADINGS rowThe MIN_MAXS view sorted for convienience.WREADINGS TEMPMINS TEMPMAXS RAINGAUGES


The Babase ER Diagrams

Figure 1. Key to the Babase Entity Relationship Diagrams

If we could we would display the diagram key here.


Figure 2. Babase Group Membership Entity Relationship Diagram

If we could we would display a diagram here depicting censusing and group membership.


Figure 3. Babase Life Events Entity Relationship Diagram

If we could we would display here a diagram depicting maturity markers and ranking.


Figure 4. Babase Sexual Cycle Entity Relationship Diagram

If we could we would display a diagram here depicting female sexual cycle information.


Figure 5. Babase Sexual Cycle Day-To-Day Tables Entity Relationship Diagram

If we could we would display a diagram here depicting female sexual cycle day-to-day tables.


Figure 6. Babase Social Interactions Entity Relationship Diagram

If we could we would display a diagram here depicting social interactions and focal point samples.


Figure 7. Babase Multiparty Interactions Entity Relationship Diagram

If we could we would display a diagram here depicting multiparty interactions.


Figure 8. Babase Darting Logistics and Morphology Entity and Relationship Diagram

If we could we would display a diagram here depicting darting logistics and morphology.


Figure 9. Babase Darting Physiology Entity and Relationship Diagram

If we could we would display a diagram here depicting darting logistics and morphology.


Figure 10. Babase Darting Samples Entity and Relationship Diagram

If we could we would display a diagram here depicting darting logistics and morphology.


Figure 11. Babase Darting Teeth and Ticks Entity and Relationship Diagram

If we could we would display a diagram here depicting darting logistics and morphology.


Figure 12. Babase Hybrid Scores Data Entity Relationship Diagram

If we could we would display here a diagram depicting Babase Hybrid Score Data tables.


Figure 13. Babase SWERB Core Tables Entity Relationship Diagram

If we could we would display a diagram here depicting the SWERB core tables.


Figure 14. Babase SWERB Grove/Waterhole Location Tables Entity Relationship Diagram

If we could we would display a diagram here depicting the SWERB Grove/Waterhole Location tables.


Figure 15. Babase Manual Weather Data Entity Relationship Diagram

If we could we would display here a diagram depicting Babase manual weather Data Samples.


Figure 16. Babase WeatherHawk Data Entity Relationship Diagram

If we could we would display here a diagram depicting Babase WeatherHawk Data Samples.


Figure 17. Warning Sub-System Entity Relationship Diagram

If we could we would display a diagram here depicting the tables in the warning sub-system.


The Babase Views

For information on the operations (INSERT, UPDATE, DELETE) allowed by each view and their actions on the underlying tables see The Babase Views of The Babase Reference Manual.

The ACTOR_ACTEES View

Figure 18. Query Defining the ACTOR_ACTEES View


SELECT interact_data.iid AS iid
     , interact_data.sid AS sid
     , interact_data.act AS act
     , interact_data.date AS date
     , interact_data.start AS start
     , interact_data.stop AS stop
     , interact_data.observer AS observer
     , actor.partid AS actorid
     , COALESCE(actor.sname, '998'::CHAR(3)) AS actor
     , (SELECT actorms.grp
          FROM members AS actorms
          WHERE actorms.sname = actor.sname
                AND actorms.date = interact_data.date) AS actor_grp
     , actee.partid AS acteeid
     , COALESCE(actee.sname, '998'::CHAR(3)) AS actee
     , (SELECT acteems.grp
          FROM members AS acteems
          WHERE acteems.sname = actee.sname
                AND acteems.date = interact_data.date) AS actee_grp
     , interact_data.handwritten AS handwritten
  FROM interact_data
       LEFT OUTER JOIN parts AS actor
            ON (actor.iid = interact_data.iid AND actor.role = 'R')
       LEFT OUTER JOIN parts AS actee
            ON (actee.iid = interact_data.iid AND actee.role = 'E')
;


Figure 19. Entity Relationship Diagram of the ACTOR_ACTEES View

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


The ANESTH_STATS View

Figure 20. Query Defining the ANESTH_STATS View


SELECT anesths.dartid AS dartid
     , count(*) AS ansamps
     , avg(anesths.anamount) AS anamount_mean
     , stddev(anesths.anamount) AS anamount_stddev
  FROM anesths
  GROUP BY anesths.dartid
;


Figure 21. Entity Relationship Diagram of the ANESTH_STATS View

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


The BODYTEMP_STATS View

Figure 22. Query Defining the BODYTEMP_STATS View


SELECT bodytemps.dartid AS dartid
     , count(*) AS btsamps
     , avg(bodytemps.btemp) AS btemp_mean
     , stddev(bodytemps.btemp) AS btemp_stddev
  FROM bodytemps
  GROUP BY bodytemps.dartid
;


Figure 23. Entity Relationship Diagram of the BODYTEMP_STATS View

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


The CENSUS_DEMOG and CENSUS_DEMOG_SORTED Views

Figure 24. 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 25. 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.


The CHEST_STATS View

Figure 26. Query Defining the CHEST_STATS View


SELECT chests.dartid AS dartid
     , count(*) AS chsamps
     , avg(chests.chcircum) AS chcircum_mean
     , stddev(chests.chcircum) AS chcircum_stddev
     , avg(chests.chunadjusted) AS chunadjusted_mean
     , stddev(chests.chunadjusted) AS chunadjusted_stddev
  FROM chests
  GROUP BY chests.dartid
;


Figure 27. Entity Relationship Diagram of the CHEST_STATS View

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


The CROWNRUMP_STATS View

Figure 28. Query Defining the CROWNRUMP_STATS View


SELECT crownrumps.dartid AS dartid
     , count(*) AS crsamps
     , avg(crownrumps.crlength) AS crlength_mean
     , stddev(crownrumps.crlength) AS crlength_stddev
  FROM crownrumps
  GROUP BY crownrumps.dartid
;


Figure 29. Entity Relationship Diagram of the CROWNRUMP_STATS View

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


The CYCLES_SEXSKINS and CYCLES_SEXSKINS_SORTED Views

Figure 30. Query Defining the CYCLES_SEXSKINS View


SELECT cycles.cid AS cid
     , cycles.sname AS sname
     , cycles.seq AS seq
     , cycles.series AS series
     , sexskins.sxid AS sxid
     , sexskins.date AS date
     , sexskins.size AS size
  FROM cycles LEFT OUTER JOIN sexskins ON (cycles.cid = sexskins.cid)
;


Figure 31. Entity Relationship Diagram of the CYCLES_SEXSKINS View

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


The CYCPOINTS_CYCLES and CYCPOINTS_CYCLES_SORTED Views

Figure 32. 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 33. 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.


The DSAMPLES View

Figure 34. Query Defining the DSAMPLES View


SELECT dartings.dartid
     , dartings.sname
     , dartings.date
     , members.grp
     , blood_unspecs.num AS bloodunspec
     , blood_paxgenes.num AS bloodpaxgene
     , blood_purpletops.num AS bloodpurpletops
     , blood_separators.num AS bloodseptube
     , blood_cpts.num AS bloodcpt
     , blood_trucultures.num AS bloodtruculture
     , blood_smears.num AS bloodsmear
     , tc_bloods.num AS tcblood
     , hair_unspecs.num AS hairunspec
     , hair_lengths.num AS hairlength
     , hair_cu_zns.num AS haircu_zn
     , teeth_3mouths.num AS mouthphotos3
     , teeth_lmandmolds.num AS lmandmold
     , teeth_lmaxmolds.num AS lmaxillamold
     , teeth_lmol1mol2s.num AS lm1m2siliconemold
     , skin_punchs.num AS skinpunch
     , tc_skins.num AS tcskin
     , vag_swabs.num AS vaginalswab
     , cerv_swabs.num AS cervicalswab
     , fecal_formalin.num AS fecal_formalin
     , vaginal_ph.num AS vaginal_ph
     , palm_swab.num AS palm_swab
     , tongue_swab.num AS tongue_swab
     , tooth_plaque_swab.num as tooth_plaque_swab
     , vagswab_microbiome.num AS vagswab_microbiome
     , glans_penis_swab.num AS glans_penis_swab
     , fecal_microbiome.num AS fecal_microbiome
     , nostrils_swab.num AS nostrils_swab
     , skin_behind_ear_swab.num AS skin_behind_ear_swab
     , skin_inside_elbow_swab.num AS skin_inside_elbow_swab
   FROM dartings
        JOIN members
             ON dartings.sname = members.sname
                AND dartings.date = members.date
        LEFT JOIN dart_samples blood_unspecs
             ON dartings.dartid = blood_unspecs.dartid
                AND blood_unspecs.ds_type = 1
        LEFT JOIN dart_samples blood_paxgenes
             ON dartings.dartid = blood_paxgenes.dartid
                AND blood_paxgenes.ds_type = 2
        LEFT JOIN dart_samples blood_purpletops
             ON dartings.dartid = blood_purpletops.dartid
                AND blood_purpletops.ds_type = 3
        LEFT JOIN dart_samples blood_separators
             ON dartings.dartid = blood_separators.dartid
                AND blood_separators.ds_type = 4
        LEFT JOIN dart_samples blood_cpts
             ON dartings.dartid = blood_cpts.dartid
                AND blood_cpts.ds_type = 5
        LEFT JOIN dart_samples blood_trucultures
             ON dartings.dartid = blood_trucultures.dartid
                AND blood_trucultures.ds_type = 6
        LEFT JOIN dart_samples blood_smears
             ON dartings.dartid = blood_smears.dartid
                AND blood_smears.ds_type = 7
        LEFT JOIN dart_samples hair_unspecs
             ON dartings.dartid = hair_unspecs.dartid
                AND hair_unspecs.ds_type = 8
        LEFT JOIN dart_samples hair_lengths
             ON dartings.dartid = hair_lengths.dartid
                AND hair_lengths.ds_type = 9
        LEFT JOIN dart_samples hair_cu_zns
             ON dartings.dartid = hair_cu_zns.dartid
                AND hair_cu_zns.ds_type = 10
        LEFT JOIN dart_samples teeth_3mouths
             ON dartings.dartid = teeth_3mouths.dartid
                AND teeth_3mouths.ds_type = 11
        LEFT JOIN dart_samples teeth_lmandmolds
             ON dartings.dartid = teeth_lmandmolds.dartid
                AND teeth_lmandmolds.ds_type = 12
        LEFT JOIN dart_samples teeth_lmaxmolds
             ON dartings.dartid = teeth_lmaxmolds.dartid
                AND teeth_lmaxmolds.ds_type = 13
        LEFT JOIN dart_samples teeth_lmol1mol2s
             ON dartings.dartid = teeth_lmol1mol2s.dartid
                AND teeth_lmol1mol2s.ds_type = 14
        LEFT JOIN dart_samples skin_punchs
             ON dartings.dartid = skin_punchs.dartid
                AND skin_punchs.ds_type = 15
        LEFT JOIN dart_samples vag_swabs
             ON dartings.dartid = vag_swabs.dartid
                AND vag_swabs.ds_type = 16
        LEFT JOIN dart_samples cerv_swabs
             ON dartings.dartid = cerv_swabs.dartid
                AND cerv_swabs.ds_type = 17
        LEFT JOIN dart_samples tc_bloods
             ON dartings.dartid = tc_bloods.dartid
                AND tc_bloods.ds_type = 18
        LEFT JOIN dart_samples tc_skins
             ON dartings.dartid = tc_skins.dartid
                AND tc_skins.ds_type = 19
        LEFT JOIN dart_samples fecal_formalin
             ON dartings.dartid = fecal_formalin.dartid
                AND fecal_formalin.ds_type = 20
        LEFT JOIN dart_samples vaginal_ph
             ON dartings.dartid = vaginal_ph.dartid
                AND vaginal_ph.ds_type = 21
        LEFT JOIN dart_samples palm_swab
             ON dartings.dartid = palm_swab.dartid
                AND palm_swab.ds_type = 22
        LEFT JOIN dart_samples tongue_swab
             ON dartings.dartid = tongue_swab.dartid
                AND tongue_swab.ds_type = 23
        LEFT JOIN dart_samples tooth_plaque_swab
             ON dartings.dartid = tooth_plaque_swab.dartid
                AND tooth_plaque_swab.ds_type = 24
        LEFT JOIN dart_samples vagswab_microbiome
             ON dartings.dartid = vagswab_microbiome.dartid
                AND vagswab_microbiome.ds_type = 25
        LEFT JOIN dart_samples glans_penis_swab
             ON dartings.dartid = glans_penis_swab.dartid
                AND glans_penis_swab.ds_type = 26
        LEFT JOIN dart_samples fecal_microbiome
             ON dartings.dartid = fecal_microbiome.dartid
                AND fecal_microbiome.ds_type = 27
        LEFT JOIN dart_samples nostrils_swab
             ON dartings.dartid = nostrils_swab.dartid
                AND nostrils_swab.ds_type = 28
        LEFT JOIN dart_samples skin_behind_ear_swab
             ON dartings.dartid = skin_behind_ear_swab.dartid
                AND skin_behind_ear_swab.ds_type = 29
        LEFT JOIN dart_samples skin_inside_elbow_swab
             ON dartings.dartid = skin_inside_elbow_swab.dartid
                AND skin_inside_elbow_swab.ds_type = 30
;


The DEMOG_CENSUS and DEMOG_CENSUS_SORTED Views

Figure 35. 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 36. 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.


The DENT_CODES View

Figure 37. Query Defining the DENT_CODES View


SELECT teethdartids.dartid AS dartid
     , rum3.rum3tstate AS rum3tstate
     , rum3.rum3tcondition AS rum3tcondition
     , rum2.rum2tstate AS rum2tstate
     , rum2.rum2tcondition AS rum2tcondition
     , rum1.rum1tstate AS rum1tstate
     , rum1.rum1tcondition AS rum1tcondition
     , rup2.rup2tstate AS rup2tstate
     , rup2.rup2tcondition AS rup2tcondition
     , rup1.rup1tstate AS rup1tstate
     , rup1.rup1tcondition AS rup1tcondition
     , ruc.ructstate AS ructstate
     , ruc.ructcondition AS ructcondition
     , rui2.rui2tstate AS rui2tstate
     , rui2.rui2tcondition AS rui2tcondition
     , rui1.rui1tstate AS rui1tstate
     , rui1.rui1tcondition AS rui1tcondition
     , lui1.lui1tstate AS lui1tstate
     , lui1.lui1tcondition AS lui1tcondition
     , lui2.lui2tstate AS lui2tstate
     , lui2.lui2tcondition AS lui2tcondition
     , luc.luctstate AS luctstate
     , luc.luctcondition AS luctcondition
     , lup1.lup1tstate AS lup1tstate
     , lup1.lup1tcondition AS lup1tcondition
     , lup2.lup2tstate AS lup2tstate
     , lup2.lup2tcondition AS lup2tcondition
     , lum1.lum1tstate AS lum1tstate
     , lum1.lum1tcondition AS lum1tcondition
     , lum2.lum2tstate AS lum2tstate
     , lum2.lum2tcondition AS lum2tcondition
     , lum3.lum3tstate AS lum3tstate
     , lum3.lum3tcondition AS lum3tcondition

     , llm3.llm3tstate AS llm3tstate
     , llm3.llm3tcondition AS llm3tcondition
     , llm2.llm2tstate AS llm2tstate
     , llm2.llm2tcondition AS llm2tcondition
     , llm1.llm1tstate AS llm1tstate
     , llm1.llm1tcondition AS llm1tcondition
     , llp2.llp2tstate AS llp2tstate
     , llp2.llp2tcondition AS llp2tcondition
     , llp1.llp1tstate AS llp1tstate
     , llp1.llp1tcondition AS llp1tcondition
     , llc.llctstate AS llctstate
     , llc.llctcondition AS llctcondition
     , lli2.lli2tstate AS lli2tstate
     , lli2.lli2tcondition AS lli2tcondition
     , lli1.lli1tstate AS lli1tstate
     , lli1.lli1tcondition AS lli1tcondition
     , rli1.rli1tstate AS rli1tstate
     , rli1.rli1tcondition AS rli1tcondition
     , rli2.rli2tstate AS rli2tstate
     , rli2.rli2tcondition AS rli2tcondition
     , rlc.rlctstate AS rlctstate
     , rlc.rlctcondition AS rlctcondition
     , rlp1.rlp1tstate AS rlp1tstate
     , rlp1.rlp1tcondition AS rlp1tcondition
     , rlp2.rlp2tstate AS rlp2tstate
     , rlp2.rlp2tcondition AS rlp2tcondition
     , rlm1.rlm1tstate AS rlm1tstate
     , rlm1.rlm1tcondition AS rlm1tcondition
     , rlm2.rlm2tstate AS rlm2tstate
     , rlm2.rlm2tcondition AS rlm2tcondition
     , rlm3.rlm3tstate AS rlm3tstate
     , rlm3.rlm3tcondition AS rlm3tcondition

     , drum2.drum2tstate AS drum2tstate
     , drum2.drum2tcondition AS drum2tcondition
     , drum1.drum1tstate AS drum1tstate
     , drum1.drum1tcondition AS drum1tcondition
     , druc.dructstate AS dructstate
     , druc.dructcondition AS dructcondition
     , drui2.drui2tstate AS drui2tstate
     , drui2.drui2tcondition AS drui2tcondition
     , drui1.drui1tstate AS drui1tstate
     , drui1.drui1tcondition AS drui1tcondition
     , dlui1.dlui1tstate AS dlui1tstate
     , dlui1.dlui1tcondition AS dlui1tcondition
     , dlui2.dlui2tstate AS dlui2tstate
     , dlui2.dlui2tcondition AS dlui2tcondition
     , dluc.dluctstate AS dluctstate
     , dluc.dluctcondition AS dluctcondition
     , dlum1.dlum1tstate AS dlum1tstate
     , dlum1.dlum1tcondition AS dlum1tcondition
     , dlum2.dlum2tstate AS dlum2tstate
     , dlum2.dlum2tcondition AS dlum2tcondition
     , dllm2.dllm2tstate AS dllm2tstate
     , dllm2.dllm2tcondition AS dllm2tcondition
     , dllm1.dllm1tstate AS dllm1tstate
     , dllm1.dllm1tcondition AS dllm1tcondition
     , dllc.dllctstate AS dllctstate
     , dllc.dllctcondition AS dllctcondition
     , dlli2.dlli2tstate AS dlli2tstate
     , dlli2.dlli2tcondition AS dlli2tcondition
     , dlli1.dlli1tstate AS dlli1tstate
     , dlli1.dlli1tcondition AS dlli1tcondition
     , drli1.drli1tstate AS drli1tstate
     , drli1.drli1tcondition AS drli1tcondition
     , drli2.drli2tstate AS drli2tstate
     , drli2.drli2tcondition AS drli2tcondition
     , drlc.drlctstate AS drlctstate
     , drlc.drlctcondition AS drlctcondition
     , drlm1.drlm1tstate AS drlm1tstate
     , drlm1.drlm1tcondition AS drlm1tcondition
     , drlm2.drlm2tstate AS drlm2tstate
     , drlm2.drlm2tcondition AS drlm2tcondition

FROM (SELECT teeth.dartid
        FROM teeth
        GROUP BY teeth.dartid)
       AS teethdartids
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS rum3dartid
             , teeth.tstate AS rum3tstate
             , teeth.tcondition AS rum3tcondition
          FROM teeth
          WHERE teeth.tooth = 'rum3')
         AS rum3
       ON rum3.rum3dartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS rum2dartid
             , teeth.tstate AS rum2tstate
             , teeth.tcondition AS rum2tcondition
          FROM teeth
          WHERE teeth.tooth = 'rum2')
         AS rum2
       ON rum2.rum2dartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS rum1dartid
             , teeth.tstate AS rum1tstate
             , teeth.tcondition AS rum1tcondition
          FROM teeth
          WHERE teeth.tooth = 'rum1')
         AS rum1
       ON rum1.rum1dartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS rup2dartid
             , teeth.tstate AS rup2tstate
             , teeth.tcondition AS rup2tcondition
          FROM teeth
          WHERE teeth.tooth = 'rup2')
         AS rup2
       ON rup2.rup2dartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS rup1dartid
             , teeth.tstate AS rup1tstate
             , teeth.tcondition AS rup1tcondition
          FROM teeth
          WHERE teeth.tooth = 'rup1')
         AS rup1
       ON rup1.rup1dartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS rucdartid
             , teeth.tstate AS ructstate
             , teeth.tcondition AS ructcondition
          FROM teeth
          WHERE teeth.tooth = 'ruc')
         AS ruc
       ON ruc.rucdartid = teethdartids.dartid

     LEFT OUTER JOIN
       (SELECT teeth.dartid AS rui2dartid
             , teeth.tstate AS rui2tstate
             , teeth.tcondition AS rui2tcondition
          FROM teeth
          WHERE teeth.tooth = 'rui2')
         AS rui2
       ON rui2.rui2dartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS rui1dartid
             , teeth.tstate AS rui1tstate
             , teeth.tcondition AS rui1tcondition
          FROM teeth
          WHERE teeth.tooth = 'rui1')
         AS rui1
       ON rui1.rui1dartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS lui1dartid
             , teeth.tstate AS lui1tstate
             , teeth.tcondition AS lui1tcondition
          FROM teeth
          WHERE teeth.tooth = 'lui1')
         AS lui1
       ON lui1.lui1dartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS lui2dartid
             , teeth.tstate AS lui2tstate
             , teeth.tcondition AS lui2tcondition
          FROM teeth
          WHERE teeth.tooth = 'lui2')
         AS lui2
       ON lui2.lui2dartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS lucdartid
             , teeth.tstate AS luctstate
             , teeth.tcondition AS luctcondition
          FROM teeth
          WHERE teeth.tooth = 'luc')
         AS luc
       ON luc.lucdartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS lup1dartid
             , teeth.tstate AS lup1tstate
             , teeth.tcondition AS lup1tcondition
          FROM teeth
          WHERE teeth.tooth = 'lup1')
         AS lup1
       ON lup1.lup1dartid = teethdartids.dartid

     LEFT OUTER JOIN
       (SELECT teeth.dartid AS lup2dartid
             , teeth.tstate AS lup2tstate
             , teeth.tcondition AS lup2tcondition
          FROM teeth
          WHERE teeth.tooth = 'lup2')
         AS lup2
       ON lup2.lup2dartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS lum1dartid
             , teeth.tstate AS lum1tstate
             , teeth.tcondition AS lum1tcondition
          FROM teeth
          WHERE teeth.tooth = 'lum1')
         AS lum1
       ON lum1.lum1dartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS lum2dartid
             , teeth.tstate AS lum2tstate
             , teeth.tcondition AS lum2tcondition
          FROM teeth
          WHERE teeth.tooth = 'lum2')
         AS lum2
       ON lum2.lum2dartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS lum3dartid
             , teeth.tstate AS lum3tstate
             , teeth.tcondition AS lum3tcondition
          FROM teeth
          WHERE teeth.tooth = 'lum3')
         AS lum3
       ON lum3.lum3dartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS llm3dartid
             , teeth.tstate AS llm3tstate
             , teeth.tcondition AS llm3tcondition
          FROM teeth
          WHERE teeth.tooth = 'llm3')
         AS llm3
       ON llm3.llm3dartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS llm2dartid
             , teeth.tstate AS llm2tstate
             , teeth.tcondition AS llm2tcondition
          FROM teeth
          WHERE teeth.tooth = 'llm2')
         AS llm2
       ON llm2.llm2dartid = teethdartids.dartid

     LEFT OUTER JOIN
       (SELECT teeth.dartid AS llm1dartid
             , teeth.tstate AS llm1tstate
             , teeth.tcondition AS llm1tcondition
          FROM teeth
          WHERE teeth.tooth = 'llm1')
         AS llm1
       ON llm1.llm1dartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS llp2dartid
             , teeth.tstate AS llp2tstate
             , teeth.tcondition AS llp2tcondition
          FROM teeth
          WHERE teeth.tooth = 'llp2')
         AS llp2
       ON llp2.llp2dartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS llp1dartid
             , teeth.tstate AS llp1tstate
             , teeth.tcondition AS llp1tcondition
          FROM teeth
          WHERE teeth.tooth = 'llp1')
         AS llp1
       ON llp1.llp1dartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS llcdartid
             , teeth.tstate AS llctstate
             , teeth.tcondition AS llctcondition
          FROM teeth
          WHERE teeth.tooth = 'llc')
         AS llc
       ON llc.llcdartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS lli2dartid
             , teeth.tstate AS lli2tstate
             , teeth.tcondition AS lli2tcondition
          FROM teeth
          WHERE teeth.tooth = 'lli2')
         AS lli2
       ON lli2.lli2dartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS lli1dartid
             , teeth.tstate AS lli1tstate
             , teeth.tcondition AS lli1tcondition
          FROM teeth
          WHERE teeth.tooth = 'lli1')
         AS lli1
       ON lli1.lli1dartid = teethdartids.dartid

     LEFT OUTER JOIN
       (SELECT teeth.dartid AS rli1dartid
             , teeth.tstate AS rli1tstate
             , teeth.tcondition AS rli1tcondition
          FROM teeth
          WHERE teeth.tooth = 'rli1')
         AS rli1
       ON rli1.rli1dartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS rli2dartid
             , teeth.tstate AS rli2tstate
             , teeth.tcondition AS rli2tcondition
          FROM teeth
          WHERE teeth.tooth = 'rli2')
         AS rli2
       ON rli2.rli2dartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS rlcdartid
             , teeth.tstate AS rlctstate
             , teeth.tcondition AS rlctcondition
          FROM teeth
          WHERE teeth.tooth = 'rlc')
         AS rlc
       ON rlc.rlcdartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS rlp1dartid
             , teeth.tstate AS rlp1tstate
             , teeth.tcondition AS rlp1tcondition
          FROM teeth
          WHERE teeth.tooth = 'rlp1')
         AS rlp1
       ON rlp1.rlp1dartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS rlp2dartid
             , teeth.tstate AS rlp2tstate
             , teeth.tcondition AS rlp2tcondition
          FROM teeth
          WHERE teeth.tooth = 'rlp2')
         AS rlp2
       ON rlp2.rlp2dartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS rlm1dartid
             , teeth.tstate AS rlm1tstate
             , teeth.tcondition AS rlm1tcondition
          FROM teeth
          WHERE teeth.tooth = 'rlm1')
         AS rlm1
       ON rlm1.rlm1dartid = teethdartids.dartid

     LEFT OUTER JOIN
       (SELECT teeth.dartid AS rlm2dartid
             , teeth.tstate AS rlm2tstate
             , teeth.tcondition AS rlm2tcondition
          FROM teeth
          WHERE teeth.tooth = 'rlm2')
         AS rlm2
       ON rlm2.rlm2dartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS rlm3dartid
             , teeth.tstate AS rlm3tstate
             , teeth.tcondition AS rlm3tcondition
          FROM teeth
          WHERE teeth.tooth = 'rlm3')
         AS rlm3
       ON rlm3.rlm3dartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS drum2dartid
             , teeth.tstate AS drum2tstate
             , teeth.tcondition AS drum2tcondition
          FROM teeth
          WHERE teeth.tooth = 'drum2')
         AS drum2
       ON drum2.drum2dartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS drum1dartid
             , teeth.tstate AS drum1tstate
             , teeth.tcondition AS drum1tcondition
          FROM teeth
          WHERE teeth.tooth = 'drum1')
         AS drum1
       ON drum1.drum1dartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS drucdartid
             , teeth.tstate AS dructstate
             , teeth.tcondition AS dructcondition
          FROM teeth
          WHERE teeth.tooth = 'druc')
         AS druc
       ON druc.drucdartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS drui2dartid
             , teeth.tstate AS drui2tstate
             , teeth.tcondition AS drui2tcondition
          FROM teeth
          WHERE teeth.tooth = 'drui2')
         AS drui2
       ON drui2.drui2dartid = teethdartids.dartid

     LEFT OUTER JOIN
       (SELECT teeth.dartid AS drui1dartid
             , teeth.tstate AS drui1tstate
             , teeth.tcondition AS drui1tcondition
          FROM teeth
          WHERE teeth.tooth = 'drui1')
         AS drui1
       ON drui1.drui1dartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS dlui1dartid
             , teeth.tstate AS dlui1tstate
             , teeth.tcondition AS dlui1tcondition
          FROM teeth
          WHERE teeth.tooth = 'dlui1')
         AS dlui1
       ON dlui1.dlui1dartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS dlui2dartid
             , teeth.tstate AS dlui2tstate
             , teeth.tcondition AS dlui2tcondition
          FROM teeth
          WHERE teeth.tooth = 'dlui2')
         AS dlui2
       ON dlui2.dlui2dartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS dlucdartid
             , teeth.tstate AS dluctstate
             , teeth.tcondition AS dluctcondition
          FROM teeth
          WHERE teeth.tooth = 'dluc')
         AS dluc
       ON dluc.dlucdartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS dlum1dartid
             , teeth.tstate AS dlum1tstate
             , teeth.tcondition AS dlum1tcondition
          FROM teeth
          WHERE teeth.tooth = 'dlum1')
         AS dlum1
       ON dlum1.dlum1dartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS dlum2dartid
             , teeth.tstate AS dlum2tstate
             , teeth.tcondition AS dlum2tcondition
          FROM teeth
          WHERE teeth.tooth = 'dlum2')
         AS dlum2
       ON dlum2.dlum2dartid = teethdartids.dartid

     LEFT OUTER JOIN
       (SELECT teeth.dartid AS dllm2dartid
             , teeth.tstate AS dllm2tstate
             , teeth.tcondition AS dllm2tcondition
          FROM teeth
          WHERE teeth.tooth = 'dllm2')
         AS dllm2
       ON dllm2.dllm2dartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS dllm1dartid
             , teeth.tstate AS dllm1tstate
             , teeth.tcondition AS dllm1tcondition
          FROM teeth
          WHERE teeth.tooth = 'dllm1')
         AS dllm1
       ON dllm1.dllm1dartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS dllcdartid
             , teeth.tstate AS dllctstate
             , teeth.tcondition AS dllctcondition
          FROM teeth
          WHERE teeth.tooth = 'dllc')
         AS dllc
       ON dllc.dllcdartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS dlli2dartid
             , teeth.tstate AS dlli2tstate
             , teeth.tcondition AS dlli2tcondition
          FROM teeth
          WHERE teeth.tooth = 'dlli2')
         AS dlli2
       ON dlli2.dlli2dartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS dlli1dartid
             , teeth.tstate AS dlli1tstate
             , teeth.tcondition AS dlli1tcondition
          FROM teeth
          WHERE teeth.tooth = 'dlli1')
         AS dlli1
       ON dlli1.dlli1dartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS drli1dartid
             , teeth.tstate AS drli1tstate
             , teeth.tcondition AS drli1tcondition
          FROM teeth
          WHERE teeth.tooth = 'drli1')
         AS drli1
       ON drli1.drli1dartid = teethdartids.dartid

     LEFT OUTER JOIN
       (SELECT teeth.dartid AS drli2dartid
             , teeth.tstate AS drli2tstate
             , teeth.tcondition AS drli2tcondition
          FROM teeth
          WHERE teeth.tooth = 'drli2')
         AS drli2
       ON drli2.drli2dartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS drlcdartid
             , teeth.tstate AS drlctstate
             , teeth.tcondition AS drlctcondition
          FROM teeth
          WHERE teeth.tooth = 'drlc')
         AS drlc
       ON drlc.drlcdartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS drlm1dartid
             , teeth.tstate AS drlm1tstate
             , teeth.tcondition AS drlm1tcondition
          FROM teeth
          WHERE teeth.tooth = 'drlm1')
         AS drlm1
       ON drlm1.drlm1dartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS drlm2dartid
             , teeth.tstate AS drlm2tstate
             , teeth.tcondition AS drlm2tcondition
          FROM teeth
          WHERE teeth.tooth = 'drlm2')
         AS drlm2
       ON drlm2.drlm2dartid = teethdartids.dartid
;


Figure 38. Entity Relationship Diagram of the DENT_CODES View

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


The DENT_SITES View

Figure 39. Query Defining the DENT_SITES View


SELECT teethdartids.dartid AS dartid
     , s1.s1tstate AS s1tstate
     , s1.s1tcondition AS s1tcondition
     , s1.s1deciduous AS s1deciduous
     , s2.s2tstate AS s2tstate
     , s2.s2tcondition AS s2tcondition
     , s2.s2deciduous AS s2deciduous
     , s3.s3tstate AS s3tstate
     , s3.s3tcondition AS s3tcondition
     , s3.s3deciduous AS s3deciduous
     , s4.s4tstate AS s4tstate
     , s4.s4tcondition AS s4tcondition
     , s4.s4deciduous AS s4deciduous
     , s5.s5tstate AS s5tstate
     , s5.s5tcondition AS s5tcondition
     , s5.s5deciduous AS s5deciduous
     , s6.s6tstate AS s6tstate
     , s6.s6tcondition AS s6tcondition
     , s6.s6deciduous AS s6deciduous
     , s7.s7tstate AS s7tstate
     , s7.s7tcondition AS s7tcondition
     , s7.s7deciduous AS s7deciduous
     , s8.s8tstate AS s8tstate
     , s8.s8tcondition AS s8tcondition
     , s8.s8deciduous AS s8deciduous
     , s9.s9tstate AS s9tstate
     , s9.s9tcondition AS s9tcondition
     , s9.s9deciduous AS s9deciduous
     , s10.s10tstate AS s10tstate
     , s10.s10tcondition AS s10tcondition
     , s10.s10deciduous AS s10deciduous
     , s11.s11tstate AS s11tstate
     , s11.s11tcondition AS s11tcondition
     , s11.s11deciduous AS s11deciduous
     , s12.s12tstate AS s12tstate
     , s12.s12tcondition AS s12tcondition
     , s12.s12deciduous AS s12deciduous
     , s13.s13tstate AS s13tstate
     , s13.s13tcondition AS s13tcondition
     , s13.s13deciduous AS s13deciduous
     , s14.s14tstate AS s14tstate
     , s14.s14tcondition AS s14tcondition
     , s14.s14deciduous AS s14deciduous
     , s15.s15tstate AS s15tstate
     , s15.s15tcondition AS s15tcondition
     , s15.s15deciduous AS s15deciduous
     , s16.s16tstate AS s16tstate
     , s16.s16tcondition AS s16tcondition
     , s16.s16deciduous AS s16deciduous

     , s17.s17tstate AS s17tstate
     , s17.s17tcondition AS s17tcondition
     , s17.s17deciduous AS s17deciduous
     , s18.s18tstate AS s18tstate
     , s18.s18tcondition AS s18tcondition
     , s18.s18deciduous AS s18deciduous
     , s19.s19tstate AS s19tstate
     , s19.s19tcondition AS s19tcondition
     , s19.s19deciduous AS s19deciduous
     , s20.s20tstate AS s20tstate
     , s20.s20tcondition AS s20tcondition
     , s20.s20deciduous AS s20deciduous
     , s21.s21tstate AS s21tstate
     , s21.s21tcondition AS s21tcondition
     , s21.s21deciduous AS s21deciduous
     , s22.s22tstate AS s22tstate
     , s22.s22tcondition AS s22tcondition
     , s22.s22deciduous AS s22deciduous
     , s23.s23tstate AS s23tstate
     , s23.s23tcondition AS s23tcondition
     , s23.s23deciduous AS s23deciduous
     , s24.s24tstate AS s24tstate
     , s24.s24tcondition AS s24tcondition
     , s24.s24deciduous AS s24deciduous
     , s25.s25tstate AS s25tstate
     , s25.s25tcondition AS s25tcondition
     , s25.s25deciduous AS s25deciduous
     , s26.s26tstate AS s26tstate
     , s26.s26tcondition AS s26tcondition
     , s26.s26deciduous AS s26deciduous
     , s27.s27tstate AS s27tstate
     , s27.s27tcondition AS s27tcondition
     , s27.s27deciduous AS s27deciduous
     , s28.s28tstate AS s28tstate
     , s28.s28tcondition AS s28tcondition
     , s28.s28deciduous AS s28deciduous
     , s29.s29tstate AS s29tstate
     , s29.s29tcondition AS s29tcondition
     , s29.s29deciduous AS s29deciduous
     , s30.s30tstate AS s30tstate
     , s30.s30tcondition AS s30tcondition
     , s30.s30deciduous AS s30deciduous
     , s31.s31tstate AS s31tstate
     , s31.s31tcondition AS s31tcondition
     , s31.s31deciduous AS s31deciduous
     , s32.s32tstate AS s32tstate
     , s32.s32tcondition AS s32tcondition
     , s32.s32deciduous AS s32deciduous

FROM (SELECT teeth.dartid
        FROM teeth
        GROUP BY teeth.dartid)
       AS teethdartids
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS s1dartid
             , teeth.tstate AS s1tstate
             , teeth.tcondition AS s1tcondition
             , toothcodes.deciduous AS s1deciduous
          FROM toothcodes, teeth
          WHERE toothcodes.toothsite = '1'
                AND teeth.tooth = toothcodes.tooth)
         AS s1
       ON s1.s1dartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS s2dartid
             , teeth.tstate AS s2tstate
             , teeth.tcondition AS s2tcondition
             , toothcodes.deciduous AS s2deciduous
          FROM toothcodes, teeth
          WHERE toothcodes.toothsite = '2'
                AND teeth.tooth = toothcodes.tooth)
         AS s2
       ON s2.s2dartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS s3dartid
             , teeth.tstate AS s3tstate
             , teeth.tcondition AS s3tcondition
             , toothcodes.deciduous AS s3deciduous
          FROM toothcodes, teeth
          WHERE toothcodes.toothsite = '3'
                AND teeth.tooth = toothcodes.tooth)
         AS s3
       ON s3.s3dartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS s4dartid
             , teeth.tstate AS s4tstate
             , teeth.tcondition AS s4tcondition
             , toothcodes.deciduous AS s4deciduous
          FROM toothcodes, teeth
          WHERE toothcodes.toothsite = '4'
                AND teeth.tooth = toothcodes.tooth)
         AS s4
       ON s4.s4dartid = teethdartids.dartid

     LEFT OUTER JOIN
       (SELECT teeth.dartid AS s5dartid
             , teeth.tstate AS s5tstate
             , teeth.tcondition AS s5tcondition
             , toothcodes.deciduous AS s5deciduous
          FROM toothcodes, teeth
          WHERE toothcodes.toothsite = '5'
                AND teeth.tooth = toothcodes.tooth)
         AS s5
       ON s5.s5dartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS s6dartid
             , teeth.tstate AS s6tstate
             , teeth.tcondition AS s6tcondition
             , toothcodes.deciduous AS s6deciduous
          FROM toothcodes, teeth
          WHERE toothcodes.toothsite = '6'
                AND teeth.tooth = toothcodes.tooth)
         AS s6
       ON s6.s6dartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS s7dartid
             , teeth.tstate AS s7tstate
             , teeth.tcondition AS s7tcondition
             , toothcodes.deciduous AS s7deciduous
          FROM toothcodes, teeth
          WHERE toothcodes.toothsite = '7'
                AND teeth.tooth = toothcodes.tooth)
         AS s7
       ON s7.s7dartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS s8dartid
             , teeth.tstate AS s8tstate
             , teeth.tcondition AS s8tcondition
             , toothcodes.deciduous AS s8deciduous
          FROM toothcodes, teeth
          WHERE toothcodes.toothsite = '8'
                AND teeth.tooth = toothcodes.tooth)
         AS s8
       ON s8.s8dartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS s9dartid
             , teeth.tstate AS s9tstate
             , teeth.tcondition AS s9tcondition
             , toothcodes.deciduous AS s9deciduous
          FROM toothcodes, teeth
          WHERE toothcodes.toothsite = '9'
                AND teeth.tooth = toothcodes.tooth)
         AS s9
       ON s9.s9dartid = teethdartids.dartid

     LEFT OUTER JOIN
       (SELECT teeth.dartid AS s10dartid
             , teeth.tstate AS s10tstate
             , teeth.tcondition AS s10tcondition
             , toothcodes.deciduous AS s10deciduous
          FROM toothcodes, teeth
          WHERE toothcodes.toothsite = '10'
                AND teeth.tooth = toothcodes.tooth)
         AS s10
       ON s10.s10dartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS s11dartid
             , teeth.tstate AS s11tstate
             , teeth.tcondition AS s11tcondition
             , toothcodes.deciduous AS s11deciduous
          FROM toothcodes, teeth
          WHERE toothcodes.toothsite = '11'
                AND teeth.tooth = toothcodes.tooth)
         AS s11
       ON s11.s11dartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS s12dartid
             , teeth.tstate AS s12tstate
             , teeth.tcondition AS s12tcondition
             , toothcodes.deciduous AS s12deciduous
          FROM toothcodes, teeth
          WHERE toothcodes.toothsite = '12'
                AND teeth.tooth = toothcodes.tooth)
         AS s12
       ON s12.s12dartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS s13dartid
             , teeth.tstate AS s13tstate
             , teeth.tcondition AS s13tcondition
             , toothcodes.deciduous AS s13deciduous
          FROM toothcodes, teeth
          WHERE toothcodes.toothsite = '13'
                AND teeth.tooth = toothcodes.tooth)
         AS s13
       ON s13.s13dartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS s14dartid
             , teeth.tstate AS s14tstate
             , teeth.tcondition AS s14tcondition
             , toothcodes.deciduous AS s14deciduous
          FROM toothcodes, teeth
          WHERE toothcodes.toothsite = '14'
                AND teeth.tooth = toothcodes.tooth)
         AS s14
       ON s14.s14dartid = teethdartids.dartid

     LEFT OUTER JOIN
       (SELECT teeth.dartid AS s15dartid
             , teeth.tstate AS s15tstate
             , teeth.tcondition AS s15tcondition
             , toothcodes.deciduous AS s15deciduous
          FROM toothcodes, teeth
          WHERE toothcodes.toothsite = '15'
                AND teeth.tooth = toothcodes.tooth)
         AS s15
       ON s15.s15dartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS s16dartid
             , teeth.tstate AS s16tstate
             , teeth.tcondition AS s16tcondition
             , toothcodes.deciduous AS s16deciduous
          FROM toothcodes, teeth
          WHERE toothcodes.toothsite = '16'
                AND teeth.tooth = toothcodes.tooth)
         AS s16
       ON s16.s16dartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS s17dartid
             , teeth.tstate AS s17tstate
             , teeth.tcondition AS s17tcondition
             , toothcodes.deciduous AS s17deciduous
          FROM toothcodes, teeth
          WHERE toothcodes.toothsite = '17'
                AND teeth.tooth = toothcodes.tooth)
         AS s17
       ON s17.s17dartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS s18dartid
             , teeth.tstate AS s18tstate
             , teeth.tcondition AS s18tcondition
             , toothcodes.deciduous AS s18deciduous
          FROM toothcodes, teeth
          WHERE toothcodes.toothsite = '18'
                AND teeth.tooth = toothcodes.tooth)
         AS s18
       ON s18.s18dartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS s19dartid
             , teeth.tstate AS s19tstate
             , teeth.tcondition AS s19tcondition
             , toothcodes.deciduous AS s19deciduous
          FROM toothcodes, teeth
          WHERE toothcodes.toothsite = '19'
                AND teeth.tooth = toothcodes.tooth)
         AS s19
       ON s19.s19dartid = teethdartids.dartid

     LEFT OUTER JOIN
       (SELECT teeth.dartid AS s20dartid
             , teeth.tstate AS s20tstate
             , teeth.tcondition AS s20tcondition
             , toothcodes.deciduous AS s20deciduous
          FROM toothcodes, teeth
          WHERE toothcodes.toothsite = '20'
                AND teeth.tooth = toothcodes.tooth)
         AS s20
       ON s20.s20dartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS s21dartid
             , teeth.tstate AS s21tstate
             , teeth.tcondition AS s21tcondition
             , toothcodes.deciduous AS s21deciduous
          FROM toothcodes, teeth
          WHERE toothcodes.toothsite = '21'
                AND teeth.tooth = toothcodes.tooth)
         AS s21
       ON s21.s21dartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS s22dartid
             , teeth.tstate AS s22tstate
             , teeth.tcondition AS s22tcondition
             , toothcodes.deciduous AS s22deciduous
          FROM toothcodes, teeth
          WHERE toothcodes.toothsite = '22'
                AND teeth.tooth = toothcodes.tooth)
         AS s22
       ON s22.s22dartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS s23dartid
             , teeth.tstate AS s23tstate
             , teeth.tcondition AS s23tcondition
             , toothcodes.deciduous AS s23deciduous
          FROM toothcodes, teeth
          WHERE toothcodes.toothsite = '23'
                AND teeth.tooth = toothcodes.tooth)
         AS s23
       ON s23.s23dartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS s24dartid
             , teeth.tstate AS s24tstate
             , teeth.tcondition AS s24tcondition
             , toothcodes.deciduous AS s24deciduous
          FROM toothcodes, teeth
          WHERE toothcodes.toothsite = '24'
                AND teeth.tooth = toothcodes.tooth)
         AS s24
       ON s24.s24dartid = teethdartids.dartid

     LEFT OUTER JOIN
       (SELECT teeth.dartid AS s25dartid
             , teeth.tstate AS s25tstate
             , teeth.tcondition AS s25tcondition
             , toothcodes.deciduous AS s25deciduous
          FROM toothcodes, teeth
          WHERE toothcodes.toothsite = '25'
                AND teeth.tooth = toothcodes.tooth)
         AS s25
       ON s25.s25dartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS s26dartid
             , teeth.tstate AS s26tstate
             , teeth.tcondition AS s26tcondition
             , toothcodes.deciduous AS s26deciduous
          FROM toothcodes, teeth
          WHERE toothcodes.toothsite = '26'
                AND teeth.tooth = toothcodes.tooth)
         AS s26
       ON s26.s26dartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS s27dartid
             , teeth.tstate AS s27tstate
             , teeth.tcondition AS s27tcondition
             , toothcodes.deciduous AS s27deciduous
          FROM toothcodes, teeth
          WHERE toothcodes.toothsite = '27'
                AND teeth.tooth = toothcodes.tooth)
         AS s27
       ON s27.s27dartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS s28dartid
             , teeth.tstate AS s28tstate
             , teeth.tcondition AS s28tcondition
             , toothcodes.deciduous AS s28deciduous
          FROM toothcodes, teeth
          WHERE toothcodes.toothsite = '28'
                AND teeth.tooth = toothcodes.tooth)
         AS s28
       ON s28.s28dartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS s29dartid
             , teeth.tstate AS s29tstate
             , teeth.tcondition AS s29tcondition
             , toothcodes.deciduous AS s29deciduous
          FROM toothcodes, teeth
          WHERE toothcodes.toothsite = '29'
                AND teeth.tooth = toothcodes.tooth)
         AS s29
       ON s29.s29dartid = teethdartids.dartid

     LEFT OUTER JOIN
       (SELECT teeth.dartid AS s30dartid
             , teeth.tstate AS s30tstate
             , teeth.tcondition AS s30tcondition
             , toothcodes.deciduous AS s30deciduous
          FROM toothcodes, teeth
          WHERE toothcodes.toothsite = '30'
                AND teeth.tooth = toothcodes.tooth)
         AS s30
       ON s30.s30dartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS s31dartid
             , teeth.tstate AS s31tstate
             , teeth.tcondition AS s31tcondition
             , toothcodes.deciduous AS s31deciduous
          FROM toothcodes, teeth
          WHERE toothcodes.toothsite = '31'
                AND teeth.tooth = toothcodes.tooth)
         AS s31
       ON s31.s31dartid = teethdartids.dartid
     LEFT OUTER JOIN
       (SELECT teeth.dartid AS s32dartid
             , teeth.tstate AS s32tstate
             , teeth.tcondition AS s32tcondition
             , toothcodes.deciduous AS s32deciduous
          FROM toothcodes, teeth
          WHERE toothcodes.toothsite = '32'
                AND teeth.tooth = toothcodes.tooth)
         AS s32
       ON s32.s32dartid = teethdartids.dartid
;


Figure 40. Entity Relationship Diagram of the DENT_SITES View

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


The INTERACT and INTERACT_SORTED Views

Figure 41. Query Defining the INTERACT View


SELECT iid AS iid
     , interact_data.sid AS sid
     , interact_data.act AS act
     , acts.class AS class
     , interact_data.date AS date
     , julian(interact_data.date) AS jdate
     , interact_data.start AS start
     , spm(interact_data.start) AS startspm
     , stop AS stop
     , spm(interact_data.stop) AS stopspm
     , interact_data.observer AS observer
     , interact_data.handwritten AS handwritten
  FROM interact_data
       JOIN acts
            ON (acts.act = interact_data.act)
;


Figure 42. Entity Relationship Diagram of the INTERACT View

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


The MATERNITIES View

Figure 43. Query Defining the MATERNITIES View


SELECT cycles.sname AS mom
     , cycles.cid AS cid
     , cycles.seq AS seq
     , cycles.series AS series
     , cycpoints.cpid AS conceive
     , cycpoints.date AS zdate
     , members.grp AS zdate_grp
     , cycpoints.edate AS edate
     , cycpoints.ldate AS ldate
     , cycpoints.source AS source
     , pregs.pid AS pid
     , pregs.parity AS parity
     , biograph.bioid AS child_bioid
     , biograph.sname AS child
     , biograph.birth AS birth
  FROM cycles
       JOIN cycpoints ON (cycpoints.cid = cycles.cid)
       JOIN members ON (members.date = cycpoints.date
                        AND members.sname = cycles.sname)
       JOIN pregs ON (pregs.conceive = cycpoints.cpid)
       JOIN biograph ON (pregs.pid = biograph.pid)
;


Figure 44. Entity Relationship Diagram of the MATERNITIES View

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


The MIN_MAXS View

Figure 45. Query Defining the MIN_MAXS View


SELECT wreadings.wrid AS wrid
     , wreadings.wstation AS wstation
     , wreadings.wrdaytime AS wrdaytime
     , wreadings.estdaytime AS estdaytime
     , wreadings.wrperson AS wrperson
     , wreadings.wrnotes AS wrnotes
     , tempmins.tempmin AS tempmin
     , tempmaxs.tempmax AS tempmax
     , raingauges.rgspan AS rgspan
     , raingauges.estrgspan AS estrgspan
     , raingauges.rain AS rain
  FROM wreadings
       LEFT OUTER JOIN tempmins
            ON wreadings.wrid = tempmins.wrid
       LEFT OUTER JOIN tempmaxs
            ON wreadings.wrid = tempmaxs.wrid
       LEFT OUTER JOIN raingauges
            ON wreadings.wrid = raingauges.wrid
;


Figure 46. Entity Relationship Diagram of the MIN_MAXS View

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


The MIN_MAXS_SORTED View

Figure 47. Query Defining the MIN_MAXS_SORTED View


SELECT wreadings.wrid AS wrid
     , wreadings.wstation AS wstation
     , wreadings.wrdaytime AS wrdaytime
     , wreadings.estdaytime AS estdaytime
     , wreadings.wrperson AS wrperson
     , wreadings.wrnotes AS wrnotes
     , tempmins.tempmin AS tempmin
     , tempmaxs.tempmax AS tempmax
     , raingauges.rgspan AS rgspan
     , raingauges.estrgspan AS estrgspan
     , raingauges.rain AS rain
  FROM wreadings
       LEFT OUTER JOIN tempmins
            ON wreadings.wrid = tempmins.wrid
       LEFT OUTER JOIN tempmaxs
            ON wreadings.wrid = tempmaxs.wrid
       LEFT OUTER JOIN raingauges
            ON wreadings.wrid = raingauges.wrid
  ORDER BY wreadings.wrdaytime, wreadings.wstation;
;


Figure 48. Entity Relationship Diagram of the MIN_MAXS_SORTED View

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


The MPI_EVENTS View

Figure 49. Query Defining the MPI_EVENTS View


SELECT mpis.mpiid AS mpiid
     , mpis.date AS date
     , mpis.context_type AS context_type
     , mpis.context AS context
     , mpi_data.mpidid AS mpidid
     , mpi_data.seq AS seq
     , mpi_data.mpiact AS mpiact
     , actor.mpipid AS actorid
     , actor.sname AS actor
     , actor.unksname AS unkactor
     , actee.mpipid AS acteeid
     , actee.sname AS actee
     , actee.unksname AS unkactee
     , CASE WHEN EXISTS(SELECT 1
                          FROM mpiacts
                          WHERE mpiacts.mpiact = mpi_data.mpiact
                                AND mpiacts.kind = 'H')
              THEN
              EXISTS(SELECT 1
                FROM mpi_data AS request
                   , mpiacts
                   , mpi_parts AS requestor
                   , mpi_parts AS requestee
                WHERE request.mpiid = mpi_data.mpiid
                      AND request.seq < mpi_data.seq
                      AND mpiacts.mpiact = request.mpiact
                      AND mpiacts.kind = 'R'
                      AND requestor.mpidid = request.mpidid
                      AND requestor.role = 'R'
                      AND requestor.sname = actee.sname
                      AND requestee.mpidid = request.mpidid
                      AND requestee.role = 'E'
                      AND requestee.sname = actor.sname)
             ELSE
               NULL
       END AS solicited
     , EXISTS(SELECT 1
         FROM mpi_data AS initial,
              mpiacts
         WHERE initial.mpiid = mpi_data.mpiid
               AND initial.seq = 1
               AND mpiacts.mpiact = initial.mpiact
               AND mpiacts.decided)
       AS decided
     , mpi_data.helped AS helped
     , mpi_data.active AS active
  FROM mpis
       LEFT OUTER JOIN mpi_data ON (mpis.mpiid = mpi_data.mpiid)
       LEFT OUTER JOIN mpi_parts AS actor
            ON (actor.mpidid = mpi_data.mpidid AND actor.role = 'R')
       LEFT OUTER JOIN mpi_parts AS actee
            ON (actee.mpidid = mpi_data.mpidid AND actee.role = 'E')
;


Figure 50. Entity Relationship Diagram of the MPI_EVENTS View

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


The MTD_CYCLES View

Figure 51. Query Defining the MTD_CYCLES View


SELECT cycles.cid AS cid
     , cycles.sname AS sname
     , cycles.seq AS seq
     , cycles.series AS series
     , mcp.cpid AS mcpid
     , mcp.date AS mdate
     , mcp.edate AS emdate
     , mcp.ldate AS lmdate
     , mcp.source AS msource
     , tcp.cpid AS tcpid
     , tcp.date AS tdate
     , tcp.edate AS etdate
     , tcp.ldate AS ltdate
     , tcp.source AS tsource
     , dcp.cpid AS dcpid
     , dcp.date AS ddate
     , dcp.edate AS eddate
     , dcp.ldate AS lddate
     , dcp.source AS dsource
  FROM cycles
   LEFT OUTER JOIN cycpoints AS mcp ON (mcp.cid = cycles.cid
                                        AND mcp.code = 'M')
   LEFT OUTER JOIN cycpoints AS tcp ON (tcp.cid = cycles.cid
                                        AND tcp.code = 'T')
   LEFT OUTER JOIN cycpoints AS dcp ON (dcp.cid = cycles.cid
                                        AND dcp.code = 'D')
  ORDER BY cycles.sname, cycles.seq
;


Figure 52. Entity Relationship Diagram of the MTD_CYCLES View

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


The PARENTS View

Figure 53. 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 54. Entity Relationship Diagram of the PARENTS View

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


The PCSKINS_SORTED View

Figure 55. Query Defining the PCSKINS_SORTED View


SELECT pcskins.pcsid AS pcsid
     , pcskins.sname AS sname
     , pcskins.date AS date
     , pcskins.color AS color
  FROM pcskins
  ORDER BY sname, date
;


Figure 56. Entity Relationship Diagram of the PCSKINS_SORTED View

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


The PCV_STATS View

Figure 57. Query Defining the PCV_STATS View


SELECT pcvs.dartid AS dartid
     , count(*) AS pcvsamps
     , avg(pcvs.pcv) AS pcv_mean
     , stddev(pcvs.pcv) AS pcv_stddev
  FROM pcvs
  GROUP BY pcvs.dartid
;


Figure 58. Entity Relationship Diagram of the PCV_STATS View

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


The POINTS and POINTS_SORTED Views

Figure 59. Query Defining the POINTS View


SELECT pntid AS pntid
     , sid AS sid
     , activity AS activity
     , posture AS posture
     , foodcode AS foodcode
     , ptime AS ptime
     , spm(ptime) AS ptimespm
  FROM point_data
;


Figure 60. Entity Relationship Diagram of the POINTS View

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


The POTENTIAL_DADS View

Figure 61. 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 supergroup(mommembers.grp, mommembers.date)
                         = supergroup(dadmembers.grp, dadmembers.date))
          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 supergroup(mommembers.grp,
                                                mommembers.date)
                                     = supergroup(dadmembers.grp,
                                                  dadmembers.date))
                      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 62. 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 63. 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

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


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

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


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

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


The QUADS View

Figure 66. Query Defining the QUADS View


SELECT quad_data.quad AS quad
     , ST_X(quad_data.xyloc) AS x
     , ST_Y(quad_data.xyloc) AS y
     , quad_data.aerial AS aerial
  FROM quad_data
;


Figure 67. Entity Relationship Diagram of the QUADS View

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


The SEXSKINS_CYCLES and SEXSKINS_CYCLES_SORTED Views

Figure 68. Query Defining the SEXSKINS_CYCLES View


SELECT cycles.cid AS cid
     , cycles.sname AS sname
     , cycles.seq AS seq
     , cycles.series AS series
     , sexskins.sxid AS sxid
     , sexskins.date AS date
     , sexskins.size AS size
  FROM sexskins, cycles
  WHERE cycles.cid = sexskins.cid
  ORDER BY cycles.sname, sexskins.date
;


Figure 69. Entity Relationship Diagram of the SEXSKINS_CYCLES View

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


The SWERB view

Figure 70. Query Defining the SWERB View


SELECT swerb_data.swid AS swid
     , swerb_departs_data.did AS did
     , swerb_departs_data.date AS date
     , swerb_data.time AS time
     , swerb_bes.beid AS beid
     , swerb_bes.focal_grp AS focal_grp
     , swerb_bes.seq AS seq
     , swerb_data.event AS event
     , swerb_data.seen_grp AS seen_grp
     , swerb_data.lone_animal AS lone_animal
     , swerb_data.quad AS quad
     , CASE
         WHEN swerb_data.xyloc IS NULL
           THEN 'quad'
         ELSE 'gps'
       END AS merged_is
     , COALESCE(ST_X(swerb_data.xyloc), ST_X(quad_data.xyloc))
         AS x
     , COALESCE(ST_Y(swerb_data.xyloc), ST_Y(quad_data.xyloc))
         AS y
     , swerb_data.altitude AS altitude
     , swerb_data.pdop AS pdop
     , swerb_data.accuracy AS accuracy
     , swerb_data.subgroup AS subgroup
     , swerb_data.ogdistance AS ogdistance
     , swerb_data.gps_datetime AS gps_datetime
     , swerb_data.garmincode AS garmincode
     , swerb_loc_data.loc AS loc
     , swerb_loc_data.adcode AS adcode
     , adcodes.adn AS adn
     , swerb_loc_data.conf AS conf
     , swerb_loc_data.adtime AS adtime
     , ST_X(swerb_loc_gps.xyloc) AS second_x
     , ST_Y(swerb_loc_gps.xyloc) AS second_y
     , swerb_loc_gps.altitude AS second_altitude
     , swerb_loc_gps.pdop AS second_pdop
     , swerb_loc_gps.accuracy AS second_accuracy
     , swerb_loc_gps.gps_datetime AS second_gps_datetime
     , swerb_loc_gps.garmincode AS second_garmincode
     , swerb_bes.start AS start
     , swerb_bes.btimeest AS btimeest
     , swerb_bes.bsource AS bsource
     , swerb_bes.stop AS stop
     , swerb_bes.etimeest AS etimeest
     , swerb_bes.esource AS esource
     , swerb_bes.is_effort AS is_effort
     , swerb_departs_gps.gps AS gps
     , swerb_bes.notes AS notes
  FROM swerb_data
       LEFT OUTER JOIN quad_data 
                       ON (quad_data.quad = swerb_data.quad)
       JOIN swerb_bes
            ON (swerb_bes.beid = swerb_data.beid)
       JOIN swerb_departs_data
            ON (swerb_departs_data.did = swerb_bes.did)
       LEFT OUTER JOIN swerb_departs_gps
                       ON (swerb_departs_gps.did = swerb_bes.did)
       LEFT OUTER JOIN swerb_loc_data
                       ON (swerb_loc_data.swid = swerb_data.swid)
       LEFT OUTER JOIN adcodes ON (adcodes.adcode = swerb_loc_data.adcode)
       LEFT OUTER JOIN swerb_loc_gps
                       ON (swerb_loc_gps.swid = swerb_loc_data.swid)
;


Figure 71. Entity Relationship Diagram of the SWERB View

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


The SWERB_DEPARTS view

Figure 72. Query Defining the SWERB_DEPARTS View


SELECT swerb_departs_data.did AS did
     , swerb_departs_data.date AS date
     , swerb_departs_data.time AS time
     , ST_X(swerb_departs_gps.xyloc) AS x
     , ST_Y(swerb_departs_gps.xyloc) AS y
     , swerb_departs_gps.altitude AS altitude
     , swerb_departs_gps.pdop AS pdop
     , swerb_departs_gps.accuracy AS accuracy
     , swerb_departs_gps.gps AS gps
     , swerb_departs_gps.garmincode AS garmincode
  FROM swerb_departs_data
       LEFT OUTER JOIN swerb_departs_gps
                       ON (swerb_departs_gps.did = swerb_departs_data.did)
;


Figure 73. Entity Relationship Diagram of the SWERB_DEPARTS View

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


The SWERB_LOC_GPS_XY view

Figure 74. Query Defining the SWERB_LOC_GPS_XY View


SELECT swerb_loc_gps.swid AS swid
     , ST_X(swerb_loc_gps.xyloc) AS x
     , ST_Y(swerb_loc_gps.xyloc) AS y
     , swerb_loc_gps.altitude AS altitude
     , swerb_loc_gps.pdop AS pdop
     , swerb_loc_gps.accuracy AS accuracy
     , swerb_loc_gps.gps_datetime AS gps_datetime
     , swerb_loc_gps.garmincode AS garmincode
  FROM swerb_loc_gps
;


Figure 75. Entity Relationship Diagram of the SWERB_LOC_GPS_XY View

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


The SWERB_LOCS view

Figure 76. Query Defining the SWERB_LOCS View


SELECT swerb_loc_data.swid AS swid
     , swerb_loc_data.loc AS loc
     , swerb_loc_data.adcode AS adcode
     , adcodes.adn AS adn
     , swerb_loc_data.conf AS conf
     , swerb_loc_data.adtime AS time
  FROM swerb_loc_data
       JOIN adcodes ON (adcodes.adcode = swerb_loc_data.adcode)
;


Figure 77. Entity Relationship Diagram of the SWERB_LOCS View

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


The SWERB_UPLOAD view

Figure 78. Query Defining the SWERB_UPLOAD View


SELECT NULL::TEXT AS header
     , NULL::TEXT    AS name
     , NULL::TEXT    AS description
     , NULL::TEXT    AS type
     , NULL::TEXT    AS position
     , NULL::TEXT    AS altitude
     , NULL::TEXT    AS depth
     , NULL::TEXT    AS proximity
     , NULL::TEXT    AS display_mode
     , NULL::TEXT    AS color
     , NULL::TEXT    AS symbol
     , NULL::TEXT    AS facility
     , NULL::TEXT    AS city
     , NULL::TEXT    AS state
     , NULL::TEXT    AS country
     , NULL::TEXT    AS pdop
     , NULL::TEXT    AS accuracy
     , NULL::TEXT    AS quad
     , NULL::TEXT    AS date
     , NULL::TEXT    AS timeest
     , NULL::TEXT    AS source
     , NULL::TEXT    AS lone_animal
     , NULL::TEXT    AS is_effort
     , NULL::BOOLEAN AS secondary_ad
     , NULL::TEXT    AS notes
  WHERE _raise_babase_exception(
          'Cannot select SWERB_UPLOAD'
          || ': The only use of the SWERB_UPLOAD view is to insert'
          || ' new data into the SWERB portion of babase')
;


Figure 79. Entity Relationship Diagram of the SWERB_UPLOAD View

The SWERB_UPLOAD view is used only to insert data into the SWERB portion of Babase. Since it cannot be queried and the semantics of the uploaded file varies by line it has no ER diagram.


The ULNA_STATS View

Figure 80. Query Defining the ULNA_STATS View


SELECT ulnas.dartid AS dartid
     , count(*) AS ulsamps
     , avg(ulnas.ullength) AS ullength_mean
     , stddev(ulnas.ullength) AS ullength_stddev
     , avg(ulnas.ulunadjusted) AS ulunadjusted_mean
     , stddev(ulnas.ulunadjusted) AS ulunadjusted_stddev
  FROM ulnas
  GROUP BY ulnas.dartid
;


Figure 81. Entity Relationship Diagram of the ULNA_STATS View

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


The TESTES_ARC_STATS View

Figure 82. Query Defining the TESTES_ARC_STATS View


SELECT testesdartids.dartid AS dartid
     , testesllength.testllengthsamps AS testllengthsamps
     , testesllength.testllength_mean AS testllength_mean
     , testesllength.testllength_stddev AS testllength_stddev
     , testeslwidth.testlwidthsamps AS testlwidthsamps
     , testeslwidth.testlwidth_mean AS testlwidth_mean
     , testeslwidth.testlwidth_stddev AS testlwidth_stddev
     , testesrlength.testrlengthsamps AS testrlengthsamps
     , testesrlength.testrlength_mean AS testrlength_mean
     , testesrlength.testrlength_stddev AS testrlength_stddev
     , testesrwidth.testrwidthsamps AS testrwidthsamps
     , testesrwidth.testrwidth_mean AS testrwidth_mean
     , testesrwidth.testrwidth_stddev AS testrwidth_stddev
FROM (SELECT testes_arc.dartid
        FROM testes_arc
        GROUP BY testes_arc.dartid)
       AS testesdartids
     LEFT OUTER JOIN
       (SELECT testes_arc.dartid AS llengthdartid
             , count(*) AS testllengthsamps
             , avg(testes_arc.testlength) AS testllength_mean
             , stddev(testes_arc.testlength) AS testllength_stddev
          FROM testes_arc
          WHERE testes_arc.testside = 'L'
                AND testes_arc.testlength IS NOT NULL
          GROUP BY testes_arc.dartid)
         AS testesllength
       ON testesllength.llengthdartid = testesdartids.dartid
     LEFT OUTER JOIN
       (SELECT testes_arc.dartid AS lwidthdartid
             , count(*) AS testlwidthsamps
             , avg(testes_arc.testwidth) AS testlwidth_mean
             , stddev(testes_arc.testwidth) AS testlwidth_stddev
          FROM testes_arc
          WHERE testes_arc.testside = 'L'
                AND testes_arc.testwidth IS NOT NULL
          GROUP BY testes_arc.dartid)
         AS testeslwidth
       ON testeslwidth.lwidthdartid = testesdartids.dartid
     LEFT OUTER JOIN
       (SELECT testes_arc.dartid AS rlengthdartid
             , count(*) AS testrlengthsamps
             , avg(testes_arc.testlength) AS testrlength_mean
             , stddev(testes_arc.testlength) AS testrlength_stddev
          FROM testes_arc
          WHERE testes_arc.testside = 'R'
                AND testes_arc.testlength IS NOT NULL
          GROUP BY testes_arc.dartid)
         AS testesrlength
       ON testesrlength.rlengthdartid = testesdartids.dartid
     LEFT OUTER JOIN
       (SELECT testes_arc.dartid AS rwidthdartid
             , count(*) AS testrwidthsamps
             , avg(testes_arc.testwidth) AS testrwidth_mean
             , stddev(testes_arc.testwidth) AS testrwidth_stddev
          FROM testes_arc
          WHERE testes_arc.testside = 'R'
                AND testes_arc.testwidth IS NOT NULL
          GROUP BY testes_arc.dartid)
         AS testesrwidth
       ON testesrwidth.rwidthdartid = testesdartids.dartid
;


Figure 83. Entity Relationship Diagram of the TESTES_ARC_STATS View

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


The TESTES_DIAM_STATS View

Figure 84. Query Defining the TESTES_DIAM_STATS View


SELECT testesdartids.dartid AS dartid
     , testesllength.testllengthsamps AS testllengthsamps
     , testesllength.testllength_mean AS testllength_mean
     , testesllength.testllength_stddev AS testllength_stddev
     , testeslwidth.testlwidthsamps AS testlwidthsamps
     , testeslwidth.testlwidth_mean AS testlwidth_mean
     , testeslwidth.testlwidth_stddev AS testlwidth_stddev
     , testesrlength.testrlengthsamps AS testrlengthsamps
     , testesrlength.testrlength_mean AS testrlength_mean
     , testesrlength.testrlength_stddev AS testrlength_stddev
     , testesrwidth.testrwidthsamps AS testrwidthsamps
     , testesrwidth.testrwidth_mean AS testrwidth_mean
     , testesrwidth.testrwidth_stddev AS testrwidth_stddev
FROM (SELECT testes_diam.dartid
        FROM testes_diam
        GROUP BY testes_diam.dartid)
       AS testesdartids
     LEFT OUTER JOIN
       (SELECT testes_diam.dartid AS llengthdartid
             , count(*) AS testllengthsamps
             , avg(testes_diam.testlength) AS testllength_mean
             , stddev(testes_diam.testlength) AS testllength_stddev
          FROM testes_diam
          WHERE testes_diam.testside = 'L'
                AND testes_diam.testlength IS NOT NULL
          GROUP BY testes_diam.dartid)
         AS testesllength
       ON testesllength.llengthdartid = testesdartids.dartid
     LEFT OUTER JOIN
       (SELECT testes_diam.dartid AS lwidthdartid
             , count(*) AS testlwidthsamps
             , avg(testes_diam.testwidth) AS testlwidth_mean
             , stddev(testes_diam.testwidth) AS testlwidth_stddev
          FROM testes_diam
          WHERE testes_diam.testside = 'L'
                AND testes_diam.testwidth IS NOT NULL
          GROUP BY testes_diam.dartid)
         AS testeslwidth
       ON testeslwidth.lwidthdartid = testesdartids.dartid
     LEFT OUTER JOIN
       (SELECT testes_diam.dartid AS rlengthdartid
             , count(*) AS testrlengthsamps
             , avg(testes_diam.testlength) AS testrlength_mean
             , stddev(testes_diam.testlength) AS testrlength_stddev
          FROM testes_diam
          WHERE testes_diam.testside = 'R'
                AND testes_diam.testlength IS NOT NULL
          GROUP BY testes_diam.dartid)
         AS testesrlength
       ON testesrlength.rlengthdartid = testesdartids.dartid
     LEFT OUTER JOIN
       (SELECT testes_diam.dartid AS rwidthdartid
             , count(*) AS testrwidthsamps
             , avg(testes_diam.testwidth) AS testrwidth_mean
             , stddev(testes_diam.testwidth) AS testrwidth_stddev
          FROM testes_diam
          WHERE testes_diam.testside = 'R'
                AND testes_diam.testwidth IS NOT NULL
          GROUP BY testes_diam.dartid)
         AS testesrwidth
       ON testesrwidth.rwidthdartid = testesdartids.dartid
;


Figure 85. Entity Relationship Diagram of the TESTES_DIAM_STATS View

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


Views Which Add Gid To Tables

In addition to the above views there are a number of views which produce the group of a referenced individual as of a pertinent date. These views are all named after the table from which they are derived, with the addition of the suffixed _GRP. They are nearly identical to the table from which they derive, differing only by the addition of a column named Grp.

The only operation allowed on these views is SELECT. INSERT, UPDATE, and DELETE are not allowed.

The BIRTH_GRP View

Figure 86. Query Defining the BIRTH_GRP View


SELECT biograph.*
     , members.grp AS grp
  FROM members, biograph
  WHERE members.sname = biograph.sname
        AND members.date = CAST(biograph.birth AS DATE)
;


Figure 87. Entity Relationship Diagram of the BIRTH_GRP View

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


The ENTRYDATE_GRP View

Figure 88. Query Defining the ENTRYDATE_GRP View


SELECT biograph.*
     , members.grp AS grp
  FROM members, biograph
  WHERE members.sname = biograph.sname
        AND members.date = CAST(biograph.entrydate AS DATE)
;


Figure 89. Entity Relationship Diagram of the ENTRYDATE_GRP View

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


The STATDATE_GRP View

Figure 90. Query Defining the STATDATE_GRP View


SELECT biograph.*
     , members.grp AS grp
  FROM members, biograph
  WHERE members.sname = biograph.sname
        AND members.date = CAST(biograph.statdate AS DATE)
;


Figure 91. Entity Relationship Diagram of the STATDATE_GRP View

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


The CONSORTDATES_GRP View

Figure 92. Query Defining the CONSORTDATES_GRP View


SELECT consortdates.*
     , members.grp AS grp
  FROM members, consortdates
  WHERE members.sname = consortdates.sname
        AND members.date = CAST(consortdates.consorted AS DATE)
;


Figure 93. Entity Relationship Diagram of the CONSORTDATES_GRP View

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


The CYCGAPDAYS_GRP View

Figure 94. Query Defining the CYCGAPDAYS_GRP View


SELECT cycgapdays.*
     , members.grp AS grp
  FROM members, cycgapdays
  WHERE members.sname = cycgapdays.sname
        AND members.date = CAST(cycgapdays.date AS DATE)
;


Figure 95. Entity Relationship Diagram of the CYCGAPDAYS_GRP View

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


The CYCGAPS_GRP View

Figure 96. Query Defining the CYCGAPS_GRP View


SELECT cycgaps.*
     , members.grp AS grp
  FROM members, cycgaps
  WHERE members.sname = cycgaps.sname
        AND members.date = CAST(cycgaps.date AS DATE)
;


Figure 97. Entity Relationship Diagram of the CYCGAPS_GRP View

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


The CYCSTATS_GRP View

Figure 98. Query Defining the CYCSTATS_GRP View


SELECT cycstats.*
     , members.grp AS grp
  FROM members, cycstats
  WHERE members.sname = cycstats.sname
        AND members.date = CAST(cycstats.date AS DATE)
;


Figure 99. Entity Relationship Diagram of the CYCSTATS_GRP View

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


The DARTINGS_GRP View

Figure 100. Query Defining the DARTINGS_GRP View


SELECT dartings.*
     , members.grp AS grp
  FROM members, dartings
  WHERE members.sname = dartings.sname
        AND members.date = CAST(dartings.date AS DATE)
;


Figure 101. Entity Relationship Diagram of the DARTINGS_GRP View

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


The DISPERSEDATES_GRP View

Figure 102. Query Defining the DISPERSEDATES_GRP View


SELECT dispersedates.*
     , members.grp AS grp
  FROM members, dispersedates
  WHERE members.sname = dispersedates.sname
        AND members.date = CAST(dispersedates.dispersed AS DATE)
;


Figure 103. Entity Relationship Diagram of the DISPERSEDATES_GRP View

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


The MATUREDATES_GRP View

Figure 104. Query Defining the MATUREDATES_GRP View


SELECT maturedates.*
     , members.grp AS grp
  FROM members, maturedates
  WHERE members.sname = maturedates.sname
        AND members.date = CAST(maturedates.matured AS DATE)
;


Figure 105. Entity Relationship Diagram of the MATUREDATES_GRP View

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


The MDINTERVALS_GRP View

Figure 106. Query Defining the MDINTERVALS_GRP View


SELECT mdintervals.*
     , members.grp AS grp
  FROM members, mdintervals
  WHERE members.sname = mdintervals.sname
        AND members.date = CAST(mdintervals.date AS DATE)
;


Figure 107. Entity Relationship Diagram of the MDINTERVALS_GRP View

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


The MMINTERVALS_GRP View

Figure 108. Query Defining the MMINTERVALS_GRP View


SELECT mmintervals.*
     , members.grp AS grp
  FROM members, mmintervals
  WHERE members.sname = mmintervals.sname
        AND members.date = CAST(mmintervals.date AS DATE)
;


Figure 109. Entity Relationship Diagram of the MMINTERVALS_GRP View

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


The PCSKINS_GRP View

Figure 110. Query Defining the PCSKINS_GRP View


SELECT pcskins.*
     , members.grp AS grp
  FROM members, pcskins
  WHERE members.sname = pcskins.sname
        AND members.date = CAST(pcskins.date AS DATE)
;


Figure 111. Entity Relationship Diagram of the PCSKINS_GRP View

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


The RANKDATES_GRP View

Figure 112. Query Defining the RANKDATES_GRP View


SELECT rankdates.*
     , members.grp AS grp
  FROM members, rankdates
  WHERE members.sname = rankdates.sname
        AND members.date = CAST(rankdates.ranked AS DATE)
;


Figure 113. Entity Relationship Diagram of the RANKDATES_GRP View

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


The REPSTATS_GRP View

Figure 114. Query Defining the REPSTATS_GRP View


SELECT repstats.*
     , members.grp AS grp
  FROM members, repstats
  WHERE members.sname = repstats.sname
        AND members.date = CAST(repstats.date AS DATE)
;


Figure 115. Entity Relationship Diagram of the REPSTATS_GRP View

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