Biotics 4.0 DATA MODEL
VERSION 2.0
January 14, 2008
Biotics 4.0 DATA MODEL
VERSION 2.0
January 14, 2008
Revision
History
|
Date |
Description / Changes |
Author(s) |
|
8/28/01 |
First version |
Carol Fogelsong |
|
9/27/01 |
New
diagrams for the following subject areas: Source Feature, |
Carol Fogelsong |
|
12/10/01 |
New diagrams for the following subject areas: Element Groups, Scientific Name, Sites, Managed Areas, Eos, Community Eos, Element Management, Source Features, References, EO Specs, EO Rank Specs, Element: Global, Element: National and Subnational, Element/Site |
Carol Fogelsong |
|
12/10/01 |
Broke Element Ranking subject area into three different diagrams for better readability. New subject areas are: Element Ranking: Global, Element Ranking: National, and Element Ranking Subnational |
Carol Fogelsong |
|
04/02/02 |
Refreshed document with new diagrams |
Carol Fogelsong |
|
04/15/02 |
Updated TOC to include inadvertently deleted Element Reference subject area |
Carol Fogelsong |
|
08/01/02 |
Updated
|
Carol Fogelsong |
|
12/20/02 |
Updated to show new application name and logo. Updated Introduction. Added subject areas for System tables, Security, and Working Lists. Added hyperlinks for each diagram to go back to TOC. |
Carol Fogelsong |
|
12/30/04 |
Incorporated many schema changes made over the last two years. All diagrams were replaced with updated ones. Added high level overview diagram. Also, colors of many tables were changed so that they wouldn’t be so dark when printed out. |
Carol Fogelsong |
|
01/14/2008 |
Incorporated schema changes made for Biotics 4.1 release. Subject areas added/updated: Managed Areas, Community Characterization: Local, Element: National and Subnational, Element Occurrence: Details, Element Occurrence: High Level, Element: Other Identifiers, Element and Scientific Name, Source Feature |
Carol Fogelsong |
Animal
Characterization Abstract: Global
Animal
Characterization Abstract: National
Animal
Characterization Abstract: Subnational
Community
Characterization Abstract: Global
Community
Characterization Abstract: Local
Community
Characterization Abstract: Local/Geog Area
Community
Characterization Abstract: National
Community
Characterization Abstract: Subnational
Element
Occurrences: High Level
Element
Occurrences: Communities
Element:
National and Subnational
Plant
Characterization: Global
Plant
Characterization: National
Plant
Characterization: Subnational
This document contains the entity relationship diagrams for Version 2.0 of the Biotics 4.1 Physical Data Model (PDM). This model represents the Biotics 4.0 business tables as they are physically implemented. Also included are tables for areas such as security, working lists, and audit tracking. When changes are made to the application that affects the database schema, this document will be updated accordingly.
As of the publication date of this document, all functionality to be contained in Biotics 4.1 production version 2.0 has undergone extensive analysis, design, and user review. This model is a product of that effort. Biotics 4.0 was implemented at NatureServe Central offices on November 4, 2002. Installation of Biotics at network member programs began at pilot sites shortly thereafter. As of January 14, 2008, xx out of xx member programs currently use Biotics. In addition, Biotics is now in use in many Canadian and Latin American programs.
The Biotics 4.0 PDM represents the project mission to port
the BCD file structure used in the Central,
Because of the size of the Biotics 4.1 PDM, it was necessary to divide the model into separate subject areas. They do not correspond exactly to Biotics 4.1 application windows, but instead, show logical areas of focus. It should be noted that the tables are intricately related in many ways that do not always show as relationships on the diagrams.
One of the first tasks in creating the model was the formulation of naming and modeling standards. These standards were deemed necessary in order to result in a model that is consistent, clear, and easy for users to understand. One standard is the use of the suffix ‘D_’ to denote domain tables. These tables are those that contain the valid values for the drop-down lists used in the Biotics 4.0 application. An example of this is D_CONTACT_CATEGORY. This table contains the values that are valid for column contact_category_cd (currently P and O). The column contact_category_desc contains the description of each code value (Person and Organization, respectively). The table CONTACT has a relationship with a value in this domain table, the foreign key d_contact_category_id. Because of the large number of domain tables used in the PDM, it was not possible to include a diagram listing all of them. Instead, they are listed in an accompanying document, the Biotics 4.1 Physical Data Base Data Dictionary. This document contains a listing of all tables, their columns and their physical properties.
The Biotics 4.1 PDM also contains many views created to provide easier querying as well as to accommodate the integration of Biotics Mapper functionality. These views are designated in two ways. One is to show the table in dashed lines as opposed to solid lines. This is shown in the example below:
Another way is by naming convention. Most views in this model will have the _VW suffixed to their names. Views created for Biotics Mapper, however, will not follow this convention.
This document is formatted to print on 11 x 17 paper, which is required in order for the diagrams to be legible. Use of smaller paper may result in some of the diagrams being truncated. If there are difficulties in printing this document, the reader may wish to refer to the Biotics 4.1 PDM Data Dictionary. This document contains the same information, but in a tabular report.
Please direct any questions or comments regarding the Biotics 4.1 PDM to Carol Fogelsong. She can be reached at (703) 908-1864 or carol_fogelsong@natureserve.org.
There are three types of relationships shown in the Biotics PDM; identifying, non-identifying, and subtype relationships. Identifying relationships are shown with an unbroken line between the parent and the child tables, as shown in the example below:
Identifying relationship:
This type of relationship means that the child table,
ALLIANCE_ECOREGION is fully dependent on the parent table,
A non-identifying relationship is shown with a dashed line, as in the example below:
Non-identifying relationship:
A nonidentifying relationship means that the child table, ALLIANCE_USFS_ECOREGION, has its own primary key. The primary key of the parent table, ALLIANCE_ECOREGION is migrated as a non-identifying foreign key.
A subtype relationship is one where the parent table will have a relationship with one, and only one of its children. It is shown with a branched line and zero or one cardinality. In the example below, the parent table, HIGHER_CLASS_UNIT, will have a relationship with either HIGHER_TAXON, or HIGHER_COMMUNITY, meaning that an occurrence of a higher class unit is either a higher taxon unit or a higher community unit. Another example of a subtype relationship can be found in the Element Global subject area. An ELEMENT_GLOBAL will be related to either a TAXON_GLOBAL or a COMMUNITY_GLOBAL, but not both. The supertype table; ELEMENT_GLOBAL or HIGHER_CLASS_UNIT, contains information that is common to both types of subtypes. The subtype tables; TAXON_GLOBAL, COMMUNITY_GLOBAL, HIGHER_TAXON, and HIGHER_COMMUNITY, contain information that is special to those types of occurrences.
Subtype relationship:
Relationship cardinality represents the fact that each parent entity or table within a relationship is connected to a particular number of instances of the child entity or table. It tells us how many instances of a related table must and/or can exist in relationship to another table. In the Biotics PDM, there are several cardinalities represented: zero, one, or many; zero or one; one to many; and one to one.
A zero, one, or many relationship means that every parent table can have zero, one, or many occurrences of the child table. This type of relationship is shown in the example below:
The right side of the relationship shows the zero, one, or many symbol. This diagram tells us that for each occurrence of an ELEMENT_GLOBAL, there can be zero, one or many occurrences of an OTHER_GLOBAL_COMMON_NAME. The left side of the relationship shows a one to one relationship. For each occurrence of an OTHER_GLOBAL_COMMON_NAME, there can be only one occurrence of an ELEMENT_GLOBAL.
A zero or one relationship means that every parent table may have zero or one occurrence of the child table. An example of this type of relationship is shown below:
This diagram tells us that TAXON_GLOBAL may be related to zero or one occurrence of ANIMAL_CAG. It cannot have more than once occurrence, however. An occurrence of ANIMAL_CAG can only be related to one and only one TAXON_GLOBAL. This diagram also shows an example of a recursive relationship, which is a relationship that an occurrence can have with another occurrence in the same table. A TAXON_GLOBAL can be related to zero, one, or many other TAXON_GLOBALs in a subspecies/variety type of relationship. However, a TAXON_GLOBAL can be related to zero or one other TAXON_GLOBAL in a parent species relationship.
A one to many relationship means that the parent table must have one occurrences of the child table, but can also have more than one occurrence. An example is shown below:
This diagram tells us that an occurrence of EO_RANK_SPECS will have at least one occurrence of EO_RANK_SPECS_DETAIL, but can also have more than on occurrence. Each occurrence of EO_RANK_SPECS_DETAIL will have one and only one EO_RANK_SPECS.
In addition, there are several symbols associated with columns. The key symbol denotes that the column is the primary key for the table. The primary key is also shown at the top of the table, above the dividing line. The (FK) designation means that the column is a foreign key that has migrated from another table. The (AK) designation indicates that the column is part of an alternate key for the table. This means that the value for this column, possibly in conjunction with other columns, must be unique within the table. The numbers after the designation tell which key the column is in and which part it is. In the example above, EO_RANK_SPECS_DETAIL has one AK that has two parts. The columns eo_rank_specs_id and d_location_use_class_id comprise a two-part alternate key for this table, meaning that each combination of values for these columns can only occur once in the table. For an example of a table with multiple alternate keys, each with several parts, see the ELEMENT_GLOBAL table.
The following table shows some of the more commonly used abbreviations:
Abbreviation |
Meaning |
|
Classif |
Classification |
|
Com |
Comment |
|
Comm |
Community |
|
Cons |