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 LOCATIONS_FREE View
The MATERNITIES View
The MIN_MAXS View
The MIN_MAXS_SORTED View
The MPI_EVENTS View
The MTD_CYCLES View
The NUCACID_CONCS View
The NUCACIDS View
The NUCACIDS_W_CONC View
The PARENTS View
The PCSKINS_SORTED View
The PCV_STATS View
The POINTS and POINTS_SORTED Views
The POTENTIAL_DADS View
The PROPORTIONAL_RANKS 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 TISSUES View
The ULNA_STATS View
The TESTES_ARC_STATS View
The TESTES_DIAM_STATS View
The WP_DETAILS_AFFECTEDPARTS View
The WP_HEALS View
The WP_REPORTS_OBSERVERS 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 Inventory Entity Relationship Diagram
13. Babase Physical Traits Hormone Data Entity Relationship Diagram
14. Babase Physical Traits Genetic Hybrid Score Data Entity Relationship Diagram
15. Babase Physical Traits Wounds and Pathologies Data Entity Relationship Diagram
16. Babase SWERB Core Tables Entity Relationship Diagram
17. Babase SWERB Grove/Waterhole Location Tables Entity Relationship Diagram
18. Babase Manual Weather Data Entity Relationship Diagram
19. Babase WeatherHawk Data Entity Relationship Diagram
20. Warning Sub-System Entity Relationship Diagram
21. Query Defining the ACTOR_ACTEES View
22. Entity Relationship Diagram of the ACTOR_ACTEES View
23. Query Defining the ANESTH_STATS View
24. Entity Relationship Diagram of the ANESTH_STATS View
25. Query Defining the BODYTEMP_STATS View
26. Entity Relationship Diagram of the BODYTEMP_STATS View
27. Query Defining the CENSUS_DEMOG View
28. Entity Relationship Diagram of the CENSUS_DEMOG View
29. Query Defining the CHEST_STATS View
30. Entity Relationship Diagram of the CHEST_STATS View
31. Query Defining the CROWNRUMP_STATS View
32. Entity Relationship Diagram of the CROWNRUMP_STATS View
33. Query Defining the CYCLES_SEXSKINS View
34. Entity Relationship Diagram of the CYCLES_SEXSKINS View
35. Query Defining the CYCPOINTS_CYCLES View
36. Entity Relationship Diagram of the CYCPOINTS_CYCLES View
37. Query Defining the DSAMPLES View
38. Query Defining the DEMOG_CENSUS View
39. Entity Relationship Diagram of the DEMOG_CENSUS View
40. Query Defining the DENT_CODES View
41. Entity Relationship Diagram of the DENT_CODES View
42. Query Defining the DENT_SITES View
43. Entity Relationship Diagram of the DENT_SITES View
44. Query Defining the INTERACT View
45. Entity Relationship Diagram of the INTERACT View
46. Query Defining the LOCATIONS_FREE View
47. Entity Relationship Diagram of the LOCATIONS_FREE View
48. Query Defining the MATERNITIES View
49. Entity Relationship Diagram of the MATERNITIES View
50. Query Defining the MIN_MAXS View
51. Entity Relationship Diagram of the MIN_MAXS View
52. Query Defining the MIN_MAXS_SORTED View
53. Entity Relationship Diagram of the MIN_MAXS_SORTED View
54. Query Defining the MPI_EVENTS View
55. Entity Relationship Diagram of the MPI_EVENTS View
56. Query Defining the MTD_CYCLES View
57. Entity Relationship Diagram of the MTD_CYCLES View
58. Query Defining the NUCACID_CONCS View
59. Entity Relationship Diagram of the NUCACID_CONCS View
60. Query Defining the NUCACIDS View
61. Entity Relationship Diagram of the NUCACIDS View
62. Query Defining the NUCACIDS_W_CONC View
63. Entity Relationship Diagram of the NUCACIDS_W_CONC View
64. Query Defining the PARENTS View
65. Entity Relationship Diagram of the PARENTS View
66. Query Defining the PCSKINS_SORTED View
67. Entity Relationship Diagram of the PCSKINS_SORTED View
68. Query Defining the PCV_STATS View
69. Entity Relationship Diagram of the PCV_STATS View
70. Query Defining the POINTS View
71. Entity Relationship Diagram of the POINTS View
72. Query Defining the POTENTIAL_DADS View
73. Entity Relationship Diagram of the foundation of the POTENTIAL_DADS View
74. 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
75. Entity Relationship Diagram of that portion of the POTENTIAL_DADS View having easily computed columns
76. Entity Relationship Diagram of that portion of the POTENTIAL_DADS View involving social interactions
77. Query Defining the PROPORTIONAL_RANKS View
78. Entity Relationship Diagram of the PROPORTIONAL_RANKS View
79. Query Defining the QUADS View
80. Entity Relationship Diagram of the QUADS View
81. Query Defining the SEXSKINS_CYCLES View
82. Entity Relationship Diagram of the SEXSKINS_CYCLES View
83. Query Defining the SWERB View
84. Entity Relationship Diagram of the SWERB View
85. Query Defining the SWERB_DEPARTS View
86. Entity Relationship Diagram of the SWERB_DEPARTS View
87. Query Defining the SWERB_LOC_GPS_XY View
88. Entity Relationship Diagram of the SWERB_LOC_GPS_XY View
89. Query Defining the SWERB_LOCS View
90. Entity Relationship Diagram of the SWERB_LOCS View
91. Query Defining the SWERB_UPLOAD View
92. Entity Relationship Diagram of the SWERB_UPLOAD View
93. Query Defining the TISSUES View
94. Entity Relationship Diagram of the TISSUES View
95. Query Defining the ULNA_STATS View
96. Entity Relationship Diagram of the ULNA_STATS View
97. Query Defining the TESTES_ARC_STATS View
98. Entity Relationship Diagram of the TESTES_ARC_STATS View
99. Query Defining the TESTES_DIAM_STATS View
100. Entity Relationship Diagram of the TESTES_DIAM_STATS View
101. Query Defining the WP_DETAILS_AFFECTEDPARTS View
102. Entity Relationship Diagram of the WP_DETAILS_AFFECTEDPARTS View
103. Query Defining the WP_HEALS View
104. Entity Relationship Diagram of the WP_HEALS View, Overall
105. Entity Relationship Diagram of the WP_HEALS View for rows with an update to a wound/pathology report
106. Entity Relationship Diagram of the WP_HEALS View for rows with an update to a wound/pathology cluster
107. Entity Relationship Diagram of the WP_HEALS View for rows with an update to an affected body part
108. Query Defining the WP_REPORTS_OBSERVERS View
109. Entity Relationship Diagram of the WP_REPORTS_OBSERVERS View
110. Query Defining the BIRTH_GRP View
111. Entity Relationship Diagram of the BIRTH_GRP View
112. Query Defining the ENTRYDATE_GRP View
113. Entity Relationship Diagram of the ENTRYDATE_GRP View
114. Query Defining the STATDATE_GRP View
115. Entity Relationship Diagram of the STATDATE_GRP View
116. Query Defining the CONSORTDATES_GRP View
117. Entity Relationship Diagram of the CONSORTDATES_GRP View
118. Query Defining the CYCGAPDAYS_GRP View
119. Entity Relationship Diagram of the CYCGAPDAYS_GRP View
120. Query Defining the CYCGAPS_GRP View
121. Entity Relationship Diagram of the CYCGAPS_GRP View
122. Query Defining the CYCSTATS_GRP View
123. Entity Relationship Diagram of the CYCSTATS_GRP View
124. Query Defining the DARTINGS_GRP View
125. Entity Relationship Diagram of the DARTINGS_GRP View
126. Query Defining the DISPERSEDATES_GRP View
127. Entity Relationship Diagram of the DISPERSEDATES_GRP View
128. Query Defining the MATUREDATES_GRP View
129. Entity Relationship Diagram of the MATUREDATES_GRP View
130. Query Defining the MDINTERVALS_GRP View
131. Entity Relationship Diagram of the MDINTERVALS_GRP View
132. Query Defining the MMINTERVALS_GRP View
133. Entity Relationship Diagram of the MMINTERVALS_GRP View
134. Query Defining the PCSKINS_GRP View
135. Entity Relationship Diagram of the PCSKINS_GRP View
136. Query Defining the RANKDATES_GRP View
137. Entity Relationship Diagram of the RANKDATES_GRP View
138. Query Defining the REPSTATS_GRP View
139. 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
Physical Traits
TableOne row for each
WP_AFFECTEDPARTSbody part affected by a specific wound/pathology
WP_DETAILSwound or pathology cluster indicated on a report
WP_HEALUPDATESupdate on progress of wound/pathology healing
WP_REPORTSwound/pathology report
 
