-- Comp2400 Assignment 3

-- Name: Joseph Curtis
-- ID: 3952239

-- Roles

-- Role for students
create role u3952239_Student;

-- Role for tutors
create role u3952239_Tutor;

-- Role for Course Managers
create role u3952239_CourseManagers;


-- Views

-- Accessable by Public
-- Shows information about the tutorials and their times

create view Tutorial as 
select TutId, TutorName, Room, Day, Time
from B_Tutorial;



-- Accessable by Student, Tutor, Manager
-- Displays information about assessment items

create view AssessmentItem as
select AId, ADate, TotalMark, FinalPercent
from B_AssessmentItem;



-- Accessable by Student, Tutor, Manager
-- Displays summary data for all assignments

create view AssignmentSummary as
select AId, max(AMark) as Maximum, min(AMark) as Minimum,
round(avg(AMark),2) as Average, count(StudentId) as Submissions
from B_Mark
where lower(substr(aid,1,1)) = 'a'
group by aid;



-- Accessable by Student
-- Displays information about the student who is accessing the view

create view Student_Student as
select StudentId, Surname, FirstName, Username, TutId, Withdrawn
from B_A3Student
where StudentId = substr(user,8);



-- Accessable by Student
-- Displays information about the assginment marks of the student accessing
-- the view

create view Student_Mark as
select StudentId, AId, AMark, DateSubmitted
from B_Mark
where AId != 'exam'
and StudentId = substr(user,8,14);



-- Accessable by Tutor
-- Displays information about the students in the tutorial group of the
-- tutor accessing the view

create view Tutor_Student as
select StudentId, Surname, FirstName, Username, S.TutId, Withdrawn
from B_A3Student S, B_Tutorial T
where S.TutId = T.TutId
and upper(TutorName) = upper(substr(user, 6));



-- Accessable by Tutor
-- Displays information about the assignment marks for the students in the
-- tutorial group of the tutor accessing the view.

create view Tutor_Mark as
select M.StudentId, AId, AMark, DateSubmitted
from B_Mark M, B_A3Student S, B_Tutorial T
where S.TutId = T.TutId
and M.StudentId = S.StudentId
and upper(TutorName) = upper(substr(user, 6));



-- Accessable by Tutor
-- Displays a summary of the marks for the assignments of any given
-- tutorial group

create view MarkSummary as
select S.TutId, M.AId, max(AMark) as Maximum, min(AMark) as Miniumum,
round(avg(AMark),2) as Average, count(M.StudentId) as Subsissions
from B_Mark M, B_A3Student S
where M.StudentId = S.StudentId
and lower(substr(M.AId,1,1)) = 'a'
group by TutId, M.AId;



-- Accessable by Managers

create view Student as
select * from B_A3Student;



-- Accessable by Managers

create view Mark as
select * from B_Mark;



-- Accessable by Managers
-- Displays the final mark for all students

create view FinalMark as
select StudentId, ROUND(SUM(FinalPercent*(AMark/TotalMark)),2) as Final_Mark
from B_Mark, B_AssessmentItem
where B_Mark.AId = B_AssessmentItem.AId
group by StudentId;



-- granting roles to people
grant u3952239_Student to Student2010123;
grant u3952239_Student to Student2010999;
grant u3952239_Tutor to TutorJones;
grant u3952239_Tutor to TutorSmith;
grant u3952239_CourseManagers to Manager8100234;
grant u3952239_CourseManagers to Manager9100888;


-- make tutorial public
grant select on tutorial to Public;

-- Part 1
grant select on AssessmentItem to u3952239_Student;
grant select on AssessmentItem to u3952239_Tutor;
grant select on AssessmentItem to u3952239_CourseManagers;

-- Part 1
grant select on AssignmentSummary to u3952239_Student;
grant select on AssignmentSummary to u3952239_Tutor;
grant select on AssignmentSummary to u3952239_CourseManagers;

-- Part 2
grant select on Student_Student to u3952239_Student;
grant select on Student_Mark to u3952239_Student;

-- Part 3
grant select on Tutor_Student to u3952239_Tutor;
grant insert, update, delete, select on Tutor_Mark to u3952239_Tutor;
grant select on MarkSummary to u3952239_Tutor;

-- Part 4
grant delete, update, insert on Tutorial to u3952239_CourseManagers;
grant select, delete, update, insert on Student to u3952239_CourseManagers;
grant select, delete, update, insert on AssessmentItem
to u3952239_CourseManagers;
grant select, delete, update, insert on Mark to u3952239_CourseManagers;
grant select on FinalMark to U3952239_CourseManagers;