/schemas/vegbien.for_ERD.sql - Annotate - BIEN 3 - NCEAS Projects

root/schemas/vegbien.for_ERD.sql @ 582

-            aaronmk
+-- Existing tables
 CREATE TABLE aux_role
+(
   role_id serial NOT NULL,
   rolecode character varying(30) NOT NULL,
   roledescription character varying(200),
   CONSTRAINT aux_role_pkey PRIMARY KEY (role_id )
 );
 CREATE TABLE reference
+(
   reference_id serial NOT NULL,
   shortname character varying(250),
   fulltext text,
   referencetype character varying(250)
 );
 CREATE TABLE party
+(
   party_id serial NOT NULL,
   salutation character varying(20),
   givenname character varying(50),
   middlename character varying(50),
   surname character varying(50),
   organizationname character varying(100)
 );
 CREATE TABLE plantname
+(
   plantname_id serial NOT NULL,
   plantname character varying(255) NOT NULL,
   reference_id integer,
   dateentered timestamp with time zone DEFAULT now(),
   CONSTRAINT plantname_pkey PRIMARY KEY (plantname_id ),
   CONSTRAINT plantname_reference_id FOREIGN KEY (reference_id)
       REFERENCES reference (reference_id) MATCH SIMPLE
       ON UPDATE CASCADE ON DELETE CASCADE
 );
 CREATE TABLE plantconcept
+(
   plantconcept_id serial NOT NULL,
   plantname_id integer NOT NULL,
   reference_id integer NOT NULL,
   plantname character varying(200),
   plantcode character varying(23),
   plantdescription text,
   d_obscount integer,
   d_currentaccepted boolean,
   accessioncode character varying(255),
   CONSTRAINT plantconcept_pkey PRIMARY KEY (plantconcept_id ),
   CONSTRAINT plantconcept_plantname_id FOREIGN KEY (plantname_id)
       REFERENCES plantname (plantname_id) MATCH SIMPLE
       ON UPDATE CASCADE ON DELETE CASCADE,
   CONSTRAINT plantconcept_reference_id FOREIGN KEY (reference_id)
       REFERENCES reference (reference_id) MATCH SIMPLE
       ON UPDATE CASCADE ON DELETE CASCADE
 );
 CREATE TABLE namedplace
+(
   namedplace_id serial NOT NULL,
   placesystem character varying(50),
   placename character varying(100) NOT NULL,
   placedescription text,
   placecode character varying(15),
   owner character varying(100),
   reference_id integer,
   d_obscount integer,
   accessioncode character varying(255),
   CONSTRAINT namedplace_pkey PRIMARY KEY (namedplace_id ),
   CONSTRAINT namedplace_reference_id FOREIGN KEY (reference_id)
       REFERENCES reference (reference_id) MATCH SIMPLE
       ON UPDATE CASCADE ON DELETE CASCADE,
   CONSTRAINT namedplace_keys UNIQUE (placesystem , placename )
 );
-            aaronmk
+CREATE TABLE locationplace
             aaronmk
   locationplace_id serial NOT NULL,
   location_id integer NOT NULL,
   calculated boolean,
   namedplace_id integer NOT NULL,
-            aaronmk
+  CONSTRAINT locationplace_pkey PRIMARY KEY (locationplace_id ),
   CONSTRAINT locationplace_location_id FOREIGN KEY (location_id)
-            aaronmk
+      REFERENCES location (location_id) MATCH SIMPLE
       ON UPDATE CASCADE ON DELETE CASCADE,
-            aaronmk
+  CONSTRAINT locationplace_namedplace_id FOREIGN KEY (namedplace_id)
-            aaronmk
+      REFERENCES namedplace (namedplace_id) MATCH SIMPLE
       ON UPDATE CASCADE ON DELETE CASCADE,
-            aaronmk
+  CONSTRAINT locationplace_keys UNIQUE (location_id , namedplace_id )
-            aaronmk
+);
-            aaronmk
+CREATE TABLE project
+(
   project_id serial NOT NULL,
   projectname character varying(150) NOT NULL,
   projectdescription text,
   startdate timestamp with time zone,
   stopdate timestamp with time zone,
   d_obscount integer,
   d_lastlocationaddeddate timestamp with time zone,
   accessioncode character varying(255),
   CONSTRAINT project_pkey PRIMARY KEY (project_id ),
   CONSTRAINT project_keys UNIQUE (projectname , startdate , stopdate )
 );
 -- New tables
