# Presentation: 517944

## PART 1Creating and Using Databases

- Clinton P. MacDonaldDianne S. Miller
- Sonoma Technology, Inc.

## Creating and Using Databases

- We will cover
- Using databases to create one data table ready for tool development
- how to get individual data sets into the database
- how to merge tables
- Mining the data in the database
- Setting up a simple interface in MS Access to run your tool

- Lots of material!  This part covers the minimum that you’ll need to get started

## Outline

- Overview
- About Databases
- Components of a Database
- Populating a Database
- Importing
- Keying
- Checking
- Using a Database
- Queries
- SYSTAT
- Export
- Forecast tools

## About Databases – Why use a database?

- Stores large amount of data (much more than Excel)
- Easy to merge different data sets
- Easy to query data
- Scalable
- Readable by other programs including statistical packages
- Can be set up as a forecast system that computes and stores forecasts
- Can be linked to e-mail systems for forecast dissemination

- _Number of records_
- Excel = 65,000
- Access = 2 million
- SQL = trillions (storage capacity is ~1 terabyte)
- Oracle = trillions (storage capacity is ~1 terabyte)

## About Databases

- Storage system that organizes data for quick retrieval
- If properly designed, data can be trusted more than when stored in other platforms
- Some common types
- MS Access, SQL, Oracle
- Structure types
- Flat (simple)
- Easy to develop and use
- Not easily scalable or space efficient
- Relational (complex)
- More difficult to develop
- Easily scalable
- Space efficient
- No repeated information
- We’ll focus on MS Access (v.2002) with simple structure

**Notes:**
Data storage system allows user to store, retrieve, and manipulate data.  Primary function is storing data in an organized manner.

Three types of databases; Access is the most user-friendly, but least powerful.  SQL and Oracle can hold much more data than Access, but knowledge of SQL code is needed to write queries to extract data subsets.

Two structure types; clicking the mouse will make examples appear and disappear.  Simple is easier to create from scratch, complex is expandable and better in the long-term.

## About Databases – Flat

- This is our goal – one data table with merged meteorological data ready to be used in a statistical program

## About Databases – Relational

## Components of a Database

- Tables – where the data are stored
- Queries – used to extract or manipulate data in tables
- Forms – used to simplify data entry and/or computations
- Reports – used to visualize  data, data summaries, and charts

## Components of a Database – Tables

**Notes:**
Smallest field type should be used; however, be sure to allow for future data that may need extra storage space.  For example, historical PM2.5 data may be integers, but real time may need decimal places.

Data will be truncated if assigned field type is too small.

## Components of a Database – Table Design

- Describes the attributes of the data
- Data types
- Text
- Numeric
- Date/time
- Etc.
- Primary keys
- Field(s) that are required to be unique (i.e., no duplicates)
- For this example, date and time should be unique
- Ensures integrity so that youcan trust your data

## Components of a Database – Queries

- Used to extract or manipulate data
- Take up much less space than tables

## Queries – Common Types

**Select**
**Ask questions about the data, make non-permanent calculations of the data**
**Group By function ****— ****Allows computation of averages, minimum, maximum, counts, etc. of the data for different field groupings (e.g., maximum hourly ozone value grouped by day)**
- Crosstab
- Summarize the data using Group By and display it in a grid format (e.g., maximum hourly ozone value by day by site)
- Update*
- Change data in the table to new values using calculations or assigning a set value for each record
- Append*
- Add new data to an existing table
- Delete*
- Delete existing data from tables based on given criteria
- Make-Table*
- Put Select query results into a new table in the database
- *THESE ACTION QUERIES CANNOT BE UNDONE!

## Queries – Select

- Returns all individual records with valid precipitation data
- Returns 8,646 rows out of 26,085 from the original SFC table

## Queries - Group By Select

- Returns one record per day with the daily sum of precipitation data
- Returns 1,097 records

## Queries – Crosstab Example

- Reports of
- measurable rain for each month by hour