Analyzed: Physical Traits
TableOne row for each
HORMONE_KITSkit or protocol used to assay hormone concentration
HORMONE_PREP_DATAlaboratory preparation performed on a sample in the specified series
HORMONE_PREP_SERIESseries of preparations and assays performed on a sample
HORMONE_RESULT_DATAassay for hormone concentration in a sample
HORMONE_SAMPLE_DATAtissue sample used in hormone analysis
HYBRIDGENE_ANALYSESanalysis of genetic hybrid scores
HYBRIDGENE_SCORESgenetic hybrid score for an individual from an analysis
 
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
 
Inventory
TableOne row for each
LOCATIONSLocation that can be used to store tissue and nucleic acid samples
NUCACID_CONC_DATAQuantification of a nucleic acid sample's concentration
NUCACID_DATANucleic acid sample that is or ever has been in the inventory
NUCACID_LOCAL_IDSName/ID used to identify a nucleic acid sample at a particular institution
NUCACID_SOURCESNucleic acid sample that has another nucleic acid sample as its source
POPULATIONSStudy population under observation or from which tissue or nucleic acid samples have been collected
TISSUE_DATATissue sample that is or ever has been in the inventory
TISSUE_LOCAL_IDSName/ID used to identify a tissue sample at a particular institution
UNIQUE_INDIVSIndividual under observation or from whom tissue or nucleic acid samples have been collected
 
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_DATA_CONFIDENCESanalyzed observation of a location
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...
BODYPARTSBodypartTICKS.Bodypart, BODYPARTS.Bodyregion, WP_AFFECTEDPARTS.Bodypartpart of the body
LAB_PERSONNELInitialsHYBRIDGENE_ANALYSES.Analyzed_By, NUCACID_CREATORS.Creator, WBC_COUNTS.Counted_Byperson who generates data, usually in a lab setting
OBSERVERSInitialsSAMPLES.Observer, WREADINGS.WRperson, RGSETUPS.RGSPerson, CROWNRUMPS.CRobserver, CHESTS.Chobserver, ULNAS.Ulobserver, HUMERUSES.Huobserver, SWERB_OBSERVERS.Observerperson who record observational data
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
DAD_DATA_COMPLETENESSCompletenessDAD_DATA.Completenesscategory of analysis completeness
DAD_DATA_MISMATCHESMismatchDAD_DATA.Consensus_Mismatchcategory of genetic mismatch
RNKTYPESRnktypeRANKS.Rnktyperank ordering assigned to subject and month
STATUSESStatusBIOGRAPH.Statusbaboon alive at last observation
 
