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 "Dogs" (
"dog_id" INT UNIQUE,
"owner_id" INT,
"abandoned_yn" VARCHAR(1),
PRIMARY KEY ("dog_id"),
FOREIGN KEY ("owner_id") REFERENCES "Owners"("owner_id")
);

CREATE TABLE "Owners" (
"owner_id" INT UNIQUE,
"city" VARCHAR(50),
"zip_code" VARCHAR(20),
PRIMARY KEY ("owner_id")
);

CREATE TABLE "Treatment_Types" (
"treatment_type_code" VARCHAR(10),
"treatment_type_description" VARCHAR(80),
PRIMARY KEY ("treatment_type_code")
);

CREATE TABLE "Treatments" (
"treatment_id" INT,
"dog_id" INT,
"cost_of_treatment" DECIMAL(19, 4),
PRIMARY KEY ("treatment_id"),
FOREIGN KEY ("dog_id") REFERENCES "Dogs"("dog_id")
);





INSERT INTO "Owners" VALUES ("1", "New York", "10001");
INSERT INTO "Owners" VALUES ("2", "Los Angeles", "90001");
INSERT INTO "Owners" VALUES ("3", "Chicago", "60601");
INSERT INTO "Owners" VALUES ("4", "Houston", "77001");
INSERT INTO "Owners" VALUES ("5", "Phoenix", "85001");
INSERT INTO "Owners" VALUES ("6", "Philadelphia", "19101");
INSERT INTO "Owners" VALUES ("7", "San Antonio", "78201");
INSERT INTO "Owners" VALUES ("8", "San Diego", "92101");
INSERT INTO "Owners" VALUES ("9", "Dallas", "75201");
INSERT INTO "Owners" VALUES ("10", "San Jose", "95101");
INSERT INTO "Dogs" VALUES ("101", "1", "N");
INSERT INTO "Dogs" VALUES ("102", "1", "N");
INSERT INTO "Dogs" VALUES ("103", "2", "N");
INSERT INTO "Dogs" VALUES ("104", "2", "N");
INSERT INTO "Dogs" VALUES ("105", "3", "N");
INSERT INTO "Dogs" VALUES ("106", "3", "N");
INSERT INTO "Dogs" VALUES ("107", "4", "N");
INSERT INTO "Dogs" VALUES ("108", "5", "N");
INSERT INTO "Dogs" VALUES ("109", "6", "N");
INSERT INTO "Dogs" VALUES ("110", "7", "N");
INSERT INTO "Dogs" VALUES ("111", "8", "N");
INSERT INTO "Dogs" VALUES ("112", "9", "N");
INSERT INTO "Dogs" VALUES ("113", "10", "N");
INSERT INTO "Dogs" VALUES ("114", "1", "Y");
INSERT INTO "Dogs" VALUES ("115", "2", "Y");
INSERT INTO "Treatment_Types" VALUES ("VACC", "Vaccination");
INSERT INTO "Treatment_Types" VALUES ("SURG", "Surgery");
INSERT INTO "Treatment_Types" VALUES ("MEDS", "Medication");
INSERT INTO "Treatment_Types" VALUES ("CHECK", "General Check-up");
INSERT INTO "Treatment_Types" VALUES ("DENT", "Dental Cleaning");
INSERT INTO "Treatments" VALUES ("1001", "101", "50.00");
INSERT INTO "Treatments" VALUES ("1002", "101", "120.50");
INSERT INTO "Treatments" VALUES ("1003", "102", "75.25");
INSERT INTO "Treatments" VALUES ("1004", "103", "200.00");
INSERT INTO "Treatments" VALUES ("1005", "103", "350.75");
INSERT INTO "Treatments" VALUES ("1006", "104", "150.00");
INSERT INTO "Treatments" VALUES ("1007", "105", "90.00");
INSERT INTO "Treatments" VALUES ("1008", "105", "110.00");
INSERT INTO "Treatments" VALUES ("1009", "106", "250.00");
INSERT INTO "Treatments" VALUES ("1010", "107", "300.00");
INSERT INTO "Treatments" VALUES ("1011", "107", "400.00");
INSERT INTO "Treatments" VALUES ("1012", "108", "60.00");
INSERT INTO "Treatments" VALUES ("1013", "109", "80.00");
INSERT INTO "Treatments" VALUES ("1014", "110", "100.00");
INSERT INTO "Treatments" VALUES ("1015", "111", "130.00");
INSERT INTO "Treatments" VALUES ("1016", "112", "180.00");
INSERT INTO "Treatments" VALUES ("1017", "113", "220.00");
INSERT INTO "Treatments" VALUES ("1018", "101", "500.00");
INSERT INTO "Treatments" VALUES ("1019", "102", "10.00");
INSERT INTO "Treatments" VALUES ("1020", "103", "5.00");
INSERT INTO "Treatments" VALUES ("1021", "104", "1000.00");
INSERT INTO "Treatments" VALUES ("1022", "104", "2000.00");
INSERT INTO "Treatments" VALUES ("1023", "101", "100.00");
INSERT INTO "Treatments" VALUES ("1024", "102", "20.00");
INSERT INTO "Treatments" VALUES ("1025", "103", "15.00");
INSERT INTO "Treatments" VALUES ("1026", "104", "500.00");
INSERT INTO "Treatments" VALUES ("1027", "105", "25.00");
INSERT INTO "Treatments" VALUES ("1028", "106", "30.00");
INSERT INTO "Treatments" VALUES ("1029", "107", "40.00");
INSERT INTO "Treatments" VALUES ("1030", "108", "50.00");
INSERT INTO "Treatments" VALUES ("1031", "109", "60.00");
INSERT INTO "Treatments" VALUES ("1032", "110", "70.00");
INSERT INTO "Treatments" VALUES ("1033", "111", "80.00");
INSERT INTO "Treatments" VALUES ("1034", "112", "90.00");
INSERT INTO "Treatments" VALUES ("1035", "113", "100.00");