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),
"name" VARCHAR(50),
"age" 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 ("LAB", "Labrador Retriever");
INSERT INTO "Breeds" VALUES ("GSD", "German Shepherd Dog");
INSERT INTO "Breeds" VALUES ("POO", "Poodle");
INSERT INTO "Breeds" VALUES ("BEA", "Beagle");
INSERT INTO "Breeds" VALUES ("BUL", "Bulldog");
INSERT INTO "Breeds" VALUES ("CHI", "Chihuahua");
INSERT INTO "Breeds" VALUES ("DACH", "Dachshund");
INSERT INTO "Breeds" VALUES ("BOX", "Boxer");
INSERT INTO "Breeds" VALUES ("AUS", "Australian Shepherd");
INSERT INTO "Breeds" VALUES ("COR", "Corgi");
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 "Sizes" VALUES ("XS", "Extra Small");
INSERT INTO "Dogs" VALUES ("1", "LAB", "L", "Buddy", "5 years");
INSERT INTO "Dogs" VALUES ("2", "GSD", "L", "Max", "3 years");
INSERT INTO "Dogs" VALUES ("3", "POO", "M", "Bella", "2 years");
INSERT INTO "Dogs" VALUES ("4", "BEA", "M", "Lucy", "4 years");
INSERT INTO "Dogs" VALUES ("5", "BUL", "M", "Rocky", "6 years");
INSERT INTO "Dogs" VALUES ("6", "CHI", "S", "Daisy", "1 year");
INSERT INTO "Dogs" VALUES ("7", "DACH", "S", "Charlie", "7 years");
INSERT INTO "Dogs" VALUES ("8", "BOX", "L", "Duke", "3 years");
INSERT INTO "Dogs" VALUES ("9", "AUS", "M", "Sadie", "2 years");
INSERT INTO "Dogs" VALUES ("10", "COR", "S", "Winston", "5 years");
INSERT INTO "Dogs" VALUES ("11", "LAB", "M", "Molly", "1 year");
INSERT INTO "Dogs" VALUES ("12", "GSD", "XL", "Zeus", "4 years");
INSERT INTO "Dogs" VALUES ("13", "POO", "S", "Coco", "3 years");
INSERT INTO "Dogs" VALUES ("14", "BEA", "S", "Cooper", "2 years");
INSERT INTO "Dogs" VALUES ("15", "BUL", "L", "Roxy", "5 years");
INSERT INTO "Dogs" VALUES ("16", "CHI", "XS", "Gizmo", "6 months");
INSERT INTO "Dogs" VALUES ("17", "DACH", "XS", "Peanut", "8 years");
INSERT INTO "Dogs" VALUES ("18", "BOX", "M", "Apollo", "1 year");
INSERT INTO "Dogs" VALUES ("19", "AUS", "L", "Luna", "3 years");
INSERT INTO "Dogs" VALUES ("20", "COR", "M", "Oliver", "4 years");
INSERT INTO "Dogs" VALUES ("21", "LAB", "XL", "Shadow", "6 years");
INSERT INTO "Dogs" VALUES ("22", "GSD", "M", "Bear", "2 years");
INSERT INTO "Dogs" VALUES ("23", "POO", "L", "Sophie", "4 years");
INSERT INTO "Dogs" VALUES ("24", "BEA", "L", "Bailey", "1 year");
INSERT INTO "Dogs" VALUES ("25", "BUL", "S", "Frank", "7 years");
INSERT INTO "Dogs" VALUES ("26", "CHI", "M", "Princess", "2 years");
INSERT INTO "Dogs" VALUES ("27", "DACH", "M", "Oscar", "3 years");
INSERT INTO "Dogs" VALUES ("28", "BOX", "S", "Ruby", "5 years");
INSERT INTO "Dogs" VALUES ("29", "AUS", "S", "Willow", "6 years");
INSERT INTO "Dogs" VALUES ("30", "COR", "L", "Leo", "1 year");