Inventory

LOCATIONS_FREE (LOCATIONS available for storage)

Contains one row for every LOCATIONS row whose Location is not used in NUCACID_DATA or TISSUE_DATA. That is, it contains one row for every location that is not occupied by a nucleic acid or tissue sample.

Tip

Use this view when looking for locations available to store new samples.

Caution

This view makes no attempt to treat non-unique locations (those whose Is_Unique is FALSE) differently from unique ones. A non-unique location that might in reality be available will not appear in this view if it is already in use in NUCACID_DATA or TISSUE_DATA.

Definition

Figure 6.104. Query Defining the LOCATIONS_FREE View


SELECT locations.locid AS locid
     , locations.institution AS institution
     , locations.location AS location
     , locations.is_unique AS is_unique
  FROM locations
  WHERE NOT EXISTS (SELECT 1
                      FROM tissue_data
                      WHERE tissue_data.locid = locations.locid)
    AND NOT EXISTS (SELECT 1
                      FROM nucacid_data
                      WHERE nucacid_data.locid = locations.locid)
;


Figure 6.105. Entity Relationship Diagram of the LOCATIONS_FREE View

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


Table 6.50. Columns in the LOCATIONS_FREE View

Column From Description
LocId LOCATIONS.LocId Identifier for the row
Institution LOCATIONS.Institution Organization, building, etc. describing the locale of this row's Location
Location LOCATIONS.Location Specific place/position available for a sample.
Is_Unique LOCATIONS.Is_Unique Whether or not this location can be used more than once.

Operations Allowed

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

NUCACID_CONCS (NUCACID_CONC_DATA, extended)

Contains one row for every NUCACID_CONC_DATA row. This view shows all the data from NUCACID_CONC_DATA, but also includes a descriptive column from NUCACID_CONC_METHODS to clarify the meaning of the Conc_Method column, and an additional calculated column that shows the concentration in nanograms per microliter (ng/μL).

This view is also useful for adding data. New quantifications can be inserted in either pg/μL or ng/μL, and the system will perform unit conversions as needed.

Tip

Use this view instead of the NUCACID_CONC_DATA table.

Warning

Do not assume that the number of significant figures shown in the Pg_ul and Ng_ul columns is the "true" number of significant figures used for a quantification. See Example 3.2 for more.

Definition

Figure 6.106. Query Defining the NUCACID_CONCS View


SELECT nucacid_conc_data.nacid AS nacid
     , nucacid_conc_data.naid AS naid
     , local_1.localid AS localid_1
     , local_2.localid AS localid_2
     , nucacid_conc_data.conc_method AS conc_method
     , nucacid_conc_methods.descr AS method_descr
     , nucacid_conc_data.conc_date AS conc_date
     , nucacid_conc_data.pg_ul AS pg_ul
     , (nucacid_conc_data.pg_ul / 1000)::numeric(10,4) AS ng_ul
  FROM nucacid_conc_data
  JOIN nucacid_conc_methods
    ON nucacid_conc_methods.conc_method = nucacid_conc_data.conc_method
  LEFT JOIN nucacid_local_ids AS local_1
    ON local_1.naid = nucacid_conc_data.naid
       AND local_1.institution = 1
  LEFT JOIN nucacid_local_ids AS local_2
    ON local_2.naid = nucacid_conc_data.naid
       AND local_2.institution = 2
;


Figure 6.107. Entity Relationship Diagram of the NUCACID_CONCS View

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


Table 6.51. Columns in the NUCACID_CONCS View

Column From Description
NACId NUCACID_CONC_DATA.NACId The unique identifier for this quantification.
NAId NUCACID_CONC_DATA.NAId The unique identifier for the quantified sample.
LocalId_1 NUCACID_LOCAL_IDS.LocalId The local identifier used for this sample at Institution #1, if any.
LocalId_2 NUCACID_LOCAL_IDS.LocalId The local identifier used for this sample at Institution #2, if any.
Conc_Method NUCACID_CONC_DATA.Conc_Method The method of quantification used to determine this concentration.
Method_Descr NUCACID_CONC_METHODS.Descr A textual description of the quantification method used to determine this concentration.
Conc_Date NUCACID_CONC_DATA.Conc_Date The date of the quantification.
Pg_ul NUCACID_CONC_DATA.Pg_ul The concentration of the sample according to this quantification, in pg/μL.
Ng_ul NUCACID_CONC_DATA.Pg_ul / 1000 The concentration of the sample according to this quantification, in ng/μL.

Operations Allowed

INSERT

Inserting a row into NUCACID_CONCS inserts a row into NUCACID_CONC_DATA as expected.