## Components of a Database – Forms and Reports

- Tools for viewing and/or entering data outside the table view
- Both tools are linked to tables or queries

**Notes:**
Forms are used interactively, requiring user input or actions
Reports cannot be altered without altering the underlying data or queries.

## Populating a Database

- What needs to be done
- Some key issues
- Time stamps
- Missing data
- Duplicates
- How to resolve issues

## Populating – Step 1: Importing Data

- Use the Import Text Wizard
- Wizards assist in performing actions within the database that would otherwise be complex
- Be sure to install wizards
- Issues
- Formats
- Variable types
- Truncating data because of improper type assignment
- Assigning primary key(s)

**Notes:**
Screen shots show how to do it and what the wizard looks like for a text file.  

Many sources can be used to import data; .csv and .txt allows the most flexibility on import.  

Briefly explain variable types.  

Example of shows how default variable types may be wrong.  Access reads first record to assign default type. If it is missing, it will assign ‘text’; if it is an integer, it will truncate all data to integers.  Any numbers assigned as text will not be available to use later in calculations.  Data types for Excel or Access files cannot be altered on import.

Any data type can be changed later by going into the Design view of the table; however, data may be lost if too small of a type is selected.

If primary key is a single field (such as date), it can be assigned here.  Otherwise, set the primary key in design view.

## Importing Data – Example (1 of 4)

- Select *File*, *Get External Data, *and *Import*

## Importing Data – Example (2 of 4)

- Select data format

## Importing Data – Example (3 of 4)

- Assign data types
- Beware of truncating data with incorrect assignments from Access defaults
- Use smallest data type that will accommodate all data (see next page)

## Importing Data – Data Types (1 of 2)

- General data types

## Importing Data – Data Types (2 of 2)

- Numeric data types

## Importing Data – Example (4 of 4)

- Assign primary key if it is only one field *or*
- Select no primary key and assign key after data is imported

## Populating – Step 2: Keying Data

- Prevents duplicate records by not allowing them
- Key data fields that make the record unique (for example, Date and Time)

- Duplicate

**Notes:**
If duplicates already exist in the data, a key cannot be saved.  Running a find-duplicates query will identify which records are duplicates.  The records will then have to be deleted manually.  After this, a primary key can be set.  Once a key is set, the user will not be allowed to add a duplicate record to the database manually or through an append query.

## Keying Data – How

- Import
*or*
- If data is already in database, run “find duplicates” query and remove existing duplicates, then set key in table design view

## Keying Data – Example (1 of 3)

- Open table in design view
- Highlight fields to be set as keys
- Push the Key button
- Saves when database is closed if no duplicates exist

## Keying data – Example (2 of 3)

- If duplicates exist, the following errors appear:
- Remove the keys by clicking the key button again, then close the table and run a “find duplicates” query

## Keying Data – Example (3 of 3)

- Find Duplicates Query Wizard

- 1.

- 2.

- 3.

- 4.

- Select fields with duplicates

- Select other fields to view

- Manually delete duplicate records

## Populating – Step 3: Checking Data

- Check for outliers
- Look at the minimum and maximum values for each field; are they reasonable?
- Check rate of change between records at each extreme.
- Time stamps
- Has all data been properly matched by time?
- Time series plots can help identify problems shifting from UTC to LST.
- Missing data
- Is the same identifier used for each field?  I.e., –999.
- Units
- Are units consistent among different data sets?  I.e., m/s or knots for wind speeds.
- Validation codes
- Are validation codes consistent among different data sets?
- Do the validation codes match the data values?  I.e., are data values of –999 flagged as missing?

**Notes:**
Make sure to check the rate of change in the top and bottom few records – is the 500 mb height in one record 2000 m less than the next closest?  Sounding data are frequently bad.  Perhaps check by time of year; 6000 m 500 mb heights are fine in June, not in January.

METAR uses different indicators for the different variables (9999 vs. 99).  This is important to know when running queries.

