Setup

BEGIN
FOR c IN (SELECT TABLE_NAME FROM user_tables) loop
EXECUTE immediate ('drop table '||c.TABLE_NAME||' cascade constraints');
END loop;
END;
/
BEGIN
FOR c IN (SELECT * FROM user_objects) loop
EXECUTE immediate ('drop '||c.object_type||' '||c.object_name);
END loop;
END;
/

-- Tables

CREATE TABLE StudyProgramme (
programmeName VARCHAR(30),
PRIMARY KEY (programmeName)
);

CREATE TABLE Branches (
branchName VARCHAR(30),
programmeName VARCHAR(30),
FOREIGN KEY (programmeName) REFERENCES StudyProgramme(programmeName),
PRIMARY KEY (branchName, programmeName)
);

CREATE TABLE Students (
idd INT,
studentName VARCHAR(30),
branchName VARCHAR(30),
programmeName VARCHAR (30),
FOREIGN KEY (branchName, programmeName) REFERENCES Branches(branchName, programmeName),
PRIMARY KEY (idd)
);

CREATE TABLE Courses (
code INT,
credits INT,
courseName VARCHAR(30),
CONSTRAINT NonNegativeCredits CHECK (credits > 0 ),
PRIMARY KEY (code)
);

CREATE TABLE Studying (
idd INT REFERENCES Students(idd),
code INT REFERENCES Courses(code),
PRIMARY KEY (idd, code)
);

CREATE TABLE Finished (
grade VARCHAR(1),
idd INT REFERENCES Students(idd),
code INT REFERENCES Courses(code),
CONSTRAINT GradeSpan CHECK (grade IN ('U',3,4,5)),
PRIMARY KEY (idd, code)
);

CREATE TABLE RestrictedCourse (
spots INT,
code INT REFERENCES Courses(code),
CONSTRAINT NonNegativeSpots CHECK (spots > 0),
PRIMARY KEY (code)
);

CREATE TABLE Departments (
departmentName VARCHAR(20),
PRIMARY KEY (departmentName)
);

CREATE TABLE Categorized (
typ VARCHAR(15),
PRIMARY KEY (typ)
);

CREATE TABLE IsCategorized (
code INT REFERENCES Courses(code),
typ VARCHAR(15) REFERENCES Categorized(typ),
PRIMARY KEY (code, typ)
);

CREATE TABLE HostedBy (
programmeName VARCHAR(30) REFERENCES StudyProgramme(programmeName),
departmentName VARCHAR(20) REFERENCES Departments(departmentName),
PRIMARY KEY (programmeName, departmentName)
);

CREATE TABLE isMandatorySP (
programmeName VARCHAR(30) REFERENCES StudyProgramme(programmeName),
code INT REFERENCES Courses(code),
PRIMARY KEY (code, programmeName)
);

CREATE TABLE isRecommended (
code INT REFERENCES Courses(code),
branchName VARCHAR(30),
programmeName VARCHAR (30),
FOREIGN KEY (branchName, programmeName) REFERENCES Branches(branchName, programmeName),
PRIMARY KEY (code, branchName, programmeName)
);

CREATE TABLE isMandatoryB (
code INT REFERENCES Courses(code),
branchName VARCHAR(30),
programmeName VARCHAR (30),
FOREIGN KEY (branchName, programmeName) REFERENCES Branches(branchName, programmeName),
PRIMARY KEY (code, branchName, programmeName)
);

CREATE TABLE Waiting (
queueNumber INT,
idd INT REFERENCES Students(idd),
code INT REFERENCES Courses(code),
CONSTRAINT NonNegativeQueue CHECK (queuenumber > 0),
PRIMARY KEY (idd, code)
);

CREATE TABLE PrerequisiteCourse (
course_code INT REFERENCES Courses(code),
required_course INT REFERENCES Courses(code),
PRIMARY KEY (course_code, required_course)
);

-- Insert

/* StudyProgramme */

INSERT INTO StudyProgramme
  VALUES ('I');

INSERT INTO StudyProgramme
  VALUES ('V');

INSERT INTO StudyProgramme
  VALUES ('E');

INSERT INTO StudyProgramme
  VALUES ('M');

/* Branches */

INSERT INTO Branches
  VALUES ('Management','I');

INSERT INTO Branches
  VALUES ('Mathematics','I');

INSERT INTO Branches
  VALUES ('Management','V');

INSERT INTO Branches
  VALUES ('Mathematics', 'E');