If no NAId is provided, one or both LocalId columns can be provided instead to look up the intended NAId. If LocalId_1 and/or LocalId_2 values are provided, these must be related to a single NUCACID_LOCAL_IDS.NAId value. If a NAId value is also provided, it must equal that single NAId that is related to the provided LocalId column(s).

At least one of either the Conc_Method or Method_Descr columns must be provided to determine the correct value to insert into NUCACID_CONC_DATA.Conc_Method. If Method_Descr is provided, it is used to look up the appropriate Conc_Method value from NUCACID_CONC_METHODS. If both are provided, the provided values must be related in NUCACID_CONC_METHODS.

The inserted NUCACID_CONCS row must have a non-NULL value in Pg_ul or Ng_ul, or both. If both, Pg_ul must equal Ng_ul × 1000. When Pg_ul is provided, the value is inserted as the new NUCACID_CONC_DATA.Pg_ul. When Ng_ul is provided and Pg_ul is not, the Ng_ul is multiplied by 1000 and inserted as the new NUCACID_CONC_DATA.Pg_ul.

UPDATE

Updating a row in NUCACID_CONCS updates the underlying NUCACID_CONC_DATA row, as discussed below.

The NAId may be updated in this view via updates to the NAId column only. Updates to the LocalId_1 and LocalId_2 columns result in an error[279].

A row's underlying NUCACID_CONC_DATA.Conc_Method may be updated in this view via updates to the Conc_Method or Method_Descr columns. If more than one of these values is updated, all the newly-updated values must be related, as discussed above.

A row's underlying NUCACID_CONC_DATA.Pg_ul may be updated in this view via updates to Pg_ul, Ng_ul, or both, as discussed above.

Updating Conc_Method or Conc_Date updates the columns in the underlying NUCACID_CONC_DATA row, as expected.

DELETE

Deleting a row from NUCACID_CONCS deletes a row from NUCACID_CONC_DATA as expected.

NUCACIDS (NUCACID_DATA, extended)

Contains one row for every NUCACID_DATA row. This view includes columns from BIOGRAPH, LOCATIONS, NUCACID_CREATORS, NUCACID_LOCAL_IDS, NUCACID_SOURCES, TISSUE_DATA, and UNIQUE_INDIVS in order to portray information about nucleic acid samples in a more user-friendly format than that in NUCACID_DATA. This view can also be used to upload data.

Tip

Use this view — or the NUCACIDS_W_CONC view if the sample's concentration is important to you — instead of the NUCACID_DATA table.

When uploading data with this view, it is an error if creator initials cannot be unambiguously interpreted. In the admittedly-unlikely event that there is a creator whose initials legitimately include the separator character "/", this creator's initials cannot be inserted via this view. In this case, the offending creator code must be removed from the data, then manually inserted into NUCACID_CREATORS.

Definition

Figure 6.108. Query Defining the NUCACIDS View


WITH concat_creators AS (SELECT naid
                              , string_agg(creator, '/' ORDER BY naid, nacrid) AS created_by
                           FROM nucacid_creators
                           GROUP BY naid)
SELECT nucacid_data.naid AS naid
     , nucacid_data.tid AS tid
     , nucacid_data.locid AS locid
     , locations.institution AS institution
     , locations.location AS location
     , local_1.localid AS localid_1
     , local_2.localid AS localid_2
     , tissue_data.uiid AS uiid
     , unique_indivs.popid AS popid
     , unique_indivs.individ AS individ
     , biograph.sname AS sname
     , nucacid_data.name_on_tube AS name_on_tube
     , nucacid_data.nucacid_type AS nucacid_type
     , tissue_data.tissue_type AS tissue_type
     , nucacid_data.creation_date AS creation_date
     , concat_creators.created_by AS created_by
     , nucacid_data.creation_method AS creation_method
     , nucacid_sources.source_naid AS source_na
     , nucacid_sources.relationship AS source_na_relationship
     , nucacid_data.initial_vol_ul AS initial_vol_ul
     , nucacid_data.actual_vol_ul AS actual_vol_ul
     , nucacid_data.actual_vol_date AS actual_vol_date
     , nucacid_data.notes AS notes
  FROM nucacid_data
  JOIN locations
    ON locations.locid = nucacid_data.locid
  JOIN tissue_data
    ON tissue_data.tid = nucacid_data.tid
  JOIN unique_indivs
    ON unique_indivs.uiid = tissue_data.uiid
  LEFT JOIN biograph
    ON biograph.bioid::text = unique_indivs.individ
       AND unique_indivs.popid = 1
  LEFT JOIN nucacid_local_ids AS local_1
    ON local_1.naid = nucacid_data.naid
       AND local_1.institution = 1
  LEFT JOIN nucacid_local_ids AS local_2
    ON local_2.naid = nucacid_data.naid
       AND local_2.institution = 2
  LEFT JOIN nucacid_sources
    ON nucacid_sources.naid = nucacid_data.naid
  LEFT JOIN concat_creators
    ON concat_creators.naid = nucacid_data.naid
