w54fDUU1

1DROP TABLE IF EXISTS Student;
2CREATE TABLE Student (
3PRIMARY KEY (StudentID),
4StudentID INT  AUTO_INCREMENT NOT NULL,
5StudentFirstname char(40) NOT NULL,
6StudentLastname char(40) NOT NULL,
7StudentBirthdate DATE NOT NULL,
8StudentPhone char(15) NOT NULL
9);
10
11DROP TABLE IF EXISTS Instructor;
12CREATE TABLE Instructor (
13PRIMARY KEY (InstructorID),
14InstructorID INT AUTO_INCREMENT NOT NULL,
15InstructorFirstname char(25) NOT NULL,
16InstructorLastname char(25) NOT NULL,
17InstructorHomePhone char(15) NOT NULL,
18InstructorOfficePhone char(15) NOT NULL,
19Office INT(3) NOT NULL,
20Building INT(3) NOT NULL
21)AUTO_INCREMENT = 1;
22
23DROP TABLE IF EXISTS Course;
24CREATE TABLE Course (
25PRIMARY KEY (CourseID),
26CourseID INT AUTO_INCREMENT UNIQUE NOT NULL,
27CourseName char(50) NOT NULL,
28InstructorID INT NOT NULL,
29Section char(10) NOT NULL,
30CourseData char(10) NOT NULL,
31Classroom INT(3) NOT NULL,
32Building INT (3) NOT NULL,
33CourseCredits FLOAT(3) NOT NULL,
34
35FOREIGN KEY (InstructorID) REFERENCES Instructor (InstructorID)
36);
37
38DROP TABLE IF EXISTS Grade;
39CREATE TABLE Grade ( 
40PRIMARY KEY (FinalGrade, LetterGrade),
41FinalGrade INT (3) UNIQUE NOT NULL,
42LetterGrade char(3)
43);
44
45DROP TABLE IF EXISTS Enrolment;
46CREATE TABLE Enrolment (
47CourseID INT NOT NULL,
48StudentID INT NOT NULL,
49FinalGrade INT NOT NULL,
50PRIMARY KEY (CourseID, StudentID, FinalGrade),
51FOREIGN KEY (CourseID) REFERENCES Course(CourseID),
52FOREIGN KEY (StudentID) REFERENCES Student (StudentID),
53FOREIGN KEY (FinalGrade) REFERENCES Grade (FinalGrade)
54);
55DROP TABLE IF EXISTS srsdata;
56CREATE TABLE IF NOT EXISTS srsdata (
57StudentFirstname char(50),
58StudentLastname char(50),
59StudentBirthDate char(50),
60StudentPhone char(50),
61CourseData char(50),
62CourseName char(50),
63Classroom char(50),
64CourseCredits char(50),
65FinalGrade char(50),
66LetterGrade char(3),
67Instructor char(50),
68Office char(50),
69InstructorOfficePhone char(25),
70InstructorHomePhone char(25)
71);
72
73LOAD DATA INFILE 'SRSData2018.csv' INTO TABLE .srsdata FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r' IGNORE 1 LINES;
74
75INSERT INTO student (StudentFirstname, StudentLastname, StudentBirthdate, StudentPhone)
76SELECT DISTINCT StudentFirstname,StudentLastname, STR_TO_DATE(StudentBirthdate, '%m/%d/%y'), StudentPhone FROM srsdata;
77SELECT * FROM student;
78INSERT INTO Instructor (InstructorFirstName, InstructorLastName, Building, Office, InstructorOfficePhone, InstructorHomePhone) 
79SELECT DISTINCT SUBSTRING_INDEX(Instructor, ', ', -1), SUBSTRING_INDEX(Instructor, ', ', 1), SUBSTR(Office, 1,3), SUBSTR(Office, 5,3), InstructorOfficePhone, InstructorHomePhone
80FROM srsdata;
81SELECT * FROM Instructor;
82
83INSERT INTO Course (InstructorID, CourseData, Section, CourseName, Building, Classroom, CourseCredits)
84SELECT DISTINCT (SELECT DISTINCT InstructorID FROM Instructor where instructor.InstructorLastname = SUBSTRING_INDEX(srsdata.Instructor, ', ', 1)), SUBSTR(CourseData, 1,7), SUBSTR(CourseData, 9,14), CourseName, SUBSTR(REPLACE(Classroom,'na','N/A') , 1,3), SUBSTR(REPLACE(Classroom,'na','N/A') , 5,3), CourseCredits
85FROM srsdata;
86
87INSERT INTO Grade(FinalGrade, LetterGrade) SELECT DISTINCT FinalGrade, LetterGrade FROM srsdata ON DUPLICATE KEY UPDATE grade.FinalGrade=grade.FinalGrade;
88
89
90INSERT INTO Enrolment (StudentID, CourseID, FinalGrade)
91SELECT DISTINCT (SELECT StudentID FROM student WHERE StudentFirstName = srsdata.StudentFirstName AND StudentLastName = srsdata.StudentLastname), (SELECT CourseID FROM course WHERE course.CourseName = srsdata.CourseName), FinalGrade FROM srsdata;