A company owns a fleet of buses on which it carries fare-paying passengers. On occasion, incidents occur that result in a claim. The company's claims section handles most of the claims. When an incident occurs, staff members are expected to report it, giving his or her staff number (staff_no), name (staff_name) and role (staff_role) along with details of the incident (a_date, a_description, a_location). If a bus is involved, its registration is also recorded (bus_registration). An incident key (incident_key) is generated at the time of recording. Claimants can submit claims, giving the incident details (a_date, a_description, a_location), their details (cl_name, cl_addr), date of birth (cl_dob) and the nature of the claim (natureofclaim). A claim key (cl_key) is generated at the time of recording. If the incident has not yet been reported by a member of staff, it is recorded with the claim and the claim state (c_state) is ' U ' for unconfirmed. If a staff member confirms it, the state is updated to ' C ' for confirmed and the staff_no is added to the incident. While assessing the claim, the assessor may look for an expert report, creating the request (cl_expertreport) giving the claim key (cl_key) and picking an appropriate expert (expertid) from the experts table (cl_expert). This action causes the claim state to change to ' E ' for awaiting expert. When the expert has finished reviewing the claim, he / she updates the report, adding content (reportcontent) and the date he / she submitted the report. If all expert reports on the claim have been submitted, the claim state (c_state) reverts to ' C ' for confirmed. When the claim is assessed its state can change to ' S ' for settled or ' R ' for rejected. Question 1 (compulsory) [40 marks] 1. (a) Claimants can make claims by electronic means, but can only ever see data specific to them. They cannot see incident details or details of other claims related to that incident. The assessor can see everything, and can change the claim state and request expert reports. The expert can see details of the claimant, the claim and the incident and can submit reportcontent and datesubmitted for expert reports requested of them and change their own address. Other staff members can see their own details and incidents and can verify them, by entering their staff no. 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) 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 staff names (STAFF_NAME) who are working as drivers (STAFF_ROLE='Driver') who have never reported an incident. (6 marks) (ii) Write SQL to list the names of claimants (CL_NAME) whose claims have been investigated by a doctor (E_Expertise is 'Medical'). (6 marks) (iii) Write SQL to list the expert name, expertid and e_expertise of any expert that has submitted expert reports on more than ten claims. (6 marks) (c) Write a function count_claims(DOB) to take an input parameter of a date and return number of claims made by claimants who were born on that date, including error and exception checking. (10 marks) (d) Explain how this function could be called from a 3GL (like Python) in a safe and secure way, noting security you would apply and the steps required in a 3GL to retrieve data from a relational database. (6 marks)