Social and Multiparty Interactions
Prev  Chapter 6. The Babase Views  Next

Social and Multiparty Interactions

ACTOR_ACTEES (Complete social interactions, INTERACT_DATA extended twice with PARTS)

Contains one row for every row in INTERACT_DATA. Each row contains a column for the actor and a column for the actee. The actor and actee are retrieved from the PARTS table, when there is no related parts row the actor or actee is NULL.

This view is somewhat useful for the maintenance and analysis of social interaction data.[232] It's primarily optimized for speed and so finds it's best use when writing queries.

Definition

Figure 6.36. 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 6.37. Entity Relationship Diagram of the ACTOR_ACTEES View

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


Table 6.16. Columns in the ACTOR_ACTEES View

Column From Description
Iid INTERACT_DATA.Iid Identifier of the interaction.
Sid INTERACT_DATA.Sid Identifier of the sample, if any, during which the data was collected.
Act INTERACT_DATA.Act The kind of interaction.
Date INTERACT_DATA.Date The date of the interaction.
Start INTERACT_DATA.Start The time the interaction began.
Stop INTERACT_DATA.Stop The time the interaction ended.
Observer INTERACT_DATA.Observer The observer who recorded the interaction.
Actorid PARTS.Partid The Partid of the actor's PARTS row.
Actor PARTS.Sname The Sname of the actor in the interaction, when there is a PARTS row for the actor. Otherwise, the value 998.
Actor_Grp MEMBERS.Grp The Grp of the actor on the date of the interaction.
Acteeid PARTS.Partid The Partid of the actee's PARTS row.
Actee PARTS.Sname The Sname of the actee in the interaction, when there is a PARTS row for the actee. Otherwise, the value 998.
Actee_Grp MEMBERS.Grp The Grp of the actee on the date of the interaction.

Operations Allowed

The Actor and Actee columns must not be NULL or 998 when inserting into or updating this view.

When inserting or updating ACTOR_ACTEES the values of the Actor_Grp and Actee_Grp columns must either be NULL or match the group recorded for the individual for that day in MEMBERS, if such a row exists. Inserting and updating ACTOR_ACTEES cannot affect group membership.

Tip

It is usually a good idea to leave the computed columns unspecified (NULL) when maintaining social interactions using this view.

INSERT

Inserting a row into ACTOR_ACTEES inserts a row into INTERACT[233] and two rows, one for the actor and one for the actee, into PARTS, as expected.

To insert an actor without an actee (or vice versa) use a NULL value for the Actee (or Actor).

Tip

When entering a new social interaction it is usually a good idea to leave Iid unspecified (or specified as NULL). In this case Babase will compute a new Iid and use it appropriately in the new PARTS rows. Likewise the Actorid and Acteeid columns are usually best left NULL, in which case Babase will also create appropriate values.

UPDATE

Updating a row in ACTOR_ACTEES updates the underlying columns in INTERACT and PARTS, as expected.

An actor or actees PARTS row can be deleted or inserted by UPDATE of ACTOR_ACTEES. To insert an new PARTS row supply a Sname for the actor or actee where there was none. To delete a actor or or actee set the Sname to NULL.

When deleting an actor or actee either the corresponding Actorid/Acteeid value must be set to NULL, or the corresponding Actorid/Acteeid value must be unaltered.

Tip

The Actor and Actee cannot be switched with a update operation.[234] Delete the interaction and re-create it instead.

DELETE

Deleting a row in ACTOR_ACTEES deletes the underlying row in INTERACT and the two underlying rows in PARTS, as expected.

ACTOR_ACTEES_EX (ACTOR_ACTEES, EXtended with computed columns)

Contains one row for every row in the INTERACT view. The view is like the ACTOR_ACTEES view but contains additional computed columns which may be useful for query purposes. These columns are those the INTERACT view adds to the INTERACT_DATA table.

This view is somewhat useful for the analysis of social interaction data. It's primarily optimized for analysis and so finds it's best use when writing queries.

Definition

Figure 6.38. 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 6.39. Entity Relationship Diagram of the ACTOR_ACTEES_EX View

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


Table 6.17. Columns in the ACTOR_ACTEES_EX View