;


Figure 6.109. Entity Relationship Diagram of the NUCACIDS View

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


Table 6.52. Columns in the NUCACIDS View

Column From Description
NAId NUCACID_DATA.NAId Identifier for this sample.
TId NUCACID_DATA.TId Identifier for this nucleic acid sample's source tissue sample.
LocId NUCACID_DATA.LocId Identifier for this sample's Institution-Location pair.
Institution LOCATIONS.Institution Identifier for this sample's locale.
Location LOCATIONS.Location The current place/position of the sample.
LocalId_1 NUCACID_LOCAL_IDS.LocalId The local identifier, if any, used for this sample at Institution #1.
LocalId_2 NUCACID_LOCAL_IDS.LocalId The local identifier, if any, used for this sample at Institution #2.
UIId TISSUE_DATA.UIId Identifier for the source individual.
PopId UNIQUE_INDIVS.PopId Identifier for the population of the source individual.
IndivId UNIQUE_INDIVS.IndivId Name/ID of the source individual.
Sname BIOGRAPH.Sname Sname of the source individual, if any.
Name_on_Tube NUCACID_DATA.Name_on_Tube Name/identifier written on the sample's label.
NucAcid_Type NUCACID_DATA.NucAcid_Type The nucleic acid sample type.
Tissue_Type TISSUE_DATA.Tissue_Type The source tissue's sample type.
Creation_Date NUCACID_DATA.Creation_Date Date that the sample was created.
Created_By NUCACID_CREATORS.Creator Initials of all the personnel involved with the creation of this sample concatenated into a single string, ordered by their related NUCACID_CREATORS.NACrId and separated by a "/". If no related creators, then NULL.
Creation_Method NUCACID_DATA.Creation_Method The method used to create the sample.
Source_NA NUCACID_SOURCES.Source_NAId NAId of this nucleic acid sample's source nucleic acid sample, if any.
Source_NA_Relationship NUCACID_SOURCES.Relationship A textual description of how this NAId is related to its Source_NA.
Initial_Vol_ul NUCACID_DATA.Initial_Vol_ul Volume in microliters of the sample when first created.
Actual_Vol_ul NUCACID_DATA.Actual_Vol_ul The amount of sample (in microliters) remaining in the tube, as of the Actual_Vol_Date.
Actual_Vol_Date NUCACID_DATA.Actual_Vol_Date The date that the Actual_Vol_ul was determined.
Notes NUCACID_DATA.Notes Miscellaneous notes about the sample.

Operations Allowed

INSERT

Inserting a row into NUCACIDS inserts a row into NUCACID_DATA. Additional rows may be inserted into NUCACID_CREATORS, NUCACID_LOCAL_IDS, and NUCACID_SOURCES, as discussed below.

For each "/"-separated creator provided in the Created_By column, one row is inserted into the NUCACID_CREATORS table, with the related NAId. A NULL Created_By column is interpreted to mean that there are no rows to add to NUCACID_CREATORS; it does not result in a new NUCACID_CREATORS row with a NULL Creator value.

When either or both of the LocalId_1 and LocalId_2 columns is not NULL, a row is inserted into NUCACID_LOCAL_IDS for each non-NULL value provided. The new NUCACID_LOCAL_IDS.LocalId is the provided LocalId_N value, and the new Institution is 1 (for LocalId_1) or 2 (for LocalId_2).

When Source_NA and Source_NA_Relationship are not NULL, a row is inserted into NUCACID_SOURCES. The new NUCACID_SOURCES.NAId is the NAId of the new NUCACID_DATA row, the new Source_NAId is the provided Source_NA, and the new Relationship is the provided Source_NA_Relationship.

To indicate a sample's current locale and location, either the LocId column or both the Institution and Location columns must be provided. If all three are provided, the Institution and Location must be equal to the related columns in LOCATIONS for the provided LocId.

It is not necessary to provide UIId, PopId, IndivId, or Sname values. Any such values that are provided must equal the related values for the source tissue sample (the TId).

It is not necessary to provide Tissue_Type. If provided, it must match the related TISSUE_DATA.Tissue_Type value.

