Sexual Cycles

CYCLES_SEXSKINS (CYCLES extended with SEXSKINS information)

Contains one row for every row in SEXSKINS, and for every row in CYCLES that does not have a related SEXSKINS row. Each row contains the SEXSKINS columns and the related CYCLES columns. Because there is a many-to-one relationship between SEXSKINS and CYCLES, the same CYCLES data will appear repeatedly, once for each related SEXSKINS row. In those cases where there is CYCLES row but no related SEXSKINS row the SEXSKINS columns will be NULL. Because a SEXSKINS row always has a related CYCLES row, and it is the CYCLES row that identifies the cycling female, when working with the SEXSKINS table alone it is difficult to tell which sexskin/PCS observations belong to which female. This view provides a convenient way to create and maintain the SEXSKINS/CYCLES combination.

Tip

It is usually a good idea to leave the Cid column unspecified (NULL) when maintaining SEXSKINS using this view. This view uses the rules described in the Sexual Cycle Determination section when the underlying tables are maintained to automatically determine the appropriate Cid values to use in the SEXSKINS rows when no Cid is supplied.

Definition

Figure 6.51. 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
     , sexskins.color AS color
  FROM cycles LEFT OUTER JOIN sexskins ON (cycles.cid = sexskins.cid)
;


Figure 6.52. 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.


Table 6.23. Columns in the CYCLES_SEXSKINS View

Column From Description
Cid CYCLES.Cid Arbitrary number uniquely identifying the CYCLES row.
Sname CYCLES.Sname Female that is cycling.
Seq CYCLES.Seq Number indicating the cycle's position in time within the sequence of all the cycles of the female. Counts from 1 upwards.
Series CYCLES.Series Number indicating with which CYCLES (Female Sexual Cycles) of continuous observation the cycle belongs.
Sxid SEXSKINS.Sxid Unique number identifying the sexskin observation.
Date SEXSKINS.Date Date-of-record of the sexual cycle transition event.
Size SEXSKINS.Size Measured sexskin size.
Color SEXSKINS.Color Observed sexskin color.

Readonly Columns

Both the Seq and Series columns are read only.

Warning

Changes to the Seq and Series columns are silently ignored.

Operations Allowed

Tip

In most cases Cid, Cpid, Seq, and Series should be unspecified (or specified as NULL), in which case Babase will compute and assign the correct values.

INSERT

Inserting a row into CYCLES_SEXSKINS or SEXSKINS_CYCLES inserts a row into SEXSKINS, as expected. A new row is never inserted into CYCLES. Either a Cid or a Sname must be supplied, it is usually preferable to supply a Sname. When a Sname is supplied Babase will determine the appropriate Cid value automatically. When a Cid is supplied and a CYCLES row already exists with the given Cid then the underlying CYCLES row is updated to conform with the inserted data.[256] Supplying a Cid serves only to identify a female. Babase automatically chooses which of a female's CYCLES to relate to the sexskin measurement based on the dates involved. For further information see the documentation of the SEXSKINS table.

UPDATE

Updating a row in CYCLES_SEXSKINS updates the underlying columns in CYCLES and SEXSKINS, as expected. However, the relationship between CYCLES and SEXSKINS introduces some complications.

Updating the Cid column updates[257] the Cid columns in both CYCLES and SEXSKINS. Setting all the SEXSKINS columns (Cid and Sxid excepted) to NULL causes the deletion of the SEXSKINS row. Setting SEXSKINS columns to a non-NULL value when all the SEXSKINS columns were NULL previously creates a new row in SEXSKINS.

DELETE

Deleting a row in CYCLES_SEXSKINS or SEXSKINS_CYCLES deletes the underlying row in SEXSKINS. The underlying row in CYCLES is never deleted.

CYCLES_SEXSKINS_SORTED (CYCLES_SEXSKINS, Sorted)

Contains one row for every row in the CYCLES_SEXSKINS view. This view is sorted for ease of maintenance.

Definition

Figure 6.53. Query Defining the CYCLES_SEXSKINS_SORTED 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
     , sexskins.color AS color
  FROM cycles LEFT OUTER JOIN sexskins ON (cycles.cid = sexskins.cid)
  ORDER BY cycles.sname, sexskins.date
;


