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),
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),
"size_description" VARCHAR(80),
PRIMARY KEY ("size_code")
);





INSERT INTO "Breeds" VALUES ("B001", "Labrador Retriever");
INSERT INTO "Breeds" VALUES ("B002", "German Shepherd");
INSERT INTO "Breeds" VALUES ("B003", "Bulldog");
INSERT INTO "Breeds" VALUES ("B004", "Beagle");
INSERT INTO "Breeds" VALUES ("B005", "Poodle");
INSERT INTO "Breeds" VALUES ("B006", "Rottweiler");
INSERT INTO "Breeds" VALUES ("B007", "Yorkshire Terrier");
INSERT INTO "Sizes" VALUES ("S", "Small");
INSERT INTO "Sizes" VALUES ("M", "Medium");
INSERT INTO "Sizes" VALUES ("L", "Large");
INSERT INTO "Sizes" VALUES ("XL", "Extra Large");
INSERT INTO "Dogs" VALUES ("1", "B001", "L");
INSERT INTO "Dogs" VALUES ("2", "B001", "L");
INSERT INTO "Dogs" VALUES ("3", "B001", "L");
INSERT INTO "Dogs" VALUES ("4", "B001", "L");
INSERT INTO "Dogs" VALUES ("5", "B001", "L");
INSERT INTO "Dogs" VALUES ("6", "B002", "L");
INSERT INTO "Dogs" VALUES ("7", "B002", "L");
INSERT INTO "Dogs" VALUES ("8", "B002", "L");
INSERT INTO "Dogs" VALUES ("9", "B003", "M");
INSERT INTO "Dogs" VALUES ("10", "B003", "M");
INSERT INTO "Dogs" VALUES ("11", "B004", "M");
INSERT INTO "Dogs" VALUES ("12", "B004", "M");
INSERT INTO "Dogs" VALUES ("13", "B004", "M");
INSERT INTO "Dogs" VALUES ("14", "B005", "S");
INSERT INTO "Dogs" VALUES ("15", "B005", "S");
INSERT INTO "Dogs" VALUES ("16", "B006", "XL");
INSERT INTO "Dogs" VALUES ("17", "B006", "XL");
INSERT INTO "Dogs" VALUES ("18", "B006", "XL");
INSERT INTO "Dogs" VALUES ("19", "B006", "XL");
INSERT INTO "Dogs" VALUES ("20", "B007", "S");