/* SQL DDL: Create an implementation of the model using the SQL Data Definition Language */
/*  Note: Comments such as this may not be supported by your system and may have to be removed */
/*  You may want to change some field types to improve the implementation on your DBMS. */
/*  Data type 'National Character' etc. indicates Unicode text./*   PostgreSQL: You should create the entire database as Unicode!  */
/*   PostgreSQL: For Postgres you should create the entire database as Unicode! The 'National' has been removed from the SQL code */
/*   PostgreSQL: the data type CHARACTER VARYING has been replaced with VARCHAR */
/*   PostgreSQL: the data type DATETIME has been replaced with TIMESTAMP */
/*   PostgreSQL: Table and field names have been quoted (= "Fieldname") to allow case-sensitivity */
/*   PostgreSQL: the default values for boolean fields have been replaced with 'true' and 'false' */



/*=== Table: "Exsiccata" ===*/
/* An Exsiccata is a series of specimen issued in a publication like form. (Note: the examples within this entity are purposely denormalized!). */
  /*   ExsiccataID: ID of the exsiccata (primary key). */
  /*   ExsAbbreviation: Std. abbreviation (authors/editors, std. abbrev. of title). */
  /*   ExsTitle: Exact title (create a new entry if title spelling changed considerably, link entries using PrecededBy/SupersededBy). */
  /*   EditingInstitution: Issuing/Eding institution (If no editors are known, at least this must be entered). */
  /*   EditingLocationOri: Original spelling of city/country where the exsiccata has been issued. */
  /*   EditingLocationEnglish: Current English spelling of city/country where the exsiccata has been issued. */
  /*   PrecededBy: An earlier exsiccata publication appeared under different title or editors (Reference to entry in this database). */
  /*   SupersededBy: A later exsiccata publication appeared under different title or editors (Reference to entry in this database). */
  /*   ExsNumberFirst: If known: number of the first exs. that was issued by the current editors under the current title. */
  /*   ExsNumberLast: If known: number of the last exs. that was issued by the current editors under the current title. */
  /*   ExsPublYearFirst: If known: publication year of the first exs. that was issued by the current editors under the current title. */
  /*   ExsPublYearLast: If known: publication year of the last exs. that was issued by the current editors under the current title. */
  /*   Notes: Annotation, please add your name and a date for each annotation. */
  /*   Problems: Please explain here if you have a problem, this field will later be checked. */
  /*   Fungi: check if exsiccatum covers fungal species (default) 1 or -1= Yes it does, 0=No it does not, Null/empty = don't know. */
  /*   Algae: check if exsiccatum covers Algae/Cyanophyta. */
  /*   Mosses: check if exsiccatum covers mosses/bryophytes. */
  /*   Pteridophytes: check if exsiccatum covers pteridophytes. */
  /*   HigherPlants: check if exsiccatum covers higher plants. */
  /*   InPfister85: Exsiccate is included in publication by Pfister 1985. */
  /*   InSayre69: Exsiccate is included in publication by Sayre 1969. */
  /*   InSayre71: Exsiccate is included in publication by Sayre 1971. */
  /*   InSayre75: Exsiccate is included in publication by Sayre 1975. */
  /*   Educational: Educational purpose of the exsiccata, 1 or -1= Yes, 0=No, Null/empty = don't know. */
  /*   Scientific: Scientific purpose of the exsiccata, 1 or -1= Yes, 0=No, Null/empty = don't know. */
  /*   GroupSpecific: Exsiccata is restricted to a taxonomic entity or ecologically delimited group, e.g. family or lichens on leaves, 1 or -1= Yes, 0=No, Null/empty = don't know. */
  /*   LichenSpecific: check if exsiccatum covers Lichens. */
CREATE TABLE "Exsiccata" (
    "ExsiccataID" 	int4	 NOT NULL PRIMARY KEY DEFAULT =CLng(1+Rnd()*999999998),
    "ExsAbbreviation" 	varchar(255)	 NOT NULL,
    "ExsTitle" 	varchar(255)	 NOT NULL,
    "EditingInstitution" 	varchar(255)	 NULL,
    "EditingLocationOri" 	varchar(255)	 NULL,
    "EditingLocationEnglish" 	varchar(255)	 NULL,
    "PrecededBy" 	int4	 NULL,
    "SupersededBy" 	int4	 NULL,
    "ExsNumberFirst" 	varchar(20)	 NULL,
    "ExsNumberLast" 	varchar(20)	 NULL,
    "ExsPublYearFirst" 	varchar(20)	 NULL,
    "ExsPublYearLast" 	varchar(80)	 NULL,
    "Notes" 	text	 NULL,
    "Problems" 	varchar(255)	 NULL,
    "Fungi" 	int2	 NULL DEFAULT Null,
    "Algae" 	int2	 NULL DEFAULT Null,
    "Mosses" 	int2	 NULL DEFAULT Null,
    "Pteridophytes" 	int2	 NULL DEFAULT Null,
    "HigherPlants" 	int2	 NULL DEFAULT Null,
    "InPfister85" 	int2	 NULL DEFAULT Null,
    "InSayre69" 	int2	 NULL DEFAULT Null,
    "InSayre71" 	int2	 NULL DEFAULT Null,
    "InSayre75" 	int2	 NULL DEFAULT Null,
    "Educational" 	int2	 NULL DEFAULT 0,
    "Scientific" 	int2	 NULL DEFAULT 0,
    "GroupSpecific" 	int2	 NULL DEFAULT 0,
    "LichenSpecific" 	bool	 NULL
);




