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 "Semesters" (
"semester_id" INT UNIQUE,
"semester_name" VARCHAR(255),
"semester_description" VARCHAR(255),
PRIMARY KEY ("semester_id")
);

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

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





INSERT INTO "Degree_Programs" VALUES ("1", "Computer Science");
INSERT INTO "Degree_Programs" VALUES ("2", "Business Administration");
INSERT INTO "Degree_Programs" VALUES ("3", "Mechanical Engineering");
INSERT INTO "Semesters" VALUES ("101", "Fall 2023", "Fall semester of 2023");
INSERT INTO "Semesters" VALUES ("102", "Spring 2024", "Spring semester of 2024");
INSERT INTO "Semesters" VALUES ("103", "Summer 2024", "Summer semester of 2024");
INSERT INTO "Semesters" VALUES ("104", "Winter 2024", "Winter semester of 2024");
INSERT INTO "Students" VALUES ("201", "Alice");
INSERT INTO "Students" VALUES ("202", "Bob");
INSERT INTO "Students" VALUES ("203", "Charlie");
INSERT INTO "Students" VALUES ("204", "Diana");
INSERT INTO "Students" VALUES ("205", "Ethan");
INSERT INTO "Students" VALUES ("206", "Fiona");
INSERT INTO "Students" VALUES ("207", "George");
INSERT INTO "Students" VALUES ("208", "Hannah");
INSERT INTO "Students" VALUES ("209", "Ian");
INSERT INTO "Students" VALUES ("210", "Jane");
INSERT INTO "Students" VALUES ("211", "Kyle");
INSERT INTO "Students" VALUES ("212", "Laura");
INSERT INTO "Students" VALUES ("213", "Mike");
INSERT INTO "Students" VALUES ("214", "Nina");
INSERT INTO "Students" VALUES ("215", "Oscar");
INSERT INTO "Student_Enrolment" VALUES ("301", "101", "201");
INSERT INTO "Student_Enrolment" VALUES ("302", "101", "202");
INSERT INTO "Student_Enrolment" VALUES ("303", "101", "203");
INSERT INTO "Student_Enrolment" VALUES ("304", "101", "204");
INSERT INTO "Student_Enrolment" VALUES ("305", "101", "205");
INSERT INTO "Student_Enrolment" VALUES ("306", "101", "206");
INSERT INTO "Student_Enrolment" VALUES ("307", "101", "207");
INSERT INTO "Student_Enrolment" VALUES ("308", "101", "208");
INSERT INTO "Student_Enrolment" VALUES ("309", "101", "209");
INSERT INTO "Student_Enrolment" VALUES ("310", "102", "210");
INSERT INTO "Student_Enrolment" VALUES ("311", "102", "211");
INSERT INTO "Student_Enrolment" VALUES ("312", "102", "212");
INSERT INTO "Student_Enrolment" VALUES ("313", "102", "213");
INSERT INTO "Student_Enrolment" VALUES ("314", "102", "214");
INSERT INTO "Student_Enrolment" VALUES ("315", "102", "215");
INSERT INTO "Student_Enrolment" VALUES ("316", "102", "201");
INSERT INTO "Student_Enrolment" VALUES ("317", "102", "202");
INSERT INTO "Student_Enrolment" VALUES ("318", "102", "203");
INSERT INTO "Student_Enrolment" VALUES ("319", "102", "204");
INSERT INTO "Student_Enrolment" VALUES ("320", "103", "205");
INSERT INTO "Student_Enrolment" VALUES ("321", "103", "206");
INSERT INTO "Student_Enrolment" VALUES ("322", "103", "207");
INSERT INTO "Student_Enrolment" VALUES ("323", "104", "208");
INSERT INTO "Student_Enrolment" VALUES ("324", "104", "209");
INSERT INTO "Student_Enrolment" VALUES ("325", "104", "210");
INSERT INTO "Student_Enrolment" VALUES ("326", "104", "211");
INSERT INTO "Student_Enrolment" VALUES ("327", "104", "212");
INSERT INTO "Student_Enrolment" VALUES ("328", "104", "213");
INSERT INTO "Student_Enrolment" VALUES ("329", "104", "214");
INSERT INTO "Student_Enrolment" VALUES ("330", "104", "215");
INSERT INTO "Student_Enrolment" VALUES ("331", "104", "201");
INSERT INTO "Student_Enrolment" VALUES ("332", "104", "202");
INSERT INTO "Student_Enrolment" VALUES ("333", "104", "203");
INSERT INTO "Student_Enrolment" VALUES ("334", "104", "204");
INSERT INTO "Student_Enrolment" VALUES ("335", "104", "205");