Physical Traits
TableId ColumnRelated Column(s) One entry for every possible choice of...
HORMONE_IDSHormoneHORMONE_KITS.Hormonehormone that may be extracted and assayed for
HORMONE_PREP_PROCEDURESIdHORMONE_PREP_DATA.Procedureprocedure that may be performed in preparation for a hormone assay
HYBRIDGENE_SOFTWARESoftwareHYBRIDGENE_ANALYSES.Softwaresoftware used for genetic hybrid score analysis
MARKERSMarkerHYBRIDGENE_ANALYSES.Markertype of genetic marker used for genetic hybrid score analysis
WP_HEALSTATUSESHealstatusWP_HEALUPDATES.HealStatushealing progress used in healing updates
WP_REPORTSTATESReportStateWP_REPORTS.ReportStatestatus of wound/pathology report
WP_WOUNDPATHCODESWoundPathCodeWP_DETAILS.WoundPathCodewound or pathology
 
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...
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
 
Inventory
TableId ColumnRelated Column(s) One entry for every possible ...
INSTITUTIONSInstitutionLOCATIONS.Institution, NUCACID_LOCAL_IDS.Institution, TISSUE_LOCAL_IDS.Institutionpossible locale where tissue and nucleic acid samples can be stored or used
MISID_STATUSESMisid_StatusTISSUE_DATA.Misid_Statuslevel of confidence in the identity of a tissue sample
NUCACID_CONC_METHODSConc_MethodNUCACID_CONC_DATA.Conc_Methodmethod used for quantifying nucleic acid concentrations
NUCACID_CREATION_METHODSCreation_MethodNUCACID_DATA.Creation_Methodmethod used for creating nucleic acid samples
NUCACID_TYPESNucAcid_TypeNUCACID_DATA.NucAcid_Typetype of nucleic acid sample
STORAGE_MEDIAStorage_MediumTISSUE_DATA.Storage_Mediummedium used for storage/archiving of tissue samples
TISSUE_TYPESTissue_TypeTISSUE_DATA.Tissue_Typetype of tissue sample
 
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_CONFIDENCESConfSWERB_LOC_DATA_CONFIDENCES.Confidenceconfidence score used when analyzing the accuracy of a recorded observation of a location.
SWERB_LOC_STATUSESConfSWERB_LOC_DATA.Loc_Statusstatus for a recorded observation of a location.
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
PROPORTIONAL_RANKSRANKS rowAutomatic calculation of proportional ranks from the ordinal ranks in RANKS.RANKS
 