-            aaronmk
+CREATE TABLE location
+(
   location_id serial NOT NULL,
-            aaronmk
+  authorlocationcode character varying(30),
-            aaronmk
+  reference_id integer,
   parent_id integer,
-            aaronmk
+  reallatitude double precision,
   reallongitude double precision,
-            aaronmk
+  locationaccuracy double precision,
-            aaronmk
+  confidentialitystatus integer NOT NULL DEFAULT 0,
-            aaronmk
+  confidentialityreason character varying(200),
-            aaronmk
+  publiclatitude double precision,
   publiclongitude double precision,
-            aaronmk
+  "... (truncated) ..." integer,
-            aaronmk
+  accessioncode character varying(255),
   sublocationxposition double precision,
   sublocationyposition double precision,
   namedplace_id integer,
   CONSTRAINT location_pkey PRIMARY KEY (location_id ),
   CONSTRAINT location_namedplace_id FOREIGN KEY (namedplace_id)
       REFERENCES namedplace (namedplace_id) MATCH SIMPLE
       ON UPDATE CASCADE ON DELETE CASCADE,
   CONSTRAINT location_parent_id FOREIGN KEY (parent_id)
       REFERENCES location (location_id) MATCH SIMPLE
       ON UPDATE CASCADE ON DELETE CASCADE,
   CONSTRAINT location_reference_id FOREIGN KEY (reference_id)
       REFERENCES reference (reference_id) MATCH SIMPLE
       ON UPDATE CASCADE ON DELETE CASCADE,
-            aaronmk
+  CONSTRAINT location_keys_code UNIQUE (reference_id , authorlocationcode ),
   CONSTRAINT location_keys_coords UNIQUE (reference_id , reallatitude , reallongitude ),
   CONSTRAINT location_keys_subplot_code UNIQUE (parent_id , authorlocationcode ),
   CONSTRAINT location_keys_subplot_coords UNIQUE (parent_id , sublocationxposition , sublocationyposition )
-            aaronmk
+);
 CREATE TABLE locationevent -- VegBank's observation table.
+(
   locationevent_id serial NOT NULL,
   previous_id integer,
   location_id integer NOT NULL,
   project_id integer,
-            aaronmk
+  authoreventcode character varying(30),
-            aaronmk
+  "... (truncated) ..." integer,
-            aaronmk
+  accessioncode character varying(255),
   CONSTRAINT locationevent_pkey PRIMARY KEY (locationevent_id ),
   CONSTRAINT locationevent_location_id FOREIGN KEY (location_id)
       REFERENCES location (location_id) MATCH SIMPLE
       ON UPDATE CASCADE ON DELETE CASCADE,
   CONSTRAINT locationevent_project_id FOREIGN KEY (project_id)
       REFERENCES project (project_id) MATCH SIMPLE
       ON UPDATE CASCADE ON DELETE CASCADE,
-            aaronmk
+  CONSTRAINT locationevent_keys UNIQUE (location_id , project_id , authoreventcode )
-            aaronmk
+);
 CREATE TABLE taxonoccurrence -- VegBank's taxonobservation table.
+(
   taxonoccurrence_id serial NOT NULL,
   locationevent_id integer NOT NULL,
   authorplantname character varying(255),
   reference_id integer,
   taxoninferencearea double precision,
   emb_taxonoccurrence integer,
-            aaronmk
+  "... (truncated) ..." integer,
-            aaronmk
+  accessioncode character varying(255),
   CONSTRAINT taxonoccurrence_pkey PRIMARY KEY (taxonoccurrence_id ),
   CONSTRAINT taxonoccurrence_locationevent_id FOREIGN KEY (locationevent_id)
       REFERENCES locationevent (locationevent_id) MATCH SIMPLE
       ON UPDATE CASCADE ON DELETE CASCADE,
   CONSTRAINT taxonoccurrence_reference_id FOREIGN KEY (reference_id)
       REFERENCES reference (reference_id) MATCH SIMPLE
       ON UPDATE CASCADE ON DELETE CASCADE
 );
 CREATE TABLE aggregateoccurrence -- VegBank's taxonimportance table.
+(
   aggregateoccurrence_id serial NOT NULL,
   taxonoccurrence_id integer NOT NULL,
   taxonbin_id integer,
   cover double precision,
   basalarea double precision,
   biomass double precision,
   inferencearea double precision,
   stratumbase double precision,
   stratumheight double precision,
   emb_aggregateoccurrence integer,
   covercode character varying(10),
   count integer NOT NULL,
   accessioncode character varying(255),
   CONSTRAINT aggregateoccurrence_pkey PRIMARY KEY (aggregateoccurrence_id ),
   CONSTRAINT aggregateoccurrence_taxonbin_id FOREIGN KEY (taxonbin_id)
       REFERENCES taxonbin (taxonbin_id) MATCH SIMPLE
       ON UPDATE CASCADE ON DELETE CASCADE,
   CONSTRAINT aggregateoccurrence_taxonoccurrence_id FOREIGN KEY (taxonoccurrence_id)
       REFERENCES taxonoccurrence (taxonoccurrence_id) MATCH SIMPLE
       ON UPDATE CASCADE ON DELETE CASCADE,
   CONSTRAINT aggregateoccurrence_keys UNIQUE (taxonoccurrence_id , taxonbin_id )
 );
 CREATE TABLE individualplant -- VegBank's stemcount table.
