Transcript Relational databases as a tool to manage environmental

Relational databases as a tool to
manage environmental data at the
research plot scale
Tyler Erickson
Institute of Arctic and Alpine Research
University of Colorado
Deborah Donahue
Data Manager, Snow Hydrology Research Group
Donald Bren School of Environmental Science and Management
University of California
Q: Why Relational Databases?
A: To Manage Large Data Sets
Storage Choices
• Field notes
– Good for data
collection
– Difficult to share
Storage Choices
• Field notes
• The bookshelf
Storage Choices
• Field notes
• The bookshelf
• The file cabinet
Storage Choices
•
•
•
•
Field notes
The bookshelf
The file cabinet
Etc.
Distributable Storage Choices
• Text data files
– Most flexible; easy to
set up & distribute
– Difficult for analysis
Distributable Storage Choices
• Text data files
• Spreadsheets
– Good for analysis
– Inflexible distribution
Distributable Storage Choices
• Text data files
• Spreadsheets
• Database
– Best for large highdimensional datasets;
flexible distribution
– Work intensive to set up
& maintain
Distributable Storage Choices
•
•
•
•
Text data files
Spreadsheets
Database
GIS
– Best for spatial data
– Uses a relational
database
It’s 4pm and you are somewhere near
Granby, Colorado…
Do you know where your data are?
1
2
3
4
5
6
7
8
Data in Need of a Database
• Data Characteristics
– Large volume
– Constant format
– Need for sharing
Designing a Database
Data Modeling
1. Identifying related entities
•
Sample locations, spatial extent, sampling
methods, recorded data, data quality,
topography…
2. Determining relationships between entities
Relationships
CARDINALITY: Defines the numeric relationships
between occurrences of the entities
• One-to-One
– (one member to one home address)
• One-to-Many
– (one region/state/province to many members)
• Many-to-Many
– (many members to many papers)
Example: Keeping Track of the WSC
Example Table Data
(MMSA Example)
Database Information System
Data File
Database Information System
Relational
Database
Data File
Database Information System
Relational
Database
Data File
Database
Client Tools
Database Information System
Relational
Database
Database
Client Tools
Data File
Other Programs
Pre-Processing
Programs
Database Information System
Relational
Database
Database
Client Tools
Data File
Other Programs
Pre-Processing
Programs
Web Pages
Other Examples
• EPA
http://www.epa.gov/storet/
Other Examples
• EPA
• USGS Surface
Water
http://waterdata.usgs.gov/nwis/sw
Other Examples
• EPA
• USGS Surface
Water
• Snotel
http://www.wrcc.dri.edu/snotel.html
Summary
• Relational databases can be overkill for
small data sets.
• Relational databases are a good choice for
large data sets with a consistent format, that
need to be distributed to many users.
Acknowledgments
• NASA-EOS
• Mammoth Mountain
Energy Balance Monitoring Site
http://neige.bren.ucsb.edu/mmsa/