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

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

CREATE TABLE "Owners" (
"owner_id" INT UNIQUE,
"first_name" VARCHAR(50),
"last_name" VARCHAR(50),
"email_address" VARCHAR(50),
PRIMARY KEY ("owner_id")
);

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", "Bulldog");
INSERT INTO "Breeds" VALUES ("BR004", "Beagle");
INSERT INTO "Breeds" VALUES ("BR005", "Poodle");
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 "Owners" VALUES ("101", "Alice", "Johnson", "alice.johnson@example.com");
INSERT INTO "Owners" VALUES ("102", "Bob", "Smith", "bob.smith@example.com");
INSERT INTO "Owners" VALUES ("103", "Carol", "Davis", "carol.davis@example.com");
INSERT INTO "Owners" VALUES ("104", "David", "Miller", "david.miller@example.com");
INSERT INTO "Owners" VALUES ("105", "Eva", "Wilson", "eva.wilson@example.com");
INSERT INTO "Owners" VALUES ("106", "Frank", "Moore", "frank.moore@example.com");
INSERT INTO "Dogs" VALUES ("201", "101", "BR001", "SZ03");
INSERT INTO "Dogs" VALUES ("202", "102", "BR002", "SZ04");
INSERT INTO "Dogs" VALUES ("203", "103", "BR003", "SZ02");
INSERT INTO "Dogs" VALUES ("204", "104", "BR004", "SZ01");
INSERT INTO "Dogs" VALUES ("205", "105", "BR005", "SZ02");
INSERT INTO "Dogs" VALUES ("206", "106", "BR001", "SZ03");
INSERT INTO "Dogs" VALUES ("207", "101", "BR004", "SZ01");
INSERT INTO "Dogs" VALUES ("208", "102", "BR005", "SZ02");