VegBank

-----CREATE TABLES----------

CREATE TABLE citation

(

  citation_ID serial NOT NULL,

  shortName varchar (250) ,

  citationType varchar (250) ,

  title varchar (250) ,

  titleSuperior varchar (250) ,

  pubDate Date ,

  accessDate Date ,

  conferenceDate Date ,

  citationJournal_ID INTEGER ,

  volume varchar (250) ,

  issue varchar (250) ,

  pageRange varchar (250) ,

  totalPages INTEGER ,

  publisher varchar (250) ,

  publicationPlace varchar (250) ,

  isbn varchar (250) ,

  edition varchar (250) ,

  numberOfVolumes INTEGER ,

  chapterNumber INTEGER ,

  reportNumber INTEGER ,

  communicationType varchar (250) ,

  degree varchar (250) ,

  url varchar (250) ,

  doi varchar (250) ,

  additionalInfo varchar (250) ,

  comments text ,

  PRIMARY KEY(citation_ID)

);

CREATE TABLE citationAlternateIdentifier

(

  citationAlternateIdentifier_ID serial NOT NULL,

  system varchar (250) ,

  identifier varchar (250) NOT NULL,

  PRIMARY KEY(citationAlternateIdentifier_ID)

);

CREATE TABLE citationContributor

(

  citationContributor_ID serial NOT NULL,

  citation_ID INTEGER NOT NULL,

  citationParty_ID INTEGER NOT NULL,

  roleType varchar (250) ,

  order INTEGER ,

  PRIMARY KEY(citationContributor_ID)

);

CREATE TABLE citationParty

(

  citationParty_ID serial NOT NULL,

  type varchar (250) ,

  positionName varchar (250) ,

  salutation varchar (250) ,

  givenName varchar (250) ,

  surname varchar (250) ,

  suffix varchar (250) ,

  organizationName varchar (250) ,

  currentParty INTEGER ,

  PRIMARY KEY(citationParty_ID)

);

CREATE TABLE citationJournal

(

  citationJournal_ID serial NOT NULL,

  journal varchar (250) NOT NULL,

  issn varchar (250) ,

  abbreviation varchar (250) ,

  PRIMARY KEY(citationJournal_ID)

);

CREATE TABLE commReference

(

  commReference_ID serial NOT NULL,

  shortName varchar (250) ,

  citationType varchar (250) ,

  title varchar (250) ,

  titleSuperior varchar (250) ,

  pubDate Date ,

  accessDate Date ,

  conferenceDate Date ,

  commReferenceJournal_ID INTEGER ,

  volume varchar (250) ,

  issue varchar (250) ,

  pageRange varchar (250) ,

  totalPages INTEGER ,

  publisher varchar (250) ,

  publicationPlace varchar (250) ,

  isbn varchar (250) ,

  edition varchar (250) ,

  numberOfVolumes INTEGER ,

  chapterNumber INTEGER ,

  reportNumber INTEGER ,

  communicationType varchar (250) ,

  degree varchar (250) ,

  url varchar (250) ,

  doi varchar (250) ,

  additionalInfo varchar (250) ,

  comments text ,

  PRIMARY KEY(commReference_ID)

);

CREATE TABLE commReferenceAlternateIdentifier

(

  commReferenceAlternateIdentifier_ID serial NOT NULL,

  system varchar (250) ,

  identifier varchar (250) NOT NULL,

  PRIMARY KEY(commReferenceAlternateIdentifier_ID)

);

CREATE TABLE commReferenceContributor

(

  commReferenceContributor_ID serial NOT NULL,

  commReference_ID INTEGER NOT NULL,

  commReferenceParty_ID INTEGER NOT NULL,

  roleType varchar (250) ,

  order INTEGER ,

  PRIMARY KEY(commReferenceContributor_ID)

);

CREATE TABLE commReferenceParty

