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),
"degree_summary_description" 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")
);





INSERT INTO "Courses" VALUES ("101", "Introduction to Programming", "Fundamentals of programming using Python.", "Prerequisite: None");
INSERT INTO "Courses" VALUES ("102", "Data Structures", "Advanced data structures and algorithms.", "Prerequisite: CSC101");
INSERT INTO "Courses" VALUES ("103", "Database Systems", "Relational database design and SQL.", "Prerequisite: CSC101");
INSERT INTO "Courses" VALUES ("104", "Web Development", "Building web applications with HTML, CSS, JS.", "Prerequisite: CSC101");
INSERT INTO "Courses" VALUES ("105", "Operating Systems", "Concepts of operating systems.", "Prerequisite: CSC102");
INSERT INTO "Courses" VALUES ("106", "Calculus I", "Introduction to differential and integral calculus.", "Prerequisite: Pre-Calculus");
INSERT INTO "Courses" VALUES ("107", "Linear Algebra", "Vector spaces, linear transformations, eigenvalues.", "Prerequisite: MTH106");
INSERT INTO "Courses" VALUES ("108", "Discrete Mathematics", "Logic, sets, functions, graph theory.", "Prerequisite: None");
INSERT INTO "Degree_Programs" VALUES ("1", "Computer Science BS", "Bachelor of Science in Computer Science, focusing on software development and theory.");
INSERT INTO "Degree_Programs" VALUES ("2", "Data Science MS", "Master of Science in Data Science, focusing on data analysis and machine learning.");
INSERT INTO "Degree_Programs" VALUES ("3", "Mathematics BA", "Bachelor of Arts in Mathematics, focusing on theoretical and applied mathematics.");
INSERT INTO "Departments" VALUES ("1", "Computer Science");
INSERT INTO "Departments" VALUES ("2", "Mathematics");
INSERT INTO "Departments" VALUES ("3", "Electrical Engineering");
INSERT INTO "Sections" VALUES ("1", "101", "h", "This section covers basic programming concepts and syntax in Python.", "Instructor: Dr. Smith, Room: CS101");
INSERT INTO "Sections" VALUES ("2", "101", "Section B", "This section focuses on object-oriented programming in Python.", "Instructor: Prof. Johnson, Room: CS102");
INSERT INTO "Sections" VALUES ("3", "102", "Section A", "This section covers advanced data structures like trees and graphs.", "Instructor: Dr. Lee, Room: CS201");
INSERT INTO "Sections" VALUES ("4", "103", "Section X", "This section focuses on SQL queries and database normalization.", "Instructor: Prof. Davis, Room: CS202");
INSERT INTO "Sections" VALUES ("5", "104", "Section Y", "This section covers front-end web development.", "Instructor: Dr. Brown, Room: CS103");
INSERT INTO "Sections" VALUES ("6", "101", "Section C", "An alternative section for Introduction to Programming.", "Instructor: Dr. Green, Room: CS104");
INSERT INTO "Sections" VALUES ("7", "105", "Section Z", "This section covers process management and memory management.", "Instructor: Prof. White, Room: CS301");
INSERT INTO "Sections" VALUES ("8", "106", "Section M", "This section covers limits and derivatives.", "Instructor: Dr. Adams, Room: MTH101");
INSERT INTO "Sections" VALUES ("9", "108", "Section N", "This section covers propositional logic and proof techniques.", "Instructor: Prof. King, Room: MTH102");