gabrielgurley.com

Lesson: Analyzing North Atlantic Hurricane Data Using NeoOffice 2 Base

Lesson Objectives

In this lesson, you will learn the following:

• How to create, run and save a query using Base.

• How to perform comparisons among records using queries.

• How to sort query data results.

• How to specify field properties within table records.

• How to create and print reports.

Overview

In the previous lesson, you learned how to use Base in conjunction with Writer to create labels for a bulk mailing list. Base has the capability to do much more than create basic tables and produce mailing labels. Base also has the capability to create queries, perform comparisons among records, create forms and reports, and more. Moreover, Base can also import from and export to other database applications, including Microsoft Access and FileMaker Pro.

In this lesson, you will become acquainted with additional features and capabilities within Base. Using a Base file containing tables and records related to North Atlantic hurricanes, you will learn about the strongest and costliest hurricanes to strike the United States / North Atlantic region by using Base as a way to analyze and compare available data. Upon completion of this lesson, you will have learned how to use Base to create and run queries, perform record comparisons, how to create and print reports, how to specify field properties within table records and more.


Getting Started

Before we do so, we need to open the Lesson Ten file that is available for use with this course. To open the file, follow these steps:

• If you have not already downloaded the lesson files for this course, click on the Lesson Files topic located in the course lessons area and select to download the .zip file to your computer's desktop.

• When the file appears on your computer’s desktop, double-click the file icon to unzip its contents and access the files associated with each lesson. Double-click the Lesson Files folder icon that contains the available lesson files, locate the Lesson 10 folder and double-click on it to access the file for this lesson. When you have opened the folder, double-click the file named lesson_10_start.odb to open the file.

• For this exercise, you need to save this file to your computer’s hard drive, a network storage drive or a readable/writable removable storage device, such as a floppy disk. To do so, click on the File menu and select Save As from the menu options that appear. Select the location where the database should be saved. In this example, use lesson_10_work as the filename for the saved document. Moreover, select the file type OpenDocument Database. Then click the SAVE button.

How to Create and Run a Query Using the Query Design View

In our previous lesson, we created mailing labels utilizing a table that contained twenty records. When working with databases at school or in the workplace, you may often be entering and analyzing hundreds, even thousands, of records. However, searching or analyzing data by glancing through a table can be a time consuming and cumbersome task when that many records are involved. A much better method of searching and analyzing the data contained within table records is by utilizing queries. A query can be thought of as simply a subset, or filtered set, of records from a table that was created by specifying certain criteria to obtain a desired result.

Before a query can be performed, a new database document must be created and contain at least one table with records entered into it. For this lesson, a file has already been created for you to begin working on which contains two tables: Costliest Ranking and Intensity Ranking. We will use these tables to learn to create a simple query. To do so, follow these steps:

• With the lesson_10_work.odb file open, click on the Queries icon located on the left side of the document window within the Database Pane.

• In the Tasks pane located at the top of the window, single-click Create Query in Design View to launch the Design View window.

• When the Query Design View window appears, the Add Table or Query window will also appear. Using the radio buttons available within the window, tables or queries that have been created within the database file are listed for availability to perform a query. For this example, select the Intensity Ranking table by single-clicking its icon among the list and click the ADD button. Once the Intensity Ranking table appears within the Query Design View window, click the CLOSE button within the Add Table or Query window.

• The next step in creating a query is to select which fields to utilize to run the query. To do so, utilize the first Field popup menu provided within the lower pane of the window to select the appropriate field name. This can be accomplished by simply using the mouse pointer to click within the field. In this example, select the Intensity Ranking.Hurricane field.

• After selecting the Intensity Ranking.Hurricane field, select the table that is associated with the field from Step #5 by using the Table popup menu provided within the same column. In this case, the table selected should be the Intensity Ranking table.

At this point, we could execute the run command to create a basic query. However, the results would not be meaningful, as the query would only contain a list of hurricanes. Therefore, to make this query produce some useful results, we will continue with the creation of the query by selecting the Year field to display the year the hurricane struck the Unites States coast. Moreover, we will enter a criterion to have the query only display hurricanes that reached an intensity of Category 4 strength or greater based upon the Saffir-Simpson scale. To complete this query, continue with the following steps:

