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 "employee" (
"Employee_ID" INT,
"City" TEXT,
PRIMARY KEY ("Employee_ID")
);

CREATE TABLE "evaluation" (
"Employee_ID" TEXT,
"Year_awarded" TEXT,
"Bonus" FLOAT,
PRIMARY KEY ("Employee_ID", "Year_awarded")
);

CREATE TABLE "hiring" (
"Employee_ID" INT,
PRIMARY KEY ("Employee_ID"),
FOREIGN KEY ("Employee_ID") REFERENCES "employee"("Employee_ID")
);

CREATE TABLE "shop" (
"Shop_ID" INT,
"Name" TEXT,
"Location" TEXT,
"District" TEXT,
"Number_products" INT,
PRIMARY KEY ("Shop_ID")
);





INSERT INTO "shop" VALUES ("1", "Green Grocers", "North End", "District A", "0");
INSERT INTO "shop" VALUES ("2", "Tech Hub", "Downtown", "District B", "150");
INSERT INTO "shop" VALUES ("3", "Book Nook", "East Side", "District C", "45");
INSERT INTO "shop" VALUES ("4", "Green Grocers", "West End", "District A", "30");
INSERT INTO "shop" VALUES ("5", "Fashion Point", "Uptown", "District D", "200");
INSERT INTO "shop" VALUES ("6", "Gadget World", "Downtown", "District B", "150");
INSERT INTO "shop" VALUES ("7", "Corner Market", "South End", "District E", "5");
INSERT INTO "shop" VALUES ("8", "Mega Store", "Central", "District F", "1000");
INSERT INTO "shop" VALUES ("9", "Mini Mart", "North End", "District A", "1");
INSERT INTO "shop" VALUES ("10", "Fashion Point", "Midtown", "District D", "200");
INSERT INTO "shop" VALUES ("11", "Old Books", "East Side", "District C", "0");
INSERT INTO "shop" VALUES ("12", "Tech Hub", "Uptown", "District B", "150");
INSERT INTO "shop" VALUES ("13", "Corner Market", "West End", "District E", "5");
INSERT INTO "shop" VALUES ("14", "Super Electronics", "Downtown", "District B", "300");
INSERT INTO "shop" VALUES ("15", "Super Electronics", "East Side", "District B", "300");