Homework #19 - 10/24 (Makeup)

Tonight I'm going to blog about my project for my databases class. I was really enthusiastic about it and intend to keep working on it after the course is over, just for the experience. It was supposed to be a system for backyard gardeners. Users would be able to create an account with their name, email, and address. They would automatically be assigned a garden object upon their account creation. This was going to be handled by using SQL triggering on the database. The code would look something like this:

DELIMITER \\
CREATE TRIGGER addGarden  AFTER INSERT ON gardeners
BEGIN
  INSERT INTO gardens (owner_id)
  VALUES (OLD.id);
END\\
DELIMITER ;

I did not get this code to actually work, but the idea is that you have to change the delimiter from a semicolon before you write the function so that you can use the semicolon at the end of the insert statement without ending the function early. The trigger runs after an insert occurs in the gardeners table, or in other words, after a gardener has created their account. It then inserts a new garden into the gardens table with the owner_id attribute of the garden set to the OLD.id. Here the OLD means that it is using the id of the statement that caused the trigger, the gardener. I think this may be where I made my mistake, as I am not sure that the OLD.id is doing what I think it is. Then the trigger is ended, and we reset the delimiter back to the semicolon.

There is also a pre-filled database of plants containing attributes such as common name, scientific name, mature height, duration, growth rate, minimum pH, maximum pH, among others. Users will be able to select from plants from this database to add to their personal garden and will get advice on things they should do based on the plants selected, for example, how deep to plant the seed, or how far apart it should be from another seed. The system will also recommend certain plants that the user should try to grow based on the location of their address. It can do this because the database also has table containnig list of states that each plant naturally occur in, so it can do a simple query like the following to produce a suggestion list:

SELECT common_name FROM plants p
INNER JOIN naturally_occuring n
ON p.scientific_name = n.scientific_name
INNER JOIN gardeners g
ON g.state = n.state
WHERE g.state = n.state

This code seems bloated from reading it, but it basically makes a new, temporary table from the three tables, plants, naturally_occurring, and gardeners and matches the tuples accordingly. It then selects only the common name of the plants where the state the gardener lives is the same as the state that the plant naturally occurs in.

For now that is all, I intend to have more features too - perhaps reminders on when to harvest, or weather information to alert users of inclement weather that may endanger their plant.