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

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

CREATE TABLE "cars_data" (
"Id" INT,
"MPG" TEXT,
"Cylinders" INT,
"Edispl" FLOAT,
"Horsepower" TEXT,
PRIMARY KEY ("Id"),
FOREIGN KEY ("Id") REFERENCES "car_names"("MakeId")
);

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





INSERT INTO "car_makers" VALUES ("1", "ford", "Ford Motor Company");
INSERT INTO "car_makers" VALUES ("2", "honda", "Honda Motor Co.");
INSERT INTO "car_makers" VALUES ("3", "fiat", "Fiat S.p.A.");
INSERT INTO "car_makers" VALUES ("4", "mazda", "Mazda Motor Corporation");
INSERT INTO "car_makers" VALUES ("5", "subaru", "Subaru Corporation");
INSERT INTO "car_makers" VALUES ("6", "suzuki", "Suzuki Motor Corporation");
INSERT INTO "car_makers" VALUES ("7", "bmw", "Bayerische Motoren Werke AG");
INSERT INTO "car_makers" VALUES ("8", "audi", "Audi AG");
INSERT INTO "model_list" VALUES ("101", "1", "Pinto");
INSERT INTO "model_list" VALUES ("102", "1", "Mustang");
INSERT INTO "model_list" VALUES ("201", "2", "Civic");
INSERT INTO "model_list" VALUES ("202", "2", "Accord");
INSERT INTO "model_list" VALUES ("301", "3", "124 Spider");
INSERT INTO "model_list" VALUES ("302", "3", "500");
INSERT INTO "model_list" VALUES ("401", "4", "RX-7");
INSERT INTO "model_list" VALUES ("402", "4", "Miata");
INSERT INTO "model_list" VALUES ("501", "5", "Impreza");
INSERT INTO "model_list" VALUES ("601", "6", "Swift");
INSERT INTO "model_list" VALUES ("701", "7", "i3");
INSERT INTO "model_list" VALUES ("801", "8", "A3");
INSERT INTO "car_names" VALUES ("101", "Pinto", "ford");
INSERT INTO "car_names" VALUES ("102", "Mustang", "ford");
INSERT INTO "car_names" VALUES ("201", "Civic", "honda");
INSERT INTO "car_names" VALUES ("202", "Accord", "honda");
INSERT INTO "car_names" VALUES ("301", "124 Spider", "fiat");
INSERT INTO "car_names" VALUES ("302", "500", "fiat");
INSERT INTO "car_names" VALUES ("401", "RX-7", "mazda");
INSERT INTO "car_names" VALUES ("402", "Miata", "mazda");
INSERT INTO "car_names" VALUES ("501", "Impreza", "subaru");
INSERT INTO "car_names" VALUES ("601", "Swift", "suzuki");
INSERT INTO "car_names" VALUES ("701", "i3", "bmw");
INSERT INTO "car_names" VALUES ("801", "A3", "audi");
INSERT INTO "cars_data" VALUES ("101", "28.0", "4", "120.0", "90");
INSERT INTO "cars_data" VALUES ("102", "20.0", "8", "302.0", "140");
INSERT INTO "cars_data" VALUES ("201", "33.0", "4", "91.0", "68");
INSERT INTO "cars_data" VALUES ("202", "25.0", "4", "119.0", "97");
INSERT INTO "cars_data" VALUES ("301", "27.0", "4", "107.0", "90");
INSERT INTO "cars_data" VALUES ("302", "30.0", "3", "80.0", "60");
INSERT INTO "cars_data" VALUES ("401", "19.0", "3", "70.0", "100");
INSERT INTO "cars_data" VALUES ("402", "26.0", "4", "116.0", "100");
INSERT INTO "cars_data" VALUES ("501", "24.0", "4", "121.0", "110");
INSERT INTO "cars_data" VALUES ("601", "35.0", "3", "79.0", "70");
INSERT INTO "cars_data" VALUES ("701", "40.0", "3", "60.0", "170");
INSERT INTO "cars_data" VALUES ("801", "29.0", "4", "109.0", "110");