Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

(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

image text in transcribed

image text in transcribed

(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)

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Database Fundamentals Study Guide

Authors: Dr. Sergio Pisano

1st Edition

B09K1WW84J, 979-8985115307

More Books

Students also viewed these Databases questions

Question

What is the difference between Needs and GAP Analyses?

Answered: 1 week ago

Question

What are ERP suites? Are HCMSs part of ERPs?

Answered: 1 week ago