DATABASE DESIGN DOCUMENTATION

1 CONSERVATION AND SUSTAINABLE USEOF THEMESOAMERICAN BARRIER REEF SYSTEMSPROJECT (MBRS)Belize Guatemala Honduras - Mexico DATABASE DESIGN DOCUMENTATIOND esign and Implementation of aRegional Environmental Information System (REIS)for the Mesoamerican Barrier Reef Systems Project Project Coordinating UnitCoastal Resources Multi-Complex BuildingPrincess Margaret Box 93 Belize City Belize Tel: (501) 223-3895; 223-4561 Fax: (501) 223-4513 SAM / MBRSCONSERVATION AND SUSTAINABLE USE OF THE MESOAMERICAN BARRIER REEF SYSTEM (MBRS) DATABASE DESIGN DOCUMENTATIONDESIGN AND IMPLEMENTATION OF A REGIONAL ENVIRONMENTAL INFORMATION SYSTEM (REIS) FOR THE MESOAMERICAN BARRIER REEF SYSTEMS PROJECTM esoamerican Barrier Reef Systems Project Coordination Unit Coastal Resources Multi-Complex, Fisheries Compound, Princess Margaret Drive Box 93, Belize City, Belize Central America Tel: 501-22-33895/34561 Fax: 501-22-34513 E-mail.

2 Technical Document #20 REIS DATABASE DESIGN DocumentationTABLE OF CONTENTS PAGE 1. and Hardware Specifications .. Software Hardware requirements .. General Detailed Technical Specifications .. Common Tables .. Site Table .. Survey Table .. 12 15 Person .. Species 19 Species .. 19 Local Name .. Threatened and Mangroves .. Forest Structure .. 24 25 IMBRS Technical Document #20 REIS DATABASE DESIGN Seedling biomass.

3 Interstitial Leaf 30 Coral Reefs .. Manta Tow .. 35 Adult Fish Recruitment .. 40 Rover Point Intercept .. 42 Benthiclut .. Benthic Coral .. Seagrasses .. Seagrass Growth .. Seagrass Seagrass Leaf Area Index .. 53 Appendix A Data Types .. 56 IIMBRS Technical Document #20 REIS DATABASE DESIGN DocumentationDATABASE DESIGN DOCUMENTATION1. INTRODUCTION The Mesoamerican Barrier Reef System (MBRS) is the largest barrier reef system in the Caribbean and the second largest reefsystem in the world.

4 The primary goal of the MBRS project is to enhance protection of these valuable ecosystems. Key to anyprotection strategy is knowledge of the habitats and the various uses of those habitats. Management decisions need to be based on information of the system. While there may be many efforts to monitor, study, and manage individual portions of the MBRS, asystem wide overview of the information available is essential for management of the MBRS as a whole unit. Collecting all of thedisparate information and compiling it into one easily accessible DATABASE is the goal of the Regional Environmental InformationSystem (REIS).

5 The DESIGN of the DATABASE is based on the information that is to be collected, or has been collected in the data are stored in postgresql on a Dell server running Red Hat Linux Version Access to the data will be through a web interface running on Apache web server and using PHP. This DESIGN is to allow easy data entry access and querying toresearchers throughout the 4 countries served by MBRS. The driving philosophy behind the DATABASE DESIGN was to have an efficient, normalized DATABASE that would be easy to maintain andexpand, as well as allow easy data entry and access.

6 - 1 -MBRS Technical Document #20 REIS DATABASE DESIGN AND HARDWARE Software SelectionFollowing reviews of the data that would be entered into the DATABASE , and the requirements of retrieving the data several criteriawere identified that need to be met by the DATABASE software. The minimum requirements for the software were: 1. Must support the relational DATABASE model, and some version of the SQL language. This is an industry standard, and asa program that will be spanning several countries and many years adhering to this standard will ensure the longevity andportability of the DATABASE .

7 In addition most DATABASE administrators are familiar with some form of SQL and relationaldatabases, so training of an administrator will be relatively easy. 2. Need to allow multiple users to access tables simultaneously. Since the data is going to be entered by users from 4 countries and numerous agencies, it is likely that multiple people will be entering data into the same table at the Allow running of stored SQL scripts. There are many processes that can be automated with stored scripts, to facility management, updates, editing, and querying of the DATABASE .

8 This is especially important if users are accessing the datafrom the internet. Being able to call and run a stored script is far easier and more efficient than trying to code all theinformation into a web form. 4. Allow restrictions on the data values entered in columns within a table. Being able to restrict the data to certain ranges orvalues will reduce the possible errors in data Allow creation of multiple indexes on a table, as well as unique indexes within a table. Also must be able to create oneindex on multiple columns.

9 Multiple indexes on a table allow faster sorts and queries based on various a unique index across multiple columns will prevent entering duplicate data. - 2 -MBRS Technical Document #20 REIS DATABASE DESIGN Documentation6. Allow creation of views on the data. This allows a minimum amount of data to be stored and a virtually unlimited numberof outputs to be created. Views allow display of calculated values, without having to create additional columns in the datatables and have them populated with the calculated values.

10 Having the additional columns can lead to conflicting datawithin one record of a data table. In addition, views allow multiple tables to be joined together to provide a customizedview of the data in the data table. 7. Allow inner joins, left outer joins, right outer joins, full outer joins, and multiple joins within a query. The joins are differentways of selecting items from one or more tables, in either a query or a view. The inner join selects only the records thatexist in both tables and matches them up.