/* * Nomencurator Database Schema for PostgreSQL 7.2 * * $Id: makedbschema.sql,v 1.10 2002/08/26 09:22:50 t.okada Exp $ * $Log: makedbschema.sql,v $ * Revision 1.10 2002/08/26 09:22:50 t.okada * add publisher and place to publication * * Revision 1.9 2002/08/22 13:29:02 t.okada * It corrects for a locale colum addition. * * Revision 1.8 2002/08/20 13:20:42 t.okada * add lines column and notes column * * Revision 1.7 2002/08/15 05:17:35 ryo * change type of year column of Publication table into 'text' * * Revision 1.6 2002/07/29 04:58:45 ryo * change column name of NameUsage table from 'note' to 'notes' * * Revision 1.5 2002/07/24 12:15:05 t.okada * add note * * Revision 1.4 2002/07/08 07:39:27 ryo * remove unused table * * Revision 1.3 2002/07/03 13:35:06 ryo * modify specification * * Revision 1.2 2002/04/16 03:53:38 nozomi * migration to NameUsage from NameRecord * * Revision 1.1 2002/02/14 10:21:29 ryo * initial import into CVS. * */ /* drop table Affiliation; drop table Annotation; drop table Appearance; drop table Author; drop table Institution; drop table NameUsage; drop table Publication; drop table NRnode; drop table ScientificName; drop table DepositedData; drop table ParsedData; drop table ObjectTable; drop table LT_Annotation_annotators; drop table LT_Annotation_annotatants; drop table LT_Appearance_annotations; drop table LT_Appearance_nameusages; drop table LT_Author_affiliations; drop table LT_NameUsage_lowerTaxa; drop table LT_NameUsage_authorList; drop table LT_Publication_authorList; drop table LT_Publication_contents; drop table LT_Publication_appearances; drop table LT_NRnode_linkFrom; drop table LT_NRnode_linkTo; drop table LT_NRnode_scientificNames; drop table LT_NRnode_lower; drop table LT_ScientificName_authority; drop table LT_ScientificName_usages; drop table LT_DepositedData_contents; drop table LT_ParsedData_parsedObjects; drop table LinkTable; */ /* * Object Table */ CREATE TABLE ObjectTable ( id text -- object id ); /* * Affiliation */ CREATE TABLE Affiliation ( institute text, -- (OID) pointer to Institution record dateFrom timestamp, -- date (when a author) moved to the institution dateTo timestamp -- date (when a author) moved from the institution ) INHERITS (ObjectTable); /* * Annotation */ CREATE TABLE Annotation ( linkType text, -- link type between two NameUsages, e.g. synonymy appearance text -- (OID) pointer to Appearance object which holds "taxonomically encoded" text -- annotators -> LT_Anotation_annotators -- (OID list) list of pointers to NameUsages which used -- in the Publication referred via the Appearance -- annotatants -> LT_Anotation_annotatants -- (OID list) list of pointers to NameUsages which are annotated -- by the Publication referred via the Appearace ) INHERITS (ObjectTable); /* * Appearance */ CREATE TABLE Appearance ( page text, -- precise page where the name appeared (with taxonomic action, if available) lines text, -- precise lines where the name appeared (with taxonomic action, if available) appearance text, -- precise expression of taxonomic actions such as naming and taxonomic decision publication text -- annotations -> LT_Appearance_annotations -- (OID list) pointers to Annotation decoded from the appearance -- NameUsages -> LT_Appearance_nameusages -- (OID list) pointers to NameUsage decoded from the appearance ) INHERITS (ObjectTable); /* * Author */ CREATE TABLE Author ( surname text, -- surname of the author firstName text, -- first name of the author middleName text, -- middle name of the author faudality text, -- faudality part of author's name title text, -- title, such as Sir, if available epithet text, -- epithet, e.g. Jr., Sen, if available birth timestamp, -- date of birth, if available death timestamp, -- date of death, if available -- publications -> LT_Publication_authorList -- (OID list) list of pointers to Publications by this Author -- affiliations -> LT_Author_affiliations -- (OID list) list of Affiliations of this Author firstPublication text, lastPublication text ) INHERITS (ObjectTable); /* * Institution */ CREATE TABLE Institution ( name text, -- name of the institution address text, -- address of the institution dateFrom timestamp, -- date (a author) moved to the institution dateTo timestamp, -- date (a author) moved from the institution previous text, -- (OID) pointer to previous Institution record if name or address was changed next text, -- (OID) pointer to next Institution record if name or address was changed divisionOf text -- (OID) pointer to the Institution record to which this institution belongs to -- divisions -> LT_Institution_divisions -- (OID list) list of pointers to Insttiution which is division of this institution ) INHERITS (ObjectTable); /* * NameUsage */ CREATE TABLE NameUsage ( rank text, -- the rank given in the Appearance of the Publication name text, -- name used for the taxon concept in the Publication authority text, -- (OID) pointer to an Appearance in the authority Publication of the name appearance text, -- (OID) pointer to an Appearance of the Publication in which the name was used higherTaxon text, -- (OID) pointer to the higher rank NameUsage of this NameUsage, given in the publication -- lower -> LT_NameUsage_lowerTaxa -- (OID list) a list of pointers to lower rank NameUsage of this NameUsage, given in the publication type text, -- (OID) pointer to the type NameUsage of this NameUsage, given in the publication typeOf text, -- (OID) pointer to the NameUsage of which type is this NameUsage, as given in the publication -- annotations -> LT_annotation_annotators -- (OID list) a list of pointers to Annotations stated with this NameUsage in relation to NameUsages of older usage of the name year int2, notes text, locale text, incertaeSeds text -- authorList -> LT_NameUsage_authorList ) INHERITS (ObjectTable); /* * Publication */ CREATE TABLE Publication ( authors text, -- authors as text appeared in the publication affiliations text, -- affiliations as text appeared in the publication -- authorList -> LT_Publication_authorList -- (OID list) list of pointers to Author of this publication citationtitle text, contentstitle text, ISXN text, -- ISSN/ISBN code of the publication year text, -- year of the publication volume text, -- volume of the publication issue text, -- number of the publication firstPage text, -- first page of the publication lastPage text, -- last page of the publication publisher text, -- first page of the publication place text, -- last page of the publication notes text, -- notes of the publication recieved timestamp, -- date when the manuscript was received reviced timestamp, -- date when the reviced manuscript was received accepted timestamp, -- date when the manuscript was accepted partOf text -- (OID) pointer to the Publication containing this publication, if available -- (e.g. book contining this publication as its chapter) -- parts -> LT_Publication_contents -- (OID list) list of pointers to Publication contained by this publication, if available -- (e.g. chapters in this publication, if it is a book) -- appearances -> LT_Publication_appearances -- (OID list) list of pointers to Annotation abstracted from the publication ) INHERITS (ObjectTable); /* * NRnode */ CREATE TABLE NRnode ( NameUsage text -- (OID) pointer to the NameUsage proxied by this data -- from -> LT_NRnode_LinkFrom -- (OID) -- to -> LT_NRnode_LinkTo -- (OID) -- scientificNames -> LT_NRnode_scientificNames -- (OID) ) INHERITS (ObjectTable); /* * ScientificName */ CREATE TABLE ScientificName ( authority text -- (OID) ) INHERITS (ObjectTable); /* * DepositedData */ CREATE TABLE DepositedData ( contents text, -- (OID) depositor text, depositedAt timestamp ) INHERITS (ObjectTable); /* * ParsedData */ CREATE TABLE ParsedData ( contents text -- (OID) ) INHERITS (ObjectTable); /* * LinkTable */ CREATE TABLE LinkTable ( linkFrom text, -- tableName text, linkTo text, seq int2 -- link order ); CREATE TABLE LT_Annotation_annotators () INHERITS (LinkTable); CREATE TABLE LT_Annotation_annotatants () INHERITS (LinkTable); CREATE TABLE LT_Appearance_annotations () INHERITS (LinkTable); CREATE TABLE LT_Appearance_NameUsages () INHERITS (LinkTable); CREATE TABLE LT_Author_affiliations () INHERITS (LinkTable); CREATE TABLE LT_NameUsage_lowerTaxa () INHERITS (LinkTable); CREATE TABLE LT_NameUsage_authorList () INHERITS (LinkTable); CREATE TABLE LT_Publication_authorList () INHERITS (LinkTable); CREATE TABLE LT_Publication_contents () INHERITS (LinkTable); CREATE TABLE LT_Publication_appearances () INHERITS (LinkTable); CREATE TABLE LT_NRnode_linkFrom () INHERITS (LinkTable); CREATE TABLE LT_NRnode_linkTo () INHERITS (LinkTable); CREATE TABLE LT_NRnode_scientificNames () INHERITS (LinkTable); CREATE TABLE LT_NRnode_lower () INHERITS (LinkTable); CREATE TABLE LT_ScientificName_authority () INHERITS (LinkTable); CREATE TABLE LT_ScientificName_usages () INHERITS (LinkTable); CREATE TABLE LT_DepositedData_contents () INHERITS (LinkTable); CREATE TABLE LT_ParsedData_parsedObjects () INHERITS (LinkTable); /* * grant to nobody user. */ grant all on Affiliation to nobody; grant all on Annotation to nobody; grant all on Appearance to nobody; grant all on Author to nobody; grant all on Institution to nobody; grant all on NameUsage to nobody; grant all on Publication to nobody; grant all on NRnode to nobody; grant all on ScientificName to nobody; grant all on DepositedData to nobody; grant all on ParsedData to nobody; grant all on ObjectTable to nobody; grant all on LT_Annotation_annotators to nobody; grant all on LT_Annotation_annotatants to nobody; grant all on LT_Appearance_annotations to nobody; grant all on LT_Appearance_NameUsages to nobody; grant all on LT_Author_affiliations to nobody; grant all on LT_NameUsage_lowerTaxa to nobody; grant all on LT_NameUsage_authorList to nobody; grant all on LT_Publication_authorList to nobody; grant all on LT_Publication_contents to nobody; grant all on LT_Publication_appearances to nobody; grant all on LT_NRnode_linkFrom to nobody; grant all on LT_NRnode_linkTo to nobody; grant all on LT_NRnode_scientificNames to nobody; grant all on LT_NRnode_lower to nobody; grant all on LT_ScientificName_authority to nobody; grant all on LT_ScientificName_usages to nobody; grant all on LT_DepositedData_contents to nobody; grant all on LT_ParsedData_parsedObjects to nobody;