Column From Description
Iid INTERACT.Iid Identifier of the interaction.
Sid INTERACT.Sid Identifier of the sample, if any, during which the data was collected.
Act INTERACT.Act The kind of interaction.
Date INTERACT.Date The date of the interaction.
Jdate INTERACT.Date (computed) The date of the interaction, in Julian date form.
Start INTERACT.Start The time the interaction began.
Startspm INTERACT.Start (computed) The time the interaction begin (Start), represented as the number of seconds past midnight. This is useful for computing and analyzing time intervals outside of the PostgreSQL environment.
Stop INTERACT.Stop The time the interaction ended.
Stopspm INTERACT.Stop (computed) The time the interaction ended (Stop), represented as the number of seconds past midnight. This is useful for computing and analyzing time intervals outside of the PostgreSQL environment.
Observer INTERACT_DATA.Observer The observer who recorded the interaction.
Actorid PARTS.Partid The Partid of the actor's PARTS row.
Actor PARTS.Sname The Sname of the actor in the interaction, when there is a PARTS row for the actor. Otherwise, the value 998.
Actor_Grp MEMBERS.Grp The Grp of the actor on the date of the interaction.
Acteeid PARTS.Partid The Partid of the actee's PARTS row.
Actee PARTS.Sname The Sname of the actee in the interaction, when there is a PARTS row for the actee. Otherwise, the value 998.
Actee_Grp MEMBERS.Grp The Grp of the actee on the date of the interaction.

Read-Only Columns

The Actor_Grp, Actee_Grp, Jdate, Startspm, and Stopspm columns are computed. Attempts to put a value into these columns raise an error if the new value is not NULL or does not correspond to the computed value.

Tip

Best practice is to omit computed columns from inserts and updates.

Operations Allowed

The operations allowed are as described in the ACTOR_ACTEES_EX view. In particular, the Actor and Actee columns must not be NULL or 998 when inserting into or updating this view.

ACTOR_ACTEES_EX_SORTED (ACTOR_ACTEES_EX, Sorted)

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

This view is less efficient than any of the other ACTOR_ACTEES like views.

Definition

Figure 6.40. Query Defining the ACTOR_ACTEES_EX_SORTED 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
     , actee.partid AS acteeid
     , COALESCE(actee.sname, '998'::CHAR(3)) AS actee
  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')
  ORDER BY interact.act
         , interact.date
         , interact.start
         , interact.stop
         , actor.sname
         , actee.sname
         , interact.iid;


Figure 6.41. Entity Relationship Diagram of the ACTOR_ACTEES_EX_SORTED View

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


Table 6.18. Columns in the ACTOR_ACTEES_EX_SORTED View

Column From Description
Iid INTERACT.Iid Identifier of the interaction.
Sid INTERACT.Sid Identifier of the sample, if any, during which the data was collected.
Act INTERACT.Act The kind of interaction.
Date INTERACT.Date The date of the interaction.
Jdate INTERACT.Date (computed) The date of the interaction, in Julian date form.
Start INTERACT.Start The time the interaction began.
Startspm INTERACT.Start (computed) The time the interaction begin (Start), represented as the number of seconds past midnight. This is useful for computing and analyzing time intervals outside of the PostgreSQL environment.
Stop INTERACT.Stop The time the interaction ended.
Stopspm INTERACT.Stop (computed) The time the interaction ended (Stop), represented as the number of seconds past midnight. This is useful for computing and analyzing time intervals outside of the PostgreSQL environment.
Observer INTERACT_DATA.Observer The observer who recorded the interaction.
Actorid PARTS.Partid The Partid of the actor's PARTS row.
Actor PARTS.Sname The Sname of the actor in the interaction, when there is a PARTS row for the actor. Otherwise, the value 998.
Actor_Grp MEMBERS.Grp The Grp of the actor on the date of the interaction.
Acteeid PARTS.Partid The Partid of the actee's PARTS row.
Actee PARTS.Sname The Sname of the actee in the interaction, when there is a PARTS row for the actee. Otherwise, the value 998.
Actee_Grp MEMBERS.Grp The Grp of the actee on the date of the interaction.

Operations Allowed

The operations allowed are as described in the ACTOR_ACTEES_EX view.

