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

root/schemas/vegbien.for_ERD.sql @ 678

-            aaronmk
+-- Existing tables
-            aaronmk
+CREATE TABLE role
             aaronmk
   role_id serial NOT NULL,
   rolecode character varying(30) NOT NULL,
   roledescription character varying(200),
-            aaronmk
+  CONSTRAINT role_pkey PRIMARY KEY (role_id )
-            aaronmk
+);
 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,
-            aaronmk
+  reference_id integer,
-            aaronmk
+  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
 );
-            aaronmk
+CREATE TABLE stratummethod
+(
   stratummethod_id serial NOT NULL,
   reference_id integer,
   stratummethodname character varying(30) NOT NULL,
   stratummethoddescription text,
   stratumassignment character varying(50),
   accessioncode character varying(255),
   CONSTRAINT stratummethod_pkey PRIMARY KEY (stratummethod_id ),
   CONSTRAINT stratummethod_reference_id FOREIGN KEY (reference_id)
       REFERENCES reference (reference_id) MATCH SIMPLE
       ON UPDATE CASCADE ON DELETE CASCADE
 );
 CREATE TABLE stratumtype
+(
   stratumtype_id serial NOT NULL,
   stratummethod_id integer NOT NULL,
   stratumindex character varying(10),
   stratumname character varying(30),
   stratumdescription text,
   CONSTRAINT stratumtype_pkey PRIMARY KEY (stratumtype_id ),
   CONSTRAINT stratumtype_stratummethod_id FOREIGN KEY (stratummethod_id)
       REFERENCES stratummethod (stratummethod_id) MATCH SIMPLE
       ON UPDATE CASCADE ON DELETE CASCADE
 );
-            aaronmk
+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,
-            aaronmk
+  location_id integer,
-            aaronmk
+  project_id integer,
-            aaronmk
+  authoreventcode character varying(30),
-            aaronmk
+  "... (truncated) ..." integer,
-            aaronmk
+  accessioncode character varying(255),
-            aaronmk
+  sourceaccessioncode character varying(100),
-            aaronmk
+  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_accessioncode UNIQUE (location_id , project_id , sourceaccessioncode ),
   CONSTRAINT locationevent_keys_code UNIQUE (location_id , project_id , authoreventcode )
-            aaronmk
+);
 CREATE TABLE taxonoccurrence -- VegBank's taxonobservation table.
+(
   taxonoccurrence_id serial NOT NULL,
-            aaronmk
+  locationevent_id integer,
-            aaronmk
+  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,
-            aaronmk
+  taxonbinmethod_id integer,
-            aaronmk
+  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),
-            aaronmk
+  count integer,
-            aaronmk
+  accessioncode character varying(255),
-            aaronmk
+  sourceaccessioncode character varying(100),
-            aaronmk
+  plantobservation_id integer,
-            aaronmk
+  stratum_id integer,
-            aaronmk
+  sizeclass_id integer,
   coverindex_id integer,
-            aaronmk
+  CONSTRAINT aggregateoccurrence_pkey PRIMARY KEY (aggregateoccurrence_id ),
-            aaronmk
+  CONSTRAINT aggregateoccurrence_plantobservation_id FOREIGN KEY (plantobservation_id)
       REFERENCES plantobservation (plantobservation_id) MATCH SIMPLE
-            aaronmk
+      ON UPDATE CASCADE ON DELETE CASCADE,
-            aaronmk
+  CONSTRAINT aggregateoccurrence_sizeclass_id FOREIGN KEY (sizeclass_id)
       REFERENCES sizeclass (sizeclass_id) MATCH SIMPLE
       ON UPDATE CASCADE ON DELETE CASCADE,
-            aaronmk
+  CONSTRAINT aggregateoccurrence_stratum_id FOREIGN KEY (stratum_id)
       REFERENCES stratum (stratum_id) MATCH SIMPLE
       ON UPDATE CASCADE ON DELETE CASCADE,
-            aaronmk
+  CONSTRAINT aggregateoccurrence_taxonbinmethod_id FOREIGN KEY (taxonbinmethod_id)
       REFERENCES taxonbinmethod (taxonbinmethod_id) MATCH SIMPLE
-            aaronmk
+      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,
-            aaronmk
+  CONSTRAINT aggregateoccurrence_keys_accessioncode UNIQUE (taxonoccurrence_id , sourceaccessioncode ),
   CONSTRAINT aggregateoccurrence_keys_method UNIQUE (taxonoccurrence_id , taxonbinmethod_id )