UPDATE

Updating a row in NUCACIDS updates the underlying row in NUCACID_DATA, as expected. Related rows in NUCACID_CREATORS, NUCACID_LOCAL_IDS and NUCACID_SOURCES may be inserted, updated, or deleted, as discussed below.

When an update changes the Created_By column, all prior rows in NUCACID_CREATORS are deleted, and new rows are inserted as described above. When an update doesn't change the Created_By column, the related data in NUCACID_CREATORS are unaffected.

When LocalId_1 or LocalId_2 is changed, the related NUCACID_LOCAL_IDS.LocalId value is also changed as expected, except when the "old" or "new" value is NULL. If the change is from NULL to non-NULL, a new NUCACID_LOCAL_IDS row is inserted, as discussed above. If from non-NULL to NULL, the related NUCACID_LOCAL_IDS row is deleted.

When Source_NA and/or Source_NA_Relationship is changed, the related NUCACID_SOURCES.Source_NAId and/or Relationship is also changed as expected, except when the "old" or "new" value is NULL. If the change is from NULL to non-NULL, a new NUCACID_SOURCES row is inserted, as discussed above. If both columns are changed from non-NULL to NULL, the related NUCACID_SOURCES row is deleted.

Updating the Institution and Location columns updates the related LocId column, as expected.

Attempts to update the UIId, PopId, IndivId, Sname, or Tissue_Type columns returns an error.

Tip

To change any of these values for a nucleic acid sample, you should update only the TId column or update the related TISSUE_DATA row.

DELETE

Deleting a row from NUCACIDS deletes the underlying row from NUCACID_DATA, as expected. Related rows in NUCACID_CREATORS, NUCACID_LOCAL_IDS, and NUCACID_SOURCES, if any, are also deleted.

NUCACIDS_W_CONC (NUCleic ACIDS With CONCentration data)

This view contains one row for every row in NUCACID_DATA. It includes columns from BIOGRAPH, LOCATIONS, NUCACID_CREATORS, NUCACID_LOCAL_IDS, NUCACID_SOURCES, TISSUE_DATA, and UNIQUE_INDIVS, as in the NUCACIDS view. It also includes several additional columns derived from NUCACID_CONC_DATA that indicate the sample's concentration according to various specific quantification methods.

Tip

Use this view — or just NUCACIDS if the sample's concentration is not important to you — instead of the NUCACID_DATA table.

Warning

A nucleic acid sample's concentration may be quantified more than once with the same method, so this view shows only the concentration from the most recent NUCACID_CONC_DATA.Conc_Date for each method. Because of this, concentrations whose related Conc_Date is NULL are not included in this view.

Definition

Figure 6.110. Query Defining the NUCACIDS_W_CONC View


WITH last_quants AS (SELECT DISTINCT
                            naid
                          , conc_method
                          , last_value(pg_ul) OVER w AS last_pg_ul
                          , last_value(conc_date) OVER w AS lastdate
                       FROM nucacid_conc_data
                       WHERE conc_date IS NOT NULL
                       WINDOW w AS (PARTITION BY naid, conc_method
                                    ORDER BY conc_date
                                      RANGE BETWEEN UNBOUNDED PRECEDING
                                        AND UNBOUNDED FOLLOWING))
   , concat_creators AS (SELECT naid
                              , string_agg(creator, '/' ORDER BY naid, nacrid) AS created_by
                           FROM nucacid_creators
                           GROUP BY naid)