Figure 6.54. Entity Relationship Diagram of the CYCLES_SEXSKINS_SORTED View

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


Table 6.24. Columns in the CYCLES_SEXSKINS_SORTED View

Column From Description
Cid CYCLES.Cid Arbitrary number uniquely identifying the CYCLES row.
Sname CYCLES.Sname Female that is cycling.
Seq CYCLES.Seq Number indicating the cycle's position in time within the sequence of all the cycles of the female. Counts from 1 upwards.
Series CYCLES.Series Number indicating with which CYCLES (Female Sexual Cycles) of continuous observation the cycle belongs.
Sxid SEXSKINS.Sxid Unique number identifying the sexskin observation.
Date SEXSKINS.Date Date-of-record of the sexual cycle transition event.
Size SEXSKINS.Size Measured sexskin size.
Color SEXSKINS.Color Observed sexskin color.

Operations Allowed

The operations allowed are as described in the CYCLES_SEXSKINS view.

MATERNITIES (completed reproductive events)

Contains one row for every birth or fetal loss, summarizing the reproductive event.

Caution

Pregnancies with no recorded outcome do not appear in this view. If this is a problem we can change this. (kop)

Definition

Figure 6.55. 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 6.56. Entity Relationship Diagram of the MATERNITIES View

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


Table 6.25. Columns in the MATERNITIES View

Column From Description
Mom CYCLES.Sname Identifier (Sname) of the mother.
Cid CYCLES.Cid Identifier of conception cycle.
Seq CYCLES.Seq Ordinal sequence of the conception cycle among all of the mother's cycles.
Series CYCLES.Series Series number. Ordinal position of the continuous period of observation during which the mother's conception cycle was recorded, among all of the periods of continuous observation of the mother.
Conceive CYCPOINTS.Cpid Identifier of the CYCPOINTS row containing the Zdate.
Zdate CYCPOINTS.Date Conception date-of-record.
Zdate_Grp MEMBERS.Grp Mother's group as of the conception date-of-record.
Edate CYCPOINTS.Edate Earliest possible date of conception.
Ldate CYCPOINTS.Ldate Latest possible date of conception.
Source CYCPOINTS.Source The origin of the conception date. This has bearing as to its accuracy.
Pid PREGS.Pid Identifier of the pregnancy.
Parity PREGS.Parity Parity of the pregnancy.
Child_Bioid BIOGRAPH.Bioid Identifier (Bioid) of the progeny.
Child BIOGRAPH.Sname Identifier (Sname) of the progeny.
Birth BIOGRAPH.Birth Birthdate of the progeny.

Operations Allowed

Only SELECT is allowed on MATERNITIES. INSERT, UPDATE, and DELETE are not allowed.

MTD_CYCLES (CYCLES and Mdate, Tdate, and Ddate CYCPOINTS data)

Contains one row for every row in CYCLES. Each row contains the CYCLES columns and separate columns for the related CYCPOINTS Mdate, Tdate, and Ddate information. Sexual cycles that do not have a Mdate, Tdate, or Ddate, where there is no such CYCPOINTS row, contain NULL where data are missing. This view provides a convenient way to connect the Mdates, Tdates, and Ddates of each cycle.

Definition

Figure 6.57. 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 6.58. 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.


Table 6.26. Columns in the MTD_CYCLES View

