Rainforest Fragments Database Application Design Proposal


Client: Rainforest Rescue International

Prepared by: Mike Edwards, Challenges Worldwide consultant / developer

Updated: 15/10/2010. Possible enhancements added (section 9)


    1. Outline


Rainforest Rescue International would like to record and track changes in the pieces of rainforest left in the Sri Lanka. This would assist in the analysis of the stocks remaining, and in planing conservation and restoration activities into the future.


The purpose of this document is to propose a solution that will fulfil these requirements. Of necessity, options have been proposed that the developer is familiar with.


The audience is those managers at RRI who need to approve the project. A second (though not necessarily secondary) audience, is future IT developers who may need to know technical details of the solution in order to maintain, enhance or bug-fix the application. As these two groups have quite disparate needs, I must apologise that some parts of the document will not be comprehensible to either one group or the other.


  1. Objectives

Build an application that can record, store, retrieve, report and display information on the rainforest. Do this within the 3 months of the placement at RRI.


System Overview

  1. Functionality


Specific uses / functionality:


  1. Data Input, including perhaps import of Excel or comma delimited bulk data

  2. Data display and retrieval through the use of:

    1. Reports

    2. Charts

    3. Raw data export (eg Excel or csv)

  3. Backup of data for security / redundancy

  4. Access within RRI office Wakunagoda Road, Galle

  5. Role based (user-names and passwords to set permissions)

  6. Maintenance of some application data, such as reference data on forest structural classification, species, accounts, etc., to be possible through the application. Additionally, viewing the application log to help with error resolution, and running SQL scripts to update the database

  7. Ability to upload photos


See Appendix 2, Use Case Diagram, and Appendices 3 – 9 for Activity Diagrams


  1. Description


It is proposed that a web application be built using Microsoft technologies. Logically, the application will have three layers: data layer, application layer and presentation layer, though all three layers will reside in one physical machine: the server. Indeed, both the data layer and the business logic component of the application will be handled by SQL server, where the latter will be implemented through table relationships and stored procedure logic primarily. The client will be a thin web client. This is shown schematically below (Diagram 1).



D
iagram 1. Application topology



The advantages of this over a Windows forms (executable client) solution (or indeed other solutions) are as follows:


  1. Clients need have no software installed (assuming they have a pre-existing web browser, such as Internet Explorer). This is the case at RRI

  2. All staff members have the potential to access the application as they have the pre-requisite technology. Their access is dependent purely on being given an account.

  3. Deployment is to one machine only (a networked machine, the 'server'). Minimum requirements for the server are already met at RRI

  4. Updates need only be deployed to the server: if required this can be done remotely over the internet

  5. When required, though beyond the scope of this project, the application can be moved to web server and made available over the web.

  6. Microsoft technologies have been chosen as the developer has experience with these; they are well built; free versions are available and integration of the components is guaranteed.

  7. It is planned to implement permissions and roles using forms based authentication. As currently RRI in Galle do not have a Windows Domain, it is not possible to use Windows authentication. This is not a major issue. Roles will allow different groups of staff to have higher or lower access to the data. This will also be useful if the application gets deployed to the web at a future date.

  8. Reference data will be included in the database to minimise the incidence of 'free-text' data entry errors. This will assist users and main integrity of the data. Reference data will include:

    1. forest structural, growth and leaf forms

    2. species

    3. towns and communities


  1. Data to be captured


Data based on the work of Dr Ranil Senanayake1.. The data set will include:


  1. Fragment description and details (location, ID number etc.)

  2. Fragment time details (date of observations, area) with measurements of variables that can change with time:

    1. Fragment structural, leaf and growth forms.

    2. Dominant tree and other plant species. Other plant and animal species (see appendices 1a, 1b and 1c, Data Structure, for more complete details)






  1. Technologies and Requirements


Microsoft (MS) technologies will be used for development (specifically MS Web Developer 2005 Express, MS SQL Server Management Studio Express). The application requires MS Windows technologies to run (see Application Requirements)


  1. Software

    1. Server

      1. Operating System: Windows XP Professional or better

        1. Includes the required web server, IIS 5 or better

      2. MS .Net Framework 2

      3. SQL Server 2005 Express or better

    1. Client

      1. Operating System: any that support a web browser, including Windows XP, Linux, Mac OS

      2. Web browser that supports javascript, cookies and images: IE 5 or better

  1. Hardware

    1. Server

      1. Pentium 4 1000 Mhz or better

      2. 500 MB RAM

    2. Client

      1. Pentium 3 600 MHz or

      2. Apple Macintosh G3 233 MHz

  2. Other

    1. Internet connection required for use of Integrated Taxonomic Information System (ITIS)2 web services for species search

    2. Clients need a TCP/IP connection to the server


