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 )

For this task, you need to write a trigger that catches inserts into the SIGHTINGS table, and lets the user know when there is a potential foreign key problem. Specifically, the trigger should print an error (hint: use RAISE(ABORT, my message) ) message if the newly-inserted tuple references a location that does not currently exist in the database:

Error: Insert into the SIGHTINGS table references location Grous Meadow that is not found in the database.

Once you have implemented your trigger, test it by trying the following inserts:

INSERT INTO SIGHTINGS VALUES

(Douglas dustymaiden, Person B, Double Mountain, 2005-11-28);

INSERT INTO SIGHTINGS VALUES

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

INSERT INTO SIGHTINGS VALUES

(Douglas dustymaiden, Person B, Grouse Meadow, 2006-11-28);

INSERT INTO SIGHTINGS VALUES

(Douglas dustymaiden, Person C, Grouse Meadow, 2006-08-16);

INSERT INTO SIGHTINGS VALUES

(Douglas dustymaiden, Person A, Piute, 2007-02-17);

Then run the following query:

SELECT *

FROM SIGHTINGS

WHERE PERSON = Person A or Person B or Person C;

SELECT *

FROM FEATURES

WHERE CLASS = UNKNOWN;