SELECT nucacid_data.naid AS naid
     , nucacid_data.tid AS tid
     , nucacid_data.locid AS locid
     , locations.institution AS institution
     , locations.location AS location
     , local_1.localid AS localid_1
     , local_2.localid AS localid_2
     , tissue_data.uiid AS uiid
     , unique_indivs.popid AS popid
     , unique_indivs.individ AS individ
     , biograph.sname AS sname
     , nucacid_data.name_on_tube AS name_on_tube
     , nucacid_data.nucacid_type AS nucacid_type
     , tissue_data.tissue_type AS tissue_type
     , nucacid_data.creation_date AS creation_date
     , concat_creators.created_by AS created_by
     , nucacid_data.creation_method AS creation_method
     , nucacid_sources.source_naid AS source_na
     , nucacid_sources.relationship AS source_na_relationship
     , nucacid_data.initial_vol_ul AS initial_vol_ul
     , nucacid_data.actual_vol_ul AS actual_vol_ul
     , nucacid_data.actual_vol_date AS actual_vol_date
     , nucacid_data.notes AS notes
     , qpcr.last_pg_ul AS qpcr_pg_ul
     , qpcr.lastdate AS qpcr_lastdate
     , (nanodrop.last_pg_ul / 1000)::numeric(10,4) AS nanodrop_ng_ul
     , nanodrop.lastdate AS nanodrop_lastdate
     , (qubit.last_pg_ul / 1000)::numeric(10,4) AS qubit_ng_ul
     , qubit.lastdate AS qubit_lastdate
     , (bioanalyzer.last_pg_ul / 1000)::numeric(10,4) AS bioanalyzer_ng_ul
     , bioanalyzer.lastdate AS bioanalyzer_lastdate
     , (quantit.last_pg_ul / 1000)::numeric(10,4) AS quantit_ng_ul
     , quantit.lastdate AS quantit_lastdate
  FROM nucacid_data
  JOIN locations
    ON locations.locid = nucacid_data.locid
  JOIN tissue_data
    ON tissue_data.tid = nucacid_data.tid
  JOIN unique_indivs
    ON unique_indivs.uiid = tissue_data.uiid
  LEFT JOIN biograph
    ON biograph.bioid::text = unique_indivs.individ
       AND unique_indivs.popid = 1
  LEFT JOIN nucacid_local_ids AS local_1
    ON local_1.naid = nucacid_data.naid
       AND local_1.institution = 1
  LEFT JOIN nucacid_local_ids AS local_2
    ON local_2.naid = nucacid_data.naid
       AND local_2.institution = 2
  LEFT JOIN nucacid_sources
    ON nucacid_sources.naid = nucacid_data.naid
  LEFT JOIN concat_creators
    ON concat_creators.naid = nucacid_data.naid
  LEFT JOIN last_quants AS qpcr
    ON qpcr.conc_method = 1
       AND qpcr.naid = nucacid_data.naid
  LEFT JOIN last_quants AS nanodrop
    ON nanodrop.conc_method = 2
       AND nanodrop.naid = nucacid_data.naid
  LEFT JOIN last_quants AS qubit
    ON qubit.conc_method = 3
       AND qubit.naid = nucacid_data.naid
  LEFT JOIN last_quants AS bioanalyzer
    ON bioanalyzer.conc_method = 4
       AND bioanalyzer.naid = nucacid_data.naid
  LEFT JOIN last_quants AS quantit
    ON quantit.conc_method = 5
       AND quantit.naid = nucacid_data.naid
;


Figure 6.111. Entity Relationship Diagram of the NUCACIDS_W_CONC View

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


Table 6.53. Columns in the NUCACIDS_W_CONC View

Column From Description
NAId NUCACID_DATA.NAId Identifier for this sample.
TId NUCACID_DATA.TId Identifier for this nucleic acid sample's source tissue sample.
LocId NUCACID_DATA.LocId Identifier for this sample's Institution-Location pair.
Institution LOCATIONS.Institution Identifier for this sample's locale.
Location LOCATIONS.Location The current place/position of the sample.
LocalId_1 NUCACID_LOCAL_IDS.LocalId The local identifier, if any, used for this sample at Institution #1.
LocalId_2 NUCACID_LOCAL_IDS.LocalId The local identifier, if any, used for this sample at Institution #2.
UIId TISSUE_DATA.UIId Identifier for the source individual.
PopId UNIQUE_INDIVS.PopId Identifier for the population of the source individual.
IndivId UNIQUE_INDIVS.IndivId Name/ID of the source individual.
Sname BIOGRAPH.Sname Sname of the source individual, if any.
Name_on_Tube NUCACID_DATA.Name_on_Tube Name/identifier written on the sample's label.
NucAcid_Type NUCACID_DATA.NucAcid_Type The nucleic acid sample type.
Tissue_Type TISSUE_DATA.Tissue_Type The source tissue's sample type.
Creation_Date NUCACID_DATA.Creation_Date Date that the sample was created.
Created_By NUCACID_CREATORS.Creator Initials of all the personnel involved with the creation of this sample concatenated into a single string, ordered by their related NUCACID_CREATORS.NACrId and separated by a "/". If no related creators, then NULL.
Creation_Method NUCACID_DATA.Creation_Method The method used to create the sample.
Source_NA NUCACID_SOURCES.Source_NAId NAId of this nucleic acid sample's source nucleic acid sample, if any.
Source_NA_Relationship NUCACID_SOURCES.Relationship A textual description of how this NAId is related to its Source_NA.
Initial_Vol_ul NUCACID_DATA.Initial_Vol_ul Volume in microliters of the sample when first created.
Actual_Vol_ul NUCACID_DATA.Actual_Vol_ul The amount of sample (in microliters) remaining in the tube, as of the Actual_Vol_Date.
Actual_Vol_Date NUCACID_DATA.Actual_Vol_Date The date that the Actual_Vol_ul was determined.
Notes NUCACID_DATA.Notes Miscellaneous notes about the sample.
QPCR_Pg_ul NUCACID_CONC_DATA.Pg_ul The concentration of this sample in pg/μL, according to the most recent quantitative PCR.
QPCR_LastDate NUCACID_CONC_DATA.Conc_Date The date of this row's QPCR_Pg_ul was determined; the date of the most recent QPCR.
Nanodrop_Ng_ul NUCACID_CONC_DATA.Pg_ul × 1000 The concentration of this sample in ng/μL, according to the most recent Nanodrop measurement.
Nanodrop_LastDate NUCACID_CONC_DATA.Conc_Date The date that this row's Nanodrop_Ng_ul was determined; the date of the most recent Nanodrop measurement.
Qubit_Ng_ul NUCACID_CONC_DATA.Pg_ul × 1000 The concentration of this sample in ng/μL, according to the most recent Qubit measurement.
Qubit_LastDate NUCACID_CONC_DATA.Conc_Date The date that this row's Qubit_Ng_ul was determined; the date of the most recent Qubit measurement.
Bioanalyzer_Ng_ul NUCACID_CONC_DATA.Pg_ul × 1000 The concentration of this sample in ng/μL, according to the most recent Bioanalyzer run.
Bioanalyzer_LastDate NUCACID_CONC_DATA.Conc_Date The date that this row's Bioanalyzer_Ng_ul was determined; the date of the most recent Bioanalyzer run.
Quantit_Ng_ul NUCACID_CONC_DATA.Pg_ul × 1000 The concentration of this sample in ng/μ/L, according to the most recent Quant-iT assay.
Quantit_LastDate NUCACID_CONC_DATA.Conc_Date The date that this row's Quantit_Ng_ul was determined; the date of the most-recent Quant-iT assay.