(

  commReferenceParty_ID serial NOT NULL,

  type varchar (250) ,

  positionName varchar (250) ,

  salutation varchar (250) ,

  givenName varchar (250) ,

  surname varchar (250) ,

  suffix varchar (250) ,

  organizationName varchar (250) ,

  currentParty INTEGER ,

  PRIMARY KEY(commReferenceParty_ID)

);

CREATE TABLE commReferenceJournal

(

  commReferenceJournal_ID serial NOT NULL,

  journal varchar (250) NOT NULL,

  issn varchar (250) ,

  abbreviation varchar (250) ,

  PRIMARY KEY(commReferenceJournal_ID)

);

CREATE TABLE plantReference

(

  plantReference_ID serial NOT NULL,

  shortName varchar (250) ,

  citationType varchar (250) ,

  title varchar (250) ,

  titleSuperior varchar (250) ,

  pubDate Date ,

  accessDate Date ,

  conferenceDate Date ,

  plantReferenceJournal_ID INTEGER ,

  volume varchar (250) ,

  issue varchar (250) ,

  pageRange varchar (250) ,

  totalPages INTEGER ,

  publisher varchar (250) ,

  publicationPlace varchar (250) ,

  isbn varchar (250) ,

  edition varchar (250) ,

  numberOfVolumes INTEGER ,

  chapterNumber INTEGER ,

  reportNumber INTEGER ,

  communicationType varchar (250) ,

  degree varchar (250) ,

  url varchar (250) ,

  doi varchar (250) ,

  additionalInfo varchar (250) ,

  comments text ,

  PRIMARY KEY(plantReference_ID)

);

CREATE TABLE plantReferenceAlternateIdentifier

(

  plantReferenceAlternateIdentifier_ID serial NOT NULL,

  system varchar (250) ,

  identifier varchar (250) NOT NULL,

  PRIMARY KEY(plantReferenceAlternateIdentifier_ID)

);

CREATE TABLE plantReferenceContributor

(

  plantReferenceContributor_ID serial NOT NULL,

  plantReference_ID INTEGER NOT NULL,

  plantReferenceParty_ID INTEGER NOT NULL,

  roleType varchar (250) ,

  order INTEGER ,

  PRIMARY KEY(plantReferenceContributor_ID)

);

CREATE TABLE plantReferenceParty

(

  plantReferenceParty_ID serial NOT NULL,

  type varchar (250) ,

  positionName varchar (250) ,

  salutation varchar (250) ,

  givenName varchar (250) ,

  surname varchar (250) ,

  suffix varchar (250) ,

  organizationName varchar (250) ,

  currentParty INTEGER ,

  PRIMARY KEY(plantReferenceParty_ID)

);

CREATE TABLE plantReferenceJournal

(

  plantReferenceJournal_ID serial NOT NULL,

  journal varchar (250) NOT NULL,

  issn varchar (250) ,

  abbreviation varchar (250) ,

  PRIMARY KEY(plantReferenceJournal_ID)

);

---------CREATE RELATIONSHIPS-----------------

ALTER TABLE citation

  ADD CONSTRAINT Rel_1_citationJournal_ID FOREIGN KEY (citationJournal_ID)

  REFERENCES citationJournal (citationJournal_ID);

ALTER TABLE citationContributor

  ADD CONSTRAINT Rel_2_citation_ID FOREIGN KEY (citation_ID)

  REFERENCES citation (citation_ID);

ALTER TABLE citationContributor

  ADD CONSTRAINT Rel_3_citationParty_ID FOREIGN KEY (citationParty_ID)

  REFERENCES citationParty (citationParty_ID);

ALTER TABLE citationParty

  ADD CONSTRAINT Rel_4_currentParty FOREIGN KEY (currentParty)

  REFERENCES citationParty (citationParty_ID);

ALTER TABLE commReference

  ADD CONSTRAINT Rel_5_commReferenceJournal_ID FOREIGN KEY (commReferenceJournal_ID)

  REFERENCES commReferenceJournal (commReferenceJournal_ID);