Physical Traits
ViewOne row for eachPurposeTables/Views used
ESTROGENSHORMONE_RESULT_DATA row with an estrogen kitEasy access to estrogen data.BIOGRAPH, HORMONE_KITS, HORMONE_PREP_DATA, HORMONE_PREP_SERIES, HORMONE_RESULT_DATA, HORMONE_SAMPLE_DATA, TISSUE_DATA, UNIQUE_INDIVS
GLUCOCORTICOIDSHORMONE_RESULT_DATA row with a glucocorticoid kit.Easy access to glucocorticoid data.BIOGRAPH, HORMONE_KITS, HORMONE_PREP_DATA, HORMONE_PREP_SERIES, HORMONE_RESULT_DATA, HORMONE_SAMPLE_DATA, TISSUE_DATA, UNIQUE_INDIVS
HORMONE_PREPSHORMONE_PREP_DATA rowPresents HORMONE_PREP_DATA with identifying information from TISSUE_DATA and BIOGRAPH. Also useful for maintaining data in HORMONE_PREP_DATA.BIOGRAPH, HORMONE_PREP_DATA, HORMONE_PREP_SERIES, HORMONE_SAMPLE_DATA, TISSUE_DATA, UNIQUE_INDIVS
HORMONE_RESULTSHORMONE_RESULT_DATA rowPresents HORMONE_RESULT_DATA with identifying information from TISSUE_DATA and BIOGRAPH. Also useful for maintaining data in HORMONE_RESULT_DATA.BIOGRAPH, HORMONE_KITS, HORMONE_PREP_SERIES, HORMONE_RESULT_DATA, HORMONE_SAMPLE_DATA, TISSUE_DATA, UNIQUE_INDIVS
HORMONE_SAMPLESHORMONE_SAMPLE_DATA rowPresents HORMONE_SAMPLE_DATA with identifying information from TISSUE_DATA and BIOGRAPH. Also useful for maintaining data in HORMONE_SAMPLE_DATA.BIOGRAPH, HORMONE_SAMPLE_DATA, TISSUE_DATA, UNIQUE_INDIVS
HORMONESHORMONE_SAMPLE_DATA rowPresents all preparations and assay results for a sample in a single row, with identifying information from TISSUE_DATA and BIOGRAPH.BIOGRAPH, HORMONE_KITS, HORMONE_PREP_DATA, HORMONE_PREP_SERIES, HORMONE_RESULT_DATA, HORMONE_SAMPLE_DATA, TISSUE_DATA, UNIQUE_INDIVS
PROGESTERONESHORMONE_RESULT_DATA row with a progesterone kit.Easy access to progesterone data.BIOGRAPH, HORMONE_KITS, HORMONE_PREP_DATA, HORMONE_PREP_SERIES, HORMONE_RESULT_DATA, HORMONE_SAMPLE_DATA, TISSUE_DATA, UNIQUE_INDIVS
TESTOSTERONESHORMONE_RESULT_DATA row with a testosterone kit.Easy access to testosterone data.BIOGRAPH, HORMONE_KITS, HORMONE_PREP_DATA, HORMONE_PREP_SERIES, HORMONE_RESULT_DATA, HORMONE_SAMPLE_DATA, TISSUE_DATA, UNIQUE_INDIVS
THYROID_HORMONESHORMONE_RESULT_DATA row with a thyroid hormone kit.Easy access to thyroid hormone data.BIOGRAPH, HORMONE_KITS, HORMONE_PREP_DATA, HORMONE_PREP_SERIES, HORMONE_RESULT_DATA, HORMONE_SAMPLE_DATA, TISSUE_DATA, UNIQUE_INDIVS
WOUNDSPATHOLOGIESWP_AFFECTEDPARTS rowQuerying of wounds/pathologies data (without heal updates).WP_REPORTS, WP_OBSERVERS, WP_DETAILS, WP_AFFECTEDPARTS, BODYPARTS
WP_DETAILS_AFFECTEDPARTSWP_AFFECTEDPARTS rowUpload of WP_DETAILS and WP_AFFECTEDPARTS rows.WP_DETAILS, WP_AFFECTEDPARTS, BODYPARTS
WP_HEALSWP_HEALUPDATES rowUpload and viewing of WP_HEALUPDATES rows.WP_REPORTS, WP_DETAILS, WP_AFFECTEDPARTS, BODYPARTS, WP_HEALUPDATES
WP_REPORTS_OBSERVERSWP_REPORTS rowUpload of WP_REPORTS and WP_OBSERVERS rows.WP_REPORTS, WP_OBSERVERS
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
 
Inventory
ViewOne row for eachPurposeTables/Views used
LOCATIONS_FREELOCATIONS row that isn't used in NUCACID_DATA or in TISSUE_DATAQuerying of available ("free") locations for storing new samplesLOCATIONS, NUCACID_DATA, TISSUE_DATA
NUCACID_CONCSNUCACID_CONC_DATA rowConverting and standardizing units of nucleic acid concentrationNUCACID_CONC_DATA, NUCACID_CONC_METHODS, NUCACID_LOCAL_IDS
NUCACIDSNUCACID_DATA rowShowing data about nucleic acids in a human-readable formatNUCACID_DATA, TISSUE_DATA, UNIQUE_INDIVS, BIOGRAPH, NUCACID_LOCAL_IDS, NUCACID_SOURCES
NUCACIDS_W_CONCNUCACID_DATA rowShowing data about nucleic acids in a human-readable format, including concentrations from the most-recent quantificationsNUCACID_DATA, NUCACID_CONC_DATA, TISSUE_DATA, UNIQUE_INDIVS, BIOGRAPH, NUCACID_LOCAL_IDS, NUCACID_SOURCES
TISSUESTISSUE_DATA rowShowing data about tissue samples in a human-readable formatTISSUE_DATA, UNIQUE_INDIVS, BIOGRAPH, TISSUE_LOCAL_IDS
 
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 Inventory Entity Relationship Diagram

