A Technical Specification Summary
Anne Ndeti Hubbard
DocBook formattingKarl O. Pinc
DocBook formattingMarch 25, 2009
Table of Contents
- Babase Summarized
- The Babase ER Diagrams
- The Babase Views
- The ACTOR_ACTEES and ACTOR_ACTEES_SORTED Views
- The ACTOR_ACTEES_EX and ACTOR_ACTEES_EX_SORTED Views
- 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 DEMOG_CENSUS and DEMOG_CENSUS_SORTED Views
- The DENT_CODES View
- The DENT_SITES View
- The INTERACT and INTERACT_SORTED Views
- The MATERNITIES View
- The MIN_MAXS View
- The MIN_MAXS_SORTED View
- The MPI_EVENTS View
- The MTD_CYCLES View
- The PARENTS View
- The PCSKINS_SORTED View
- The PCV_STATS View
- The POINTS and POINTS_SORTED Views
- The POTENTIAL_DADS View
- The QUADS View
- The SEXSKINS_CYCLES and SEXSKINS_CYCLES_SORTED Views
- The SWERB view
- The SWERB_DEPARTS view
- The SWERB_LOC_GPS_XY view
- The SWERB_LOCS view
- The SWERB_UPLOAD view
- The ULNA_STATS View
- The TESTES_ARC_STATS View
- The TESTES_DIAM_STATS View
- Views Which Add Gid To Tables
- The BIRTH_GRP View
- The ENTRYDATE_GRP View
- The STATDATE_GRP View
- The CONSORTDATES_GRP View
- The CYCGAPDAYS_GRP View
- The CYCGAPS_GRP View
- The CYCSTATS_GRP View
- The DARTINGS_GRP View
- The DISPERSEDATES_GRP View
- The MATUREDATES_GRP View
- The MDINTERVALS_GRP View
- The MMINTERVALS_GRP View
- The PCSKINS_GRP View
- The RANKDATES_GRP View
- The REPSTATS_GRP View
List of Figures
- 1. Key to the Babase Entity Relationship Diagrams
- 2. Babase Group Membership Entity Relationship Diagram
- 3. Babase Life Events Entity Relationship Diagram
- 4. Babase Sexual Cycle Entity Relationship Diagram
- 5. Babase Sexual Cycle Day-To-Day Tables Entity Relationship Diagram
- 6. Babase Social Interactions Entity Relationship Diagram
- 7. Babase Multiparty Interactions Entity Relationship Diagram
- 8. Babase Darting Logistics and Morphology Entity and Relationship Diagram
- 9. Babase Darting Physiology Entity and Relationship Diagram
- 10. Babase Darting Samples Entity and Relationship Diagram
- 11. Babase Darting Teeth and Ticks Entity and Relationship Diagram
- 12. Babase SWERB Core Tables Entity Relationship Diagram
- 13. Babase SWERB Grove/Waterhole Location Tables Entity Relationship Diagram
- 14. Babase Manual Weather Data Entity Relationship Diagram
- 15. Babase WeatherHawk Data Entity Relationship Diagram
- 16. Warning Sub-System Entity Relationship Diagram
- 17. Query Defining the ACTOR_ACTEES View
- 18. Entity Relationship Diagram of the ACTOR_ACTEES View
- 19. Query Defining the ACTOR_ACTEES_EX View
- 20. Entity Relationship Diagram of the ACTOR_ACTEES_EX View
- 21. Query Defining the ANESTH_STATS View
- 22. Entity Relationship Diagram of the ANESTH_STATS View
- 23. Query Defining the BODYTEMP_STATS View
- 24. Entity Relationship Diagram of the BODYTEMP_STATS View
- 25. Query Defining the CENSUS_DEMOG View
- 26. Entity Relationship Diagram of the CENSUS_DEMOG View
- 27. Query Defining the CHEST_STATS View
- 28. Entity Relationship Diagram of the CHEST_STATS View
- 29. Query Defining the CROWNRUMP_STATS View
- 30. Entity Relationship Diagram of the CROWNRUMP_STATS View
- 31. Query Defining the CYCLES_SEXSKINS View
- 32. Entity Relationship Diagram of the CYCLES_SEXSKINS View
- 33. Query Defining the CYCPOINTS_CYCLES View
- 34. Entity Relationship Diagram of the CYCPOINTS_CYCLES View
- 35. Query Defining the DEMOG_CENSUS View
- 36. Entity Relationship Diagram of the DEMOG_CENSUS View
- 37. Query Defining the DENT_CODES View
- 38. Entity Relationship Diagram of the DENT_CODES View
- 39. Query Defining the DENT_SITES View
- 40. Entity Relationship Diagram of the DENT_SITES View
- 41. Query Defining the INTERACT View
- 42. Entity Relationship Diagram of the INTERACT View
- 43. Query Defining the MATERNITIES View
- 44. Entity Relationship Diagram of the MATERNITIES View
- 45. Query Defining the MIN_MAXS View
- 46. Entity Relationship Diagram of the MIN_MAXS View
- 47. Query Defining the MIN_MAXS_SORTED View
- 48. Entity Relationship Diagram of the MIN_MAXS_SORTED View
- 49. Query Defining the MPI_EVENTS View
- 50. Entity Relationship Diagram of the MPI_EVENTS View
- 51. Query Defining the MTD_CYCLES View
- 52. Entity Relationship Diagram of the MTD_CYCLES View
- 53. Query Defining the PARENTS View
- 54. Entity Relationship Diagram of the PARENTS View
- 55. Query Defining the PCSKINS_SORTED View
- 56. Entity Relationship Diagram of the PCSKINS_SORTED View
- 57. Query Defining the PCV_STATS View
- 58. Entity Relationship Diagram of the PCV_STATS View
- 59. Query Defining the POINTS View
- 60. Entity Relationship Diagram of the POINTS View
- 61. Query Defining the POTENTIAL_DADS View
- 62. Entity Relationship Diagram of the foundation of the POTENTIAL_DADS View
- 63. Entity Relationship Diagram of that portion of the POTENTIAL_DADS View which places the mother and potential father in the same group during the fertile period
- 64. Entity Relationship Diagram of that portion of the POTENTIAL_DADS View having easily computed columns
- 65. Entity Relationship Diagram of that portion of the POTENTIAL_DADS View involving social interactions
- 66. Query Defining the QUADS View
- 67. Entity Relationship Diagram of the QUADS View
- 68. Query Defining the SEXSKINS_CYCLES View
- 69. Entity Relationship Diagram of the SEXSKINS_CYCLES View
- 70. Query Defining the SWERB View
- 71. Entity Relationship Diagram of the SWERB View
- 72. Query Defining the SWERB_DEPARTS View
- 73. Entity Relationship Diagram of the SWERB_DEPARTS View
- 74. Query Defining the SWERB_LOC_GPS_XY View
- 75. Entity Relationship Diagram of the SWERB_LOC_GPS_XY View
- 76. Query Defining the SWERB_LOCS View
- 77. Entity Relationship Diagram of the SWERB_LOCS View
- 78. Query Defining the SWERB_UPLOAD View
- 79. Entity Relationship Diagram of the SWERB_UPLOAD View
- 80. Query Defining the ULNA_STATS View
- 81. Entity Relationship Diagram of the ULNA_STATS View
- 82. Query Defining the TESTES_ARC_STATS View
- 83. Entity Relationship Diagram of the TESTES_ARC_STATS View
- 84. Query Defining the TESTES_DIAM_STATS View
- 85. Entity Relationship Diagram of the TESTES_DIAM_STATS View
- 86. Query Defining the BIRTH_GRP View
- 87. Entity Relationship Diagram of the BIRTH_GRP View
- 88. Query Defining the ENTRYDATE_GRP View
- 89. Entity Relationship Diagram of the ENTRYDATE_GRP View
- 90. Query Defining the STATDATE_GRP View
- 91. Entity Relationship Diagram of the STATDATE_GRP View
- 92. Query Defining the CONSORTDATES_GRP View
- 93. Entity Relationship Diagram of the CONSORTDATES_GRP View
- 94. Query Defining the CYCGAPDAYS_GRP View
- 95. Entity Relationship Diagram of the CYCGAPDAYS_GRP View
- 96. Query Defining the CYCGAPS_GRP View
- 97. Entity Relationship Diagram of the CYCGAPS_GRP View
- 98. Query Defining the CYCSTATS_GRP View
- 99. Entity Relationship Diagram of the CYCSTATS_GRP View
- 100. Query Defining the DARTINGS_GRP View
- 101. Entity Relationship Diagram of the DARTINGS_GRP View
- 102. Query Defining the DISPERSEDATES_GRP View
- 103. Entity Relationship Diagram of the DISPERSEDATES_GRP View
- 104. Query Defining the MATUREDATES_GRP View
- 105. Entity Relationship Diagram of the MATUREDATES_GRP View
- 106. Query Defining the MDINTERVALS_GRP View
- 107. Entity Relationship Diagram of the MDINTERVALS_GRP View
- 108. Query Defining the MMINTERVALS_GRP View
- 109. Entity Relationship Diagram of the MMINTERVALS_GRP View
- 110. Query Defining the PCSKINS_GRP View
- 111. Entity Relationship Diagram of the PCSKINS_GRP View
- 112. Query Defining the RANKDATES_GRP View
- 113. Entity Relationship Diagram of the RANKDATES_GRP View
- 114. Query Defining the REPSTATS_GRP View
- 115. Entity Relationship Diagram of the REPSTATS_GRP View
List of Tables
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 | |
| Table | One row for each |
| ALTERNATE_SNAMES | rescinded sname |
| BIOGRAPH | animal, including fetuses |
| CENSUS | day each individual is (or is not) observed in a group |
| CONSORTDATES | male who has a known first consortship |
| DEMOG | mention of an individual's presence in a group within a field textual note |
| DISPERSEDATES | male who has left his maternal study group |
| GROUPS | group (including solitary males) |
| MATUREDATES | individual who is sexually mature |
| RANKDATES | individual[a] who has attained adult rank |
| Analyzed: Group Membership and Life Events | |
| Table | One row for each |
| DAD_DATA | offspring having a paternity analysis |
| MEMBERS | day each individual is alive |
| RANKS | month each individual is ranked in each group |
| Sexual Cycles | |
| Table | One row for each |
| CYCGAPS | female for each initiation or cessation of a continuous period of observation |
| CYCLES | female's cycle (complete or not) |
| CYCPOINTS | Mdate (menses), Tdate (turgesence onset), or Ddate (deturgesence onset) date of each female |
| PCSKINS | PCS color of each female |
| PREGS | time a female becomes pregnant |
| SEXSKINS | sexskin measurement of each female |
| The Sexual Cycle Day-By-Day Tables | |
| Table | One row for each |
| CYCGAPDAYS | female for each day within a period during which there is not continuous observation |
| CYCSTATS | day each female is cycling -- by M, T and Ddates |
| MDINTERVALS | day each female is cycling and is between M and Ddates |
| MMINTERVALS | day each female is cycling -- by Mdates |
| REPSTATS | day each female has a known reproductive state |
| Social and Multiparty Interactions | |
| Table | One row for each |
| ALLMISCS | “free form” all-occurrences datum |
| CONSORTS | multiparty dispute over a consortship |
| FPOINTS | point observation of a mature female |
| INTERACT_DATA | interaction between individuals |
| MPIS | collection of multiparty interactions |
| MPI_DATA | single dyadic interaction of a multiparty interaction collection |
| MPI_PARTS | participant in a dyadic interaction of a multiparty interaction collection |
| PARTS | participant in each interaction |
| POINT_DATA | individual point observation |
| NEIGHBORS | neighbor recorded in each point sample |
| SAMPLES | all-occurrences sample |
| Darting | |
| Table | One row for each |
| ANESTHS | time additional sedation is administered to a darted individual |
| BODYTEMPS | body temperature measurement taken of a darted individual |
| CHESTS | chest circumference measurement made of a darted individual |
| CROWNRUMPS | crown to rump measurement made of a darted individual |
| DARTINGS | darting of an animal when data was collected |
| DPHYS | darting event during which physiological measurements were taken |
| DSAMPLES | darting event during which tissue samples were taken |
| DTCULTURES | darting event during which tissue samples were taken for culture |
| HUMERUSES | humerous length measurement made of a darted individual |
| PCVS | packed cell volume measurement taken from a darted individual |
| TEETH | possible tooth site within the mouth on which data was collected for every darting event during which dentition data was collected |
| TESTES_ARC | every testicle width/length measurement recorded, as measured along a portion of the circumference |
| TESTES_DIAM | every testicle width/length measurement recorded, as measured along the diameter |
| TICKS | darting event during which data on ticks and other parasites were recorded |
| ULNAS | ulna length measurement made of a darted individual |
| SWERB Data (Group-level Geolocation Data) | |
| Table | One row for each |
| AERIALS | aerial photo used for map quadrant specification |
| GPS_UNITS | GPS device |
| QUAD_DATA | SWERB map quadrant |
| SWERB_BES | uninterrupted bout of group-level observation |
| SWERB_DATA | event related to group-level geolocation |
| SWERB_DEPARTS_DATA | departure from camp of a observation team which collected SWERB data |
| SWERB_GWS | geolocated physical object (grove or waterhole) |
| SWERB_GW_LOC_DATA | recorded location of a geolocated physical object (grove or waterhole) |
| SWERB_LOC_DATA | observation of a group at a time at a geolocated physical object |
| SWERB_LOC_GPS | observation of a group at a time at a geolocated physical object made using gps units and a protocol that requires 2 waypoint readings |
| SWERB_OBSERVERS | departure from camp of an observer who drove or collected SWERB data |
| Weather Data | |
| Table | One row for each |
| RAINGAUGES | rain gauge reading |
| RGSETUPS | rain gauge installation |
| TEMPMAXS | maximum temperature reading |
| TEMPMINS | minimum temperature reading |
| WHAWKS | weather reading reported by the WeatherHawk instruments |
| WREADINGS | manually collected meteorological data collection event |
Table 2. The Warning Sub-System Tables
| Table | One row for each |
|---|---|
| INTEGRITY_QUERIES | query used to discover data integrity problems |
| INTEGRITY_WARNINGS | data integrity problem discovered by the warning sub-system |
Table 3. The Babase Support Tables
| General Support Tables | |||
| Table | Id Column | Related Column(s) | One entry for every possible choice of... |
| OBSERVERS | Initials | SAMPLES.Observer, WREADINGS.WRperson, RGSETUPS.RGSPerson, CROWNRUMPS.CRobserver, CHESTS.Chobserver, ULNAS.Ulobserver, HUMERUSES.Huobserver, SWERB_OBSERVERS.Observer | person who records information |
| OBSERVER_ROLES | Initials | OBSERVERS.Role, OBSERVERS.SWERB_Observer_Role, OBSERVERS.SWERB_Driver_Role, SWERB_OBSERVERS.Role | way in which a person can be involved in the data collection process |
| UNKSNAMES | Unksname | NEIGHBORS.Unksname and the SWERB_UPLOAD view | problem 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 | |||
| Table | Id Column | Related Column(s) | One entry for every possible choice of... |
| BSTATUSES | Bstatus | BIOGRAPH.Bstatus | birthday estimation accuracy |
| CONFIDENCES | Dcauseconfidence | BIOGRAPH.Dcauseconfidence, DISPERSEDATES.Dispconfidence, BIOGRAPH.Matgrpconfidence | degree of certitude in cause of death, disperse date assignment, or maternal group assignment |
| DAD_SOFTWARE | Software | DAD_DATA.Software | software package used to perform genetic paternity analysis |
| DCAUSES | Dcause | BIOGRAPH.Dcause | cause of death |
| DEMOG_REFERENCES | Reference | DEMOG.Reference | data source for demography notes |
| MSTATUSES | Mstatus | MATUREDATES.Matured, RANKDATES.Ranked | maturity marker date estimation process |
| RNKTYPES | Rnktype | RANKS.Rnktype | rank ordering assigned to subject and month |
| STATUSES | Status | BIOGRAPH.Status | baboon alive at last observation |
| Social and Multiparty Interactions | |||
| Table | Id Column | Related Column(s) | One entry for every possible choice of... |
| ACTIVITIES | Activity | POINT_DATA.Activity | activity classification |
| ACTS | Act | INTERACT_DATA.Act | interaction classification |
| DATA_STRUCTURES | Data_Structure | SETUPIDS.Data_Structure | version of data structure produced by the palmtops which collect data |
| CONTEXT_TYPES | Context_type | MPIS.Context_type | context in which a multiparty interaction occurs |
| FOODCODES | Foodcode | POINT_DATA.Foodcode | name of a food item |
| FOODTYPES | Ftype | FOODCODES.Ftype | food category |
| KIDCONTACTS | Kidcontact | FPOINTS.Kidcontact | spatial relationship between mother and infant |
| MPIACTS | Mpiact | MPI_DATA.MPIAct | multiparty interaction classification |
| NCODES | Ncode | NEIGHBORS.Ncode | neighbor classification |
| PALMTOPS | Palmtop | SAMPLES.Palmtop | hand-held computer used in the field |
| PARTUNKS | Unksname | MPI_PARTS.Unksname | problem in identifying participant in a multiparty interaction |
| POSTURES | Posture | POINT_DATA.Posture | designated posture |
| PROGRAMIDS | Programid | SAMPLES.Programid | version of program used on the palmtops to collect data |
| SETUPIDS | Setupid | SAMPLES.Setupid | setupfile used on the palmtops to collect data |
| SUCKLES | Suckle | FPOINTS.Kidsuckle | infant suckling activity |
| Sexual Cycles and The Sexual Cycle Day-By-Day Tables | |||
| Table | Id Column | Related Column(s) | One entry for every possible choice of... |
| PCSCOLORS | Color | PCSKINS.Color | paracallosal skin coloration |
| Darting | |||
| Table | Id Column | Related Column(s) | One entry for every possible choice of... |
| BODYPARTS | Bodypart | TICKS.Bodypart, BODYPARTS.Bodyregion | part of the body examined for parasites when darting |
| DRUGS | Drug | DRUGS.Drug | anesthetic drug |
| LYMPHSTATES | Lymphstate | DPHYS.Ringnode, DPHYS.Lingnode, DPHYS.Raxnode, DPHYS.Laxnode, DPHYS.Lsubmandnode, DPHYS.Rsubmandnode | lymph node condition |
| PARASITES | PARASITE | TICKS.Tickkind | parasite species, species developmental stage, or kind of parasite sign counted |
| TCONDITIONS | Tcondition | TEETH.Tcondition | physical condition of a tooth |
| TICKSTATUSES | Tickstatus | TICKS.Tickstatus | parasite count outcome category |
| TOOTHCODES | Tooth | TEETH.Tooth | adult or deciduous tooth |
| TOOTHSITES | Toothsite | TOOTHCODES.Toothsite | dental site within the mouth |
| TSTATES | Tstate | TEETH.Tstate | tooth “presence” |
| SWERB Data (Group-level Geolocation Data) | |||
| Table | Id Column | Related Column(s) | One entry for every possible ... |
| ADCODES | ADCode | SWERB_LOC_DATA.ADcode | relationship between baboon groups and sleeping groves. |
| SWERB_LOC_CONFS (SWERB sleeping grove Confidences) | Conf | SWERB_LOC_DATA.Conf | level of confidence in sleeping grove on record. |
| SWERB_TIME_SOURCES | Source | SWERB_BES.Bsource, SWERB_BES.Esource | data source used to estimate beginning and ending of observation bouts |
| SWERB_XYSOURCES (SWERB Time Sources) | Source | SWERB_GW_LOC_DATA.XYSource | data source used to obtain XY coordinates |
| Weather Data | |||
| Table | Id Column | Related Column(s) | One entry for every possible choice of... |
| WSTATIONS | Wstation | WREADINGS.Wstation | meteorological data collection location or device |
Table 4. The Warning Sub-System Support Tables
| Table | Id Column | Related Column(s) | One entry for every possible choice of... |
|---|---|---|---|
| IQTYPES | IQType | INTEGRITY_QUERIES.Type | kind of problem with data integrity |
| WARNING_REMARKS | WRID | INTEGRITY_WARNINGS.Category | remark which might apply to more than one instance of questionable database integrity |
Table 5. The Babase Views
| Group Membership and Life Events | |||
| View | One row for each | Purpose | Tables/Views used |
| CENSUS_DEMOG | CENSUS row | Maintenance of CENSUS rows that are extended with DEMOG information. | CENSUS, DEMOG |
| CENSUS_DEMOG_SORTED | CENSUS row | Maintenance of CENSUS_DEMOG rows in a pre-sorted fashion. | CENSUS, DEMOG |
| CYCPOINTS_CYCLES | CYCPOINTS row | Maintenance of CYCPOINTS rows that are extended with CYCLES information. | CYCLES, CYCPOINTS |
| CYCPOINTS_CYCLES_SORTED | CYCPOINTS row | The CYCPOINTS_CYCLES view sorted by CYCLES.Sname, by CYCPOINTS.Date. | CYCLES, CYCPOINTS |
| DEMOG_CENSUS | DEMOG row | Maintenance of DEMOG rows. | CENSUS, DEMOG |
| DEMOG_CENSUS_SORTED | CENSUS row | Maintenance of DEMOG_CENSUS rows in a pre-sorted fashion. | CENSUS, DEMOG |
| GROUPS_HISTORY | GROUPS row | Depiction of GROUPS rows in a more human-readable format. | GROUPS |
| PARENTS | BIOGRAPH
row for which there is either a row in MATERNITIES with a
record of the individual's mother or there is a row in DAD_DATA with a record
of the individual's father -- 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 period | Research into paternity, especially the selection of potential fathers for further genetic testing. | MATERNITIES, MEMBERS (multiple times), ACTOR_ACTEES (multiple times), BIOGRAPH, RANKDATES, MATUREDATES |
| Sexual Cycles | |||
| View | One row for each | Purpose | Tables/Views used |
| CYCLES_SEXSKINS | CYCLES row | Maintenance of SEXSKINS rows. | CYCLES, SEXSKINS |
| CYCLES_SEXSKINS_SORTED | CYCLES row | The CYCLES_SEXSKINS view sorted by CYCLES.Sname, by SEXSKINS.Date. | CYCLES, SEXSKINS |
| MATERNITIES | birth or fetal loss | Summarizes (completed) reproductive events. | BIOGRAPH, PREGS, CYCPOINTS, CYCLES |
| MTD_CYCLES | CYCLES row | Presents CYCLES together with Mdate, Tdate, and Ddate CYCPOINTS information for a view of an "entire" sexual cycle as a single row. | CYCLES, CYCPOINTS |
| PCSKINS_SORTED | PCSKINS row | Sorts PCSKINS by Sname for ease of maintenance. | PCSKINS |
| SEXSKINS_CYCLES | SEXSKINS row | Maintenance of SEXSKINS rows. | CYCLES, SEXSKINS |
| SEXSKINS_CYCLES_SORTED | SEXSKINS row | The SEXSKINS_CYCLES view sorted by CYCLES.Sname, by SEXSKINS.Date. | CYCLES, SEXSKINS |
| Social and Multiparty Interactions | |||
| View | One row for each | Purpose | Tables/Views used |
| ACTOR_ACTEES | INTERACT_DATA row | Maintenance of social interaction data, INTERACT_DATA rows and POINTS. A view optimized for highest performance when working with these tables. Analysis of social interaction data. | INTERACT_DATA, PARTS |
| ACTOR_ACTEES_EX | INTERACT row | The ACTOR_ACTEES view extended with additional computed columns. | INTERACT, PARTS |
| ACTOR_ACTEES_EX_SORTED | INTERACT row | The ACTOR_ACTEES_EX view sorted for convienience. | INTERACT, PARTS |
| ACTOR_ACTEES_SORTED | INTERACT_DATA row | The ACTOR_ACTEES view sorted to make maintenance convenient. A view optimized for high performance. | INTERACT_DATA, PARTS |
| INTERACT | INTERACT_DATA row | Presents INTERACT_DATA with additional date and time columns that transform the underlying date and time columns in useful and interesting ways. | INTERACT_DATA |
| INTERACT_SORTED | INTERACT_DATA row | Presents the INTERACT view sorted in a fashion expected to ease maintenance. | INTERACT_DATA |
| MPI_EVENTS | MPI_DATA row | Analysis and correction of multiparty interaction data. | MPI_DATA, MPI_PARTS, MPIACTS |
| POINTS | POINT_DATA row | Presents POINT_DATA with the Ptime column transformed into a column that may be useful and interesting. | POINT_DATA |
| POINTS_SORTED | POINTS row | Presents POINTS sorted by Sid, and within that by Ptime. | POINTS |
| SAMPLES_GOFF | SAMPLES row | Presents SAMPLES with an additional column Grp_of_focal, which has the group of the focal at the time of sampling. | SAMPLES |
| Darting | |||
| View | One row for each | Purpose | Tables/Views used |
| ANESTH_STATS | unique ANESTHS.Dartid value -- for each darting during which additional anesthetic was administered | Analysis and “eyeballing” of data involving additional administration of anesthetic when darting. | ANESTHS |
| BODYTEMP_STATS | unique BODYTEMPS.Dartid value -- for each darting having body temperature measurements | Analysis and “eyeballing” of darting body temperature measurements. | BODYTEMPS |
| CHEST_STATS | unique CHESTS.Dartid value -- for each darting having chest circumference measurements | Analysis and “eyeballing” of darting chest circumference measurements. | CHESTS |
| CROWNRUMP_STATS | unique CROWNRUMPS.Dartid value -- for each darting having crown-to-rump measurements | Analysis and “eyeballing” of darting crown-to-rump measurements. | CROWNRUMPS |
| DENT_CODES | unique TEETH.Dartid value -- for each darting with recorded tooth information | Perusal and maintenance of TEETH rows by kind of tooth. | TEETH |
| DENT_SITES | unique TEETH.Dartid value -- for each darting with recorded tooth information | Perusal of TEETH rows by position in the mouth. | TEETH, TOOTHCODES |
| HUMERUS_STATS | unique HUMERUSES.Dartid value -- for each darting having humerus length measurements | Analysis and “eyeballing” of darting humerus length measurements. | HUMERUSES |
| PCV_STATS | unique PCVS.Dartid value -- for each darting having PCV measurements | Analysis and “eyeballing” of darting PCV measurements. | PCVS |
| TESTES_ARC_STATS | unique TESTES_ARC.Dartid value -- for each darting having at least one measurement of testes length or width circumference | Analysis of testes length and width measurements taken during darting. | TESTES_ARC |
| TESTES_DIAM_STATS | unique TESTES_DIAM.Dartid value -- for each darting having at least one measurement of testes length or width diameter | Analysis of testes length and width measurements taken during darting. | TESTES_DIAM |
| ULNA_STATS | unique ULNAS.Dartid value -- for each darting having ulna length measurements | Analysis and “eyeballing” of darting ulna length measurements. | ULNAS |
| SWERB Data (Group-level Geolocation Data) | |||
| View | One row for each | Purpose | Tables/Views used |
| QUADS | QUAD_DATA row | Querying of X, Y coodinates from and maintenance of QUAD_DATA rows. | QUAD_DATA |
| SWERB | SWERB_DATA row -- for every SWERB event, departure from camp excluded | Collects 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_XY | SWERB_DATA row -- for every SWERB event, departure from camp excluded | Separates SWERB_DATA geolocation points into X and Y coordinates for ease of maintenance. | SWERB_DATA |
| SWERB_DEPARTS | SWERB_DEPARTS_DATArow -- for every departure from camp of every observation team, for those observation teams which have collected SWERB data | Collects departure related information spread among several tables and separates geolocation points into X and Y coordinates. | SWERB_DEPARTS_DATA, SWERB_DEPARTS_GPS |
| SWERB_GW_LOCS | SWERB_GW_LOC_DATA row -- for every geolocation of an object, of a grove or waterhole | Collects 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_XY | SWERB_GW_LOC_DATA row -- for every geolocation of an object, of a grove or waterhole | Separates SWERB_GW_LOC_DATA geolocation points into X and Y coordinates for ease of maintenance. | SWERB_GW_LOC_DATA |
| SWERB_LOC_GPS_XY | SWERB_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 data | Separates SWERB_LOC_GPS geolocation points into X and Y coordinates for ease of maintenance. | SWERB_LOC_DATA, ADCODES |
| SWERB_LOCS | SWERB_LOC_DATA row -- for every time a group is observed at a geolocated physical object, usually a grove or waterhole | Presents the relationship between the groups and physical features of the landscape in a more comprehensive manner for simpler querying. | SWERB_LOC_DATA, ADCODES |
| SWERB_UPLOAD | row uploaded into SWERB | This 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 | |||
| View | One row for each | Purpose | Tables/Views used |
| MIN_MAXS | WREADINGS row | Analysis and correlation of manually collected weather data. | WREADINGS TEMPMINS TEMPMAXS RAINGAUGES |
| MIN_MAXS_SORTED | WREADINGS row | The MIN_MAXS view sorted for convienience. | WREADINGS TEMPMINS TEMPMAXS RAINGAUGES |
Table 6. The table_GRP 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.
Figure 17. 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
FROM interact_data
LEFT OUTER JOIN parts AS actor
ON (actor.iid = interact_data.iid AND actor.role = 'R')
LEFT OUTER JOIN parts AS actee
ON (actee.iid = interact_data.iid AND actee.role = 'E');
Figure 19. Query Defining the ACTOR_ACTEES_EX View
SELECT interact.iid AS iid
, interact.sid AS sid
, interact.act AS act
, interact.class AS class
, interact.date AS date
, interact.jdate AS jdate
, interact.start AS start
, interact.startspm AS startspm
, interact.stop AS stop
, interact.stopspm AS stopspm
, interact.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.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.date) AS actee_grp
FROM interact
LEFT OUTER JOIN parts AS actor
ON (actor.iid = interact.iid AND actor.role = 'R')
LEFT OUTER JOIN parts AS actee
ON (actee.iid = interact.iid AND actee.role = 'E');
Figure 21. 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 23. 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 25. 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 27. 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 29. 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 31. 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 33. 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 35. Query Defining the DEMOG_CENSUS View
SELECT census.cenid AS cenid
, census.sname AS sname
, census.date AS date
, census.grp AS grp
, census.status AS status
, census.cen AS cen
, demog.reference AS reference
, demog.comment AS comment
FROM census, demog
WHERE census.cenid = demog.cenid;
Figure 37. Query Defining the DENT_CODES View
SELECT teethdartids.dartid AS dartid
, rum3.rum3tstate AS rum3tstate
, rum3.rum3tcondition AS rum3tcondition
, rum2.rum2tstate AS rum2tstate
, rum2.rum2tcondition AS rum2tcondition
, rum1.rum1tstate AS rum1tstate
, rum1.rum1tcondition AS rum1tcondition
, rup2.rup2tstate AS rup2tstate
, rup2.rup2tcondition AS rup2tcondition
, rup1.rup1tstate AS rup1tstate
, rup1.rup1tcondition AS rup1tcondition
, ruc.ructstate AS ructstate
, ruc.ructcondition AS ructcondition
, rui2.rui2tstate AS rui2tstate
, rui2.rui2tcondition AS rui2tcondition
, rui1.rui1tstate AS rui1tstate
, rui1.rui1tcondition AS rui1tcondition
, lui1.lui1tstate AS lui1tstate
, lui1.lui1tcondition AS lui1tcondition
, lui2.lui2tstate AS lui2tstate
, lui2.lui2tcondition AS lui2tcondition
, luc.luctstate AS luctstate
, luc.luctcondition AS luctcondition
, lup1.lup1tstate AS lup1tstate
, lup1.lup1tcondition AS lup1tcondition
, lup2.lup2tstate AS lup2tstate
, lup2.lup2tcondition AS lup2tcondition
, lum1.lum1tstate AS lum1tstate
, lum1.lum1tcondition AS lum1tcondition
, lum2.lum2tstate AS lum2tstate
, lum2.lum2tcondition AS lum2tcondition
, lum3.lum3tstate AS lum3tstate
, lum3.lum3tcondition AS lum3tcondition
, llm3.llm3tstate AS llm3tstate
, llm3.llm3tcondition AS llm3tcondition
, llm2.llm2tstate AS llm2tstate
, llm2.llm2tcondition AS llm2tcondition
, llm1.llm1tstate AS llm1tstate
, llm1.llm1tcondition AS llm1tcondition
, llp2.llp2tstate AS llp2tstate
, llp2.llp2tcondition AS llp2tcondition
, llp1.llp1tstate AS llp1tstate
, llp1.llp1tcondition AS llp1tcondition
, llc.llctstate AS llctstate
, llc.llctcondition AS llctcondition
, lli2.lli2tstate AS lli2tstate
, lli2.lli2tcondition AS lli2tcondition
, lli1.lli1tstate AS lli1tstate
, lli1.lli1tcondition AS lli1tcondition
, rli1.rli1tstate AS rli1tstate
, rli1.rli1tcondition AS rli1tcondition
, rli2.rli2tstate AS rli2tstate
, rli2.rli2tcondition AS rli2tcondition
, rlc.rlctstate AS rlctstate
, rlc.rlctcondition AS rlctcondition
, rlp1.rlp1tstate AS rlp1tstate
, rlp1.rlp1tcondition AS rlp1tcondition
, rlp2.rlp2tstate AS rlp2tstate
, rlp2.rlp2tcondition AS rlp2tcondition
, rlm1.rlm1tstate AS rlm1tstate
, rlm1.rlm1tcondition AS rlm1tcondition
, rlm2.rlm2tstate AS rlm2tstate
, rlm2.rlm2tcondition AS rlm2tcondition
, rlm3.rlm3tstate AS rlm3tstate
, rlm3.rlm3tcondition AS rlm3tcondition
, drum2.drum2tstate AS drum2tstate
, drum2.drum2tcondition AS drum2tcondition
, drum1.drum1tstate AS drum1tstate
, drum1.drum1tcondition AS drum1tcondition
, druc.dructstate AS dructstate
, druc.dructcondition AS dructcondition
, drui2.drui2tstate AS drui2tstate
, drui2.drui2tcondition AS drui2tcondition
, drui1.drui1tstate AS drui1tstate
, drui1.drui1tcondition AS drui1tcondition
, dlui1.dlui1tstate AS dlui1tstate
, dlui1.dlui1tcondition AS dlui1tcondition
, dlui2.dlui2tstate AS dlui2tstate
, dlui2.dlui2tcondition AS dlui2tcondition
, dluc.dluctstate AS dluctstate
, dluc.dluctcondition AS dluctcondition
, dlum1.dlum1tstate AS dlum1tstate
, dlum1.dlum1tcondition AS dlum1tcondition
, dlum2.dlum2tstate AS dlum2tstate
, dlum2.dlum2tcondition AS dlum2tcondition
, dllm2.dllm2tstate AS dllm2tstate
, dllm2.dllm2tcondition AS dllm2tcondition
, dllm1.dllm1tstate AS dllm1tstate
, dllm1.dllm1tcondition AS dllm1tcondition
, dllc.dllctstate AS dllctstate
, dllc.dllctcondition AS dllctcondition
, dlli2.dlli2tstate AS dlli2tstate
, dlli2.dlli2tcondition AS dlli2tcondition
, dlli1.dlli1tstate AS dlli1tstate
, dlli1.dlli1tcondition AS dlli1tcondition
, drli1.drli1tstate AS drli1tstate
, drli1.drli1tcondition AS drli1tcondition
, drli2.drli2tstate AS drli2tstate
, drli2.drli2tcondition AS drli2tcondition
, drlc.drlctstate AS drlctstate
, drlc.drlctcondition AS drlctcondition
, drlm1.drlm1tstate AS drlm1tstate
, drlm1.drlm1tcondition AS drlm1tcondition
, drlm2.drlm2tstate AS drlm2tstate
, drlm2.drlm2tcondition AS drlm2tcondition
FROM (SELECT teeth.dartid
FROM teeth
GROUP BY teeth.dartid)
AS teethdartids
LEFT OUTER JOIN
(SELECT teeth.dartid AS rum3dartid
, teeth.tstate AS rum3tstate
, teeth.tcondition AS rum3tcondition
FROM teeth
WHERE teeth.tooth = 'rum3')
AS rum3
ON rum3.rum3dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS rum2dartid
, teeth.tstate AS rum2tstate
, teeth.tcondition AS rum2tcondition
FROM teeth
WHERE teeth.tooth = 'rum2')
AS rum2
ON rum2.rum2dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS rum1dartid
, teeth.tstate AS rum1tstate
, teeth.tcondition AS rum1tcondition
FROM teeth
WHERE teeth.tooth = 'rum1')
AS rum1
ON rum1.rum1dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS rup2dartid
, teeth.tstate AS rup2tstate
, teeth.tcondition AS rup2tcondition
FROM teeth
WHERE teeth.tooth = 'rup2')
AS rup2
ON rup2.rup2dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS rup1dartid
, teeth.tstate AS rup1tstate
, teeth.tcondition AS rup1tcondition
FROM teeth
WHERE teeth.tooth = 'rup1')
AS rup1
ON rup1.rup1dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS rucdartid
, teeth.tstate AS ructstate
, teeth.tcondition AS ructcondition
FROM teeth
WHERE teeth.tooth = 'ruc')
AS ruc
ON ruc.rucdartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS rui2dartid
, teeth.tstate AS rui2tstate
, teeth.tcondition AS rui2tcondition
FROM teeth
WHERE teeth.tooth = 'rui2')
AS rui2
ON rui2.rui2dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS rui1dartid
, teeth.tstate AS rui1tstate
, teeth.tcondition AS rui1tcondition
FROM teeth
WHERE teeth.tooth = 'rui1')
AS rui1
ON rui1.rui1dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS lui1dartid
, teeth.tstate AS lui1tstate
, teeth.tcondition AS lui1tcondition
FROM teeth
WHERE teeth.tooth = 'lui1')
AS lui1
ON lui1.lui1dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS lui2dartid
, teeth.tstate AS lui2tstate
, teeth.tcondition AS lui2tcondition
FROM teeth
WHERE teeth.tooth = 'lui2')
AS lui2
ON lui2.lui2dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS lucdartid
, teeth.tstate AS luctstate
, teeth.tcondition AS luctcondition
FROM teeth
WHERE teeth.tooth = 'luc')
AS luc
ON luc.lucdartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS lup1dartid
, teeth.tstate AS lup1tstate
, teeth.tcondition AS lup1tcondition
FROM teeth
WHERE teeth.tooth = 'lup1')
AS lup1
ON lup1.lup1dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS lup2dartid
, teeth.tstate AS lup2tstate
, teeth.tcondition AS lup2tcondition
FROM teeth
WHERE teeth.tooth = 'lup2')
AS lup2
ON lup2.lup2dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS lum1dartid
, teeth.tstate AS lum1tstate
, teeth.tcondition AS lum1tcondition
FROM teeth
WHERE teeth.tooth = 'lum1')
AS lum1
ON lum1.lum1dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS lum2dartid
, teeth.tstate AS lum2tstate
, teeth.tcondition AS lum2tcondition
FROM teeth
WHERE teeth.tooth = 'lum2')
AS lum2
ON lum2.lum2dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS lum3dartid
, teeth.tstate AS lum3tstate
, teeth.tcondition AS lum3tcondition
FROM teeth
WHERE teeth.tooth = 'lum3')
AS lum3
ON lum3.lum3dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS llm3dartid
, teeth.tstate AS llm3tstate
, teeth.tcondition AS llm3tcondition
FROM teeth
WHERE teeth.tooth = 'llm3')
AS llm3
ON llm3.llm3dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS llm2dartid
, teeth.tstate AS llm2tstate
, teeth.tcondition AS llm2tcondition
FROM teeth
WHERE teeth.tooth = 'llm2')
AS llm2
ON llm2.llm2dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS llm1dartid
, teeth.tstate AS llm1tstate
, teeth.tcondition AS llm1tcondition
FROM teeth
WHERE teeth.tooth = 'llm1')
AS llm1
ON llm1.llm1dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS llp2dartid
, teeth.tstate AS llp2tstate
, teeth.tcondition AS llp2tcondition
FROM teeth
WHERE teeth.tooth = 'llp2')
AS llp2
ON llp2.llp2dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS llp1dartid
, teeth.tstate AS llp1tstate
, teeth.tcondition AS llp1tcondition
FROM teeth
WHERE teeth.tooth = 'llp1')
AS llp1
ON llp1.llp1dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS llcdartid
, teeth.tstate AS llctstate
, teeth.tcondition AS llctcondition
FROM teeth
WHERE teeth.tooth = 'llc')
AS llc
ON llc.llcdartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS lli2dartid
, teeth.tstate AS lli2tstate
, teeth.tcondition AS lli2tcondition
FROM teeth
WHERE teeth.tooth = 'lli2')
AS lli2
ON lli2.lli2dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS lli1dartid
, teeth.tstate AS lli1tstate
, teeth.tcondition AS lli1tcondition
FROM teeth
WHERE teeth.tooth = 'lli1')
AS lli1
ON lli1.lli1dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS rli1dartid
, teeth.tstate AS rli1tstate
, teeth.tcondition AS rli1tcondition
FROM teeth
WHERE teeth.tooth = 'rli1')
AS rli1
ON rli1.rli1dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS rli2dartid
, teeth.tstate AS rli2tstate
, teeth.tcondition AS rli2tcondition
FROM teeth
WHERE teeth.tooth = 'rli2')
AS rli2
ON rli2.rli2dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS rlcdartid
, teeth.tstate AS rlctstate
, teeth.tcondition AS rlctcondition
FROM teeth
WHERE teeth.tooth = 'rlc')
AS rlc
ON rlc.rlcdartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS rlp1dartid
, teeth.tstate AS rlp1tstate
, teeth.tcondition AS rlp1tcondition
FROM teeth
WHERE teeth.tooth = 'rlp1')
AS rlp1
ON rlp1.rlp1dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS rlp2dartid
, teeth.tstate AS rlp2tstate
, teeth.tcondition AS rlp2tcondition
FROM teeth
WHERE teeth.tooth = 'rlp2')
AS rlp2
ON rlp2.rlp2dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS rlm1dartid
, teeth.tstate AS rlm1tstate
, teeth.tcondition AS rlm1tcondition
FROM teeth
WHERE teeth.tooth = 'rlm1')
AS rlm1
ON rlm1.rlm1dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS rlm2dartid
, teeth.tstate AS rlm2tstate
, teeth.tcondition AS rlm2tcondition
FROM teeth
WHERE teeth.tooth = 'rlm2')
AS rlm2
ON rlm2.rlm2dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS rlm3dartid
, teeth.tstate AS rlm3tstate
, teeth.tcondition AS rlm3tcondition
FROM teeth
WHERE teeth.tooth = 'rlm3')
AS rlm3
ON rlm3.rlm3dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS drum2dartid
, teeth.tstate AS drum2tstate
, teeth.tcondition AS drum2tcondition
FROM teeth
WHERE teeth.tooth = 'drum2')
AS drum2
ON drum2.drum2dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS drum1dartid
, teeth.tstate AS drum1tstate
, teeth.tcondition AS drum1tcondition
FROM teeth
WHERE teeth.tooth = 'drum1')
AS drum1
ON drum1.drum1dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS drucdartid
, teeth.tstate AS dructstate
, teeth.tcondition AS dructcondition
FROM teeth
WHERE teeth.tooth = 'druc')
AS druc
ON druc.drucdartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS drui2dartid
, teeth.tstate AS drui2tstate
, teeth.tcondition AS drui2tcondition
FROM teeth
WHERE teeth.tooth = 'drui2')
AS drui2
ON drui2.drui2dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS drui1dartid
, teeth.tstate AS drui1tstate
, teeth.tcondition AS drui1tcondition
FROM teeth
WHERE teeth.tooth = 'drui1')
AS drui1
ON drui1.drui1dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS dlui1dartid
, teeth.tstate AS dlui1tstate
, teeth.tcondition AS dlui1tcondition
FROM teeth
WHERE teeth.tooth = 'dlui1')
AS dlui1
ON dlui1.dlui1dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS dlui2dartid
, teeth.tstate AS dlui2tstate
, teeth.tcondition AS dlui2tcondition
FROM teeth
WHERE teeth.tooth = 'dlui2')
AS dlui2
ON dlui2.dlui2dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS dlucdartid
, teeth.tstate AS dluctstate
, teeth.tcondition AS dluctcondition
FROM teeth
WHERE teeth.tooth = 'dluc')
AS dluc
ON dluc.dlucdartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS dlum1dartid
, teeth.tstate AS dlum1tstate
, teeth.tcondition AS dlum1tcondition
FROM teeth
WHERE teeth.tooth = 'dlum1')
AS dlum1
ON dlum1.dlum1dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS dlum2dartid
, teeth.tstate AS dlum2tstate
, teeth.tcondition AS dlum2tcondition
FROM teeth
WHERE teeth.tooth = 'dlum2')
AS dlum2
ON dlum2.dlum2dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS dllm2dartid
, teeth.tstate AS dllm2tstate
, teeth.tcondition AS dllm2tcondition
FROM teeth
WHERE teeth.tooth = 'dllm2')
AS dllm2
ON dllm2.dllm2dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS dllm1dartid
, teeth.tstate AS dllm1tstate
, teeth.tcondition AS dllm1tcondition
FROM teeth
WHERE teeth.tooth = 'dllm1')
AS dllm1
ON dllm1.dllm1dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS dllcdartid
, teeth.tstate AS dllctstate
, teeth.tcondition AS dllctcondition
FROM teeth
WHERE teeth.tooth = 'dllc')
AS dllc
ON dllc.dllcdartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS dlli2dartid
, teeth.tstate AS dlli2tstate
, teeth.tcondition AS dlli2tcondition
FROM teeth
WHERE teeth.tooth = 'dlli2')
AS dlli2
ON dlli2.dlli2dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS dlli1dartid
, teeth.tstate AS dlli1tstate
, teeth.tcondition AS dlli1tcondition
FROM teeth
WHERE teeth.tooth = 'dlli1')
AS dlli1
ON dlli1.dlli1dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS drli1dartid
, teeth.tstate AS drli1tstate
, teeth.tcondition AS drli1tcondition
FROM teeth
WHERE teeth.tooth = 'drli1')
AS drli1
ON drli1.drli1dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS drli2dartid
, teeth.tstate AS drli2tstate
, teeth.tcondition AS drli2tcondition
FROM teeth
WHERE teeth.tooth = 'drli2')
AS drli2
ON drli2.drli2dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS drlcdartid
, teeth.tstate AS drlctstate
, teeth.tcondition AS drlctcondition
FROM teeth
WHERE teeth.tooth = 'drlc')
AS drlc
ON drlc.drlcdartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS drlm1dartid
, teeth.tstate AS drlm1tstate
, teeth.tcondition AS drlm1tcondition
FROM teeth
WHERE teeth.tooth = 'drlm1')
AS drlm1
ON drlm1.drlm1dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS drlm2dartid
, teeth.tstate AS drlm2tstate
, teeth.tcondition AS drlm2tcondition
FROM teeth
WHERE teeth.tooth = 'drlm2')
AS drlm2
ON drlm2.drlm2dartid = teethdartids.dartid;
Figure 39. Query Defining the DENT_SITES View
SELECT teethdartids.dartid AS dartid
, s1.s1tstate AS s1tstate
, s1.s1tcondition AS s1tcondition
, s1.s1deciduous AS s1deciduous
, s2.s2tstate AS s2tstate
, s2.s2tcondition AS s2tcondition
, s2.s2deciduous AS s2deciduous
, s3.s3tstate AS s3tstate
, s3.s3tcondition AS s3tcondition
, s3.s3deciduous AS s3deciduous
, s4.s4tstate AS s4tstate
, s4.s4tcondition AS s4tcondition
, s4.s4deciduous AS s4deciduous
, s5.s5tstate AS s5tstate
, s5.s5tcondition AS s5tcondition
, s5.s5deciduous AS s5deciduous
, s6.s6tstate AS s6tstate
, s6.s6tcondition AS s6tcondition
, s6.s6deciduous AS s6deciduous
, s7.s7tstate AS s7tstate
, s7.s7tcondition AS s7tcondition
, s7.s7deciduous AS s7deciduous
, s8.s8tstate AS s8tstate
, s8.s8tcondition AS s8tcondition
, s8.s8deciduous AS s8deciduous
, s9.s9tstate AS s9tstate
, s9.s9tcondition AS s9tcondition
, s9.s9deciduous AS s9deciduous
, s10.s10tstate AS s10tstate
, s10.s10tcondition AS s10tcondition
, s10.s10deciduous AS s10deciduous
, s11.s11tstate AS s11tstate
, s11.s11tcondition AS s11tcondition
, s11.s11deciduous AS s11deciduous
, s12.s12tstate AS s12tstate
, s12.s12tcondition AS s12tcondition
, s12.s12deciduous AS s12deciduous
, s13.s13tstate AS s13tstate
, s13.s13tcondition AS s13tcondition
, s13.s13deciduous AS s13deciduous
, s14.s14tstate AS s14tstate
, s14.s14tcondition AS s14tcondition
, s14.s14deciduous AS s14deciduous
, s15.s15tstate AS s15tstate
, s15.s15tcondition AS s15tcondition
, s15.s15deciduous AS s15deciduous
, s16.s16tstate AS s16tstate
, s16.s16tcondition AS s16tcondition
, s16.s16deciduous AS s16deciduous
, s17.s17tstate AS s17tstate
, s17.s17tcondition AS s17tcondition
, s17.s17deciduous AS s17deciduous
, s18.s18tstate AS s18tstate
, s18.s18tcondition AS s18tcondition
, s18.s18deciduous AS s18deciduous
, s19.s19tstate AS s19tstate
, s19.s19tcondition AS s19tcondition
, s19.s19deciduous AS s19deciduous
, s20.s20tstate AS s20tstate
, s20.s20tcondition AS s20tcondition
, s20.s20deciduous AS s20deciduous
, s21.s21tstate AS s21tstate
, s21.s21tcondition AS s21tcondition
, s21.s21deciduous AS s21deciduous
, s22.s22tstate AS s22tstate
, s22.s22tcondition AS s22tcondition
, s22.s22deciduous AS s22deciduous
, s23.s23tstate AS s23tstate
, s23.s23tcondition AS s23tcondition
, s23.s23deciduous AS s23deciduous
, s24.s24tstate AS s24tstate
, s24.s24tcondition AS s24tcondition
, s24.s24deciduous AS s24deciduous
, s25.s25tstate AS s25tstate
, s25.s25tcondition AS s25tcondition
, s25.s25deciduous AS s25deciduous
, s26.s26tstate AS s26tstate
, s26.s26tcondition AS s26tcondition
, s26.s26deciduous AS s26deciduous
, s27.s27tstate AS s27tstate
, s27.s27tcondition AS s27tcondition
, s27.s27deciduous AS s27deciduous
, s28.s28tstate AS s28tstate
, s28.s28tcondition AS s28tcondition
, s28.s28deciduous AS s28deciduous
, s29.s29tstate AS s29tstate
, s29.s29tcondition AS s29tcondition
, s29.s29deciduous AS s29deciduous
, s30.s30tstate AS s30tstate
, s30.s30tcondition AS s30tcondition
, s30.s30deciduous AS s30deciduous
, s31.s31tstate AS s31tstate
, s31.s31tcondition AS s31tcondition
, s31.s31deciduous AS s31deciduous
, s32.s32tstate AS s32tstate
, s32.s32tcondition AS s32tcondition
, s32.s32deciduous AS s32deciduous
FROM (SELECT teeth.dartid
FROM teeth
GROUP BY teeth.dartid)
AS teethdartids
LEFT OUTER JOIN
(SELECT teeth.dartid AS s1dartid
, teeth.tstate AS s1tstate
, teeth.tcondition AS s1tcondition
, toothcodes.deciduous AS s1deciduous
FROM toothcodes, teeth
WHERE toothcodes.toothsite = '1'
AND teeth.tooth = toothcodes.tooth)
AS s1
ON s1.s1dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS s2dartid
, teeth.tstate AS s2tstate
, teeth.tcondition AS s2tcondition
, toothcodes.deciduous AS s2deciduous
FROM toothcodes, teeth
WHERE toothcodes.toothsite = '2'
AND teeth.tooth = toothcodes.tooth)
AS s2
ON s2.s2dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS s3dartid
, teeth.tstate AS s3tstate
, teeth.tcondition AS s3tcondition
, toothcodes.deciduous AS s3deciduous
FROM toothcodes, teeth
WHERE toothcodes.toothsite = '3'
AND teeth.tooth = toothcodes.tooth)
AS s3
ON s3.s3dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS s4dartid
, teeth.tstate AS s4tstate
, teeth.tcondition AS s4tcondition
, toothcodes.deciduous AS s4deciduous
FROM toothcodes, teeth
WHERE toothcodes.toothsite = '4'
AND teeth.tooth = toothcodes.tooth)
AS s4
ON s4.s4dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS s5dartid
, teeth.tstate AS s5tstate
, teeth.tcondition AS s5tcondition
, toothcodes.deciduous AS s5deciduous
FROM toothcodes, teeth
WHERE toothcodes.toothsite = '5'
AND teeth.tooth = toothcodes.tooth)
AS s5
ON s5.s5dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS s6dartid
, teeth.tstate AS s6tstate
, teeth.tcondition AS s6tcondition
, toothcodes.deciduous AS s6deciduous
FROM toothcodes, teeth
WHERE toothcodes.toothsite = '6'
AND teeth.tooth = toothcodes.tooth)
AS s6
ON s6.s6dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS s7dartid
, teeth.tstate AS s7tstate
, teeth.tcondition AS s7tcondition
, toothcodes.deciduous AS s7deciduous
FROM toothcodes, teeth
WHERE toothcodes.toothsite = '7'
AND teeth.tooth = toothcodes.tooth)
AS s7
ON s7.s7dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS s8dartid
, teeth.tstate AS s8tstate
, teeth.tcondition AS s8tcondition
, toothcodes.deciduous AS s8deciduous
FROM toothcodes, teeth
WHERE toothcodes.toothsite = '8'
AND teeth.tooth = toothcodes.tooth)
AS s8
ON s8.s8dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS s9dartid
, teeth.tstate AS s9tstate
, teeth.tcondition AS s9tcondition
, toothcodes.deciduous AS s9deciduous
FROM toothcodes, teeth
WHERE toothcodes.toothsite = '9'
AND teeth.tooth = toothcodes.tooth)
AS s9
ON s9.s9dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS s10dartid
, teeth.tstate AS s10tstate
, teeth.tcondition AS s10tcondition
, toothcodes.deciduous AS s10deciduous
FROM toothcodes, teeth
WHERE toothcodes.toothsite = '10'
AND teeth.tooth = toothcodes.tooth)
AS s10
ON s10.s10dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS s11dartid
, teeth.tstate AS s11tstate
, teeth.tcondition AS s11tcondition
, toothcodes.deciduous AS s11deciduous
FROM toothcodes, teeth
WHERE toothcodes.toothsite = '11'
AND teeth.tooth = toothcodes.tooth)
AS s11
ON s11.s11dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS s12dartid
, teeth.tstate AS s12tstate
, teeth.tcondition AS s12tcondition
, toothcodes.deciduous AS s12deciduous
FROM toothcodes, teeth
WHERE toothcodes.toothsite = '12'
AND teeth.tooth = toothcodes.tooth)
AS s12
ON s12.s12dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS s13dartid
, teeth.tstate AS s13tstate
, teeth.tcondition AS s13tcondition
, toothcodes.deciduous AS s13deciduous
FROM toothcodes, teeth
WHERE toothcodes.toothsite = '13'
AND teeth.tooth = toothcodes.tooth)
AS s13
ON s13.s13dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS s14dartid
, teeth.tstate AS s14tstate
, teeth.tcondition AS s14tcondition
, toothcodes.deciduous AS s14deciduous
FROM toothcodes, teeth
WHERE toothcodes.toothsite = '14'
AND teeth.tooth = toothcodes.tooth)
AS s14
ON s14.s14dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS s15dartid
, teeth.tstate AS s15tstate
, teeth.tcondition AS s15tcondition
, toothcodes.deciduous AS s15deciduous
FROM toothcodes, teeth
WHERE toothcodes.toothsite = '15'
AND teeth.tooth = toothcodes.tooth)
AS s15
ON s15.s15dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS s16dartid
, teeth.tstate AS s16tstate
, teeth.tcondition AS s16tcondition
, toothcodes.deciduous AS s16deciduous
FROM toothcodes, teeth
WHERE toothcodes.toothsite = '16'
AND teeth.tooth = toothcodes.tooth)
AS s16
ON s16.s16dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS s17dartid
, teeth.tstate AS s17tstate
, teeth.tcondition AS s17tcondition
, toothcodes.deciduous AS s17deciduous
FROM toothcodes, teeth
WHERE toothcodes.toothsite = '17'
AND teeth.tooth = toothcodes.tooth)
AS s17
ON s17.s17dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS s18dartid
, teeth.tstate AS s18tstate
, teeth.tcondition AS s18tcondition
, toothcodes.deciduous AS s18deciduous
FROM toothcodes, teeth
WHERE toothcodes.toothsite = '18'
AND teeth.tooth = toothcodes.tooth)
AS s18
ON s18.s18dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS s19dartid
, teeth.tstate AS s19tstate
, teeth.tcondition AS s19tcondition
, toothcodes.deciduous AS s19deciduous
FROM toothcodes, teeth
WHERE toothcodes.toothsite = '19'
AND teeth.tooth = toothcodes.tooth)
AS s19
ON s19.s19dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS s20dartid
, teeth.tstate AS s20tstate
, teeth.tcondition AS s20tcondition
, toothcodes.deciduous AS s20deciduous
FROM toothcodes, teeth
WHERE toothcodes.toothsite = '20'
AND teeth.tooth = toothcodes.tooth)
AS s20
ON s20.s20dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS s21dartid
, teeth.tstate AS s21tstate
, teeth.tcondition AS s21tcondition
, toothcodes.deciduous AS s21deciduous
FROM toothcodes, teeth
WHERE toothcodes.toothsite = '21'
AND teeth.tooth = toothcodes.tooth)
AS s21
ON s21.s21dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS s22dartid
, teeth.tstate AS s22tstate
, teeth.tcondition AS s22tcondition
, toothcodes.deciduous AS s22deciduous
FROM toothcodes, teeth
WHERE toothcodes.toothsite = '22'
AND teeth.tooth = toothcodes.tooth)
AS s22
ON s22.s22dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS s23dartid
, teeth.tstate AS s23tstate
, teeth.tcondition AS s23tcondition
, toothcodes.deciduous AS s23deciduous
FROM toothcodes, teeth
WHERE toothcodes.toothsite = '23'
AND teeth.tooth = toothcodes.tooth)
AS s23
ON s23.s23dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS s24dartid
, teeth.tstate AS s24tstate
, teeth.tcondition AS s24tcondition
, toothcodes.deciduous AS s24deciduous
FROM toothcodes, teeth
WHERE toothcodes.toothsite = '24'
AND teeth.tooth = toothcodes.tooth)
AS s24
ON s24.s24dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS s25dartid
, teeth.tstate AS s25tstate
, teeth.tcondition AS s25tcondition
, toothcodes.deciduous AS s25deciduous
FROM toothcodes, teeth
WHERE toothcodes.toothsite = '25'
AND teeth.tooth = toothcodes.tooth)
AS s25
ON s25.s25dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS s26dartid
, teeth.tstate AS s26tstate
, teeth.tcondition AS s26tcondition
, toothcodes.deciduous AS s26deciduous
FROM toothcodes, teeth
WHERE toothcodes.toothsite = '26'
AND teeth.tooth = toothcodes.tooth)
AS s26
ON s26.s26dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS s27dartid
, teeth.tstate AS s27tstate
, teeth.tcondition AS s27tcondition
, toothcodes.deciduous AS s27deciduous
FROM toothcodes, teeth
WHERE toothcodes.toothsite = '27'
AND teeth.tooth = toothcodes.tooth)
AS s27
ON s27.s27dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS s28dartid
, teeth.tstate AS s28tstate
, teeth.tcondition AS s28tcondition
, toothcodes.deciduous AS s28deciduous
FROM toothcodes, teeth
WHERE toothcodes.toothsite = '28'
AND teeth.tooth = toothcodes.tooth)
AS s28
ON s28.s28dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS s29dartid
, teeth.tstate AS s29tstate
, teeth.tcondition AS s29tcondition
, toothcodes.deciduous AS s29deciduous
FROM toothcodes, teeth
WHERE toothcodes.toothsite = '29'
AND teeth.tooth = toothcodes.tooth)
AS s29
ON s29.s29dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS s30dartid
, teeth.tstate AS s30tstate
, teeth.tcondition AS s30tcondition
, toothcodes.deciduous AS s30deciduous
FROM toothcodes, teeth
WHERE toothcodes.toothsite = '30'
AND teeth.tooth = toothcodes.tooth)
AS s30
ON s30.s30dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS s31dartid
, teeth.tstate AS s31tstate
, teeth.tcondition AS s31tcondition
, toothcodes.deciduous AS s31deciduous
FROM toothcodes, teeth
WHERE toothcodes.toothsite = '31'
AND teeth.tooth = toothcodes.tooth)
AS s31
ON s31.s31dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS s32dartid
, teeth.tstate AS s32tstate
, teeth.tcondition AS s32tcondition
, toothcodes.deciduous AS s32deciduous
FROM toothcodes, teeth
WHERE toothcodes.toothsite = '32'
AND teeth.tooth = toothcodes.tooth)
AS s32
ON s32.s32dartid = teethdartids.dartid;
Figure 41. Query Defining the INTERACT View
SELECT iid AS iid
, interact_data.sid AS sid
, interact_data.act AS act
, acts.class AS class
, interact_data.date AS date
, julian(interact_data.date) AS jdate
, interact_data.start AS start
, spm(interact_data.start) AS startspm
, stop AS stop
, spm(interact_data.stop) AS stopspm
, interact_data.observer AS observer
FROM interact_data
JOIN acts
ON (acts.act = interact_data.act);
Figure 43. Query Defining the MATERNITIES View
SELECT cycles.sname AS mom
, cycles.cid AS cid
, cycles.seq AS seq
, cycles.series AS series
, cycpoints.cpid AS conceive
, cycpoints.date AS zdate
, members.grp AS zdate_grp
, cycpoints.edate AS edate
, cycpoints.ldate AS ldate
, cycpoints.source AS source
, pregs.pid AS pid
, pregs.parity AS parity
, biograph.bioid AS child_bioid
, biograph.sname AS child
, biograph.birth AS birth
FROM cycles
JOIN cycpoints ON (cycpoints.cid = cycles.cid)
JOIN members ON (members.date = cycpoints.date
AND members.sname = cycles.sname)
JOIN pregs ON (pregs.conceive = cycpoints.cpid)
JOIN biograph ON (pregs.pid = biograph.pid);
Figure 45. Query Defining the MIN_MAXS View
SELECT wreadings.wrid AS wrid
, wreadings.wstation AS wstation
, wreadings.wrdaytime AS wrdaytime
, wreadings.estdaytime AS estdaytime
, wreadings.wrperson AS wrperson
, wreadings.wrnotes AS wrnotes
, tempmins.tempmin AS tempmin
, tempmaxs.tempmax AS tempmax
, raingauges.rgspan AS rgspan
, raingauges.estrgspan AS estrgspan
, raingauges.rain AS rain
FROM wreadings
LEFT OUTER JOIN tempmins
ON wreadings.wrid = tempmins.wrid
LEFT OUTER JOIN tempmaxs
ON wreadings.wrid = tempmaxs.wrid
LEFT OUTER JOIN raingauges
ON wreadings.wrid = raingauges.wrid;
Figure 47. Query Defining the MIN_MAXS_SORTED View
SELECT wreadings.wrid AS wrid
, wreadings.wstation AS wstation
, wreadings.wrdaytime AS wrdaytime
, wreadings.estdaytime AS estdaytime
, wreadings.wrperson AS wrperson
, wreadings.wrnotes AS wrnotes
, tempmins.tempmin AS tempmin
, tempmaxs.tempmax AS tempmax
, raingauges.rgspan AS rgspan
, raingauges.estrgspan AS estrgspan
, raingauges.rain AS rain
FROM wreadings
LEFT OUTER JOIN tempmins
ON wreadings.wrid = tempmins.wrid
LEFT OUTER JOIN tempmaxs
ON wreadings.wrid = tempmaxs.wrid
LEFT OUTER JOIN raingauges
ON wreadings.wrid = raingauges.wrid
ORDER BY wreadings.wrdaytime, wreadings.wstation;;
Figure 49. Query Defining the MPI_EVENTS View
SELECT mpis.mpiid AS mpiid
, mpis.date AS date
, mpis.context_type AS context_type
, mpis.context AS context
, mpi_data.mpidid AS mpidid
, mpi_data.seq AS seq
, mpi_data.mpiact AS mpiact
, actor.mpipid AS actorid
, actor.sname AS actor
, actor.unksname AS unkactor
, actee.mpipid AS acteeid
, actee.sname AS actee
, actee.unksname AS unkactee
, CASE WHEN EXISTS(SELECT 1
FROM mpiacts
WHERE mpiacts.mpiact = mpi_data.mpiact
AND mpiacts.kind = 'H')
THEN
EXISTS(SELECT 1
FROM mpi_data AS request
, mpiacts
, mpi_parts AS requestor
, mpi_parts AS requestee
WHERE request.mpiid = mpi_data.mpiid
AND request.seq < mpi_data.seq
AND mpiacts.mpiact = request.mpiact
AND mpiacts.kind = 'R'
AND requestor.mpidid = request.mpidid
AND requestor.role = 'R'
AND requestor.sname = actee.sname
AND requestee.mpidid = request.mpidid
AND requestee.role = 'E'
AND requestee.sname = actor.sname)
ELSE
NULL
END AS solicited
, EXISTS(SELECT 1
FROM mpi_data AS initial,
mpiacts
WHERE initial.mpiid = mpi_data.mpiid
AND initial.seq = 1
AND mpiacts.mpiact = initial.mpiact
AND mpiacts.decided)
AS decided
, mpi_data.helped AS helped
, mpi_data.active AS active
FROM mpis
LEFT OUTER JOIN mpi_data ON (mpis.mpiid = mpi_data.mpiid)
LEFT OUTER JOIN mpi_parts AS actor
ON (actor.mpidid = mpi_data.mpidid AND actor.role = 'R')
LEFT OUTER JOIN mpi_parts AS actee
ON (actee.mpidid = mpi_data.mpidid AND actee.role = 'E');
Figure 51. Query Defining the MTD_CYCLES View
SELECT cycles.cid AS cid
, cycles.sname AS sname
, cycles.seq AS seq
, cycles.series AS series
, mcp.cpid AS mcpid
, mcp.date AS mdate
, mcp.edate AS emdate
, mcp.ldate AS lmdate
, mcp.source AS msource
, tcp.cpid AS tcpid
, tcp.date AS tdate
, tcp.edate AS etdate
, tcp.ldate AS ltdate
, tcp.source AS tsource
, dcp.cpid AS dcpid
, dcp.date AS ddate
, dcp.edate AS eddate
, dcp.ldate AS lddate
, dcp.source AS dsource
FROM cycles
LEFT OUTER JOIN cycpoints AS mcp ON (mcp.cid = cycles.cid
AND mcp.code = 'M')
LEFT OUTER JOIN cycpoints AS tcp ON (tcp.cid = cycles.cid
AND tcp.code = 'T')
LEFT OUTER JOIN cycpoints AS dcp ON (dcp.cid = cycles.cid
AND dcp.code = 'D')
ORDER BY cycles.sname, cycles.seq;
Figure 53. Query Defining the PARENTS View
SELECT biograph.sname AS kid
, maternities.mom AS mom
, dad_data.dad_consensus AS dad
, maternities.zdate AS zdate
, dad_data.dadid AS dadid
, maternities.zdate_grp AS momgrp
, members.grp AS dadgrp
FROM biograph
LEFT OUTER JOIN maternities
ON (maternities.child = biograph.sname)
LEFT OUTER JOIN dad_data
ON (dad_data.kid = biograph.sname)
LEFT OUTER JOIN members
ON (members.sname = dad_data.dad_consensus
AND members.date = maternities.zdate)
WHERE maternities.mom IS NOT NULL
OR dad_data.dad_consensus IS NOT NULL;
Figure 55. Query Defining the PCSKINS_SORTED View
SELECT pcskins.pcsid AS pcsid
, pcskins.sname AS sname
, pcskins.date AS date
, pcskins.color AS color
FROM pcskins
ORDER BY sname, date;
Figure 57. Query Defining the PCV_STATS View
SELECT pcvs.dartid AS dartid
, count(*) AS pcvsamps
, avg(pcvs.pcv) AS pcv_mean
, stddev(pcvs.pcv) AS pcv_stddev
FROM pcvs
GROUP BY pcvs.dartid;
Figure 59. Query Defining the POINTS View
SELECT pntid AS pntid
, sid AS sid
, activity AS activity
, posture AS posture
, foodcode AS foodcode
, ptime AS ptime
, spm(ptime) AS ptimespm
FROM point_data;
Figure 61. Query Defining the POTENTIAL_DADS View
SELECT maternities.child_bioid AS bioid
, maternities.child AS kid
, maternities.mom AS mom
, maternities.zdate AS zdate
, maternities.zdate_grp AS grp
, pdads.sname AS pdad
, CASE
WHEN rankdates.ranked <= maternities.zdate
THEN 'A'
WHEN maturedates.matured <= maternities.zdate
THEN 'S'
ELSE 'O'
END
AS status
, maternities.zdate - pdads.birth AS pdad_age_days
, trunc((maternities.zdate - pdads.birth) / 365.25, 1)
AS pdad_age_years
, (SELECT count(*)
FROM members as dadmembers
JOIN members AS mommembers
ON (mommembers.date = dadmembers.date
AND supergroup(mommembers.grp, mommembers.date)
= supergroup(dadmembers.grp, dadmembers.date))
WHERE dadmembers.sname = pdads.sname
AND dadmembers.date < maternities.zdate
AND dadmembers.date >= maternities.zdate - 5
AND mommembers.sname = maternities.mom
AND mommembers.date < maternities.zdate
AND mommembers.date >= maternities.zdate - 5)
AS estrous_presence
, (SELECT count(*)
FROM actor_actees
WHERE actor_actees.date < maternities.zdate
AND actor_actees.date >= maternities.zdate - 5
AND (actor_actees.act = 'M'
OR actor_actees.act = 'E')
AND actor_actees.actor = pdads.sname
AND actor_actees.actee = maternities.mom)
AS estrous_me
, (SELECT count(*)
FROM actor_actees
WHERE actor_actees.date < maternities.zdate
AND actor_actees.date >= maternities.zdate - 5
AND actor_actees.act = 'C'
AND actor_actees.actor = pdads.sname
AND actor_actees.actee = maternities.mom)
AS estrous_c
FROM maternities
JOIN biograph AS pdads
ON (pdads.sname
IN (SELECT dadmembers.sname
FROM members AS dadmembers
JOIN members AS mommembers
ON (mommembers.date = dadmembers.date
AND supergroup(mommembers.grp,
mommembers.date)
= supergroup(dadmembers.grp,
dadmembers.date))
WHERE dadmembers.sname = pdads.sname
AND dadmembers.date < maternities.zdate
AND dadmembers.date >= maternities.zdate - 5
AND mommembers.sname = maternities.mom
AND mommembers.date < maternities.zdate
AND mommembers.date >= maternities.zdate - 5))
LEFT OUTER JOIN rankdates
ON (rankdates.sname = pdads.sname)
LEFT OUTER JOIN maturedates
ON (maturedates.sname = pdads.sname)
WHERE pdads.sex = 'M'
-- Speed things up by eliminating potential dads
-- who could not possibly interpolate into the mom's group
-- during the fertile period.
AND pdads.statdate >= maternities.zdate - 5 - 14
-- Potential dad must be at least 2192 days old
-- (approximately 6 years) on the zdate.
AND maternities.zdate - pdads.birth >= 2192;
Figure 63. Entity Relationship Diagram of that portion of the POTENTIAL_DADS View which places the mother and potential father in the same group during the fertile period
 |
