Home

Database Design Documentation - Mesoamerican Barrier Reef

1. database program It is used to identify when the record was added 40 MBRS Technical Document 20 REIS Database Design Documentation 3 4 4 Rover Diver The rover diver table rover is set up similar to the recruit table Only species that are recorded on the survey are entered into the database This setup minimizes the size of the table and allows for easy expansion of the species list that can be recorded in the table Even though the rover diver is not based on a transect for compatibility with the other tables in the group a transect record still needs to be created before data can be entered into this table The rover table is linked to the transect table on transect_id Table Name rover Column Type Descriptive Valid Values Index Allow Description Name name Column Nulls transect_id integer Transect ID Unique No This field links these records to the transect Index 2 table The values are automatically entered by the input form species_id integer Species ID 0 2999 Unique No This is the field that contains the id number for Index 2 the species and is linked to the species table abundance char 1 Abundance S F M A No A one letter code for the number of individuals Codes seen for the species record_id integer Record ID Unique No Unique identifier for the record This is Index 1 entered automatically by the database and is used for internal operations only The user does
2. 14 MBRS Technical Document 20 REIS Database Design Documentation Column Name Type Descriptive name Valid Values Index Column Allow Nulls Description survey_id integer Survey ID Unique Index 1 No This is an integer value assigned by the database to uniquely identify this record The data from all the datasheets links back to this record on the survey_id number This number should not be entered or altered by the user usrid integer User ID No This field is populated automatically by the web interface with the user id The id links to the login table so the user name can be displayed This field is used to keep track of who enters the data adddate date Date No This field is populated automatically by the database program It is used to identify when the record was added The various sample datasheets are each given their own table and are related back to the survey table Multiple sample sheet tables may relate back to one survey table entry This design minimizes the data entry required for identifying the sampling site and time 3 1 3 Transect The survey table records all of the information that is collected once per each site visit In addition to the survey information a table is needed to keep track of the transects at each survey The information recorded for each transect is usually limited to start times and
3. transect_id LONG FK species_id LONG FK survey_id LONG abundance TEXT 1 record_id LONG site_id TEXT 25 FK startdate DATE enddate DATE starttime DATE person_col LONG FK person_proc LONG Fk tide TEXT 1 sea TEXT 15 wind TEXT 10 winddir INT cloud INT secchimark DOUBLE secchicoll DOUBLE depthstart DOUBLE depthend DOUBLE tempair DOUBLE currentspd DOUBLE currentdir DOUBLE survey_type TEXT usrid INT adddate DATE transect transect_id LONG survey_id LONG person_col LONG starttime DATE usrid INT adddate DATE person person_id LONG person TEXT 50 agency TEXT 120 usrid INT adddate DATE MBRS REIS Edit Date 5 27 2004 10 07 15 AM usrid INT adddate DATE mantatow transect_id LONG FK tow INT corallive TEXT 2 coraldead TEXT 2 softcoral TEXT 2 algae TEXT 2 features TEXT 254 record_id LONG usrid INT adddate DATE benthic recruitfish transect_id LONG FK species_id LONG FK fishcount LONG record_id LONG usrid INT adddate DATE adultfish transect_id LONG Fk species_id LONG FK cm0_5 LONG cm6_10 LONG cm11_20 LONG cm21_30 LONG cm31_40 LONG cm41_ LONG record_id LONG usrid INT adddate DATE diameter DOUBLE height DOUBLE olddead INT newdead INT bb BINARY wb BINARY wpii BINARY yb BINARY dsi
4. Column Type Descriptive Valid Values Index Allow Description Name name Column Nulls corallive char 2 Coral Cover 0 1 2 3 4 5 No This is a percentage cover of live coral The Live 1 2 3 4 5 following code is used to record the percent 1 42 4 3 4 4 coverage 5 0 0 1 1 10 2 11 30 3 31 50 4 51 75 5 76 100 For each category except 0 a plus or minus is added to denote whether the estimate falls into the upper or lower half of each category coraldead char 2 Coral Cover 0 1 2 3 4 5 No This is a percentage cover of dead coral The Dead 1 2 3 4 5 following code is used to record the percent 1 2 3 4 5 coverage 0 0 1 1 10 2 11 30 3 31 50 4 51 75 5 76 100 For each category except 0 a plus or minus is added to denote whether the estimate falls into the upper or lower half of each category 36 MBRS Technical Document 20 REIS Database Design Documentation Column Type Descriptive Valid Values Index Allow Description Name name Column Nulls softcoral char 2 Cover SC 0 1 2 3 4 5 No This is a percentage cover of soft coral The 1 2 3 4 5 following code is used to record the percent 1 42 4 3 4 4 coverage 5 0 0 1 1 10 2 11 30 3 31 50 4 51 75 5 76 100 For each category except 0 a plus or minus is added to denote whether the estimate falls into th
5. 5 21 25 knots 4132 MBRS Technical Document 20 REIS Database Design Documentation Column Type Descriptive Valid Index Allow Description Name name Values Column Nulls winddir integer Wind 0 360 Yes The direction the wind is blowing from in Direction degrees cloud integer Cloud Cover 0 8 Yes Cloud cover is quantified in terms of eighths of the sky area covered by clouds The unit of measure is the okta with a possible range of 0 no clouds to 8 completely overcast secchimark decimal 9 3 Secchi at Yes Secchi reading at the time the seagrasses are Marking marked for the seagrass growth data This field is used only in association with the seagrass growth transects secchicoll decimal 9 3 Secchi at Yes Secchi reading at the time the seagrasses are Collection collected for the seagrass growth data This field is used only in association with the seagrass growth transects tempair decimal 9 3 Air Yes The ambient air temperature at the time of the Temperature sampling C currentspd decimal 9 3 Water Yes The water current speed in meters sec at the Current time of sampling Speed m s currentdir decimal 9 3 Water 0 360 Yes The water current direction at the time of Current sampling In degrees from north Direction survey_type varchar 15 Survey Type No This is a character field that identifies what data set this survey record is associated with
6. IE transect_id LONG Fk IE litter trap INT tare_leaf DOUBLE total_leaf DOUBLE tare_bract DOUBLE tare_flower DOUBLE total_flower DOUBLE tare_fruit DOUBLE total_fruit DOUBLE tare_wood DOUBLE total_wood DOUBLE tare_misc DOUBLE total_misc DOUBLE record_id LONG usrid INT adddate DATE species_id LONG FK E transect_id LONG Fk IE seedlingbio sapling TEXT 8 height DOUBLE tare DOUBLE total DOUBLE jo record_id LONG usrid INT adddate DATE species_id LONG FK IE transect_id LONG Fk IE seedling subplot INT position_x DOUBLE position_y DOUBLE sapling LONG cbh DOUBLE height DOUBLE live TEXT 1 le record_id LONG usrid INT adddate DATE species_id LONG FK IE transect_id LONG Fk IE interstitialwater depth LONG sedexposed TEXT 1 salinity INT record_id LONG usrid INT adddate DATE transect_id LONG FK IE 34 MBRS Technical Document 20 REIS Database Design Documentation 3 4 Coral Reefs As with mangroves there are numerous tables that are related to data collected at coral reef sites The tables for site and survey are the same as for mangroves The following tables are in the coral reef monitoring group manta adult recruit rover benthic pointintercept and benthiclut This is only a logical assemblage of tables and is not physically set as an assemblage in the data structure The c
7. Yes Free form comments field for other features of the coral record_id integer Record ID Unique Index 1 No Unique identifier for the record This is entered automatically by the database and is used for internal operations only The user does not see this value usrid integer User ID No This field is populated automatically by the web interface with the user id The id links to the login table so the user name can be displayed This field is used to keep track of who enters the data adddate date Date No This field is populated automatically by the database program It is used to identify when the record was added The following page has the entity relationship diagram for corals and fish 46 MBRS Technical Document 20 site location TEXT 50 site_id TEXT 25 latitude DOUBLE longitude DOUBLE agency TEXT 80 ecosystem TEXT 25 habitat TEXT 25 country TEXT 15 category INT description TEXT selection TEXT 14 usrid INT adddate DATE survey REIS Database Design Documentation species localname species_id LONG gen_spec TEXT 45 e o name TEXT 35 i FT Sant Teer carr TEX class TEXT 25 eo ndate LONG record id LONG element TEXT 10 idate LONG s kiles id LONG FK IE usrid INT species_id LONG FK IE usrid INT e usrid INT DATE adddate DATE adddate DATE
8. 9 3 Water Yes The water depth in meters at the start of the Depth coral transect This field is only used for coral Start transects depthend decimal 9 3 Water Yes The water depth in meters at the end of the Depth coral transect This field is only used for coral End transects bearing integer Bearing 0 360 Yes Heading in degrees from north of the transect line from the shoreline Used for mangrove forest zonation 16 MBRS Technical Document 20 REIS Database Design Documentation Column Type Descripti Valid Values Index Allow Description Name ve name Column Nulls transect_id integer Transect Unique No This is an integer value assigned by the ID Index 1 database to uniquely identify this record The data from all the datasheets links back to this record on the transect_id number This number should not be entered or altered by the user usrid integer User ID No This field is populated automatically by the web interface with the user id The id links to the login table so the user name can be displayed This field is used to keep track of who enters the data adddate date Date No This field is populated automatically by the database program It is used to identify when the record was added 3 1 4 Person The person table is a lookup table that provides an authoritative list of names of the people and their agencies that are registered to collect data f
9. BINARY dsii BINARY rb BINARY asp BINARY other BINARY bleach TEXT 2 comments TEXT record_id LONG usrid INT adddate DATE transect_id LONG Fk species_id LONG FK benthiclut benthic_id INT component TEXT 25 usrid INT adddate DATE pointintercept transect_id LONG FK benthic_id INT FK benthcount INT record_id LONG Description Coral and Fish data structure layout Target DB PostgreSQL Rev 1 Creator Jeffrey Dahlin Filename DesignDoc_v_5 doc Company RPI comments TEXT 254 A7 MBRS Technical Document 20 REIS Database Design Documentation 3 5 Seagrasses There are three tables associated with seagrasses in addition to the site and survey tables The seagrass tables were more complex to set up and data entry may be a little more complex However with this structure it should facilitate querying minimize redundancy and reduce errors 3 5 1 Seagrass Growth The seagrass growth table contains the data from the Seagrass Growth Data entry form For this table the survey table should have a start date and end date As with all other tables this table is linked backed to the transect table with the transect_id The quadrat number from the seagrass growth data sheet is recorded in this table and not in the transect table since there is only one set of data entered for each quadrat An entry in the transect table is still required for compatibility with othe
10. N No Indicate whether the sediment surface was Surface above the water level at the time of sample Exposed collection This field takes a Y S or N salinity smallint Salinity No The salinity of the water in parts per ppt thousand It is expected that salinity would be whole numbers record_id integer Record ID Unique No Unique identifier for the record This is Index 1 entered automatically by the database and is used for internal operations only The user does not see this value usrid integer User ID No This field is populated automatically by the web interface with the user id The id links to the login table so the user name can be displayed This field is used to keep track of who enters the data 29 MBRS Technical Document 20 REIS Database Design Documentation Column Type Descriptive Valid Values Index Allow Description Name name Column Nulls adddate date Date No This field is populated automatically by the database program It is used to identify when the record was added 3 3 5 Leaf Litter The litter table is based on the leaf litter data entry form This is one of the forms that requires a start and end dates The start and end dates are entered in the survey table and not in the litter table This same table is used for initial biomass but there would only be an entry for the start date in the survey table and the end date would be blank Table Nam
11. No The y location in meters relative to the corner of the plot tree integer Tree No Unique No The tree number in the plot Index 2 species_id integer Species ID 0 2999 No This is the field that contains the id number for the species and is linked to the species table cbh decimal 9 3 Circumference Yes The circumference at Breast Height This cm value is the measure of the circumference of the tree in centimeters From this the diameter at breast height DBH will be calculated DBH is a common measurement used in forestry proproot decimal 9 3 Prop Roots Yes The height of the prop root in centimeters cm 24 MBRS Technical Document 20 REIS Database Design Documentation Column Type Descriptive Valid Index Allow Description Name name Values Column Nulls trunklength decimal 9 3 Trunk Length Yes The length of the trunk in meters This is m the distance from the proproot to the first branch treeheight decimal 9 3 Tree height Yes The height of the tree in meters m record_id integer Record ID Unique No The unique identifier for the record This is Index 1 entered automatically by the database and is used for internal operations only The user does not see this value usrid integer User ID No This field is populated automatically by the web interface with the user id The id links to the login table so the user name can be displayed This field is used to keep
12. REIS Database Design Documentation 3 2 Species List Several tables are required to create an efficient species list Since this database is being used in several countries and in 2 languages there could be a problem keeping track of common names and threatened and endangered status of various species The names and status change from country to country and may even be different within different regions of a country Therefore these features have been broken out into separate table to accommodate the potential variety 3 2 1 Species The species table is the basis of all the species information At present it only contains the scientific name Genus and species family order class and broad taxonomic classification such as bird fish invertebrate etc It is hoped that it will be able to be expanded to accommodate the full taxonomic information for each species All of the tables that require species names store only the species_id from this table Table Name species Column Type Descriptiv Valid Values Index Allow Description Name e Name Column Nulls species_id integer Species ID 1 2999 Unique No An integer number to link the species name Index 1 to the various tables This number is a unique identifier for the species gen_spec varchar 45 Scientific Unique No The scientific Genus and species name of Name Index 2 the animal or plant If species level information is not known then the value in the field wo
13. The site table was created to store general information for the site This is one of the smallest tables yet it provides a spatial reference to all of the data in the database In the event that a GIS system is used this table can be used as a link between the spatial information and the data This table is not fully normalized The fields ocation and ecosystem could be kept in a separate table to minimize space used by this table However it was decided to include them in this table to facilitate the ease of use In addition this table is expected to be relatively small possibly only a few hundred records therefore splitting out these fields into a separate table would not result in any significant savings in space The fields for the site table are MBRS Technical Document 20 Table Name site REIS Database Design Documentation Column Type Descriptive Valid Values Index Allow Description Name name Column Nulls site_id varchar 8 Site ID Unique No This field is a unique alphanumeric identifier for Index the site This identifier should be used by all data collectors for the site location varchar 50 Location No This field is the name of the SMP Location that is being monitored by this site for example Lighthouse Reef latitude decimal 9 6 Latitude 15 0 N to No The latitude of the site in decimal degrees For 21 5 N accuracy this should be to 5 decimal places approximately 1 meter lo
14. all they hold is a true or false value If the coral has the disease the box is checked and the value in the field is set to true If the disease is not present the box is left blank and the value in the field is blank Table Name benthic Column Type Descriptive Valid Values Index Allow Description Name name Column Nulls transect_id integer Transect ID Index 2 No This field links these records to the transect table The values are automatically entered by the input form species_id integer Species ID 0 2999 Index 2 No This is the field that contains the id number for the species and is linked to the species table diameter integer Max 0 500 No The diameter in centimeters of the coral head Diameter being measured cm height integer Max Height 0 500 No The height in centimeters of the coral head cm being measured MBRS Technical Document 20 REIS Database Design Documentation Column Type Descriptive Valid Values Index Allow Description Name name Column Nulls olddead integer Dead Old 0 100 No Percent of the coral that has been long dead This is an integer from 1 to 100 newdead integer Dead 0 100 No Percent of the coral that has recently died Recent This is an integer from 1 to 100 bb binary Black Band Yes This is a yes no field Yes if it has Black Band Disease Disease and No if it does not have the disease wb binary White Band Y
15. for all of these tables is the transect_id Therefore this group can be easily changed by adding additional tables or removing tables as needed In all the tables that require species name a species_id is stored in the data table that is linked to the species table identified above This provides the flexibility of easily accommodating changes to the scientific or common names if necessary in the future It also eliminates the possibility of misspelling a species name 293 MBRS Technical Document 20 REIS Database Design Documentation 3 3 1 Forest Structure This data table is for recording the information for the forest structure It is based on the forest structure spreadsheet It is related to the transect table by the transect_idfield Since there may be numerous plots at one site there would be one transect_id record for each plot There would then be multiple entries in this table for each transect record It is also linked to the species table on species_id The table is as shown below Table Name structure Column Type Descriptive Valid Index Allow Description Name name Values Column Nulls transect_id integer Transect ID Unique No This field links these records to the transect Index 2 table The values are automatically entered by the input form position_x decimal 9 3 Position X No The x location in meters relative to the corner of the plot position_y decimal 9 3 Position Y
16. not see this value usrid integer User ID No This field is populated automatically by the web interface with the user id The id links to the login table so the user name can be displayed This field is used to keep track of who enters the data adddate date Date No This field is populated automatically by the database program It is used to identify when the record was added s MBRS Technical Document 20 REIS Database Design Documentation 3 4 5 Point Intercept The pointintercept table corresponds to the data form Point Intercept Transect Data Entry Form in the SMP Manual The information in this table identifies the various types of substrate on the transect The identifier in this table is the column benthic_id This is an ID number that is linked to the table benthiclut which contains the names for the substrate The information in this table is entered by transect with the transect number being recorded in the transect table This table is linked to the transect table on transect_id Table Name pointintercept Column Type Descriptive Valid Values Index Allow Description Name name Column Nulls transect_id integer Transect ID Unique No This field links these records to the transect Index 2 table The values are automatically entered by the input form benthic_id integer Benthic Unique No This is an ID that links to the benthic Component Index 2 component
17. tare in grams If the scale is already g adjusted to compensate for tare weight enter the final weight reading here record_id integer Record ID Unique No Unique identifier for the record This is Index entered automatically by the database and is used for internal operations only The user does not see this value usrid integer User ID No This field is populated automatically by the web interface with the user id The id links to the login table so the user name can be displayed This field is used to keep track of who enters the data adddate date Date No This field is populated automatically by the database program It is used to identify when the record was added 28 MBRS Technical Document 20 REIS Database Design Documentation 3 3 4 Interstitial Water This table has the interstitial water salinity information for the mangrove areas It is based on the interstitial water entry form As with the forest structure this table is related back to the transect table with transect_id Table Name interstitial Column Type Descriptive Valid Values Index Allow Description Name name Column Nulls transect_id integer Transect ID Index 2 No This field links these records to the transect table The values are automatically entered by the input form depth integer Approx No Depth in centimeters at which the sample Sample was collected Depth cm sedexposed char 1 Sediment Y S
18. the net weight can be Leaves g calculated standtarewt decimal 9 3 Tare Wt Yes The tare weight for old standing crop in grams Old Standing Crop 9 standgrosswt decimal 9 3 Gross Wt Yes The gross weight for old standing crop in Old grams From the tare and gross the net Standing weight can be calculated Crop 9 record_id integer Record ID Unique No Unique identifier for the record This is Index 1 entered automatically by the database and is used for internal operations only The user does not see this value usrid integer User ID No This field is populated automatically by the web interface with the user id The id links to the login table so the user name can be displayed This field is used to keep track of who enters the data adddate date Date No This field is populated automatically by the database program It is used to identify when the record was added 49 MBRS Technical Document 20 REIS Database Design Documentation 3 5 2 Seagrass Biomass The seagrass biomass table is based on the seagrass biomass entry form There is one record in the table for each core replicate taken As with the seagrass growth table a record needs to be entered into the transect table for the seagrass biomass If two stations are sampled for each site the entries in the transect table would represent the stations None of the calculated fields such as Ratio A B are stored in the table These ar
19. track of who enters the data adddate date Date No This field is populated automatically by the database program It is used to identify when the record was added 3 3 2 Seedling The seedling table is used for recording the information from the Mangrove structure seedling sapling data entry form As with the forest structure this table is related back to the transect table with transect_id It is also linked to the species table on species_id There is one transect record for each plot and there will be 5 subplots within the plot 25 MBRS Technical Document 20 REIS Database Design Documentation Table Name seedling Column Type Descriptive Valid Index Allow Description Name name Values Column Nulls transect_id integer Transect ID Unique No This field links these records to the transect Index 2 table The values are automatically entered by the input form subplot smallint Subplot No Unique No The subplot identifier within the plot Index 2 position_x decimal 9 3 Position X cm No The x location in centimeters relative to the corner of the plot position_y decimal 9 3 Position Y cm No The y location in centimeters relative to the corner of the plot sapling integer Sapling or Unique No The seedling sapling number in the subplot Seedling No Index 2 species_id integer Species ID 0 2999 No This is the field that contains the id number for
20. CONSERVATION AND SUSTAINABLE USE OF THE MESOAMERICAN BARRIER REEF SYSTEMS PROJECT MBRS Belize Guatemala Honduras Mexico DATABASE DESIGN DOCUMENTATION SAM MBRS 6 Design and Implementation of a Regional Environmental Information System REIS for the Mesoamerican Barrier Reef Systems Project Project Coordinating Unit Coastal Resources Multi Complex Building Princess Margaret Drive P O Box 93 Belize City Belize Tel 501 223 3895 223 4561 Fax 501 223 4513 Email mbrsCbtl net Website http www mbrs org bz CONSERVATION AND SUSTAINABLE USE OF THE MESOAMERICAN BARRIER REEF SYSTEM MBRS DATABASE DESIGN DOCUMENTATION DESIGN AND IMPLEMENTATION OF A REGIONAL ENVIRONMENTAL INFORMATION SYSTEM REIS FOR THE MESOAMERICAN BARRIER REEF SYSTEMS PROJECT Mesoamerican Barrier Reef Systems Project Coordination Unit Coastal Resources Multi Complex Fisheries Compound Princess Margaret Drive P O Box 93 Belize City Belize Central America Tel 501 22 33895 34561 Fax 501 22 34513 E mail mbrs btl net MBRS Technical Document 20 REIS Database Design Documentation TABLE OF CONTENTS PAGE Te INTO UCA A A A A waves snk A A A AI 1 2 Software and Hardware Specifications wiii sl ceeed ee aldetdatieeeelaniisceveeeai dinars neneheaeeede 1 20 Software Sole int A O Ri 1 2 23 Hardware FEGUIROMPING ce ti dor toco eres 4 2 20 General PEC A ION e 4 2 2 2 Detailed Technical Spec alOAS aint ES A A bas 4 Database
21. Desi Mitad ir 8 3411 Common Tables cerrara rasa 9 A eal 6 nner er ee nee ee eee ee eee ree te ee een 9 Bde OUI OY a CCA PE O 12 3 13 Tansetltasinna an 15 Bel A PO ss T E E A T TT 17 CAE 01 101 T E E E et ie EA ts E A E E 19 3 2 O e a a a A sled e bik ble a a 19 desa Local Nal E T A A E E E 20 3 2 3 Threate ed and ENdangerSd raciocinio 22 AR 23 O73 POSES UC a ec ls 24 A cds 25 MBRS Technical Document 20 REIS Database Design Documentation i BCU IMIG LOMAS Seasick O A O 27 3 3 4 Interstitial Waters saee dahon least E amanda dan E A E a ed 29 Be SAUL A O O ONO 30 3 39 50 ZONA editando 32 JA Coral Sii do 35 A yee nati ct teeta a a ale a a ea ae ie cet cate idence a Sh a aaa eaaa Gai 35 3 42 AQUA SU cassette dele Se eta ocio 38 B45 FISKE TRS CIUNLIMIONE cet A S A ainateci ua use sdnece dea ed unadidsun E txaaad sh ne A gah anche T 40 34 4 R ver DIV aa ia N E a E a S 41 3 4 5 Point Intercept ii 42 3 40 Benthic RA 43 CETA Benthic Coral e EE E E T 44 AA a a wea a tcc a A a a a a ie wetee exmiate 48 Dab Seagrass OWN as 48 A lee ETN OA O 50 350 Seagrass Leaf Area Index a O 53 Appendix A Data TYPOS iii A A a A E E LAA dues diner eee 56 I MBRS Technical Document 20 REIS Database Design Documentation DATABASE DESIGN DOCUMENTATION 1 INTRODUCTION The Mesoamerican Barrier Reef System MBRS is the largest barrier reef system in the Caribbean and the second largest reef system in the world The primary goal of the MBRS p
22. _id field Survey_idis automatically added by the database when a new survey record is entered The survey table has one entry for each separate dataset i e point intercept benthic survey and benthic coral survey every time a site is surveyed This table is connected back to the site table by site_id This design allows the site information to be entered only once regardless of how many samples are collected at a site on a given day The survey table has one entry for each time a site is sampled There is a one to many relationship between the site table and the survey table Below is the layout of the survey table Table Name survey Column Type Descriptive Valid Index Allow Description Name name Values Column Nulls site_id varchar 8 Site ID Index 2 No This field relates the survey table back to the site table In the survey table this value is automatically entered by the input form startdate date Sampling After Index 2 No The beginning date for a timed interval Start Date January 1 sampling such as leaf litter or seagrass 2003 and growth If there is no timed sampling the start before the date is the date of the sampling entry date enddate date Sampling After Yes The ending date for a timed interval sampling End Date January 1 such as leaf litter or seagrass growth If there 2003 and is no timed sampling then this field is left before the blank entry date starttime time Time No The time of day when sampling wa
23. _id integer Record ID Unique No Unique identifier for the record This is Index 1 entered automatically by the database and is used for internal operations only The user does not see this value usrid integer User ID No This field is populated automatically by the web interface with the user id The id links to the login table so the user name can be displayed This field is used to keep track of who enters the data adddate date Date No This field is populated automatically by the database program It is used to identify when the record was added 3 5 3 Seagrass Leaf Area Index The seagrass leaf area index table sglai is used to store the information from the leaf area index form This table is linked back to the survey table through the transect table There should be one entry in the transect table for each quadrat that is sampled The sglai table has one record for each leaf that is measured The area for each leaf is not entered This value is calculated by the database Table Name sglai Column Type Descriptive Valid Values Index Allow Description Name name Column Nulls transect_id integer Transect ID Unique No This field links these records to the transect Index 2 table The values are automatically entered by the input form 53 MBRS Technical Document 20 REIS Database Design Documentation Column Type Desc
24. ams total_fruit decimal 9 3 Fruit Tare Yes The total weight for fruit including tare in Sample g grams tare_wood decimal 9 3 Wood Tare g Yes The tare weight for wood in grams total_wood decimal 9 3 Wood Tare Yes The total weight for wood including tare in Sample g grams tare_misc decimal 9 3 Miscellaneous Yes The tare weight for the miscellaneous Tare g material in grams total_misc decimal 9 3 Miscellaneous Yes The total weight for miscellaneous material Tare Sample including tare in grams 9 record_id integer Record ID Unique No Unique identifier for the record This is Index 1 entered automatically by the database and is used for internal operations only The user does not see this value usrid integer User ID No This field is populated automatically by the web interface with the user id The id links to the login table so the user name can be displayed This field is used to keep track of who enters the data 31 MBRS Technical Document 20 REIS Database Design Documentation Column Type Descriptive Valid Index Allow Description Name name Values Column Nulls adddate date Date No This field is populated automatically by the database program It is used to identify when the record was added 3 3 6 Zonation This table contains the data from the Mangrove characterization zonation data entry form As with the forest structure
25. ar 14 Selection Criteria unbiased strategic representative Yes The method by which the site was chosen Unbiased Chosen based on a random sampling strategy Strategic Chosen with local knowledge because they are threatened suspected to be degraded or in particularly good condition or because they are currently being monitored through another program Representative Chosen with local knowledge to be representative of reefs in that area usrid integer User ID No This field is populated automatically by the web interface with the user id The id links to the login table so the user name can be displayed This field is used to keep track of who enters the data adddate date Date No This field is populated automatically by the database program It is used to identify when the record was added A description of all the data types can be found in Appendix A 11 MBRS Technical Document 20 REIS Database Design Documentation 3 1 2 Survey Table One level down from the site table is the survey table This table is linked to the site table via the site_id value This table is used across all of the groups This table contains the detailed sampling specific information such as time date sampler weather conditions lab providing analysis etc All of the specific data collection tables are linked to this table through the transect table and the survey
26. database program It is used to identify when the record was added 21 MBRS Technical Document 20 REIS Database Design Documentation 3 2 3 Threatened and Endangered This table identifies the threatened and endangered status of species It includes the national ranking for each country as well as the IUCN Red Book listing The only species included in this list are those that are listed as threatened or endangered in a country or have and IUCN Red Book listing of critically endangered endangered or vulnerable It is designed to accommodate different listing statuses based on country This table is linked to species on species_id Table Name tande Column Type Descriptiv Valid Values Index Allow Description Name e Name Column Nulls species_id integer Species ID 1 2999 Index 2 No This field contains the id number for the species and is linked to the species table nation char 1 National T E R Yes Field to indicate the national endangered or Ranking threatened status The values are T Threatened E Endangered R Rare iucn char 2 IUCN Red CR EN VU Yes Field to indicate the international status this Book is based on the IUCN Red List The values Listing are CR critically endangered EN endangered VU vulnerable Typically the other categories of the IUCN would not be used country char 2 Country MX BZ GT HN Index 2 Yes The country for which the national listing is Code applicabl
27. e litter Column Type Descriptive Valid Index Allow Description Name name Values Column Nulls transect_id integer Transect ID Unique No This field links these records to the transect Index 2 table The values are automatically entered by the input form trap smallinteger Trap No 1 10 Unique No The trap id of the trap that was used to Index 2 collect the leaf litter This is going to be an integer species_id integer Species ID 0 2999 Yes This is the field that contains the id number for the species and is linked to the species table tare_leaf decimal 9 3 Leaves Tare Yes The tare weight for the leaves in grams 9 total_leaf decimal 9 3 Leaves Tare Yes The total weight for leaves including tare in Sample g grams 30 MBRS Technical Document 20 REIS Database Design Documentation Column Type Descriptive Valid Index Allow Description Name name Values Column Nulls tare_bract decimal 9 3 Bract Tare g Yes The tare weight for bract in grams total_bract decimal 9 3 Bract Tare Yes The total weight for bract including tare in Sample g grams tare_flower decimal 9 3 Flower Tare Yes The tare weight for flowers in grams total_flower decimal 9 3 Flower Tare Yes The total weight for flowers including tare Sample g in grams tare_fruit decimal 9 3 Fruit Tare g Yes The tare weight for fruit in gr
28. e ndate integer National Yes An integer number to represent the year and Source month of the source data for determining the Date national threatened or endangered status listing The value is entered as YYYYMM 22 MBRS Technical Document 20 REIS Database Design Documentation Column Type Descriptiv Valid Values Index Allow Description Name e Name Column Nulls idate integer IUCN Red Yes An integer number to represent the year and Book month of the source data for determining the Source IUCN listing status The value is entered as Date YYYYMM record_id integer Record ID Unique No Unique identifier for the record This is Index 1 entered automatically by the database and is used for internal operations only The user does not see this value usrid integer User ID No This field is populated automatically by the web interface with the user id The id links to the login table so the user name can be displayed This field is used to keep track of who enters the data adddate date Date No This field is populated automatically by the database program It is used to identify when the record was added 3 3 Mangroves The following tables are in the mangrove monitoring group structure seedling seedlingbio litter zonation interstitialwater This is only a logical assemblage of tables and is not physically set as an assemblage in the data structure The common links
29. e all calculated by the database as required for viewing or printing reports based on the input values Table Name sgbiomass Column Type Descriptive Valid Values Index Allow Description Name name Column Nulls transect_id integer Transect ID Unique No This field links these records to the transect Index 2 table The values are automatically entered by the input form replicate integer Core Unique No The core replicate number Replicate Index 2 diameter decimal 9 3 Core No The diameter of the core in centimeters Diameter cm depth decimal 9 3 Core Depth Yes Depth of core in centimeters cm livingshoots integer Living Yes Number of living shoots in the core shoots Core tgrntare decimal 9 3 Thalassia Yes The tare weight for the green leaves in grams Green Leaves Tare Wt g tgrngross decimal 9 3 Thalassia Yes The gross weight for the green leaves in Green Leaves grams From the tare and gross the net Gross Wt g weight can be calculated tshttare decimal 9 3 Thalassia Yes The tare weight for the short shoots in grams 50 MBRS Technical Document 20 REIS Database Design Documentation Column Type Descriptive Valid Values Index Allow Description Name name Column Nulls Short Shoots Tare Wt g tshtgross decimal 9 3 Thalassia Yes The gross weight for the short shoots in Short Shoots gra
30. e upper or lower half of each category algae char 2 Algae 0 1 2 3 4 5 No This is a percentage cover of algae The 1 2 3 4 5 following code is used to record the percent 1 42 4 3 4 4 coverage 5 0 0 1 1 10 2 11 30 3 31 50 4 51 75 5 76 100 For each category except 0 a plus or minus is added to denote whether the estimate falls into the upper or lower half of each category features text Other Yes Free form comments field for other features of Features the reef record_id integer Record ID Unique No Unique identifier for the record This is Index 1 entered automatically by the database and is used for internal operations only The user does not see this value 37 MBRS Technical Document 20 REIS Database Design Documentation Column Type Descriptive Valid Values Index Allow Description Name name Column Nulls usrid integer User ID No This field is populated automatically by the web interface with the user id The id links to the login table so the user name can be displayed This field is used to keep track of who enters the data adddate date Date No This field is populated automatically by the database program It is used to identify when the record was added 3 4 2 Adult fish The adult table records the information from the adult fish entry form On the adult fish data entry form in the SMP Manual there is a row fo
31. ed with null values 8 Have some method of replication between two servers Since the data is going to be housed on two servers some sort of replication is necessary 9 Allow triggers on the data tables Triggers will allow predetermined actions to be taken when information is entered edited or deleted from a data table Column data checks are an intrinsic form of triggers 10 Allow data entry from the internet Most of the data will be entered into the forms from the internet 11 Had to run on a Linux System The project is running a Linux server therefore the database program must run on Linux Based on these requirements the qualified software was examined was Oracle Informix Ingres and PostgreSQL All of these products met the requirements outlined above PostgreSQL offered the best price performance of the qualified software Based on research of the computer literature PostgreSQL appeared to serve data over the web as fast or nearly as fast as any of the other MBRS Technical Document 20 REIS Database Design Documentation products Also being an open source program there is no upfront cost to acquiring the software Based on the cost and performance PostgreSQL was chosen as the software to use for this project Following the selection of the database software and operating system the web server and server side scripting language defaulted to Apache Web server and PHP This is the best combination that supports Linux and Postg
32. er Grass Yes The tare weight for the nongreen tissue from Nongreen other grasses in grams Tissue Tare Wt g ongrngross decimal 9 3 Other Grass Yes The gross weight for the nongreen tissue from Nongreen other grasses in grams From the tare and Tissue Gross gross the net weight can be calculated Wt g fatare decimal 9 3 Fleshy Algae Yes The tare weight for fleshy algae in grams Tare Wt g fagross decimal 9 3 Fleshy Algae Yes The gross weight for the fleshy algae in Gross Wt g grams From the tare and gross the net weight can be calculated caabvtare decimal 9 3 Calcareous Yes The tare weight for above ground calcareous Algae Above algae in grams Ground Tare Wt g caabvgross decimal 9 3 Calcareous Yes The gross weight for the above ground Algae Above calcareous algae in grams From the tare and Ground Gross gross the net weight can be calculated Wt 9 cablwtart decimal 9 3 Calcareous Yes The tare weight for the below ground Algae Below calcareous algae in grams Ground Tare Wt g cablwgross decimal 9 3 Calcareous Yes The gross weight for the below ground Algae Below calcareous algae in grams From the tare and Ground Gross Wt g gross the net weight can be calculated 52 MBRS Technical Document 20 REIS Database Design Documentation Column Type Descriptive Valid Values Index Allow Description Name name Column Nulls record
33. es This is a yes no field Yes if it has White Band Disease Disease and No if it does not have the disease wpii binary White Yes This is a yes no field Yes if it has White Plague ll Plague ll and No if it does not have the disease yb binary Yellow Yes This is a yes no field Yes if it has Yellow Blotch Blotch Disease and No if it does not have the Disease disease dsi binary Dark Spots Yes This is a yes no field Yes if it has Dark Spots Disease Disease and No if it does not have the disease dsii binary Dark Spots Yes This is a yes no field Yes if it has Dark Spots Disease ll Disease II and No if it does not have the disease rb binary Red Band Yes This is a yes no field Yes if it has Red Band Disease Disease and No if it does not have the disease asp binary Aspergillosis Yes This is a yes no field Yes if it has Aspergillosis and No if it does not have the disease 45 MBRS Technical Document 20 REIS Database Design Documentation Column Name Type Descriptive name Valid Values Index Column Allow Nulls Description other binary Other Yes This is a yes no field Yes for all other unconfirmed pathogen produced diseases and No if it does not have other disease bleach char 2 Bleached N P PB BL No code for level of bleaching N No Bleaching P Pale PB Partly Bleached BL Bleached comments text Comments
34. he tree in meters m observations text Observations Yes Free form text field that allows entry of any additional observations made at the location record_id integer Record ID Unique No Unique identifier for the record This is Index 1 entered automatically by the database and is used for internal operations only The user does not see this value usrid integer User ID No This field is populated automatically by the web interface with the user id The id links to the login table so the user name can be displayed This field is used to keep track of who enters the data adddate date Date No This field is populated automatically by the database program It is used to identify when the record was added The following page has the entity relationship diagram for mangroves zB 31 MBRS Technical Document 20 site site_id TEXT 25 location TEXT 50 latitude DOUBLE longitude DOUBLE agency TEXT 80 ecosystem TEXT 25 habitat TEXT 25 country TEXT 15 category INT description TEXT selection TEXT 14 usrid INT adddate DATE survey survey_id LONG site_id TEXT 25 FK startdate DATE y enddate DATE starttime DATE person_col LONG FK person_proc LONG FK tide TEXT 1 sea TEXT 15 wind TEXT 10 winddir INT cloud INT secchimark DOUBLE secchicoll DOUBLE depthstart DOUBLE depthend DOUBLE 0 tempair DOUBLE i currentspd DOUBLE currentdir DOUBLE survey_type TEXT
35. mail or paging to keep administrators informed of potential server problems before they become critical Fault monitoring of voltage fan and thermal conditions to help ensure notification in case of potential problems Asset management features to enable system administrator to inventory server MBRS Technical Document 20 REIS Database Design Documentation Item Description configuration CPU memory and disk information helping keep track of systems and keep them up to date Built in remote management Management of drive array under RAID Controller Pre Executable Environment PXE support of embedded NICs Must have Simple Network Management Protocol SNMP agent software available Environmental Parameters Operating Temperature 10 C to 35 C 50 F to 95 F Operating Relative Humidity 8 to 80 non condensing Storage Relative Humidity 5 to 95 non condensing Hardware Documentation Set Users Manual Installation and Trouble Shooting Guide on CD Warranty and Hardware Support 3 Year Onsite Parts and Labor Warranty Uninterruptible Power Supply Stand alone 2200VA 1600W Smart UPS 120 V to provide 30 minutes of runtime at half load with the following features Input 120V Output 120V Input frequency 50 60 Hz 3 Hz auto sensing DB 9 RS 232 Interface Port w Smart UPS signalling RS 232 cable Network grade line conditioning Management Software on CD User Manual amp Ins
36. ms From the tare and gross the net Gross Wt g weight can be calculated trhztare decimal 9 3 Thalassia Yes The tare weight for the rhizomes in grams Rhizomes Tare Wt g trhzgross decimal 9 3 Thalassia Yes The gross weight for the rhizomes in grams Rhizomes From the tare and gross the net weight can be Gross Wt g calculated troottare decimal 9 3 Thalassia Yes The tare weight for the roots in grams Roots Tare Wt trootgross decimal 9 3 Thalassia Yes The gross weight for the roots in grams From Roots Gross the tare and gross the net weight can be Wi g calculated tdeaatare decimal 9 3 Thalassia Yes The tare weight for the dead tissue in grams Dead Tissue Tare Wt tdeadgross_ decimal 9 3 Thalassia Yes The gross weight for the dead tissue in grams Dead Tissue From the tare and gross the net weight can be Gross Wt g calculated ogrntare decimal 9 3 Other Grass Yes The tare weight for green tissue from other Green Tissue grasses in grams Tare Wt g ogrngross decimal 9 3 Other Grass Yes The gross weight for the green tissue from Green Tissue Gross Wt g other grasses in grams From the tare and gross the net weight can be calculated 51 MBRS Technical Document 20 REIS Database Design Documentation Column Type Descriptive Valid Values Index Allow Description Name name Column Nulls ongrntare decimal 9 3 Oth
37. name in the benthiclut table S benthcount integer Occurrence 0 120 No The number of occurrences for this benthic Trans on type along the transect This is an integer the data value theoretically less than or equal to 120 sheet Based on the study design there are only 120 points along each transect where the substrate will be recorded comments text Comments Yes A free form text field for any observations made for the benthic component or transect record _id integer Record ID Unique No Unique identifier for the record This is Index 1 entered automatically by the database and is used for internal operations only The user does not see this value usrid integer User ID No This field is populated automatically by the 42 MBRS Technical Document 20 REIS Database Design Documentation Column Name Type Descriptive name Valid Values Index Column Allow Nulls Description web interface with the user id The id links to the login table so the user name can be displayed This field is used to keep track of who enters the data adddate date Date No This field is populated automatically by the database program It is used to identify when the record was added 3 4 6 Benthiclut This table is a lookup table with a list of all of the possible benthic components that would be used in the point intercept data sheet It is related to the poin
38. newtarewt DOUBLE newgrosswt DOUBLE oldtarewt DOUBLE oldgrosswt DOUBLE standtarewt DOUBLE standgrosswt DOUBLE record id LONG usrid INT adddate DATE seagrassfractions fraction id INT fraction TEXT 35 usrid INT adddate DATE 55 MBRS Technical Document 20 REIS Database Design Documentation APPENDIX A DATA TYPES Type Name Description bigint signed eight byte integer 9223372036854775808 to 9223372036854775807 bigserial autoincrementing eight byte integer bit fixed length bit string bit varying n variable length bit string boolean logical Boolean true false bytea binary data character n char n fixed length character string date calendar date year month day double precision double precision floating point number 15 decimal digits integer signed four byte integer 2147483648 to 2147483647 interval p general use time span decimal p s exact numeric with selectable precision p and decimal places s real single precision floating point number 6 decimal digits smallint signed two byte integer 32768 to 32767 serial autoincrementing four byte integer text variable length character string time time of day timetz time of day including time zone timestamp date and time timestamptz date and time including time zone varchar n variable length character string 56
39. ngitude decimal 9 6 Longitude 83 25 W to No The longitude of the site in decimal degrees 89 0 W For accuracy this should be to 5 decimal places approximately 1 meter agency varchar 80 Support No The Laboratory that is conducting the Agency monitoring at this site ecosystem varchar 25 Ecosystem Coral No The ecosystem where the site is located A list Mangrove of the ecosystems are given in Manual of Seagrass Methods for Synoptic Monitoring Page 20 habitat varchar 25 Habitat shallow back No The habitat within the ecosystem that is being reef shallow monitored A listing of the habitats are given in fore reef deep Manual of Methods for Synoptic Monitoring fore reef Page 20 coastal fringing country varchar 15 Country Mexico No The country in which the sampling site is Belize located Guatemala Honduras 10 MBRS Technical Document 20 REIS Database Design Documentation Column Name Type Descriptive name Valid Values Index Column Allow Nulls Description category smallint Category 1 2 3 No The monitoring category for the site Details for the monitoring category are given in Manual of Methods for Synoptic Monitoring Page 6 description text Site Description Yes A textual description of the observations made at the site on the initial visit This can be from 1 paragraph to half a page long selection varch
40. omass table seedlingbio is based on the Seedling biomass data entry form As with the forest structure this table is related back to the transect table with transect_id It is also linked to the species table on species_id Table Name seedlingbio Column Type Descriptive Valid Values Index Allow Description Name name Column Nulls transect_id integer Transect ID No This field links these records to the transect table The values are automatically entered by the input form species_id integer Species ID 0 2999 No This is the field that contains the id number for the species and is linked to the species table oqo MBRS Technical Document 20 REIS Database Design Documentation Column Type Descriptive Valid Values Index Allow Description Name name Column Nulls sapling varchar 8 Sapling sapling Yes Indicate whether the plant being collected Seedling seedling and weighed is a seedling or sapling This may be used later to determine if there is a difference in the height to weight ratio between seedlings and saplings height decimal 9 3 Height cm No The height of the seedling sapling in centimeters from the sediment surface tare decimal 9 3 Tare Wt g No The tare weight in grams If the scale is already adjusted to compensate for tare weight enter zero in this field total decimal 9 3 Tare Wt No The total weight of the sample including Sample Wt
41. ommon links for all of these tables is the transect_id Therefore this group can be easily changed by adding additional tables or removing tables as needed In all the tables that require species name a species_id is stored in the data table that is linked to the species table identified above This provides the flexibility of easily accommodating changes to the scientific or common names if necessary in the future It also eliminates the possibility of misspelling a species name Below are discussed the tables specific for coral reefs 3 4 1 Manta Tow This data table is used for the data collected on the Manta tow spreadsheet Even though the manta tow is not done on a transect for compatibility with the other tables in the group an entry has to be made into the transect table to be able to enter data into the manta table The transect number in the transect table would be 1 Within the manta table there cannot be two entries with the same tow number for each transect Table Name manta Column Type Descriptive Valid Values Index Allow Description Name name Column Nulls transect_id integer Transect ID Unique No This field links these records to the transect Index 2 table The values are automatically entered by the input form tow smallint Tow No Unique No The number of the tow This is an integer Index 2 field 35 MBRS Technical Document 20 REIS Database Design Documentation
42. one index on multiple columns Multiple indexes on a table allow faster sorts and queries based on various parameters Creating a unique index across multiple columns will prevent entering duplicate data MBRS Technical Document 20 REIS Database Design Documentation 6 Allow creation of views on the data This allows a minimum amount of data to be stored and a virtually unlimited number of outputs to be created Views allow display of calculated values without having to create additional columns in the data tables and have them populated with the calculated values Having the additional columns can lead to conflicting data within one record of a data table In addition views allow multiple tables to be joined together to provide a customized view 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 different ways of selecting items from one or more tables in either a query or a view The inner join selects only the records that exist in both tables and matches them up The left outer join selects all of the records from the left table and only the matching records from the right table The right outer join selects all of the records from the right table and only the matching records from the left table The full outer join selects all records from both the left and right table and joins the records that match The non matching records are join
43. onship diagram for seagrasses 54 MBRS Technical Document 20 site site_id TEXT 25 location TEXT 50 latitude DOUBLE longitude DOUBLE laboratory TEXT 80 ecosystem TEXT 25 habitat TEXT 25 country TEXT 15 category INT description TEXT selection TEXT 14 usrid INT adddate DATE survey survey_id LONG site _id TEXT 25 FK startdate DATE enddate DATE starttime DATE person_col LONG FK H person_proc LONG FK pe tide TEXT 1 sea TEXT 15 wind TEXT 10 winddir INT cloud INT secchimark DOUBLE secchicoll DOUBLE depthstart DOUBLE depthend DOUBLE tempair DOUBLE currentspd DOUBLE currentdir DOUBLE survey_type TEXT usrid INT adddate DATE transect transect_id LONG survey_id LONG person_col LONG starttime DATE usrid INT adddate DATE person person_id LONG person TEXT 50 agency TEXT 120 usrid INT adddate DATE MBRS REIS Edit Date 12 19 2003 1 12 56 AM Description Seagrass data structure layout Target DB PostgreSQL Rev 1 Creator Jeffrey Dahlin Filename DesignDoc_v_5 doc Company RPI REIS Database Design Documentation seagrassbiomass re transect_id LONG FK replicate LONG diameter DOUBLE depth DOUBLE livingshoots INT fraction_id INT FK tarewt DOUBLE grosswt DOUBLE record_id LONG usrid INT adddate DATE seagrassgrowth pa transect_id LONG FK quadrat INT
44. ont Side Bus 400MHz front side bus Expansion Slots Cache 512KB L2 Advanced Transfer Cache Chipset ServerWorks GC LE chipset Memory 2GB 200MHz ECC DDR SDRAM 2 x 1 GB Memory Expandable to Total of 6 DIMM sockets on system board configurable for up to 6 GB 3 full length PCI X slots 1 X 64bit 133MHz 2 X 64bit 100MHz RAID Controller Primary Controller Dual channel integrated RAID Controller With 128MB battery backed cache 2 internal channels Embedded RAID i e ROMB RAID On Motherboard Capable of handling RAID 1 and RAID 5 Hard Drive Backplane 5 Bay Hot Plug SCSI Hard Drive Backplane for 1 x 5 configuration On Board RAID 1 RAID 5 5 drives connected to on board RAID Hard Drives 5 73GB 10 000 rpm 1 inch Ultra3 Ultra 160 Hot Plug SCSI Diskette Drive 1 44MB Diskette Drive Optical Drive DVD ROM CD ROM capable Drive Monitor 15in 13 8inch viewable Monitor Graphics Card Integrated controller w 8MB of RAM Network Adapter Intel Pro 100 Dual Port Server Adapter To allow connection to a 100Mbps port on Internet Switch With failover and load balancing support Keyboard Standard Windows PS 2 Keyboard With Keyboard Cable Mouse PS 2 two button mouse with scroll wheel and With Mouse Cable MBRS Technical Document 20 REIS Database Design Documentation Item Description SCSI Drive Controller Secondary Controller SCSI Drive Controller com
45. or the project This table is related to the columns person_co and person_proc in survey table or person_col in transect table on person_id Optionally this table could be expanded to include more information for the individual than just the name and agency 17 MBRS Technical Document 20 Table Name person REIS Database Design Documentation Column Type Descriptive Valid Values Index Allow Description Name name Column Nulls person_id integer Person ID Unique No This is an integer value assigned by the Index database to uniquely identify this record All other tables that have people s names are linked to this table on this field person varchar 50 Name of No This is the name of the individual The full Individual name of the individual is entered into this field agency varchar 120 Agency Yes The agency with which the person is affliated country varchar 10 Country Mexico Belize Yes The country the person and agency are Guatemala associated with Honduras usrid integer User ID No This field is populated automatically by the web interface with the user id The id links to the login table so the user name can be displayed This field is used to keep track of who enters the data adddate date Date No This field is populated automatically by the database program It is used to identify when the record was added 18 MBRS Technical Document 20
46. patible with Internal LTO Tape Backup Unit Plus appropriate cable s Tape Backup Unit Internal LTO Tape Backup Drive Capacity 100 GB native 200 GB compressed Media Type LTO Ultrium Recording Format LTO Ultrium Generation 1 Average Seek Access Time 71s Data Transfer Rate 15 MBps native 30 MBps compressed Interface Type Ultra wide SCSI 2 Tape Backup Software Veritas Netbackup Datacenter or equivalent Compatible with LTO drive Must be able to run on a RedHat Linux 8 0 operating system Suitable for use in a relational database environment 25 pin parallel port 9 pin serial ports Universal Serial Bus ports Power Supplies Hot pluggable redundant 500 watts power supplies 2x500watt and hot pluggable fans Voltage 100 240 VAC Chassis Tower Chassis Operating System Red Hat Linux 8 0 Professional Installed With up to date drivers for all system components including video SCSI motherboard NIC etc Operating System Documentation Set Documentation and Media for Red Hat Linux 8 0 Professional Management Software Embedded Hardware based Remote Access ERA management features including built in port to enable administrators to access diagnose and remotely manage the server Server management tools with the following features Facilitate system set up installation and configuration Complete event management including logging and filtering events E
47. person Therefore a separate table was created for each transect that serves as a link between the detailed data collected and the survey record An entry is required in this table for every site or transect that is completed The system assigns each entry in 15 MBRS Technical Document 20 REIS Database Design Documentation Even if there is only one this table a unique number called transect_id All of the datasheets link to this table on this number sampling conducted for a site and no transect or plots are used an entry is still required in this table with a transect number of 1 Table name transect Column Type Descripti Valid Values Index Allow Description Name ve name Column Nulls survey_id integer Survey Unique No This field links these records to the survey ID Index 2 table The values are automatically entered by the input form transect varchar 5 Transect Unique No The transect or plot for which the data is being or Plot Index 2 collected There may be multiple transects within one site on one day There should be a separate entry for each transect tstarttime time Start Yes The time the survey was started for the Time transect person_col integer Collector Values from No This field contains an ID number that links to or pull down list the name of the person collecting or recording Recorder the data in the field This field should be filled out for all transects depthstart decimal
48. r each species that is to be counted However in the table only the species that were sighted and counted on the transect being surveyed are entered If a species on the data sheet does not have a count associated with it for that survey and transect it is not entered into the table As with all the other tables this table relates back to the transect table on transect_id Table Name adult Column Type Descriptive Valid Values Index Allow Description Name name Column Nulls transect_id integer Transect ID Unique No This field links these records to the transect Index 2 table The values are automatically entered by the input form species_id integer Species ID 0 2999 Unique No This is the field that contains the id number for Index 2 the species and is linked to the species table cm0_5 integer 0 5 cm 0 500 Yes Count for the size range from 0 to 5 cm for the species This is an integer value and may be 38 MBRS Technical Document 20 REIS Database Design Documentation Column Name Type Descriptive name Valid Values Index Column Allow Nulls Description left blank Blanks will be considered 0 cm6_10 integer 6 10 cm 0 500 Yes Count for the size range from 6 to 10 cm for the species This is an integer value and may be left blank Blanks will be considered 0 cm11_20 integer 11 20 cm 0 500 Yes Count for the size
49. r tables in the group The transect table links the sggrowth table back to the survey table The transect number given in the transect table should be 1 None of the calculated values such as areal productivity turnover or biomass of the plants are stored in the data table These are all calculated by the database as required for view or printing reports based on the input values Table Name sggrowth Column Type Descriptiv Valid Values Index Allow Description Name e name Column Nulls transect_id integer Transect Unique No This field links these records to the transect ID Index 2 table The values are automatically entered by the input form quadrat smallint Quadrat 1 6 Unique No Numeric value for quadrat within the site Index 2 newtarewt decimal 9 3 Tare Wt Yes The tare weight for new leaves in grams New Leaves g 48 MBRS Technical Document 20 REIS Database Design Documentation Column Type Descriptiv Valid Values Index Allow Description Name e name Column Nulls newgrosswt decimal 9 3 Gross Wt Yes The gross weight for new leaves in grams New From the tare and gross the net weight can be Leaves g calculated oldtarewt decimal 9 3 Tare Wt Yes The tare weight for old leaves in grams Old Leaves 9 oldgrosswt decimal 9 3 Gross Wt Yes The gross weight for old leaves in grams Old From the tare and gross
50. range from 11 to 20 cm for the species This is an integer value and may be left blank Blanks will be considered 0 cm21_30 integer 21 30 cm 0 500 Yes Count for the size range from 21 to 30 cm for the species This is an integer value and may be left blank Blanks will be considered 0 cm31_40 integer 31 40 cm 0 500 Yes Count for the size range from 31 to 40 cm for the species This is an integer value and may be left blank Blanks will be considered 0 cm41 integer gt 40 cm 0 500 Yes Count for the size range greater than 40 cm for the species This is an integer value and may be left blank Blanks will be considered 0 record_id integer Record ID Unique Index 1 No Unique identifier for the record This is entered automatically by the database and is used for internal operations only The user does not see this value usrid integer User ID No This field is populated automatically by the web interface with the user id The id links to the login table so the user name can be displayed This field is used to keep track of who enters the data adddate date Date No This field is populated automatically by the database program It is used to identify when the record was added 39 MBRS Technical Document 20 3 4 3 Fish Recruitment REIS Database Design Documentation The fish recruitment table rec
51. re 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 as a program that will be spanning several countries and many years adhering to this standard will ensure the longevity and portability of the database In addition most database administrators are familiar with some form of SQL and relational databases so training of an administrator will be relatively easy 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 same time 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 This is especially important if users are accessing the data from the internet Being able to call and run a stored script is far easier and more efficient than trying to code all the information into a web form Allow restrictions on the data values entered in columns within a table Being able to restrict the data to certain ranges or values will reduce the possible errors in data entry Allow creation of multiple indexes on a table as well as unique indexes within a table Also must be able to create
52. reSQL 2 2 Hardware Requirements 2 2 1 General Specifications The database server will be used as a web server and database server for a regional project with its central office located in Belize A tower chassis has been chosen to accommodate an internal LTO tape drive This server will be connected via the Internet with another server located in Belmopan Belize Both servers will have the same hardware and software configuration The manufacturer of any software included with the hardware must be a reputable and globally recognized manufacturer of that class of software The Manufacturer of the proposed equipment must be a reputable and globally recognized manufacturer of microcomputer hardware Absolutely no clones will be considered The equipment vendor must be an authorized dealer of the proposed equipment and software preferably with an office in each country where equipment is to be located 2 2 2 Detailed Technical Specifications The server should meet the following detailed specification These specifications were determined based on the size of the database the number of users and the life expectancy of the project Based on monetary considerations it is not expected that the server will be replaced for at least 5 years MBRS Technical Document 20 REIS Database Design Documentation Item Description System Processors Dual Intel Xeon 2 4GHz with NetBurst Micro architecture with Hyper Threading technology Fr
53. riptive Valid Values Index Allow Description Name name Column Nulls shoot integer Shoot 1 6 Unique No The number of the shoot that is being Number Index 2 measured leaf integer Leaf 1 6 Unique No The number of the leaf on the associated Number Index 2 shoot that is being measured tip char 1 Round Tip Y N S Yes Indicate whether the tip of the leaf is rounded Y Yes N No S Si epis decimal 9 3 Length to Yes The length in centimeters from the base of the epis cm leaf to the first occurrence of epiphytes If epiphytes cover the entire leaf all the way down to the base this value would be 0 If there are no epiphytes this value would be the same as the leaf length length decimal 9 3 Length cm Yes The length of the leaf in centimeters width decimal 9 3 Width cm Yes The width of the leaf in centimeters record_id integer Record ID Unique No Unique identifier for the record This is Index 1 entered automatically by the database and is used for internal operations only The user does not see this value usrid integer User ID No This field is populated automatically by the web interface with the user id The id links to the login table so the user name can be displayed This field is used to keep track of who enters the data adddate date Date No This field is populated automatically by the database program It is used to identify when the record was added The following page has the entity relati
54. roject is to enhance protection of these valuable ecosystems Key to any protection 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 a system wide overview of the information available is essential for management of the MBRS as a whole unit Collecting all of the disparate information and compiling it into one easily accessible database is the goal of the Regional Environmental Information System REIS The design of the database is based on the information that is to be collected or has been collected in the past The data are stored in PostgreSQL 7 3 2 on a Dell Server running Red Hat Linux Version 8 2 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 to researchers 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 and expand as well as allow easy data entry and access MBRS Technical Document 20 REIS Database Design Documentation 2 SOFTWARE AND HARDWARE SPECIFICATIONS 2 1 Software Selection Following reviews of the data that would be entered into the database and the requirements of retrieving the data several criteria we
55. ruit is similar to the adult table in that only the species that have count information are entered into the database A separate record in the transect table needs to be created for each column on the datasheet After entering a transect record users only need to enter species and count information into this table This table is linked to the transect table on transect_id Table Name recruit Column Type Descriptive Valid Values Index Allow Description Name name Column Nulls transect_id integer Transect ID Unique No This field links these records to the transect Index 2 table The values are automatically entered by the input form species_id integer Species ID 0 2999 Unique No This is the field that contains the id number for Index 2 the species and is linked to the species table fishcount integer Count 0 500 No The number of fish of the given species below Trans on the maxTL found on this transect the datasheet record_id integer Record ID Unique No Unique identifier for the record This is Index 1 entered automatically by the database and is used for internal operations only The user does not see this value usrid integer User ID No This field is populated automatically by the web interface with the user id The id links to the login table so the user name can be displayed This field is used to keep track of who enters the data adddate date Date No This field is populated automatically by the
56. s started 12 MBRS Technical Document 20 REIS Database Design Documentation Column Type Descriptive Valid Index Allow Description Name name Values Column Nulls person_col integer Collector or Values No This field contains an ID number that links to Recorder from pull the name of the person collecting or recording down list the data in the field This field should be filled out for all transects person_proc integer Processor Values Yes This field contains an ID number that links to from pull the name of the person processing the data down list This field is used only with the seagrass biomass and growth transects tide varchar 7 Tide high low No Tidal stage at the time of sampling The falling values are rising high One hour either side of high water low One hour either side of low water falling The period between high and low water rising The period between low and high water sea varchar 15 Sea state calm No The sea state at the time of sampling The slight values are moderate calm Mirror like to small ripples rough slight Small waves some whitecaps moderate Moderate waves many whitecaps rough Large waves 2 3 m whitecaps everywhere some spray wind integer Wind Speed 1 5 Yes Wind strength category based on wind speed The values are 1 0 5 knots 2 6 10 knots 3 11 15 knots 4 16 20 knots
57. t record a field to identify who was doing the data entry and another field to track when the data was entered In most cases these fields are hidden from the user and are updated automatically by the system This information is accessible by the administrator for troubleshooting purposes MBRS Technical Document 20 REIS Database Design Documentation Certain conventions were followed in this report All table names are in bold All column names are in italic The following values are found in the Index Column of the tables in this report Index 1 All of the column names in a table with this value are indexed together to create the primary index on the table Index 2 All of the column names in a table with this value are indexed together to create the secondary index on the table Unique Index 1 All of the column names in a table with this value are indexed together to create a unique primary index on the table The combination of values in this index cannot be duplicated anywhere in the table Unique Index 2 All of the column names in a table with this value are indexed together to create a unique secondary index on the table The combination of values in this index cannot be duplicated anywhere in the table 3 1 Common Tables There is a group of base tables that is used throughout the database These tables are common to some or most of the groups listed 3 1 1 Site Table At the highest level is the site information
58. tallation Guides Overload Indicator and Replace Battery Indicator LED status display with load and battery bar graphs Optional Emergency Power Off EPO Optional Surge energy rating 320 joules Full time multi pole noise filtering 0 3 IEEE surge let through zero clamping response time meets UL 1449 Maintenance free battery sealed Lead Acid battery with suspended electrolyte leakproof Typical recharge time 3 hour s 2 year repair or replace warranty MBRS Technical Document 20 REIS Database Design Documentation 3 DATABASE DESIGN The first step in the database design was to analyze the data that would be collected and determine the expected uses of the data For consistency each data group is defined as a group of related data tables Data from one or more surveys may be included in a group Analysis of the datasheets and data collection methods identified several different data groups The groups are Mangrove monitoring Seagrass monitoring Coral Reef monitoring Pollution monitoring Once the groups where identified the commonalities between the datasheets within each group where identified These commonalities would be in one table to which all of the other tables in the group would be linked This would provide a connection between the various tables within a group To aid in the management of the data and tables each table would have at least one field that contained a unique identifier for tha
59. the species and is linked to the species table cbh decimal 9 3 Circumference Yes The Circumference at Breast Height CBH cm This value is the measure of the circumference of the tree in centimeters From this the diameter at breast height DBH will be calculated DBH is a common measurement used in forestry height decimal 9 3 Height cm No The height of the seedling sapling in centimeters from the sediment surface live Varchar 1 Live Y N Y S N No Indicates whether the seedling sapling is alive Input is Y S or N observation text Observations Yes General observations about the subplot or seedling 26 MBRS Technical Document 20 REIS Database Design Documentation Column Type Descriptive Valid Index Allow Description Name name Values Column Nulls record_id integer Record ID Unique No Unique identifier for the record This is Index 1 entered automatically by the database and is used for internal operations only The user does not see this value usrid integer User ID No This field is populated automatically by the web interface with the user id The id links to the login table so the user name can be displayed This field is used to keep track of who enters the data adddate date Date No This field is populated automatically by the database program It is used to identify when the record was added 3 3 3 Seedling biomass The seedling bi
60. this form is related back to the transect form with transect_id It is also linked to the species table on species_id Table Name zonation Column Type Descriptive Valid Index Allow Description Name name Values Column Nulls transect_id integer Transect ID Unique No This field links these records to the transect Index 2 table The values are automatically entered by the input form quadrant varchar 3 Quadrant 1 1 Wl IW Unique No Quadrant that the tree is located in Index 2 pointdistance decimal 9 3 Point Unique No Distance from the origin of the centerline to distance from Index 2 the point in meters shore m species_id integer Species ID 0 2999 No This is the field that contains the id number for the species and is linked to the species table distance decimal 9 3 Distance from No The distance in centimeters from the center Center Point point of the quadrant cm 392 MBRS Technical Document 20 REIS Database Design Documentation Column Type Descriptive Valid Index Allow Description Name name Values Column Nulls cbh decimal 9 3 Circumference No Circumference at Breast Height This value at Breast is the measure of the circumference of the Height CBH tree in centimeters From this the diameter cm at breast height DBH will be calculated DBH is a common measurement used in forestry height decimal 9 3 Total Height No The height of t
61. tintercept table on benthic_id Table Name benthiclut Column Name Type Descriptive Valid Values Index Allow Description name Column Nulls benthic_id integer Benthic ID Unique No This field links these records to the Index 1 pointintercept table component varchar 35 Benthic No This is the name of the substrate type Component componen_spt varchar 35 Benthic No This is the Spanish name of the substrate type Component usrid integer User ID No This field is populated automatically by the web interface with the user id The id links to the login table so the user name can be displayed This field is used to keep track of who enters the data 43 MBRS Technical Document 20 REIS Database Design Documentation adddate date Date No This field is populated automatically by the database program It is used to identify when the record was added 3 4 7 Benthic Coral This table corresponds to the date form Benthic Data Entry Form This table records the information for the various corals found along the survey transect The table design has one row per coral record similar to the data entry form In the SMP Manual the data sheet has a column for disease in which a code is entered for the disease The data table uses nine columns for this information There is a separate column for each type of disease These are Boolean fields meaning that
62. uld be Genus_name spp family varchar 25 Family Yes The family the species belongs in Super Name and subfamilies are not included at this time ordr varchar 25 Order Yes The order the species belongs in Name 19 MBRS Technical Document 20 REIS Database Design Documentation Column Type Descriptiv Valid Values Index Allow Description Name e Name Column Nulls class varchar 25 Class Yes The class the species belongs in Name element varchar 10 Biological FISH CORAL Unique No Broad taxonomic group for the species This Element TREE INVERT Index 2 field is used to categories the species in the list for purposes of data entry and data manipulations Currently the elements are FISH CORAL TREE form varchar 15 Data Form Codes for the data form in which the species is used This is mainly for fish which are used on several forms usrid integer User ID No This field is populated automatically by the web interface with the user id The id links to the login table so the user name can be displayed This field is used to keep track of who enters the data adddate date Date No This field is populated automatically by the database program It is used to identify when the record was added 3 2 2 Local Names The table localname provides the local common name for the species This table is designed to accommodate various local names based on co
63. untries or regions within a country The information in this table includes the common name what country that common name is used in and if appropriate the region of the country The local name is linked to the species table and the scientific name based on the species_id 20 MBRS Technical Document 20 Table Name localname REIS Database Design Documentation Column Type Descriptiv Valid Values Index Allow Description Name e Name Column Nulls species_id integer Species ID 1 2999 Index 2 No This is the field that contains the id number for the species and is linked to the species table name varchar 35 Common Index 2 No Local common name for the species Name country char 2 Country MX BZ GT HN Index 2 No 2 letter country code in which the local name Code is used locality varchar 40 Locality Yes The locality within a country if appropriate for which the common name is valid record_id integer Record ID Unique No Unique identifier for the record This is Index 1 entered automatically by the database and is used for internal operations only The user does not see this value usrid integer User ID No This field is populated automatically by the web interface with the user id The id links to the login table so the user name can be displayed This field is used to keep track of who enters the data adddate date Date No This field is populated automatically by the
64. usrid INT adddate DATE transect transect_id LONG survey_id LONG starttime DATE usrid INT adddate DATE person person_id LONG person TEXT 50 agency TEXT 120 usrid INT adddate DATE person_col LONG Species species_id LONG family TEXT 25 ordr TEXT 25 class TEXT 25 element TEXT 10 usrid INT adddate DATE Dee eS total_bract DOUBLE MBRS REIS Target DB PostgreSQL Rev 1 Edit Date 12 19 2003 1 07 35 AM Description Mangrove data structure layout Creator Jeffrey Dahlin Filename DesignDoc_v_5 doc Company RPI gen_spec TEXT 45 8 position_x DOUBLE REIS Database Design Documentation localname nation TEXT 1 iucn TEXT 2 country TEXT 2 ndate LONG idate LONG usrid INT adddate DATE species_id LONG FK IE name TEXT 35 country TEXT 2 locality TEXT 40 record_id LONG species_id LONG Fk IE usrid INT adddate DATE foreststructure position_y DOUBLE tree LONG species_id INT FK cbh DOUBLE proproot DOUBLE branchheight DOUBLE treeheight DOUBLE record_id LONG transect_id LONG Fk IE usrid INT adddate DATE zonation quadrant LONG bearing DOUBLE pointdistance DOUBLE distance DOUBLE cbh DOUBLE height DOUBLE observation TEXT 254 record_id LONG usrid INT adddate DATE species_id LONG FK

Database Design Documentation - Mesoamerican Barrier Reef

Contents

Download Pdf Manuals

Related Search

Related Contents