3. Database implementation and loading data

  • Due Oct 3, 2013 by 1:30pm
  • Points 100
  • Submitting a file upload
  • Available after Sep 26, 2013 at 12am

Hydroinformatics

Assignment 3

Database Implementation and Loading Data

Due Date: October 3

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 assignment are available in a zip file on the course web site: Hydroinformatics_Assignment-3_Data.zip Download Hydroinformatics_Assignment-3_Data.zip
  2. You may use any database management system to complete this project.  However the detailed instructions are provided using free software tools from CUAHSI HIS that work with MS SQL Server.
  3. 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 3rd floor of the new engineering building at USU.  BYU and U of U students may not have access to the full version, but the Express version will work well for this project.
  4. 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. 

Database Implementation Option1 – ODM on MS SQL Server:

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. 

To 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.

Database Implementation Option 2 – Your Schema on a DBMS of Your Choice:

If you choose to implement your own database structure using a different database management system, you will need to organize the tables of your database, load your data into the database, and present a discussion of the results. We’re you able to successfully create table that store all your data? Were you able to create relationships to link your tables?

To test and explore the generality of YOUR database beyond the Little Bear River Sensor data, find and load data and metadata for at least one additional site and variable into your database.  Examples might be discharge data for a USGS gage in the watershed or weather variables from a nearby weather station.

ODM Analysis (Everyone)

Whether you implement your own database or use ODM, please explore the ODM design and provide a brief evaluation of ODM. In this assessment, 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 explaining the database system you used, the data you loaded and any challenges encountered. Provide a brief evaluation of ODM as a data management database for sensor data from the Little Bear River experimental watershed.  In an appendix provide screen shots of the software used to conduct this project. For example, you could provide plots of the data that demonstrate the capabilities of ODM and the ODM software tools for your colleagues. If you use another DBMS system, you can provide other visual representations of the data as loaded in your database.

1380828600 10/03/2013 01:30pm