Creating Relationships - United Nations University



Chapter Two: PLEC Agrobiodiversity Database

Introduction

The design of the PLEC Agrodiversity Database can be divided into two stages. The first is the transfer of Agrobiodiversity data into the PLEC Agrobiodiversity Database. The second stage includes the expansion of the Agrobiodiversity Database to integrate data that fall under the broader category of agrodiversity. This chapter provides an overview of the Agrobiodiversity Database and contains step-by-step instructions on transferring data from Excel to Access.

The Structure of the Agrobiodiversity Database

The Agrobiodiversity Database contains six tables linked together through relationships (see Annex II). It is based on the guidelines suggested by Daniel Zarin in volume 14 of PLEC News and Views. The only major modification is the addition of a Species List table containing a Species ID, which is discussed in detail below. Depending on the cluster, some adjustment to the Agrobiodiversity Database may be necessary to include agrobiodiversity data that does not fit under the proposed structure. Furthermore, some data may be irrelevant in certain clusters and, while blank fields are not a problem, can be removed from the database to avoid confusion. The Yunnan database provides and interesting example of this (see Annex II or the China Agrodiversity Database on the CD for a sample of Yunnan database structure). Be sure to review table design and relationships in chapter one before modifying the six-table structure.

Once the structure of the PLEC Agrobiodiversity database is completed, the database will not allow entries that have improper relationships. Data entry must be approached systematically from the general tables to the specific. Pay strict attention to “referential integrity” when entering data in a table that refers to a more general table. For example, an entry in the Plot Description table must refer to a specific sample area in the Sample Area table. If there are only two sample areas recorded in the sample area table and these are sample area numbers 1 and 2, the Sample Area Number column in the Plot Description table can only contain the numbers 1 or 2. The same is true when describing the species within a plot. Each entry in the Species Description table must refer to the plot number of the specific plot in which the species is located.

Transferring data from Excel to Access

This section explains how to transfer data from Excel into the PLEC Agrobiodiversity Database and reviews a few essential concepts regarding relational databases. The PLEC Agrodiversity Database is on the CD that accompanies this manual. Be sure to save the file onto a hard drive before attempting to input data. Data cannot be saved onto a CD. While the transfer of data is simply a ‘copy’ and ‘paste’ operation, if problems occur, they will most probably be due to the format and inconsistency of data. Access certainly has more ‘rules’ than Excel and moving data between programs often requires editing and re-organizing data. The benefits derived from the transfer are clearly worth the effort and provide an extremely efficient way to input, store, and analyze data.

a. Using a Species ID number

The complexity of scientific names and the multiplicity and variations in spelling of local names create problems when analyzing data in Access. Any misspelling, even the use or non-use of an accent mark, will result in the name being registered as a completely different species. For this reason the PLEC database uses a Species ID number, rather than the scientific or common name, to indicate a species. In order to use a Species ID, a list of species must be created with a unique number assigned to each species. This list of species and unique numbers will be linked to the database, and numbers can be changed to species names in queries and reports. In using numbers rather than names for species, consistency is extremely important. For instance, if the number 6 is assigned to Acer rubrum in the species list, then any entry for the abundance, utility, measurements, etc. of all Acer rubrum in the database should be referenced to the species list by putting the number 6 in the Species ID column. No species should be assigned two numbers and no two numbers should be assigned to the same species.

If data are to be converted from Excel to Access the Species List with unique Species IDs for each species should be created before importing data into the PLEC Agrobiodiversity Database. The PLEC Agrobiodiversity Database does not have columns for common and scientific names in any of its tables except the Species List table. After creating a species list and assigning each species a unique number, the common and scientific names should be deleted and replaced by Species ID numbers in the Species Data, Utility Data, and Tree Data tables.

b. Converting files from Excel to the PLEC Agrobiodiversity Database

There are four important points that must be taken into account before converting tables from Excel to Access.

1) The column titles in Excel (field headings) must be in the same order as they are in the Access tables.

2) The type of data in Excel must match the data type specified in Access for each column. To avoid errors in entering data and making calculations with data in Access, the PLEC Database requires that specific data types be entered into each column. The list of data types for columns in an Access table can be viewed by opening the table in Design View (to open a table in design view, first open the table then select Design View from the View menu on the top of the screen). See chapter one for a description of each data type. Annex III contains the data types for the six tables including notes on the fields that may be problematic.

3) Each table must have a Primary Key. In some cases, one of the columns in the Excel table is the primary key (e.g. in the Sample Areas table the Sample Area Number is the primary key). In other cases, the primary key is automatically added when data are pasted or entered into the table (e.g. in the Species Data table the column on the right titled ID with "(AutoNumber)" written in the empty row is the primary key). In cases where the primary key is not automatic, the primary key column must be carefully checked to make sure that each entry has a unique number. See Annex III for more on the primary key in each table.

4) Since the tables are linked through relationships, Access will not allow data to be inputted in fields where there is no relationship in a more general table (e.g. the Sample Area table contains more general data than the Plot Description table). For this reason, data must be pasted into the tables starting from the general proceeding to the specific. A good order to use when pasting data into Access would be: Sample Area table, Plot Descriptions table, Species List table, Species Data table, Tree Data table, and then Utility Data table. See the section titled ‘Linking Tables Through Relationships’ in chapter one for more on relationships.

After checking to make sure that the column headings, data types, and primary key are correct, simply copy the Excel tables and paste them into the Access tables. There are a few things to take note of when copying and pasting tables:

1) Copy all data in the Excel table by highlighting it and selecting Copy from the Edit menu on the top of the screen. All the data from the table should be highlighted and copied, however DO NOT INCLUDE THE COLUMN TITLES.

2) After copying the raw data from the Excel table, open up the matching Access table. In order to successfully paste every record it is necessary to highlight the new entry row. To highlight a row for pasting new data simply click on the asterisk (sometimes it is an arrow instead of an asterisk) that indicates the new entry row in the left-most portion of the column. After highlighting the column, select Paste from the Edit menu on the top of the screen. All of the copied records should paste into the Access table. If the data are all in the proper format, the program will ask if you would like to paste the rows. Click Yes It is common to run into errors at this point. All errors will be pasted into a new table called "paste errors." Fortunately, most of these errors can be easily fixed. See Annex IV for a list of common errors and solutions.

Repeat the above procedure for the six tables.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download