Column From Description
Cid CYCLES.Cid Arbitrary number uniquely identifying the CYCLES row.
Sname CYCLES.Sname Female that is cycling.
Seq CYCLES.Seq Number indicating the cycle's position in time within the sequence of all the cycles of the female. Counts from 1 upwards.
Series CYCLES.Series Number indicating with which series of continuous observation the cycle belongs.
Mcpid CYCPOINTS.Cpid Number uniquely identifying the Mdate's CYCPOINTS row, or NULL if the cycle has no Mdate.
Mdate CYCPOINTS.Date Date-of-record of the sexual cycle's Mdate, or NULL if the cycle has no Mdate.
Emdate CYCPOINTS.Edate Earliest possible date for the sexual cycle's Mdate, or NULL if the cycle has no Mdate or there is no Edate associated with the Mdate .
Lmdate CYCPOINTS.Ldate Latest possible date for the sexual cycle's Mdate, or NULL if the cycle has no Mdate or there is no Ldate associated with the Mdate.
Msource CYCPOINTS.Source Code indicating from whence the Mdate data were derived, or NULL if the cycle has no Mdate. This has a bearing as to its accuracy.
Tcpid CYCPOINTS.Cpid Number uniquely identifying the Tdate's CYCPOINTS row, or NULL if the cycle has no Tdate.
Tdate CYCPOINTS.Date Date-of-record of the sexual cycle's Tdate, or NULL if the cycle has no Tdate.
Etdate CYCPOINTS.Edate Earliest possible date for the sexual cycle's Tdate, or NULL if the cycle has no Tdate or there is no Edate associated with the Tdate.
Ltdate CYCPOINTS.Ldate Latest possible date for the sexual cycle's Tdate, or NULL if the cycle has no Tdate or there is no Ldate associated with the Tdate.
Tsource CYCPOINTS.Source Code indicating from whence the Tdate data were derived, or NULL if the cycle has no Tdate. This has a bearing as to its accuracy.
Dcpid CYCPOINTS.Cpid Number uniquely identifying the Ddate's CYCPOINTS row, or NULL if the cycle has no Ddate.
Ddate CYCPOINTS.Date Date-of-record of the sexual cycle's Ddate, or NULL if the cycle has no Ddate.
Eddate CYCPOINTS.Edate Earliest possible date for the sexual cycle's Ddate, or NULL if the cycle has no Ddate or there is no Edate associated with the Ddate.
Lddate CYCPOINTS.Ldate Latest possible date for the sexual cycle's Ddate, or NULL if the cycle has no Ddate or there is no Ldate associated with the Ddate.
Dsource CYCPOINTS.Source Code indicating from whence the Ddate data were derived, or NULL if the cycle has no Ddate. This has a bearing as to its accuracy.

Read-Only Columns

Warning

Any modifications to the Seq and Series columns are silently ignored.

Operations Allowed

INSERT

Inserting rows into MTD_CYCLES inserts rows into the underlying tables as expected. However, there are complications introduced due to the nature of the view. No row is inserted into CYCPOINTS for a particular Mdate, Tdate, or Ddate when the relevant Date, Edate, and Ldate columns are all NULL.

Unlike the CYCPOINTS.Source column, the "source" columns in this view default to D (data). Omitting a "source" column from an INSERT statement or specifying it as NULL results in the default value of D.

Tip

It is strongly recommended that the Cid, Mcpid, Tcpid, and Dcpid be assigned automatically by the system. To do this either do not specify a value for these columns or specify a value of NULL.

Caution

Babase automatically determines which CYCLES are related to which CYCPOINTS. The Mdates, Ddates, and Tdates inserted into MTD_CYCLES may not necessarily remain related to the same CYCLES row.

UPDATE

The MTD_CYCLES view may not be updated.

DELETE

Deleting a row from MTD_CYCLES deletes the underlying CYCLES and CYCPOINTS rows as expected.

SEXSKINS_CYCLES (CYCLES extended with SEXSKINS information)

Contains one row for every row in SEXSKINS. Each row contains the SEXSKINS columns and the related CYCLES columns. Because there is a many-to-one relationship between SEXSKINS and CYCLES, the same CYCLES data will appear repeatedly, once for each related SEXSKINS row. Because a SEXSKINS row always has a related CYCLES row, and it is the CYCLES row that identifies the cycling female, when working with the SEXSKINS table alone it is difficult to tell which sexskin/PCS observations belong to which female. This view provides a convenient way to create and maintain the SEXSKINS/CYCLES combination.

Tip

It is usually a good idea to leave the Cid column unspecified (NULL) when maintaining SEXSKINS using this view. This view uses the rules described in the Sexual Cycle Determination section when the underlying tables are maintained to automatically determine the appropriate Cid values to use in the SEXSKINS rows when no Cid is supplied.

Note

The SEXSKINS_CYCLES view is very similar to the CYCLES_SEXSKINS view. It is unclear which is more useful so both exist.

Definition

Figure 6.59. 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
     , sexskins.color AS color
  FROM sexskins, cycles
  WHERE cycles.cid = sexskins.cid
  ORDER BY cycles.sname, sexskins.date
;


Figure 6.60. 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.