INSERT INTO Branches
  VALUES ('Data', 'E');

INSERT INTO Branches
  VALUES ('Mathematics', 'M');

INSERT INTO Branches
  VALUES ('Construction', 'M');

/* Students */

INSERT INTO Students
  VALUES ('1', 'Erik', 'Management', 'I');

INSERT INTO Students
  VALUES ('2', 'Johan', 'Mathematics', 'I');

INSERT INTO Students
  VALUES ('3', 'Frida', 'Construction', 'M');

INSERT INTO Students
  VALUES ('4', 'Stina', 'Management', 'V');

INSERT INTO Students
  VALUES ('5', 'Emma', 'Data', 'E');


/* Courses */

INSERT INTO Courses
  VALUES ('146', '7', 'Avslutande matte');

INSERT INTO Courses
  VALUES ('100', '7', 'Olinjär algebra');

INSERT INTO Courses
  VALUES ('200', '15', 'Ek 4an');

INSERT INTO Courses
  VALUES ('243', '30', 'Programmera mera');

INSERT INTO Courses
  VALUES ('23', '15', 'Matematikens underbara värld');

INSERT INTO Courses
  VALUES ('50', '7', 'Alkemi');

INSERT INTO Courses
  VALUES ('10', '7', 'Palmestri');

/* Categorized */

INSERT INTO Categorized
  VALUES ('Mathematical');

INSERT INTO Categorized
  VALUES ('Seminar');

INSERT INTO Categorized
  VALUES ('Research');

/* Departments */

INSERT INTO Departments
  VALUES ('Dep för CS');

INSERT INTO Departments
  VALUES ('Dep för Logistik');

INSERT INTO Departments
  VALUES ('Dep för Management');

INSERT INTO Departments
  VALUES ('Dep för Matematik');

INSERT INTO Departments
  VALUES ('Dep för Kvantfysik');

/* HostedBy */

INSERT INTO HostedBy
  VALUES ('I', 'Dep för Management');

INSERT INTO HostedBy
  VALUES ('I', 'Dep för Matematik');

INSERT INTO HostedBy
  VALUES ('V', 'Dep för Logistik');

INSERT INTO HostedBy
  VALUES ('E', 'Dep för Kvantfysik');

INSERT INTO HostedBy
  VALUES ('M', 'Dep för CS');

/* IsCategorized */

INSERT INTO IsCategorized
  VALUES ('200', 'Seminar');

INSERT INTO IsCategorized
  VALUES ('100', 'Mathematical');

INSERT INTO IsCategorized
  VALUES ('23', 'Research');

INSERT INTO IsCategorized
  VALUES ('146', 'Mathematical');

INSERT INTO IsCategorized
  VALUES ('243', 'Mathematical');

/* IsMandatoryB */

INSERT INTO IsMandatoryB
  VALUES ('100', 'Management', 'V');

INSERT INTO IsMandatoryB
  VALUES ('200', 'Construction', 'M');

/* IsMandatorySP */

INSERT INTO IsMandatorySP
  VALUES ('M', '243');

/* IsRecommended */

INSERT INTO IsRecommended
  VALUES ('100', 'Management', 'I');

INSERT INTO IsRecommended
  VALUES ('23', 'Data', 'E');

/* Finished */

INSERT INTO Finished
  VALUES ('U', '4', '100');

INSERT INTO Finished
  VALUES ('5', '5', '200');

INSERT INTO Finished
  VALUES ('5', '3', '100');

INSERT INTO Finished
  VALUES ('3', '5', '243');

INSERT INTO Finished
  VALUES ('5', '2', '200');

INSERT INTO Finished
  VALUES ('4', '2', '100');

INSERT INTO Finished
  VALUES ('3', '1', '23');

INSERT INTO Finished
  VALUES ('3', '5', '23');

/* RestrictedCourse */

INSERT INTO RestrictedCourse
  VALUES ('100', '100');

INSERT INTO RestrictedCourse
  VALUES ('50', '23');

INSERT INTO RestrictedCourse
  VALUES ('1', '50');

INSERT INTO RestrictedCourse
  VALUES ('1', '10');

/* Studying */

INSERT INTO Studying
  VALUES ('1', '100');

INSERT INTO Studying
  VALUES ('1', '200');

INSERT INTO Studying
  VALUES ('2', '100');

INSERT INTO Studying
  VALUES ('1', '50');

INSERT INTO Studying
  VALUES ('1', '10');

INSERT INTO Studying
  VALUES ('2', '10');

