A university runs a residence on the college campus, for its students. Students who are studying in the college can rent rooms in the residence. There are different types of apartments; some have individual rooms with en-suites, but share a kitchen and living area between four or six students. Some are for couples and these have their own apartement. Undergraduates are in a different wing to postgraduates. Every student has access to a kitchen, but there is also on-site catering, that can either be collected or delivered to a room. There is a standing charge of 200 at the start of the residency, giving the student an AMOUNT_OWING amount of 200. Students can go into arrears of 50. When this is used up, students cannot order any more until they pay. The residence records each order and students in arrears are sent a bill at the end of each month. The record of orders is kept until the end of the academic year. Question 1 (compulsory) [40 marks] (a) Prospective students can see the rooms that are available for rent. Student residents can see all their own details. They can see the full menu and can add orders online. Caterers can add and delete menu items. They can see and can update orders to indicate that they are delivered. Porters can add students and allocate students to rooms. Copy the table below into your examination script and using the information described in Case Study 1, fill in the boxes with permissions (S for select, I for insert, U for update, D for delete) that each type of user has over the tables, using the principle of least privilege. (6) marks) (b) Write queries to do the following: (3x6 marks) (i) Write SQL to return a list of all item numbers (item_number), item_name and the total quantity of that item that has been ordered. (6 marks) (ii) Write SQL to list the names of students who have ordered the menu_item with item_name 'Lasagne'. (6 marks) (iii) Write SQL to list the room-key and number of occupants for any room that has fewer occupants than its capacity, ROOM_CAPACITY (i.e. there's a place left). (6 marks) (c) Write a PL/SQL function total_order_cost to take input parameters of date and student_no and return the total amount that student has spent on orders since the date. (10 marks) (d) Assuming the conceptual schema shown is in a schema named SRESIDENCE, show how you would make it possible for a user with the schema name JBLOGGS to run a query to return the student_name, amount_owing and total_order_cost for the student with student_no 'C11223344' in the last thirty days, using the function written in part (c). (6 marks)