Table: `ndb.repositoryinstitutions`#

Description#

A lookup table of institutions that are repositories for fossil specimens. Table is referenced by the RepositorySpecimens table.

TODO: Expand this description with: - What data does this table store? - What is the business/research purpose? - How is this data collected or generated? - Are there any important caveats or data quality issues?

Table Structure#

Schema: ndb | Table Comment: A lookup table of institutions that are repositories for fossil specimens. Table is referenced by the RepositorySpecimens table.

Statistics#

Metric	Value
Row Count	394
Total Size	80 kB
Table Size	40 kB
Indexes Size	32 kB

Relationships#

Primary Key: repositoryid

No foreign key relationships.

Referenced By:

TODO: Document which tables reference this table (will be auto-detected in validation).

Data Dictionary#

Column	Type	Nullable	Default	Constraints	Description
`repositoryid`	integer	✗	`nextval('ndb.seq_repository...`	PRIMARY KEY	An arbitrary Repository identification number. Repositories include museums, university departments, and various governmental agencies.
`acronym`	character varying(64)	✗	`-`	-	A unique acronym for the repository. Many repositories have well-established acronyms (e.g. AMNH = American Museum of Natural History); however, there is no official list. Various acronyms have been used for some institutions, and in some cases the same acronym has been used for different institutions. Consequently, the database acronym may differ from the acronym used in some publications. For example, «CMNH» has been used for the Carnegie Museum of Natural History, the Cleveland Museum of Natural History, and the Cincinnati Museum of Natural History. In Neotoma, two of these institutions were assigned different acronyms, ones that have been used for them in other publications: CM – Carnegie Museum of Natural History, CLM – Cleveland Museum of Natural History.
`repository`	character varying(128)	✗	`-`	-	The full name of the repository.
`notes`	text	✓	`-`	-	Free form notes or comments about the repository, especially notes about name changes, closures, and specimen transfers. In some cases, it is known that the specimens were transferred, but their current disposition may be uncertain.
`recdatecreated`	timestamp without time zone	✗	`timezone('UTC'::text, now())`	-
`recdatemodified`	timestamp without time zone	✗	`-`	-

TODO: Review column descriptions and add comments where missing.

Usage Examples#

Example 1: Basic Selection#

-- Get recent records from repositoryinstitutions
SELECT *
FROM repositoryinstitutions
ORDER BY repositoryid DESC
LIMIT 10;

Purpose: Retrieve the 10 most recent records from repositoryinstitutions

Example 2: Count Records#

-- Count total records
SELECT COUNT(*) as total_records
FROM repositoryinstitutions;

Purpose: Get the total number of records in repositoryinstitutions

Example 3: Filter by Date Range#

-- Get records within a date range
SELECT *
FROM repositoryinstitutions
WHERE recdatecreated >= '2024-01-01'
  AND recdatecreated < '2025-01-01'
ORDER BY recdatecreated DESC;

Purpose: Retrieve records from repositoryinstitutions within a specific date range

Example 4: Aggregate Data#

-- Aggregate records by acronym
SELECT 
    acronym,
    COUNT(*) as count
FROM repositoryinstitutions
GROUP BY acronym
ORDER BY count DESC
LIMIT 10;

Purpose: Count records grouped by acronym

TODO: Add more specific examples relevant to common research questions or operational tasks.

Data Quality Notes#

TODO: Document: - Known data quality issues - Validation rules - Expected data ranges - Update frequency and mechanisms - Any ETL processes that populate this table

Maintenance#

Data Owner: TODO: Assign owner
Update Frequency: TODO: Document frequency
Last Major Schema Change: TODO: Document when schema last changed

Table: ndb.repositoryinstitutions#