INSERT INTO Studying
  VALUES ('3', '10');

/* Waiting */

INSERT INTO Waiting
  VALUES ('1', '2', '146');

INSERT INTO Waiting
  VALUES ('1', '3', '243');

INSERT INTO Waiting
  VALUES ('1', '3', '50');

INSERT INTO Waiting
  VALUES ('2', '4', '50');

/* Prerequisite */

INSERT INTO PrerequisiteCourse
  VALUES ('146', '100');

-- Views

/* For all students, their names, and the programme and branch they are following.*/

CREATE OR REPLACE VIEW StudentsFollowing AS
SELECT studentname, idd, branchname, programmename
FROM Students;

CREATE OR REPLACE VIEW AllCourses AS
SELECT coursename, code, credits
FROM Courses;

/* For all students, all finished courses, along with their grades (grade 'U', '3', '4' or '5').*/

CREATE OR REPLACE VIEW FinishedCourses AS
SELECT DISTINCT Students.STUDENTNAME, coursename, grade
FROM Courses, Students, Finished
WHERE Courses.CODE = Finished.CODE AND Finished.idd = students.idd;

/* All registered and waiting students for all courses, along with their waiting status ('registered' or 'waiting').*/

CREATE OR REPLACE VIEW Registrations AS
(SELECT studentname, students.idd, coursename, Courses.code, 'waiting' AS STATUS
FROM Students, Waiting, Courses
WHERE Students.idd = waiting.idd AND waiting.code = courses.code)
UNION
(SELECT studentname, students.idd, coursename, Courses.code, 'registered' AS STATUS
FROM Students, Studying, Courses
WHERE Students.idd = studying.idd AND studying.code = courses.code);

/* For all students, all passed courses, i.e. courses finished with a grade other than 'U'.
This view is intended as a helper view towards the PathToGraduation view, and will not be directly used by your application.*/

CREATE OR REPLACE VIEW PassedCourses AS
  SELECT studentname, Students.idd, coursename, finished.code, grade, credits
  FROM Courses, Finished, Students
  WHERE Courses.code = finished.code AND Finished.idd = Students.idd
  AND grade != 'U';

/* For all students, the mandatory courses (branch and programme) they have not yet passed.*/

CREATE OR REPLACE VIEW UnreadMandatory AS
  (SELECT studentName, idd, code
  FROM Students NATURAL JOIN isMandatorySP)
  UNION
  (SELECT studentNAme, idd, code
  FROM Students NATURAL JOIN isMandatoryB)
  MINUS
  (SELECT studentName, idd, code
  FROM Students NATURAL JOIN PassedCourses);

/* PathToGraduation
For all students, their path to graduation, i.e. a view with columns for:
the number of credits they have passed.
the number of branch-specific mandatory and recommended credits they have passed.
the number of mandatory courses they have yet to pass (branch or programme).
the number of credits they have passed in courses that are classified as math courses.
the number of credits they have passed in courses that are classified as research courses.
the number of seminar courses they have passed.
whether or not they qualify for graduation.*/


/* the number of credits they have passed.*/

CREATE OR REPLACE VIEW PTGpassedCredits AS
SELECT DISTINCT students.studentname,students.idd, SUM(DISTINCT credits) AS passedCredits
FROM Students, Finished, Courses
WHERE Students.idd = Finished.IDD AND Finished.CODE = Courses.CODE AND grade != 'U'
GROUP BY students.studentname, students.idd;


/* the number of branch-specific mandatory and recommended credits they have passed.*/

CREATE OR REPLACE VIEW PTGMandatoryBSCourses AS
SELECT studentname, Students.idd, SUM(DISTINCT credits) AS mandatoryBScoursesCredits
FROM Students, Finished, Courses, isMandatoryB, isRecommended
WHERE Students.idd = Finished.idd AND Finished.code = Courses.code AND grade != 'U'
AND (Courses.code = isMandatoryB.CODE OR Courses.code = isRecommended.code)
GROUP BY studentname, Students.idd;



/* the number of mandatory courses they have yet to pass (branch or programme).*/

CREATE OR REPLACE VIEW PTGCoursesYetToPass AS
SELECT studentname, idd, code, COUNT(DISTINCT code) AS coursesYetToPass
FROM UnreadMandatory
GROUP BY studentname, idd, code
MINUS
SELECT studentname, idd, code, COUNT(DISTINCT code) AS coursesYetToPass
FROM PassedCourses
GROUP BY studentname, idd, code;