/*=== Table: "ExsiccataEditor" ===*/
/* An editor that published an Exsiccata. */
  /*   Name: Enter as last name, comma, first names, use blanks after comma and period. */
  /*   ExsiccataID: Reference to ExsiccataID in Exsiccata (foreign Key). */
  /*   Sequence: (automatically set during entry, to maintain the sequence of editors). */
CREATE TABLE "ExsiccataEditor" (
    "Name" 	varchar(255)	 NOT NULL,
    "ExsiccataID" 	int4	 NOT NULL,
    "Sequence" 	timestamp	 NOT NULL DEFAULT =Now(),
  PRIMARY KEY ("Name","ExsiccataID")
);




/*=== Table: "ExsiccataCountry" ===*/
/* An Exsiccata is a series of specimen issued in a publication like form. (Note: the examples within this entity are purposely denormalized!). */
  /*   ExsiccataID: Reference to ExsiccataID in Exsiccata (foreign Key). */
  /*   Country: The editing country of the exsiccata. */
CREATE TABLE "ExsiccataCountry" (
    "ExsiccataID" 	int4	 NOT NULL,
    "Country" 	varchar(50)	 NOT NULL,
  PRIMARY KEY ("ExsiccataID","Country")
);




/*=== Table: "ExsiccataRegion" ===*/
/* An Exsiccata is a series of specimen issued in a publication like form. (Note: the examples within this entity are purposely denormalized!). */
  /*   ExsiccataID: Reference to ExsiccataID in Exsiccata (foreign Key). */
  /*   Region: A region where the specimen within the exsiccata where collected. */
CREATE TABLE "ExsiccataRegion" (
    "ExsiccataID" 	int4	 NOT NULL,
    "Region" 	varchar(50)	 NOT NULL,
  PRIMARY KEY ("ExsiccataID","Region")
);




/*=== Table: "ExsiccataExamples" ===*/
/* An Exsiccata is a series of specimen issued in a publication like form. (Note: the examples within this entity are purposely denormalized!). */
  /*   ExsiccataID: Reference to ExsiccataID in Exsiccata (foreign Key). */
  /*   ExampleID: A number that defines the sequence in which the examples have been added. */
  /*   CollName: Example: Name or Acronym of collection where the example issue is available. Default is M for München. */
  /*   Number: Example: Number of example issue within the Exsiccata series. */
  /*   PublishedName: Example: Published taxon name of example issue. */
  /*   StorageLocation: Example: Deposited under (scientific name indicating a storage location). */
  /*   ImageFile: Example: Path and file name for an image of the label of the exsiccata example issue. */
  /*   CollectionSpecimenID: Optionally: The ID under which the example specimen is recorded in DiversityCollection. */
CREATE TABLE "ExsiccataExamples" (
    "ExsiccataID" 	int4	 NOT NULL,
    "ExampleID" 	int4	 NOT NULL DEFAULT =CLng((Now()-58600)*99999),
    "CollName" 	varchar(255)	 NULL DEFAULT 'M',
    "Number" 	varchar(50)	 NULL,
    "PublishedName" 	varchar(255)	 NULL,
    "StorageLocation" 	varchar(255)	 NULL,
    "ImageFile" 	varchar(255)	 NULL,
    "CollectionSpecimenID" 	int4	 NULL UNIQUE,
  PRIMARY KEY ("ExsiccataID","ExampleID")
);


ALTER TABLE "Exsiccata" ADD 
  FOREIGN KEY ("PrecededBy") REFERENCES "Exsiccata" ("ExsiccataID") ON UPDATE CASCADE,
  FOREIGN KEY ("SupersededBy") REFERENCES "Exsiccata" ("ExsiccataID") ON UPDATE CASCADE
ALTER TABLE "ExsiccataEditor" ADD 
  FOREIGN KEY ("ExsiccataID") REFERENCES "Exsiccata" ("ExsiccataID") ON UPDATE CASCADE ON DELETE CASCADE
ALTER TABLE "ExsiccataExamples" ADD 
  FOREIGN KEY ("ExsiccataID") REFERENCES "Exsiccata" ("ExsiccataID") ON UPDATE CASCADE ON DELETE CASCADE