-            aaronmk
+);
-            aaronmk
+CREATE TABLE plant -- A physical, tagged plant.
+(
   plant_id serial NOT NULL,
   CONSTRAINT plant_pkey PRIMARY KEY (plant_id )
 );
 CREATE TABLE planttag
+(
   planttag_id serial NOT NULL,
   plant_id integer NOT NULL,
   tag character varying(255) NOT NULL,
   CONSTRAINT planttag_pkey PRIMARY KEY (planttag_id ),
   CONSTRAINT planttag_plant_id FOREIGN KEY (plant_id)
       REFERENCES plant (plant_id) MATCH SIMPLE
       ON UPDATE CASCADE ON DELETE CASCADE,
   CONSTRAINT planttag_keys UNIQUE (plant_id , tag )
 );
-            aaronmk
+CREATE TABLE plantobservation -- VegBank's stemcount table.
             aaronmk
-            aaronmk
+  plantobservation_id serial NOT NULL,
-            aaronmk
+  aggregateoccurrence_id integer NOT NULL,
-            aaronmk
+  overallheight double precision,
   overallheightaccuracy double precision,
-            aaronmk
+  emb_plantobservation integer,
-            aaronmk
+  authorplantcode character varying(20),
-            aaronmk
+  accessioncode character varying(255),
   stemcount integer,
-            aaronmk
+  sourceaccessioncode character varying(100),
-            aaronmk
+  plant_id integer,
-            aaronmk
+  CONSTRAINT plantobservation_pkey PRIMARY KEY (plantobservation_id ),
   CONSTRAINT plantobservation_aggregateoccurrence_id FOREIGN KEY (aggregateoccurrence_id)
-            aaronmk
+      REFERENCES aggregateoccurrence (aggregateoccurrence_id) MATCH SIMPLE
       ON UPDATE CASCADE ON DELETE CASCADE,
-            aaronmk
+  CONSTRAINT plantobservation_plant_id FOREIGN KEY (plant_id)
       REFERENCES plant (plant_id) MATCH SIMPLE
       ON UPDATE CASCADE ON DELETE CASCADE,
-            aaronmk
+  CONSTRAINT plantobservation_aggregateoccurrence_id_1_to_1 UNIQUE (aggregateoccurrence_id )
-            aaronmk
+);
-            aaronmk
+CREATE TABLE stemobservation -- VegBank's stemlocation table.
             aaronmk
-            aaronmk
+  stemobservation_id serial NOT NULL,
-            aaronmk
+  plantobservation_id integer NOT NULL,
-            aaronmk
+  authorstemcode character varying(20),
-            aaronmk
+  xposition double precision,
   yposition double precision,
   health character varying(50),
-            aaronmk
+  emb_stemobservation integer,
-            aaronmk
+  diameter double precision,
   height double precision,
   heightaccuracy double precision,
   age double precision,
   accessioncode character varying(255),
-            aaronmk
+  diameteraccuracy double precision,
-            aaronmk
+  sourceaccessioncode character varying(100),
-            aaronmk
+  CONSTRAINT stemobservation_pkey PRIMARY KEY (stemobservation_id ),
   CONSTRAINT stemobservation_plantobservation_id FOREIGN KEY (plantobservation_id)
-            aaronmk
+      REFERENCES plantobservation (plantobservation_id) MATCH SIMPLE
-            aaronmk
+      ON UPDATE CASCADE ON DELETE CASCADE,
-            aaronmk
+  CONSTRAINT stemobservation_keys_accessioncode UNIQUE (plantobservation_id , sourceaccessioncode ),
   CONSTRAINT stemobservation_keys_code UNIQUE (plantobservation_id , authorstemcode )
-            aaronmk
+);
-            aaronmk
+CREATE TABLE specimen -- A physical specimen collected from a plant. Used to link replicates of the same specimen together.
             aaronmk
   specimen_id serial NOT NULL,
   CONSTRAINT specimen_pkey PRIMARY KEY (specimen_id )
 );
-            aaronmk
+CREATE TABLE specimenreplicate -- A herbarium's replicate of a specimen. Contains Darwin Core specimen data.
             aaronmk
-            aaronmk
+  specimenreplicate_id serial NOT NULL,
-            aaronmk
+  reference_id integer NOT NULL,
-            aaronmk
+  collectioncode_dwc character varying(255), -- The code for the collection that the specimenreplicate is from.
-            aaronmk
+  catalognumber_dwc character varying(255),
   collectiondate timestamp with time zone,
   museum_id integer,
