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),
"gender" VARCHAR(1),
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 ("SIB", "Siberian Husky");
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", "3 years", "M");
INSERT INTO "Dogs" VALUES ("2", "GSD", "L", "Max", "5 years", "M");
INSERT INTO "Dogs" VALUES ("3", "POO", "M", "Bella", "2 years", "F");
INSERT INTO "Dogs" VALUES ("4", "BEA", "M", "Lucy", "4 years", "F");
INSERT INTO "Dogs" VALUES ("5", "BUL", "M", "Rocky", "6 years", "M");
INSERT INTO "Dogs" VALUES ("6", "CHI", "S", "Daisy", "1 year", "F");
INSERT INTO "Dogs" VALUES ("7", "DACH", "S", "Frankie", "7 years", "M");
INSERT INTO "Dogs" VALUES ("8", "BOX", "L", "Duke", "3 years", "M");
INSERT INTO "Dogs" VALUES ("9", "AUS", "L", "Sadie", "2 years", "F");
INSERT INTO "Dogs" VALUES ("10", "SIB", "L", "Luna", "4 years", "F");
INSERT INTO "Dogs" VALUES ("11", "LAB", "L", "Charlie", "1 year", "M");
INSERT INTO "Dogs" VALUES ("12", "GSD", "L", "Bailey", "6 years", "F");
INSERT INTO "Dogs" VALUES ("13", "POO", "S", "Milo", "3 years", "M");
INSERT INTO "Dogs" VALUES ("14", "BEA", "M", "Penny", "2 years", "F");
INSERT INTO "Dogs" VALUES ("15", "BUL", "M", "Gus", "5 years", "M");
INSERT INTO "Dogs" VALUES ("16", "CHI", "XS", "Coco", "2 years", "F");
INSERT INTO "Dogs" VALUES ("17", "DACH", "S", "Oscar", "4 years", "M");
INSERT INTO "Dogs" VALUES ("18", "BOX", "L", "Roxy", "1 year", "F");
INSERT INTO "Dogs" VALUES ("19", "AUS", "M", "Cooper", "3 years", "M");
INSERT INTO "Dogs" VALUES ("20", "SIB", "L", "Zoe", "5 years", "F");
INSERT INTO "Dogs" VALUES ("21", "LAB", "M", "Maxine", "2 years", "F");
INSERT INTO "Dogs" VALUES ("22", "POO", "M", "Leo", "1 year", "M");
INSERT INTO "Dogs" VALUES ("23", "BUL", "L", "Winston", "4 years", "M");
INSERT INTO "Dogs" VALUES ("24", "CHI", "S", "Peanut", "3 years", "F");
INSERT INTO "Dogs" VALUES ("25", "DACH", "M", "Ruby", "2 years", "F");
INSERT INTO "Dogs" VALUES ("26", "BOX", "XL", "Apollo", "5 years", "M");
INSERT INTO "Dogs" VALUES ("27", "AUS", "L", "Willow", "1 year", "F");
INSERT INTO "Dogs" VALUES ("28", "SIB", "XL", "Koda", "3 years", "M");
INSERT INTO "Dogs" VALUES ("29", "LAB", "XL", "Bear", "4 years", "M");
INSERT INTO "Dogs" VALUES ("30", "GSD", "XL", "Shadow", "2 years", "F");