Operations Allowed

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

TISSUES

Contains one row for every TISSUE_DATA row. This view includes columns from BIOGRAPH, LOCATIONS, TISSUE_LOCAL_IDS, and UNIQUE_INDIVS in order to portray information about tissue samples in a more user-friendly format than that in TISSUE_DATA. This view can also be used to upload data.

Tip

Use this view instead of the TISSUE_DATA table.

Definition

Figure 6.112. Query Defining the TISSUES View


SELECT tissue_data.tid AS tid
     , tissue_data.locid
     , locations.institution AS institution
     , locations.location AS location
     , local_1.localid AS localid_1
     , local_2.localid AS localid_2
     , tissue_data.uiid AS uiid
     , unique_indivs.popid AS popid
     , unique_indivs.individ AS individ
     , biograph.sname AS sname
     , tissue_data.name_on_tube AS name_on_tube
     , tissue_data.collection_date AS collection_date
     , tissue_data.collection_time AS collection_time
     , tissue_data.tissue_type AS tissue_type
     , tissue_data.storage_medium AS storage_medium
     , tissue_data.misid_status AS misid_status
     , tissue_data.collection_date_status AS collection_date_status
     , tissue_data.notes AS notes
  FROM tissue_data
  JOIN locations
    ON locations.locid = tissue_data.locid
  JOIN unique_indivs
    ON unique_indivs.uiid = tissue_data.uiid
  LEFT JOIN biograph
    ON biograph.bioid::text = unique_indivs.individ
       AND unique_indivs.popid = 1
  LEFT JOIN tissue_local_ids AS local_1
    ON local_1.tid = tissue_data.tid
       AND local_1.institution = 1
  LEFT JOIN tissue_local_ids AS local_2
    ON local_2.tid = tissue_data.tid
       AND local_2.institution = 2
;


Figure 6.113. Entity Relationship Diagram of the TISSUES View

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


Table 6.54. Columns in the TISSUES View

