Geospatial Database System for Managing State Botanical Collection
Locality Information
1. Project Team
ShiqiangRen1348969 GuanziLiu1329242
HaonanLiu1419167 ZixuanXiao1132915
2. Summary of requirements
2.1Projectrationale
TheNationalHerbariumofVictoriaisanimportantpartoftheRoyalBotanicGardensofVictoria
andhasthelargestherbariuminOceania.Thiscollectionisvitalforthestudyandconservationof
plantbiodiversityinAustraliaandglobally,butcurrentlimitationsinmanaginggeospatialdata
preventusfromusingthesedatasetseffectivelyforresearch.Intheabsenceofacomprehensive
databasesystemcapableofprocessingcomplexgeospatialdataandensuringhigh-qualitydata
management,weneedtobuildageospatialdatabasethatwillsupportaccuratedataentry,
managementandanalysiscapabilities,accurateandreliablegeospatialdataisnotonlyessential
forresearchbutalsoguaranteestheprincipleofIndigenousdatasovereignty.
Ourprojectobjectiveistodevelopadedicatedgeospatialdatabasesystemtoimprovethe
efficiencyofdatamanagementandresearchutilisationofthecollectionsoftheNationalBotanic
GardensofVictoria.First,thesystemwillensuretheaccuracyofthegeoreferencedata,enabling
accuratemappingofspecimenlocationsandtheiroriginalcollectionconditions.Second,the
databasewillintegratemultipledatasourcestoensurethatlocationdatameetsinternational
standardsandsupportsdataexchangeandinteroperability.Inaddition,thesystemwillmanage
sensitiveculturalandscientificdatacontainingindigenousculturalknowledge,allowing
controlledaccessbasedonthesensitivityandoriginoftheinformation.Powerfulsearchand
analysistoolsarealsoavailable,allowingresearcherstoextractdetailedreportsonthegeographic
distributionofspecies,assessenvironmentalimpacts,andsupportconservationefforts.Finally,
thedatabaseprojectwillalsohelptheArboretummanageitsvaluablebiologicalcollections,
enhancescientificresearch,andsupporttheconservationandrespectfulprocessingofindigenous
knowledge.
2.2Projectscope
2.2.1Recordingcapability
Intherecordingfunctionpart,ourdatabasewillbeabletorecordandmanagegeolocation
dataindetail,supportconversionfromtextdescriptionstocoordinates,andupdateand
verifygeocoding,whileprovidingclearinformationongeocodingsourcesandmethods.
Inaddition,thedatabaseneedstomaintainthecontextofthedataandsupportthe
identificationandmanagementofrecordscontainingIndigenousculturalknowledgeto
ensurethattheaccuracyandculturalsensitivityofthedataarefullyrespected.Thiswill
providestrongsupportforscientificresearchanddatamanagement.
2.2.2Search/retrievalfunctionality
Thispartisdesignedtoprovidepowerfulqueryandanalysistoolsthatenableusersto
searchrecordsforspecificsitesbyspeciesname,supportenvironmentalscientistsin
analyzingintervalchanges,helpplanconservationsurveysofendangeredplants,and
allowpublicuserstoaccessspecieslistingsforspecificsites.Together,thesecapabilities
improvetheavailabilityofdataandtheefficiencyofresearchandpublicengagement.
2.2.3OperatorandEquipmentManagement
Thesefeaturesaredesignedtoimproveproductivityandmanagementtransparency,
includingdetailedrecordingofemployeeinformation,planningfieldtrips,tracking
historicaltripinformation,andmanagingemployeetraining.Thesefeaturesnotonly
ensurethesafetyandhealthofemployeesbutalsooptimizeresourceutilization,support
efficientprojectimplementationandguaranteedataquality.
2.2.4Outofscope
Toensuredatasecurity,wewillnotimplementthecreationandmodificationofdatabase
useraccessrightsinthisproject.However,youcancontrolaccessbydefiningrolesand
permissions,usingGRANTandREVOKEstatements,enforcingrow-levelsecurity
policies,creatingviewstorestrictdataaccess,andenforcingdataencryptionandsecurity
measures.Thesemethodshelpensurethesecurityofthedatabase.
2.3Anticipatedusesand usertypes
Stakeholders Whattheyusethesystemto do
Collector
Tocollectspecimens,thegeographicallocationandotherinformationof
existingspecimenswereinquired.
Employee
Collectandmanagetheinformationinthedatabase.
Externaluser
Iftheyareinterestedinplantspecimens,theywillinquireaboutsomeofthe
contenttheyareinterestedin.
Researcher
Theywillusethedatatostudybiodiversity,andgeographyandassistin
calibratingtheaccuracyofthedatabase.
Indigenous
FortheprotectionandresearchofIndigenousKnowledge,thedataofplant
Knowledge
HolderGroup specimensrelatedtoindigenousarecalibrated.
3. Conceptual design
3.1Entityandrelationships
Entity Attributes Relationships Constraints
Specimen Collectdate CollectorCollectsSpecimen Cardinalityconstraint1:NforCollector:SpecimeninCollects
Ctelognumber ExpeditionprovidesSpecimen Cardinalityconstraint1:NforExpedition:SpecimeninProvides
Name SpecimenbelongstoSpecie Totalparticipationconstraint:everySpecimenmustbelongstoone
SpecimenrelatestoIndigenous Specie(N:1)
Knowledge CardinalityconstraintN:NforIndigenousKnowledge:Specimenin
SpecimeniscollectedatLocation Relatesto
Totalparticipationconstraint:everySpecimenmustbecollectedatleast
onelocation(N:N)
Collector Phone CollectorjoinsExpedition Totalparticipationconstraint:everyExpeditionmustconsistofatleast
Email CollectorCollectsSpecimen oneCollector(N:N)
Name Cardinalityconstraint1:NforCollector:SpecimeninCollects
Expedition Date CollectorjoinsExpedition Totalparticipationconstraint:everyExpeditionmustconsistofatleast
Description ExpeditionprovidesSpecimen oneCollector(N:N)
Destination Cardinalityconstraint1:NforExpedition:SpecimeninProvides
Species Common SpecimenbelongstoSpecie Totalparticipationconstraint:everySpecimenmustbelongstoone
name(multivalues) LocationofInterestscontains Specie(N:1)
Species CardinalityconstraintN:NforLocationofInterests:SpeciesinContains
Taxonomicname
Locationof Name LocationofInterestscontains CardinalityconstraintN:NforLocationofInterests:SpeciesinContains
Interests Address Species
Indigenous content SpecimenrelatestoIndigenous CardinalityconstraintN:NforIndigenousKnowledge:Specimenin
Knowledge Knowledge Relatesto
IndigenousKnowledgeHolder Totalparticipationconstraint:everyKnowledgemustbehold byone
GroupholdsIndigenousKnowledge Group(N:1)
Indigenous Name,Email,Phone IndigenousKnowledgeHolder Totalparticipationconstraint:everyKnowledgemustbehold byone
Knowledge GroupholdsIndigenousKnowledge Group(N:1)
Holder
Group
Location DarwinCoreStandard SpecimeniscollectedatLocation Totalparticipationconstraint:everySpecimenmustbecollectedatleast
GroudTruthingTripvisitsLocation onelocation(N:N)
Location,Geocode CardinalityconstraintN:NforGroudTruthingTrip:Locationinvisits
source
Geocodeacquisition
method
Ground Description GroundTruthingTripvisits CardinalityconstraintN:NforGroudTruthingTrip:Locationinvisits
Truthing Operatorid Location Totalparticipationconstraint:everyTrip mustconsistof atleastone
Trip Route,Date EmployeejoinsGroundTruthing Employee(N:N)
Trip CardinalityconstraintN:1forGroudTruthingTrip:Hotelinbooks
GroundTruthingTripbooksHotel CardinalityconstraintN:1forGroudTruthingTrip:Hospitaland
GroundTruthingTripgoesto Emergencyingoes
HospitalandEmergency
Employee Name,Phone EmployeejoinsGroundTruthing Totalparticipationconstraint:everyTrip mustconsistof atleastone
Email,Position Trip Employee(N:N)
Lasttrainingdate
Hotel Name,Address GroundTruthingTripbooksHotel CardinalityconstraintN:1forGroudTruthingTrip:Hotelinbooks
Phone,Email
Hospital Name,Address GroundTruthingTripgoesto CardinalityconstraintN:1forGroudTruthingTrip:Hospitaland
and Phone,Email,Type HospitalandEmergency Emergencyingoes
Emergency
TheERdiagramisprovidedinAppendix1.
3.2IdentifiedAmbiguities andCorrespondingSolutions
1.ShouldweaddexternaluserandresearcherentitiestotheERdiagram?
Solution:Afterresearchandgroupdiscussions,weconcludedthattheseentitiescannotbe
effectivelyconnectedwithotherentities.Theyshouldbetreatedasrolesmanagedthroughsystem
permissions,sowedecidednottoincludethemintheERdiagram.
2. Shouldthelocationdataforplacesofinterestbeincludedinthelocationtableora
newtable?
Solution:Thegroupdiscussedtheoriginaldesignpurposeandcontentofthelocationtable.
Consideringthatthelocationtableshouldonlycontainspecimencollectionlocations,wedecided
tocreateanewtableforplacesofinterest.
4. Logic design
4.1TablesandRelationships
● TheSpecimenTableholdsthecorebiologicaldata.Thecataloguenumberistheprimary
key.SpatialdataislinkedtotheLocationTableviathelocation_idforeignkey,whichisa
one-to-onerelationshipensuringeachspecimenhasaspecificlocation.Thecollector_id
andexpedition_idlinktheCollectorTableandtheExpeditionTable,respectively.
● TheLocationTableholdsDarwinCorestandardlocationdataaswellasotherspatial
data.Itdescribeswherethespecimenwascollected.
● TheSpecieTableholdsthetaxonomicnamesofspecimens.
● ThisSpecieCommonNameTableholdsallcommonnamesconnectedtotheSpecie
Tablewiththespecie_idforeignkey.
● TheCollectorTableholdsthenamesandcontactsofcollectorswhocollectedspecimens.
● TheExpeditionTableholdsthedateandspatialdataofexpeditions.Allspecimenswere
collectedduringthoseexpeditions.
● TheCollectorExpeditionTablelinkstheCollectorTableandtheExpeditionTableto
implementamany-to-manyrelationship.Thecombinationofcollector_idand
expedition_idisunique.Therefore,theyaretheprimarykey.
● TheLocationofInterestsTableholdsthenameandspatialdataofthelocationthatthe
publicusersmaybeinterestedin.
● TheSpecieLocationTablelinkstheSpecieTableandtheLocationofInterestsTableto
implementamany-to-manyrelationship.Thecombinationofspecie_idand
location_of_interests_idisuniquesotheyareprimarykeys.
● TheIndigenousKnowledgeHolderGroupTableholdsthenamesandcontactsof
IndigenousknowledgeholdergroupsinAustralia.
● ThisIndigenousKnowledgeTableholdsindigenousknowledgeofspecies.Ithasa
foreignkeyindigenous_knowledge_holder_group_ididentifyingwhichindigenous
knowledge-holdergroupholdsthisknowledge.
● ThisSpecieIndigenousKnowledgeTablelinkstheSpecieTableandtheIndigenous
KnowledgeTabletoimplementamany-to-manyrelationship.Thecombinationof
specie_idandindigenous_knowledgeisuniquesotheyaretheprimarykey.
● ThisEmployeeTableholdsthenames,positions,andcontactsofallemployees.The
positionofanemployeecanbecollectionmanager,dataqualitymanager,orother.
● ThisGroundTruthingTripTableholdsgroundtruthingtripdata,includingroutein
Geometrydatatype,closeshospitalandemergencylinkedtotheHospitalandEmergency
Tableviahospital_and_emergency_idforeignkey,andhotellinkedtotheHotelTablevia
hotel_idforeignkey.
● ThisEmployeeTripTablelinkstheEmployeeTableandtheGroundTruthingTripTable
toimplementamany-to-manyrelationship.Thecombinationofemployee_idand
ground_truthing_trip_idisuniquesotheyaretheprimarykey.
● ThisHotelTableholdsnames,contacts,andspatialdataofhotels,thatwerebooked
duringgroundtruthingtrips.
● ThisHospitalandEmergencyTableholdsnames,contacts,andspatialdataofhospitals
andemergencies.Theclosestonewillberequiredforground-truthtripsincaseof
emergencies.
ThedetailedlogicdesigntablescanbefoundinAppendix2,andthelogicdesigndiagramis
providedinAppendix3.
4.2 QueriestoCreatetheSchemas
TheSQLstatementssuggestedtocreatethedatabasetablescanbefoundinAppendix4.
4.3Constraints
● Allprimarykeysandforeignkeysshouldnotbenull.
● Surrogatekeysshouldbeintegers.
● Theforeignkeysofone-to-onerelationshipsshouldbeunique.
● Spatialdatashouldbeidentifiedbydatatypeandtypesofgeometries.
5. Data sources
First,weassumethatouractivitiesallhappenedinthestateofVictoria.Inourdatabasesystem,
therearethreetypesofdatasourcesused–Givendatasource,Onlinedatasourceand
Assumptiondatasource.
5.1HerbariumSpecimenData
● Purpose:Thisdatasetcontainsdetailedrecordsofherbariumspecimens,including
cataloguenumbers,collectorinformation,collectiondates,taxonomicnames,andvarious
observationalnotes.Itservesasthecoredataformanagingspecimeninformationwithin
theherbarium'sdatabasesystem.
● Processing:Outofthe49columns,onlythosedirectlyrelevanttothegeospatial
managementofspecimenswillberetained.Keyattributesinclude‘CatalogNumber’,
‘TaxonName’,‘StartDate’,‘CollectorLastName’,‘WGS84coordinate’,and‘Location
description’.Attributessuchas‘TimestampCreated’and‘TimestampModified’are
retainedfordataprovenance,butotherlessrelevantfields(e.g.,‘EthnobotanicalNotes’,
‘CommonName’)maybeexcludediftheydonotcontributetotheproject'sobjectives.
5.2Location,ElevationandSpatialData
● https://metashare.maps.vic.gov.au/geonetwork/srv/eng/catalog.search#/metadata/019d763
1-1234-5112-9f21-8f7346647b61
https://metashare.maps.vic.gov.au/geonetwork/srv/eng/catalog.search#/metadata/7e81312
c-4678-5b71-80b6-38dba3f3ce55
● Purpose:Thesedatasetsprovidetheessentiallocation,elevation,andspatialfeatures
requiredforgeoreferencingherbariumspecimensandverifyinggeocodesagainstdigital
elevationmodels(DEMs).Theyalsosupportbasicmappingandspatialanalysistasks
necessaryfortheproject.
● Processing:Retaineddataincludeslocationcoordinates,elevation,andkeyspatial
featuresrelevanttogeoreferencingandmappingwithinspecificLGAs.Unnecessary
layersandattributes,suchasthosenotcontributingdirectlytogeoreferencingorspatial
analysis,arediscardedtostreamlinedatasetsizeandimprovedatabaseperformance.
5.3AmenitiesandPoints of Interest
● https://download.geofabrik.de/australia-oceania/australia.html
https://discover.data.vic.gov.au/dataset/vicmap-features-of-interest
● Purpose:Thesedataprovidevitalinformationaboutamenities(hotels,hospitals)near
specimensites,crucialforplanningground-truthingtripsandsupportingstafflogistics.
● Processing:Thedatasetistrimmedtoincludeonlyrelevantareasandfeaturesaround
specimenlocationsinVictoria.Non-essentialdata,suchasamenitiesnotpertinentto
fieldwork,areexcludedtomaintainaleandatabase.
5.4Assumption EmployeeandExpedition/Ground-Truthing TripData
● AssumptionandCreationofEmployeeData:Intheabsenceofrealemployeedata,an
Employeetablewillbecreated,containingfieldssuchasemployeeID,name,email,
phonenumber,andjobtitle.Itwillalsotrackthelastfirstaidtrainingdateandcurrent
workload.
● AssumptionandCreationofExpedition/Ground-TruthingTripData:Expeditionand
Ground-TruthingtripswillberepresentedbyatablerecordingtripdetailssuchastripID,
startdate,parameters,andlocationsvisited.Tripscanbeselectedbasedonstartdatesor
byreviewingpasttripsforfutureplanning.
5.5IndigenousKnowledgeData
● https://aiatsis.gov.au/explore/map-indigenous-australia
● Purpose:ThisdatasetisessentialforidentifyingtheTraditionalOwnergroupsassociated
withthelocationswhereherbariumspecimenswerecollected.Itallowsthesystemtolink
specimenstoIndigenousCulturalKnowledgeandupholdprinciplesofIndigenousData
Sovereigntybyensuringthatrelevantpartiesareconsultedwhenmanagingsensitivedata.
● Processing:TheAIATSISdatasetwillbeusedtomapspecimencollectionsitestothe
correspondingIndigenousgroupsandtheirterritories.Onlydatarelevanttothespecific
collectionlocationswithinVictoriawillberetained.Thedatasetwillbefilteredtofocus
onattributesthathelpinidentifyingtheCountryandassociatedIndigenous
knowledge-holdergroups.
Appendix1
ERDiagram
Appendix2CatalogueofDatabaseTables
Entity(Table)Name Attribute Datatype PK FK
Specimen catalog_number VARCHAR(20) Yes
specie_id INTEGER Yes
collect_date DATE
location_id INTEGER Yes
collector_id INTEGER Yes
expedition_id INTEGER Yes
Entity(Table)Name Attribute Datatype PK FK
Specie id INTEGER Yes
taxonomic_name VARCHAR(255)
Entity(Table)Name Attribute Datatype PK FK
SpecieCommon specie_id INTEGER Yes Yes
Name
common_name VARCHAR(255) Yes
Entity(Table)Name Attribute Datatype PK FK
Location id INTEGER Yes
country VARCHAR(100)
country_code VARCHAR(5)
state_province VARCHAR(100)
locality VARCHAR(255)
verbatim_locality TEXT
latitude DECIMAL(10,8)
longitude DECIMAL(10,8)
datum VARCHAR(50)
uncertainty INTEGER
geocode_source VARCHAR(255)
geocode_acquisition_m VARCHAR(255)
ethod
protocol VARCHAR(255)
min_elevation INTEGER
max_elevation INTEGER
min_depth INTEGER
max_depth INTEGER
location GEOMETRY(POINT,
4326)
Entity(Table)Name Attribute Datatype PK FK
Collector id INTEGER Yes
first_name VARCHAR(100)
last_name VARCHAR(100)
email VARCHAR(100)
phone VARCHAR(20)
Entity(Table)Name Attribute Datatype PK FK
Collector collector_id INTEGER Yes Yes
Expedition
expedition_id INTEGER Yes Yes
Entity(Table)Name Attribute Datatype PK FK
Expedition id INTEGER Yes
date DATE
description TEXT
destination VARCHAR(255)
location GEOMETRY(POIN
T,4326)
Entity(Table)Name Attribute Datatype PK FK
Hotel id INTEGER Yes
name VARCHAR(255)
email VARCHAR(100)
phone VARCHAR(20)
address VARCHAR(255)
location GEOMETRY(POIN
T,4326)
Entity(Table)Name Attribute Datatype PK FK
Ground Truthing id INTEGER Yes
Trip
date DATE
description TEXT
operator_id INTEGER Yes
route GEOMETRY(LINE
STRING, 4326)
hotel_id INTEGER Yes
hospital_and_emergency_id INTEGER Yes
Entity(Table)Name Attribute Datatype PK FK
Ground Truthing location_id INTEGER Yes Yes
Trip Location
location_id INTEGER Yes Yes
Entity(Table)Name Attribute Datatype PK FK
Employee id INTEGER Yes
first_name VARCHAR(100)
last_name VARCHAR(100)
work_email VARCHAR(100)
phone VARCHAR(20)
position VARCHAR(50)
last_training_date DATE
Entity(Table)Name Attribute Datatype PK FK
EmployeeTrip ground_truthing_trip_id INTEGER Yes Yes
employee_id INTEGER Yes Yes
Entity(Table)Name Attribute Datatype PK FK
Hospital and id INTEGER Yes
Emergency
name VARCHAR(255)
phone VARCHAR(20)
email VARCHAR(100)
address VARCHAR(255)
type VARCHAR(50)
location GEOMETRY(POIN
T,4326)
Entity(Table)Name Attribute Datatype PK FK
Location of id INTEGER Yes
Interests
address VARCHAR(255)
name VARCHAR(255)
location GEOMETRY(POIN
T,4326)
Entity(Table)Name Attribute Datatype PK FK
SpecieLocation specie_id INTEGER Yes Yes
location_of_interests_id INTEGER Yes Yes
Entity(Table)Name Attribute Datatype PK FK
Indigenous id INTEGER Yes
Knowledge Holder
Group
name VARCHAR(255)
phone VARCHAR(20)
email VARCHAR(100)
Entity(Table)Name Attribute Datatype PK FK
Indigenous id INTEGER Yes
Knowledge
content TEXT
indigenous_knowledge_hol INTEGER Yes
der_group_id
Entity(Table)Name Attribute Datatype PK FK
SpecieIndigenous specie_id INTEGER Yes Yes
Knowledge
indigenous_knowledge_id INTEGER Yes Yes
Appendix 3 Logic design diagram
Appendix 4 SQL statements suggested to create database tables
CREATETABLESpecimen (
catalog_numberVARCHAR(20) PRIMARY KEY,
specie_idINTEGERNOT NULL,
collect_dateDATENOTNULL,
location_idINTEGERNOT NULL,
collector_idINTEGERNOTNULL,
expedition_idINTEGERNOT NULL,
FOREIGN KEY(specie_id)REFERENCES Specie(id),
FOREIGN KEY(location_id)REFERENCES Location(id),
FOREIGN KEY(collector_id)REFERENCES Collector(id),
FOREIGN KEY(expedition_id)REFERENCES Expedition(id)
);
CREATETABLESpecie(
id INTEGERAUTO_INCREMENTPRIMARY KEY,
taxonomic_nameVARCHAR(255) NOTNULL,
);
CREATETABLESpecieCommonName(
specie_idINTEGER,
common_nameVARCHAR(255),
PRIMARYKEY(specie_id,common_name),
FOREIGN KEY(specie_id)REFERENCES Specie(id),
);
CREATETABLELocation(
id INTEGERAUTO_INCREMENTPRIMARY KEY,
countryVARCHAR(100) NOTNULL,
country_codeVARCHAR(5) NOTNULL,
state_provinceVARCHAR(100) NOTNULL,
localityVARCHAR(255),
verbatim_localityVARCHAR(255),
latitudeDECIMAL(10,8),
longitudeDECIMAL(10,8),
datumVARCHAR(50),
uncertaintyINTEGER,
geocode_sourceVARCHAR(255),
geocode_acquisition_methodVARCHAR(255),
protocolVARCHAR(255),
min_elevationINTEGER,
max_elevationINTEGER,
min_depthINTEGER,
max_depthINTEGER,
locationGEOMETRY(POINT,4326) NOTNULL,
);
CREATETABLECollector(
id INTEGERAUTO_INCREMENTPRIMARY KEY,
first_nameVARCHAR(100) NOTNULL,
last_nameVARCHAR(100)NOT NULL,
emailVARCHAR(100) NOTNULL,
phoneVARCHAR(20) NOTNULL,
);
CREATETABLEExpedition (
id INTEGERAUTO_INCREMENTPRIMARY KEY,
dateDATENOTNULL,
descriptionTEXT,
destinationVARCHAR(255)NOT NULL,
locationGEOMETRY(POINT,4326) NOTNULL,
);
CREATETABLECollectorExpedition(
collector_idINTEGER,
expedition_idINTEGER,
PRIMARYKEY(collector_id,expedition_id),
FOREIGN KEY(collector_id)REFERENCES Collector(id),
FOREIGN KEY(expedition_id)REFERENCES Expedition(id),
);
CREATETABLELocationOfInterests(
id INTEGERAUTO_INCREMENTPRIMARY KEY,
nameVARCHAR(255)NOT NULL,
addressVARCHAR(255) NOTNULL,
locationGEOMETRY(POINT,4326) NOTNULL,
);
CREATETABLESpecieLocation(
specie_idINTEGER,
location_of_interests_idINTEGER,
PRIMARYKEY(specie_id,location_of_interests_id),
FOREIGN KEY(specie_id)REFERENCES Specie(id),
FOREIGN KEY(location_of_interests_id)REFERENCES LocationOfInterests(id),
);
CREATETABLEIndigenousKnowledgeGroupHolder (
id INTEGERAUTO_INCREMENTPRIMARY KEY,
nameVARCHAR(255)NOT NULL,
emailVARCHAR(100) NOTNULL,
phoneVARCHAR(20) NOTNULL,
);
CREATETABLEIndigenousKnowledge (
id INTEGERAUTO_INCREMENTPRIMARY KEY,
contentTEXTNOTNULL,
indigenous_knowledge_holder_group_idINTEGERNOTNULL,
FOREIGN KEY(indigenous_knowledge_holder_group_id)REFERENCES
IndigenousKnowledgeGroupHolder(id),
);
CREATETABLESpecieIndigenousKnowledge (
specie_idINTEGER,
indigenous_knowledge_idINTEGER,
PRIMARYKEY(specie_id,indigenous_knowledge_id),
FOREIGN KEY(specie_id)REFERENCES Specie(id),
FOREIGN KEY(indigenous_knowledge_id)REFERENCES IndigenousKnowledge(id),
);
CREATETABLEEmployee(
id INTEGERAUTO_INCREMENTPRIMARY KEY,
first_nameVARCHAR(100) NOTNULL,
last_nameVARCHAR(100)NOT NULL,
work_emailVARCHAR(100) NOTNULL,
phoneVARCHAR(20) NOTNULL,
position VARCHAR(50) NOTNULL,
last_training_dateDATE,
);
CREATETABLEGroundTruthingTrip (
id INTEGERAUTO_INCREMENTPRIMARY KEY,
dateDATENOTNULL,
descriptionTEXT,
operator_idINTEGER,
route:GEOMETRY(LINESTRING,4326)NOTNULL,
hotel_idINTEGERNOTNULL,
hospital_and_emergency_idINTEGERNOTNULL,
FOREIGN KEY(hotel_id)REFERENCES Hotel(id),
FOREIGN KEY(hospital_and_emergency_id)REFERENCES HospitalAndEmergency(id),
);
CREATETABLEEmployeeTrip(
ground_truthing_trip_idINTEGER,
employee_idINTEGER,
PRIMARYKEY(ground_truthing_trip_id,employee_id),
FOREIGN KEY(ground_truthing_trip_id)REFERENCES GroundTruthingTrip(id),
FOREIGN KEY(employee_id)REFERENCES Employee(id),
);
CREATETABLEHotel(
id INTEGERAUTO_INCREMENTPRIMARY KEY,
nameVARCHAR(255)NOT NULL,
emailVARCHAR(100) NOTNULL,
phoneVARCHAR(20) NOTNULL,
addressVARCHAR(255) NOTNULL,
locationGEOMETRY(POINT,4326) NOTNULL,
);
CREATETABLEHospitalAndEmergency(
id INTEGERAUTO_INCREMENTPRIMARY KEY,
nameVARCHAR(255)NOT NULL,
emailVARCHAR(100) NOTNULL,
phoneVARCHAR(20) NOTNULL,
addressVARCHAR(255) NOTNULL,
typeVARCHAR(50) NOTNULL,
locationGEOMETRY(POINT,4326) NOTNULL,
);