If we could we would display a diagram here depicting the Babase Inventory tables.


Figure 13. Babase Physical Traits Hormone Data Entity Relationship Diagram

If we could we would display a diagram here depicting the Babase Physical Traits Hormone Data tables.


Figure 14. Babase Physical Traits Genetic Hybrid Score Data Entity Relationship Diagram

If we could we would display a diagram here depicting the Babase Physical Traits Genetic Hybrid Score Data tables.


Figure 15. Babase Physical Traits Wounds and Pathologies Data Entity Relationship Diagram

If we could we would display a diagram here depicting the Babase Physical Traits Wounds and Pathologies Data tables.


Figure 16. Babase SWERB Core Tables Entity Relationship Diagram

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


Figure 17. 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 18. Babase Manual Weather Data Entity Relationship Diagram

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


Figure 19. Babase WeatherHawk Data Entity Relationship Diagram

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


Figure 20. 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 21. 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
     , interact_data.exact_date AS exact_date
  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 22. 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 23. 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 24. 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 25. 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 26. 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 27. 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 28. 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 29. 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 30. 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 31. 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 32. 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 33. 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 34. 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 35. 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 36. 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 37. 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 38. 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 39. 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 40. 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 41. 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 42. 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 43. 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 44. 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
     , interact_data.exact_date AS exact_date
  FROM interact_data
       JOIN acts
            ON (acts.act = interact_data.act)
;


Figure 45. Entity Relationship Diagram of the INTERACT View

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


The LOCATIONS_FREE View

Figure 46. Query Defining the LOCATIONS_FREE View


SELECT locations.locid AS locid
     , locations.institution AS institution
     , locations.location AS location
     , locations.is_unique AS is_unique
  FROM locations
  WHERE NOT EXISTS (SELECT 1
                      FROM tissue_data
                      WHERE tissue_data.locid = locations.locid)
    AND NOT EXISTS (SELECT 1
                      FROM nucacid_data
                      WHERE nucacid_data.locid = locations.locid)
;


Figure 47. Entity Relationship Diagram of the LOCATIONS_FREE View

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


The MATERNITIES View

Figure 48. 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 49. 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 50. 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 51. 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 52. 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 53. 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 54. 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 55. 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 56. 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 57. 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 NUCACID_CONCS View

Figure 58. Query Defining the NUCACID_CONCS View


SELECT nucacid_conc_data.nacid AS nacid
     , nucacid_conc_data.naid AS naid
     , local_1.localid AS localid_1
     , local_2.localid AS localid_2
     , nucacid_conc_data.conc_method AS conc_method
     , nucacid_conc_methods.descr AS method_descr
     , nucacid_conc_data.conc_date AS conc_date
     , nucacid_conc_data.pg_ul AS pg_ul
     , (nucacid_conc_data.pg_ul / 1000)::numeric(10,4) AS ng_ul
  FROM nucacid_conc_data
  JOIN nucacid_conc_methods
    ON nucacid_conc_methods.conc_method = nucacid_conc_data.conc_method
  LEFT JOIN nucacid_local_ids AS local_1
    ON local_1.naid = nucacid_conc_data.naid
       AND local_1.institution = 1
  LEFT JOIN nucacid_local_ids AS local_2
    ON local_2.naid = nucacid_conc_data.naid
       AND local_2.institution = 2
;


Figure 59. Entity Relationship Diagram of the NUCACID_CONCS View

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


The NUCACIDS View

Figure 60. Query Defining the NUCACIDS View


WITH concat_creators AS (SELECT naid
                              , string_agg(creator, '/' ORDER BY naid, nacrid) AS created_by
                           FROM nucacid_creators
                           GROUP BY naid)
