CREATE TABLE Country ( Country_id INTEGER NOT NULL AUTO_INCREMENT, countryName VARCHAR(20) NOT NULL, PRIMARY KEY ( Country_id ) ); CREATE TABLE Location ( locationName VARCHAR(20) NOT NULL, Region_id INTEGER REFERENCES Region ( Region_id) , Country_id INTEGER REFERENCES Country ( Country_id) , latitude decimal( 10,5) NOT NULL, longitude decimal( 10,5) NOT NULL, PRIMARY KEY ( Country_id, Region_id, locationName ) ); CREATE TABLE Region ( Region_id INTEGER NOT NULL AUTO_INCREMENT, Country_id INTEGER REFERENCES Country ( Country_id) , regionName VARCHAR(20) NOT NULL, PRIMARY KEY ( Country_id, Region_id ) ); CREATE TABLE Confluence ( latitude int NOT NULL, longitude int NOT NULL, altitude int NOT NULL, kind ENUM ('primary', 'secondary', 'water') NOT NULL, Region_id INTEGER REFERENCES Region ( Region_id) , Country_id VARCHAR(20) REFERENCES Region ( Country_id) , PRIMARY KEY ( longitude, latitude ) ); CREATE TABLE Visit ( visitid VARCHAR(20) NOT NULL AUTO_INCREMENT, dateVisited DATE NOT NULL, description VARCHAR(20) NOT NULL, accuracy VARCHAR(20) NOT NULL, status ENUM ('successful', 'incomplete') NOT NULL, progress ENUM ('active', 'pending', 'submitting') NOT NULL, latitude int REFERENCES Confluence ( latitude) , longitude int REFERENCES Confluence ( longitude) , PRIMARY KEY ( visitid ) ); CREATE TABLE Picture ( seqno int NOT NULL, visitid VARCHAR(20) REFERENCES Visit ( visitid) , image MEDIUMBLOB NOT NULL, title VARCHAR(20) NOT NULL, PRIMARY KEY ( visitid, seqno ) ); CREATE TABLE Visitor ( visitorid int NOT NULL, name VARCHAR(20) NOT NULL, email VARCHAR(20) NOT NULL, visitid VARCHAR(20) REFERENCES Visit ( visitid) , PRIMARY KEY ( visitorid ) );