ACTOR_ACTEES_SORTED (ACTOR_ACTEES, Sorted)

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

This view is more efficient than either the ACTOR_ACTEES_EX or the ACTOR_ACTEES_EX_SORTED views, but less efficient than the ACTOR_ACTEES view.

Definition

Figure 6.42. Query Defining the ACTOR_ACTEES_SORTED 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
     , actee.partid AS acteeid
     , COALESCE(actee.sname, '998'::CHAR(3)) AS actee
  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')
  ORDER BY interact_data.act
         , interact_data.date
         , interact_data.start
         , interact_data.stop
         , actor.sname
         , actee.sname
         , interact_data.iid;


Figure 6.43. Entity Relationship Diagram of the ACTOR_ACTEES_SORTED View

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


Table 6.19. Columns in the ACTOR_ACTEES_SORTED View

Column From Description
Iid INTERACT_DATA.Iid Identifier of the interaction.
Sid INTERACT_DATA.Sid Identifier of the sample, if any, during which the data was collected.
Act INTERACT_DATA.Act The kind of interaction.
Date INTERACT_DATA.Date The date of the interaction.
Start INTERACT_DATA.Start The time the interaction began.
Stop INTERACT_DATA.Stop The time the interaction ended.
Observer INTERACT_DATA.Observer The observer who recorded the interaction.
Actorid PARTS.Partid The Partid of the actor's PARTS row.
Actor PARTS.Sname The Sname of the actor in the interaction, when there is a PARTS row for the actor. Otherwise, the value 998.
Actor_Grp MEMBERS.Grp The Grp of the actor on the date of the interaction.
Acteeid PARTS.Partid The Partid of the actee's PARTS row.
Actee PARTS.Sname The Sname of the actee in the interaction, when there is a PARTS row for the actee. Otherwise, the value 998.
Actee_Grp MEMBERS.Grp The Grp of the actee on the date of the interaction.

Operations Allowed

The operations allowed are as described in the ACTOR_ACTEES view.

INTERACT (INTERACT_DATA, with enhanced dates and times)

Contains one row for every row in INTERACT_DATA. There is no difference between this view and the INTERACT_DATA table, other than the view extends the INTERACT_DATA table with additional date and time columns that transform the corresponding INTERACT_DATA columns in useful and interesting ways, and the view is sorted by Iid.

Definition

Figure 6.44. Query Defining the INTERACT View


SELECT iid AS iid
     , interact_data.sid AS sid
     , interact_data.act AS act
     , acts.class AS class
     , interact_data.date AS date
     , julian(interact_data.date) AS jdate
     , interact_data.start AS start
     , spm(interact_data.start) AS startspm
     , stop AS stop
     , spm(interact_data.stop) AS stopspm
     , interact_data.observer AS observer
  FROM interact_data
       JOIN acts
            ON (acts.act = interact_data.act);


Figure 6.45. Entity Relationship Diagram of the INTERACT View

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


Table 6.20. Columns in the INTERACT View

Column From Description
Iid INTERACT_DATA.Iid Identifier of the interaction.
Sid INTERACT_DATA.Sid Identifier of the point observation collection, if any, during which the data was collected.
Act INTERACT_DATA.Act The kind of interaction.
Date INTERACT_DATA.Date The date of the interaction.
Jdate INTERACT_DATA.Date (computed) The date of the interaction, in Julian date form.
Start INTERACT_DATA.Start The time the interaction began.
Startspm INTERACT_DATA.Start (computed) The time the interaction begin (Start), represented as the number of seconds past midnight. This is useful for computing and analyzing time intervals outside of the PostgreSQL environment.
Stop INTERACT_DATA.Stop The time the interaction ended.
Stopspm INTERACT_DATA.Stop (computed) The time the interaction ended (Stop), represented as the number of seconds past midnight. This is useful for computing and analyzing time intervals outside of the PostgreSQL environment.
Observer INTERACT_DATA.Observer The observer who recorded the interaction.

Read-Only Columns

Warning

Any modifications to the Jdate, Startspm, or Stopspm columns are silently ignored.

Operations Allowed

INSERT

Inserting a row into INTERACT inserts a row into INTERACT_DATA, as expected.

