PRAGMA foreign_keys = ON;

PRAGMA writable_schema = 1;
DELETE FROM sqlite_master WHERE type IN ("table", "index", "trigger");
PRAGMA writable_schema = 0;
VACUUM;

CREATE TABLE "AREA_CODE_STATE" (
"area_code" INT,
"state" VARCHAR(2) UNIQUE,
PRIMARY KEY ("area_code")
);

CREATE TABLE "VOTES" (
"vote_id" INT,
"state" VARCHAR(2),
"contestant_number" INT,
PRIMARY KEY ("vote_id"),
FOREIGN KEY ("state") REFERENCES "AREA_CODE_STATE"("state")
);





INSERT INTO "AREA_CODE_STATE" VALUES ("212", "NY");
INSERT INTO "AREA_CODE_STATE" VALUES ("213", "CA");
INSERT INTO "AREA_CODE_STATE" VALUES ("305", "FL");
INSERT INTO "AREA_CODE_STATE" VALUES ("404", "GA");
INSERT INTO "AREA_CODE_STATE" VALUES ("312", "IL");
INSERT INTO "VOTES" VALUES ("1", "NY", "101");
INSERT INTO "VOTES" VALUES ("2", "NY", "102");
INSERT INTO "VOTES" VALUES ("3", "NY", "101");
INSERT INTO "VOTES" VALUES ("4", "CA", "103");
INSERT INTO "VOTES" VALUES ("5", "CA", "101");
INSERT INTO "VOTES" VALUES ("6", "CA", "103");
INSERT INTO "VOTES" VALUES ("7", "CA", "104");
INSERT INTO "VOTES" VALUES ("8", "FL", "101");
INSERT INTO "VOTES" VALUES ("9", "GA", "102");
INSERT INTO "VOTES" VALUES ("10", "IL", "103");
INSERT INTO "VOTES" VALUES ("11", "NY", "104");
INSERT INTO "VOTES" VALUES ("12", "CA", "102");
INSERT INTO "VOTES" VALUES ("13", "NY", "103");
INSERT INTO "VOTES" VALUES ("14", "CA", "101");
INSERT INTO "VOTES" VALUES ("15", "FL", "104");