ALTER TABLE commReferenceContributor

  ADD CONSTRAINT Rel_6_commReference_ID FOREIGN KEY (commReference_ID)

  REFERENCES commReference (commReference_ID);

ALTER TABLE commReferenceContributor

  ADD CONSTRAINT Rel_7_commReferenceParty_ID FOREIGN KEY (commReferenceParty_ID)

  REFERENCES commReferenceParty (commReferenceParty_ID);

ALTER TABLE commReferenceParty

  ADD CONSTRAINT Rel_8_currentParty FOREIGN KEY (currentParty)

  REFERENCES commReferenceParty (commReferenceParty_ID);

ALTER TABLE plantReference

  ADD CONSTRAINT Rel_9_plantReferenceJournal_ID FOREIGN KEY (plantReferenceJournal_ID)

  REFERENCES plantReferenceJournal (plantReferenceJournal_ID);

ALTER TABLE plantReferenceContributor

  ADD CONSTRAINT Rel_10_plantReference_ID FOREIGN KEY (plantReference_ID)

  REFERENCES plantReference (plantReference_ID);

ALTER TABLE plantReferenceContributor

  ADD CONSTRAINT Rel_11_plantReferenceParty_ID FOREIGN KEY (plantReferenceParty_ID)

  REFERENCES plantReferenceParty (plantReferenceParty_ID);

ALTER TABLE plantReferenceParty

  ADD CONSTRAINT Rel_12_currentParty FOREIGN KEY (currentParty)

  REFERENCES plantReferenceParty (plantReferenceParty_ID);

---------CREATE CLOSED LIST TABLES and relationships---------

CREATE TABLE aux_citation_citationType

(

  [values] varchar (255) not null,

  SortOrd integer,

  PRIMARY KEY([values])

);

     -- insert values--

INSERT INTO aux_citation_citationType ([values],SortOrd) SELECT 'Article',1;

INSERT INTO aux_citation_citationType ([values],SortOrd) SELECT 'Book',2;

INSERT INTO aux_citation_citationType ([values],SortOrd) SELECT 'Chapter',3;

INSERT INTO aux_citation_citationType ([values],SortOrd) SELECT 'EditedBook',4;

INSERT INTO aux_citation_citationType ([values],SortOrd) SELECT 'Manuscript',5;

INSERT INTO aux_citation_citationType ([values],SortOrd) SELECT 'Report',6;

INSERT INTO aux_citation_citationType ([values],SortOrd) SELECT 'Thesis',7;

INSERT INTO aux_citation_citationType ([values],SortOrd) SELECT 'ConferenceProceedings',8;

INSERT INTO aux_citation_citationType ([values],SortOrd) SELECT 'PersonalCommunication',9;

INSERT INTO aux_citation_citationType ([values],SortOrd) SELECT 'Presentation',10;

INSERT INTO aux_citation_citationType ([values],SortOrd) SELECT 'Website',11;

INSERT INTO aux_citation_citationType ([values],SortOrd) SELECT 'Generic',12;

    -- add relationship for closed list --

ALTER TABLE citation

  ADD CONSTRAINT Rel_13_citationType FOREIGN KEY (citationType)

  REFERENCES aux_citation_citationType ([values]);

CREATE TABLE aux_citation_titleSuperior

(

  [values] varchar (255) not null,

  SortOrd integer,

  PRIMARY KEY([values])

);

     -- insert values--

INSERT INTO aux_citation_titleSuperior ([values],SortOrd) SELECT 'ConferenceName',1;

INSERT INTO aux_citation_titleSuperior ([values],SortOrd) SELECT 'BookTitle',2;

    -- add relationship for closed list --

ALTER TABLE citation

  ADD CONSTRAINT Rel_14_titleSuperior FOREIGN KEY (titleSuperior)

  REFERENCES aux_citation_titleSuperior ([values]);

CREATE TABLE aux_citationContributor_roleType

