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 "car_makers" (
"Id" INT UNIQUE,
"Maker" TEXT,
"Country" TEXT,
PRIMARY KEY ("Id"),
FOREIGN KEY ("Country") REFERENCES "countries"("CountryId")
);

CREATE TABLE "car_names" (
"MakeId" INT,
"Model" TEXT,
PRIMARY KEY ("MakeId"),
FOREIGN KEY ("Model") REFERENCES "model_list"("Model")
);

CREATE TABLE "countries" (
"CountryId" INT UNIQUE,
"CountryName" TEXT,
"Continent" INT,
PRIMARY KEY ("CountryId")
);

CREATE TABLE "model_list" (
"ModelId" INT,
"Maker" INT,
"Model" TEXT UNIQUE,
PRIMARY KEY ("ModelId"),
FOREIGN KEY ("Maker") REFERENCES "car_makers"("Id")
);





INSERT INTO "countries" VALUES ("1", "Italy", "1");
INSERT INTO "countries" VALUES ("2", "Germany", "1");
INSERT INTO "countries" VALUES ("3", "Japan", "1");
INSERT INTO "countries" VALUES ("4", "USA", "1");
INSERT INTO "countries" VALUES ("5", "France", "1");
INSERT INTO "countries" VALUES ("6", "South Korea", "1");
INSERT INTO "car_makers" VALUES ("1", "fiat", "1");
INSERT INTO "car_makers" VALUES ("2", "Ferrari", "1");
INSERT INTO "car_makers" VALUES ("3", "Lamborghini", "1");
INSERT INTO "car_makers" VALUES ("4", "Maserati", "1");
INSERT INTO "car_makers" VALUES ("5", "Volkswagen", "2");
INSERT INTO "car_makers" VALUES ("6", "BMW", "2");
INSERT INTO "car_makers" VALUES ("7", "Audi", "2");
INSERT INTO "car_makers" VALUES ("8", "Mercedes", "2");
INSERT INTO "car_makers" VALUES ("9", "Toyota", "3");
INSERT INTO "car_makers" VALUES ("10", "Honda", "3");
INSERT INTO "car_makers" VALUES ("11", "Nissan", "3");
INSERT INTO "car_makers" VALUES ("12", "Ford", "4");
INSERT INTO "car_makers" VALUES ("13", "Chevrolet", "4");
INSERT INTO "car_makers" VALUES ("14", "Peugeot", "5");
INSERT INTO "model_list" VALUES ("1", "1", "fiat");
INSERT INTO "model_list" VALUES ("2", "1", "panda");
INSERT INTO "model_list" VALUES ("3", "2", "f8");
INSERT INTO "model_list" VALUES ("4", "3", "huracan");
INSERT INTO "model_list" VALUES ("5", "4", "ghibli");
INSERT INTO "model_list" VALUES ("6", "5", "golf");
INSERT INTO "model_list" VALUES ("7", "6", "x5");
INSERT INTO "model_list" VALUES ("8", "7", "a4");
INSERT INTO "model_list" VALUES ("9", "8", "cclass");
INSERT INTO "model_list" VALUES ("10", "9", "corolla");
INSERT INTO "model_list" VALUES ("11", "10", "civic");
INSERT INTO "model_list" VALUES ("12", "11", "altima");
INSERT INTO "model_list" VALUES ("13", "12", "mustang");
INSERT INTO "model_list" VALUES ("14", "13", "camaro");
INSERT INTO "model_list" VALUES ("15", "14", "208");
INSERT INTO "car_names" VALUES ("1", "fiat");
INSERT INTO "car_names" VALUES ("2", "golf");
INSERT INTO "car_names" VALUES ("5", "ghibli");
INSERT INTO "car_names" VALUES ("9", "cclass");
INSERT INTO "car_names" VALUES ("14", "208");