-----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]);