(

  [values] varchar (255) not null,

  SortOrd integer,

  PRIMARY KEY([values])

);

     -- insert values--

INSERT INTO aux_citationContributor_roleType ([values],SortOrd) SELECT 'Author',1;

INSERT INTO aux_citationContributor_roleType ([values],SortOrd) SELECT 'Editor',2;

INSERT INTO aux_citationContributor_roleType ([values],SortOrd) SELECT 'Originator',3;

INSERT INTO aux_citationContributor_roleType ([values],SortOrd) SELECT 'Performer',4;

INSERT INTO aux_citationContributor_roleType ([values],SortOrd) SELECT 'Recipient',5;

INSERT INTO aux_citationContributor_roleType ([values],SortOrd) SELECT 'CustodianSteward',6;

    -- add relationship for closed list --

ALTER TABLE citationContributor

  ADD CONSTRAINT Rel_15_roleType FOREIGN KEY (roleType)

  REFERENCES aux_citationContributor_roleType ([values]);

CREATE TABLE aux_commReference_citationType

(

  [values] varchar (255) not null,

  SortOrd integer,

  PRIMARY KEY([values])

);

     -- insert values--

INSERT INTO aux_commReference_citationType ([values],SortOrd) SELECT 'Article',1;

INSERT INTO aux_commReference_citationType ([values],SortOrd) SELECT 'Book',2;

INSERT INTO aux_commReference_citationType ([values],SortOrd) SELECT 'Chapter',3;

INSERT INTO aux_commReference_citationType ([values],SortOrd) SELECT 'EditedBook',4;

INSERT INTO aux_commReference_citationType ([values],SortOrd) SELECT 'Manuscript',5;

INSERT INTO aux_commReference_citationType ([values],SortOrd) SELECT 'Report',6;

INSERT INTO aux_commReference_citationType ([values],SortOrd) SELECT 'Thesis',7;

INSERT INTO aux_commReference_citationType ([values],SortOrd) SELECT 'ConferenceProceedings',8;

INSERT INTO aux_commReference_citationType ([values],SortOrd) SELECT 'PersonalCommunication',9;

INSERT INTO aux_commReference_citationType ([values],SortOrd) SELECT 'Presentation',10;

INSERT INTO aux_commReference_citationType ([values],SortOrd) SELECT 'Website',11;

INSERT INTO aux_commReference_citationType ([values],SortOrd) SELECT 'Generic',12;

    -- add relationship for closed list --

ALTER TABLE commReference

  ADD CONSTRAINT Rel_16_citationType FOREIGN KEY (citationType)

  REFERENCES aux_commReference_citationType ([values]);

CREATE TABLE aux_commReference_titleSuperior

(

  [values] varchar (255) not null,

  SortOrd integer,

  PRIMARY KEY([values])

);

     -- insert values--

INSERT INTO aux_commReference_titleSuperior ([values],SortOrd) SELECT 'ConferenceName',1;

INSERT INTO aux_commReference_titleSuperior ([values],SortOrd) SELECT 'BookTitle',2;

    -- add relationship for closed list --

ALTER TABLE commReference

  ADD CONSTRAINT Rel_17_titleSuperior FOREIGN KEY (titleSuperior)

  REFERENCES aux_commReference_titleSuperior ([values]);

CREATE TABLE aux_commReferenceContributor_roleType

(

  [values] varchar (255) not null,

  SortOrd integer,

  PRIMARY KEY([values])

);

     -- insert values--

INSERT INTO aux_commReferenceContributor_roleType ([values],SortOrd) SELECT 'Author',1;

INSERT INTO aux_commReferenceContributor_roleType ([values],SortOrd) SELECT 'Editor',2;

INSERT INTO aux_commReferenceContributor_roleType ([values],SortOrd) SELECT 'Originator',3;

INSERT INTO aux_commReferenceContributor_roleType ([values],SortOrd) SELECT 'Performer',4;

