5  National Timing Windows Dataset

5.1 Overview

The National Timing Windows Database is a structured relational database designed to integrate diverse ecological datasets relevant to the identification of optimal timing windows for human interventions in Canadian waterbodies. The database consolidates spatial, taxonomic, phenological, and ecological trait data into a unified format to support environmental assessments, conservation efforts, and policy decisions.

The database is built using SQLite and adheres to a structured schema that maintains referential integrity and optimized data retrieval. Key datasets include species distributions, fish traits, waterbody characteristics, and species phenology.

5.2 Core Data Tables

5.2.1 1. Watersheds (watersheds)

  • Description: Contains attributes describing hydrological watershed units.
  • Primary Key: watershed_id
  • Key Fields:
    • watershed_id: Unique identifier for each watershed.
    • name: Watershed name.
    • area_km2: Watershed area in square kilometers.

5.2.2 2. Waterbodies (waterbodies)

  • Description: Stores geospatial data on lakes and rivers across Canada.
  • Primary Key: waterbody_id
  • Key Fields:
    • waterbody_id: Unique identifier for each waterbody.
    • name: Waterbody name.
    • type: Classification (lake, river, etc.).
    • area_km2: Waterbody area (if applicable).

5.2.3 3. Species (species)

  • Description: Catalog of freshwater fish species in Canada, including taxonomic and ecological attributes.
  • Primary Key: species_id
  • Key Fields:
    • species_id: Unique identifier.
    • scientific_name: Taxonomic designation.
    • common_name: Commonly used name.
    • taxonomy: Classification details (family, genus, etc.).

5.2.4 4. Watersheds-Species Relationship (watersheds_species)

  • Description: Links fish species to watersheds where they are known to occur.
  • Primary Keys: (watershed_id, species_id)
  • Foreign Keys:
    • watershed_id → watersheds
    • species_id → species

5.2.5 5. Waterbodies-Species Relationship (waterbodies_species)

  • Description: Links fish species to specific lakes and rivers.
  • Primary Keys: (waterbody_id, species_id)
  • Foreign Keys:
    • waterbody_id → waterbodies
    • species_id → species

5.3 Trait Data Tables

5.3.1 6. Habitat Preferences

  • Tables: habitat_adult, habitat_juvenile, habitat_spawning, habitat_yoy
  • Description: These tables store habitat preferences for different life stages.
  • Primary Key: species_id
  • Fields: Binary presence indicators for different habitat types (e.g., riverine, lacustrine).

5.3.2 7. Food Items (food_items)

  • Description: Documents dietary habits of fish species.
  • Primary Key: species_id
  • Fields:
    • species_id
    • food_type: Type of prey consumed.
    • frequency: Occurrence frequency.

5.3.3 8. Spawning Characteristics (spawning)

  • Description: Stores reproductive traits including spawning conditions and parental care.
  • Primary Key: species_id
  • Fields:
    • species_id
    • temperature_min: Minimum spawning temperature.
    • temperature_max: Maximum spawning temperature.
    • spawning_frequency: Number of times per year.

5.3.4 9. Morphological Traits (morphology)

  • Description: Describes body shape, size, and other morphological features.
  • Primary Key: species_id
  • Fields:
    • species_id
    • maximum_length_cm: Maximum observed body length.
    • body_depth_percent_tl: Body depth as a percentage of total length.

5.3.5 10. Physiological Tolerances

  • Tables: tolerance_adult, tolerance_embryo, tolerance_larvae, tolerance_spawner
  • Description: Captures thermal and environmental tolerance ranges.
  • Primary Key: species_id
  • Fields:
    • species_id
    • temperature_max: Maximum survivable temperature.
    • oxygen_min: Minimum dissolved oxygen tolerance.

5.4 Phenology Data Tables

5.4.1 11. Migration Timing (migration_phenology)

  • Description: Defines seasonal migration windows for each species.
  • Primary Key: species_id
  • Fields:
    • species_id
    • start_month: Beginning of migration season.
    • end_month: End of migration season.

5.4.2 12. Spawning Timing (spawning_phenology)

  • Description: Stores data on spawning periods.
  • Primary Key: species_id
  • Fields:
    • species_id
    • start_month: First month of spawning.
    • end_month: Last month of spawning.

5.4.3 13. Larval Timing (larvae_phenology)

  • Description: Tracks larval presence across seasons.
  • Primary Key: species_id
  • Fields:
    • species_id
    • start_month: First recorded larval occurrence.
    • end_month: Last recorded larval occurrence.

5.5 Database Schema Diagram

The database schema is designed to enforce referential integrity through primary and foreign key constraints. The entity-relationship diagram (ERD) visualizes these relationships, ensuring consistency and efficiency in data retrieval.

5.6 Summary

The National Timing Windows Database provides a structured, relational framework for integrating and analyzing fish species data across Canadian waterbodies. The inclusion of spatial, ecological, and temporal attributes ensures that researchers and conservation practitioners can assess seasonal constraints on fish life processes, ultimately supporting science-based conservation and management decisions.