1. Structured Data: Organizes complex datasets with relational schemas.
2. Powerful Queries: Enables efficient data retrieval using SQL.
3. Efficient Resource Usage: Reads only the data queried, minimizing RAM usage and optimizing performance.
4. Scalable: Manages large, growing datasets effectively.
5. Data Integrity: Maintains consistency with keys and constraints.
6. Secure: Provides robust access control and security features.
7. Interoperable: Seamlessly integrates with tools like R, Python, and SQL for direct querying and analysis.

con <- dbConnect(RSQLite::SQLite(), "plant_species.db")
Test <- dbGetQuery(con, "
  SELECT ID, species, accepteret_dansk_navn, wcvp_species, gbif_species 
  FROM Species 
  WHERE rodliste_2010 = 'VU'
")

# Query to get the mean of w_thousandseedweight and number of observations for each species
query <- "
  SELECT 
    s.ID AS Species_ID,
    s.species AS Species_Name,
    COUNT(sw.SeedID) AS Number_of_Observations,
    AVG(sw.w_thousandseedweight) AS Mean_Thousand_Seed_Weight
  FROM Species s
  INNER JOIN MainSeed ms ON s.ID = ms.ID
  INNER JOIN SeedWeight sw ON ms.SeedID = sw.SeedID
  WHERE s.rodliste_2010 = 'VU'
  GROUP BY s.ID, s.species
"

# Execute the query
result <- dbGetQuery(con, query)

• GROUP BY s.ID, s.species: This line groups the results by Species_ID and species name. Aggregations like COUNT() and AVG() are performed within each group.
• AVG(sw.w_thousandseedweight) AS Mean_Thousand_Seed_Weight: Computes the average value of w_thousandseedweight for each species group defined by Species_ID.

• Partitioning: Divide large tables, such as species observations, into smaller partitions based on geographic regions or time periods.
• Indexing: Create indexes on columns frequently used in queries, such as species names and observation dates, to speed up search and retrieval operations.
• Sharding: Distribute data across multiple database servers to balance the load and manage the increasing dataset size effectively.

CREATE INDEX idx_observation_date ON Observations(observation_date);
-- Example of partitioning by geographic region (in a practical implementation, this might involve separate tables)
CREATE TABLE Observations_North_America AS
SELECT * FROM Observations WHERE region = 'North America';

• Data Integrity:
  • Primary Keys: Ensure each species and observation has a unique identifier.
  • Foreign Keys: Link observations to the correct species using foreign keys.
  • Constraints: Ensure valid data entries, such as valid species IDs and observation dates.

CREATE TABLE Observations (
    observation_id INTEGER PRIMARY KEY,
    species_id INTEGER,
    observation_date DATE,
    location TEXT,
    FOREIGN KEY (species_id) REFERENCES Species(species_id)
);

-- Adding a constraint to ensure no future dates for observations
ALTER TABLE Observations
ADD CONSTRAINT check_date CHECK (observation_date <= CURRENT_DATE);

• Security Features:
  • Access Control: Define roles and permissions for different types of users (e.g., researchers, administrators).
  • Encryption: Encrypt sensitive data, such as location details, to prevent unauthorized access.
  • Audit Logging: Track access to sensitive data for compliance and security monitoring.

import sqlite3
from cryptography.fernet import Fernet

# Create a key for encryption
key = Fernet.generate_key()
cipher_suite = Fernet(key)

# Connect to the database
conn = sqlite3.connect('biodiversity.db')
cursor = conn.cursor()

# Example of storing encrypted data
def encrypt_data(data):
    return cipher_suite.encrypt(data.encode()).decode()

def decrypt_data(encrypted_data):
    return cipher_suite.decrypt(encrypted_data.encode()).decode()

# Insert encrypted data into the database
encrypted_location = encrypt_data("Sensitive Location Data")
cursor.execute("INSERT INTO Observations (species_id, observation_date, location) VALUES (?, ?, ?)",
               (1, '2023-08-15', encrypted_location))
conn.commit()

# Retrieve and decrypt data
cursor.execute("SELECT location FROM Observations WHERE species_id = 1")
encrypted_location = cursor.fetchone()[0]
decrypted_location = decrypt_data(encrypted_location)
print(f"Decrypted Location: {decrypted_location}")

conn.close()

• Seed traits (Derek): from kew germination, oil, protein, dispersal, seed weights, storage behaviour, morphology
• Field work plots (Bjarke and Mattis): Table for each plot and for each presence
• Novana plots (Rasmus): One table for plots and one for presences
• Archive datasets (Claus): One with study Id and one with presences?
• CSR dataset (Derek): Species and traits
• Plant traits (Volunteers):

• Main species table taxonomy (Bjarke, Rasmus)
• Type of SQL (eg. PostGIS alows for geographical operations such as)

-- Query to find places within a certain distance
SELECT name
FROM places
WHERE ST_Distance(geom, ST_MakePoint(-73.9654, 40.7829)) < 1000;

• Minimum metadata (right now there is a minimal automatic metadata generation)

• You can find all the codes and datasets in this repository