INSERT INTO aux_commReferenceContributor_roleType ([values],SortOrd) SELECT 'Recipient',5;

INSERT INTO aux_commReferenceContributor_roleType ([values],SortOrd) SELECT 'CustodianSteward',6;

    -- add relationship for closed list --

ALTER TABLE commReferenceContributor

  ADD CONSTRAINT Rel_18_roleType FOREIGN KEY (roleType)

  REFERENCES aux_commReferenceContributor_roleType ([values]);

CREATE TABLE aux_plantReference_citationType

(

  [values] varchar (255) not null,

  SortOrd integer,

  PRIMARY KEY([values])

);

     -- insert values--

INSERT INTO aux_plantReference_citationType ([values],SortOrd) SELECT 'Article',1;

INSERT INTO aux_plantReference_citationType ([values],SortOrd) SELECT 'Book',2;

INSERT INTO aux_plantReference_citationType ([values],SortOrd) SELECT 'Chapter',3;

INSERT INTO aux_plantReference_citationType ([values],SortOrd) SELECT 'EditedBook',4;

INSERT INTO aux_plantReference_citationType ([values],SortOrd) SELECT 'Manuscript',5;

INSERT INTO aux_plantReference_citationType ([values],SortOrd) SELECT 'Report',6;

INSERT INTO aux_plantReference_citationType ([values],SortOrd) SELECT 'Thesis',7;

INSERT INTO aux_plantReference_citationType ([values],SortOrd) SELECT 'ConferenceProceedings',8;

INSERT INTO aux_plantReference_citationType ([values],SortOrd) SELECT 'PersonalCommunication',9;

INSERT INTO aux_plantReference_citationType ([values],SortOrd) SELECT 'Presentation',10;

INSERT INTO aux_plantReference_citationType ([values],SortOrd) SELECT 'Website',11;

INSERT INTO aux_plantReference_citationType ([values],SortOrd) SELECT 'Generic',12;

    -- add relationship for closed list --

ALTER TABLE plantReference

  ADD CONSTRAINT Rel_19_citationType FOREIGN KEY (citationType)

  REFERENCES aux_plantReference_citationType ([values]);

CREATE TABLE aux_plantReference_titleSuperior

(

  [values] varchar (255) not null,

  SortOrd integer,

  PRIMARY KEY([values])

);

     -- insert values--

INSERT INTO aux_plantReference_titleSuperior ([values],SortOrd) SELECT 'ConferenceName',1;

INSERT INTO aux_plantReference_titleSuperior ([values],SortOrd) SELECT 'BookTitle',2;

    -- add relationship for closed list --

ALTER TABLE plantReference

  ADD CONSTRAINT Rel_20_titleSuperior FOREIGN KEY (titleSuperior)

  REFERENCES aux_plantReference_titleSuperior ([values]);

CREATE TABLE aux_plantReferenceContributor_roleType

(

  [values] varchar (255) not null,

  SortOrd integer,

  PRIMARY KEY([values])

);

     -- insert values--

INSERT INTO aux_plantReferenceContributor_roleType ([values],SortOrd) SELECT 'Author',1;

INSERT INTO aux_plantReferenceContributor_roleType ([values],SortOrd) SELECT 'Editor',2;

INSERT INTO aux_plantReferenceContributor_roleType ([values],SortOrd) SELECT 'Originator',3;

INSERT INTO aux_plantReferenceContributor_roleType ([values],SortOrd) SELECT 'Performer',4;

INSERT INTO aux_plantReferenceContributor_roleType ([values],SortOrd) SELECT 'Recipient',5;

INSERT INTO aux_plantReferenceContributor_roleType ([values],SortOrd) SELECT 'CustodianSteward',6;

    -- add relationship for closed list --

ALTER TABLE plantReferenceContributor

  ADD CONSTRAINT Rel_21_roleType FOREIGN KEY (roleType)

  REFERENCES aux_plantReferenceContributor_roleType ([values]);