• To achieve the desired query results listed above, we will need to select a second field and third field for our query. Under the second column within the lower pane of the Query Design View window, select the Intensity Ranking.Year field within the available Field popup menu. Under the third column, select the Intensity Ranking.Category field.

• Next, select the tables associated with each field by using the Table popup menu provided under the respective columns. In this case, the Intensity Ranking table should be selected for both the second and third column Table fields.

• For this query, we want all of the fields that have been selected to be visible when the query results appear. To make sure this occurs, each column should have a checkmark within the Visible field. This signifies that we are selecting to have the field within the respective column to appear in the results when we execute the Run Query command. If any of the Visible fields do not have a checkmark, use your left mouse button to click within the appropriate checkboxes provided to have a checkbox appear.

• When the query results appear, we want the results to be sorted by a hurricane’s intensity and in descending order. This will result in Category 5 storms appearing at the top of the list, followed by Category 4 storms. To sort the results of the query in this manner, select Descending from the Sort field popup menu in the third column provided.

• Finally, we need to enter criteria regarding of data to display for the query results. Specifically, we need only Category 4 and 5 storms to appear within the query results. To make sure this occurs, we will utilize comparison operators within the query. Comparison operators that are supported within Base include > (greater than), < (less than), >= (greater than or equal to), <= (less than or equal to) and NOT (not equal to).

To do so, take your left mouse button and single-click within the Criterion field located within the third column. When the cursor appears, type >=4 within the field. This comparison operator specifies that only hurricanes with an intensity of greater than or equal to Category 4 will appear within the query results.





How to Run a Query

Prior to running a query, you may wish to execute the Distinct Values command to omit any duplicate entries that may appear within the query results as a result of the criteria selected. To execute the Distinct Values command, simply click the Edit menu and select Distinct Values from the menu options that appear.

Once criteria have been selected to perform a query, the RUN command must be executed to produce its results. To do so, click on the Edit menu and select Run Query from the menu options that appear. Queries can also be ran by clicking on the Run Query button located within the Query Design toolbar at the top of the application window. If the toolbar is not visible, it can be made to appear by clicking on the View menu, select Toolbars from the menu options and selecting Query Design from the submenu options that appear.





Changing the Format of Data Appearing Within a Query Result

When the query results appear, you will notice that only records where a hurricane had an intensity equal to or greater than Category 4 strength appear within the results. You will also notice that the dates that appear within the Year column contain decimal values. We can change the format of the date to exclude decimal values and present the year in its proper form. To do so, follow these steps:

• Within the upper pane of the Query Design View window where the query results appeared, right-click on the column label Year and select Column Format from the contextual menu options that appear. If you are using an Apple one-button mouse, you can hold down the CONTROL button on your keyboard and single-click the mouse button to have a contextual menu to appear as well.

• When the Field Format window appears, enter 0 within the Decimal Places located under the Options selection area.

• Click the OK button. When the Query Design View window returns, the year for each query result should now be in their correct format.

How to Save a Query

Once a query has been ran, a query can be saved so that the results can be accessed later. To save a new query within a database file, follow these steps:

• To save the query performed, click the File menu and select Save As from the menu options that appear.

• When selecting the Save As menu option, a window will appear prompting a Query Name to be entered. Enter Lesson Ten 01 for the query name and click the OK button. The query has now been saved. (NOTE: For the purposes of this lesson, do not close the query results window at this time. We will utilize this query for our next exercise associated with this lesson.)

• When returning to the main database file window, click on the Queries icon located on the left side of the window within the Database pane. When doing so, the query that was saved should appear within the Queries pane in the lower portion of the window. The query can be viewed by double-clicking its icon within the window.

Making Changes to a Query

Once a query has been ran, you can make corrections or changes to the query if you do so before closing the Query Design window, which displays the query results. This makes it a much more efficient way to make corrections or changes, as opposed to closing the Query Design window and starting over with a new query. In our next exercise, we will make changes to our existing query by entering new query criteria and using compound criteria to produce query results. Afterward, we will save the query results for this exercise and learn how to clear the query design without closing the entire Query Design View window.


