Question F:
To identify whether tutors who teach on more than one module mark to the same level of difficulty, our group decided to look on the AVG(STDEVP of each tutor’ modules’ average marks). If the average STDEVP of tutors is lower than 10, we define it is reasonable and we can conclude that tutors mark to the same level of difficulty. If average STDEVP is larger than 10, we can conclude that tutors do not mark to the same level of difficulty. We choose the value of 10 because it is the most common range between degree classification.
Firstly, we calculate the average actual marks of coursework and exam for each module by multiplying the weighting of that assessment and average mark of that assessment. And we name QUERY15 for average actual mark of coursework and QUERY16 for average actual mark of exams.
Secondly, we need to get the average mark for each module by combining QUERY15 and QUERY16. We want to outer join the tables but there is no such command in the Microsoft access so we choose to inner join first, right join next and left join last to achieve the effect of an outer join. And we name this query as QUERY17.
Thirdly, we want to select the tutors who teach more than one module and join it with the TAUGHT_BY table to get the information of which tutor teaches which modules. And we save it as QUERY18.
Finally, we link the average mark for the modules query with tutor who teach more than one module (i.e. join QUERY17 & QUERY18), then select STDVP of each tutor’s modules’ average marks and take the average of all STDVP. We name this step as QUERY19.
From the result of QUERY19 we got 8.13 marks, which is below 10 marks, so we can say that tutors do mark to the same level of difficulty. (If you want to see all queries in one check QUERY 20)

QUERY 15:
SELECT module_code, avg_cw*weighting AS avg_of_cw
FROM (SELECT coursework_name, AVG(marks) AS avg_cw FROM submitted_by GROUP BY coursework_name) AS b, coursework
WHERE coursework.coursework_name=b.coursework_name;

QUERY 16:
SELECT module_code, avg_e*weighting AS avg_of_exam
FROM (SELECT exam_code, AVG(marks) AS avg_e FROM sat_by GROUP BY exam_code) AS a, exam
WHERE exam.exam_code=a.exam_code;

QUERY17:
SELECT Query15.module_code,avg_of_exam+avg_of_cw AS avg_mark
FROM Query15,Query16
WHERE Query15.module_code = Query16.module_code
UNION ALL
SELECT Query16.module_code,avg_of_exam AS avg_mark
FROM Query15 RIGHT JOIN Query16
ON Query15.module_code = Query16.module_code
WHERE (((Query15.module_code) Is Null))
UNION ALL SELECT Query15.module_code,avg_of_cw AS avg_mark
FROM Query15 LEFT JOIN Query16
ON Query15.module_code = Query16.module_code
WHERE (((Query16.module_code) Is Null));

QUERY18: SELECT c.tutor_id, module_code FROM (SELECT tutor_id, count(module_code) FROM taught_by GROUP BY tutor_id HAVING count(module_code)>1) AS c, taught_by WHERE c.tutor_id=taught_by.tutor_id;

QUERY19:
SELECT ROUND(AVG(std_of_each_tutor),2) AS avg_of_std
FROM (SELECT TUTOR_ID, StDevP(AVG_MARK) AS std_of_each_tutor FROM (SELECT tutor_id, avg_mark FROM X, Y WHERE X.module_code=Y.module_code) GROUP BY TUTOR_ID);

QUERY20:
SELECT Round(Avg(std_of_each_tutor),2) AS avg_of_std
FROM (SELECT TUTOR_ID, StDevP(AVG_MARK) AS std_of_each_tutor FROM (SELECT tutor_id, avg_mark FROM (SELECT query15.module_code,round(avg_of_exam+avg_of_cw,2) AS avg_mark FROM (SELECT module_code, avg_cw*weighting AS avg_of_cw FROM (SELECT