## Using Databases

- Queries
- SYSTAT
- Export
- Forecast tools

## Using Databases – Queries

- Queries are run using Structured Query Language (SQL) code
- Query builder simplifies the process by writing the code for the user
- To write a query,
- Select Query in Access
- Select New
- Select table(s) to query
- Select data in table to query
- Select criteria for query
- Run query

## Queries – Writing (1 of 6) 

- Select Queries, New, and Design View in Access

## Queries – Writing (2 of 6)

- Select table(s) to query
- Click on Add
*or *
- Double-click on selected table

## Queries – Writing (3 of 6)

- Select data in table(s) to query
- Double-click on selected field *OR*
- Click the field once, hold the button down, and drag the field to the field box *OR*
- Click in blank field box and use the drop down menu to select a field

## Queries – Writing (4 of 6)

- Select criteria for query
- Select sort order
- Run query

## Queries – Writing (5 of 6)

- Query builder is writing SQL code for the user.  To view the code, click on the view icon in the upper-left corner and select SQL View.

## Queries – Writing (6 of 6)

- Results: 20 records meet the selected criteria (1,067 in the original table)

## Queries – Merging Data

- After importing different data tables, you may need to merge tables (or queries) into one table for tool development
- Issues:
- Join types
- Master date/time table
- Formats/units (e.g., dates/times may appear identical when they are not

## Merging Data – Example (1 of 9)

- Open new query
- Select tables to join
- Determine which field(s) to join
- Make sure fields have same units and formats
- Click on first field in first table
- Holding the mouse button down, drag it to the matching field in the second table
- Release the mouse and a join line will appear.
- If tables in a query are not joined to one another, Access does not know which records are associated with other records, so it displays every combination of records between the two tables. Therefore, if each table had 10 records in it, the query's results will contain 100 records (10X10).

## Merging Data – Example (2 of 9)

- Open new query
- Select tables to join

## Merging Data – Example (3 of 9)

- Determine which field(s) to join, e.g.,
- Join on Date
- Date in AQData is in LST, so join has to be with Date LST in UA, not Date GMT
- Click on first field in first table
- Holding the mouse button down, drag it to the matching field in the second table
- Release the mouse and a join line will appear.

## Merging Data – Example (4 of 9)

- Select fields

## Two types of joins
Inner (default): Returns records with matching values in the join fields 
Outer: Returns all records from one table and those that match from the other table

- Inner (default): Returns records with matching values in the join fields
- Outer: Returns all records from one table and those that match from the other table

- Merging Data – Example (5 of 9)

- Tip – use a master date table

**Inner joins**

**Notes:**
Make sure fields being joined are truly equal in value and data type.  Are the times both GMT; are they both begin time; are they formatted the same, etc.

Can join tables with tables, tables with queries, or queries with queries.

Join type can cause data to be lost.  The default is fields must be equal in both tables.  

Example SLP_step3 query.  Note that 3/8/99 is missing for SLP_step2, therefore, the calculation cannot be done so there is no output for SLP_Step3.  

A master date table will solve this (or whatever field a complete list of possible values is available for).  

Example Merge_sfc_query.  The query displays all dates in DateList and whatever other data matches the date field.

## Merging Data – Example (6 of 9)

- Right click on join lines to set properties

## Merging Data – Example (7 of 9)

- Inner joins caused record on 3/8/99 to be omitted

## Merging Data – Example (8 of 9)

- Master date table contains all possible dates

- Set all joins to outer joins in the same query

## Outer joins with master date table properly preserves all data

- Merging Data – Example (9 of 9)

- Example: Field is left blank instead of omitting entire record

## Queries – Climatology (1 of 3)

- Create new field (Month1) from existing data
- Right click in blank field and select Build
- Use the Built-in Functions and/or the provided buttons to build your desired computation
- Many similarities to Excel
- The Help button is an excellent resource

## Queries – Climatology (2 of 3)

- Expression appears in Field
- No table will be affiliated with the new field because it is only available to you through running the query

## Queries – Climatology (3 of 3)

- Use Group By functions to obtain counts of high PM2.5 days by month and the maximum observed PM2.5 by month

**Notes:**
Briefly explain month calculation, group by choices (max, count, and where) and the criteria selection

Mention that results could be used to make a histogram in a report, or could be copied or exported to excel to be plotted.

## Using Databases – SYSTAT (1 of 4)

- SYSTAT can read data sets in tables and queries
- Changes made to the data set in SYSTAT cannot be saved to the database
- More details in Session 1B, Part 2

## Using Databases – SYSTAT (2 of 4)

- Using Databases – SYSTAT (2 of 4)

## Using Databases – SYSTAT (3 of 4)

- Select data source type(MS Access database)

- Select database

- Select table or query

- Using Databases – SYSTAT (4 of 4)

## Using Databases - Forecast Tools

- Develop a regression equation using statistical software (See Part 2 of this session)
- Set up a form to enter meteorological data needed for the equation (use the Form Wizard)
- Write a query to compute the forecast from input meteorological data fields
*Option to add an “action button” to the form to output query results to the screen or to a report*
*Some simple Visual Basic coding is necessary for this step; however, Access Help can provide good guidance. *

## Forecast Tools (1 of 10)

- Set up a form to enter meteorological data
- Select Forms
- Select Create form by using wizard

## Forecast Tools (2 of 10)

- Select table or query to populate using form
- Select fields to include on form

## Forecast Tools (3 of 10)

- Select form layout
- Select form style

## Forecast Tools (4 of 10)

- Name the form

## Forecast Tools (5 of 10)

- Write a query to compute forecast from input data

## Forecast Tools (6 of 10)

- You can run query manually after meteorological data is added to view equation output.

## Forecast Tools (7 of 10)

- Add an “action button” to the form to output query results to the screen directly from the form
- Saves the trouble of switching to the Queries page

## Forecast Tools (8 of 10)

- Some simple Visual Basic coding in the form design view (Build Event) is necessary to add action buttons; Access Help provides good guidance.

## Forecast Tools (9 of 10)

- Example of code for the action button

## Forecast Tools (10 of 10)

- Elaborate forms can be created in design view without using a wizard

##   Using Databases - Creating Plots

- Graph the results of the Group By query
- Select New Report
- Select “Create report in design view”
- Select Insert, Chart
- Trace desired size of chart
- Select which table or query to plot
- Select fields to plot
- Select chart type
- Assign fields to axes or series on the chart

## Creating Plots (1 of 5)

- Select New Report
- Select Create new report in design view
- Select Insert Chart

## Creating Plots (2 of 5)

- Trace desired size of chart
- Select which table or query to plot

## Creating Plots (3 of 5)

- Select fields to plot
- Select chart type

## Creating Plots (4 of 5)

- Assign fields to axes or series on the chart
- Note that the completed chart in design view is not how the chart will actually appear!

## Creating Plots (5 of 5)

- Format plot as needed, same as in Excel

## Export

- Database objects can be exported to many formats, such as .mdb, .xls, .txt, .csv, and .rtf
- Exporting a form outputs the data in the table the form is linked to.  The user interface of the form cannot be exported.
- Reports have fewer export format options.  Regardless of selected format, any charts in the report will not be exported.

## Summary

- Databases are a powerful storage medium and are useful for organizing, exploring, and using air quality and meteorological data.
- Queries allow for easy manipulation of data.
- External software packages can link to databases to mine data and develop forecast tools.
- Care must be taken when using databases because many actions are irreversible.

## Resources

- MS Access Help
- Local community college classes
- Microsoft Access 2002 for Dummies (John Kaufeld) – also a version for 2000
- Microsoft Access Version 2002 Step by Step (Online Training Solutions, Inc.)
- Access 2002: The Complete Reference (Virginia Anderson, Megg Morin)
- Many others – check your local bookstore!