+(
   individualplant_id serial NOT NULL,
   aggregateoccurrence_id integer NOT NULL,
-            aaronmk
+  overallheight double precision,
   overallheightaccuracy double precision,
-            aaronmk
+  emb_individualplant integer,
-            aaronmk
+  authorplantcode character varying(20),
-            aaronmk
+  accessioncode character varying(255),
   stemcount integer,
   CONSTRAINT individualplant_pkey PRIMARY KEY (individualplant_id ),
   CONSTRAINT individualplant_aggregateoccurrence_id FOREIGN KEY (aggregateoccurrence_id)
       REFERENCES aggregateoccurrence (aggregateoccurrence_id) MATCH SIMPLE
       ON UPDATE CASCADE ON DELETE CASCADE,
-            aaronmk
+  CONSTRAINT individualplant_keys UNIQUE (aggregateoccurrence_id , authorplantcode )
-            aaronmk
+);
 CREATE TABLE stem -- VegBank's stemlocation table.
+(
   stem_id serial NOT NULL,
   individualplant_id integer NOT NULL,
-            aaronmk
+  authorstemcode character varying(20),
-            aaronmk
+  xposition double precision,
   yposition double precision,
   health character varying(50),
   emb_stem integer,
   diameter double precision,
   height double precision,
   heightaccuracy double precision,
   age double precision,
   accessioncode character varying(255),
-            aaronmk
+  diameteraccuracy double precision,
-            aaronmk
+  CONSTRAINT stem_pkey PRIMARY KEY (stem_id ),
   CONSTRAINT stem_individualplant_id FOREIGN KEY (individualplant_id)
       REFERENCES individualplant (individualplant_id) MATCH SIMPLE
       ON UPDATE CASCADE ON DELETE CASCADE,
-            aaronmk
+  CONSTRAINT stem_keys UNIQUE (individualplant_id , authorstemcode )
-            aaronmk
+);
-            aaronmk
+CREATE TABLE specimen -- A herbarium specimen. Contains Darwin Core specimen data.
+(
   specimen_id serial NOT NULL,
   reference_id integer NOT NULL,
   collectioncode_dwc character varying(255),
   catalognumber_dwc character varying(255),
   collectiondate timestamp with time zone,
   collector_id integer,
   museum_id integer,
   sourceaccessionnumber character varying(100),
   accessioncode character varying(255),
-            aaronmk
+  taxonoccurrence_id integer NOT NULL,
-            aaronmk
+  collectornumber_dwc character varying(255),
-            aaronmk
+  CONSTRAINT specimen_pkey PRIMARY KEY (specimen_id ),
   CONSTRAINT specimen_collector_id FOREIGN KEY (collector_id)
       REFERENCES party (party_id) MATCH SIMPLE
       ON UPDATE CASCADE ON DELETE CASCADE,
   CONSTRAINT specimen_museum_id FOREIGN KEY (museum_id)
       REFERENCES party (party_id) MATCH SIMPLE
       ON UPDATE CASCADE ON DELETE CASCADE,
   CONSTRAINT specimen_reference_id_fkey FOREIGN KEY (reference_id)
       REFERENCES reference (reference_id) MATCH SIMPLE
       ON UPDATE CASCADE ON DELETE CASCADE,
   CONSTRAINT specimen_taxonoccurrence_id FOREIGN KEY (taxonoccurrence_id)
       REFERENCES taxonoccurrence (taxonoccurrence_id) MATCH SIMPLE
       ON UPDATE CASCADE ON DELETE CASCADE,
   CONSTRAINT specimen_keys UNIQUE (reference_id , collectioncode_dwc , catalognumber_dwc )
 );
-            aaronmk
+CREATE TABLE voucher
+(
   voucher_id serial NOT NULL,
-            aaronmk
+  taxonoccurrence_id integer NOT NULL,
   specimen_id integer NOT NULL,
-            aaronmk
+  accessioncode character varying(255),
   CONSTRAINT voucher_pkey PRIMARY KEY (voucher_id ),
   CONSTRAINT voucher_specimen_id FOREIGN KEY (specimen_id)
       REFERENCES specimen (specimen_id) MATCH SIMPLE
       ON UPDATE CASCADE ON DELETE CASCADE,
   CONSTRAINT voucher_taxonoccurrence_id FOREIGN KEY (taxonoccurrence_id)
       REFERENCES taxonoccurrence (taxonoccurrence_id) MATCH SIMPLE
       ON UPDATE CASCADE ON DELETE CASCADE,
   CONSTRAINT voucher_keys UNIQUE (taxonoccurrence_id , specimen_id )
 );