All these technologies are in place at RRI, Galle, or are available at no charge.


  1. Issues / Risks / Contingencies


  1. It is possible that in future the system will need enhancement. It is unlikely any RRI staff could perform this work. Similarly with bug fixes.

    1. contingency: the consultant (Mike) to perform this work from overseas or

    2. IT development skills could be purchased from a Sri Lankan based company

  2. Data input through a web form is likely to be slightly slower than a Windows application. Doing both is beyond the scope of the project. It is planned to include functionality to upload data from a flat file (e.g. xls, csv) which would allow data capture using other software.

  3. Use of the ITIS system has at least two risks. Firstly, the application will need internet access. Secondly, the data is incomplete

    1. contingency: species information specific to Sri Lankan rainforest will be held locally within the database.


  1. Limitations / Known Issues


  1. Version 1 is not planned to be internet available. It will only be available within the RRI Galle office. However, deployment to Verio, an ISP who already perform some hosting for RRI, should be possible as they use MS technologies such as SQL Server, IIS and asp.net (up to version 2). The Analog Forestry Network is also on a Windows hosting platform with http://myhosting.com/. Indeed deployment to any Microsoft web server would be possible.

  2. The application and database will not attempt to be a primary source (or indeed any sort of copy or duplicate of another source) for species data, unless the data is specifically generated by RRI and not available elsewhere. So for instance, RRI are developing a database of plants at http://67.18.176.25/pdb/login.php. At the time of writing this has not been populated with data. It would not be a good idea to attempt to duplicate this data in the fragments database, but when the plants database is up and running, it should be possible to link to, or make intermittent copies of this database. It is a MySQL database and ODBC drivers are available for this for Windows. With animal and other species, the fragments database will simply record simple identification details, and links will be made to external resources if more information is required. ITIS is discussed below, but Catalog of Life also offers possibilities too: http://www.catalogueoflife.org/annual-checklist/2010/search/

  3. An attempt will be made to use species data from the ITIS2 using their web services. This is relatively complete for North America, but not Sri Lanka. However, this international project aims to complete major international species by 2011, and for species referenced there is a large amount of validated data that can be used within this application if required.

  1. Possible Enhancements


  1. Deployment to a web server on the internet

  2. Species to have further genus / family type classification added. This will make searching more user friendly, and reporting by genus, family, kingdom etc. Proposed stratification as per ITIS and Species 2010:


Species

Genus

Family

Order

Class

Phylum

Kingdom

A brief internet search seems to show that these tables are not available for SQL Server so would have to be built from scratch: a lot of work.



Appendices


Appendix 1a: Data Structure (database tables and fields with types)


Note on the data structure:

Presented below is a list of the major data storage tables, and reference tables. The main omission is the role (or membership tables. The reason for this is that these table are standard tables generated by the asp.net development environment, typically in the aspnetdb database.

Although the database tables will store the data, good practice dictates that no direct access should be made to these tables. Stored procedures will be written to Select, Update, Insert and Delete data. These will start with the prefix 'sp_' and end respectively with '_SELECT', '_UPDATE', '_INSERT' and '_DELETE'. Database views will start with the prefix 'vw_'.


[country_ref](

[country_id] [int] IDENTITY(1,1) NOT NULL,

[country_name] [nvarchar](200) NOT NULL,

[country_region_id] [int] NULL)

[error_range_error](

[error_range_error_id] [int] IDENTITY(1,1) NOT NULL,

[range_error_field] [nvarchar](250) NOT NULL,

[range_error_max_deviantion_from_previous_percent] [int] NOT NULL)

[forest_level_2_indicator_type](

[forest_level_2_indicator_type_id] [int] IDENTITY(1,1) NOT NULL,

[forest_level_2_indicator_type_code] [nchar](1) NOT NULL,

[forest_level_2_indicator_type] [nvarchar](100) NOT NULL)

[fragment](

[frag_id] [int] IDENTITY(1,1) NOT NULL,

[frag_descr] [nvarchar](200) NULL,

[level2_indicator_2_4_coordinates_long_degrees_east] [int] NULL,

[level2_indicator_2_4_coordinates_long_minutes_east] [int] NULL,

[level2_indicator_2_4_coordinates_lat_degrees_north] [int] NULL,

[level2_indicator_2_4_coordinates_lat_minutes_north] [int] NULL,

[frag_level_2A_2_12_closest_town_or_city] [int] NULL,

[id_number_first_duet] [int] NULL,

[id_number_second_quartet] [int] NULL,

[id_number_third_quintet] [int] NULL,

[deleted] [datetime] NULL,)

[fragment_level_2A_2_11_neighbourhood_communities](

[fragment_level_2A_2_11_neighbourhood_communities_id] [int] IDENTITY(1,1) NOT NULL,

[fl2AL2L11_fragment_id] [int] NULL,

[fl2AL2L11_town_id] [int] NULL)

[fragment_time](

[frag_time_id] [int] IDENTITY(1,1) NOT NULL,

[ft_frag_id] [int] NOT NULL,

[frag_measured] [datetime] NOT NULL,

[frag_area_estimated_sq_m] [float]))

