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 "Degree_Programs" (
"degree_program_id" INT,
"degree_summary_name" VARCHAR(255),
PRIMARY KEY ("degree_program_id")
);

CREATE TABLE "Student_Enrolment" (
"student_enrolment_id" INT,
"degree_program_id" INT,
"semester_id" INT,
"student_id" INT,
"other_details" VARCHAR(255),
PRIMARY KEY ("student_enrolment_id"),
FOREIGN KEY ("student_id") REFERENCES "Students"("student_id"),
FOREIGN KEY ("degree_program_id") REFERENCES "Degree_Programs"("degree_program_id")
);

CREATE TABLE "Students" (
"student_id" INT UNIQUE,
"first_name" VARCHAR(80),
"middle_name" VARCHAR(40),
"last_name" VARCHAR(40),
"email_address" VARCHAR(40),
PRIMARY KEY ("student_id")
);





INSERT INTO "Degree_Programs" VALUES ("1", "Computer Science");
INSERT INTO "Degree_Programs" VALUES ("2", "Mechanical Engineering");
INSERT INTO "Degree_Programs" VALUES ("3", "Business Administration");
INSERT INTO "Degree_Programs" VALUES ("4", "Psychology");
INSERT INTO "Degree_Programs" VALUES ("5", "Biology");
INSERT INTO "Students" VALUES ("101", "Alice", "Marie", "Johnson", "alice.johnson@example.com");
INSERT INTO "Students" VALUES ("102", "Bob", "Lee", "Smith", "bob.smith@example.com");
INSERT INTO "Students" VALUES ("103", "Charlie", "Ann", "Brown", "charlie.brown@example.com");
INSERT INTO "Students" VALUES ("104", "Diana", "Louise", "Green", "diana.green@example.com");
INSERT INTO "Students" VALUES ("105", "Ethan", "James", "White", "ethan.white@example.com");
INSERT INTO "Students" VALUES ("106", "Fiona", "Grace", "Black", "fiona.black@example.com");
INSERT INTO "Student_Enrolment" VALUES ("201", "1", "202301", "101", "Enrolled in CS program");
INSERT INTO "Student_Enrolment" VALUES ("202", "2", "202301", "101", "Also enrolled in ME program same semester");
INSERT INTO "Student_Enrolment" VALUES ("203", "3", "202301", "102", "Single program enrollment");
INSERT INTO "Student_Enrolment" VALUES ("204", "1", "202302", "103", "Single program enrollment next semester");
INSERT INTO "Student_Enrolment" VALUES ("205", "4", "202301", "104", "Single program enrollment");
INSERT INTO "Student_Enrolment" VALUES ("206", "2", "202301", "105", "Single program enrollment");
INSERT INTO "Student_Enrolment" VALUES ("207", "3", "202301", "106", "Enrolled in BA program");
INSERT INTO "Student_Enrolment" VALUES ("208", "5", "202301", "106", "Also enrolled in Biology same semester");
INSERT INTO "Student_Enrolment" VALUES ("209", "1", "202302", "106", "Enrolled in CS next semester only");
INSERT INTO "Student_Enrolment" VALUES ("210", "4", "202302", "101", "Enrolled in Psychology next semester only");