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

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





INSERT INTO "battle" VALUES ("1", "Battle of Trafalgar");
INSERT INTO "battle" VALUES ("2", "Battle of Midway");
INSERT INTO "battle" VALUES ("3", "Battle of Jutland");
INSERT INTO "battle" VALUES ("4", "Battle of Leyte Gulf");
INSERT INTO "battle" VALUES ("5", "Battle of the Coral Sea");
INSERT INTO "ship" VALUES ("1", "101", "Victory", "3500 tons", "Ship of the line");
INSERT INTO "ship" VALUES ("1", "102", "Royal Sovereign", "2800 tons", "Ship of the line");
INSERT INTO "ship" VALUES ("2", "201", "Yorktown", "20000 tons", "Aircraft carrier");
INSERT INTO "ship" VALUES ("2", "202", "Enterprise", "25000 tons", "Aircraft carrier");
INSERT INTO "ship" VALUES ("3", "301", "Lion", "28000 tons", "Battlecruiser");
INSERT INTO "ship" VALUES ("3", "302", "Queen Mary", "32000 tons", "Battlecruiser");
INSERT INTO "ship" VALUES ("4", "401", "Musashi", "72000 tons", "Battleship");
INSERT INTO "ship" VALUES ("4", "402", "Yamato", "72000 tons", "Battleship");
INSERT INTO "ship" VALUES ("5", "501", "Lexington", "35000 tons", "Aircraft carrier");
INSERT INTO "ship" VALUES ("5", "502", "Saratoga", "35000 tons", "Aircraft carrier");
INSERT INTO "ship" VALUES ("5", "503", "USS Neosho", "9000 tons", "Oiler");
INSERT INTO "ship" VALUES ("5", "504", "USS Sims", "12000 tons", "Destroyer");