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 measurements belong to which female. This
view provides a convenient way to create and maintain the
SEXSKINS/CYCLES
combination.
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.
Figure 6.22. 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);
Table 6.10. 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. |
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.
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.[228] 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.
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[229] 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.
Contains one row for every row in the CYCLES_SEXSKINS view. This view is sorted for ease of maintenance.
Figure 6.24. 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
FROM cycles LEFT OUTER JOIN sexskins ON (cycles.cid = sexskins.cid)
ORDER BY cycles.sname, sexskins.date;
Table 6.11. 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. |
The operations allowed are as described in the CYCPOINTS_CYCLES view.
Contains one row for every birth or fetal loss, summarizing the reproductive event.
Pregnancies with no recorded outcome do not appear in this view. If this is a problem we can change this. (kop)
Figure 6.26. 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);
Table 6.12. 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. |
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.
Figure 6.28. 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;
Table 6.13. 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. |
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
.
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
.
Contains one row for every row in PCSKINS. There is no difference between this view and the PCSKINS table, other than the view is sorted by Sname by Date. This is convenient for maintaining the table.
Figure 6.30. 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;
Because the columns are those in PCSKINS, there is no description of the columns here.
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 measurements belong to which female. This view provides a convenient way to create and maintain the SEXSKINS/CYCLES combination.
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.
The SEXSKINS_CYCLES view is very similar to the CYCLES_SEXSKINS view. It is unclear which is more useful so both exist.
Figure 6.32. 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;
Table 6.14. 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. |
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.
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.[230] 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.
Contains one row for every row in the SEXSKINS_CYCLES view. This view is sorted for ease of maintenance.
Figure 6.34. 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
FROM sexskins, cycles
WHERE cycles.cid = sexskins.cid
ORDER BY cycles.sname, sexskins.date;
Table 6.15. 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. |
The operations allowed are as described in the CYCPOINTS_CYCLES view.
[228] 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.
[229] Or attempts to update, as Babase may not allow these columns to be updated.
[230] 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.
[231] Or attempts to update, as Babase may not allow these columns to be updated.