(a) (i) Create a view calledcourse_assignment_average that, for each course and assignment in the assignment table, gives the course code, assignment number and average mark for that assignment. The columns of the view should be named course_code, assignment_no and average_mark, respectively, and values in the average_mark column should be represented using two decimal places (e.g. 4.53, 69.99). Include in your answer a copy of the result obtained when the view is evaluated (that is, the table that results from the evaluation of SELECT * FROM course_assignment_average). (5 marks) (ii) Explain why values in the base table assignment cannot be inserted or updated using the view course_assignment_average. (2 marks) (iii) Instead of using a view to generate the course_assignment_average the averages could be held in a base table. Describe one disadvantage of the use of a view, and one disadvantage of the use of a base table for this purpose. Briefly describe how triggers can be used to keep the course_assignment_average base table consistent. (You do not need to write the triggers, just describe how they would be used.) (3 marks) (b) The university has been asked to produce information about student assignment submissions. For a specific assignment on a course and a specific student they want to know if that student submitted that assignment for that course. (i) Write an SQL function that, given a student id, course code and assignment number will return 'submitted' if that student on that course submitted that assignment. If the student did not submit that assignment for that course then the function should return not submitted'. Your function should have the name assignment_check. The domains student_ids, course_codes and assignment_numbers have already been defined in the University database. (11 marks) (ii) Write a query that uses your assignment_check function to answer the following request: For each student who has submitted an examination paper on a course, give the student id, course code, examination mark, and the result of applying assignment_check to assignments numbered 1, 2 and 3 on that course. Your result should produce a table with the headings shown in the first row of Figure 2 below. student course exammark assignmenti assignment2 assignment3 S39 c4 50 submitted submitted submitted s40 c2 30 not submitted submitted submitted Figure 2 Result of the expected query on a particular set of data You should include a copy of the output from your query in your answer. (4 marks) (a) (i) Create a view calledcourse_assignment_average that, for each course and assignment in the assignment table, gives the course code, assignment number and average mark for that assignment. The columns of the view should be named course_code, assignment_no and average_mark, respectively, and values in the average_mark column should be represented using two decimal places (e.g. 4.53, 69.99). Include in your answer a copy of the result obtained when the view is evaluated (that is, the table that results from the evaluation of SELECT * FROM course_assignment_average). (5 marks) (ii) Explain why values in the base table assignment cannot be inserted or updated using the view course_assignment_average. (2 marks) (iii) Instead of using a view to generate the course_assignment_average the averages could be held in a base table. Describe one disadvantage of the use of a view, and one disadvantage of the use of a base table for this purpose. Briefly describe how triggers can be used to keep the course_assignment_average base table consistent. (You do not need to write the triggers, just describe how they would be used.) (3 marks) (b) The university has been asked to produce information about student assignment submissions. For a specific assignment on a course and a specific student they want to know if that student submitted that assignment for that course. (i) Write an SQL function that, given a student id, course code and assignment number will return 'submitted' if that student on that course submitted that assignment. If the student did not submit that assignment for that course then the function should return not submitted'. Your function should have the name assignment_check. The domains student_ids, course_codes and assignment_numbers have already been defined in the University database. (11 marks) (ii) Write a query that uses your assignment_check function to answer the following request: For each student who has submitted an examination paper on a course, give the student id, course code, examination mark, and the result of applying assignment_check to assignments numbered 1, 2 and 3 on that course. Your result should produce a table with the headings shown in the first row of Figure 2 below. student course exammark assignmenti assignment2 assignment3 S39 c4 50 submitted submitted submitted s40 c2 30 not submitted submitted submitted Figure 2 Result of the expected query on a particular set of data You should include a copy of the output from your query in your answer. (4 marks)