Pisces-II: Richard L. Pyle's data model

Pisces-II: Richard L. Pyle's data model presented at 2000 ASIH meeting

types are translated to SQL terms as possible

Table Attribute
name definition name definition type domain value

AGENTS data strucutres to capture generic author information

AGENT Meta attributes to manage ORGANIZATION and PERSON in a general way AgentID OID INTEGER system given

ValidAgentID pointer to an AGENT tuple containing valid (current) agent information INTEGER AgentID

AgentType type of AGENT CHAR(12) PERSON or ORGANIZATION

Ambiguous True if the AGENT is rather "anonymous" like "a local fisherman", "a crew of R/V Townsedn Cromwell" BOOLEAN Y/N

Affiliate True if the AGENT directly affiliated with the user organization BOOLEAN Y/N

Remarks remark TEXT -

Cheat-
AgentFullName internal use CHAR(255) -

PERSON Personal data container PersonID OID INTEGER subtype of AgentID with PERSON as AgentType value

Prefix prefix of person's name CHAR(20) -

FirstName first name CHAR(255) -

MiddleName last name CHAR(255) -

LastName last name CHAR(255) -

Suffix suffix of person's name CHAR(20) -

PrimaryName specify name format preference of the person byte 0-255

Gender gender byte 0-255

BirthDate date of birth CHAR(20) -

DeathDate date of birth CHAR(20) -

ORGANIZATION organization data holder may compose hierarchies OrgID OID INTEGER subtype of AgentID with ORGANIZATION as AgentType value

Acronym acronym of the organization CHAR(20) -

OrgName name of the organization CHAR(255) -

ParentOrgID parent organization INTEGER OrgID

Scope Scope of the organization CHAR(50) International
National
Regional
Local

AGENTADDR linkage between PERSON and ORGANIZATION but also ADDRESS holder. Interpretation of ADDRESS depends PersonID and OrgID values as follows:

PersonID OrgID ADDRESS

specified specified address of the person at the organization

specified unspecified address of the person

unspecified specified address of the organization

unspecified unspecified prohibited by DBMS

AgentAddrID OID INTEGER system given

PersonID pointer to a tuple in PERSON INTEGER PersonID

OrgID pointer to a tuple in ORGANIZATION INTEGER OrgID

AddressID pointer to a tuple in ADDRESS INTEGER AddressID

AgenteRole Role of the person at the Organization CHAR(25) -

IsCurrent whether replesenting current affiliation of the person BOOLEAN Y/N

Remarks remark TEXT -

ADDRESS holder of parsed address AddressID OID INTEGER system given

StreetAddress street address CHAR(255) -

MailStop mail stop CHAR(20) -

City city name CHAR(50) -

State state or province CHAR(50) -

ZIP ZIP code CHAR(15) -

Country country name CHAR(50) -

FormattedAddr formatted address CHAR(255) -

Use preference usage of this address, e.g. correspondence, shipping CHAR(20) -

Remarks remarks CHAR(100) -

PHONEEMAIL electric contact of person or organization specified by AGENTADDR PhoneEmailID OID INTEGER system given

AgentAddrID pointer to AgentAddr INTEGER AgetAddrID

PhoneEmail phone number or email address CHAR(255) -

PhoneEmailType a flag CHAR(30) e.g. FAX, Phone, Cellular, Email, Pager, Web Page

TAXON NAMES

TAXONNAME name with acceptance status and specimen reference TaxonNameID OID INTEGER system given

RecordCodeID pointer to a tuple in RECORDCODE INTEGER RecordCodeID

Authority authority of the name CHAR(255) -

Year (authority?) year CHAR(20) -

PhyloSort ? FLOAT ?

OrigTaxonRefID original descriptoin of the taxon name INTEGER TaxonRefID

AcceptedTaxonRefID current status of the taxon name INTEGER TaxonRefID

AvailabilityID availability (of the name?) byte 0-255

Fossile fossile or not BOOLEAN Y/N

EnvironID environmental information? INTEGER OID?

WordTypeID ? byte 0-255