Entering Query Criteria

To begin this next exercise, we will begin making changes to the existing query results by entering new query criteria. To do so, we will need to add a new table, as well as change the tables and fields selected for the query. To perform these tasks, follow these steps:

• Within the Lesson Ten 01 Query Design View results window, click on the Insert menu and select Add Table or Query from the menu options that appear.

• When the Add Table or Query window appears, use the radio buttons available within the window to select the Costliest Ranking table. To do so, single-click its icon among the list and click the ADD button. Once the Costliest Ranking table appears within the Query Design View window, click the CLOSE button within the Add Table or Query window.

• The next step is to select different tables to utilize for the new query. To do so, utilize the first Table popup menu provided within the lower pane of the window to select the appropriate table. This can be accomplished by simply using the mouse pointer to click within the field. In this example, select the Costliest Ranking table.

• After selecting the Costliest Ranking table, select the appropriate field that is associated with the table from Step #3 by using the Field popup menu provided within the same column. In this case, the field selected should be the Hurricane field.

• Next, we will need to change the tables associated with the second and third columns within our query. Under the second and third columns within the lower pane of the Query Design View window, select the Costliest Ranking table within the available Table popup menus.

• Select the appropriate field associated with each table by using the Table popup menu provided under the respective columns. In this case, the Year field should be selected from the second column Field popup menu while Category should be selected for the third column Field popup menu.

• For this query, we will not have the query results sorted by category. Therefore, using your left mouse button, click on the Sort field located within the third column and select (not sorted) from the popup menu provided. This will disable sorting the query by category.

• Now that the fields and tables associated with the query have been changed, we will add an additional field to be included within the query. In the fourth column provided, select the field Costliest Ranking.Damage from the Field popup menu provided. Underneath the same column, select Costliest Ranking from the Table popup menu. Finally, choose Descending from the Sort popup menu within the same column to specify that the query results be sorted by damage estimated in descending order.

• Like the first query we created earlier, we want all of the fields that have been selected to be visible when the query results appear. To make sure this occurs, each column should have a checkmark within the Visible field. This signifies that we are selecting to have the field within the respective column to appear in the results when we execute the Run Query command. If any of the Visible fields do not have a checkmark, use your left mouse button to click within the appropriate checkboxes provided to have a checkbox appear.

Using Compound Criteria Within Queries

Base, as with many database applications, support the use of compound criteria within queries. Compound criterion typically involves two types: AND criterion and OR criterion. With AND criterion, each criterion selected for their respected fields must be true for the results to appear within the completed compound criteria query. With OR criterion, any true result for each individual criterion will appear within the completed compound criteria query.

Now that the tables and fields for the query have been changed, we now need to specify new criteria for the query prior to running it again. For this query, we will actually utilize AND criterion to produce our desired results. Afterward, we will run the query we designed again to view its results. To do so, follow these steps:

• First, we need to change the query criteria for the Category field. Specifically, we want all storms with an intensity of Category 1 strength or greater to appear within the query results. To do so, take your left mouse button and single-click within the Criterion field located within the third column. When the cursor appears, type >=1 within the field. This comparison operator specifies that only hurricanes with an intensity of greater than or equal to Category 1 (minimal hurricane strength) will appear within the query results.

• Next, we need to specify that our query results produce a list of hurricanes that are at least a Category 1 in intensity and caused at least U.S. $3 billion ($3,000,000,000) in property damage. To do this, single-click within the Criterion field located within the fourth column. When the cursor appears, type >=3000000000 within the field. This comparison operator specifies that only hurricanes with an intensity of greater than or equal to Category 1 (minimal hurricane strength) will appear within the query results. (NOTE: When entering numerical values, be sure to type the numbers without any additional characters included. For example, if the criterion to query includes $2,000, then the numerical value should be typed as 2000 within the Criterion field.)

• Prior to running a query, you may wish to execute the Distinct Values command to omit any duplicate entries that may appear within the query results as a result of the criteria selected. To execute the Distinct Values command, simply click the Edit menu and select Distinct Values from the menu options that appear.