-            aaronmk
+  sourceaccessioncode character varying(100),
-            aaronmk
+  accessioncode character varying(255),
-            aaronmk
+  taxonoccurrence_id integer NOT NULL,
-            aaronmk
+  verbatimcollectorname character varying(255),
-            aaronmk
+  collectionnumber character varying(255), -- The number of the specimenreplicate within the collection.
-            aaronmk
+  specimen_id integer,
-            aaronmk
+  CONSTRAINT specimenreplicate_pkey PRIMARY KEY (specimenreplicate_id ),
   CONSTRAINT specimenreplicate_museum_id FOREIGN KEY (museum_id)
-            aaronmk
+      REFERENCES party (party_id) MATCH SIMPLE
       ON UPDATE CASCADE ON DELETE CASCADE,
-            aaronmk
+  CONSTRAINT specimenreplicate_reference_id_fkey FOREIGN KEY (reference_id)
-            aaronmk
+      REFERENCES reference (reference_id) MATCH SIMPLE
       ON UPDATE CASCADE ON DELETE CASCADE,
-            aaronmk
+  CONSTRAINT specimenreplicate_specimen_id FOREIGN KEY (specimen_id)
       REFERENCES specimen (specimen_id) MATCH SIMPLE
       ON UPDATE CASCADE ON DELETE CASCADE,
-            aaronmk
+  CONSTRAINT specimenreplicate_taxonoccurrence_id FOREIGN KEY (taxonoccurrence_id)
-            aaronmk
+      REFERENCES taxonoccurrence (taxonoccurrence_id) MATCH SIMPLE
       ON UPDATE CASCADE ON DELETE CASCADE,
-            aaronmk
+  CONSTRAINT specimenreplicate_keys_accessioncode UNIQUE (reference_id , collectioncode_dwc , sourceaccessioncode ),
   CONSTRAINT specimenreplicate_keys_catalognumber UNIQUE (reference_id , collectioncode_dwc , catalognumber_dwc ),
-            aaronmk
+  CONSTRAINT specimenreplicate_keys_collectionnumber UNIQUE (reference_id , collectioncode_dwc , collectionnumber )
-            aaronmk
+);
-            aaronmk
+CREATE TABLE voucher
+(
   voucher_id serial NOT NULL,
-            aaronmk
+  taxonoccurrence_id integer NOT NULL,
-            aaronmk
+  specimenreplicate_id integer NOT NULL,
-            aaronmk
+  accessioncode character varying(255),
   CONSTRAINT voucher_pkey PRIMARY KEY (voucher_id ),
-            aaronmk
+  CONSTRAINT voucher_specimenreplicate_id FOREIGN KEY (specimenreplicate_id)
       REFERENCES specimenreplicate (specimenreplicate_id) MATCH SIMPLE
-            aaronmk
+      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,
-            aaronmk
+  CONSTRAINT voucher_keys UNIQUE (taxonoccurrence_id , specimenreplicate_id )
-            aaronmk
+);
-            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
+  isoriginal boolean NOT NULL DEFAULT false,
   iscurrent 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,
-            aaronmk
+  determinationdate timestamp with time zone,
-            aaronmk
+  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)
-            aaronmk
+      REFERENCES role (role_id) MATCH SIMPLE
-            aaronmk
+      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,
   stratumheight double precision,
   stratumbase double precision,
   stratumcover double precision,
-            aaronmk
+  area double precision,
-            aaronmk
+  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,
   CONSTRAINT stratum_stratumtype_id FOREIGN KEY (stratumtype_id)
       REFERENCES stratumtype (stratumtype_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
+);
-            aaronmk
+CREATE TABLE taxonbinmethod
             aaronmk
-            aaronmk
+  taxonbinmethod_id integer NOT NULL,
-            aaronmk
+  label character varying(255),
   stratumtype_id integer,
-            aaronmk
+  sizeclass_id integer,
   coverindex_id integer,
   accessioncode character varying(255),
-            aaronmk
+  CONSTRAINT taxonbinmethod_pkey PRIMARY KEY (taxonbinmethod_id ),
   CONSTRAINT taxonbinmethod_sizeclass_id FOREIGN KEY (sizeclass_id)
-            aaronmk
+      REFERENCES sizeclass (sizeclass_id) MATCH SIMPLE
-            aaronmk
+      ON UPDATE CASCADE ON DELETE CASCADE,
-            aaronmk
+  CONSTRAINT taxonbinmethod_stratumtype_id FOREIGN KEY (stratumtype_id)
       REFERENCES stratumtype (stratumtype_id) MATCH SIMPLE
       ON UPDATE CASCADE ON DELETE CASCADE
-            aaronmk
+);

Project

General

Profile