--
-- GRAD_DEGREE DATABASE
--
CREATE DATABASE GRAD_DEGREE;
USE GRAD_DEGREE;

--
-- Students TABLE
--
CREATE TABLE Students (
   stud_id CHAR(4) UNIQUE NOT NULL,
   last_name VARCHAR(20) NOT NULL,
   first_name VARCHAR(20) NOT NULL,
   middle_name VARCHAR(20),
   email VARCHAR(40) NOT NULL,
   address VARCHAR(30) NOT NULL,
   city VARCHAR(20) NOT NULL,
   state CHAR(2) NOT NULL,
   zip CHAR(5) NOT NULL,
   phone CHAR(14) NOT NULL,
   password VARCHAR(4) NOT NULL,
   degree VARCHAR(4) NOT NULL); 

LOAD DATA LOCAL INFILE "Student.txt" INTO TABLE Students LINES TERMINATED BY '\r\n';

--
-- CoursesTaken TABLE
--
CREATE TABLE CoursesTaken (
   stud_id CHAR(4) NOT NULL,
   course_num VARCHAR(9),
   semester VARCHAR(6),
   year CHAR(4),
   grade CHAR(1));

LOAD DATA LOCAL INFILE "CoursesTaken.txt" INTO TABLE CoursesTaken LINES TERMINATED BY '\r\n';

--
-- Courses TABLE
--
CREATE TABLE Courses (
   course_num VARCHAR(9) NOT NULL,
   title VARCHAR(80) NOT NULL);

LOAD DATA LOCAL INFILE "Courses.txt" INTO TABLE Courses LINES TERMINATED BY '\r\n';

--
-- Programs TABLE
--
CREATE TABLE Programs (
   degree VARCHAR(4) NOT NULL,
   title VARCHAR(50) NOT NULL,
   num_opt CHAR(1) NOT NULL);

LOAD DATA LOCAL INFILE "Programs.txt" INTO TABLE Programs LINES TERMINATED BY '\r\n';

--
-- Requirements TABLE
--
CREATE TABLE Requirements (
   course_num VARCHAR(9) NOT NULL,
   degree VARCHAR(4) NOT NULL,
   type  CHAR(1) NOT NULL,
   alt_req VARCHAR(9));

LOAD DATA LOCAL INFILE "Requirements.txt" INTO TABLE Requirements LINES TERMINATED BY '\r\n';

--
-- Prerequisites TABLE
--
CREATE TABLE Prerequisites (
   course_num VARCHAR(9) NOT NULL,
   prereq VARCHAR(9) NOT NULL,
   alt_prereq  VARCHAR(9));

LOAD DATA LOCAL INFILE "Prerequisites.txt" INTO TABLE Prerequisites LINES TERMINATED BY '\r\n';

--
-- CoursesPlanned TABLE
--
CREATE TABLE CoursesPlanned (
   stud_id CHAR(4) NOT NULL,
   course_num VARCHAR(9),
   semester VARCHAR(6),
   year CHAR(4));