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 (
DoubleflagID integer not null auto_increment,
DoubleflagName varchar(100) not null,
primary key (DoubleflagID)
);

CREATE TABLE DISCIPLINES (
DisciplineID integer not null auto_increment,
DisciplineCode varchar(5) not null,
DisciplineName varchar(100) not null,
primary key (DisciplineID)
);


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),
primary key (MinorID)
);	

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

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), 
DivisionID integer REFERENCES DIVISONS,
DisciplineID integer REFERENCES DISCIPLINES, 
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 TRANSFERS (
TransferID integer not null auto_increment,
TransferName integer not null,
CoreRequired integer not null,
primary key (TransferID)
);

CREATE TABLE GENERALS (
GeneralID integer not null auto_increment,
GeneralName varchar(255) not null,
Description varchar(255) not null,
primary key (GeneralID)
);

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)
);

LOAD DATA INFILE '/home/zoro/Catalog_Data/SCHOOLS.txt' INTO TABLE Catalog_G.SCHOOLS; 
LOAD DATA INFILE '/home/zoro/Catalog_Data/DIVISIONS.txt' INTO TABLE Catalog_G.DIVISIONS; 
LOAD DATA INFILE '/home/zoro/Catalog_Data/DISCIPLINES.txt' INTO TABLE Catalog_G.DISCIPLINES; 
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 DEGREES (DegreeName, DegreeCode, TotalCredits, LiberalArts) VALUES ('Bachelor Business Administration', 'B.B.A.', '120', '60' );
INSERT INTO DOUBLEFLAGS (DoubleflagName) VALUES ('Single Major');
INSERT INTO DOUBLEFLAGS (DoubleflagName) VALUES ('Double Major');
LOAD DATA INFILE '/home/zoro/Catalog_Data/majors_11-12.txt' INTO TABLE Catalog_G.MAJORS;
INSERT INTO LEVELS (LevelName) VALUES ('Lower');
INSERT INTO LEVELS (LevelName) VALUES ('Upper');
LOAD DATA INFILE '/home/zoro/Catalog_Data/minors_11-17.txt' INTO TABLE Catalog_G.MINORS;
LOAD DATA INFILE '/home/zoro/Catalog_Data/COURSES_11-17.txt' INTO TABLE Catalog_G.COURSES;
LOAD DATA INFILE '/home/zoro/Catalog_Data/MAJOR_REQ_11-17.txt' INTO TABLE Catalog_G.MAJOR_REQ; 
LOAD DATA INFILE '/home/zoro/Catalog_Data/MINOR_REQ_11-17.txt' INTO TABLE Catalog_G.MINOR_REQ;
LOAD DATA INFILE '/home/zoro/Catalog_Data/CORE_REQ_11-17.txt' INTO TABLE Catalog_G.CORE_REQ;
INSERT INTO COLLEGEWIDE_REQ (CreditsRequired, CourseList) VALUES ('2', '121');
INSERT INTO COLLEGEWIDE_REQ (CreditsRequired, CourseList) VALUES ('3', '120');
INSERT INTO COLLEGEWIDE_REQ (CreditsRequired, CourseList) VALUES ('3', '^02');
INSERT INTO TRANSFERS(TransferName, CoreRequired) VALUES ('10', '33');
INSERT INTO TRANSFERS(TransferName, CoreRequired) VALUES ('20', '30');
INSERT INTO TRANSFERS(TransferName, CoreRequired) VALUES ('30', '27');
INSERT INTO TRANSFERS(TransferName, CoreRequired) VALUES ('40', '24');
INSERT INTO TRANSFERS(TransferName, CoreRequired) VALUES ('50', '21');
INSERT INTO TRANSFERS(TransferName, CoreRequired) VALUES ('60', '18');
INSERT INTO TRANSFERS(TransferName, CoreRequired) VALUES ('70', '15');
INSERT INTO TRANSFERS(TransferName, CoreRequired) VALUES ('80', '12');
INSERT INTO TRANSFERS(TransferName, CoreRequired) VALUES ('90', '9');
INSERT INTO TRANSFERS(TransferName, CoreRequired) VALUES ('100', '9');
INSERT INTO GENERALS (GeneralName, Description) VALUES ('GPA Requirement','In order to graduate a student needs a GPA of 2.0 or higher.');
INSERT INTO USERS (UserName,Password,Name) VALUES ('george','test','George');