UPDATE

Updating a row in INTERACT updates the underlying columns in INTERACT_DATA, as expected.

DELETE

Deleting a row in INTERACT deletes the underlying row in INTERACT_DATA.

INTERACT_SORTED

Contains one row for every row in the INTERACT view. There is no difference between this view and the INTERACT view, other than that this view is sorted by Iid.

Definition

Figure 6.46. Query Defining the INTERACT_SORTED 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
     , interact_data.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)
  ORDER BY iid;


Figure 6.47. Entity Relationship Diagram of the INTERACT_SORTED View

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


Table 6.21. Columns in the INTERACT_SORTED View

Column From Description
Iid INTERACT_DATA.Iid Identifier of the interaction.
Sid INTERACT_DATA.Sid Identifier of the point observation collection, if any, during which the data was collected.
Act INTERACT_DATA.Act The kind of interaction.
Date INTERACT_DATA.Date The date of the interaction.
Jdate INTERACT_DATA.Date (computed) The date of the interaction, in Julian date form.
Start INTERACT_DATA.Start The time the interaction began.
Startspm INTERACT_DATA.Start (computed) The time the interaction begin (Start), represented as the number of seconds past midnight. This is useful for computing and analyzing time intervals outside of the PostgreSQL environment.
Stop INTERACT_DATA.Stop The time the interaction ended.
Stopspm INTERACT_DATA.Stop (computed) The time the interaction ended (Stop), represented as the number of seconds past midnight. This is useful for computing and analyzing time intervals outside of the PostgreSQL environment.
Observer INTERACT_DATA.Observer The observer who recorded the interaction.

Operations Allowed

The operations allowed are as described in the INTERACT view.

MPI_EVENTS (Dyadic social interactions that comprise multiparty interaction collections, MPIS joined with MPI_DATA extended twice with MPI_PARTS)

Contains one row for every row in MPI_DATA. Each row contains a column for the actor and a column for the actee. The actor and actee are retrieved from the MPI_PARTS table, when there is no related parts row the actor or actee is NULL. The MPIS table supplies the date of the interaction and there are further self-joins to the MPI_DATA, MPIACTS, and MPI_PARTS tables to compute help request circumstances and outcome.

This view is useful for the analysis of multiparty interaction data.[235].

Definition

Figure 6.48. 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 6.49. Entity Relationship Diagram of the MPI_EVENTS View

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


Table 6.22. Columns in the MPI_EVENTS View

Column From Description
Mpiid MPIS.Mpiid Identifier of the multiparty interaction collection.
Date MPIS.Date The date of the multiparty interaction collection.
Context_type MPIS.Context_type The context type of the multiparty interaction collection.
Context MPIS.MPIS-Context Text describing the context of the multiparty interaction collection.
Mpidid MPI_DATA.Mpidid Identifier of the dyadic interaction.
Seq MPI_DATA.Seq Number that orders the dyadic interaction in time within the multiparty interaction collection. The first dyadic interaction has a Seq value of 1, the second a Seq value of 2, etc. Identical Seq values indicate interactions which occurred simutainously.
Mpiact MPI_DATA.MPIAct The kind of dyadic interaction.
Actorid MPI_PARTS.Mpipid The Mpipid of the actor's MPI_PARTS row.
Actor MPI_PARTS.Sname The Sname of the actor in the interaction.
Unkactor MPI_PARTS.Unksname The PARTUNKS.Unksname code which denotes why the actor is unknown.
Acteeid MPI_PARTS.Mpipid The Mpipid of the actee's MPI_PARTS row.
Actee MPI_PARTS.Sname The Sname of the actee in the interaction.
Unkactee MPI_PARTS.Unksname The PARTUNKS.Unksname code which denotes why the actee is unknown.
Solicited Computed A boolean: TRUE or FALSE, or NULL. NULL when the act is not an act of help (MPIACTS.MPIAct is FALSE). Whether or not the help given was solicited with a request for help -- whether MPIACTS.Kind is R, from a previous (having a smaller MPI_DATA.Seq) MPI act's MPI_DATA.MPIAct value where the actor and actee match. For more details see the Figure 6.48.
Decided Computed A boolean: TRUE or FALSE. Whether or not the result of the MPI was decided when the event took place -- obtained from the MPIACTS.Decided value of the MPI_DATA.MPIAct of the first (MPI_DATA.Seq = 1) interaction of the MPI.
Helped MPI_DATA.Helped Whether or not help was given in response to the request for help. NULL when the event is not a request for help.
Active MPI_DATA.Active Whether the help given was active or passive. NULL when the event is not a request for help or no help was forthcoming.