[fragment_time_level_1_indicators](

[ftl1i_id] [int] IDENTITY(1,1) NOT NULL,

[fll1i_fragment_time_id] [int] NOT NULL,

[ftl1i_growth_form_category_ref_id] [int] NULL,

[fll1i_structural_category_height_ref_id] [int] NULL,

[fll1i_structural_category_coverage_ref_id] [int] NULL)

[fragment_time_level_1_indicators_leaf_characteristics](

[ftl1ilc_id] [int] IDENTITY(1,1) NOT NULL,

[ftl1i_id] [int] NOT NULL,

[leaf_characteristics_ref_id] [int] NOT NULL)

[fragment_time_level_2_indicators](

[ftl2i_id] [int] IDENTITY(1,1) NOT NULL,

[ftl2i_frgament_time_id] [int] NOT NULL,

[ftl2i_2_1_country_ref_id] [int] NULL CONSTRAINT [DF_Table_1_ftl2i_country_ref_id] DEFAULT ((164)),

[ftl2i_2_2_elevation_metres] [int] NULL,

[ftl2i_2_3_landform] [nvarchar](500) NULL,

[ftl2i_2_5_local_classification] [nvarchar](1000) NULL,

[ftl2i_2_7_status] [nvarchar](1000) NULL,

[ftl2i_2_8_area_under_forest_metres] [int] NULL,

[ftl2i_2_9_area_scheduled] [nvarchar](1000) NULL,

[ftl2i_2_10_responsible_authority] [nvarchar](1000) NULL,

[ftl2i_2_13_map_availability] [bit] NULL,

[ftl2i_2_13_map_details] [nvarchar](500) NULL,

[ftl2i_2_14_ngo_involvement] [bit] NULL,

[ftl2i_2_14_ngo_details] [nvarchar](500) NULL,

[ftl2i_2_15_geologic_description] [nvarchar](500) NULL,

[ftl2i_2_15_geologic_mean_annual_temp_degrees_C] [float] NULL,

[ftl2i_2_15_geologic_mean_annual_rainfall_mm] [nchar](10) NULL,

[ftl2i_2_16_scenic] [nvarchar](500) NULL,

[ftl2i_2_17_tree_special_features] [nvarchar](500) NULL,

[ftl2i_2_18_non_tree_plant_special_features] [nvarchar](500) NULL,

[ftl2i_2_19_animal_special_features] [nvarchar](500) NULL,

[ftl2i_2_20_human_special_features] [nvarchar](500) NULL,

[ftl2i_indicator_type_id] [int] NULL)

[fragment_time_species_ass](

[fragment_time_species_id] [int] IDENTITY(1,1) NOT NULL,

[species_id] [int] NOT NULL,

[frag_time_id] [int] NULL,

[species_number_seen] [int] NULL,

[species_number_estimated_in_entire_fragment] [int] NULL,

[fts_species_prevalence] [int] NULL,

[is_dominant_species] [bit] NULL)

[growth_form_category_group_ref](

[gfc_group_ref_id] [int] IDENTITY(1,1) NOT NULL,

[form_category_group_number] [int] NOT NULL,

[form_category_group] [nvarchar](250) NOT NULL,

[form_category_group_description] [nvarchar](1000) NULL,

[gfc_group_sub_category_id] [int] NULL)