Table 6.27. Columns in the SEXSKINS_CYCLES View

Column From Description
Cid CYCLES.Cid Arbitrary number uniquely identifying the CYCLES row.
Sname CYCLES.Sname Female that is cycling.
Seq CYCLES.Seq Number indicating the cycle's position in time within the sequence of all the cycles of the female. Counts from 1 upwards.
Series CYCLES.Series Number indicating with which CYCLES (Female Sexual Cycles) of continuous observation the cycle belongs.
Sxid SEXSKINS.Sxid Unique number identifying the sexskin observation.
Date SEXSKINS.Date Date-of-record of the sexual cycle transition event.
Size SEXSKINS.Size Measured sexskin size.
Color SEXSKINS.Color Observed sexskin color.

Readonly Columns

Both the Seq and Series columns are read only.

Warning

Changes to the Seq and Series columns are silently ignored.

Operations Allowed

Tip

In most cases Cid, Cpid, Seq, and Series should be unspecified (or specified as NULL), in which case Babase will compute and assign the correct values.

INSERT

Inserting a row into CYCLES_SEXSKINS or SEXSKINS_CYCLES inserts a row into SEXSKINS, as expected. A new row is never inserted into CYCLES. Either a Cid or a Sname must be supplied, it is usually preferable to supply a Sname. When a Sname is supplied Babase will determine the appropriate Cid value automatically. When a Cid is supplied and a CYCLES row already exists with the given Cid then the underlying CYCLES row is updated to conform with the inserted data.[258] Supplying a Cid serves only to identify a female. Babase automatically chooses which of a female's CYCLES to relate to the sexskin measurement based on the dates involved. For further information see the documentation of the SEXSKINS table.

UPDATE

Updating a row in SEXSKINS_CYCLES updates the underlying columns in CYCLES and SEXSKINS, as expected. However, the relationship between CYCLES and SEXSKINS introduces some complications.

Updating the Cid column updates[259] the Cid columns in both CYCLES and SEXSKINS.

DELETE

Deleting a row in CYCLES_SEXSKINS or SEXSKINS_CYCLES deletes the underlying row in SEXSKINS. The underlying row in CYCLES is never deleted.

SEXSKINS_CYCLES_SORTED (SEXSKINS_CYCLES, Sorted)

Contains one row for every row in the SEXSKINS_CYCLES view. This view is sorted for ease of maintenance.

Definition

Figure 6.61. Query Defining the SEXSKINS_CYCLES_SORTED 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
     , sexskins.color AS color
  FROM sexskins, cycles
  WHERE cycles.cid = sexskins.cid
  ORDER BY cycles.sname, sexskins.date
;


Figure 6.62. Entity Relationship Diagram of the SEXSKINS_CYCLES_SORTED View

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


Table 6.28. Columns in the SEXSKINS_CYCLES_SORTED View

Column From Description
Cid CYCLES.Cid Arbitrary number uniquely identifying the CYCLES row.
Sname CYCLES.Sname Female that is cycling.
Seq CYCLES.Seq Number indicating the cycle's position in time within the sequence of all the cycles of the female. Counts from 1 upwards.
Series CYCLES.Series Number indicating with which CYCLES (Female Sexual Cycles) of continuous observation the cycle belongs.
Sxid SEXSKINS.Sxid Unique number identifying the sexskin observation.
Date SEXSKINS.Date Date-of-record of the sexual cycle transition event.
Size SEXSKINS.Size Measured sexskin size.
Color SEXSKINS.Color Observed sexskin color.

Operations Allowed

The operations allowed are as described in the SEXSKINS_CYCLES view.

SEXSKINS_REPRO_NOTES (SEXSKINS extended with REPRO_NOTES)

Contains one row for every date that a female has a row in SEXSKINS and/or REPRO_NOTES. Each row contains all the columns from SEXSKINS and REPRO_NOTES, and may include the Sname column from CYCLES. This view provides a convenient way to insert and maintain data from both SEXSKINS and REPRO_NOTES, as both tables' data may be entered together.

When the female has SEXSKINS data for a date but not REPRO_NOTES, the columns exclusive to REPRO_NOTES — RNId and Note — will be NULL. When she has REPRO_NOTES data but not SEXSKINS, the columns exclusive to SEXSKINS — Cid, Sxid, Size, and Color — will be NULL.

