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 "Courses" (
"course_id" INT UNIQUE,
"course_name" VARCHAR(255),
PRIMARY KEY ("course_id")
);

CREATE TABLE "Degree_Programs" (
"degree_program_id" INT,
"degree_summary_name" VARCHAR(255),
PRIMARY KEY ("degree_program_id")
);

CREATE TABLE "Sections" (
"section_id" INT,
"course_id" INT,
PRIMARY KEY ("section_id"),
FOREIGN KEY ("course_id") REFERENCES "Courses"("course_id")
);

CREATE TABLE "Semesters" (
"semester_id" INT,
"semester_name" VARCHAR(255),
PRIMARY KEY ("semester_id")
);

CREATE TABLE "Student_Enrolment" (
"student_enrolment_id" INT UNIQUE,
"student_id" INT,
PRIMARY KEY ("student_enrolment_id"),
FOREIGN KEY ("student_id") REFERENCES "Students"("student_id")
);

CREATE TABLE "Student_Enrolment_Courses" (
"student_course_id" INT,
"course_id" INT,
"student_enrolment_id" INT,
PRIMARY KEY ("student_course_id"),
FOREIGN KEY ("student_enrolment_id") REFERENCES "Student_Enrolment"("student_enrolment_id"),
FOREIGN KEY ("course_id") REFERENCES "Courses"("course_id")
);

CREATE TABLE "Students" (
"student_id" INT UNIQUE,
PRIMARY KEY ("student_id")
);





INSERT INTO "Courses" VALUES ("101", "Introduction to Psychology");
INSERT INTO "Courses" VALUES ("102", "Calculus I");
INSERT INTO "Courses" VALUES ("103", "World History");
INSERT INTO "Courses" VALUES ("104", "Organic Chemistry");
INSERT INTO "Courses" VALUES ("105", "Introduction to Programming");
INSERT INTO "Courses" VALUES ("106", "Philosophy of Science");
INSERT INTO "Courses" VALUES ("107", "Advanced Physics");
INSERT INTO "Courses" VALUES ("108", "Creative Writing");
INSERT INTO "Courses" VALUES ("109", "Microeconomics");
INSERT INTO "Courses" VALUES ("110", "Art History");
INSERT INTO "Degree_Programs" VALUES ("1", "Bachelor of Science");
INSERT INTO "Degree_Programs" VALUES ("2", "Bachelor of Arts");
INSERT INTO "Degree_Programs" VALUES ("3", "Bachelor of Engineering");
INSERT INTO "Sections" VALUES ("201", "101");
INSERT INTO "Sections" VALUES ("202", "102");
INSERT INTO "Sections" VALUES ("203", "103");
INSERT INTO "Sections" VALUES ("204", "104");
INSERT INTO "Sections" VALUES ("205", "105");
INSERT INTO "Sections" VALUES ("206", "106");
INSERT INTO "Sections" VALUES ("207", "107");
INSERT INTO "Sections" VALUES ("208", "108");
INSERT INTO "Sections" VALUES ("209", "109");
INSERT INTO "Sections" VALUES ("210", "110");
INSERT INTO "Semesters" VALUES ("301", "Fall 2023");
INSERT INTO "Semesters" VALUES ("302", "Spring 2024");
INSERT INTO "Semesters" VALUES ("303", "Summer 2024");
INSERT INTO "Students" VALUES ("401");
INSERT INTO "Students" VALUES ("402");
INSERT INTO "Students" VALUES ("403");
INSERT INTO "Students" VALUES ("404");
INSERT INTO "Students" VALUES ("405");
INSERT INTO "Student_Enrolment" VALUES ("501", "401");
INSERT INTO "Student_Enrolment" VALUES ("502", "402");
INSERT INTO "Student_Enrolment" VALUES ("503", "403");
INSERT INTO "Student_Enrolment" VALUES ("504", "404");
INSERT INTO "Student_Enrolment" VALUES ("505", "405");
INSERT INTO "Student_Enrolment_Courses" VALUES ("601", "101", "501");
INSERT INTO "Student_Enrolment_Courses" VALUES ("602", "102", "501");
INSERT INTO "Student_Enrolment_Courses" VALUES ("603", "105", "502");
INSERT INTO "Student_Enrolment_Courses" VALUES ("604", "103", "503");
INSERT INTO "Student_Enrolment_Courses" VALUES ("605", "108", "504");
INSERT INTO "Student_Enrolment_Courses" VALUES ("606", "109", "505");
INSERT INTO "Student_Enrolment_Courses" VALUES ("607", "110", "505");
INSERT INTO "Student_Enrolment_Courses" VALUES ("608", "104", "502");
INSERT INTO "Student_Enrolment_Courses" VALUES ("609", "106", "503");
INSERT INTO "Student_Enrolment_Courses" VALUES ("610", "107", "504");