For this question write a program in SQLite which uses the following database with three tables:

SIGHTINGS(NAME, PERSON, LOCATION, SIGHTED)

FEATURES(LOCATION, CLASS, LATITUDE, LONGITUDE, MAP, ELEV)

FLOWERS(GENUS, SPECIES, COMNAME)

and this database has the following semantics:

SIGHTINGS gives information that describes every time that a member of the club observes one of the wildflowers described in the table FLOWERS. NAME tells the name of the flower observed, PERSON describes who saw the flower, LOCATION tells the name of a nearby geographical feature where the flower was seen, and SIGHTED tells the day when the flower was seen.

FEATURES lists the various locations where flowers have been observed. LOCATION is the name of the place, CLASS is the type of place (there are several types, such as Summit, Mine, Locale, etc.), LATITUDE and LONGITUDE describe where on the surface of the earth the locations are found (if you are not familiar with the concepts of latitude and longitude, you might want to do a web search on them; the first is like an x-coordinate on the Earths surface, and the second is like a y-coordinate). MAP tells the name of the topographic map where the feature can be found, and ELEV tells the height of the feature. These last few attributes will not be used in this assignment.

FLOWERS lists all of the flowers that the members of the SSWC try to find. GENUS and SPECIES give the scientific name for the flower, and 1 COMNAME gives the non-scientific name ( SIGHTING.NAME is a foreign key into FLOWER.COMNAME )

Sometimes, when people try to insert into the SIGHTINGS table, they acciden-tally insert the Latin name of the flower instead of its common name. For example, they might use:

INSERT INTO SIGHTINGS VALUES

(Chaenactis douglasii, Person A, Shirley Peak, 2006-08-18);

Instead of:

INSERT INTO SIGHTINGS VALUES

(Douglas dustymaiden, Person A, Shirley Peak, 2006-08-18);

For this task, you need to write a trigger that catches inserts into the SIGHTINGS table. If there is not a foreign key problem with the new flower that is added (be- cause the inserted tuple includes a common name value that is in the database) then the flower is inserted normally. Otherwise, if there is a foreign key problem, and the problem is because someone tried to enter in the Latin name rather than the common name, a warning message is printed to the screen and the correct common name is inserted into the database, rather than the Latin name. So, for example, if someone tries to use:

INSERT INTO SIGHTINGS VALUES

(Chaenactis douglasii, Person A, Shirley Peak, 2006-08-18);

Then the modified tuple is inserted:

On any other sort of foreign key error, the insert should be processed normally (that is, rejected).

Then, test your trigger as follows. Run the following five insertions with your trigger active:

INSERT INTO SIGHTINGS VALUES

(Sky pilot, Person X, Grouse Meadow, 2006-08-18);

INSERT INTO SIGHTINGS VALUES

(Hoar buckwheat, Person X, Grouse Meadow, 2006-08-18);

INSERT INTO SIGHTINGS VALUES

(Zigadenus venenosus, Person X, Grouse Meadow, 2006-08-18);

INSERT INTO SIGHTINGS VALUES

(Carex limosa, Person Y, Grouse Meadow, 2006-08-18);

INSERT INTO SIGHTINGS VALUES

(Draperia, Person Z, Grouse Meadow, 2006-08-18);

Then, in order to verify that the database was updated correctly, run the following queries:

SELECT *

FROM SIGHTINGS

WHERE PERSON = Person X or PERSON = Person Y or PERSON = Person Z;