SELECT nucacid_data.naid AS naid
     , nucacid_data.tid AS tid
     , nucacid_data.locid AS locid
     , locations.institution AS institution
     , locations.location AS location
     , local_1.localid AS localid_1
     , local_2.localid AS localid_2
     , tissue_data.uiid AS uiid
     , unique_indivs.popid AS popid
     , unique_indivs.individ AS individ
     , biograph.sname AS sname
     , nucacid_data.name_on_tube AS name_on_tube
     , nucacid_data.nucacid_type AS nucacid_type
     , tissue_data.tissue_type AS tissue_type
     , nucacid_data.creation_date AS creation_date
     , concat_creators.created_by AS created_by
     , nucacid_data.creation_method AS creation_method
     , nucacid_sources.source_naid AS source_na
     , nucacid_sources.relationship AS source_na_relationship
     , nucacid_data.initial_vol_ul AS initial_vol_ul
     , nucacid_data.actual_vol_ul AS actual_vol_ul
     , nucacid_data.actual_vol_date AS actual_vol_date
     , nucacid_data.notes AS notes
  FROM nucacid_data
  JOIN locations
    ON locations.locid = nucacid_data.locid
  JOIN tissue_data
    ON tissue_data.tid = nucacid_data.tid
  JOIN unique_indivs
    ON unique_indivs.uiid = tissue_data.uiid
  LEFT JOIN biograph
    ON biograph.bioid::text = unique_indivs.individ
       AND unique_indivs.popid = 1
  LEFT JOIN nucacid_local_ids AS local_1
    ON local_1.naid = nucacid_data.naid
       AND local_1.institution = 1
  LEFT JOIN nucacid_local_ids AS local_2
    ON local_2.naid = nucacid_data.naid
       AND local_2.institution = 2
  LEFT JOIN nucacid_sources
    ON nucacid_sources.naid = nucacid_data.naid
  LEFT JOIN concat_creators
    ON concat_creators.naid = nucacid_data.naid
;


Figure 61. Entity Relationship Diagram of the NUCACIDS View

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


The NUCACIDS_W_CONC View

Figure 62. Query Defining the NUCACIDS_W_CONC View


WITH last_quants AS (SELECT DISTINCT
                            naid
                          , conc_method
                          , last_value(pg_ul) OVER w AS last_pg_ul
                          , last_value(conc_date) OVER w AS lastdate
                       FROM nucacid_conc_data
                       WHERE conc_date IS NOT NULL
                       WINDOW w AS (PARTITION BY naid, conc_method
                                    ORDER BY conc_date
                                      RANGE BETWEEN UNBOUNDED PRECEDING
                                        AND UNBOUNDED FOLLOWING))
   , concat_creators AS (SELECT naid
                              , string_agg(creator, '/' ORDER BY naid, nacrid) AS created_by
                           FROM nucacid_creators
                           GROUP BY naid)

SELECT nucacid_data.naid AS naid
     , nucacid_data.tid AS tid
     , nucacid_data.locid AS locid
     , locations.institution AS institution
     , locations.location AS location
     , local_1.localid AS localid_1
     , local_2.localid AS localid_2
     , tissue_data.uiid AS uiid
     , unique_indivs.popid AS popid
     , unique_indivs.individ AS individ
     , biograph.sname AS sname
     , nucacid_data.name_on_tube AS name_on_tube
     , nucacid_data.nucacid_type AS nucacid_type
     , tissue_data.tissue_type AS tissue_type
     , nucacid_data.creation_date AS creation_date
     , concat_creators.created_by AS created_by
     , nucacid_data.creation_method AS creation_method
     , nucacid_sources.source_naid AS source_na
     , nucacid_sources.relationship AS source_na_relationship
     , nucacid_data.initial_vol_ul AS initial_vol_ul
     , nucacid_data.actual_vol_ul AS actual_vol_ul
     , nucacid_data.actual_vol_date AS actual_vol_date
     , nucacid_data.notes AS notes
     , qpcr.last_pg_ul AS qpcr_pg_ul
     , qpcr.lastdate AS qpcr_lastdate
     , (nanodrop.last_pg_ul / 1000)::numeric(10,4) AS nanodrop_ng_ul
     , nanodrop.lastdate AS nanodrop_lastdate
     , (qubit.last_pg_ul / 1000)::numeric(10,4) AS qubit_ng_ul
     , qubit.lastdate AS qubit_lastdate
     , (bioanalyzer.last_pg_ul / 1000)::numeric(10,4) AS bioanalyzer_ng_ul
     , bioanalyzer.lastdate AS bioanalyzer_lastdate
     , (quantit.last_pg_ul / 1000)::numeric(10,4) AS quantit_ng_ul
     , quantit.lastdate AS quantit_lastdate
  FROM nucacid_data
  JOIN locations
    ON locations.locid = nucacid_data.locid
  JOIN tissue_data
    ON tissue_data.tid = nucacid_data.tid
  JOIN unique_indivs
    ON unique_indivs.uiid = tissue_data.uiid
  LEFT JOIN biograph
    ON biograph.bioid::text = unique_indivs.individ
       AND unique_indivs.popid = 1
  LEFT JOIN nucacid_local_ids AS local_1
    ON local_1.naid = nucacid_data.naid
       AND local_1.institution = 1
  LEFT JOIN nucacid_local_ids AS local_2
    ON local_2.naid = nucacid_data.naid
       AND local_2.institution = 2
  LEFT JOIN nucacid_sources
    ON nucacid_sources.naid = nucacid_data.naid
  LEFT JOIN concat_creators
    ON concat_creators.naid = nucacid_data.naid
  LEFT JOIN last_quants AS qpcr
    ON qpcr.conc_method = 1
       AND qpcr.naid = nucacid_data.naid
  LEFT JOIN last_quants AS nanodrop
    ON nanodrop.conc_method = 2
       AND nanodrop.naid = nucacid_data.naid
  LEFT JOIN last_quants AS qubit
    ON qubit.conc_method = 3
       AND qubit.naid = nucacid_data.naid
  LEFT JOIN last_quants AS bioanalyzer
    ON bioanalyzer.conc_method = 4
       AND bioanalyzer.naid = nucacid_data.naid
  LEFT JOIN last_quants AS quantit
    ON quantit.conc_method = 5
       AND quantit.naid = nucacid_data.naid