• Once criteria have been selected to perform a query, the RUN command must be executed to produce its results. To do so, click on the Edit menu and select Run Query from the menu options that appear. Queries can also be ran by clicking on the Run Query button located within the Query Design toolbar at the top of the application window. If the toolbar is not visible, it can be made to appear by clicking on the View menu, select Toolbars from the menu options and selecting Query Design from the submenu options that appear.

Saving the Second Query

Once the second query has been ran, it can be saved so that the results can be accessed later. To save the second query within the database file, follow these steps:

• To save the query performed, click the File menu and select Save As from the menu options that appear.

• When selecting the Save As menu option, a window will appear prompting a Query Name to be entered. Enter Lesson Ten 02 for the query name and click the OK button. The query has now been saved. (NOTE: For the purposes of this lesson, do not close the query results window at this time.)

• When returning to the main database file window, click on the Queries icon located on the left side of the window within the Database pane. When doing so, the query that was saved should appear within the Queries pane in the lower portion of the window. The query can be viewed by double-clicking its icon within the window.

Clearing the Query Design

If a user wishes to make corrections or changes to criteria selected prior to or after running a query, they can clear the Query Design View window as opposed to closing it and restarting. To clear the Query Design View window, follow these steps:

• Click the Edit menu and select Clear Query from the menu options that appear. All tables and criteria selected will be cleared from the Query Design View window. However, the window will not be closed to allow the user to add new tables or queries and select new criteria.

• The Query Design View window can also be cleared by clicking on the Clear Query button located within the Query Design toolbar at the top of the application window. If the toolbar is not visible, it can be made to appear by clicking on the View menu, select Toolbars from the menu options and selecting Query Design from the submenu options that appear.

• To close the Query Design View window for this exercise, click the File menu and select Close from the menu options that appear.

Examining the Results of the Queries

As mentioned at the beginning of this lesson, a query provides a simpler way of analyzing data contained within database records by allowing users to specify certain criteria to obtain a subset, or filtered set, of desired results. To provide an example of using databases for real-world use, you will utilize the queries just created to answer the following questions regarding the hurricane statistics found within this lesson’s database file. When answering these questions, you might also find it useful to create new queries to further assist in answering the questions.

• From 1851 to 2004, how many Category 5 hurricanes have made landfall in the United States?

• Of those Category 5 hurricanes that made landfall in the United States, which years did they make landfall and how many years apart were there between storms?

• Of the Top 10 costliest storms in the United States, as identified in the Lesson Ten 02 query results, how many are considered to be strong or intense hurricanes? (Strong or intense hurricanes are those classified as Category 3,4 or 5 based upon the Saffir-Simpson scale.)

• Of the Top 10 costliest storms in the United States, as identified in the Lesson Ten 02 query results, how many made landfall between 1990 and 2004?

• Based upon the query results performed, do you think there may be a correlation between the rise of the Industrial Revolution, global warming and the number of catastrophic hurricanes (Category 5) to form in the Atlantic Ocean and strike the United States? Why? (HINT: You may want to refer to the Additional Resources section for web links to documents that can help you answer this question. However, there is not a specific answer to this question.)

• The records within this lesson’s database table(s) contain intensity data regarding hurricanes that made landfall in the United States from 1851 to 2004. Are there other intense hurricanes that made a significant impact to the United States after 2004? If so, what was the name of the hurricane, where did it make landfall, and what impact did it have to the area affected by the storm and the United States as a whole? (HINT: You may want to refer to the Additional Resources section for web links to documents that can help you answer this question.)

