APPENDIX B: Metadata Table and Data Dictionary
The following metadata tables apply to the data that can be accessed both in SciServer and through using the APIs. For each API call, results can be filtered by agency designation using url parameter agency={AGENCY}. For example, https://prod.democratizing-data.tacc.utexas.edu/authors?agency=USDA
In each case below, the API returns data in the schema listed in the table, unless noted otherwise. The API endpoint can be found below each table.
agency_run: the table of runs for the different agencies and their datasets
https://prod.democratizing-data.tacc.utexas.edu/agency_runs
| Column name | Description | Data type | Length | Is nullable |
|---|---|---|---|---|
id | unique identifier to the agency_run table | bigint | 0 | NO |
agency | name of the agency for which the run was performed | varchar | 32 | NO |
version | version of the run for the agency; allows multiple versions on the same datasets, or possibly new runs for the same agency but with different input datasets | varchar | 32 | NO |
run_date | approximate date the run was performed | date | 0 | YES |
last_updated_date | last time the row was updated; generally the time of creation of the row | datetime | 0 | NO |
asjc: the table with the All Science Journal Classification codes which define the research area of a journal and the articles it contains
https://prod.democratizing-data.tacc.utexas.edu/asjc A publication's research areas are defined through Elsevier's All Science Journal Classification scheme. More information about this classification system can be found here: https://service.elsevier.com/app/answers/detail/a_id/15181/supporthub/scopus/
| Column name | Description | Data type | Length | Is nullable |
|---|---|---|---|---|
id | unique identifier for this entry | bigint | 0 | NO |
run_id | identifies the agency run for which this entry was determined, foreign key to agency_run.id | bigint | 0 | NO |
code | the All Science Journal Classification code which defines the research area of a journal and the articles it contains; there may be more than one ASJC code for each journal/publication, and the 334 codes are used here to provide a relatively precise definition of research area | bigint | 0 | NO |
label | the All Science Journal Classification name | nvarchar | -1 | YES |
last_updated_date | last time the row was updated; generally the time of creation of the row | datetime | 0 | NO |
|
author: the table with author information
https://prod.democratizing-data.tacc.utexas.edu/authors
| Column name | Description | Data type | Length | Is nullable |
|---|---|---|---|---|
id | unique identifier for this entry | bigint | 0 | NO |
run_id | identifies the agency run for which this entry was determined, foreign key to agency_run.id | bigint | 0 | NO |
external_id | id assigned by Elsevier to the author; this allow different authors to be identified across publications, even if they used different name variations | varchar | 128 | YES |
given_name | the unique given name of the author as determined by Elsevier | nvarchar | 150 | YES |
family_name | the unique family name of the author as determined by Elsevier | nvarchar | 150 | YES |
last_updated_date | last time the row was updated; generally the time of creation of the row | datetime | 0 | NO |
|
author_affiliation: the table linking authors to their affiliations in a publication
https://prod.democratizing-data.tacc.utexas.edu/author_affiliations
| Column name | Description | Data type | Length | Is nullable |
|---|---|---|---|---|
id | unique identifier for this entry | bigint | 0 | NO |
run_id | identifies the agency run for which this entry was determined, foreign key to agency_run.id | bigint | 0 | NO |
publication_author_id | identifies the publication_author here linked to the publication affiliation | bigint | 0 | YES |
publication_affiliation_id | identifies the publication_affiliation entry here linked to a publication author | bigint | 0 | YES |
last_updated_date | last time the row was updated; generally the time of creation of the row | date | 0 | NO |
|
dataset_alias: the datasets provided by an agency for a particular run and possible aliases
https://prod.democratizing-data.tacc.utexas.edu/dataset_aliases (Note that the endpoint https://prod.democratizing-data.tacc.utexas.edu/datasets joins dataset_alias and agency_run, the schema being a concatenation of the two.) (To filter dataset_alias by other parameters, use https://prod.democratizing-data.tacc.utexas.edu/topics/{topic_id}/datasets, https://prod.democratizing-data.tacc.utexas.edu/authors/{author_id}/datasets, https://prod.democratizing-data.tacc.utexas.edu/publications/{publication_id}/datasets)
| Column name | Description | Data type | Length | Is nullable |
|---|---|---|---|---|
id | unique identifier for this entry | bigint | 0 | NO |
run_id | identifies the agency run for which this entry was determined, foreign key to agency_run.id | bigint | 0 | NO |
alias_id | the alias_id as provided by Elsevier | bigint | 0 | NO |
parent_alias_id | identifies the parent dataset entry in this table, as identified by the alias_id | bigint | 0 | YES |
alias | the name of the data set or the alias depending on whether alias_id==parent_alias_id or not | varchar | 160 | YES |
alias_type | indicates the alias type; options include main, alias, acronym, doi | varchar | 50 | YES |
url | URL to information about the dataset | varchar | 2048 | YES |
last_updated_date | last time the row was updated; generally the time of creation of the row | datetime | 0 | NO |
|
dyad: the core table with dyads representing dataset references
https://prod.democratizing-data.tacc.utexas.edu/dyads
| Column name | Description | Data type | Length | Is nullable |
|---|---|---|---|---|
id | unique identifier for this entry | bigint | 0 | NO |
run_id | identifies the agency run for which this entry was determined, foreign key to agency_run.id | bigint | 0 | NO |
publication_id | foreign key the publication table's id column, identifying the publication within which the dataset reference represented by this dyad was identified | bigint | 0 | NO |
elsevier_id | REMOVE | int | 0 | NO |
dataset_alias_id | foreign key to the dataset_alias table's id column identifying the match made between this dyad and a dataset alias provided by an agency; if no such match was found this column has a NULL | bigint | 0 | YES |
alias_id | the intrinsic id assigned by Elsevier to the dataset alias, corresponding to the alias_id column in the dataset_alias table | bigint | 0 | YES |
mention_candidate | the phrase in the publication that was deemed by the algorithm to reflect a reference to a dataset | varchar | 1028 | NO |
snippet* | snippet of text surrounding the mention_candidate, meant to provide contextual information to reviewers/validators | varchar | -1 | YES |
last_updated_date | last time the row was updated; generally the time of creation of the row | datetime | 0 | NO |
is_fuzzy | column has value 1 if the matching between mention candidate and the dataset alias was performed using a fuzzy algorithm, 0 otherwise | bit | 0 | YES |
fuzzy_score | in case the matching between mention candidate and the dataset alias was performed using a fuzzy algorithm, this column stores the score indicating how certain match was deemed to be | real | 0 | YES |
*This column is set to NULL in the publicly available databases, and is only shown in restricted use databases.
dyad_model: the table with model scores for particular entries in the dyad table
https://prod.democratizing-data.tacc.utexas.edu/dyad_models
| Column name | Description | Data type | Length | Is nullable |
|---|---|---|---|---|
id | unique identifier for this entry | bigint | 0 | NO |
run_id | identifies the agency run for which this entry was determined, foreign key to agency_run.id | bigint | 0 | NO |
dyad_id | identifies the dyad | bigint | 0 | NO |
model_id | identifies the model | bigint | 0 | NO |
score | the score of this model for the dyad | real | 0 | YES |
last_updated_date | last time the row was updated; generally the time of creation of the row | datetime | 0 | NO |
|
issn: the table with ISSN/ISBN codes for the journal
https://prod.democratizing-data.tacc.utexas.edu/issns
| Column name | Description | Data type | Length | Is nullable |
|---|---|---|---|---|
id | unique identifier for this entry | bigint | 0 | NO |
run_id | identifies the agency run for which this entry was determined, foreign key to agency_run.id | bigint | 0 | NO |
journal_id | foreign key to the journal table's id column, identifying the journal for this ISSN | bigint | 0 | YES |
ISSN | the ISSN/ISBN codes for the referenced journal/source | varchar | 13 | YES |
last_updated_date | last time the row was updated; generally the time of creation of the row | datetime | 0 | NO |
|
journal: the table linking publications to the journal in which they appeared
https://prod.democratizing-data.tacc.utexas.edu/journals
| Column name | Description | Data type | Length | Is nullable |
|---|---|---|---|---|
id | unique identifier and primary key for this table | bigint | 0 | NO |
run_id | foreign key, identifier of the agency run for which this entry was determined | bigint | 0 | NO |
publisher_id | foreign key to the publisher table, identifying the publisher for this journal at the time the agency run was executed | bigint | 0 | YES |
external_id | the scopus ID for the journal/source | varchar | 128 | YES |
title | the name of the journal/source that the publication was published in | varchar | 1028 | NO |
cite_score | citescore is an Elsevier derived metric that measures the relative standing of a journal | decimal | 0 | YES |
last_updated_date | last time the row was updated; generally the time of creation of the row | datetime | 0 | NO |
|
model: the table with the Kaggle models that are run
https://prod.democratizing-data.tacc.utexas.edu/models
| Column name | Description | Data type | Length | Is nullable |
|---|---|---|---|---|
id | unique identifier for this entry | bigint | 0 | NO |
name | the name of the model | varchar | 32 | NO |
github_commit_url | the github url where the commit for this model can be found | varchar | 1024 | YES |
description | description of the model | nvarchar | -1 | YES |
last_updated_date | last time the row was updated; generally the time of the creation of the row | datetime | 0 | NO |
|
publication: the publications discovered in a run and their metadata
https://prod.democratizing-data.tacc.utexas.edu/publications (To filter publications using other parameters, use https://prod.democratizing-data.tacc.utexas.edu/topics/{topic_id}/publications, https://prod.democratizing-data.tacc.utexas.edu/authors/{author_id}/publications, https://prod.democratizing-data.tacc.utexas.edu/datasets/{parent_alias_id}/publications)
| Column name | Description | Data type | Length | Is nullable |
|---|---|---|---|---|
id | unique identifier for this entry | bigint | 0 | NO |
run_id | identifies the agency run for which this entry was determined, foreign key to agency_run.id | bigint | 0 | NO |
journal_id | foreign key to the journal for this publication | bigint | 0 | YES |
external_id | the scopus ID (for Elsevier publications) of this publication | varchar | 128 | YES |
title | title of the publication | varchar | 400 | YES |
doi | DOI of the publication | varchar | 80 | YES |
year | the year that the publication was published as recorded in scopus | int | 0 | YES |
month | the month of publication, which may not be available; this will be an integer value, such as 1 = January | int | 0 | YES |
pub_type | the type of publication, which includes: article, review, book, book chapter, letter | varchar | 30 | YES |
citation_count | the number of times this publication is cited in scopus | int | 0 | YES |
fw_citation_impact | the Field Weighted Citation impact (FWCI) for the publication, which is a measure for how impactful or important a publication is as measured through normalized citations; the number of times cited is divided by the expected number of citations of articles in the same year, subject and publication type, and the world average across papers is 1.0 for this measure; this measure also changes over time | float | 0 | YES |
last_updated_date | last time the row was updated; generally the time of creation of the row | datetime | 0 | NO |
|
publication_affiliation: the table with affiliations linked to a publication
https://prod.democratizing-data.tacc.utexas.edu/publication_affiliations
| Column name | Description | Data type | Length | Is nullable |
|---|---|---|---|---|
id | unique identifier of the affiliation table | bigint | 0 | NO |
run_id | identifies the agency run for which this entry was determined, foreign key to agency_run.id | bigint | 0 | NO |
external_id | id assigned by Elsevier to this affiliation | varchar | 128 | YES |
institution_name | the name of the institution to which an author was associated | nvarchar | 750 | YES |
address | the address of the author, most likely that of their institution | nvarchar | 750 | YES |
country_code | the three let6ter country code associated to the affiliation, most likely of the institution | nvarchar | 10 | YES |
last_updated_date | last time the row was updated; generally the time of creation of the row | datetime | 0 | NO |
city | the city for the address or institute in this affiliation | nvarchar | 128 | YES |
state | if appropriate, the state for the address or institute in this affiliation | nvarchar | 128 | YES |
postal_code | if appropriate, the postal code for this affiliation | nvarchar | 64 | YES |
|
publication_asjc: the table linking a publication to its ASJC code(s)
https://prod.democratizing-data.tacc.utexas.edu/publication_asjcs
| Column name | Description | Data type | Length | Is nullable |
|---|---|---|---|---|
id | unique identifier for this entry | bigint | 0 | NO |
run_id | identifies the agency run for which this entry was determined, foreign key to agency_run.id | bigint | 0 | NO |
publication_id | foreign key to publication table's id column, identifying the publication in this relation | bigint | 0 | NO |
asjc_id | foreign key to the ASJC | bigint | 0 | NO |
last_updated_date | last time the row was updated; generally the time of creation of the row | datetime | 0 | NO |
|
publication_author: the table linking publication and author tables
https://prod.democratizing-data.tacc.utexas.edu/publication_authors
| Column name | Description | Data type | Length | Is nullable |
|---|---|---|---|---|
id | unique identifier for this entry | bigint | 0 | NO |
run_id | identifies the agency run for which this entry was determined, foreign key to agency_run.id | bigint | 0 | NO |
publication_id | foreign key to the publication for this author | bigint | 0 | NO |
author_id | foreign key to the table with scopus author entries | bigint | 0 | NO |
author_position | position of author in the list of authors on the publication | int | 0 | YES |
last_updated_date | last time the row was updated; generally the time of creation of the row | datetime | 0 | NO |
|
publication_topic: the table identifying the topic assigned to a publication
https://prod.democratizing-data.tacc.utexas.edu/publication_topics
| Column name | Description | Data type | Length | Is nullable |
|---|---|---|---|---|
id | unique identifier for this entry | bigint | 0 | NO |
run_id | identifies the agency run for which this entry was determined, foreign key to agency_run.id | bigint | 0 | NO |
publication_id | foreign key to the topic table's id column identifying the publication in this relation between publications and topics | bigint | 0 | NO |
topic_id | foreign key to the topic table's id column identifying the topic in this relation between publications and topics | bigint | 0 | NO |
score | TBD | real | 0 | YES |
last_updated_date | last time the row was updated; generally the time of creation of the row | datetime | 0 | NO |
|
publisher: the table with a list of publishers that can be linked to journals
https://prod.democratizing-data.tacc.utexas.edu/publishers
| Column name | Description | Data type | Length | Is nullable |
|---|---|---|---|---|
id | unique identifier for this entry | bigint | 0 | NO |
run_id | identifies the agency run for which this entry was determined, foreign key to agency_run.id | bigint | 0 | NO |
external_id | external identifier of this publisher in Elsevier's scopus repository | nvarchar | 128 | YES |
name | name of the publisher | nvarchar | 120 | YES |
last_updated_date | last time the row was updated; generally the time of creation of the row | datetime | 0 | NO |
|
topic: the table with Topics defined by Elsevier, consistinging of topic names with three concatenated keywords
https://prod.democratizing-data.tacc.utexas.edu/topics Topics are defined through Elsevier's Topic Prominence in Science methodology. More information about these Topics can be found here: https://www.elsevier.com/solutions/scival/features/topic-prominence-in-science (To filter topic by other parameters, use https://prod.democratizing-data.tacc.utexas.edu/authors/{author_id}/topics, https://prod.democratizing-data.tacc.utexas.edu/datasets/{parent_alias_id}/topics, https://prod.democratizing-data.tacc.utexas.edu/publications/{publication_id}/topics)
| Column name | Description | Data type | Length | Is nullable |
|---|---|---|---|---|
id | unique identifier for this entry | bigint | 0 | NO |
run_id | identifies the agency run for which this entry was determined, foreign key to agency_run.id | bigint | 0 | NO |
keywords | a topic is defined in Elsevier by three keywords; keywords are separated with a pipe symbol | varchar | 1028 | YES |
external_topic_id | external identifier of this topic provided by Elsevier | varchar | 128 | YES |
prominence | TBD | real | 0 | YES |
last_updated_date | last time the row was updated; generally the time of creation of the row | datetime | 0 | NO |
|
publication_ufc: the table with unified fingerprint concepts assigned to a publication
| Column name | Description | Data type | Length | Is nullable |
|---|---|---|---|---|
id | unique identifier for this entry | bigint | 0 | NO |
run_id | identifies the agency run for which this entry was determined, foreign key to agency_run.id | bigint | 0 | NO |
publication_id | foreign key to the publication table's id column identifying the publication in this relation between publications and UFC fingerprints | bigint | 0 | NO |
concept_id | TBD | bigint | 0 | YES |
concept_name | TBD | varchar | 1024 | NO |
rank | TBD | float | 0 | YES |
a_freq | TBD | integer | 0 | YES |
last_updated_date | last time the row was updated; generally the time of creation of the row | datetime | 0 | NO |
|
The following metadata tables apply only to the data that can be accessed in SciServer restricted use databases.
reviewer: the table with reviewers assigned to validate dyads in the publication_dataset_alias table
| Column name | Description | Data type | Length | Is nullable |
|---|---|---|---|---|
id | unique identifier for this entry | bigint | 0 | NO |
susd_user_id | foreign key to the susd_user table's id column, identifying the user corresponding to this reviewer | bigint | 0 | NO |
run_id | identifies the agency run for which this entry was determined, foreign key to agency_run.id | bigint | 0 | NO |
last_updated_date | last time the row was updated; generally the time of creation of the row | datetime | 0 | NO |
|
snippet_validation: the table containing validation results for dyads provided by reviewers
| Column name | Description | Data type | Length | Is nullable |
|---|---|---|---|---|
id | unique identifier for this entry | bigint | 0 | NO |
run_id | identifies the agency run for which this entry was determined, foreign key to agency_run.id | bigint | 0 | NO |
reviewer_id | foreign key to the reviewer table's id column, identifying the reviewer assigned to validate this snippet | bigint | 0 | NO |
dyad_id | foreign key to the dyad table's id column, identifying the dyad that is being validated | bigint | 0 | NO |
is_dataset_reference | if the value in this column is 1, it indicates the dyad indeed has been identified a reference to a dataset; if 0, it is not a dataset reference; if -1, the reviewer was unsure about it | smallint | 0 | YES |
agency_dataset_identified | if the value in this column is 1, it indicates the dyad indeed identified the specific dataset provided by the agency; if 0, it is not a reference to that dataset; if -1, the reviewer was unsure about it | smallint | 0 | YES |
notes | any notes the reviewer attached to the dyad being reviewed | nvarchar | -1 | YES |
last_updated_date | last time the row was updated; generally the time of creation of the row | datetime | 0 | NO |
|
susd_user: the table identifying users of the validation tool
| Column name | Description | Data type | Length | Is nullable |
|---|---|---|---|---|
id | unique identifier of this table | bigint | 0 | NO |
first_name | first name of the individual | varchar | 100 | YES |
last_name | surname of the individual | varchar | 100 | YES |
email of the user | varchar | 100 | YES | |
password | encrypted password of the user | varchar | 100 | YES |
last_updated_date | last time the row was updated; generally the time of creation of the row | datetime | 0 | NO |
Last updated