/*the number of credits they have passed in courses that are classified as math courses.*/


CREATE OR REPLACE VIEW PTGMathCoursesPassed AS
  SELECT studentname, idd, SUM(DISTINCT credits) AS passedMathCredits
  FROM PassedCourses NATURAL JOIN IsCategorized
  WHERE typ = 'Mathematical'
  GROUP BY studentname, idd;


  /*the number of credits they have passed in courses that are classified as research courses.*/

CREATE OR REPLACE VIEW PTGResearchCoursesPassed AS
  SELECT studentname, idd, SUM(DISTINCT credits) AS passedResearchCredits
  FROM PassedCourses NATURAL JOIN IsCategorized
  WHERE typ = 'Research'
  GROUP BY studentname, idd;


/*the number of seminar courses they have passed.*/

CREATE OR REPLACE VIEW PTGSeminarCoursesPassed AS
  SELECT studentname, idd, COUNT(DISTINCT code) AS passedSeminarCourses
  FROM PassedCourses NATURAL JOIN IsCategorized
  WHERE typ = 'Seminar'
  GROUP BY studentname, idd;


  /*whether or not they qualify for graduation.*/

CREATE OR REPLACE VIEW PTGGraduationYes AS
  SELECT studentname, idd, 'Yes' AS QualifiesForGraduation
  FROM PassedCourses
  NATURAL JOIN PTGMandatoryBSCourses
  NATURAL JOIN PTGMathCoursesPassed
  NATURAL JOIN PTGSeminarCoursesPassed
  NATURAL JOIN PTGResearchCoursesPassed
  WHERE MandatoryBScoursesCredits >= 10 AND passedMathCredits >= 20 AND passedSeminarCourses > 0 AND passedResearchCredits >= 10
  AND studentname NOT IN (SELECT Studentname FROM PTGCoursesYetToPass)
  GROUP BY studentname, idd, 'Yes';

  CREATE OR REPLACE VIEW PTGGraduationNo AS
  SELECT students.Studentname, students.idd, 'No' AS QualifiesForGraduation
  FROM Students
  WHERE students.Studentname NOT IN (SELECT Studentname FROM PTGGraduationYes);

CREATE OR REPLACE VIEW PTGGRaduationTotal AS
  (SELECT PTGGRaduationNo.Studentname, PTGGRaduationNo.idd, 'No' AS QualifiesForGraduation
  FROM PTGGraduationNo)
  UNION
  (SELECT PTGGRaduationYes.Studentname, PTGGRaduationYes.idd, 'Yes' AS QualifiesForGraduation
  FROM PTGGraduationYes);

  /* PathToGraduation
For all students, their path to graduation, i.e. a view with columns for:
the number of credits they have passed.
the number of branch-specific mandatory and recommended credits they have passed.
the number of mandatory courses they have yet to pass (branch or programme).
the number of credits they have passed in courses that are classified as math courses.
the number of credits they have passed in courses that are classified as research courses.
the number of seminar courses they have passed.
whether or not they qualify for graduation.*/

CREATE OR REPLACE VIEW PathToGraduation AS
  SELECT DISTINCT PTGGraduationtotal.idd, PTGGraduationtotal.studentname, PTGpassedcredits.passedcredits,
  PTGMandatoryBSCourses.mandatorybscoursescredits, PTGCoursesYetToPass.CoursesYetToPass, PTGMathCoursesPassed.passedmathcredits,
  PTGResearchCoursesPassed.passedresearchcredits, PTGSeminarCoursesPassed.passedseminarcourses, qualifiesforgraduation

  FROM PTGGraduationTotal
  FULL OUTER JOIN PTGPassedCredits ON PTGGraduationTotal.idd = PTGpassedcredits.idd
  FULL OUTER JOIN PTGMandatoryBSCourses ON PTGGraduationTotal.idd = PTGMandatoryBSCourses.idd
  FULL OUTER JOIN PTGCoursesYetToPass ON PTGGraduationTotal.idd = PTGCoursesYetToPass.idd
  FULL OUTER JOIN PTGMathCoursesPassed ON PTGGraduationTotal.idd = PTGMathCoursesPassed.idd
  FULL OUTER JOIN PTGResearchCoursesPassed ON PTGGraduationTotal.idd = PTGResearchCoursesPassed.idd
  FULL OUTER JOIN PTGSeminarCoursesPassed ON PTGGraduationTotal.idd = PTGSeminarCoursesPassed.idd

  ORDER BY PTGGraduationTotal.IDD;