• In October 1998, Hurricane Mitch made landfall in the Central American country of Honduras. Mitch became one of the deadliest hurricanes to strike the Western Hemisphere in over two centuries, with an estimated death toll of 11,000 people. At landfall in Honduras, Mitch was a Category 2 storm with sustained winds of 100 miles per hour (85 knots) and a minimum central pressure of approximately 987 millibars (mb). How does the storm’s category and minimum central pressure compare to strong hurricanes that have made landfall in the United States? If the wind speed of the storm was not the primary cause of loss of life, what was? (HINT: Refer to the following webpage to help answer the question - http://www.nhc.noaa.gov/1998mitch.html.)

How to Specify Field Properties Within Tables

Earlier when we ran the queries we created, you noticed the dates that appear within the Year column contained decimal values. We were able change the format of the date to exclude decimal values and present the year in its proper form within the Query Design View window. However, we could have also changed the format prior to running the query by opening the table associated with the query and entering field property specifications. That way, the data would have appeared in its correct format immediately after running the query. To specify field properties within a Base, follow these steps:

• In the Database pane located on the left side of the window, click on the Tables icon.

• In the Tables pane located at the bottom-right of the window, select the Intensity Ranking table by single-clicking on the icon.

• Click on the Edit menu at the top of the application window and select Edit from the menu options that appear. You can also select the Edit command by right-clicking on the table icon and select Edit from the contextual menu that appears. If you are using an Apple one-button mouse, you can bring up a contextual menu by holding down the CONTROL key on the keyboard and click the mouse button on the table icon.

• When the Table Design window appears, left-click within the gray area to the left of the field name Year to select the entire row.

• In the Field Properties selection area located at the bottom of the Table Design window, change the Decimal Places field properties to 0 and the Length field properties to 4 utilizing the selection fields provided.

• To exit the Table Design window, click on the Window menu at the top of the window and select Close Window from the menu options that appear or simply hold down the COMMAND (⌘) key and press the W key on the keyboard to return to the main Base application window. When closing the window, a prompt window may appear asking whether to save the changes being made to the table. Click the YES button to do so and the window will close.

How to Create and Print a Report

Like many other database applications, Base has the ability to create and print reports. A report can be thought of as simply a text document that presents the current data within the database, or the data selected at the time of printing. Base provides a wizard that walks users step-by-step through the process of creating a report. For this exercise, we will utilize the records within our database to create a basic report. To create a new report using the Report Wizard, follow these steps:

• In the Database pane located on the left side of the window, click on the Reports icon.

• In the Tasks pane located at the top of the window, left-click Use Wizard To Create Report to launch the Report Wizard.

• When the Report Wizard window appears, the first step will be to select the fields from an existing table to include in the report. In the Tables or Queries popup menu, select the Intensity Ranking table from the popup menu provided.

• After the table has been selected, fields that contain records to be presented in the report need to also be selected. Using the selection area provided, single-click the Hurricane field and click the right arrow button in the middle of the window to specify that the field be included in the report creation process. After doing so, also select the Year and Minimum Pressure (mb) fields to be included in the report creation process. Then click the NEXT button.

• In the next step of the Report Wizard, you can customize the field labels by entering the labels into the text fields provided (optional). For this example, we will leave the field labels as they are. Then click the NEXT button.

• In this step of the Report Wizard, you can customize the group levels using the selection area provided (optional). Records are grouped based upon the values in the selected fields. Up to four fields can be grouped in a report. For this example, we will leave the group levels as they are. Then click the NEXT button.

• Next, you can customize the sort order of the fields using the selection area provided (optional). For this example, we will select to sort the data by Minimum Pressure (mb) and in ascending order. Using the popup menu and radio buttons provided, make the appropriate selection. Then click the NEXT button.

• In this step of the Report Wizard, you can select the layout in which the report data will be presented. If the report should not possess a custom layout, you would make sure Default is selected for the header and footer layout, as well as the data layout. For this example, we will choose Modern for the data layout and Worldmap for the header / footer layout. We will also select Portrait for the page orientation. Make the appropriate layout selection and click the NEXT button.

• In the final step of the Report Wizard, select a title to save a report as by entering a title into the text field provided. For this example, we will title the report as Intensity Ranking 1851-2004. Also, select to create a static report or a dynamic report by clicking the appropriate radio button provided. A static report is where the data in the report will not change, and a dynamic report is where the data in the report may change periodically based upon the data entered into the corresponding table. For this example, we will select Static Report. Then click the FINISH button to complete the process of creating a report.

• To print a copy of the report generated, click on the File menu and select Print from the menu options that appear. To print one copy of the entire report, simply press the PRINT button and the report will begin to print. Otherwise, use the Page Range and Copies selection area to customize the print configuration before clicking the PRINT button.