Comp2400 Assignment 3
---------------------

Name: Joseph Curtis
ID: 3952239


Question 1.

Read Accessed: B_A3Student, B_AssessmentItem, B_Mark
Write Accessed: none

Users making transaction: Course Managers
Input values required: none

SQL statements:

select M.StudentId, Surname, FirstName, sum(FinalPercent*(AMark/TotalMark))
as Final_Mark
from Student S, AssessmentItem A, Mark M
where M.AId = A.AId
and M.StudentId = S.StudentId
group by M.StudentId, Surname, FirstName
order by Final_Mark desc;


Question 2.

Read Accessed: Tutorial, Student
Write Accessed: none

Users making transaction: Course Managers
Input values required: TutId

SQL Statements:

select StudentId, Surname, FirstName 
from Tutorial T, Student S
where T.TutId = '&TutId'
and T.TutId = S.TutId;


Question 3.

Read Accessed: Mark, Student, Tutorial
Write Accessed: Mark

Users making transaction: Tutors
Input values required: AId, StudentId

SQL Statements:

insert into TutorMark (AMark)
values (&Mark)
where TutorMark.AId = '&AId'
and TutorMark.StudentId = &StudentId; 


Question 4.

Read Accessed: Student
Write Accessed: Student

Users making transaction: Course Managers
Input values required: TutId, StudentId

SQL Statements:

update Student
set TutId = '&TutId'
where StudentId = &StudentId
and TutId in
    select TutId from Student
    where count(StudentId) < 19
    group by TutId;


Question 5.

Read Accessed: Mark, AssessmentItem
Write Accessed: none

Users making transaction: Course Managers
Input values required: AId

SQL Statements:

select StudentId from Mark M, AssessmentItem A
where AId = '&AId'
and M.AId = A.AId
and AMark / TotalMark < 0.5;