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 "Addresses" (
"address_id" INT,
"city" VARCHAR(255),
PRIMARY KEY ("address_id")
);

CREATE TABLE "Courses" (
"course_id" INT,
"course_name" VARCHAR(255),
PRIMARY KEY ("course_id")
);

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

CREATE TABLE "Semesters" (
"semester_id" INT UNIQUE,
PRIMARY KEY ("semester_id")
);

CREATE TABLE "Student_Enrolment" (
"student_enrolment_id" INT,
"degree_program_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"),
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),
PRIMARY KEY ("student_id")
);





INSERT INTO "Addresses" VALUES ("1", "New York");
INSERT INTO "Addresses" VALUES ("2", "Los Angeles");
INSERT INTO "Addresses" VALUES ("3", "Chicago");
INSERT INTO "Addresses" VALUES ("4", "Houston");
INSERT INTO "Addresses" VALUES ("5", "Phoenix");
INSERT INTO "Courses" VALUES ("101", "Introduction to Programming");
INSERT INTO "Courses" VALUES ("102", "Calculus I");
INSERT INTO "Courses" VALUES ("103", "Linear Algebra");
INSERT INTO "Courses" VALUES ("104", "Database Systems");
INSERT INTO "Courses" VALUES ("105", "Operating Systems");
INSERT INTO "Degree_Programs" VALUES ("1", "Computer Science BS");
INSERT INTO "Degree_Programs" VALUES ("2", "Mathematics BS");
INSERT INTO "Degree_Programs" VALUES ("3", "Electrical Engineering BS");
INSERT INTO "Degree_Programs" VALUES ("4", "Physics BS");
INSERT INTO "Degree_Programs" VALUES ("5", "Information Technology BS");
INSERT INTO "Semesters" VALUES ("202301");
INSERT INTO "Semesters" VALUES ("202302");
INSERT INTO "Semesters" VALUES ("202401");
INSERT INTO "Semesters" VALUES ("202402");
INSERT INTO "Semesters" VALUES ("202501");
INSERT INTO "Students" VALUES ("1001", "Alice", "M.", "Smith");
INSERT INTO "Students" VALUES ("1002", "Bob", "A.", "Johnson");
INSERT INTO "Students" VALUES ("1003", "Charlie", "D.", "Brown");
INSERT INTO "Students" VALUES ("1004", "Diana", "L.", "Miller");
INSERT INTO "Students" VALUES ("1005", "Eve", "P.", "Davis");
INSERT INTO "Students" VALUES ("1006", "Frank", "J.", "Garcia");
INSERT INTO "Students" VALUES ("1007", "Grace", "K.", "Rodriguez");
INSERT INTO "Students" VALUES ("1008", "Henry", "B.", "Martinez");
INSERT INTO "Student_Enrolment" VALUES ("1", "1", "202301", "1001");
INSERT INTO "Student_Enrolment" VALUES ("2", "2", "202301", "1001");
INSERT INTO "Student_Enrolment" VALUES ("3", "1", "202302", "1001");
INSERT INTO "Student_Enrolment" VALUES ("4", "3", "202301", "1002");
INSERT INTO "Student_Enrolment" VALUES ("5", "4", "202301", "1002");
INSERT INTO "Student_Enrolment" VALUES ("6", "1", "202401", "1003");
INSERT INTO "Student_Enrolment" VALUES ("7", "2", "202401", "1003");
INSERT INTO "Student_Enrolment" VALUES ("8", "5", "202302", "1004");
INSERT INTO "Student_Enrolment" VALUES ("9", "1", "202302", "1004");
INSERT INTO "Student_Enrolment" VALUES ("10", "2", "202302", "1005");
INSERT INTO "Student_Enrolment" VALUES ("11", "3", "202402", "1006");
INSERT INTO "Student_Enrolment" VALUES ("12", "4", "202402", "1006");
INSERT INTO "Student_Enrolment" VALUES ("13", "1", "202301", "1007");
INSERT INTO "Student_Enrolment" VALUES ("14", "2", "202301", "1007");
INSERT INTO "Student_Enrolment" VALUES ("15", "3", "202301", "1007");
INSERT INTO "Student_Enrolment" VALUES ("16", "1", "202301", "1008");