Figure 64. Entity Relationship Diagram of that portion of the POTENTIAL_DADS View having easily computed columns
 |
Figure 65. Entity Relationship Diagram of that portion of the POTENTIAL_DADS View involving social interactions
 |
Figure 66. Query Defining the QUADS View
SELECT quad_data.quad AS quad
, ST_X(quad_data.xyloc) AS x
, ST_Y(quad_data.xyloc) AS y
, quad_data.aerial AS aerial
FROM quad_data;
Figure 68. Query Defining the SEXSKINS_CYCLES View
SELECT cycles.cid AS cid
, cycles.sname AS sname
, cycles.seq AS seq
, cycles.series AS series
, sexskins.sxid AS sxid
, sexskins.date AS date
, sexskins.size AS size
FROM sexskins, cycles
WHERE cycles.cid = sexskins.cid
ORDER BY cycles.sname, sexskins.date;
Figure 70. Query Defining the SWERB View
SELECT swerb_data.swid AS swid
, swerb_departs_data.did AS did
, swerb_departs_data.date AS date
, swerb_data.time AS time
, swerb_bes.beid AS beid
, swerb_bes.focal_grp AS focal_grp
, swerb_bes.seq AS seq
, swerb_data.event AS event
, swerb_data.seen_grp AS seen_grp
, swerb_data.lone_animal AS lone_animal
, swerb_data.quad AS quad
, CASE
WHEN swerb_data.xyloc IS NULL
THEN 'quad'
ELSE 'gps'
END AS merged_is
, COALESCE(ST_X(swerb_data.xyloc), ST_X(quad_data.xyloc))
AS x
, COALESCE(ST_Y(swerb_data.xyloc), ST_Y(quad_data.xyloc))
AS y
, swerb_data.altitude AS altitude
, swerb_data.pdop AS pdop
, swerb_data.accuracy AS accuracy
, swerb_data.subgroup AS subgroup
, swerb_data.ogdistance AS ogdistance
, swerb_data.gps_datetime AS gps_datetime
, swerb_data.garmincode AS garmincode
, swerb_loc_data.loc AS loc
, swerb_loc_data.adcode AS adcode
, adcodes.adn AS adn
, swerb_loc_data.conf AS conf
, swerb_loc_data.adtime AS adtime
, ST_X(swerb_loc_gps.xyloc) AS second_x
, ST_Y(swerb_loc_gps.xyloc) AS second_y
, swerb_loc_gps.altitude AS second_altitude
, swerb_loc_gps.pdop AS second_pdop
, swerb_loc_gps.accuracy AS second_accuracy
, swerb_loc_gps.gps_datetime AS second_gps_datetime
, swerb_loc_gps.garmincode AS second_garmincode
, swerb_bes.start AS start
, swerb_bes.btimeest AS btimeest
, swerb_bes.bsource AS bsource
, swerb_bes.stop AS stop
, swerb_bes.etimeest AS etimeest
, swerb_bes.esource AS esource
, swerb_bes.is_effort AS is_effort
, swerb_departs_gps.gps AS gps
, swerb_bes.notes AS notes
FROM swerb_data
LEFT OUTER JOIN quad_data
ON (quad_data.quad = swerb_data.quad)
JOIN swerb_bes
ON (swerb_bes.beid = swerb_data.beid)
JOIN swerb_departs_data
ON (swerb_departs_data.did = swerb_bes.did)
LEFT OUTER JOIN swerb_departs_gps
ON (swerb_departs_gps.did = swerb_bes.did)
LEFT OUTER JOIN swerb_loc_data
ON (swerb_loc_data.swid = swerb_data.swid)
LEFT OUTER JOIN adcodes ON (adcodes.adcode = swerb_loc_data.adcode)
LEFT OUTER JOIN swerb_loc_gps
ON (swerb_loc_gps.swid = swerb_loc_data.swid);
Figure 72. Query Defining the SWERB_DEPARTS View
SELECT swerb_departs_data.did AS did
, swerb_departs_data.date AS date
, swerb_departs_data.time AS time
, ST_X(swerb_departs_gps.xyloc) AS x
, ST_Y(swerb_departs_gps.xyloc) AS y
, swerb_departs_gps.altitude AS altitude
, swerb_departs_gps.pdop AS pdop
, swerb_departs_gps.accuracy AS accuracy
, swerb_departs_gps.gps AS gps
, swerb_departs_gps.garmincode AS garmincode
FROM swerb_departs_data
LEFT OUTER JOIN swerb_departs_gps
ON (swerb_departs_gps.did = swerb_departs_data.did);
Figure 74. Query Defining the SWERB_LOC_GPS_XY View
SELECT swerb_loc_gps.swid AS swid
, ST_X(swerb_loc_gps.xyloc) AS x
, ST_Y(swerb_loc_gps.xyloc) AS y
, swerb_loc_gps.altitude AS altitude
, swerb_loc_gps.pdop AS pdop
, swerb_loc_gps.accuracy AS accuracy
, swerb_loc_gps.gps_datetime AS gps_datetime
, swerb_loc_gps.garmincode AS garmincode
FROM swerb_loc_gps;
Figure 76. Query Defining the SWERB_LOCS View
SELECT swerb_loc_data.swid AS swid
, swerb_loc_data.loc AS loc
, swerb_loc_data.adcode AS adcode
, adcodes.adn AS adn
, swerb_loc_data.conf AS conf
, swerb_loc_data.adtime AS time
FROM swerb_loc_data
JOIN adcodes ON (adcodes.adcode = swerb_loc_data.adcode);
Figure 78. Query Defining the SWERB_UPLOAD View
SELECT NULL::TEXT AS header
, NULL::TEXT AS name
, NULL::TEXT AS description
, NULL::TEXT AS type
, NULL::TEXT AS position
, NULL::TEXT AS altitude
, NULL::TEXT AS depth
, NULL::TEXT AS proximity
, NULL::TEXT AS display_mode
, NULL::TEXT AS color
, NULL::TEXT AS symbol
, NULL::TEXT AS facility
, NULL::TEXT AS city
, NULL::TEXT AS state
, NULL::TEXT AS country
, NULL::TEXT AS pdop
, NULL::TEXT AS accuracy
, NULL::TEXT AS quad
, NULL::TEXT AS date
, NULL::TEXT AS timeest
, NULL::TEXT AS source
, NULL::TEXT AS lone_animal
, NULL::TEXT AS is_effort
, NULL::BOOLEAN AS secondary_ad
, NULL::TEXT AS notes
WHERE _raise_babase_exception(
'Cannot select SWERB_UPLOAD'
|| ': The only use of the SWERB_UPLOAD view is to insert'
|| ' new data into the SWERB portion of babase');
Figure 79. Entity Relationship Diagram of the SWERB_UPLOAD View
Figure 80. Query Defining the ULNA_STATS View
SELECT ulnas.dartid AS dartid
, count(*) AS ulsamps
, avg(ulnas.ullength) AS ullength_mean
, stddev(ulnas.ullength) AS ullength_stddev
, avg(ulnas.ulunadjusted) AS ulunadjusted_mean
, stddev(ulnas.ulunadjusted) AS ulunadjusted_stddev
FROM ulnas
GROUP BY ulnas.dartid;
Figure 82. Query Defining the TESTES_ARC_STATS View
SELECT testesdartids.dartid AS dartid
, testesllength.testllengthsamps AS testllengthsamps
, testesllength.testllength_mean AS testllength_mean
, testesllength.testllength_stddev AS testllength_stddev
, testeslwidth.testlwidthsamps AS testlwidthsamps
, testeslwidth.testlwidth_mean AS testlwidth_mean
, testeslwidth.testlwidth_stddev AS testlwidth_stddev
, testesrlength.testrlengthsamps AS testrlengthsamps
, testesrlength.testrlength_mean AS testrlength_mean
, testesrlength.testrlength_stddev AS testrlength_stddev
, testesrwidth.testrwidthsamps AS testrwidthsamps
, testesrwidth.testrwidth_mean AS testrwidth_mean
, testesrwidth.testrwidth_stddev AS testrwidth_stddev
FROM (SELECT testes_arc.dartid
FROM testes_arc
GROUP BY testes_arc.dartid)
AS testesdartids
LEFT OUTER JOIN
(SELECT testes_arc.dartid AS llengthdartid
, count(*) AS testllengthsamps
, avg(testes_arc.testlength) AS testllength_mean
, stddev(testes_arc.testlength) AS testllength_stddev
FROM testes_arc
WHERE testes_arc.testside = 'L'
AND testes_arc.testlength IS NOT NULL
GROUP BY testes_arc.dartid)
AS testesllength
ON testesllength.llengthdartid = testesdartids.dartid
LEFT OUTER JOIN
(SELECT testes_arc.dartid AS lwidthdartid
, count(*) AS testlwidthsamps
, avg(testes_arc.testwidth) AS testlwidth_mean
, stddev(testes_arc.testwidth) AS testlwidth_stddev
FROM testes_arc
WHERE testes_arc.testside = 'L'
AND testes_arc.testwidth IS NOT NULL
GROUP BY testes_arc.dartid)
AS testeslwidth
ON testeslwidth.lwidthdartid = testesdartids.dartid
LEFT OUTER JOIN
(SELECT testes_arc.dartid AS rlengthdartid
, count(*) AS testrlengthsamps
, avg(testes_arc.testlength) AS testrlength_mean
, stddev(testes_arc.testlength) AS testrlength_stddev
FROM testes_arc
WHERE testes_arc.testside = 'R'
AND testes_arc.testlength IS NOT NULL
GROUP BY testes_arc.dartid)
AS testesrlength
ON testesrlength.rlengthdartid = testesdartids.dartid
LEFT OUTER JOIN
(SELECT testes_arc.dartid AS rwidthdartid
, count(*) AS testrwidthsamps
, avg(testes_arc.testwidth) AS testrwidth_mean
, stddev(testes_arc.testwidth) AS testrwidth_stddev
FROM testes_arc
WHERE testes_arc.testside = 'R'
AND testes_arc.testwidth IS NOT NULL
GROUP BY testes_arc.dartid)
AS testesrwidth
ON testesrwidth.rwidthdartid = testesdartids.dartid;
Figure 84. Query Defining the TESTES_DIAM_STATS View
SELECT testesdartids.dartid AS dartid
, testesllength.testllengthsamps AS testllengthsamps
, testesllength.testllength_mean AS testllength_mean
, testesllength.testllength_stddev AS testllength_stddev
, testeslwidth.testlwidthsamps AS testlwidthsamps
, testeslwidth.testlwidth_mean AS testlwidth_mean
, testeslwidth.testlwidth_stddev AS testlwidth_stddev
, testesrlength.testrlengthsamps AS testrlengthsamps
, testesrlength.testrlength_mean AS testrlength_mean
, testesrlength.testrlength_stddev AS testrlength_stddev
, testesrwidth.testrwidthsamps AS testrwidthsamps
, testesrwidth.testrwidth_mean AS testrwidth_mean
, testesrwidth.testrwidth_stddev AS testrwidth_stddev
FROM (SELECT testes_diam.dartid
FROM testes_diam
GROUP BY testes_diam.dartid)
AS testesdartids
LEFT OUTER JOIN
(SELECT testes_diam.dartid AS llengthdartid
, count(*) AS testllengthsamps
, avg(testes_diam.testlength) AS testllength_mean
, stddev(testes_diam.testlength) AS testllength_stddev
FROM testes_diam
WHERE testes_diam.testside = 'L'
AND testes_diam.testlength IS NOT NULL
GROUP BY testes_diam.dartid)
AS testesllength
ON testesllength.llengthdartid = testesdartids.dartid
LEFT OUTER JOIN
(SELECT testes_diam.dartid AS lwidthdartid
, count(*) AS testlwidthsamps
, avg(testes_diam.testwidth) AS testlwidth_mean
, stddev(testes_diam.testwidth) AS testlwidth_stddev
FROM testes_diam
WHERE testes_diam.testside = 'L'
AND testes_diam.testwidth IS NOT NULL
GROUP BY testes_diam.dartid)
AS testeslwidth
ON testeslwidth.lwidthdartid = testesdartids.dartid
LEFT OUTER JOIN
(SELECT testes_diam.dartid AS rlengthdartid
, count(*) AS testrlengthsamps
, avg(testes_diam.testlength) AS testrlength_mean
, stddev(testes_diam.testlength) AS testrlength_stddev
FROM testes_diam
WHERE testes_diam.testside = 'R'
AND testes_diam.testlength IS NOT NULL
GROUP BY testes_diam.dartid)
AS testesrlength
ON testesrlength.rlengthdartid = testesdartids.dartid
LEFT OUTER JOIN
(SELECT testes_diam.dartid AS rwidthdartid
, count(*) AS testrwidthsamps
, avg(testes_diam.testwidth) AS testrwidth_mean
, stddev(testes_diam.testwidth) AS testrwidth_stddev
FROM testes_diam
WHERE testes_diam.testside = 'R'
AND testes_diam.testwidth IS NOT NULL
GROUP BY testes_diam.dartid)
AS testesrwidth
ON testesrwidth.rwidthdartid = testesdartids.dartid;
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.
Figure 86. Query Defining the BIRTH_GRP View
SELECT biograph.*
, members.grp AS grp
FROM members, biograph
WHERE members.sname = biograph.sname
AND members.date = CAST(biograph.birth AS DATE);
Figure 88. Query Defining the ENTRYDATE_GRP View
SELECT biograph.*
, members.grp AS grp
FROM members, biograph
WHERE members.sname = biograph.sname
AND members.date = CAST(biograph.entrydate AS DATE);
Figure 90. Query Defining the STATDATE_GRP View
SELECT biograph.*
, members.grp AS grp
FROM members, biograph
WHERE members.sname = biograph.sname
AND members.date = CAST(biograph.statdate AS DATE);
Figure 92. Query Defining the CONSORTDATES_GRP View
SELECT consortdates.*
, members.grp AS grp
FROM members, consortdates
WHERE members.sname = consortdates.sname
AND members.date = CAST(consortdates.consorted AS DATE);
Figure 94. Query Defining the CYCGAPDAYS_GRP View
SELECT cycgapdays.*
, members.grp AS grp
FROM members, cycgapdays
WHERE members.sname = cycgapdays.sname
AND members.date = CAST(cycgapdays.date AS DATE);
Figure 96. Query Defining the CYCGAPS_GRP View
SELECT cycgaps.*
, members.grp AS grp
FROM members, cycgaps
WHERE members.sname = cycgaps.sname
AND members.date = CAST(cycgaps.date AS DATE);
Figure 98. Query Defining the CYCSTATS_GRP View
SELECT cycstats.*
, members.grp AS grp
FROM members, cycstats
WHERE members.sname = cycstats.sname
AND members.date = CAST(cycstats.date AS DATE);
Figure 100. Query Defining the DARTINGS_GRP View
SELECT dartings.*
, members.grp AS grp
FROM members, dartings
WHERE members.sname = dartings.sname
AND members.date = CAST(dartings.date AS DATE);
Figure 102. Query Defining the DISPERSEDATES_GRP View
SELECT dispersedates.*
, members.grp AS grp
FROM members, dispersedates
WHERE members.sname = dispersedates.sname
AND members.date = CAST(dispersedates.dispersed AS DATE);
Figure 104. Query Defining the MATUREDATES_GRP View
SELECT maturedates.*
, members.grp AS grp
FROM members, maturedates
WHERE members.sname = maturedates.sname
AND members.date = CAST(maturedates.matured AS DATE);
Figure 106. Query Defining the MDINTERVALS_GRP View
SELECT mdintervals.*
, members.grp AS grp
FROM members, mdintervals
WHERE members.sname = mdintervals.sname
AND members.date = CAST(mdintervals.date AS DATE);
Figure 108. Query Defining the MMINTERVALS_GRP View
SELECT mmintervals.*
, members.grp AS grp
FROM members, mmintervals
WHERE members.sname = mmintervals.sname
AND members.date = CAST(mmintervals.date AS DATE);
Figure 110. Query Defining the PCSKINS_GRP View
SELECT pcskins.*
, members.grp AS grp
FROM members, pcskins
WHERE members.sname = pcskins.sname
AND members.date = CAST(pcskins.date AS DATE);
