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 "Breeds" VALUES ("BR006", "Chihuahua");
INSERT INTO "Breeds" VALUES ("BR007", "Great Dane");
INSERT INTO "Breeds" VALUES ("BR008", "Dachshund");
INSERT INTO "Breeds" VALUES ("BR009", "Boxer");
INSERT INTO "Breeds" VALUES ("BR010", "Siberian Husky");
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 ("1", "Alice", "Johnson", "alice.johnson@example.com");
INSERT INTO "Owners" VALUES ("2", "Bob", "Smith", "bob.smith@example.com");
INSERT INTO "Owners" VALUES ("3", "Charlie", "Brown", "charlie.brown@example.com");
INSERT INTO "Owners" VALUES ("4", "Diana", "Evans", "diana.evans@example.com");
INSERT INTO "Owners" VALUES ("5", "Ethan", "Williams", "ethan.williams@example.com");
INSERT INTO "Owners" VALUES ("6", "Fiona", "Clark", "fiona.clark@example.com");
INSERT INTO "Owners" VALUES ("7", "George", "Miller", "george.miller@example.com");
INSERT INTO "Owners" VALUES ("8", "Hannah", "Davis", "hannah.davis@example.com");
INSERT INTO "Owners" VALUES ("9", "Ian", "Wilson", "ian.wilson@example.com");
INSERT INTO "Owners" VALUES ("10", "Julia", "Moore", "julia.moore@example.com");
INSERT INTO "Dogs" VALUES ("101", "1", "BR001", "SZ03");
INSERT INTO "Dogs" VALUES ("102", "2", "BR006", "SZ01");
INSERT INTO "Dogs" VALUES ("103", "3", "BR004", "SZ02");
INSERT INTO "Dogs" VALUES ("104", "4", "BR007", "SZ04");
INSERT INTO "Dogs" VALUES ("105", "5", "BR003", "SZ02");
INSERT INTO "Dogs" VALUES ("106", "6", "BR010", "SZ03");
INSERT INTO "Dogs" VALUES ("107", "7", "BR002", "SZ03");
INSERT INTO "Dogs" VALUES ("108", "8", "BR008", "SZ01");
INSERT INTO "Dogs" VALUES ("109", "9", "BR009", "SZ03");
INSERT INTO "Dogs" VALUES ("110", "10", "BR005", "SZ02");
INSERT INTO "Dogs" VALUES ("111", "1", "BR005", "SZ01");
INSERT INTO "Dogs" VALUES ("112", "2", "BR003", "SZ02");
INSERT INTO "Dogs" VALUES ("113", "3", "BR001", "SZ03");
INSERT INTO "Dogs" VALUES ("114", "4", "BR009", "SZ03");
INSERT INTO "Dogs" VALUES ("115", "5", "BR006", "SZ01");