ILO-4. Database implementation and loading data

Learning Objectives

1. Store, retrieve, and use data from important data models used in Hydrology such as the CUAHSI Observations Data Model (ODM)
2. Develop data models to represent, organize, and store data
3. Design and use relational databases to organize, store, and manipulate data

Computer and Data Requirements

1. Data required for this exercise are available in a zip file here Download here.
2. Although not specifically used in the attached instructions, you may want to use SQL Server Management Studio to examine the contents of your ODM database.  Microsoft SQL Server 2008 R2 Management Studio Express can be downloaded from Microsoft for free at http://www.microsoft.com/en-us/download/details.aspx?id=30438 Links to an external site. (download the Management Studio for your version of Windows – i.e., 32 or 64 bit).  A full version of Microsoft SQL Server is available in the open access computer lab on the 3^rd floor of the new engineering building at USU.  Installation media are also available from the instructors.
3. The following software applications for Windows can be downloaded for free from http://hydroserver.codeplex.com/releases/view/83377 Links to an external site.:  1) CUAHSI HIS ODM Data Loader; 2) CUAHSI HIS ODM Streaming Data Loader; 3) CUAHSI HIS ODM Tools Software.

The Problem

You are the data manager for the Little Bear River Experimental Watershed.  You need a way to organize and store all of the observational data that is streaming in from sensors that you have installed in the watershed as well as other data that you have retrieved from additional sources.  One of your colleagues has suggested using the Observations Data Model (ODM) developed by the CUAHSI Hydrologic Information Systems project.  Using the data and metadata provided for monitoring sites in the Little Bear River and the attached instructions, prepare and load the Site, Variable, Method, and Source information into the ODM database that has been set up for you on the class SQL Server using the ODM Data Loader.  Map the datalogger file for the Little Bear River monitoring site to the CUAHSI HIS ODM database schema using the ODM Streaming Data Loader.  Connect to your database using the ODM Tools software and explore it using the visualization functions to make sure that you have loaded your data correctly.  Additionally, to further test the generality of ODM beyond the Little Bear River Sensor data, find and load data and metadata for at least one additional site and variable into your ODM database (you may use the ODM Data Loaders or any other convenient tools).  Examples might be discharge data for a USGS gage in the watershed or weather variables from a nearby weather station.

In your evaluation of ODM, you may consider:

1. Does the ODM database organize and store the Little Bear River sensor data, its associated metadata, and that of the additional data that you loaded?
2. What deficiencies in ODM did you observe when you loaded the subset of data into the database (e.g., is there information about the sites, variables, methods, sources, etc. that is missing in ODM)?
3. What are the advantages of using a standard data model like ODM to store your data? 
4. What are the disadvantages of not using a standard data model for storing your data? 
5. Were you able to complete your evaluation with the available ODM software tools?  How could they be improved?

Deliverable

Provide a one-page briefing for your colleagues with your evaluation of ODM as a data management tool for sensor data from the Little Bear River experimental watershed.  In an appendix provide screen shots of the software and plots of the data that demonstrate the capabilities of ODM and the ODM software tools for your colleagues.

Instructions for completing this ILO are here Download here.
A PDF of this ILO for printing purposes is here Download here.
An editable Microsoft Word version of the grading rubric for this ILO is available here Download here.