;


Figure 63. Entity Relationship Diagram of the NUCACIDS_W_CONC View

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


The PARENTS View

Figure 64. 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 65. 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 66. 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 67. 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 68. 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 69. 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 70. 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 71. 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 72. Query Defining the POTENTIAL_DADS View


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

  FROM maternities
       JOIN biograph AS pdads
            ON (pdads.sname
                IN (SELECT dadmembers.sname
                      FROM members AS dadmembers
                           JOIN members AS mommembers
                             ON (mommembers.date = dadmembers.date
                                 AND mommembers.supergroup
                                     = dadmembers.supergroup)
                      WHERE dadmembers.sname = pdads.sname
                            AND dadmembers.date < maternities.zdate
                            AND dadmembers.date >= maternities.zdate - 5
                            AND mommembers.sname = maternities.mom
                            AND mommembers.date < maternities.zdate
                            AND mommembers.date >= maternities.zdate - 5))

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


Figure 73. 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 74. 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 75. 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 76. 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 PROPORTIONAL_RANKS View

Figure 77. Query Defining the PROPORTIONAL_RANKS View


WITH num_indivs AS (
  SELECT ranks.rnkdate
       , ranks.grp
       , ranks.rnktype
       , count(*) AS num_members
    FROM ranks
    GROUP BY ranks.rnkdate, ranks.grp, ranks.rnktype)

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


Figure 78. Entity Relationship Diagram of the PROPORTIONAL_RANKS View

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


The QUADS View

Figure 79. 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 80. 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 81. 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 82. 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 83. 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 xysource
     , 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_data.predator AS predator
     , swerb_loc_data.loc AS loc
     , swerb_loc_data.adcode AS adcode
     , adcodes.adn AS adn
     , swerb_loc_data.loc_status AS loc_status
     , 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 84. 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 85. 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 86. 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 87. 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 88. 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 89. 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.loc_status AS loc_status
     , swerb_loc_data.adtime AS time
  FROM swerb_loc_data
       JOIN adcodes ON (adcodes.adcode = swerb_loc_data.adcode)
;


Figure 90. 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 91. 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 92. 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 TISSUES View

Figure 93. Query Defining the TISSUES View


SELECT tissue_data.tid AS tid
     , tissue_data.locid
     , locations.institution AS institution
     , locations.location AS location
     , local_1.localid AS localid_1
     , local_2.localid AS localid_2
     , tissue_data.uiid AS uiid
     , unique_indivs.popid AS popid
     , unique_indivs.individ AS individ
     , biograph.sname AS sname
     , tissue_data.name_on_tube AS name_on_tube
     , tissue_data.collection_date AS collection_date
     , tissue_data.collection_time AS collection_time
     , tissue_data.tissue_type AS tissue_type
     , tissue_data.storage_medium AS storage_medium
     , tissue_data.misid_status AS misid_status
     , tissue_data.notes AS notes
  FROM tissue_data
  JOIN locations
    ON locations.locid = tissue_data.locid
  JOIN unique_indivs
    ON unique_indivs.uiid = tissue_data.uiid
  LEFT JOIN biograph
    ON biograph.bioid::text = unique_indivs.individ
       AND unique_indivs.popid = 1
  LEFT JOIN tissue_local_ids AS local_1
    ON local_1.tid = tissue_data.tid
       AND local_1.institution = 1
  LEFT JOIN tissue_local_ids AS local_2
    ON local_2.tid = tissue_data.tid
       AND local_2.institution = 2
;


Figure 94. Entity Relationship Diagram of the TISSUES View

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


The ULNA_STATS View

Figure 95. 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 96. 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 97. 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 98. 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 99. 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 100. 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.


The WP_DETAILS_AFFECTEDPARTS View

Figure 101. Query Defining the WP_DETAILS_AFFECTEDPARTS View


