DBdesign
pdf
keyboard_arrow_up
School
Wilfrid Laurier University *
*We aren’t endorsed by this school
Course
363
Subject
Information Systems
Date
Nov 24, 2024
Type
Pages
30
Uploaded by toastbeanss
Database design documentation
Data System Overview:
This overview section contains a list of each table and their relationships with each other
–
brief
and general. I
would like to have a graphic showing the links, similar to what Dave did in the
PowerPoint slides.
The STEWARDS data management system contains three components.
They are the CEAP
watershed measurement data, the ARS Methods Catalog, and the STEWARDS System Support
data files.
The CEAP watershed measurement data are located in a suite of data table pairs (Data table and
Data Definition table).
The tables are assembled and populated by watershed personnel.
Watershed data will be managed as a collection sites with a common theme as derived from
individual measurement devices, i.e., South Fork Meteorology - Temperature, rainfall,
atmospheric pressure, wind. Site measurement data will be managed by unique SiteID/DateTime
pairs, with measurements as additional column headings using generic column names mapped to
detailed information in the Definition table.
The tables include:
•
Data Tables
–
These tables contain the watershed’s measurement data and are described
by the Data Table Definition.
•
Data Table Definition Table
–
This table describes all the measurement data tables
provided by the watersheds.
The Definition table will maintain the relationship between
the data
producer’s
data-table specific column names and the ARS Methods Catalog.
The ARS Methods Catalog contains essential information to explain the characteristics of the
measurement data.
The tables provide users information to accurately understand the
measurements.
The table values are populated by watershed data producers assisted by a science
review committee.
The Methods Catalog is currently housed as a MS Access database and is
accompanied by a suite of tools that may be used to populate the Methods table, Analyte table,
and the Parameter table. These tables include:
•
Parameter Table
–
This is the key table for user querying data using a topic.
•
Methods Table
–
The table captures information describing general and detailed methods
the watershed used to obtain the measurement data.
The tables also include links or
pointers to other resources to more fully describe their methods used to capture the
measurement data.
•
Analytes Table
–
The table captures information describing the constituents whose value
is estimated using a method found in the Methods table.
There is a one-to-many
relationship between the Methods table and the Analytes table, as there may be more than
one analyte associated with a single method.
•
Revision Table
–
this table is used to capture any revision information that may be
associated with a method.
The STEWARDS System Support data are essential for the system to meet the requirements of
the data providers, system administrator, and end users.
These additional watershed-related
tables are used to support query and display functions in the user interface. Several of these
tables are populated by watershed personnel and maintained by the system administrator.
These
tables include:
•
Site Summary table - a system-generated, pre-set collection of information assembled
from watershed-provided data allowing for cross watershed queries.
•
Sites table
–
This table links the sites which the data are collected with the tables that
hold the measurement data.
•
SourceTables table
–
describes the individual measurement data tables (TableID ) with
table name, time step, theme, location identifier, date of the last update, and table author.
•
Locations table
–
This table provide information about the location (watershed) where the
measurement data are collected.
•
DataTable-to-Shapefile table
–
A table that links shapefiles with their associated data
tables.
In the case of multiple time-steps for a measurement theme, there may be more
than one data table associated with a single shapefile.
•
Data Download log
–
A list of downloaded data and the downloader information.
CEAP watershed measurement data tables
Data Table -
(multiple tables containing data)
–
These tables contain the watershed
measurement data.
(note:
when methods, units, and comments change over period of record,
this value must be specified in the data table.
When the value is consistent throughout period of
record, the value can be extracted from definition table.)
DataTable
COL1
COL2
COL3
COL4
COL5
COL6
COL7
COL8
COL9
COL10
ST101
1/1/1993
1
2
3
4
5
6
7
8
COL1: This column is reserved for the SiteID field.
Primary/Foreign Key for:
Data_Definition table, ARS_SiteSummary table, ARS_Sites
table.
Column Description
:
This is the sample site identifier for the location of where the data
are measured.
System use:
Interface
–
none.
System operations
–
the values are used to build ARS_Sites and ARS
SiteSummary tables.
Download -
part of data download.
Field Data Source:
Watershed data producer
Field Controls:
Can not be blank.
Format must be, StateWatershedSite_extension.
The State is the two character abbreviation.
Watershed is a two character abbreviation.
The Site_extension may not have spaces, can have 1-6 alpha-numeric characters, and is
the reference to the original value in the watershed.
For example:
OKLW1234ab.
(Note:
Must have a shape file record and html site description for each SiteID.)
COL2:
Primary/Foreign Key for:
Data_Definition table, ARS_SiteSummary table,
This
column is the date and time stamp.
Column Description
:
Date and time stamp.
System use:
Interface
–
none
System operations
–
used to build ARS SiteSummary table.
Download -
value is used to filter records for downloading.
Included in the
download file.
Field Data Source:
Watershed data producer
Field Controls:
Can not be blank.
The date time stamp for daily or other aggregated
values is clarified by the time step value in the source table.
Format is mm/dd/yyyy
hh:mm:ss AM/PM.
(Note:
A time of 12:00 AM, indicates a daily time step.)
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
COL3…COL(n):
Primary/Foreign Key for:
Data_Definition table element
Column Description
: data as defined by definition table
System use:
Interface
System operations
Download
–
included in download and display.
Field Data Source:
Watershed data producers
Field Controls:
None.
DataTable_Definition
–
This table is the key table for describing all the Data Tables obtained
from the watersheds.
The Data Table name is:
LocationID followed by an underscore, then a
unique name with the theme and time step, if needed, words distinguished by capital letters.
Eg,
IASF_PrecipDaily; IASF_PrecipHourly. The Data Table Definition name appends “_Defin
i
tion”
to the Data Table name.
Stewards DataTable_Defintion table
Field Name
Description
Field Type
Field
Size
ColumnID
Columns labels in the Data table
Text
10
TopicID
Local data producers field name for the original
data
Text
30
VAR_Type
Describes the type of data
Text
15
ParameterDescription
Parameter description
Text
255
LabMethodCode
Laboratory method code
Text
10
FieldMethodCode
Field method code
Text
10
Comments
A utility field for field-specific comments
Text
255
ColumnID:
Primary/Foreign Key for:
Relates to the parent Data table.
Column Description
:
The labeling for the columns in the Data table.
System use:
Interface/application
–
is the join field between the definitions and data table.
System operations
–
none
Download - none
Field Data Source:
The watershed will provide the information (Note:
this value may
be generated from a table generation tool that Teri is developing.)
Field Controls:
Can not be blank.
Must be sequential, COL1-COLn and match the
column in the data table.
TopicID:
Primary/Foreign Key for:
Local data producers name for the original data.
Column Description
:
Local data producers field name for the original data.
System use:
Value is not used.
Provides a link between the CEAP data table fields and
the data producers original data tables.
Interface/application
System operations
Download
Field Data Source:
Watershed data provider.
Field Controls:
(Note:
The user should put in a value relating to their original column
header, but this is not required.
This field can be blank.)
Var_Type:
Primary/Foreign Key for:
Data table
Column Description
:
Describes the type of data stored in the Data table.
System use:
Interface/application
–
none.
System operations
–
The value determines which columns are included in a data
download for a given query.
Download
–
none.
Field Data Source:
Currently, the watershed enters data value.
This value may be
selected from a list in the Data_Definition-table generation tool.
Field Controls:
Can not be blank.
Domain:
Fixed
–
Information to be included in every download.
For example, SiteID and
DateTime.
The watershed may include other “Fixed” columns.
Topic
–
Designates a parameter description.
(Note:
For a VAR_Type equal to
Topic, the ParameterDescription column value will be the column header for the
corresponding data download column.)
FldMethod
–
Indicates that the field methods changed over the period of record
and are defined in this column in the Data table. (Note: to link to a field method
column entry to a specific parameter description, enter the associated
Topic>ParameterDescription value in the field method >ParameterDescription
column.)
LabMethod
–
Indicates that the laboratory methods changed over the period of
record and are defined in this column in the Data table. (Note: to link to a lab
method column entry to a specific parameter description, enter the associated
Topic>ParameterDescription value in the lab method >ParameterDescription
column.)
Comments
–
Indicates a column containing row-specific Comments in the Data
table.
Data quality information and exceptions may be included in a Comments
column.
(Note: to link to a Comments column entry to a specific parameter
description, enter the associated Topic>ParameterDescription value in the
Comments >ParameterDescription column.)
Units
–
Indicates that the measurement units changed over the period of record
and are defined in this column in the Data table. (Note: to link to a units column
entry to a specific parameter description, enter the associated
Topic>ParameterDescription value in the units >ParameterDescription column.)
ParameterDescription:
Primary/Foreign Key for:
None
Column Description
:
Parameter description
System use:
Interface/application - Query selection values for
the “Site Specific Search”
(Note:
this value is used for a “
Parameter
Specific Search”)
System operations - none
Download - Column header for output
Field Data Source:
ARSParameterTable table.
Field Controls:
Can not be blank.
Column1
- ParameterDescription
must be “Site Identifier”.
(Note:
this value
cannot be changed without impacting software.)
Column2
- ParameterDescription
must be “Date/Time”.
(Note:
this value cannot
be changed without impacting software.)
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
LabMethodCode: (note:
When method changes over the period of record, this value is not used
by the data table, the data table value for LabMethodCode must be a specific column in the data
table.)
Primary/Foreign Key for:
PK
–
ARS_Methods table
Column Description
:
Laboratory method code, Agency-defined unique identifier
System use:
Interface/application - none
System operations - none
Download
–
locates the
lab method description
in the ARS_Methods table.
The
description
is included in download.
Field Data Source:
ARS_Methods table.
Field Controls:
Eg.
Must be a valid code in the ARS_Methods table.
(Note:
A “none”
value will be available ARS_Methods table.)
FieldMethodCode: (note:
when method changes over the period of record, this value is not used
by the data table.
The data table value for FieldMethodCode must be a specific column in the
data table.)
Primary/Foreign Key for:
PK - ARS_Methods table
Column Description
:
Field method code, Agency-defined unique identifier.
System use:
Interface/application - none
System operations - none
Download
–
locates the
field method description
in the ARS_Methods table.
The
description
is included in download.
Field Data Source:
ARS_Methods table.
Field Controls:
Eg.
Must be a valid code in the ARS_Methods
table.
(Note:
A “none”
value will be available ARS_Methods table.)
Comments:
Primary/Foreign Key for:
Column Description
:
A utility field for field-specific comments that apply to the field,
e.g., missing entry values
System use:
Interface/application - none
System operations - none
Download
–
The
Comments
field is included in a download.
Field Data Source:
Data producer
Field Controls:
None
ARS Methods Catalog
The ARS Methods Catalog is a database of the field and laboratory methods used to produce
scientific measurements that are made by ARS research staff in support of the Agency’s
programmatic research.
The data contained in these tables allow for a complete description of
the constituent measured, the field methods used to collect the samples, and the laboratory
methods used to process the samples in order to derive the measurement value.
There are three primary tables in this database; the Methods table (ARS_Methods), the Analytes
table (ARS_Analytes), and the Parameter table (ARS_ParameterTable). The ARS Methods
Catalog database structure is based on the National Environmental Methods Index (NEMI)
database.
The intent for this database is to enable ease of entry of field and laboratory methods
for ARS measurement data and to offer the opportunity to develop consistent descriptors of ARS
methods. A more detailed description of the Methods Catalog and its components can be found
in the
Notes on the ARS Methods Catalog
document.
Parameter Table
–
The Parameter Description is a descriptive term for the watershed
measurements and is the primary search field for the STEWARDS interface.
This table carries
the complete list of parameter descriptions.
Parameter Description format:
Parameter Name, [
Parameter Name Modifier
], Medium, [
Temporal Resolution
], [
Summary
Statistic
], [
Ad Hoc Modifier
], Units
Parameter Description example:
Temperature, air,
daily, maximum
, degrees Centigrade
ARS_ParameterTable
Field Name
Description
Field Type
Field Size
ParameterDescription
The full parameter description
Text
255
ParameterDescription:
Primary/Foreign Key for:
Data_Definition, ARS_SiteSummary tables
Column Description
:
Parameter Name, [Parameter Name Modifier], Medium,
[Temporal Resolution], [Summary Statistic], [Ad Hoc Modifier], Units
System use:
Interface
–
none
System operations
–
used by watershed data providers as a selection for updating
the Data_Definition table.
Download - none
Field Data Source:
Watershed data providers, however all values must be valid in
corresponding tables.
This may be created using parameter data base tool found in the
ARS_MethodsCatalog Access database.
(Note: the watershed data provider can enter
any values into this table and will be used by the system.)
Field Controls:
Can not be blank.
Must be unique.
Method
s
table
–
this table houses the ARS methods database. The format is based upon the
National Environmental methods Index (NEMI) data structure (see www.NEMI.gov). The values
in this table provide descriptive information regarding the methods used for estimating the
measurement values found in the Stewards database. The MethodID is used as a link between the
Data_Table_Definition file and this database. This information is provided to the user at
download.
ARS_Methods Table
Field Name
Description
Field Type
Field Size
Method identifier
Method Number
Text (Required)
30
Method descriptive name
Short Title
Text (Required)
150
Method type
Field, Laboratory
Text
25
Method subcategory
E.g., Organic, Bio
Text
50
Method source
Method Publisher
Text
50
Source citation
Publication cite
Text
255
Brief method summary
Method Summary
Memo (Required)
4000
Media
E.g., Air, Water
List (Required)
20
Instrument
Method Instrumentation
List (Required)
128
Official method name
Official Title
Text
255
Scope and Application
Method Applicability
Memo
4000
Detection limit type
Type of Detection Limit (e.g., MDL,
ML, LOQ)
List
11
DL note
Description of how DLs were
determined
Memo
4000
Applicable concentration range
Range of Method
Text
255
Concentration range units
Units for Range
List
20
Interferences
Method Interferences
Memo
4000
Precision Descriptor Notes
Description of how precision and
accuracy data were determined.
Memo
4000
QA requirements
QA/QC Requirements
Memo
4000
Sample handling
Sample Handling and Preservation
Req.
Memo
4000
Max holding time
Maximum Holding Time for Samples
Text
255
Sample prep methods
Separate Prep Methods Cited by the
Method
Text
40
Relative cost
Costs
List
11
Link to full method
Hyperlink to Method
Text
240
Insert Person Name
Who Entered Info.
Text
50
MethodID: Method number / identifier
Primary/Foreign Key for:
Data_Table_Definition file
Column Description
:
Unique identifier.
System use:
Interface
System operations
Download - included in download
Field Data Source:
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Field Controls:
Can not be blank.
MethodName: Method descriptive name
Primary/Foreign Key for:
none
Column Description
:
Short title
System use:
Interface
System operations
Download - included in download
Field Data Source:
Field Controls:
Can not be blank.
MethodType:
Primary/Foreign Key for:
none
Column Description
:
Method type; field, lab; ARS Extension
System use:
Interface
System operations
Download - included in download
Field Data Source:
Domain List
Field Controls:
Can not be blank.
MethodSubcategory :
Primary/Foreign Key for:
none
Column Description
:
e.g., Organic, Bio; Theme
System use:
Interface
System operations
Download - included in download
Field Data Source:
Domain List
Field Controls:
MethodSource:
Primary/Foreign Key for:
none
Column Description
:
Method Publisher
System use:
Interface
System operations
Download - included in download
Field Data Source:
Field Controls:
SourceCitation:
Primary/Foreign Key for:
none
Column Description
:
Publication cite
System use:
Interface
System operations
Download - included in download
Field Data Source:
Field Controls:
BriefMethodSummary:
Primary/Foreign Key for:
none
Column Description
:
Method summary
System use:
Interface
System operations
Download - included in download
Field Data Source:
Field Controls:
Can not be blank
Media:
Primary/Foreign Key for:
none
Column Description
:
Sample media, e.g., Air, Water, Soil
System use:
Interface
System operations
Download - included in download
Field Data Source:
Domain List
Field Controls:
Can not be blank
Instrument:
Primary/Foreign Key for:
none
Column Description
:
Method Instrumentation
System use:
Interface
System operations
Download - included in download
Field Data Source:
Domain List
Field Controls:
Can not be blank
OfficialMethodName:
Primary/Foreign Key for:
none
Column Description
:
Official method name; Official title
System use:
Interface
System operations
Download - included in download
Field Data Source:
Field Controls:
Can not be blank
ScopeApplication:
Primary/Foreign Key for:
none
Column Description
:
Scope and Application; Method Applicability
System use:
Interface
System operations
Download - included in download
Field Data Source:
Field Controls:
DetectionLimitType :
Primary/Foreign Key for:
none
Column Description
:
Type of Detection Limit (e.g., MDL, ML, LOQ)
System use:
Interface
System operations
Download - included in download
Field Data Source:
Domain List
Field Controls:
DLNote:
Detection Limit Note
Primary/Foreign Key for:
none
Column Description
:
Description of how DLs were determined
System use:
Interface
System operations
Download - included in download
Field Data Source:
Field Controls:
AppConcentrationRng:
Primary/Foreign Key for:
none
Column Description
:
Applicable concentration range; Range of Method
System use:
Interface
System operations
Download - included in download
Field Data Source:
Field Controls:
ConcentrationRngUnits: Concentration range units
Primary/Foreign Key for:
none
Column Description
:
Concentration range units; Units for Range
System use:
Interface
System operations
Download - included in download
Field Data Source:
Domain List
Field Controls:
Interferences:
Primary/Foreign Key for:
none
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Column Description
:
Method Interferences; something that interferes with the method
System use:
Interface
System operations
Download - included in download
Field Data Source:
Field Controls:
PrecisionDescriptorNotes:
Primary/Foreign Key for:
none
Column Description
:
Description of how precision and accuracy data were determined
System use:
Interface
System operations
Download - included in download
Field Data Source:
Field Controls:
QARequirements:
Primary/Foreign Key for:
none
Column Description
:
QA/QC Requirements
System use:
Interface
System operations
Download - included in download
Field Data Source:
Field Controls:
SampleHandling:
Primary/Foreign Key for:
none
Column Description
:
Sample Handling and Preservation Requirements
System use:
Interface
System operations
Download - included in download
Field Data Source:
Field Controls:
MaxHoldingTime:
Primary/Foreign Key for:
none
Column Description
:
Maximum Holding Time for Samples
System use:
Interface
System operations
Download - included in download
Field Data Source:
Field Controls:
SamplePrepMethods:
Primary/Foreign Key for:
none
Column Description
:
Separate Preparation Methods Cited by the Method
System use:
Interface
System operations
Download - included in download
Field Data Source:
Field Controls:
RelativeCost:
Primary/Foreign Key for:
none
Column Description
:
estimated costs
System use:
Interface
System operations
Download - included in download
Field Data Source:
Field Controls:
LinkToFullMethod:
Primary/Foreign Key for:
none
Column Description
:
Hyperlink/URL to full method description
System use:
Interface
System operations
Download - included in download
Field Data Source:
Field Controls:
InsertPersonName
Primary/Foreign Key for:
none
Column Description
:
Who entered method information
System use:
Interface
System operations
Download - included in download
Field Data Source:
Field Controls:
Analytes table
–
The table captures information describing the constituents whose value is
estimated using a method found in the ARS_Methods table. The format is based upon the
National Environmental methods Index (NEMI) data structure (see www.NEMI.gov). The values
in this table provide descriptive information regarding the constituents used for estimating the
measurement values found in the Stewards database. The MethodID is used as a link between the
ARS_Methods table and this database. This information is provided to the user at download.
ARS_Analytes Table
Field Name
Description
Field Type
Field Size
Analyte
Analyte Name
Text
50
Detection level value
The Detection Limit (DL)
Number
double
Detection level units
Units of the DL
List (Required)
50
Endpoint units
EP Units (Tox only)
50
Accuracy
Accuracy Value
Number
double
Accuracy units
Units of Accuracy Value
List
50
Precision
Precision Value
Number
double
Precision units
Units of Precision Value
List
50
False Positive value
False Positive Rate
Number
50
False Negative value
False Negative Rate
Number
50
Prec Acc Conc used
Analyte concentration at which
Precision/Accuracy were determined
(Spiking level)
Number
50
Insert person name
Who Entered Information
Text
50
MethodID
Method identifier
Text
30
MethodID: Analyte
Primary/Foreign Key for:
Column Description
:
Analyte name. The constituent measured.
System use:
Interface
System operations
Download - included in download
Field Data Source:
Field Controls:
Can not be blank.
DetectionLevelValue:
Primary/Foreign Key for:
none
Column Description
:
Detection level value
System use:
Interface
System operations
Download - included in download
Field Data Source:
Field Controls:
Can not be blank.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
DetectionLevelUnits:
Primary/Foreign Key for:
none
Column Description
:
Detection level units
System use:
Interface
System operations
Download - included in download
Field Data Source:
domain list
Field Controls:
Can not be blank.
EndpointUnits:
Primary/Foreign Key for:
none
Column Description
:
Endpoint units, toxicity only.
System use:
Interface
System operations
Download - included in download
Field Data Source:
Field Controls:
Accuracy:
Primary/Foreign Key for:
none
Column Description
:
Accuracy value
System use:
Interface
System operations
Download - included in download
Field Data Source:
Field Controls:
AccuracyUnits:
Primary/Foreign Key for:
none
Column Description
:
Accuracy value units
System use:
Interface
System operations
Download - included in download
Field Data Source:
Domain list
Field Controls:
Precision:
Primary/Foreign Key for:
none
Column Description
:
Precision value
System use:
Interface
System operations
Download - included in download
Field Data Source:
Field Controls:
PrecisionUnits:
Primary/Foreign Key for:
none
Column Description
:
Precision units
System use:
Interface
System operations
Download - included in download
Field Data Source:
Domain list
Field Controls:
FalsePositiveValue:
Primary/Foreign Key for:
none
Column Description
:
False Positive Rate.
System use:
Interface
System operations
Download - included in download
Field Data Source:
Field Controls:
FalseNegativeValue:
Primary/Foreign Key for:
none
Column Description
:
False Negative Rate
System use:
Interface
System operations
Download - included in download
Field Data Source:
Field Controls:
PrecAccConcUsed: Precision/Accuracy concentration used
Primary/Foreign Key for:
none
Column Description
:
Analyte concentration at which Precision/Accuracy were
determined.
System use:
Interface
System operations
Download - included in download
Field Data Source:
Field Controls:
InsertPersonName:
Primary/Foreign Key for:
Column Description
:
The person who entered information.
System use:
Interface
System operations
Download - included in download
Field Data Source:
Field Controls:
Can not be blank.
MethodID: Method number / identifier
Primary/Foreign Key for:
FK-ARS ARS_Methods
Column Description
:
Unique identifier.
System use:
Interface
System operations
Download - included in download
Field Data Source:
Field Controls:
Can not be blank.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Method Revisions Table
–
this table is used to capture any revision information that may be
associated with a method. This table is not currently in use and is included here in order to reflect
the full NEMI database structure.
ARS_MethodRevisions Table
Field Name
Description
Field Type
Field Size
Method Id
Method ID
List (Required)
Revision Information
The Revision Number and Date
Text/Date
Insert Date
Date Information is Entered NEMI
Date
Insert Person Name
Who Entered Info.
Text
50
MethodID: Method number / identifier
Primary/Foreign Key for:
FK-ARS_Methods Table
Column Description
:
Unique identifier.
System use:
Interface
System operations
Download
Field Data Source:
Field Controls:
Can not be blank.
RevisionInformation:
Primary/Foreign Key for:
Column Description
:
The Revision Number and date.
System use:
Interface
System operations
Download
Field Data Source:
Field Controls:
Can not be blank.
InsertDate:
Primary/Foreign Key for:
Column Description
:
System use:
Interface
System operations
Download
Field Data Source:
Field Controls:
Can not be blank.
InsertPersonName:
Primary/Foreign Key for:
FK-ARS Parameter Table
Column Description
:
Person who entered information.
System use:
Interface
System operations
Download
Field Data Source:
Field Controls:
Can not be blank.
STEWARDS System Support Databases
Sites
Table
–
This table describes the sites which the data are collected.
ARS_Sites Table
Field Name
Description
Field Type
Field Size
SiteID
The site identifier
Text
255
SiteName
The site name
Text
255
Table1
The 1st table associated with the site
Text
255
Table2
The 2nd table associated with the site
Text
255
TableN
The Nth table associated with the site
Text
255
SiteID:
Primary/Foreign Key for:
FK-DataTable(s) and ARS_SiteSummary table
Column Description
:
This is a unique site identifier for the location of where data are
measured.
System use:
Interface
–
source of “Site Specific Search” sites.
System operations - none
Download
–
none
Field Data Source:
Derived from Data tables
Field Controls:
Will not be blank.
SiteName:
(not used.
The value is the same as the siteID).
Don’t think there is a requirement for
this field.)
Primary/Foreign Key for:
Column Description
:
System use:
Interface/application
System operations
Download
Field Data Source:
Field Controls:
Can not be blank.
Table(1-n): (Note:
also referred to as TableID)
Primary/Foreign Key for:
PK: Data tables
Column Description
:
The Data table for the specific SiteID
System use:
Interface/application
–
list for “Site Specific Search”
System operations
–
none
Download - none
Field Data Source:
Derived from Data tables
Field Controls:
Must have one Table value.
Other column values can be, and will often
be, blank.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
SourceTables Table
–
ARS_SourceTables
Field Name
Description
Field Type
Field Size
TableID
The table identifier
Text
255
TableName
The table name
Text
255
TimeStep
time interval between samples
Text
255
Themes
Not used
Text
255
LocationID
Location (watershed) identifier
Text
255
LastUpdated
Dated of the last database update
Date
DataType
Type of data - not currently in use
Text
255
TableAuthor
Identifies person responsible for the data
Text
255
TableID: (Note:
also referred to as Table(1-n)
Primary/Foreign Key for:
FK: ARS_Sites, ARS_Locations table.
Column Description
:
Contains TableID of data table
System use:
Interface/application - none
System operations
–
translates between descriptive table name and tableID for site
and topic specific searches.
Download - none
Field Data Source:
Watershed data provider.
.
Field Controls:
The format is StateWatershed_ThemeTimestep.
The State is the 2
character abbreviation.
Watershed is the 2 character abbreviation.
Theme is one from a
yet determined list of values.
Timestep is daily, intermittent, breakpoint.
Other values
could be included, such as hourly.
Can not be blank.
TableName:
Primary/Foreign Key for:
None.
Column Description
:
The long user friendly name for the table ID
System use:
Interface
–
Referenced for the table name in the site and topic specific search.
System operations
–
table name source for ARS_SiteSummary table
Download
–
included in download.
Field Data Source:
Watershed data provider.
Field Controls:
Can not be blank.
Full theme name from a selection from a not yet
determined set of themes.
TimeStep:
Primary/Foreign Key for:
Site summary.
Column Description
:
Time description period of data measurements.
System use:
Interface
–
list of time steps in the “Summary Search”
System operations
–
for building the ARS_SiteSummary table.
Download
–
part of download table.
Field Data Source:
Watershed data provider.
Field Controls:
Can not be blank.
Domain (hourly, daily, monthly, yearly, intermittent)
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Theme: (not currently used by system.
If we do need, we may need another table to capture
valid values.)
Primary/Foreign Key for:
ARS_SiteSummary table
Column Description
:
Describes the type of research being measured.
System use:
Interface -
System operations -
Download -
Field Data Source:
Watershed data provider from predetermined values.
Field Controls:
Valid value in a currently undefined ThemeTable; Hydrology,
Meteorology, Sediment, Water Quality, Soil Chemistry.
LocationID:
Primary/Foreign Key for:
ARS_Locations table.
Column Description
:
Ties the Data table to the location.
The LocationID is defined by
the ARS_Locations table.
System use:
Interface/application - none
System operations
–
Determines the HTML file to use for Watershed descriptions.
Based on the TableID of this table, it links with the ARS_Sites table.
Download
–
none
Field Data Source:
Watershed data provider based upon format not yet determined.
StateWatershedSite.
The State is the two character abbreviation.
Watershed is a two
character abbreviation.
For example:
OKLW.
OK for Oklahoma, LW for Little
Washita.
(Note:
this abbreviation should be the same as the SiteID abbreviation less the
extension.)
Field Controls:
Can not be blank.
Must be valid value and in proper format.
LastUpdated:
Primary/Foreign Key for:
ARS_SiteSummary table.
Column Description
:
When the data table was last updated/modified.
System use:
Interface - none
System operations
–
ARS_SiteSummary table is updated from this value.
Download
–
part of download file.
Field Data Source:
System administrator.
Field Controls:
Can not be blank.
Format is mm/dd/yyyy hh:mm:ss (note:
the time is
not necessary)
DataType: (this is a place holder for future data types such as model data.
No current use for this
field).
Primary/Foreign Key for:
No.
Column Description
:
To distinguish between measurement, model, and other sources
of data.
System use:
Interface/application - none
System operations - none
Download - none
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Field Data Source:
watershed data provider (when applied)
Field Controls:
to be determined.
TableAuthor:
Primary/Foreign Key for:
No.
Column Description
:
Identifies the person responsible for this mess
System use:
Interface/application - none
System operations - none
Download
–
included in download
Field Data Source: watershed data provider
Field Controls:
Can not be blank.
Format:
Full name, location internet link.
For
example:
Mr. Greg Wilson, www.ars.usda.gov/ocio/web
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Site Summary Table
–
This table is an automated pre-set collection of information derived from
watershed-provided data allowing for cross-watershed queries.
ARS_SiteSummary Table
Field Name
Description
Field Type
Field Size
LocationName
Location (watershed) name
Text
255
SiteID
The site identifier
Text
255
TableName
The table name
Text
255
LastUpdated
Dated of the last database update
Date
TimeStep
time interval between samples
Text
255
BeginDatre
Beginning date of the Period of Record
Date
EndDate
Ending date of the Period of Record
Date
Duration
Length of Period of Record in years
Number
Integer
Theme
Table theme
Text
255
ParameterDescription
The measurement parameter
Text
255
LabMethodCode
Laboratory methods code
Text
255
FieldMethodCode
Field
methods code
Text
255
LocationName:
Primary/Foreign Key for:
ARS_Locations
Column Description
:
Full name of the watershed location
System use:
Interface/application - none
System operations
–
output of site summary search.
Download - none
Field Data Source:
ARS_Locations table
Field Controls:
Will not be blank.
SiteID:
Primary/Foreign Key for:
Data table, ARS_Sites
Column Description
:
Value of the watershed SiteID
System use:
Interface - none
System operations - output of site summary search
Download - none
Field Data Source:
Data table
Field Controls:
Can not be blank.
TableName:
Primary/Foreign Key for:
ARS_SourceTables
Column Description
:
full table name
System use:
Interface
–
(Is the value from this table used in the Interface?) - none
System operations - output of site summary search
Download - none
Field Data Source:
ARS_SourceTables table
Field Controls:
Can not be blank.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
LastUpdated:
Primary/Foreign Key for:
ARS_SourceTables
Column Description
When the data table was last updated/modified.
System use:
Interface - none
System operations
–
output of site summary search
Download
–
none
Field Data Source:
ARS_SourceTables table
Field Controls:
Can not be blank.
Format is mm/dd/yyyy hh:mm:ss (note:
the time is
not necessary)
TimeStep:
Primary/Foreign Key for:
ARS_SourceTables
Column Description
:
Time step for measurement data
System use:
Interface/application - none
System operations - output of site summary search
Download - none
Field Data Source:
ARS_SourceTables table
Field Controls:
Can not be blank.
BeginDate:
Primary/Foreign Key for:
Data table
Column Description
:
earliest date of measurement data
System use:
Interface/application
–
used for topic and site specific searches
System operations output of site summary search
Download - none
Field Data Source:
Derived from the earliest date in Column2 of the Data table
Field Controls:
Can not be blank.
EndDate:
Primary/Foreign Key for:
Data table
Column Description
:
latest date of measurement data
System use:
Interface/application
–
used for topic and site specific searches
System operations - output of site summary search
Download - none
Field Data Source:
Derived from the latest date in Column2 of the Data table
Field Controls:
Can not be blank.
Duration:
Primary/Foreign Key for:
none
Column Description
:
Duration of measurement data
System use:
Interface/application - none
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
System operations - output of site summary search
Download - none
Field Data Source:
Calculated from ARS_SiteSummary table fields BeginDate and
EndDate.
Field Controls:
Can not be blank.
If less than one year, the value is zero.
Theme: (currently not being used.)
Primary/Foreign Key for:
ARS_SourceTables table
Column Description
:
Theme of the measurement data
System use:
Interface/application - none
System operations - output of site summary search
Download - none
Field Data Source:
ARS_SourceTables table
Field Controls:
Should not be blank.
ParameterDescription:
Primary/Foreign Key for:
Data_Definition, ARS_Parameter_Codes tables
Column Description
:
Description of the parameter
System use:
Interface/application
–
site summary search and topic specific search drop down .
System operations - output of site summary search
Download - none
Field Data Source:
Data_Definition table
Field Controls:
Must be value in the ARS_ParameterTable
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Locations Table
ARS_Locations table
Field Name
Description
Field Type
Field Size
LocationID
The location identifier
Text
255
LocationName
The location name
Text
255
County
County the location is located in
Text
255
State
State the location is located in
Text
255
LocationType
Type of location
Text
255
WatershedPOC
Watershed point of contact
Text
255
ServiceName
ArcGIS Service name
Text
255
LocationID:
Primary/Foreign Key for:
FK: Source table.
Column Description
:
The code used as a reference for geospatial information.
System use:
Interface/application -
System operations
–
to identify the HTML file, an implicit link to the shape files.
Searches shape files looking for the locationID.
Download
–
may be part of download.
Field Data Source:
Watershed data provider based upon format not yet determined.
StateWatershedSite.
The State is the two character abbreviation.
Watershed is a two
character abbreviation.
For example:
OKLW.
OK for Oklahoma, LW for Little
Washita.
(Note:
this abbreviation should be the same as the SiteID abbreviation less the
extension.)
This value should be the same as the LocationID in the ARS_SourceTable.
Field Controls:
Can not be blank.
No duplications.
LocationName: (Note: LocationName is in the shape file.)
Primary/Foreign Key for:
ARS_SiteSummary table
Column Description
:
Long name for identifying watershed location.
System use:
Interface
–
This is the drop down value in selecting watershed.
System operations - none
Download
–
included in download.
Field Data Source:
Watershed data provider .
Field Controls:
Can not be blank.
County: (Note:
Same information is in shape file)
Primary/Foreign Key for:
none
Description of data belonging in column
System use:
Interface - none
System operations - none
Download
–
included in download
Field Data Source:
watershed data provider
Field Controls:
Can not be blank.
Can have multiple county entries
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
State: (Note:
Same information is in shape file.)
Primary/Foreign Key for: none
Description of data belonging in column
System use:
Interface/application - none
System operations - none
Download
–
included in download
Field Data Source:
watershed data provider
Field Controls:
Can not be blank.
Can have multiple state entries.
Can use full state
name.
LocationType: (Note:
note currently being used.
A place holder for future application for
specifying type of land uses.)
Primary/Foreign Key for:
No.
Column Description
:
Describes the type of location.
There would be a domain of
types.
The table with the domain of types has not been determined.
System use:
Interface/application - none
System operations - none
Download - none
Field Data Source:
watershed data provider
Field Controls:
Can not be blank.
Domain (
Watershed, plot, ranch, field,…
)
WatershedPOC:
Primary/Foreign Key for:
No.
Column Description
:
Identifies a local watershed point of contact
System use:
Interface/application
–
selection option when a watershed has been selected.
System operations - none
Download
–
included in download
Field Data Source:
watershed data provider
Field Controls:
Can not be blank.
Format:
url for the location.
ServiceName:
Primary/Foreign Key for:
No.
Column Description
:
Identifies the web service used for the watershed location
System use:
Interface/application
–
implicitly in the interface.
System operations - none
Download
–
none
Field Data Source:
system administrator
Field Controls:
Can not be blank.
Must be valid web service.
STEWARDS will not
run with an invalid value.
DataTable-to-Shapefile table
–
A table that links shapefiles with their associated data tables.
In
the case of multiple time-steps for a measurement theme, there may be more than one data table
associated with a single shapefile.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
ARS_TabletoShape table
Field Name
Description
Field Type
Field Size
DataTable
Data table name
Text
50
Shapefile
Shapefile name
Text
50
DataTable:
Primary/Foreign Key for:
FK- for system
Column Description
:
Data table name
System use:
Interface/application
–
none
System operations - none
Download
–
none
Field Data Source:
watershed data provider
Field Controls:
Can not be blank.
Shapefile:
Primary/Foreign Key for:
FK- for system
Column Description
:
Shapefile
System use:
Interface/application - none
System operations - none
Download
–
none
Field Data Source:
watershed data provider
Field Controls:
Can not be blank.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help