Tanto si é parlato sul forum di come costruire un database per la nostra collezione e, in generale, per i dati che possediamo per i nostri gruppi di studio. Negli ultimi mesi ci ho riflettutto parecchio, e dopo non poco lavoro, sono arrivato ad una soluzione piú che soddisfacente e a una serie di riflessioni su come organizzare i dati. Ho quindi deciso di proporre queste riflessioni agli amici del forum, e proporre una soluzione alla portata di tutti.
Questa discussione parte da 0, ovvero da cos'é un database e a cosa serve, in modo che sia utile a tutti.
Cos'é un databaseUn database é un sistema per raccogliere dati in maniera standardizzata e sistematica. Oggi, tra i tipi di database piú utilizzati sono i cosiddetti database relazionali, che usano tabelle per registrare i dati. Le diverse tabelle sono poi collegate da delle chiavi, che sono codici unici che identificano una riga di una particolare tabella. Un esempio di questo funzionamento:
Tabella 1id link_id localita
1 sp1 Milano
2 sp1 Bergamo
3 sp2 Monte Cucco
4 sp2 Lago di Bolsena
Tabella 2id specie autore_anno
sp1 Cetonia aurata (Linnaeus, 1758)
sp2 Potosia cuprea (Fabricius, 1775)
La prima tabella contiene le localitá per ogni esemplare in collezione, mentre la seconda tabella contiene le specie in collezione con il loro nome, l'autore e l'anno. Per esempio, la prima riga della tabella 1 (id = 1) ha come "localita" il valore "Milano" e come "link_id" il valore "sp1", che nella tabella 2 corrisponde a "Cetonia aurata" per il campo "specie" e "Linnaeus, 1758" per il campo "autore_anno".
In questo modo, non devo ripetere il campo "specie" e "autore_anno" 4 volte nella prima tabella, ma mi basterá collegare ogni record alla specie corrispondente. In questo piccolo esempio non sembra un gran vantaggio, ma immaginate un database di migliaia di righe in cui, ogni volta che inseriamo un esemplare dobbiamo ripetere tutta la tassonomia, per esempio "Insecta, Coleoptera, Cerambycidae, Lamiinae, Pogonocherini, Pogonocherus, Pogonocherus hispidus (Linnaeus, 1758)".
Chiariamo peró alcuni aspetti.
Database: é una struttura per inserire e maneggiare i dati, in pratica, una serie di tabelle (o anche una sola tabella, se il database é molto semplice). Non é un programma, é un tipo particolare di file.
Interfaccia grafica per database: é una interfaccia grafica che permette di visualizzare il database (il file di cui parlavamo prima) e di modificarlo.
Queste due componenti sono distinte ed é utile che sia cosí, per minimizzare la possibilitá di eliminare dati o di corromperli.
Quali caratteristiche deve avere un database in generale?Le caratteristiche di un buon database sono
1) flessibilità: deve poter contenere tutte le informazioni che ci servono
2) minimizzare la ridondanza dei dati: deve evitare che le stesse informazioni siamo ripetute piú e piú volte
3) robustezza: deve minimizzare la probabilitá di corrompere i dati o di danneggiarli
4) durevolezza: deve minimizzare il tempo richiesto per mantenere il database, o comunque ridurre al minimo il lavoro richiesto per tenerlo aggiornato
A noi entomologi cosa interessa?Per quello che posso vedere io, ci interessano 3 cose:
1) Poter registrare i dati per gli esemplari che abbiamo nella nostra collezione, per sapere che specie abbiamo, di quali località, quanti esemplari per specie, etc.
2) Registrare i dati degli esemplari che studiamo, e che poi restituiamo. Nel mio caso, quando determino una scatola di Cerambycidae o Oedemeridae, mi segno tutti i dati, perché in futuro, se dovró fare una pubblicazione sulla specie x, sapró che ci sono x esemplari in tal collezione e i dati raccolti andranno ad arricchire le conoscenza sulla distribuzione di quel taxon.
3) Tenere traccia dei nomi validi e dei sinonimi per il nostro gruppo di studio.
Quali caratteristiche dovrebbe avere una soluzione di database nel nostro caso?Gli ingredienti sono semplici (ma allo stesso tempo non lo sono):
1) Un file di database che sia semplice, veloce da leggere e durevole nel tempo. Nessuno vuole spendere mesi a crearlo per poi dover ricominciare tutto da capo qualche anno dopo perché il sistema diventa obsoleto.
2) Un programma gratuito, open source e facile da utilizzare per visualizzare il database. Open source significa che il codice é disponibile per chi vuole modificarlo, il che garantisce che il programma potrá continuare a vivere per molto tempo. Non tutti i programmi gratuiti sono open-source, attenzione!
3) Un programma facile da utilizzare
1) Un sistema di database che sia semplice, veloce e durevole
La soluzione che ho individuato io é SQLite:
https://it.wikipedia.org/wiki/SQLiteÉ un linguaggio per costruire e maneggiare database open source tra i piú utilizzati al mondo. É quindi assai improbabile che nei prossimi anni cada in disuso, e quindi che il nostro database diventi obsoleto.
2) Un programma gratuito, open source e facile da utilizzare per visualizzare il database
DBBrowser for SQLite:
https://sqlitebrowser.org/Permette di creare, modificare e visualizzare il database. É open source e gratuito, e in piú gira su tutti i sistemi operativi piú comuni: mac, windows, linux.
Come organizzare il database?Nel mio caso, ho 3 tabelle.
Records: tutti i dati che registro e che provengono dalla mia collezione o da qualsiasi altra fonte: materiale determinato, esemplari del forum, citazioni bibliografiche. Ogni riga della tabella corrisponde a una combinazione unica di chiave del taxon (un identificativo unico di quel taxon), localitá, data, raccoglitore e collezione di deposito.
Per esempio, queste due righe sono separate perché la data di raccolta é diversa. Visto che nelle collezioni un esemplare non é identificato da un codice unico, di solito, é inutile inserire una riga ad esemplare. Possiamo invece inserire una riga per ogni serie di esemplari che siano nello stesso posto (collezione), raccolti dalla stessa persona, nella stessa località e nella stessa data.
chiave_taxon località data legit collezione
12174952 Mt. Cimone 1998-06-10 L. Saltini coll. Saltini
12174952 Mt. Cimone 2003-07-19 L. Saltini coll. Saltini
Collezione: la mia collezione, dove ogni esemplare ha un codice univoco. Qui, tutti i campi si possono ripetere tranne l'identificativo.
Per questa tabella l'unica cosa che indico é dov'é l'esemplare nella collezione, come é conservato e se al campione è stato sequenziato il DNA.
id linking_id preservation sequencing
RP24.001 123 alcol 96 COI
RP245.002 123 dry NA
Voi direte, ma come? Non indichi la località e la data e chi l'ha raccolto? Attenzione, perché qua il meccanismo diventa un po' piú complesso. Visto che gli esemplari della mia collezione sono anche loro una fonte di dati sulla distribuzione, fenologia e biologia delle specie rappresentate, sono anche loro inclusi nella tabella "Records". Nella tabella "Records", ovviamente, una riga sarà una serie di esemplari presi lo stesso giorno, dalla stessa persona, etc., mentre nella tabella "Collezione" ogni esemplare ha una riga a parte. Queste due tabelle possono essere collegate dal campo "linking_id", che, per ogni riga della tabella "Collezione" punta a una riga della tabella "Records". Nell'esempio sopra, vedete che il linking_id é lo stesso, infatti, entrambi gli esemplari puntano alla stessa riga nella tabella "Records", il che vuol dire che sono entrambi due esemplari presi nello stesso giorno da me a Torre Maina.
chiave_taxon località data legit collezione
4458531 Torre Maina 2024-06-02 R. Poloni coll. Poloni
Tassonomia: Una terza tabella include i nomi dei taxon, l'autore, famiglia, etc. etc. Un esempio qui sotto:
chiave_taxon famiglia genere nome_sci autore_anno
4458531 Oedemeridae Oedemera Oedemera flavipes (Fabricius, 1792)
12174952 Cerambycidae Rhagium Rhaguum inquisitor (Linnaeus, 1758)
Ovviamente questa struttura puó essere modificata, ma costituisce un'architettura solida e semplice per un database ad uso entomologico.
Cosa inserire nel database?I campi da inserire, ovviamente, variano a seconda delle nostre esigenze, ma nel mio caso ho creato un sistema che permette di gestire i dati che interessano a noi con una certa flessibilitá. I nomi delle colonne delle tabelle e la formattazione del contenuto delle celle segue gli standard Darwin Core Archive:
https://dwc.tdwg.org/, uno standard internazionale per registrare i dati sulla biodiversità. All'inizio puó sembrare un'inutile complicazione dover chiamare, per esempio, il campo "data di raccolta" "eventDate", e dover formattare la data cosí: "2019-06-14", peró il vantaggio di questo standard internazionale é che i nostri dati saranno giá pronti per essere usati in una pubblicazione, per esempio, o per essere condivisi, proprio perché seguono uno standard internazionale riconosciuto.
Come posso visualizzare i dati?Questa é la parte piú complicata. Per quello che serve a me, ho sviluppato un'interfaccia grafica in R, che fondamentalmente permette di filtrare il database, selezionando per esempio una sola specie, o un solo stato, e visualizzare i dati filtrati in 3 forme: 1) una mappa dove ogni punto é un record del database, su cui posso cliccare per vedere il nome della località, 2) un pannello dove posso vedere la fenologia per la/le specie selezionate, 3) una tabella filtrata che riassume la tabella della collezione o quella dei record.
Potete vedere a cosa assomiglia a questo link, dove c'é un esempio del programma che legge un semplice foglio di calcolo usato in una pubblicazione di un paio di anni fa, che contiene i dati di distribuzione del genere Stenostoma:
https://r-poloni.github.io/exabase_visualiser/ . Ci vogliono circa 30 secondi per caricarla, perché girando completamente sul browser, il programma é lento. Sto cercando di sviluppare qualcosa di utilizzabile su windows e mac, sotto forma di un file eseguibile, ma mi ci vorrà un po' di tempo.
Per creare e riempire il database, non servono capacitá informatiche particolari, mentre per installare questa interfaccia grafica, almeno per ora, bisogna avere un minimo di basi di programmazione, perché l'interfaccia gira su R. Se peró qualcuno vuole provare a installarla sul proprio pc, gli daró una mano volentieri.
Andiamo alla pratica, come creare il databaseIo, come dicevo uso questo schema:
Tabella "Records""id" -- identificativo univoco di una riga (potete vederlo come il numero di riga di excel)
"usageKey" -- id univoco per un taxon, in molti casi corrisponde all'usageIKey di GBIF (
https://discourse.gbif.org/t/understand ... eskey/3045)
"identifiedBy" -- chi ha identificato l'esemplare? Cognome o nome e cognome
"dateIdentified" -- quando l'ha identificato? La data segue il formato descritto piú in basso, io generalmente metto solo l'anno.
"identificationQualifier" -- cf o cfr.
"typeStatus" -- "paratypus" o "neotypus" etc. se gli esemplari in questione sono tipi
"num_m" -- numero di maschi
"num_f" -- numoero di femmine
"num_nosex" -- numero senza sesso identificato
"num_mol" --quanti esemplari di questa serie sono disponibili per analisi molecolari
"countryCode" -- nazione di raccolta / foto / citazione
"stateProvince" -- regione o provincia
"locality" -- localitá, tutto quello che non é stato o regione amministrativa
"elevation" -- altitudine in metri sul livelo del mare
"verbatimLatitude" -- latitudine, verbatim, come sul cartellino
"verbatimLongitude" -- longitudine, verbatim, come sul cartellino
"decimalLatitude" -- latitudine in gradi decimali, convertita dal formato dei due campi precedenti o anche derivata dalla localitá
"decimalLongitude" -- longitudine in gradi decimali, convertita dal formato dei due campi precedenti o anche derivata dalla localitá
"eventDate" -- data di raccolta / foto / osservazione etc.
"recordedBy" -- raccoglitore / fotografo etc.
"biog_reg" -- regione biogeografica
"institutionID" -- collezione che ospita l'esemplare oppure articolo da cui proviene il dato
"basisOfRecord" -- che tipo di record é? Questo campo puó assumere solo questi valori: "PreservedSpecimen" OR "basisOfRecord" = "HumanObservation" OR "basisOfRecord" = "MaterialCitation"
"notes" TEXT, -- note, come tecnica di raccolta o annotazioni sull'habitat
Tabella collezione"collection_id" -- ID univoco
"linking_id" -- ID nella tabella "Records" contenente le informaziono su località, raccoglitor etc.
"sex" -- sesso dell'esemplare
"notes" -- note, tutto quello che non sta negli altri campi
"lifeStage" -- stadio vitale, puó assumere i seguenti valori: "lifeStage" = "adult" OR "lifeStage" = "larva" OR "lifeStage" = "pupa" OR "lifeStage" = "egg"
"bodypart" -- parte del corpo conservata
"preservation" -- Stile di conservazione, per esempio alcol 96, a secco, etc.
"localisation" TEXT, -- luogo dov'é l'esemplare (numero di scatola, per esempio)
Tabella tassonomia"usageKey" -- ID unico di un taxon
"order"
"family"
"subfamily"
"tribe"
"genus"
"species"
"canonicalName" -- corrisponde al nome scientifico senza autore e anno
"authorship" -- autore e anno
"scientificName" -- nome + aiutore e anno
"rank" -- rango tassonomico (specie, sottospecie, famiglia, etc.)
"isInGBIF" -- c'é in GBIF? 0 = assente 1 = presente
Per crearne uno seguendo questo schema, seguite questi passi:1) Aprite DB Browser for SQLite e cliccate su "new database" (pulsante in alto a sinitra)
2) Vi si aprirá una finestra in cui il programma vi chiede di salvare il database con un nome.. Vi consiglio di salvarlo in una cartella in Documenti o sulla vostra pagina di avvio.
3) Ora vi si aprirà una pagina che vi chiede di creare una nuova tabella, per ora cliccate su "cancel"
4) Cliccate su "execute SQL", che vi permette di eseguire un codice SQL che creerá la struttura del database.
5) Incollate queste righe di codice ( (in gergo tecnico, data definition language) nello spazio evidenziato in rosso, poi cliccate su "esegui" (il piccolo triangolo evidenziato)
Codice:
-- INSTRUCTIONS TO FILL THE DATABASE
-- The fields with "NON NULL" flag must be filled, so genus, species, locality, eventDate and institutionID
-- create a unique combination of a species found at a place at a given moment and preserved in a given institution, that
-- represents the unit of the database (a record).
-- The "records" table is the master table, and has a unique ID (id) which is automatically generated that is used in the table
-- "collection" to link the multiple specimens present in alcol collection to one record in the "records" table.
-- Another table, taxonomy, contains the taxonomical data for each name usage identified with a usageKey: family, order, author etc.
-- By filling the cells, the " ' " (apostrophe) character should be avoided because it is read by sqlite as a separator. Instead
-- use a space or an exterisk that will not hamper the meaningfulness of the locality.
-- Below a description of the fields
CREATE TABLE IF NOT EXISTS "records" (
"id" INTEGER UNIQUE, -- automatically generated
"usageKey" TEXT, -- taxonomy, unique numeric key for this taxon in gbif backbone taxonomy, used to retrieve author and higher taxonomy
"identifiedBy" TEXT, -- who identified the specimen as belonging to the species reported
"dateIdentified" TEXT, -- date of identification
"identificationQualifier" TEXT, -- determiner's doubts about the identification, e.g. cfr.
"typeStatus" TEXT, -- fill if the specimen is a type (either holotypus, paratypus, allotypus etc.)
"num_m" INTEGER, -- number of males for this record
"num_f" INTEGER, -- number of females
"num_nosex" INTEGER, -- number of specimens for which the sex cannot be identified
"num_mol" INTEGER, --number of samples available for molecular analysis
"countryCode" TEXT, -- nation where the species was recorded, alpha-2 code according to ISO 3166
"stateProvince" TEXT, -- region or province
"locality" TEXT NOT NULL, -- locality, i.e. all the toponyms not associated with a state or administrative region
"elevation" INTEGER, -- altitude expressed in metres above sea level
"verbatimLatitude" TEXT, -- latitude, verbatim as in the specimen label or exif data
"verbatimLongitude" TEXT, -- longitude, verbatim as in the specimen label or exif data
"decimalLatitude" TEXT, -- latitude, converted from verbatimLatitude or given based on toponym, always in decimal degrees
"decimalLongitude" TEXT, -- longitude, converted from verbatimLongitude or given based on toponym, always in decimal degrees
"eventDate" TEXT NOT NULL, -- date of collection of the specimen / photo / observation
"recordedBy" TEXT, -- collector or photographer or observed
"biog_reg" TEXT, -- biogeographic region associated to the record
"institutionID" TEXT NOT NULL, -- source, either museum or collection for specimens or site or other source
"basisOfRecord" TEXT CHECK("basisOfRecord" = "PreservedSpecimen" OR "basisOfRecord" = "HumanObservation" OR "basisOfRecord" = "MaterialCitation"), -- either specimen, photo or observation
"notes" TEXT, -- all that does not fit into the other fields, like habitat or the collecting technique (e.g. UV light, funnel trap)
PRIMARY KEY("id" AUTOINCREMENT),
UNIQUE (usageKey, locality, eventDate, recordedBy, institutionID),
FOREIGN KEY (usageKey) REFERENCES taxonomy(usageKey)
);
CREATE TABLE IF NOT EXISTS "collection" (
"collection_id" TEXT UNIQUE, -- ID of the specimen in the alcol collection
"linking_id" INTEGER, -- ID of the record in "records" containing the occurrence data of the specimen
"sex" TEXT, -- sex of the specimen if known, otherwise NA
"notes" TEXT, -- all that does not fit into the other fields
"lifeStage" TEXT CHECK("lifeStage" = "adult" OR "lifeStage" = "larva" OR "lifeStage" = "pupa" OR "lifeStage" = "egg"),
"bodypart" TEXT, -- bodypart preserved (e.g whole body, or hind left leg)
"preservation" TEXT, -- either ethanol 96, ethanol 70, snap frozen, collection (dry)) or collection (fixed in ethanol)
"localisation" TEXT, -- place where the specimen is preserved
PRIMARY KEY("collection_id"),
FOREIGN KEY (linking_id) REFERENCES records(id)
);
CREATE TABLE IF NOT EXISTS "taxonomy" (
"id" INTEGER UNIQUE, -- unique, automatically generated
"usageKey" TEXT UNIQUE, -- unique key for this taxon in gbif backbone taxonomy, used to retrieve author and higher taxonomy. If added manually is preceded by a "m"
"order" TEXT, -- taxonomy, order of the species
"family" TEXT, -- taxonomy, family of the species
"subfamily" TEXT, -- taxonomy, subfamily of the species
"tribe" TEXT, -- taxonomy, tribe of the species
"genus" TEXT, -- taxonomy, genus
"species" TEXT, --taxonomy, species name (genus + species)
"canonicalName" TEXT, -- taxonomy, full scientific name without authorship
"authorship" TEXT, -- taxonomy, authorship associated to the name usage (scientificName)
"scientificName" TEXT, -- taxonomy, full scientific name wit authorship that creates a name usage in gbif
"rank" TEXT, -- rank of the name usage, i.e. in our case species or subspecies
"isInGBIF" INTEGER CHECK("isInGBIF" = 0 OR "isInGBIF" = 1), -- binary, 0 or 1, 1 = name usage present in gbif, 0 = not present (added manually)
PRIMARY KEY("id" AUTOINCREMENT),
UNIQUE(usageKey)
);
Ora, non vi resta che cliccare su "write changes" per salvare le modifiche.
Ora avete un database pronto per essere riempito. Per chi programma, si possono fare, come ho fatto io, degli script su python che importano i dati da un foglio excel, riformattando le celle e unendo / dividendo i campi quando necessario.
Per chi non programma, si puó comunque importare un file csv e ci sono opzioni per riformattare i dati una volta inseriti, e si possono inserire i dati man mano come su un foglio excel, con il vantaggio di avere la flessibilità e le possibilità di un database in SQLite.
Buon lavoro
