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.
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.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);
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. |
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.[257] 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[258] 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.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;
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. |
The operations allowed are as described in the CYCLES_SEXSKINS 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.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);
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. |
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.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;
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. |
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 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.
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.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;
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. |
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.[259] 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.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;
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. |
The operations allowed are as described in the SEXSKINS_CYCLES view.
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[261] — the Sname and Date are the REPRO_NOTES.Sname and Date.
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.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;
Table 6.29. Columns in the SEXSKINS_REPRO_NOTES View
Column | From | Description |
---|---|---|
Sname |
|
Female under observation. |
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. |
In most cases the Cid should be unspecified (or
specified as NULL
), in which case Babase will compute
and assign the correct value.
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
.
Updating a row in SEXSKINS_REPRO_NOTES updates the underlying columns in SEXSKINS and REPRO_NOTES (if any), as expected.
Deleting a row from SEXSKINS_REPRO_NOTES deletes the underlying columns from SEXSKINS and REPRO_NOTES (if any), as expected.
[257] 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.
[258] Or attempts to update, as Babase may not allow these columns to be updated.
[259] 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.
[260] Or attempts to update, as Babase may not allow these columns to be updated.
[261] 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.