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 "Breeds" (
"breed_code" VARCHAR(10) UNIQUE,
"breed_name" VARCHAR(80),
PRIMARY KEY ("breed_code")
);

CREATE TABLE "Dogs" (
"dog_id" INT,
"breed_code" VARCHAR(10),
"size_code" VARCHAR(10),
"weight" VARCHAR(20),
PRIMARY KEY ("dog_id"),
FOREIGN KEY ("size_code") REFERENCES "Sizes"("size_code"),
FOREIGN KEY ("breed_code") REFERENCES "Breeds"("breed_code")
);

CREATE TABLE "Sizes" (
"size_code" VARCHAR(10) UNIQUE,
"size_description" VARCHAR(80),
PRIMARY KEY ("size_code")
);





INSERT INTO "Breeds" VALUES ("BR001", "Labrador Retriever");
INSERT INTO "Breeds" VALUES ("BR002", "German Shepherd");
INSERT INTO "Breeds" VALUES ("BR003", "Chihuahua");
INSERT INTO "Breeds" VALUES ("BR004", "Great Dane");
INSERT INTO "Breeds" VALUES ("BR005", "Bulldog");
INSERT INTO "Breeds" VALUES ("BR006", "Poodle");
INSERT INTO "Breeds" VALUES ("BR007", "Beagle");
INSERT INTO "Breeds" VALUES ("BR008", "Dachshund");
INSERT INTO "Sizes" VALUES ("SZ01", "Small");
INSERT INTO "Sizes" VALUES ("SZ02", "Medium");
INSERT INTO "Sizes" VALUES ("SZ03", "Large");
INSERT INTO "Sizes" VALUES ("SZ04", "Extra Large");
INSERT INTO "Dogs" VALUES ("1", "BR001", "SZ03", "65 lbs");
INSERT INTO "Dogs" VALUES ("2", "BR001", "SZ02", "50 lbs");
INSERT INTO "Dogs" VALUES ("3", "BR002", "SZ03", "75 lbs");
INSERT INTO "Dogs" VALUES ("4", "BR002", "SZ04", "90 lbs");
INSERT INTO "Dogs" VALUES ("5", "BR003", "SZ01", "6 lbs");
INSERT INTO "Dogs" VALUES ("6", "BR004", "SZ04", "140 lbs");
INSERT INTO "Dogs" VALUES ("7", "BR005", "SZ02", "50 lbs");
INSERT INTO "Dogs" VALUES ("8", "BR005", "SZ03", "55 lbs");
INSERT INTO "Dogs" VALUES ("9", "BR006", "SZ02", "45 lbs");
INSERT INTO "Dogs" VALUES ("10", "BR006", "SZ01", "15 lbs");
INSERT INTO "Dogs" VALUES ("11", "BR007", "SZ02", "25 lbs");
INSERT INTO "Dogs" VALUES ("12", "BR008", "SZ01", "20 lbs");
INSERT INTO "Dogs" VALUES ("13", "BR008", "SZ02", "25 lbs");