Skip to content

README.md

Latest commit

 

History

History
138 lines (110 loc) · 5.18 KB

README.md

File metadata and controls

138 lines (110 loc) · 5.18 KB
Raw

Database Documentation

Below is all necessary information regarding the database design, implementation and use. All database testing has been done with MySQL Server 8.0.44

Notes

  • The database for the app is currently hosted on AWS. Testing can be done locally, instructions for both use cases are provided.

Running database instructions

  1. Download MySQL from here: https://dev.mysql.com/downloads/file/?id=546163, make sure you're downloading MySQL 8.0.44.0

  2. Follow the installer setup

  3. Run this command in terminal to connect to the server (If you installed MySQL in a different location, use your path to mysql.exe or mysql for Mac):

    • Windows:

      • Local:

        & "C:\Program Files\MySQL\MySQL Server 8.0\bin\mysql.exe" -u root -p

        • AWS:

          & "C:\Program Files\MySQL\MySQL Server 8.0\bin\mysql.exe" -h identiflora.crwyoycsq98a.us-west-1.rds.amazonaws.com -P 3306 -u admin -p

      • On Mac:

        • Local:

          /usr/local/mysql/bin/mysql -u root -p

        • AWS:

          /usr/local/mysql/bin/mysql -h identiflora.crwyoycsq98a.us-west-1.rds.amazonaws.com -P 3306 -u admin -p

  4. To build the database on your machine, run: 'SOURCE initialize_database.sql;'

  5. The above format can be used to run any .sql file from the mysql terminal.

Tables

plant_species

One entry exists for each plant the model is able to identify.

  • species_id
    • Datatype - int
    • Description - auto-incrementing primary key identifier.
  • common_name
    • Datatype - varchar(255)
    • Description - common name of the plant.
  • scientific_name
    • Datatype - varchar(255)
    • Description - scientific (Latin) species name of the plant.
  • genus
    • Datatype - varchar(255)
    • Description - genus to which the plant belongs.
  • img_url
    • Datatype - varchar(512)
    • Description - URL to an example reference image of the plant.

identification_submission

Entry is created when a user submits a photo for identification.

  • identification_id
    • Datatype - int
    • Description - auto-incrementing primary key identifier.
  • img_url
    • Datatype - varchar(512)
    • Description - URL to the image the user submitted.
  • user_id
    • Datatype - int
    • Description - references the PK identifier of the user that submitted the photo.
  • time_submitted
    • Datatype - timestamp
    • Description - time when the entry was added to the database.

identification_option

Specifies one possible species option for an identification submission. Each submission may have up to five ranked options.

  • option_id
    • Datatype - int
    • Description - auto-incrementing primary key identifier for an option.
  • identification_id
    • Datatype - int
    • Description - Foreign key, references the submission this option belongs to.
  • species_id
    • Datatype - int
    • Description - Foreign key, references the plant species associated with this option.
  • rank
    • Datatype - int
    • Description - position of this option within the N-best list (1-5), ensuring each rank is unique per submission.

Additional Constraints

  • Each option for a submission must have a unique rank.
  • Each option for a submission must reference a unique species.
  • Cascading deletes ensure options are removed when their submission or species is deleted.

identification_result

Stores the final chosen result (best-ranked) for an identification submission. Each submission may have at most one result.

  • identification_id
    • Datatype - int
    • Description - primary key, foreign key referencing the submission. Guarantees one result per submission.
  • option_id
    • Datatype - int
    • Description - references the selected option that was chosen for final result.

Additional Constraints

  • Ensures the chosen option belongs to the same submission by enforcing the composite foreign key (identification_id, option_id). This ensures that the option_id references an option that is associated with the correct submission.
  • Cascading deletes ensure results are removed automatically if the associated submission or option is deleted.

incorrect_identification

Stores a user-reported incorrect prediction for a submission.

  • identification_id
    • Datatype - int
    • Description - primary key; FK to identification_submission.
  • correct_species_id
    • Datatype - int
    • Description - FK to plant_species (true species).
  • incorrect_species_id
    • Datatype - int
    • Description - FK to plant_species (model-predicted species).
  • time_submitted
    • Datatype - timestamp
    • Description - time when the incorrect report was added.

Additional Constraints

  • Composite FK (identification_id, incorrect_species_id) must match an option in identification_option (ensures the reported incorrect species was one of the model's options for that submission).
  • Cascading deletes clean up rows if submissions or species are removed.