Column From Description
TId TISSUE_DATA.TId Identifier for this sample.
LocId TISSUE_DATA.LocId Identifier for this sample's Institution-Location pair.
Institution LOCATIONS.Institution Identifier for this sample's locale.
Location LOCATIONS.Location The current place/position of the sample.
LocalId_1 TISSUE_LOCAL_IDS.LocalId The local identifier, if any, used for this sample at Institution #1.
LocalId_2 TISSUE_LOCAL_IDS.LocalId The local identifier, if any, used for this sample at Institution #2.
UIId TISSUE_DATA.UIId Identifier for the source individual.
PopId UNIQUE_INDIVS.PopId Identifier for the population of the source individual.
IndivId UNIQUE_INDIVS.IndivId Name of the source individual.
Sname BIOGRAPH.Sname Sname of the source individual, if any.
Name_on_Tube TISSUE_DATA.Name_on_Tube Name or ID of the source individual, according to the label on the tube.
Collection_Date TISSUE_DATA.Collection_Date Date that the sample was collected.
Collection_Time TISSUE_DATA.Collection_Time Time that the sample was collected.
Tissue_Type TISSUE_DATA.Tissue_Type The tissue sample type.
Storage_Medium TISSUE_DATA.Storage_Medium The medium used for storing the sample.
Misid_Status TISSUE_DATA.Misid_Status The mis-identification status of the sample.
Collection_Date_Status TISSUE_DATA.Collection_Date_Status The status of this Collection_Date
Notes TISSUE_DATA.Notes Miscellaneous notes about the sample.

Operations Allowed

INSERT

Inserting a row into TISSUES inserts a row into TISSUE_DATA. Additional rows may be inserted into TISSUE_LOCAL_IDS, as discussed below.

When either or both of the LocalId_1 and LocalId_2 columns is not NULL, a row is inserted into TISSUE_LOCAL_IDS for each non-NULL value provided. The new TISSUE_LOCAL_IDS.LocalId is the provided LocalId_N value, and the new Institution is 1 (for LocalId_1) or 2 (for LocalId_2).

It is not necessary to provide all of the UIId, PopId, IndivId, and Sname columns; there must only be enough information provided to identify a single UIId. Specifically: there must be a UIId, a PopId and an IndivId, or an Sname. When more than one of those is provided, all provided values must be related to the same UNIQUE_INDIVS.UIId.

To indicate a sample's current locale and location, either the LocId column or both the Institution and Location columns must be provided. If all three are provided, the Institution and Location must be equal to the related columns in LOCATIONS for the provided LocId.

UPDATE

Updating a row in TISSUES updates the underlying row in TISSUE_DATA, as expected. Related rows in TISSUE_LOCAL_IDS may be inserted, updated, or deleted, as discussed below.

When LocalId_1 or LocalId_2 is changed, the related TISSUE_LOCAL_IDS.LocalId value is also changed, as expected. If this change is from NULL to non-NULL, a new TISSUE_LOCAL_IDS row is inserted, as discussed above. If from non-NULL to NULL, the related TISSUE_LOCAL_IDS row is deleted.

Updating a sample's UIId can be done by updating the UIId, PopId and IndivId, and/or Sname columns. Any such updates must correspond to exactly one UIId, as discussed above.

Updating the Institution and Location columns updates the related LocId column, as expected.

DELETE

Deleting a row from TISSUES deletes the underlying row from TISSUE_DATA, as expected. Related rows in TISSUE_LOCAL_IDS, if any, are also deleted.

TISSUES_HORMONES

Contains one row for every TISSUE_DATA row. This view includes columns from BIOGRAPH, LOCATIONS, TISSUE_LOCAL_IDS, UNIQUE_INDIVS, and HORMONE_SAMPLE_DATA in order to portray information about tissue samples in a more user-friendly format than that in TISSUE_DATA, especially samples that are used in hormone analysis. This view is also useful for uploading new tissue samples that will be used for hormone analysis; it provides a way to upload samples into TISSUE_DATA and HORMONE_SAMPLE_DATA simultaneously.

Definition

Figure 6.114. Query Defining the TISSUES_HORMONES View


SELECT tissue_data.tid AS tid
     , tissue_data.locid
     , locations.institution AS institution
     , locations.location AS location
     , local_1.localid AS localid_1
     , local_2.localid AS localid_2
     , tissue_data.uiid AS uiid
     , unique_indivs.popid AS popid
     , unique_indivs.individ AS individ
     , biograph.sname AS sname
     , tissue_data.name_on_tube AS name_on_tube
     , tissue_data.collection_date AS collection_date
     , tissue_data.collection_time AS collection_time
     , tissue_data.tissue_type AS tissue_type
     , tissue_data.storage_medium AS storage_medium
     , tissue_data.misid_status AS misid_status
     , tissue_data.collection_date_status AS collection_date_status
     , tissue_data.notes AS notes
     , hormone_sample_data.hsid AS hsid
     , hormone_sample_data.fzdried_date AS fzdried_date
     , hormone_sample_data.sifted_date AS sifted_date
     , hormone_sample_data.avail_mass_g AS avail_mass_g
     , hormone_sample_data.avail_date AS avail_date
     , hormone_sample_data.comments AS comments
  FROM tissue_data
  JOIN locations
    ON locations.locid = tissue_data.locid
  JOIN unique_indivs
    ON unique_indivs.uiid = tissue_data.uiid
  LEFT JOIN biograph
    ON biograph.bioid::text = unique_indivs.individ
       AND unique_indivs.popid = 1
  LEFT JOIN tissue_local_ids AS local_1
    ON local_1.tid = tissue_data.tid
       AND local_1.institution = 1
  LEFT JOIN tissue_local_ids AS local_2
    ON local_2.tid = tissue_data.tid
       AND local_2.institution = 2
  LEFT JOIN hormone_sample_data
    ON hormone_sample_data.tid = tissue_data.tid
