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),
"course_description" VARCHAR(255),
"other_details" 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 "Departments" (
"department_id" INT,
"department_name" VARCHAR(255),
PRIMARY KEY ("department_id")
);

CREATE TABLE "Sections" (
"section_id" INT,
"course_id" INT,
"section_name" VARCHAR(255),
"section_description" VARCHAR(255),
"other_details" VARCHAR(255),
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")
);





INSERT INTO "Courses" VALUES ("101", "Introduction to Biology", "Basic concepts of biology", "Lab required");
INSERT INTO "Courses" VALUES ("102", "Advanced Mathematics", "In-depth study of calculus and algebra", "Prerequisite: Calculus I");
INSERT INTO "Courses" VALUES ("103", "World History", "Overview of global historical events", "Includes field trips");
INSERT INTO "Courses" VALUES ("104", "Computer Science Fundamentals", "Introduction to programming and algorithms", "Project-based course");
INSERT INTO "Courses" VALUES ("105", "English Literature", "Study of classic and modern literature", "Essay assignments");
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 "Departments" VALUES ("10", "Biology");
INSERT INTO "Departments" VALUES ("20", "Mathematics");
INSERT INTO "Departments" VALUES ("30", "History");
INSERT INTO "Departments" VALUES ("40", "Computer Science");
INSERT INTO "Departments" VALUES ("50", "English");
INSERT INTO "Sections" VALUES ("201", "101", "Section A", "Morning class with lab sessions", "Room 101");
INSERT INTO "Sections" VALUES ("202", "101", "Section B", "Evening class with lab sessions", "Room 102");
INSERT INTO "Sections" VALUES ("203", "102", "Section h", "Advanced calculus topics, focus on integration techniques", "Room 201");
INSERT INTO "Sections" VALUES ("204", "103", "Section C", "History of the 20th century", "Room 301");
INSERT INTO "Sections" VALUES ("205", "104", "Section D", "Programming fundamentals with Python", "Computer Lab 1");
INSERT INTO "Sections" VALUES ("206", "105", "Section E", "Modern poetry analysis", "Room 401");
INSERT INTO "Semesters" VALUES ("301", "Fall 2023");
INSERT INTO "Semesters" VALUES ("302", "Spring 2024");
INSERT INTO "Semesters" VALUES ("303", "Summer 2024");