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 "battle" (
"id" INT UNIQUE,
"name" TEXT,
"date" TEXT,
PRIMARY KEY ("id")
);

CREATE TABLE "death" (
"caused_by_ship_id" INT,
"id" INT,
PRIMARY KEY ("id"),
FOREIGN KEY ("caused_by_ship_id") REFERENCES "ship"("id")
);

CREATE TABLE "ship" (
"lost_in_battle" INT,
"id" INT UNIQUE,
"name" TEXT,
"ship_type" TEXT,
PRIMARY KEY ("id"),
FOREIGN KEY ("lost_in_battle") REFERENCES "battle"("id")
);





INSERT INTO "battle" VALUES ("1", "Battle of Trafalgar", "1805-10-21");
INSERT INTO "battle" VALUES ("2", "Battle of the Nile", "1798-08-01");
INSERT INTO "battle" VALUES ("3", "Battle of Midway", "1942-06-04");
INSERT INTO "battle" VALUES ("4", "Battle of Jutland", "1916-05-31");
INSERT INTO "battle" VALUES ("5", "Battle of Tsushima", "1905-05-27");
INSERT INTO "battle" VALUES ("6", "Battle of Cape St. Vincent", "1797-02-14");
INSERT INTO "battle" VALUES ("7", "Battle of the Atlantic", "1939-09-01");
INSERT INTO "battle" VALUES ("8", "Battle of Leyte Gulf", "1944-10-23");
INSERT INTO "battle" VALUES ("9", "Battle of Salamis", "0480-09-29");
INSERT INTO "battle" VALUES ("10", "Battle of Actium", "0031-09-02");
INSERT INTO "ship" VALUES ("1", "101", "HMS Victory", "Ship of the Line");
INSERT INTO "ship" VALUES ("1", "102", "Redoutable", "Ship of the Line");
INSERT INTO "ship" VALUES ("2", "103", "L’Orient", "Ship of the Line");
INSERT INTO "ship" VALUES ("3", "104", "USS Yorktown", "Aircraft Carrier");
INSERT INTO "ship" VALUES ("3", "105", "IJN Kaga", "Aircraft Carrier");
INSERT INTO "ship" VALUES ("4", "106", "HMS Invincible", "Battlecruiser");
INSERT INTO "ship" VALUES ("5", "107", "Knyaz Suvorov", "Battleship");
INSERT INTO "ship" VALUES ("6", "108", "HMS Captain", "Brig");
INSERT INTO "ship" VALUES ("6", "109", "San Nicolas", "Brig");
INSERT INTO "ship" VALUES ("7", "110", "U-47", "Submarine");
INSERT INTO "ship" VALUES ("8", "111", "USS Gambier Bay", "Escort Carrier");
INSERT INTO "ship" VALUES ("1", "112", "HMS Bellerophon", "Brig");
INSERT INTO "ship" VALUES ("2", "113", "Leander", "Frigate");
INSERT INTO "ship" VALUES ("3", "114", "USS Hammann", "Destroyer");
INSERT INTO "ship" VALUES ("4", "115", "SMS Lützow", "Battlecruiser");
INSERT INTO "ship" VALUES ("5", "116", "Oryol", "Battleship");
INSERT INTO "ship" VALUES ("6", "117", "HMS Excellent", "Brig");
INSERT INTO "ship" VALUES ("7", "118", "HMS Hood", "Battlecruiser");
INSERT INTO "ship" VALUES ("8", "119", "IJN Musashi", "Battleship");
INSERT INTO "ship" VALUES ("9", "120", "Themistocles", "Trireme");
INSERT INTO "ship" VALUES ("10", "121", "Antonia", "Quinquereme");
INSERT INTO "ship" VALUES ("NULL", "122", "HMS Surprise", "Frigate");
INSERT INTO "ship" VALUES ("NULL", "123", "USS Constitution", "Frigate");
INSERT INTO "ship" VALUES ("NULL", "124", "Black Pearl", "Brig");
INSERT INTO "ship" VALUES ("NULL", "125", "Flying Dutchman", "Brig");
INSERT INTO "death" VALUES ("102", "1");
INSERT INTO "death" VALUES ("103", "2");
INSERT INTO "death" VALUES ("104", "3");
INSERT INTO "death" VALUES ("105", "4");
INSERT INTO "death" VALUES ("106", "5");
INSERT INTO "death" VALUES ("107", "6");
INSERT INTO "death" VALUES ("108", "7");
INSERT INTO "death" VALUES ("109", "8");
INSERT INTO "death" VALUES ("110", "9");
INSERT INTO "death" VALUES ("111", "10");
INSERT INTO "death" VALUES ("112", "11");
INSERT INTO "death" VALUES ("114", "12");
INSERT INTO "death" VALUES ("115", "13");
INSERT INTO "death" VALUES ("116", "14");
INSERT INTO "death" VALUES ("117", "15");
INSERT INTO "death" VALUES ("118", "16");
INSERT INTO "death" VALUES ("119", "17");
INSERT INTO "death" VALUES ("120", "18");
INSERT INTO "death" VALUES ("121", "19");