Gender gender (of hybrids' parent? specimen?) CHAR(1) -

TypeTaxonNameID type of this this taxon INTEGER TaxonNameID

Complete completenes (of what?) BOOLEAN Y/N

Cheat-
Epithet epithet CHAR(256) -

Cheat-
RankID rankID byte 0-255

Cheat-
ValidTaxonNameID valid TAXONNAME INTEGER TaxonNameID

Cheat-
ParentID parent TAXONNAME(?) INTEGER TaxonNameID

Cheat-
Parents have CheatParents(pl.?) BOOLEAN Y/N

Cheat-
FullTaxonomy hierarchy TEXT -

CheatGlobalPhyloSort ? CHAR(50) -

HYBRID
PARENT hybrid relationship of pointer to a TaxonNames TaxonNameID pointer to a TAXONNAME INTEGER TaxonNameID

Parent1ID reference to parent TAXONNAME INTEGER TaxonNameID

Parent2ID reference to parent TAXONNAME INTEGER TaxonNameID

TAXON
EXCERPT relatioship between a TAXONNAME and an EXCERPT TaxonExcerptID OID INTEGER system given

TaxonNameID reference to a TAXONNAME INTEGER TaxonNameID
ExcerptID reference to an EXCERPT INTEGER ExcerptID

PATRONYM relatioship between a TAXONNAME and an AGENT PatronymID OID INTEGER system given

TaxonNameID reference to a TAXONNAME INTEGER TaxonNameID

AgentID reference to an AGENT INTEGER AgentID

TAXONREF component of network of NameRecords and Annotations TaxonRefID OID INTEGER system given

ReferenceID pointer to a tuple in REFERENCE INTEGER ReferenceID

Page page where the taxon is described under the name CHAR(255) -

Illustration illustration descriptor CHAR(255) -

TaxonNameID pointer to a TAXONNAME INTEGER TaxonNameID

TaxonName name of the taxon CHAR(255) -

RankID rank byte 0-255

ParentID parent TAXONNAME (why not TAXONREF?) INTEGER TaxonNameID

TaxonNameID pointer to a valid tuple in TAXONNAME INTEGER TaxonNameID

TypeDesignation type of type, e.g. holotype? CHAR(255) -

Nominotypical Nominotypical flag BOOLEAN Y/N

IncertaeSedis IncertaeSedis flag BOOLEAN Y/N

Objective objective sysnonymy status? BOOLEAN Y/N

InPart ? BOOLEAN Y/N

TypeCatalog ? BOOLEAN Y/N

FirstRevision whether firstr revision or not BOOLEAN Y/N

NeedsVerification not (verified or not) BOOLEAN Y/N

Reliability reliability of TAXONREF byte 0-255

Remarks remark TEXT -

Cheat-
OriginalDescription ? BOOLEAN Y/N

Cheat-
Accepted ? BOOLEAN Y/N

VERNACULAR a common name of a TaxonRef VernacularID OID INTEGER system given

TaxonRefID pointer to a tuple in TAXONREF INTEGER TaxonRefID

VernacularName common name itself CHAR(255) -

Language language CHAR(50) -

Remarks remarks TEXT -

LIST
TAXONRANK list of taxon ranks RankID OID INTEGER system given

RankLevel name of the rank CHAR(50) -

Abbreviation abbreviation of rank name CHAR(10) -

PrefixChars number of prefix characters byte 0-255

IncludeAbbrev ? BOOLEAN Y/N

REFERENCE data strucutre

REFERENECE reference RefeerenceID OID INTEGER system given

RecordCodeID pointer to a tuple in RECORDCODE INTEGER RecordCodeID

RefType type of reference CHAR(25) -

ParentRefID reference to parent REFERENCE INTEGER ReferenceID

Year year CHAR(25) -

Title title of the reference TEXT -

ScondaryTitle secondary title TEXT -

SecondaryAuthor secondary author CHAR(255) -

Publisher publisher CHAR(255) -

PlacePublished published place CHAR(255) -

Volume volume CHAR(25) -

NumberVolumes number of volumes CHAR(25) -

Number number CHAR(25) -

Pages pages CHAR(255) -

Figures figures CHAR(255) -

RefSeriesID pointer to a tuple in REFSERIES INTEGER RefSeriesID

Edition edition CHAR(50) -

DatePublished published date DATE -

DateRemarks remarks on date CHAR(255) -

TypeWork type of the work CHAR(255) -

SubsidaryAuthor subsidary author CHAR(50) -

AlternateTitle alternate title TEXT -

ISBN ISBN CHAR(50) -

Language language CHAR(50) -

Copy ? BOOLEAN Y/N

Complete completeness of the work? BOOLEAN Y/N

Cheat-
Citation ? CHAR(255) -

Cheat-
Author ? CHAR(255) -

Cheat-
FullAuthors authors appeared in the work TEXT -

REFAUTHOR authors of a reference RefAuthorID OID INTEGER system given

ReferenceID pointer to the REFERENCE INTEGER ReferenceID

AgentID pointer to author of the reference as AGENT INTEGER AgentID

Seq ? byte 0-255

REF
BIBLIOGRAPHY refernces referred to by a reference RefBibliographyID OID INTEGER system given

ReferenceID pointer to the REFERENCE referring to other REFERENCEs INTEGER ReferenceID

CitedRefID pointer to a REFERENCE referred to by the REFERENCE INTEGER ReferenceID

Seq ? byte 0-255

EXCERPT refernces referred to by a reference ExcerptID OID INTEGER system given

ExcerptTtype type of the excerpt CHAR(50) -

ReferenceID pointer to the REFERENCE to which the excerpt corresponds INTEGER ReferenceID

Pages pages where the excerpt appeared CHAR(15) -

Language languae of the excerpt CHAR(50) -

Excerpt excerpt itself TEXT -

TranslatorID translator of the excerpt INTEGER AgentID

REFSERIES journals and othr serial publications RefSeriesID OID INTEGER system given

RecordCodeID pointer to the RECODECODE INTEGER RecordCodeID

Acronym Acronym of the series CHAR(50) -

Abbreviation abbreviation of the series CHAR(255) -

Title title of the series CHAR(255) -

Series ? CHAR(50) -

Editor editor of the series CHAR(255) -

Dates ? CHAR(50) -

Remarks remarks TEXT -

GLOSSARY data structures

GLOSSARY a word with attributes includings its language and definition GlossaryID OID INTEGER system given

Word word itself CHAR(80) -

Language language of the word CHAR(50) -

WordTypeID type of the word byte 0-255

Definition Definition of the word TEXT -

Remakrs remarks TEXT -

REFKEYWORD a keyword appeared in a reference RefKeywordID OID INTEGER system given

ReferenceID pointer to the REFERENCE where the keyword appeared INTEGER ReferenceID

GlossaryID pointer to the GLOSSARY INTEGER GlossaryID

THESAURUS a pair of words having ligiusitically equivalent values ThesaurusID OID INTEGER system given

GlossaryID pointer to a GLOSSARY INTEGER GlossaryID

Glossary2ID pointer to another GLOSSARY INTEGER GlossaryID

OBJECT data structures

OBJECT supporting evidence for systematics and biogeography, either biological object (e.g. specimen) or objects depicting/representing biological objects (e.g. images, observations or published citations) ObjectID OID INTEGER system given

ParentObjectID pointer to parent OBJECT INTEGER ObjectID

ObjectTypeID pointer to a tuple in LISTOBJECTTYPE INTEGER ObjectTypeID

RecordCodeID ? INTEGER RecordCodeID

Current-
DeterminoationID pointer to current DETERMINATION INTEGER

PreparationMethod method of preparation CHAR(255) -

StorageContainer storage container CHAR(255) -

Abundance abundance of what? CHAR(255) -

Count count of what? INTEGER -

CountEstimate is estimated count? BOOLEAN Y/N

TypeStatus type status CHAR(50) -

LifeStage life stage of biological object CHAR(255) -

VerbatimLocality verbatim locality TEXT -

TerrestrialNameID pointer to terrestorial LOCATIONNAME INTEGER LocationNameID

AcaticNameID pointer to auatic LOCATIONNAME INTEGER LocationNameID

TerrestrialQualifier ? CHAR(120) -

AquaticQualifier ? CHAR(120) -

LocationDescription description of a location CHAR(120) -

LatLongType type of latitude/longitude CHAR(50) -

Longitude1 longitude FLOAT -

Latitude1 latitude FLOAT -

Longitude2 longitude FLOAT -

Latitude2 latitude FLOAT -

LatLongMethod method used to determine lat/long CHAR(255) -

LatLongAccuracy accuracy of lat/long value CHAR(255) -

Datum ? CHAR(50) -

EnvironID pointer to a tuple in LISTENVIRON INTEGER EnvrionID

MinElevation minimum elevation FLOAT -

MaxElevation maximum elevation FLOAT -

ElevationType type of elevation CHAR(20) DepthElevType

MinDepth minimum depth DOUBLE -

MaxDepth maximum depth DOUBLE -

DepthType type of depth CHAR(20) DepthElevType

Expedition expedition in which the biological object was captured CHAR(255) -

Vessel vessel name used to catch the object CHAR(50) -

Method mthod to capture the object CHAR(50) -

VerbatimDate date in verbatim CHAR(50) -

StartDate start date (of expedition?) DATE -

EndDate end date (of expedition?) DATE -

StartTime start time (of expedition?) TIMESTAMP -

EndTime end time (of expedition?) TIMESTAMP -

Cheat-
SizeRange renge of size CHAR(255) -

Cheat-
CatalogNumber catalog number (in which collection?) CHAR(255) -

Cheat-
Collectors collectors TEXT -

OBJECTAGENT relationship between an OBJECT and an AGENT ObjectAgentID OID INTEGER system given

ObjectID pointer to a tuple in OBJECT INTEGER ObjectID

AgentID pointer to a tuple in AGENT INTEGER AgentID

RoleID a pointer to a tuple in LISTROLE INTEGER RoleID

Sequence Sequence number of..? INTEGER -

LISTROLE relationship between an OBJECT and an AGENT RoleID OID INTEGER system given

ObjectTypeID pointer to a tuple in LISTOBJECTTYPE INTEGER ObjectTypeID

ObjectRole role type CHAR(50) -

LIST
OBJECTTYPE relationship between an OBJECT and an AGENT ObjectTypeID OID INTEGER system given

ObjectTypeName name of object type CHAR(50) -

Category category of the object type CHAR(50) -

Cataloged cataloged status BOOLEAN Y/N

SingleTaxon single taxon status BOOLEAN Y/N

DETERMINATION identification of the object as assignment to a TAXONREF DeterminationID OID INTEGER system given

ObjectID pointer to a tuple in OBJECT INTEGER ObjectID

TaxonRefID pointer to a tuple in TAXONREF INTEGER TaxonRefID

IDQuestionable ? BOOLEAN Y/N

Remarks remarks TEXT -

Cheat-
IsCurrent current or not BOOLEAN Y/N

STATUSMODE giving a status to a tuple in DETERMINATION StatusModeID OID INTEGER system given

DeterminationID pointer to a tuple in DETERMINATION INTEGER DeterminationID

ModeID mode byte 0-255

StatusID status byte 0-255

FirstRecord whether the first record (of what?) BOOLEAN Y/N

Remarks remarks TEXT -

OBJECT
RELATION relationship between two OBJECTs with relationship type ObjectRelationID OID INTEGER system given

ObjectRelationID type of relationship INTEGER ObjectRelationTypeID

ObjectID pointer to an OBJECT INTEGER ObjectID

RelatedObjectID pointer to another OBJECT related to the OBJECT INTEGER ObjectID

Remarks remarks TEXT -

LIST
OBJECT
RELATIONTYPE relationship type between two OBJECTs ObjectRelationTypeID OID INTEGER system given

RelationshipName name of relationship type CHAR(50) -

LeftSideRoleName role type of left side OBJECT CHAR(50) -

RightSideRoleName role type of right side OBJECT CHAR(50) -

IsReflexive whether reflexive relationship type BOOLEAN Y/N

Remarks remarks TEXT -

OBJECTHABITAT habitat parameter of an OBJECT ObjectHabitatID OID INTEGER system given

ObjectID pointer to an ObjectID

HabitatParameter ? CHAR(255) -

ParamterValue value of the parameter CHAR(255) -

ParamterUnits units of the parameter CHAR(50) -

Remarks remarks TEXT -

LIST
DEPTHELEV
TYPE type of depth/elevation DepthElevType type of depth/elevation CHAR(20) -

MetersFactor a factor FLOAT -

FeetFactor a factor FLOAT -

IMAGE image ImageID OID INTEGER system given

OriginalID ? INTEGER ImageID?

Index1 ? CHAR(50) -

Index2 ? CHAR(50) -

Medium medium of the image CHAR(50) -

Sequence ? INTEGER -

AspectRatio aspect ratio FLOAT -

Quality quality of image byte 0-255

PhotoLocation location of photograph? CHAR(255) -

IMAGEFILE image file ImageID OID INTEGER system given

ImageID IMAGE INTEGER ImageID?

FileName file name CHAR(255) -

FilePath file path CHAR(255) -

Other data structures

CODENUMBER code number handler RecordCodeID OID INTEGER system given

TableName name of a table pointed by RecordID CHAR(50) -

RecordID pointer to either TAXONNAME, REFERENCE, REFSERIES or OBJECT INTEGER TaxonNameID
ReferenceID
RefSeriesID
ObjectID

CodeNumberType type of code number CHAR(50) -

CodeNumberSeriesID pointer to a tuple in CODENUMBERSERIES INTEGER CodeNumberSeriesID

CodeNumber code number itself CHAR(255) -

CODENUMBER
SERIES collection handler CodeNumberSeriesID OID INTEGER system given

CodeNumberSeries code number series itself CHAR(10) -

CodeNumberSeriesName name of code number series CHAR(255) -

OwnerID owner of the code number series INTEGER AgentID

ColType type of the collection CHAR(255) -

Description description of the collection TEXT -

COMMENT comment on recorods, i.e., TAXONNAME, REFERENCE, REFSERIES or OBJECT CommentID OID INTEGER system given

TableName name of a table pointed by RecordID CHAR(50) -

RecordID pointer to either TAXONNAME, REFERENCE, REFSERIES or OBJECT INTEGER TaxonNameID
ReferenceID
RefSeriesID
ObjectID

CommentType type of comment CHAR(50) -

Comment comment itself TEXT -

LIST
TAXON
AVAILABILITY availability of a name AvailabilityID OID INTEGER system given

Availability availability of the name CHAR(25) -

Available availability in boolean BOOLEAN Y/N

LIST
TAXON
AVAILABILITY envrionment descriptor with its hierarchy EnvironID OID INTEGER system given

EnvrionmentType type of the environment CHAR(50) -

ParentEnvironID pointer to parent ENVIRON INTEGER EnvironID

LISTWORDTYPE type of a word WordtypeID OID INTEGER system given

WordType type of the word CHAR(50) -

LISTLANGUAGE language lookup table Language language CHAR(50) -

LISTMODE list of mode in STATUSMODE ModeID OID INTEGER system given

ModeName name of the mode TEXT -

Abbreviation abbreviation of mode name CHAR(5) -

LISTSTATUS list of status in STATUSMODE StatusID OID INTEGER system given

StatusName name of the status TEXT -

Abbreviation abbreviation of status name CHAR(5) -

EDITLOG transaction tracker EditLogID OID INTEGER system given

TableName name of modified table CHAR(50) -

FieldName name of modified field CHAR(50) -

RecordID pointer to either TAXONNAME, REFERENCE, REFSERIES, OBJECT, or primary key field of any user-editable table INTEGER TaxonNameID
ReferenceID
RefSeriesID
ObjectID etc.

PreviousValue previous contents TEXT -

Editor who modified the record INTEGER AgentID

Date date of modification DATE, but must be
TIMESTAMP -

Please send your feedback to: support@nomencurator.org