SELECT wp_details.wpdid                         AS wpdid
     , wp_reports.wprid                         AS wprid
     , wp_reports.wid                           AS wid
     , wp_details.woundpathcode                 AS woundpathcode
     , wp_details.cluster                       AS cluster
     , wp_details.maxdimension                  AS maxdimension
     , wp_details.impairslocomotion             AS impairslocomotion
     , wp_details.infectionsigns                AS infectionsigns
     , wp_details.notes                         AS detailnotes
     , wp_affectedparts.wpaid                   AS wpaid
     , wp_affectedparts.wpdid                   AS bodypart_wpdid
     , wp_affectedparts.bodypart                AS bodypart
     , bodyparts.bodyside                       AS bodyside
     , bodyparts.innerouter                     AS innerouter
     , bodyparts.bodyregion                     AS bodyregion
     , wp_affectedparts.quantity_affecting_part AS quantity_affecting_part
  FROM wp_reports
  JOIN wp_details
    ON wp_details.wprid = wp_reports.wprid
  LEFT JOIN wp_affectedparts
    ON wp_affectedparts.wpdid = wp_details.wpdid
  LEFT JOIN bodyparts
    ON bodyparts.bpid = wp_affectedparts.bodypart
;


Figure 102. Entity Relationship Diagram of the WP_DETAILS_AFFECTEDPARTS View

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


The WP_HEALS View

Figure 103. Query Defining the WP_HEALS View


WITH concat_observers AS (SELECT wprid
                               , string_agg(observer, '/' ORDER BY wpoid) as observers
                            FROM wp_observers
                            GROUP BY wprid)
SELECT wp_reports.wprid AS wprid
     , wp_reports.wid AS wid
     , wp_reports.date AS reportdate
     , wp_reports.time AS reporttime
     , concat_observers.observers AS observers
     , wp_reports.sname AS sname
     , wp_reports.grp AS grp
     , wp_reports.observercomments AS observercomments
     , wp_reports.reportstate AS reportstate
     , wp_details.wpdid AS wpdid
     , wp_details.woundpathcode AS woundpathcode
     , wp_details.cluster AS cluster
     , wp_details.maxdimension AS maxdimension
     , wp_details.impairslocomotion AS impairslocomotion
     , wp_details.infectionsigns AS infectionsigns
     , wp_details.notes AS detailnotes
     , wp_affectedparts.wpaid AS wpaid
     , wp_affectedparts.bodypart AS bodypart
     , bodyparts.bodyside AS bodyside
     , bodyparts.innerouter AS innerouter
     , bodyparts.bodyregion AS bodyregion
     , wp_affectedparts.quantity_affecting_part AS quantity_affecting_part
     , wp_healupdates.wphid AS wphid
     , wp_healupdates.date AS healdate
     , wp_healupdates.healstatus AS healstatus
     , wp_healupdates.notes AS healnotes
  FROM wp_healupdates
  LEFT JOIN wp_affectedparts
    ON wp_affectedparts.wpaid = wp_healupdates.wpaid
  LEFT JOIN bodyparts
    ON bodyparts.bpid = wp_affectedparts.bodypart
  LEFT JOIN wp_details
    ON wp_details.wpdid = COALESCE(wp_affectedparts.wpdid, wp_healupdates.wpdid)
  LEFT JOIN wp_reports
    ON wp_reports.wprid = COALESCE(wp_details.wprid, wp_healupdates.wprid)
  LEFT JOIN concat_observers
    ON concat_observers.wprid = wp_reports.wprid
;


Figure 104. Entity Relationship Diagram of the WP_HEALS View, Overall

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


Figure 105. Entity Relationship Diagram of the WP_HEALS View for rows with an update to a wound/pathology report

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


Figure 106. Entity Relationship Diagram of the WP_HEALS View for rows with an update to a wound/pathology cluster

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


Figure 107. Entity Relationship Diagram of the WP_HEALS View for rows with an update to an affected body part

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


The WP_REPORTS_OBSERVERS View

Figure 108. Query Defining the WP_REPORTS_OBSERVERS View


WITH concat_observers AS (SELECT wprid
                               , string_agg(observer, '/' ORDER BY wpoid) as observers
                            FROM wp_observers
                            GROUP BY wprid)
SELECT wp_reports.wprid            AS wprid
     , wp_reports.wid              AS wid
     , wp_reports.date             AS date
     , wp_reports.time             AS time
     , concat_observers.observers  AS observers
     , wp_reports.sname            AS sname
     , wp_reports.grp              AS grp
     , wp_reports.observercomments AS observercomments
     , wp_reports.reportstate      AS reportstate
  FROM wp_reports
  LEFT JOIN concat_observers
    ON concat_observers.wprid = wp_reports.wprid
;


Figure 109. Entity Relationship Diagram of the WP_REPORTS_OBSERVERS View

If we could we would display here the diagram showing how the WP_REPORTS_OBSERVERS 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 110. 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 111. 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 112. 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 113. 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 114. 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 115. 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 116. 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 117. 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 118. 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 119. 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 120. 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 121. 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 122. 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 123. 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 124. 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 125. 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 126. 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 127. 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 128. 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 129. 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 130. 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 131. 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 132. 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 133. 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 134. 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 135. 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 136. 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 137. 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 138. 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 139. 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.