Operations Allowed

Caution

Attempts to change the computed columns, Solicited and Decided, are silently ignored.

Tip

It is usually a good idea to leave the computed columns, as well as the automatically assigned ID columns, unspecified (NULL) when maintaining social interactions using this view.

INSERT

Inserting a row into MPI_EVENTs inserts a row into MPI_DATA and two rows, one for the actor and one for the actee, into MPI_PARTS, as expected. It also may insert a row into the MPIS table.

Warning

The presence or absence of a Mpiid value determines whether or not a MPIS row is created. When Mpiid is NULL (or the column is not specified) a new MPIS row is created. When a non-NULL value is supplied the given Mpiid identifies the existing multiparty interaction collection, a MPIS row, to which the new dyadic interaction is added.

Warning

The value of the Date, Context_type, and MPIS-Context columns are ignored when the supplied Mpiid identifies an existing MPIS row.

Warning

The PostgreSQL nextval() function cannot be part of an INSERT expression which assigns a value to this view's Mpiid or Mpidid columns.

Tip

When entering a new social interaction it is usually a good idea to leave Mpidid unspecified (or specified as NULL). In this case Babase will compute a new Mpiid and use it appropriately in the new MPI_PARTS rows. Likewise the Actorid and Acteeid columns are usually best left NULL, in which case Babase will also create appropriate values.

UPDATE

Updating a row in MPI_EVENTS updates the underlying columns in MPIS, MPI_DATA, and MPI_PARTS as expected.

Tip

Because updates to the database occur one table at a time, and because Babase does not allow an interaction to have the same individual as both the actor and actee, it is impossible to switch the Actor and Actee with a update operation. Delete the interaction and re-create it instead.

DELETE

Deleting a row in MPI_EVENTS deletes the underlying row in MPI_DATA and the two underlying rows in MPI_PARTS, as expected. The underlying MPIS row is deleted when the last related MPI_DATA row is deleted.

POINTS (POINT_DATA, with enhanced times)

Contains one row for every row in POINT_DATA. There is no difference between this view and the POINT_DATA table, other than the view contains additional columns that may be useful derivatives of the Ptime column.

Tip

Use this view instead of the POINT_DATA table.

Definition

Figure 6.50. 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 6.51. Entity Relationship Diagram of the POINTS View

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


Table 6.23. Columns in the POINTS View

Column From Description
Pntid POINT_DATA.Pntid Identifier of the point observation.
Sid POINT_DATA.Sid Identifier of the sample during which the data was collected.
Activity POINT_DATA.Activity The kind of activity the focal was engaged in.
Posture POINT_DATA.Posture The posture of the focal.
Foodcode POINT_DATA.Foodcode The food eaten, if any.
Ptime POINT_DATA.Ptime The time the observation was recorded, with a precision of 1 second.
Ptimespm POINT_DATA.Ptime (computed) The time the point observation was recorded (Ptime) represented as the number of seconds past midnight. This is useful for computing and analyzing time intervals outside of the PostgreSQL environment.

Read-Only Columns

The Ptimespm column is computed. Attempts to put a value into the Ptimespm column raise an error if the new value is not NULL or does not correspond to the computed value.

Tip

Best practice is to omit computed columns from inserts and updates.

Operations Allowed

INSERT

Inserting a row into POINTS inserts a row into POINT_DATA, as expected.

UPDATE

Updating a row in POINTS updates the underlying columns in POINT_DATA, as expected.

DELETE

Deleting a row in POINTS deletes the underlying row in POINT_DATA.

POINTS_SORTED (POINTS, Sorted)

Contains one row for every row in POINTS. There is no difference between this view and the POINTS view, other than this view is sorted by Sid, and within that by Ptime.

Definition

