extending the biogeographical model africamuseum 6 (7?) june 2013
DESCRIPTION
Fields for classification Add fields to ‘species’ table for the main ranks: Kingdom Phylum (=division) Class Order Family Alternative: separate table Split taxonomy in genus/species, and family and above Problem: not properly normalised, not good solution for long-term management of taxonomic registerTRANSCRIPT
![Page 1: Extending the biogeographical model Africamuseum 6 (7?) June 2013](https://reader036.vdocuments.us/reader036/viewer/2022062905/5a4d1ada7f8b9ab059974552/html5/thumbnails/1.jpg)
Extending the biogeographical model
Africamuseum6 (7?) June 2013
![Page 2: Extending the biogeographical model Africamuseum 6 (7?) June 2013](https://reader036.vdocuments.us/reader036/viewer/2022062905/5a4d1ada7f8b9ab059974552/html5/thumbnails/2.jpg)
Workplan
Continue with database constructed so far Add fields for
Authority (name of the author, date, parentheses)
Classification Fields for different ranks
Synonymy Sources
![Page 3: Extending the biogeographical model Africamuseum 6 (7?) June 2013](https://reader036.vdocuments.us/reader036/viewer/2022062905/5a4d1ada7f8b9ab059974552/html5/thumbnails/3.jpg)
Fields for classification Add fields to ‘species’ table for the main ranks:
Kingdom Phylum (=division) Class Order Family
Alternative: separate table Split taxonomy in genus/species, and family and above
Problem: not properly normalised, not good solution for long-term management of taxonomic register
![Page 4: Extending the biogeographical model Africamuseum 6 (7?) June 2013](https://reader036.vdocuments.us/reader036/viewer/2022062905/5a4d1ada7f8b9ab059974552/html5/thumbnails/4.jpg)
Add field for authority
Add field for authority in the species table One text field, not structured further Too complex to be split up in separate
fields Optionally, add field for reference to
original description Ideally as a link to the sources table
![Page 5: Extending the biogeographical model Africamuseum 6 (7?) June 2013](https://reader036.vdocuments.us/reader036/viewer/2022062905/5a4d1ada7f8b9ab059974552/html5/thumbnails/5.jpg)
Add table sources
Fields ID (=primary key, autonumber) Author Year Reference
![Page 6: Extending the biogeographical model Africamuseum 6 (7?) June 2013](https://reader036.vdocuments.us/reader036/viewer/2022062905/5a4d1ada7f8b9ab059974552/html5/thumbnails/6.jpg)
Relations with sources table
Add ‘SourcesID’ link in Species table Add ‘SourcesID’ link in Distribution
table
Create relations between Sources and Species table Sources and distribution table
![Page 7: Extending the biogeographical model Africamuseum 6 (7?) June 2013](https://reader036.vdocuments.us/reader036/viewer/2022062905/5a4d1ada7f8b9ab059974552/html5/thumbnails/7.jpg)
Add synonymy ‘Self-referential’ table
Record in table refers to other record in same table Needs status of the taxon described in the record
Create new fields: AcceptedID TaxonStatus (optionally link to separate table)
Create relation: AcceptedID link to ID of taxon table
![Page 8: Extending the biogeographical model Africamuseum 6 (7?) June 2013](https://reader036.vdocuments.us/reader036/viewer/2022062905/5a4d1ada7f8b9ab059974552/html5/thumbnails/8.jpg)
Relations diagram - synonyms
![Page 9: Extending the biogeographical model Africamuseum 6 (7?) June 2013](https://reader036.vdocuments.us/reader036/viewer/2022062905/5a4d1ada7f8b9ab059974552/html5/thumbnails/9.jpg)
One-to-many relationships
E.g. species in family: each species belongs to one, and only one family
Implement by listing the Primary Key of the ‘one’ side as a field in the table of the ‘many’ side
‘Foreign Key’ points at the right record in the ‘one’ side of the relationship
![Page 10: Extending the biogeographical model Africamuseum 6 (7?) June 2013](https://reader036.vdocuments.us/reader036/viewer/2022062905/5a4d1ada7f8b9ab059974552/html5/thumbnails/10.jpg)
One-to-many relationships
(Many) species in (one) family
(Many) places/localitiesin (one) country
![Page 11: Extending the biogeographical model Africamuseum 6 (7?) June 2013](https://reader036.vdocuments.us/reader036/viewer/2022062905/5a4d1ada7f8b9ab059974552/html5/thumbnails/11.jpg)
Many-to-many relationships?
Problem: we don’t have a ‘one’ side, there is no single record to refer to
Can not be implemented directly in SQL Needs extra table
To split up many-to-many in two one-to-many relations
Extra table takes pointers to both sides of the relationship
![Page 12: Extending the biogeographical model Africamuseum 6 (7?) June 2013](https://reader036.vdocuments.us/reader036/viewer/2022062905/5a4d1ada7f8b9ab059974552/html5/thumbnails/12.jpg)
Many-to-many relationship
Example: ‘sources’ versus ‘species’ Source = literature, report, database…
One source refers to several species A species is referred to in many sources Create many-to-many table
Split the one many-to-many relationship in two one-to-many relationships
![Page 13: Extending the biogeographical model Africamuseum 6 (7?) June 2013](https://reader036.vdocuments.us/reader036/viewer/2022062905/5a4d1ada7f8b9ab059974552/html5/thumbnails/13.jpg)
Many-to-many relationship
![Page 14: Extending the biogeographical model Africamuseum 6 (7?) June 2013](https://reader036.vdocuments.us/reader036/viewer/2022062905/5a4d1ada7f8b9ab059974552/html5/thumbnails/14.jpg)
Relations diagram - complete
![Page 15: Extending the biogeographical model Africamuseum 6 (7?) June 2013](https://reader036.vdocuments.us/reader036/viewer/2022062905/5a4d1ada7f8b9ab059974552/html5/thumbnails/15.jpg)
Present structure: Philosophy
Structure has to be as simple as possible But not any simpler!! Present structure is good short-term
solution, but not long-term Needs better alternative to represent
classification and hierarchy
![Page 16: Extending the biogeographical model Africamuseum 6 (7?) June 2013](https://reader036.vdocuments.us/reader036/viewer/2022062905/5a4d1ada7f8b9ab059974552/html5/thumbnails/16.jpg)
Hierarchy: flat table
Every rank in the hierarchy is represented by a field in the table
Simplest solution Easy to create Easy to query
![Page 17: Extending the biogeographical model Africamuseum 6 (7?) June 2013](https://reader036.vdocuments.us/reader036/viewer/2022062905/5a4d1ada7f8b9ab059974552/html5/thumbnails/17.jpg)
Hierarchy: flat table
![Page 18: Extending the biogeographical model Africamuseum 6 (7?) June 2013](https://reader036.vdocuments.us/reader036/viewer/2022062905/5a4d1ada7f8b9ab059974552/html5/thumbnails/18.jpg)
Hierarchy: flat table
Problems not normalised!
Not a real problem if a quick-and-dirty solution is all that is needed
Difficult to maintain hierarchy in the long term
‘Standard’ problems with non-normalised database Possible conflicting information, inefficient storage…
Cfr MASDEA; too simple
![Page 19: Extending the biogeographical model Africamuseum 6 (7?) June 2013](https://reader036.vdocuments.us/reader036/viewer/2022062905/5a4d1ada7f8b9ab059974552/html5/thumbnails/19.jpg)
Hierarchy: normalised tables
Every rank is represented by a separate table
Not very difficult to write a query to regenerate flat table
Every taxon can have additional information Extra fields with description…
![Page 20: Extending the biogeographical model Africamuseum 6 (7?) June 2013](https://reader036.vdocuments.us/reader036/viewer/2022062905/5a4d1ada7f8b9ab059974552/html5/thumbnails/20.jpg)
Hierarchy: cascading tables
![Page 21: Extending the biogeographical model Africamuseum 6 (7?) June 2013](https://reader036.vdocuments.us/reader036/viewer/2022062905/5a4d1ada7f8b9ab059974552/html5/thumbnails/21.jpg)
![Page 22: Extending the biogeographical model Africamuseum 6 (7?) June 2013](https://reader036.vdocuments.us/reader036/viewer/2022062905/5a4d1ada7f8b9ab059974552/html5/thumbnails/22.jpg)
![Page 23: Extending the biogeographical model Africamuseum 6 (7?) June 2013](https://reader036.vdocuments.us/reader036/viewer/2022062905/5a4d1ada7f8b9ab059974552/html5/thumbnails/23.jpg)
Hierarchy: normalised tables
Avantages Easy to maintain and query Normailised, possible to add information at any level
of the hierarchy Drawbacks
Ranks are hard-wired on the structure of the database New rank would require change of the structure of the
database And probably of the user interface, web interface…
Number of tables Lot of functionality duplicated
![Page 24: Extending the biogeographical model Africamuseum 6 (7?) June 2013](https://reader036.vdocuments.us/reader036/viewer/2022062905/5a4d1ada7f8b9ab059974552/html5/thumbnails/24.jpg)
Taxonomic reality Ranks used depend on the taxonomic group
Botany: mainly infra-specific; zoology: mainly on higher levels
Many of the ranks are only sparsely used Needs for a more flexible system Much of the functionality is the same across
all ranks ‘parent’, synonymy Authority, description…
![Page 25: Extending the biogeographical model Africamuseum 6 (7?) June 2013](https://reader036.vdocuments.us/reader036/viewer/2022062905/5a4d1ada7f8b9ab059974552/html5/thumbnails/25.jpg)
‘Open Hierarchy’ Possible to define new ranks without having
to rewrite the structure of the database All taxonomic names are stored in a field in
a single table; other fields indicate parent and rank
Many-to-one relation: a single parent, several descendants Include ID of parent in the record of the
descendant
![Page 26: Extending the biogeographical model Africamuseum 6 (7?) June 2013](https://reader036.vdocuments.us/reader036/viewer/2022062905/5a4d1ada7f8b9ab059974552/html5/thumbnails/26.jpg)
![Page 27: Extending the biogeographical model Africamuseum 6 (7?) June 2013](https://reader036.vdocuments.us/reader036/viewer/2022062905/5a4d1ada7f8b9ab059974552/html5/thumbnails/27.jpg)
![Page 28: Extending the biogeographical model Africamuseum 6 (7?) June 2013](https://reader036.vdocuments.us/reader036/viewer/2022062905/5a4d1ada7f8b9ab059974552/html5/thumbnails/28.jpg)
Open Hierarchy Avantages
Completely normalised Flexible
Drawbacks Difficult to query classification
Queries of the type ‘all species of the Echinodermata’… Solution:
Recursive query ‘calculated field’ program
![Page 29: Extending the biogeographical model Africamuseum 6 (7?) June 2013](https://reader036.vdocuments.us/reader036/viewer/2022062905/5a4d1ada7f8b9ab059974552/html5/thumbnails/29.jpg)
Synonymy
Every taxon can have several synonyms; in principle, only one valid name for any synonym Many-to-one relation: one valid name,
many synonymous names Include ID of the valid name in the record
of the synonymous nameOther fields for the type of synonymy…
![Page 30: Extending the biogeographical model Africamuseum 6 (7?) June 2013](https://reader036.vdocuments.us/reader036/viewer/2022062905/5a4d1ada7f8b9ab059974552/html5/thumbnails/30.jpg)
![Page 31: Extending the biogeographical model Africamuseum 6 (7?) June 2013](https://reader036.vdocuments.us/reader036/viewer/2022062905/5a4d1ada7f8b9ab059974552/html5/thumbnails/31.jpg)
Rest of the taxonomic model
Ranks should be in a separate table Information on the level of the rank
can be added Possibility of extra quality control
Rank of a parent as compared to rank of descendants
Rank of siblings should be same
![Page 32: Extending the biogeographical model Africamuseum 6 (7?) June 2013](https://reader036.vdocuments.us/reader036/viewer/2022062905/5a4d1ada7f8b9ab059974552/html5/thumbnails/32.jpg)
![Page 33: Extending the biogeographical model Africamuseum 6 (7?) June 2013](https://reader036.vdocuments.us/reader036/viewer/2022062905/5a4d1ada7f8b9ab059974552/html5/thumbnails/33.jpg)
Combine with rest
Previous work on geography and sources