using oracle sql
This question covers Sections 6 and 7 of Block 3. This question considers views, triggers and functions and makes use of the University database. (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. 50 student course exammark assignmenti assignment2 assignment3 S39 c4 submitted submitted submitted s40 c2 30 not submitted submitted submitted Figure 2 Result of the expected query on a particular set of data AssignmentIncludes Studentld CourseCode Assignment Number Form Number c2 1 SC754 s10 c4 1 SC458 s09 Examination Studentld Course Code Examination Location Mark s05 Bath 57 s07 Bedford 85 s09 c2 New York 56 s09 Taunton 63 s09 c7 Taunton 71 s10 Gateshead 27 88888 ExamIncludes Studentld CourseCode s05 FormNumber SC245 SpecialCircumstances Form Form Number Date Received SC245 2004-10-19 SC458 2004-04-16 SC754 2004-03-07 Course Code c4 c5 c7 Assignment Studentld Course Code Assignment Number Mark s01 c4 1 80 s01 c4 2 74 s02 c5 1 70 s02 c5 2 65 s02 3 73 s02 c7 1 53 s02 c7 2 58 s05 c2 1 78 s05 c2 2 63 s07 c4 1 91 s09 c2 1 92 s09 2 76 s09 1 85 s10 1 45 s22 1 74 s22 c2 2 68 s38 1 51 s38 c5 3 43 s57 c4 1 93 s57 c4 2 82 s57 c4 3 84 s57 c5 1 74 c7 c4 1888 Enrolment Studentld s01 SO2 SO2 s05 s05 S07 s09 s09 s09 s10 s10 s22 s38 s38 546 s57 s57 EnrolmentDate 2005-01-12 2005-01-01 2005-06-12 2004-06-04 2004-10-18 2004-12-12 2004-12-16 2004-12-18 2004-12-15 2004-06-20 2005-05-05 2002-03-15 2003-09-18 2004-03-09 2002-03-01 2001-06-30 2003-01-20 c7 8888888 Course Course Code c2 c4 65 Title Syntax Semantics Logic Credit 30 60 60 Tutors Studentld s01 s02 Course Code c4 c5 StaffNumber 3158 5324