Figure 6.52. Query Defining the POINTS_SORTED View


SELECT pntid AS pntid
     , sid AS sid
     , activity AS activity
     , posture AS posture
     , foodcode AS foodcode
     , ptime AS ptime
     , ptimespm AS ptimespm
  FROM points
  ORDER BY sid, ptime;


Figure 6.53. Entity Relationship Diagram of the POINTS_SORTED View

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


Table 6.24. Columns in the POINTS_SORTED View

Column From Description
Pntid POINT_DATA.Pntid Identifier of the point observation.
Sid POINT_DATA.Sid Identifier of the sample during which the data was collected.
Activity POINT_DATA.Activity The kind of activity the focal was engaged in.
Posture POINT_DATA.Posture The posture of the focal.
Foodcode POINT_DATA.Foodcode The food eaten, if any.
Ptime POINT_DATA.Ptime The time the observation was recorded, with a precision of 1 second.
Ptimespm POINT_DATA.Ptime (computed) The time the point observation was recorded (Ptime) represented as the number of seconds past midnight. This is useful for computing and analyzing time intervals outside of the PostgreSQL environment.

Operations Allowed

The operations allowed are as described in the POINTS view.

SAMPLES_GOFF (SAMPLES, with the Group OF the Focal)

Contains one row for every row in SAMPLES. This row is identical to the SAMPLES table except that it has an additional column Grp_of_focal which contains the group of the focal on the sampling date.[236]

Definition

Figure 6.54. Query Defining the SAMPLES_GOFF View


SELECT samples.sid AS sid
     , samples.date AS date
     , samples.stime AS stime
     , samples.observer AS observer
     , samples.stype AS stype
     , samples.grp AS grp
     , samples.sname AS sname
     , samples.mins AS mins
     , samples.minsis AS minsis
     , samples.programid AS programid
     , samples.setupid AS setupid
     , samples.palmtop AS palmtop
     , members.grp AS grp_of_focal
  FROM members, samples
  WHERE members.sname = samples.sname
        AND members.date = CAST(samples.date AS DATE);


Figure 6.55. Entity Relationship Diagram of the SAMPLES_GOFF View

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


Table 6.25. Columns in the SAMPLES_GOFF View

Column From Description
Sid SAMPLES.Sid Identifier of the sample.
Date SAMPLES.Date Date of sample collection.
STime SAMPLES.Stime Time of sample collection.
Observer SAMPLES.Observer Observer who collected the sample.
Stype SAMPLES.Stype A code indicating the nature of the focal individual and the data collection procedure used.
Grp SAMPLES.Grp The group the observation team sampled.
Sname SAMPLES.Sname Identifier of sampled individual.
Mins SAMPLES.Mins Sample duration in minutes, from start to finish.
Minsis SAMPLES.Minsis Number of minutes of sample data.
Programid SAMPLES.Programid Identifer of the program used on the palmtop to collect the sample.
Setupid SAMPLES.Setupid The version of the setup file used to drive the data collection program on the palmtop.
Palmtop SAMPLES.Palmtop The palmtop computer used to collect the sample.
Grp_of_focal MEMBERS.Grp The group of the sampled individual.

Operations Allowed

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


[232] Babase should contain one and exactly one actor and one and exactly one actee for every interaction. The ACTOR_ACTEES view does left outer joins of the INTERACT view with the PARTS table so that invalid data can still be maintained in the event the stated relationships do not exist. To ignore invalid data, e.g. for purposes of analysis, write a query that does regular joins instead of the left outer joins used by the view.

[233] and hence INTERACT_DATA

[234] This restriction is leftover from implementation limitations in older versions of PostgreSQL and could now be removed if so desired.

[235] Babase should contain one and exactly one actor and one and exactly one actee for every interaction. The MPI_EVENTS view does left outer joins of the MPI_DATA table with the MPI_PARTS table so that invalid data can be observed in the event the stated relationships do not exist. To ignore invalid data, e.g. for purposes of analysis, write a query that does regular joins instead of the left outer joins used by the view.

[236] This is expected to be the group that is sampled, but you never know.

Prev  Up  Next
Sexual Cycles  Home  Darting

Page generated: 2016-07-22T23:08:27-04:00.