/* Student table */
CREATE TABLE IF NOT EXISTS student (
  -- Fields:
  name VARCHAR(255) NOT NULL,
  student_number INTEGER NOT NULL,
  class INTEGER NOT NULL,
  major VARCHAR(255) NOT NULL,

  -- Constraints: 
  PRIMARY KEY (student_number)
);

/* Course table */
CREATE TABLE IF NOT EXISTS course (
  -- Fields:
  course_name VARCHAR(255) NOT NULL,
  course_number VARCHAR(191) NOT NULL,
  credit_hours INTEGER NOT NULL,
  department VARCHAR(255) NOT NULL,

  -- Constraints:
  PRIMARY KEY (course_number)
);

/* Section table */
CREATE TABLE IF NOT EXISTS section (
  -- Fields:
  section_identifier INTEGER NOT NULL,
  course_number VARCHAR(191) NOT NULL,
  semester VARCHAR(255) NOT NULL,
  year INTEGER NOT NULL,
  instructor VARCHAR(255) NOT NULL,

  -- Constraints:
  PRIMARY KEY (section_identifier),
  FOREIGN KEY (course_number) REFERENCES course(course_number)
);

/* Grade report table */
CREATE TABLE IF NOT EXISTS grade_report (
  -- Fields:
  student_number INTEGER NOT NULL,
  section_identifier INTEGER NOT NULL,
  grade VARCHAR(255) NOT NULL,

  -- Constraints:
  FOREIGN KEY (student_number) REFERENCES student(student_number),
  FOREIGN KEY (section_identifier) REFERENCES section(section_identifier)
);

/* Prerequisite table */
CREATE TABLE IF NOT EXISTS prerequisite (
  -- Fields:
  course_number VARCHAR(191) NOT NULL,
  prerequisite_number VARCHAR(191) NOT NULL,

  -- Constraints:
  FOREIGN KEY (course_number) REFERENCES course(course_number),
  FOREIGN KEY (prerequisite_number) REFERENCES course(course_number)
);
