DROP DATABASE Catalog_G;
CREATE DATABASE Catalog_G;
USE Catalog_G;
CREATE TABLE SCHOOLS (
SchoolID integer not null auto_increment,
SchoolName varchar(100) not null,
primary key (SchoolID)
);

CREATE TABLE DIVISIONS (
DivisionID integer not null auto_increment,
DivisionName varchar(100) not null,
SchoolID integer not null REFERENCES SCHOOLS,
primary key (DivisionID)
);

CREATE TABLE DEPARTMENTS (
DepartmentID integer not null auto_increment,
DepartmentName varchar(100) not null,
DivisionID integer not null REFERENCES DIVISIONS,
primary key (DepartmentID)
);

CREATE TABLE DEGREES (
DegreeID integer not null auto_increment,
DegreeName varchar(100) not null,
DegreeCode varchar(5) not null,
TotalCredits integer not null,
LiberalArts integer not null,
primary key (DegreeID)
);


CREATE TABLE DOUBLEFLAGS (
ID integer not null auto_increment,
DoubleFlagName varchar(100) not null,
primary key (ID)
);

CREATE TABLE MAJORS (
MajorID integer not null auto_increment,
MajorName varchar(100) not null,
MajorCode varchar(3) not null,
DoubleFlagID integer not null REFERENCES DOUBLEFLAGS,
DegreeID integer not null REFERENCES DEGREES,
primary key (MajorID)
);

CREATE TABLE MINORS (
MinorID integer not null auto_increment,
MinorName varchar(100) not null,
MinorCode varchar(3) not null,
NotAvailableList varchar(100) not null,
primary key (MinorID)
);	

CREATE TABLE LEVELS (
ID integer not null auto_increment,
LevelName varchar(100) not null,
primary key (ID)
);

CREATE TABLE COURSES (
CourseID integer not null auto_increment,
CourseNumber  varchar(5) not null,
CourseNumberNew  varchar(5) not null,
CourseLetter varchar(5),
CourseCode  varchar(12) not null,
CourseName varchar(100) not null,
Credits integer not null,
LevelID integer not null REFERENCES LEVELS,
CrossList varchar(255), 
PreReqList varchar(255), 
CoReqList varchar(255), 
PostReqList varchar(255), 
DepartmentID integer REFERENCES DEPARTMENTS,
MajorID integer REFERENCES MAJORS, 
primary key (CourseID)
);

CREATE TABLE MAJOR_REQ (
ID integer not null auto_increment,
Credits integer not null,
CourseList varchar(255) not null,
MajorID integer REFERENCES MAJORS,
primary key (ID)
);	

CREATE TABLE MINOR_REQ (
ID integer not null auto_increment,
Credits integer not null,
CourseList varchar(255) not null,
MinorID integer REFERENCES MINORS,
primary key (ID)
);

CREATE TABLE CORE_REQ (
ID integer not null auto_increment,
CreditsRequired integer not null,
CourseList varchar(255) not null,
primary key (ID)
);

CREATE TABLE COLLEGEWIDE_REQ (
ID integer not null auto_increment,
CreditsRequired integer not null,
CourseList varchar(255) not null,
primary key (ID)
);

CREATE TABLE TRANSFER_REQ (
ID integer not null auto_increment,
TransferCredits integer not null,
CoreRequired integer not null,
primary key (ID)
);

CREATE TABLE GENERAL_REQ (
ID integer not null auto_increment,
Description varchar(255) not null,
primary key (ID)
);

CREATE TABLE USERS (
UserID integer not null auto_increment,
UserName varchar(50) not null,
Password varchar(50) not null,
Name varchar(50) not null,
primary key (UserID)
);
INSERT INTO SCHOOLS (SchoolName) VALUES ('Arts and Sciences');
INSERT INTO DIVISIONS (DivisionName, SchoolID) VALUES ('Natural Science and Mathematics', '1');
INSERT INTO DEPARTMENTS (DepartmentName, DivisionID) VALUES ('Computer Science', '1');
INSERT INTO DEGREES (DegreeName, DegreeCode, TotalCredits, LiberalArts) VALUES ('Bachelor of Science', 'B.S.', '120', '60' );
INSERT INTO DEGREES (DegreeName, DegreeCode, TotalCredits, LiberalArts) VALUES ('Bachelor of Arts', 'B.A.', '120', '90' );
INSERT INTO DOUBLEFLAGS (DoubleFlagName) VALUES ('Single Major');
INSERT INTO MAJORS (MajorName, MajorCode, DoubleFlagID, DegreeID) VALUES ('Computer Science', 'CSC', '1', '1' );
INSERT INTO MAJORS (MajorName, MajorCode, DoubleFlagID, DegreeID) VALUES ('Mathematics', 'MTH', '1', '2' );
INSERT INTO LEVELS (LevelName) VALUES ('Lower');
INSERT INTO MINORS (MinorName, MinorCode, NotAvailableList) VALUES ('Computer Science', 'CSC', '1,2');
INSERT INTO MINOR_REQ (Credits, CourseList, MinorID) VALUES ('4', '1', '1');
INSERT INTO COLLEGEWIDE_REQ (CreditsRequired, CourseList) VALUES ('3', '55');
INSERT INTO TRANSFER_REQ (TransferCredits, CoreRequired) VALUES ('10', '33');
INSERT INTO CORE_REQ (CreditsRequired, CourseList) VALUES ('3', '33');
INSERT INTO GENERAL_REQ (Description) VALUES ('In order to graduate a student needs a GPA of 2.0 or higher.');
INSERT INTO USERS (UserName,Password,Name) VALUES ('george','test','George');
LOAD DATA INFILE '/home/students/KichukoG/Catalog_Data/courses_CSC.txt' INTO TABLE Catalog_G.COURSES; 
LOAD DATA INFILE '/home/students/KichukoG/Catalog_Data/major_req_CSC.txt' INTO TABLE Catalog_G.MAJOR_REQ;