-            aaronmk
+CREATE TABLE taxondetermination -- VegBank's taxoninterpretation table.
+(
   taxondetermination_id serial NOT NULL,
   taxonoccurrence_id integer NOT NULL,
   plantconcept_id integer NOT NULL,
-            aaronmk
+  party_id integer,
-            aaronmk
+  role_id integer NOT NULL,
   determinationtype character varying(30),
   reference_id integer,
-            aaronmk
+  originaldetermination boolean NOT NULL DEFAULT false,
   currentdetermination boolean NOT NULL DEFAULT false,
-            aaronmk
+  taxonfit character varying(50),
   taxonconfidence character varying(50),
   grouptype character varying(20),
   notes text,
   notespublic boolean,
   notesmgt boolean,
   revisions boolean,
   determinationdate timestamp with time zone NOT NULL,
   emb_taxondetermination integer,
   accessioncode character varying(255),
   CONSTRAINT taxondetermination_pkey PRIMARY KEY (taxondetermination_id ),
   CONSTRAINT taxondetermination_party_id FOREIGN KEY (party_id)
       REFERENCES party (party_id) MATCH SIMPLE
       ON UPDATE CASCADE ON DELETE CASCADE,
   CONSTRAINT taxondetermination_plantconcept_id FOREIGN KEY (plantconcept_id)
       REFERENCES plantconcept (plantconcept_id) MATCH SIMPLE
       ON UPDATE CASCADE ON DELETE CASCADE,
   CONSTRAINT taxondetermination_reference_id FOREIGN KEY (reference_id)
       REFERENCES reference (reference_id) MATCH SIMPLE
       ON UPDATE CASCADE ON DELETE CASCADE,
   CONSTRAINT taxondetermination_role_id FOREIGN KEY (role_id)
       REFERENCES aux_role (role_id) MATCH SIMPLE
       ON UPDATE CASCADE ON DELETE CASCADE,
   CONSTRAINT taxondetermination_taxonoccurrence_id FOREIGN KEY (taxonoccurrence_id)
       REFERENCES taxonoccurrence (taxonoccurrence_id) MATCH SIMPLE
       ON UPDATE CASCADE ON DELETE CASCADE
 );
-            aaronmk
+CREATE TABLE stratum
+(
   stratum_id serial NOT NULL,
   locationevent_id integer NOT NULL,
   stratumtype_id integer NOT NULL,
   stratummethod_id integer,
   stratumname character varying(30),
   stratumheight double precision,
   stratumbase double precision,
   stratumcover double precision,
   stratumdescription character varying(200),
   CONSTRAINT stratum_pkey PRIMARY KEY (stratum_id ),
   CONSTRAINT stratum_locationevent_id FOREIGN KEY (locationevent_id)
       REFERENCES locationevent (locationevent_id) MATCH SIMPLE
-            aaronmk
+      ON UPDATE CASCADE ON DELETE CASCADE
-            aaronmk
+);
-            aaronmk
+CREATE TABLE sizeclass -- A range of size measurements used to aggregate organisms.
             aaronmk
-            aaronmk
+  sizeclass_id serial NOT NULL,
   mindiameter double precision,
   minheight double precision,
   maxdiameter double precision,
   maxheight double precision,
-            aaronmk
+  accessioncode character varying(255),
-            aaronmk
+  CONSTRAINT sizeclass_pkey PRIMARY KEY (sizeclass_id )
-            aaronmk
+);
 CREATE TABLE taxonbin -- Defines a set of aggregate measurements. Used by aggregateoccurrence to define how measurements were aggregated.
+(
   taxonbin_id integer NOT NULL,
   label character varying(255) NOT NULL,
   stratum_id integer,
   sizeclass_id integer,
   coverindex_id integer,
   accessioncode character varying(255),
-            aaronmk
+  count integer NOT NULL,
-            aaronmk
+  CONSTRAINT taxonbin_pkey PRIMARY KEY (taxonbin_id ),
   CONSTRAINT taxonbin_sizeclass_id FOREIGN KEY (sizeclass_id)
       REFERENCES sizeclass (sizeclass_id) MATCH SIMPLE
       ON UPDATE CASCADE ON DELETE CASCADE
 );

Project

General

Profile