;


Figure 6.115. Entity Relationship Diagram of the TISSUES_HORMONES View

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


Table 6.55. Columns in the TISSUES_HORMONES View

Column From Description
TId TISSUE_DATA.TId Identifier for this sample.
LocId TISSUE_DATA.LocId Identifier for this sample's Institution-Location pair.
Institution LOCATIONS.Institution Identifier for this sample's locale.
Location LOCATIONS.Location The current place/position of the sample.
LocalId_1 TISSUE_LOCAL_IDS.LocalId The local identifier, if any, used for this sample at Institution #1.
LocalId_2 TISSUE_LOCAL_IDS.LocalId The local identifier, if any, used for this sample at Institution #2.
UIId TISSUE_DATA.UIId Identifier for the source individual.
PopId UNIQUE_INDIVS.PopId Identifier for the population of the source individual.
IndivId UNIQUE_INDIVS.IndivId Name of the source individual.
Sname BIOGRAPH.Sname Sname of the source individual, if any.
Name_on_Tube TISSUE_DATA.Name_on_Tube Name or ID of the source individual, according to the label on the tube.
Collection_Date TISSUE_DATA.Collection_Date Date that the sample was collected.
Collection_Time TISSUE_DATA.Collection_Time Time that the sample was collected.
Tissue_Type TISSUE_DATA.Tissue_Type The tissue sample type.
Storage_Medium TISSUE_DATA.Storage_Medium The medium used for storing the sample.
Misid_Status TISSUE_DATA.Misid_Status The mis-identification status of the sample.
Collection_Date_Status TISSUE_DATA.Collection_Date_Status The status of this Collection_Date
Notes TISSUE_DATA.Notes Miscellaneous notes about the sample.
HSId HORMONE_SAMPLE_DATA.HSId User-generated identifier for the tissue sample.
FzDried_Date HORMONE_SAMPLE_DATA.FzDried_Date Date the sample was freeze-dried.
Sifted_Date HORMONE_SAMPLE_DATA.Sifted_Date Date the freeze-dried sample was sifted.
Avail_Mass_g HORMONE_SAMPLE_DATA.Avail_Mass_g Amount of sample (in g) remaining in the tube, as of the Avail_Date.
Avail_Date HORMONE_SAMPLE_DATA.Avail_Date Date that the Avail_Mass_g was determined.
Comments HORMONE_SAMPLE_DATA.Comments Miscellaneous notes/comments about this sample that are relevant only to hormone analysis.

Operations Allowed

Because the primary purpose of this view is facilitate working with TISSUE_DATA and HORMONE_SAMPLE_DATA rows simultaneously, this view only allows operations where there is a practical need to operate on the two tables simultaneously[280]. UPDATE is not allowed on TISSUES_HORMONES; there are no needs satisfied nor utility gained from being able to update both tables at once.

Tip

To update data that appear in this view, use the TISSUES or HORMONE_SAMPLES views.

INSERT

Inserting a row into TISSUES_HORMONES inserts a row into TISSUE_DATA, then a row into HORMONE_SAMPLE_DATA with the same TId. Additional rows may be inserted into TISSUE_LOCAL_IDS, as discussed above.

It is not necessary to provide all of the UIId, PopId, IndivId, and Sname columns, as discussed above.

To indicate a sample's current locale and location, either the LocId column or both the Institution and Location columns must be provided. If all three are provided, the Institution and Location must be equal to the related columns in LOCATIONS for the provided LocId.

DELETE

Deleting a row from TISSUES_HORMONES deletes the underlying row from TISSUE_DATA and from HORMONE_SAMPLE_DATA, as expected. Related rows in TISSUE_LOCAL_IDS, if any, are also deleted.



[279] Allowing updates via the the LocalId columns is certainly doable, but requires an uncomfortable amount of "magic". At the time of this writing it doesn't seem like a huge burden to only allow direct updates to NAId.

[280] Or, in a single transaction.


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