The source of the Sname and Date columns depends on whether the female has data in SEXSKINS for the row's Date. If yes, the Sname is the related CYCLES.Sname and the Date is the SEXSKINS.Date. If no — and she does have data in REPRO_NOTES for this date[260] — the Sname and Date are the REPRO_NOTES.Sname and Date.

Tip

It is usually a good idea to leave the Cid column unspecified (NULL) when maintaining SEXSKINS using this view. This view uses the rules described in the Sexual Cycle Determination section when the underlying tables are maintained to automatically determine the appropriate Cid values to use in the SEXSKINS rows when no Cid is supplied.

Definition

Figure 6.63. Query Defining the SEXSKINS_REPRO_NOTES View


SELECT COALESCE(cycles.sname, repro_notes.sname) AS sname
     , COALESCE(sexskins.date, repro_notes.date) AS date
     , sexskins.cid AS cid
     , sexskins.sxid AS sxid
     , sexskins.size AS size
     , sexskins.color AS color
     , repro_notes.rnid AS rnid
     , repro_notes.note AS note
  FROM sexskins
  JOIN cycles
    ON cycles.cid = sexskins.cid
  FULL OUTER JOIN repro_notes
    ON repro_notes.sname = cycles.sname
       AND repro_notes.date = sexskins.date
;


Figure 6.64. Entity Relationship Diagram of the SEXSKINS_REPRO_NOTES View

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


Table 6.29. Columns in the SEXSKINS_REPRO_NOTES View

Column From Description
Sname

COALESCE(cycles.sname
       , repro_notes.sname)

Female under observation.
Date

COALESCE(sexskins.date
       , repro_notes.date)

Date of observation.
Cid SEXSKINS.Cid Unique identifier for the related CYCLES row, or NULL if there are no rows in SEXSKINS for this Date.
Sxid SEXSKINS.Sxid Unique identifier for the sexskin observation, or NULL if there are no rows in SEXSKINS for this Date.
Size SEXSKINS.Size Observed sexskin size, or NULL if there are no rows in SEXSKINS for this Date.
Color SEXSKINS.Color Observed sexskin color, or NULL if there are no rows in SEXSKINS for this Date.
RNId REPRO_NOTES.RNId Unique identifier for the reproductive note, or NULL if there are no rows in REPRO_NOTES for this Date.
Note REPRO_NOTES.Note Text of the reproductive note, or NULL if there are no rows in REPRO_NOTES for this Date.

Operations Allowed

Tip

In most cases the Cid should be unspecified (or specified as NULL), in which case Babase will compute and assign the correct value.

INSERT

Inserting a row into SEXSKINS_REPRO_NOTES inserts rows into SEXSKINS and/or REPRO_NOTES, as expected. Either a Cid or Sname must be supplied, but it is usually preferable to supply the Sname. When Sname is supplied, Babase will determine the appropriate Cid value automatically.

When all of the columns exclusive to SEXSKINS — Cid, Sxid, Size, and Color — are NULL, the view will not attempt to insert a row into SEXSKINS.

When both of the columns exclusive to REPRO_NOTES — RNId and Note — are NULL, the view will not attempt to insert a row into REPRO_NOTES.

Each insert to this view must insert something somewhere. It is an error for all of the table-exclusive columns listed above to be NULL.

UPDATE

Updating a row in SEXSKINS_REPRO_NOTES updates the underlying columns in SEXSKINS and REPRO_NOTES (if any), as expected.

DELETE

Deleting a row from SEXSKINS_REPRO_NOTES deletes the underlying columns from SEXSKINS and REPRO_NOTES (if any), as expected.



[256] There is little use in attempting to update CYCLES because updates to the the Seq and Series columns are silently ignored and changing Sname is not allowed.

[257] Or attempts to update, as Babase may not allow these columns to be updated.

[258] There is little use in attempting to update CYCLES because updates to the the Seq and Series columns are silently ignored and changing Sname is not allowed.

[259] Or attempts to update, as Babase may not allow these columns to be updated.

[260] This is implicit, because if she also has no data in REPRO_NOTES, then there won't be a row in the view at all.


Page generated: 2024-03-06T15:02:34-05:00.