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 UNIQUE,
PRIMARY KEY ("address_id")
);

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

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

CREATE TABLE "Student_Enrolment" (
"student_enrolment_id" INT,
"degree_program_id" INT,
PRIMARY KEY ("student_enrolment_id"),
FOREIGN KEY ("degree_program_id") REFERENCES "Degree_Programs"("degree_program_id")
);

CREATE TABLE "Students" (
"student_id" INT UNIQUE,
"current_address_id" INT,
"permanent_address_id" INT,
"first_name" VARCHAR(80),
"middle_name" VARCHAR(40),
"last_name" VARCHAR(40),
"cell_mobile_number" VARCHAR(40),
"email_address" VARCHAR(40),
"ssn" VARCHAR(40),
"date_first_registered" DATETIME,
"date_left" DATETIME,
"other_student_details" VARCHAR(255),
PRIMARY KEY ("student_id"),
FOREIGN KEY ("permanent_address_id") REFERENCES "Addresses"("address_id"),
FOREIGN KEY ("current_address_id") REFERENCES "Addresses"("address_id")
);





INSERT INTO "Addresses" VALUES ("1");
INSERT INTO "Addresses" VALUES ("2");
INSERT INTO "Addresses" VALUES ("3");
INSERT INTO "Addresses" VALUES ("4");
INSERT INTO "Addresses" VALUES ("5");
INSERT INTO "Addresses" VALUES ("6");
INSERT INTO "Addresses" VALUES ("7");
INSERT INTO "Addresses" VALUES ("8");
INSERT INTO "Addresses" VALUES ("9");
INSERT INTO "Addresses" VALUES ("10");
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 "Semesters" VALUES ("1", "Fall 2023");
INSERT INTO "Semesters" VALUES ("2", "Spring 2024");
INSERT INTO "Semesters" VALUES ("3", "Summer 2024");
INSERT INTO "Student_Enrolment" VALUES ("1", "1");
INSERT INTO "Student_Enrolment" VALUES ("2", "2");
INSERT INTO "Student_Enrolment" VALUES ("3", "3");
INSERT INTO "Student_Enrolment" VALUES ("4", "4");
INSERT INTO "Student_Enrolment" VALUES ("5", "5");
INSERT INTO "Student_Enrolment" VALUES ("6", "1");
INSERT INTO "Student_Enrolment" VALUES ("7", "2");
INSERT INTO "Student_Enrolment" VALUES ("8", "3");
INSERT INTO "Student_Enrolment" VALUES ("9", "4");
INSERT INTO "Student_Enrolment" VALUES ("10", "5");
INSERT INTO "Students" VALUES ("101", "1", "2", "Zachary", "Lee", "Young", "555-0101", "zachary.young@example.com", "123-45-6789", "2021-08-15 09:00:00", "NULL", "Prefers evening classes");
INSERT INTO "Students" VALUES ("102", "3", "4", "Yvonne", "Marie", "Smith", "555-0102", "yvonne.smith@example.com", "987-65-4321", "2020-01-10 10:30:00", "2023-05-20 15:00:00", "Transferred to another university");
INSERT INTO "Students" VALUES ("103", "5", "6", "Xander", "J", "Brown", "555-0103", "xander.brown@example.com", "111-22-3333", "2019-09-01 08:45:00", "NULL", "On scholarship");
INSERT INTO "Students" VALUES ("104", "7", "8", "Wendy", "K", "Johnson", "555-0104", "wendy.johnson@example.com", "444-55-6666", "2022-02-20 11:15:00", "NULL", "Part-time student");
INSERT INTO "Students" VALUES ("105", "9", "10", "Victor", "L", "Davis", "555-0105", "victor.davis@example.com", "777-88-9999", "2023-01-05 14:00:00", "NULL", "International student");
INSERT INTO "Students" VALUES ("106", "1", "3", "Uma", "N", "Miller", "555-0106", "uma.miller@example.com", "222-33-4444", "2021-06-10 09:30:00", "2022-12-15 12:00:00", "Graduated");
INSERT INTO "Students" VALUES ("107", "4", "5", "Timothy", "O", "Wilson", "555-0107", "timothy.wilson@example.com", "555-66-7777", "2020-08-25 13:45:00", "NULL", "Needs special accommodation");
INSERT INTO "Students" VALUES ("108", "6", "7", "Samantha", "P", "Moore", "555-0108", "samantha.moore@example.com", "888-99-0000", "2018-11-11 10:00:00", "NULL", "Exchange student");
INSERT INTO "Students" VALUES ("109", "8", "9", "Robert", "Q", "Taylor", "555-0109", "robert.taylor@example.com", "333-44-5555", "2019-03-15 16:20:00", "NULL", "Athlete");
INSERT INTO "Students" VALUES ("110", "10", "1", "Quinn", "R", "Anderson", "555-0110", "quinn.anderson@example.com", "666-77-8888", "2022-09-01 08:00:00", "NULL", "Veteran student");