[growth_form_category_ref](

[gfcr_id] [int] IDENTITY(1,1) NOT NULL,

[growth_form_category] [nvarchar](250) NOT NULL,

[growth_form_category_symbol] [nchar](1) NOT NULL,

[gfc_group_ref_id] [int] NOT NULL,

[gfc_group_sub_cat_id] [int] NULL,

[growth_form_category_description] [nvarchar](500) NULL)

[growth_form_group_sub_category_ref](

[gfgscr_id] [int] IDENTITY(1,1) NOT NULL,

[gf_group_sub_category] [nvarchar](250) NOT NULL,

[gf_group_sub_cat_description] [nvarchar](250) NULL)

[region_ref](

[region_id] [int] IDENTITY(1,1) NOT NULL,

[region_name] [nvarchar](250) NOT NULL)

[species_prevalence_ref](

[species_prevalence_id] [int] IDENTITY(1,1) NOT NULL,

[species_prevalence] [nvarchar](250) NOT NULL,

[species_prevalence_descr] [nvarchar](250) NULL)

[species_ref](

[species_id] [int] IDENTITY(1,1) NOT NULL,

[species_ITIS_tfn] [int] NULL,

[species_latin_name] [nvarchar](300) NULL,

[species_common_name] [nvarchar](200) NULL,

[species_local_name] [nvarchar](200) NULL,

[species_genus_ref] [int] NULL

[is_tree_species] [bit] NULL,

[is_rainforest_quality_indicator_species] [bit] NULL)

[structural_category_class_ref](

[sc_class_ref_id] [int] IDENTITY(1,1) NOT NULL,

[structural_category_class_number] [int] NOT NULL,

[structural_category_class] [nvarchar](250) NOT NULL,

[structural_category_class_description] [nvarchar](500) NULL)

[structural_category_ref](

[scr_id] [int] IDENTITY(1,1) NOT NULL,

[structural_category] [nchar](1) NOT NULL,

[structural_category_class_id] [int] NOT NULL,

[structural_category_description] [nvarchar](500) NULL,

[structural_category_minimum] [float] NULL,

[structural_category_maximum] [float] NULL,

[structural_category_units_id] [int] NULL)

[town_ref](

[town_id] [int] IDENTITY(1,1) NOT NULL,

[town] [nvarchar](120) NOT NULL,

[province] [nvarchar](120) NULL,

[district] [nvarchar](120) NULL,

[country_ref_id] [int] NULL,

[population] [int] NULL)

[units_ref](

[unit_id] [int] IDENTITY(1,1) NOT NULL,

[unit_name] [nvarchar](100) NOT NULL,

[unit_description] [nvarchar](500) NULL)



Appendix 1b: Data structure with descriptions






















Appendix 3: Activity Diagram, Enter Data Manually








Appendix 4, Activity Diagram, Upload data from file






Appendix 5, Activity Diagram, Extract Raw Data






Appendix 6, Activity Diagram, Run reports






Appendix 7, Activity Diagram, Update reference values





Appendix 8, Activity Diagram, Manage application






Appendix 9, Activity Diagram, Run system reports







Appendix 10, a note on the UML Diagrams


Only two types of Unified Modelling Language (UML) diagrams have been used in this design specification: Use Case and Activity. The document does however include two non-UML diagrams: an application topology diagram and a database table schema.


Other diagram types were not thought useful. If is possible that some presentation logic or other code might be included in separate classes, but it was felt there was no reason or a class diagram. State transition diagrams were also omitted as little will be held within object states. Communication diagrams have also been omitted as there are not many application components between which communication will occur. Data Flow diagrams were also not felt to be necessary for the reason that there is little data flow or transformation, except to and from the database.


Within the Activity Diagrams, an addition has been made to indicate where a Graphical User Interface component is required with the graphic




This is to assist development activities.


Additionallly, the activities are shown in pale blue where they are a system activity e.g.:







and in grey when it is a user activity:




References


  1. Senanayake 1989, An Identification System For Refugial Forest Patches. (full citation needed)

  2. ITIS: Integrated Taxonomic Information System. http://www.itis.gov/ (full citation needed)

Version (saved date): 15/09/10